Create a table:
CREATE TABLE table_name
(column_name data_type);
Select all from some table of some database:
SELECT * from [database_name].[dbo].[table_name];
Select some column of table as different name:
SELECT column_name AS new_name FROM table_name;
Insert into table:
INSERT INTO table_name
VALUES (123);
Delete all contents of a table:
DELETE FROM table_name;
or
TRUNCATE TABLE table_name;
Delete complete table itself:
DROP TABLE table_name;
Change column data type
ALTER TABLE table_name
ALTER COLUMN column_name varchar(255);
Rename a column:
EXEC sp_rename '[table_name].[old_column_name]', 'new_column_name', 'COLUMN';
Add a column into a table:
ALTER TABLE [dbo].[table_name]
ADD [column_name] VARCHAR(255);
Delete a table:
USE [database_name]
DROP TABLE [dbo].[table_name]
Check data length of a column:
SELECT [column_name], DATALENGTH([column_name]) AS Data_length from table_name;
for varchar take in mind that it takes actual data size +2 bytes always.
IF, ELSE types of conditions:
SELECT @var1 + iif(@var2 is null, '', ' ' + @var2) + ' ' + @var3 AS column_name
or
SELECT @var1 + CASE WHEN @var2 IS NULL THEN '' ELSE ' ' + @var2 END + ' ' + @var3 AS column_name
or
SELECT @var1 + COALESCE(' ' + @var2,'') + ' ' + @var3 AS column_name
or
SELECT CONCAT(@var1, ' ' + @var2, ' ', @var3) AS column_name
SELECT templates:
SELECT * FROM [table_name]
WHERE [column_name] LIKE 'r-t%'
where data in column_name starts from «r» or «s» or «t»
SELECT * FROM [table_name]
WHERE [column_name] LIKE '^rst%'
where data in column_name does not starts from «r» or «s» or «t»
to find exact symbol that is mask in t-sql use square brackets [symbol]
SELECT * FROM [table_name]
WHERE [column_name] LIKE '[%]%'
where data in column_name starts from «%»
% —
_ —
SELECT, GROUP, ORDER:
SELECT year(column_name) as YearOfDateOfBirth, count(*) as NumberBorn
FROM [dbo].[table_name]
WHERE 1=1
GROUP BY year(column_name)
ORDER BY year(column_name) ASC
count(*) — counts number of rows
WHERE 1=1 — just filler
GROUP BY — group by something
ORDER BY — order by something
ASC — acceding order
DESC — descending order
YearOfDateOfBirth — alias for column name
SELECT with TOP:
SELECT top(5) left([column_name],1) as Initial, count(*) as CountOfInitial
FROM table_name
GROUP BY LEFT([column_name],1)
ORDER BY count(*) DESC
SELECT top(5) — show top 5 results by count of rows
left([column_name],1) — select by 1 character from left in column_name column
SELECT with HAVING:
SELECT left([column_name],1) as Initial, count(*) as CountOfInitial
FROM table_name
GROUP BY LEFT([column_name],1)
HAVING count(*)>=50
ORDER BY count(*) DESC
HAVING count(*)>=50 — show only results where count of rows is more or equals to 50
Update information in a column:
UPDATE table_name
SET [column_name] = NULL
WHERE [column_name] = ''
set data to ‘NULL’ where data equals » (empty) in a column column_name
SELECT and ORDER BY different from GROUP BY statement:
SELECT DATENAME(month,DateOfBirth) as MonthNumber, count(*) as NumberEmployees
FROM table_name
GROUP BY DATENAME(month,DateOfBirth), DATEPART(month,DateOfBirth)
ORDER BY DATEPART(month,DateOfBirth) DESC
Orders by month and shows only text version of a month name
Update certain data in table and check output:
BEGIN TRAN
SELECT * FROM tblTransaction
WHERE EmployeeNumber = 194
UPDATE tblTransaction
SET EmployeeNumber = 194
OUTPUT inserted., deleted.
FROM tblTransaction
WHERE EmployeeNumber = 3
SELECT * FROM tblTransaction
WHERE EmployeeNumber = 194
ROLLBACK TRAN
In table tblTransaction where EmployeeNumber equals 3 change it to 194
And rollback transaction
JOIN:
SELECT [dbo].[tblEmployee].[EmployeeNumber], [EmployeeFirstName], [EmployeeLastName], sum([Amount]) as SumOfAmount
FROM [dbo].[tblEmployee]
JOIN [dbo].[tblTransaction]
on [dbo].[tblEmployee].[EmployeeNumber] = [dbo].[tblTransaction].[EmployeeNumber]
GROUP BY [dbo].[tblEmployee].[EmployeeNumber], [EmployeeFirstName], [EmployeeLastName]
ORDER BY [EmployeeNumber]
3 table JOIN:
SELECT DepartmentHead, SUM(Amount) as SumOfAmount
FROM tblDepartment
LEFT JOIN tblEmployee
ON tblDepartment.Department = tblEmployee.Department
LEFT JOIN tblTransaction
ON tblEmployee.EmployeeNumber = tblTransaction.EmployeeNumber
GROUP BY DepartmentHead
ORDER BY DepartmentHead
Execute and rollback transaction:
BEGIN TRANSACTION
SELECT COUNT(*) FROM tblTransaction
DELETE tblTransaction
FROM tblEmployee AS E
RIGHT JOIN tblTransaction AS T
ON E.EmployeeNumber = T.EmployeeNumber
WHERE E.EmployeeNumber IS NULL
SELECT COUNT(*) FROM tblTransaction
ROLLBACK TRANSACTION
JOINs:
INNER JOIN — default — row must exist in both tables
LEFT JOIN — ALL rows from LEFT table
RIGHT JOIN — ALL rows from RIGHT table
CROSS — ALL rows from both tables, multiply all possible combinations.
Create table from information from another table:
SELECT DISTINCT column_name1, '' AS column_name2
INTO new_table_name
FROM old_table_name
Will create table with new_table_name with 2 columns, column_name1 with unique rows from column_name1 old_table_name from and empty column_name2
Constraints
Add a Unique constraint:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column_name);
Delete the constraint:
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
Add constraint with default value:
ALTER TABLE table_name
ADD CONSTRAINT column_name DEFAULT GETDATE() FOR column_name;
Add a check constraint to a column:
ALTER TABLE table_name
ADD CONSTRAINT chkAmount CHECK (Amount>-1000 AND Amount < 1000)
Add a check constraint to a column without checking existing rows but replace if ‘.’ exists in a value with » (nothing):
ALTER TABLE table_name WITH NOCHECK
ADD CONSTRAINT constraint_name CHECK
(REPLACE(column_name,'.','') = column_name OR column_name IS NULL)
Add a PRIMARY KEY constraint:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column_name)
Add a FOREIGN KEY:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name FOREIGN KEY (column_name)
REFERENCES table_name2(column_name2)
Complex FOREING KEY constrain:
ALTER TABLE tblTransaction ALTER COLUMN EmployeeNumber INT NULL
ALTER TABLE tblTransaction ADD CONSTRAINT DF_tblTransaction DEFAULT 124 FOR EmployeeNumber
ALTER TABLE tblTransaction WITH NOCHECK
ADD CONSTRAINT FK_tblTransaction_EmployeeNumber FOREIGN KEY (EmployeeNumber)
REFERENCES tblEmployee(EmployeeNumber)
ON UPDATE CASCADE
ON DELETE set default
1. Alter table and column to NULLable INTeger
2. Add constraint with default value 124 for column
3. Set FOREIGN KEY
4. On update row cascade change
5. On delete row set default value
Create a simple trigger:
CREATE TRIGGER trigger_name
ON table_name
AFTER DELETE, INSERT, UPDATE
AS
BEGIN
SELECT * FROM inserted
SELECT * FROM deleted
END
GO
Show current triggers configuration:
EXEC sp_configure 'nested triggers';
Change current triggers configuration:
EXEC sp_configure 'nested triggers', 0;
RECONFIGURE
Nested trigger:
ALTER TRIGGER TR_tblTransaction
ON tblTransaction
AFTER DELETE, INSERT, UPDATE
AS
BEGIN
IF @@ROWCOUNT > 0
BEGIN
select * from Inserted
select * from Deleted
END
END
GO
Union — unites output:
SELECT convert(char(5),'hi') AS GreetingNow
UNION
SELECT convert(char(11),'hello there')
UNION
SELECT convert(char(11),'Bonjour')
UNION
SELECT convert(char(11),'hi')
Case statement:
SELECT TOP (1000) [EmployeeNumber]
,[EmployeeFirstName]
,[EmployeeMiddleName]
,[EmployeeLastName]
,[EmployeeGovernmentID]
,[DateOfBirth]
,[Department],
CASE WHEN left([EmployeeGovernmentID],1)='A' then 'Letter A'
WHEN left([EmployeeGovernmentID],1)='B' then 'Letter B'
ELSE 'Neither letter' END AS Name
FROM [70-461].[dbo].[tblEmployee]
Merging 2 tables:
MERGE INTO tblTransaction as T
USING (
SELECT [EmployeeNumber], DateOfTransaction, sum(Amount) AS Amount
FROM tblTransaction
GROUP BY [EmployeeNumber], DateOfTransaction
) AS S
ON T.EmployeeNumber = S.EmployeeNumber AND T.DateOfTransaction = S.DateOfTransaction
WHEN MATCHED THEN
UPDATE SET Amount = T.Amount + S.Amount
WHEN NOT MATCHED BY TARGET THEN
INSERT ([Amount], [DateOfTransaction], [EmployeeNumber])
VALUES (S.Amount, S.DateOfTransaction, S.EmployeeNumber);
Create and execute procedure:
CREATE PROC NameEmployees AS
BEGIN
SELECT [EmployeeNumber], [EmployeeFirstName], [EmployeeLastName]
FROM [dbo].[tblEmployee]
END
EXEC NameEmployees
Delete a procedure:
DROP PROC procedure_name
Check what procedures are currently in DB:
SELECT * FROM sys.procedures
or
SELECT object_ID('procedure_name','P')
More complex Procedure (IF, ELSE):
IF OBJECT_ID('NameEmployees', 'P') IS NOT NULL
DROP PROC NameEmployees
GO
CREATE PROC NameEmployees(@EmployeeNumber int) AS
BEGIN
IF EXISTS (SELECT * FROM [dbo].[tblEmployee] WHERE [EmployeeNumber] = @EmployeeNumber)
BEGIN
IF @EmployeeNumber < 300
BEGIN
SELECT [EmployeeNumber], [EmployeeFirstName], [EmployeeLastName]
FROM [dbo].[tblEmployee]
WHERE EmployeeNumber = @EmployeeNumber
END
ELSE
BEGIN
SELECT [EmployeeNumber], [EmployeeFirstName], [EmployeeLastName], Department
FROM [dbo].[tblEmployee]
WHERE EmployeeNumber = @EmployeeNumber
SELECT * FROM tblTransaction WHERE EmployeeNumber = @EmployeeNumber
END
END
END
go
TRY and CATCH procedure:
if object_ID('AverageBalance','P') IS NOT NULL
drop proc AverageBalance
go
create proc AverageBalance(@EmployeeNumberFrom int, @EmployeeNumberTo int, @AverageBalance int OUTPUT) as
begin
SET NOCOUNT ON
declare @TotalAmount money
declare @NumOfEmployee int
begin try
select @TotalAmount = sum(Amount) from tblTransaction
where EmployeeNumber between @EmployeeNumberFrom and @EmployeeNumberTo
select @NumOfEmployee = count(distinct EmployeeNumber) from tblEmployee
where EmployeeNumber between @EmployeeNumberFrom and @EmployeeNumberTo
set @AverageBalance = @TotalAmount / @NumOfEmployee
RETURN 0
end try
begin catch
set @AverageBalance = 0
SELECT ERROR_MESSAGE() AS ErrorMessage, ERROR_LINE() as ErrorLine,
ERROR_NUMBER() as ErrorNumber, ERROR_PROCEDURE() as ErrorProcedure,
ERROR_SEVERITY() as ErrorSeverity, -- 0-10 for information
ERROR_STATE() as ErrorState
RETURN 1
end catch
end
OVER and PARTITION BY procedure with ORDER BY + PRECEDING and FOLLOWING options. Data manipulation.
select A.EmployeeNumber, A.AttendanceMonth,
A.NumberAttendance,
SUM(A.NumberAttendance) over(PARTITION BY E.EmployeeNumber ORDER BY A.AttendanceMonth ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as RollingTotal
from tblEmployee as E join tblAttendance as A
on E.EmployeeNumber = A.EmployeeNumber
LAG (goes backwards) and LEAD (goes forward):
SELECT A.EmployeeNumber, A.AttendanceMonth,
A.NumberAttendance,
lag(NumberAttendance, 1) over(partition by E.EmployeeNumber
ORDER BY A.AttendanceMonth) AS MyLag,
lead(NumberAttendance, 1) over(partition by E.EmployeeNumber
ORDER BY A.AttendanceMonth) AS MyLead,
NumberAttendance - lag(NumberAttendance, 1) over(partition by E.EmployeeNumber
ORDER BY A.AttendanceMonth) AS MyDiff
FROM tblEmployee AS E join tblAttendance AS A
on E.EmployeeNumber = A.EmployeeNumber
PERCENTILE_CONT and PERCENTILE_DISC:
PERCENTILE_CONT(0.5) — exact value on 50% of all range
PERCENTILE_DISC(0.5) — nearest to 50% value from the range of existing values
SELECT DISTINCT EmployeeNumber,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY NumberAttendance) OVER (PARTITION BY EmployeeNumber) as AverageCont,
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY NumberAttendance) OVER (PARTITION BY EmployeeNumber) as AverageDisc
from tblAttendance
GROUPING, GROUPING_ID, ROLLUP (to get rid of UNION)
select E.Department, E.EmployeeNumber, A.AttendanceMonth as AttendanceMonth, sum(A.NumberAttendance) as NumberAttendance,
GROUPING(E.EmployeeNumber) AS EmployeeNumberGroupedBy,
GROUPING_ID(E.Department, E.EmployeeNumber, A.AttendanceMonth) AS EmployeeNumberGroupedID
from tblEmployee as E join tblAttendance as A
on E.EmployeeNumber = A.EmployeeNumber
group by ROLLUP (E.Department, E.EmployeeNumber, A.AttendanceMonth)
order by Department, EmployeeNumber, AttendanceMonth
Sub query:
SELECT * FROM [dbo].[tblTransaction] AS T
WHERE EmployeeNumber in
(SELECT EmployeeNumber FROM tblEmployee WHERE EmployeeLastName LIKE 'y%')
ORDER BY EmployeeNumber
select *
from tblTransaction as T
left join (select * from tblEmployee
where EmployeeLastName like 'y%') as E
on E.EmployeeNumber = T.EmployeeNumber
order by T.EmployeeNumber
select *
from tblTransaction as T
left join tblEmployee as E
on E.EmployeeNumber = T.EmployeeNumber
Where E.EmployeeLastName like 'y%'
order by T.EmployeeNumber
Correlated sub query:
Select *, (select count(EmployeeNumber)
from tblTransaction as T
where T.EmployeeNumber = E.EmployeeNumber) as NumTransactions,
(Select sum(Amount)
from tblTransaction as T
where T.EmployeeNumber = E.EmployeeNumber) as TotalAmount
from tblEmployee as E
Where E.EmployeeLastName like 'y%' --correlated subquery
WITH statement:
with tblWithRanking as
(select D.Department, EmployeeNumber, EmployeeFirstName, EmployeeLastName,
rank() over(partition by D.Department order by E.EmployeeNumber) as TheRank
from tblDepartment as D
join tblEmployee as E on D.Department = E.Department
select * from tblWithRanking
where TheRank <= 5
order by Department, EmployeeNumber
Complex WITH statement:
with Numbers as (
select top(select max(EmployeeNumber) from tblTransaction) row_Number() over(order by (select null)) as RowNumber
from tblTransaction as U)
select U.RowNumber from Numbers as U
left join tblTransaction as T
on U.RowNumber = T.EmployeeNumber
where T.EmployeeNumber is null
order by U.RowNumber
PIVOT statement:
with myTable as
(select year(DateOfTransaction) as TheYear, month(DateOfTransaction) as TheMonth, Amount from tblTransaction)
SELECT * FROM myTable
PIVOT (SUM(Amount) for TheMonth in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) AS myPvt
ORDER BY TheYear
SELF JOIN:
ALTER TABLE [dbo].[tblEmployee]
add Manager int
go
UPDATE [dbo].[tblEmployee]
SET Manager = ((EmployeeNumber-123)/10)+123
WHERE EmployeeNumber>123
SELECT E.EmployeeNumber, E.EmployeeFirstName, E.EmployeeLastName, E.Manager,
M.EmployeeNumber, M.EmployeeFirstName, M.EmployeeLastName, M.Manager
FROM [tblEmployee] AS E
left JOIN [tblEmployee] AS M
ON E.Manager = M.EmployeeNumber
Recursive common table expression.
alter table tblEmployee
add Manager int
go
update tblEmployee
set Manager = ((EmployeeNumber-123)/10)+123
where EmployeeNumber>123;
with myTable as
(select EmployeeNumber, EmployeeFirstName, EmployeeLastName, 0 as BossLevel --Anchor
from tblEmployee
where Manager is null
UNION ALL --UNION ALL!!
select E.EmployeeNumber, E.EmployeeFirstName, E.EmployeeLastName, myTable.BossLevel + 1 --Recursive
from tblEmployee as E
join myTable on E.Manager = myTable.EmployeeNumber
) --recursive CTE
select * from myTable
Functions:
CREATE FUNCTION AmountPlusOne(@Amount smallmoney)
RETURNS smallmoney
AS
BEGIN
RETURN @Amount + 1
END
GO
select DateOfTransaction, EmployeeNumber, Amount, dbo.AmountPlusOne(Amount) as AmountAndOne
from tblTransaction
DECLARE @myValue smallmoney
EXEC @myValue = dbo.AmountPlusOne @Amount = 345.67
select @myValue
if object_ID(N'NumberOfTransactions',N'FN') IS NOT NULL
DROP FUNCTION NumberOfTransactions
GO
CREATE FUNCTION NumberOfTransactions(@EmployeeNumber int)
RETURNS int
AS
BEGIN
DECLARE @NumberOfTransactions INT
SELECT @NumberOfTransactions = COUNT(*) FROM tblTransaction
WHERE EmployeeNumber = @EmployeeNumber
RETURN @NumberOfTransactions
END
Apply statement. To compare table to results of a function (array).
SELECT *
from dbo.TransList(123)
GO
select , (select count() from dbo.TransList(E.EmployeeNumber)) as NumTransactions
from tblEmployee as E
select *
from tblEmployee as E
outer apply TransList(E.EmployeeNumber) as T
select *
from tblEmployee as E
cross apply TransList(E.EmployeeNumber) as T
--123 left join TransList(123)
--124 left join TransList(124)
--outer apply all of tblEmployee, UDF 0+ rows
--cross apply UDF 1+ rows
--outer apply = LEFT JOIN
--cross apply = INNER JOIN
Create clustered index:
create clustered index idx_tblEmployee on [dbo].[tblEmployee]([EmployeeNumber])
Create non-clustered index:
create nonclustered index idx_tblEmployee_DateOfBirth
on [dbo].[tblEmployee]([DateOfBirth])
Create a non-clustered index including additional fields:
create nonclustered index idx_tblEmployee_DateOfBirth
on [dbo].[tblEmployee]([DateOfBirth])
include ([EmployeeFirstName],[EmployeeMiddleName],[EmployeeLastName])
Cursor:
DECLARE @EmployeeID int
DECLARE csr CURSOR FOR
SELECT EmployeeNumber
FROM [dbo].[tblEmployee]
WHERE EmployeeNumber between 120 and 299
OPEN csr
FETCH NEXT FROM csr INTO @EmployeeID
WHILE @@FETCH_STATU=0
BEGIN
SELECT * FROM [dbo].[tblTransaction] where EmployeeNumber = @EmployeeID
FETCH NEXT FROM csr INTO @EmployeeID
END
CLOSE csr
deallocate csr
Alternative to the cursor:
SELECT *
FROM tblTransacrion AS T
right join tblEmployee as E
on T.EmployeeNumber = E.EmployeeNumber
WHERE E.EmployeeNumber between 120 and 299
and T.EmployeeNumber is not NULL
Iterate through 3000 rows and update each separately:
DECLARE @var AS INT
SET @var = 0
WHILE @var!=3000
BEGIN
UPDATE [dbo].[tblTransaction]
SET [DateOfTransaction] = CURRENT_TIMESTAMP - FLOOR(RAND() * 14)
WHERE [EmployeeNumber] = @var
SET @var = @var + 1
END
UI:
If something in SSMS is shown as not found (underlined in red) update local cache by hitting CRTL-SHIFT-R or go to Edit —> IntelliSense —> Refresh local cache.
Char types:
char — ASCII — 1 byte
varchar — ASCII — 1 byte
nchar — UNICODE — 2 bytes
nvarchar — UNICODE — 2 bytes
Check what port is used by SQL server:
SELECT DISTINCT
local_tcp_port
FROM sys.dm_exec_connections
WHERE local_tcp_port IS NOT NULL
Change MSSQL server default Collation:
WARNING this action can lead to data loss Backup everything and take a snapshot.
Stop MS SQL service.
Open cmd with admin privileges
change dir to where ms SQL server installed (Examle: C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Binn)
Execute command: sqlservr -m -T4022 -T3659 -s»SQL_Server_name» -q»Cyrillic_General_CI_AS»
wait till it will finish
you can see errors
wait for more
after it finishes start ms SQL service.
done
