This document provides a cheat sheet of common T-SQL functions and statements including determining if an object exists, adding check constraints, understanding T-SQL processing order, using window functions, casting, case expressions, string functions, wildcards, date functions, metadata queries, cross joins, derived tables, common table expressions, recursive CTEs, correlated subqueries, EXISTS, views, APPLY operator, set operators, and PIVOT.
This document provides a cheat sheet of common T-SQL functions and statements including determining if an object exists, adding check constraints, understanding T-SQL processing order, using window functions, casting, case expressions, string functions, wildcards, date functions, metadata queries, cross joins, derived tables, common table expressions, recursive CTEs, correlated subqueries, EXISTS, views, APPLY operator, set operators, and PIVOT.
This document provides a cheat sheet of common T-SQL functions and statements including determining if an object exists, adding check constraints, understanding T-SQL processing order, using window functions, casting, case expressions, string functions, wildcards, date functions, metadata queries, cross joins, derived tables, common table expressions, recursive CTEs, correlated subqueries, EXISTS, views, APPLY operator, set operators, and PIVOT.
This document provides a cheat sheet of common T-SQL functions and statements including determining if an object exists, adding check constraints, understanding T-SQL processing order, using window functions, casting, case expressions, string functions, wildcards, date functions, metadata queries, cross joins, derived tables, common table expressions, recursive CTEs, correlated subqueries, EXISTS, views, APPLY operator, set operators, and PIVOT.
Locking Exclusive Locks generated by update operations no other session can update or read an item that has an exclusive lock Shared Locks generated by selects Isolation levels affect how sql interacts with shared locks: READ UNCOMMITTED - SELECT does not generate shared locks - dirty reads READ COMMITTED (default) - SELECT requires shared locks REPEATABLE READ - shared lock open for the entire transaction SERIALIZABLE - locks range of keys returned to prevent phantom records SNAPSHOT - reads previous row reivsion stored in tempdb READ COMMITTED SNAPSHOT - Gets the last committed version of the row that was available when the statement started Can be set at database level or transaction level: SET TRANSACTION ISOLATION LEVEL <name>
Variables DECLARE @i AS INT;
SET @i = 10;
DECLARE @i AS INT = 10;-- only works in SS2008 or higher
DECLARE @ManufacturerCode VARCHAR(25)
DECLARE @Name VARCHAR(100)
-- select into variables
SELECT @ManufacturerCode = ManufacturerCode,
@Name = NAME
FROM product.Model
WHERE ModelID = 10000
Flow Control -- single statements
IF < expression >
-- will execute if expression is true
ELSE
-- will execute if expression is false or unknown
-- Multiple statements
IF < expression >
BEGIN
END
ELSE
BEGIN END
WHILE < expression
BEGIN
END
-- FOR replacement
DECLARE @i AS INT;
SET @i = 1
WHILE @i <= 10
BEGIN
PRINT @i;
SET @i = @i + 1;
END;
Cursors DECLARE @ModelID INT,
@ManufacturerCode VARCHAR(25)
DECLARE C CURSOR FAST_FORWARD /* read only, forward only */
FOR
SELECT ModelID,
ManufacturerCode
FROM Product.Model
ORDER BY ModelID
OPEN C
FETCH NEXT
FROM C
INTO @ModelID,
@ManufacturerCode;
WHILE @@FETCH_STATUS = 0
BEGIN
-- do work
FETCH NEXT
FROM C
INTO @ModelID,
@ManufacturerCode;
END CLOSE C;
DEALLOCATE C;
Min Key approach
DECLARE @ModelID INT
DECLARE @ManufacturerCode VARCHAR(25)
SET @ModelID = (SELECT MIN(ModelID)
FROM product.model)
WHILE @ModelID IS NOT NULL
BEGIN
SELECT @ManufacturerCode = ManufacturerCode
FROM product.model
WHERE ModelID = @ModelID
-- do work
SET @ModelID = (SELECT MIN(ModelID)
FROM product.model
WHERE ModelID > @ModelID)
END
Temporary Tables CREATE TABLE #TempTable (
ModelID INT,
ManufacturerCode VARCHAR(25)
CREATE TABLE ##GlobalTempTable (
ModelID INT,
ManufacturerCode VARCHAR(25)
Table Variables DECLARE @TempTable TABLE (
ModelID INT,
ManufacturerCode VARCHAR(25)
);
Table Types IF TYPE_ID('dbo.OrderTotalsByYear') IS NOT NULL DROP TYPE dbo.OrderTotalsByYear;
CREATE TYPE dbo.OrderTotalsByYear AS TABLE (
orderyear INT NOT NULL PRIMARY KEY,
qty INT NOT NULL
);
DECLARE @MyOrderTotalsByYear AS dbo.OrderTotalsByYear;
Dynamic SQL -- EXEC
DECLARE @sql AS VARCHAR(100);
SET @sql = 'PRINT ''This message was printed by a dynamic SQL batch.'';';
EXEC (@sql);
-- sp_execute - supports params
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'select * from product.model where Name LIKE ''%'' + @Name + ''%'' AND CategoryId = @CategoryId'
EXEC sp_executesql @stmt = @sql,
@params = N'@Name as varchar(100), @CategoryId as int',
@Name = 'John Deere',
@CategoryId = 2
User Defined Functions
Scalar CREATE FUNCTION dbo.fn_age (
@birthdate AS DATETIME,
@eventdate AS DATETIME
RETURNS INT -- <-- Defines as scalar
AS
BEGIN
RETURN DATEDIFF(year, @birthdate, @eventdate) –
CASE WHEN 100 * MONTH(@eventdate) + DAY(@eventdate) < 100 * MONTH(@birthdate) + DAY(@birthdate) THEN 1
ELSE 0
END
END
GO
Table Value ALTER FUNCTION [dbo].[Split_MultiValue_Parameter] ( @delimitedString VARCHAR(MAX),
@delimiter VARCHAR(1)
RETURNS @Table TABLE (VALUE VARCHAR(100)) -- <-- Defines as table
AS
BEGIN
DECLARE @tempString VARCHAR(MAX);
SET @tempString = ISNULL(@delimitedString, '') + @Delimiter;
