Documentation

Origin documentation for msCount

Syntax:

COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )

This function returns the number of items found in a group. COUNT operates like the COUNT_BIG function. These functions differ only in the data types of their return values. COUNT always returns an int data type value. COUNT_BIG always returns a bigint data type value.

Example:

SELECT COUNT(DISTINCT Title)
FROM HumanResources.Employee;
GO

Example:

SELECT COUNT(*)
FROM HumanResources.Employee;
GO

Example:

SELECT COUNT(*), AVG(Bonus)
FROM Sales.SalesPerson
WHERE SalesQuota > 25000;
GO

Example:

SELECT DISTINCT Name
    , MIN(Rate) OVER (PARTITION BY edh.DepartmentID) AS MinSalary
    , MAX(Rate) OVER (PARTITION BY edh.DepartmentID) AS MaxSalary
    , AVG(Rate) OVER (PARTITION BY edh.DepartmentID) AS AvgSalary
    , COUNT(edh.BusinessEntityID) OVER (PARTITION BY edh.DepartmentID) AS EmployeesPerDept
FROM HumanResources.EmployeePayHistory AS eph
JOIN HumanResources.EmployeeDepartmentHistory AS edh
    ON eph.BusinessEntityID = edh.BusinessEntityID
JOIN HumanResources.Department AS d
ON d.DepartmentID = edh.DepartmentID
WHERE edh.EndDate IS NULL
ORDER BY Name;

Example:

USE ssawPDW;

SELECT COUNT(DISTINCT Title)
FROM dbo.DimEmployee;

Example:

USE ssawPDW;

SELECT COUNT(*)
FROM dbo.DimEmployee;

Example:

USE ssawPDW;

SELECT COUNT(EmployeeKey) AS TotalCount, AVG(SalesAmountQuota) AS [Average Sales Quota]
FROM dbo.FactSalesQuota
WHERE SalesAmountQuota > 500000 AND CalendarYear = 2001;

Example:

USE ssawPDW;

SELECT DepartmentName,
    COUNT(EmployeeKey)AS EmployeesInDept
FROM dbo.DimEmployee
GROUP BY DepartmentName
HAVING COUNT(EmployeeKey) > 15;

Example:

USE ssawPDW;

SELECT DISTINCT COUNT(ProductKey) OVER(PARTITION BY SalesOrderNumber) AS ProductCount
    , SalesOrderNumber
FROM dbo.FactInternetSales
WHERE SalesOrderNumber IN (N'SO53115',N'SO55981');

Origin documentation for msCount_Distinctness

Syntax:

COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )

This function returns the number of items found in a group. COUNT operates like the COUNT_BIG function. These functions differ only in the data types of their return values. COUNT always returns an int data type value. COUNT_BIG always returns a bigint data type value.

Example:

SELECT COUNT(DISTINCT Title)
FROM HumanResources.Employee;
GO

Example:

SELECT COUNT(*)
FROM HumanResources.Employee;
GO

Example:

SELECT COUNT(*), AVG(Bonus)
FROM Sales.SalesPerson
WHERE SalesQuota > 25000;
GO

Example:

SELECT DISTINCT Name
    , MIN(Rate) OVER (PARTITION BY edh.DepartmentID) AS MinSalary
    , MAX(Rate) OVER (PARTITION BY edh.DepartmentID) AS MaxSalary
    , AVG(Rate) OVER (PARTITION BY edh.DepartmentID) AS AvgSalary
    , COUNT(edh.BusinessEntityID) OVER (PARTITION BY edh.DepartmentID) AS EmployeesPerDept
FROM HumanResources.EmployeePayHistory AS eph
JOIN HumanResources.EmployeeDepartmentHistory AS edh
    ON eph.BusinessEntityID = edh.BusinessEntityID
JOIN HumanResources.Department AS d
ON d.DepartmentID = edh.DepartmentID
WHERE edh.EndDate IS NULL
ORDER BY Name;

Example:

USE ssawPDW;

SELECT COUNT(DISTINCT Title)
FROM dbo.DimEmployee;

Example:

USE ssawPDW;

SELECT COUNT(*)
FROM dbo.DimEmployee;

Example:

USE ssawPDW;

SELECT COUNT(EmployeeKey) AS TotalCount, AVG(SalesAmountQuota) AS [Average Sales Quota]
FROM dbo.FactSalesQuota
WHERE SalesAmountQuota > 500000 AND CalendarYear = 2001;

Example:

USE ssawPDW;

SELECT DepartmentName,
    COUNT(EmployeeKey)AS EmployeesInDept
FROM dbo.DimEmployee
GROUP BY DepartmentName
HAVING COUNT(EmployeeKey) > 15;

Example:

USE ssawPDW;

SELECT DISTINCT COUNT(ProductKey) OVER(PARTITION BY SalesOrderNumber) AS ProductCount
    , SalesOrderNumber
FROM dbo.FactInternetSales
WHERE SalesOrderNumber IN (N'SO53115',N'SO55981');

Origin documentation for msCount_STAR

Syntax:

COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )

This function returns the number of items found in a group. COUNT operates like the COUNT_BIG function. These functions differ only in the data types of their return values. COUNT always returns an int data type value. COUNT_BIG always returns a bigint data type value.

Example:

SELECT COUNT(DISTINCT Title)
FROM HumanResources.Employee;
GO

Example:

SELECT COUNT(*)
FROM HumanResources.Employee;
GO

Example:

SELECT COUNT(*), AVG(Bonus)
FROM Sales.SalesPerson
WHERE SalesQuota > 25000;
GO

Example:

SELECT DISTINCT Name
    , MIN(Rate) OVER (PARTITION BY edh.DepartmentID) AS MinSalary
    , MAX(Rate) OVER (PARTITION BY edh.DepartmentID) AS MaxSalary
    , AVG(Rate) OVER (PARTITION BY edh.DepartmentID) AS AvgSalary
    , COUNT(edh.BusinessEntityID) OVER (PARTITION BY edh.DepartmentID) AS EmployeesPerDept
FROM HumanResources.EmployeePayHistory AS eph
JOIN HumanResources.EmployeeDepartmentHistory AS edh
    ON eph.BusinessEntityID = edh.BusinessEntityID
JOIN HumanResources.Department AS d
ON d.DepartmentID = edh.DepartmentID
WHERE edh.EndDate IS NULL
ORDER BY Name;

Example:

USE ssawPDW;

SELECT COUNT(DISTINCT Title)
FROM dbo.DimEmployee;

Example:

USE ssawPDW;

SELECT COUNT(*)
FROM dbo.DimEmployee;

Example:

USE ssawPDW;

SELECT COUNT(EmployeeKey) AS TotalCount, AVG(SalesAmountQuota) AS [Average Sales Quota]
FROM dbo.FactSalesQuota
WHERE SalesAmountQuota > 500000 AND CalendarYear = 2001;

Example:

USE ssawPDW;

SELECT DepartmentName,
    COUNT(EmployeeKey)AS EmployeesInDept
FROM dbo.DimEmployee
GROUP BY DepartmentName
HAVING COUNT(EmployeeKey) > 15;

Example:

USE ssawPDW;

SELECT DISTINCT COUNT(ProductKey) OVER(PARTITION BY SalesOrderNumber) AS ProductCount
    , SalesOrderNumber
FROM dbo.FactInternetSales
WHERE SalesOrderNumber IN (N'SO53115',N'SO55981');

Origin documentation for msCast

Syntax:

CAST ( expression AS data_type [ ( length ) ] )

No description

Example:

SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary);

Example:

SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS;

Example:

DECLARE @myval DECIMAL(5, 2);
SET @myval = 193.57;

SELECT CAST(CAST(@myval AS VARBINARY(20)) AS DECIMAL(10, 5));

-- Or, using CONVERT
SELECT CONVERT(DECIMAL(10, 5), CONVERT(VARBINARY(20), @myval));
GO

Example:

USE AdventureWorks2019;
GO

SELECT p.FirstName,
    p.LastName,
    SUBSTRING(p.Title, 1, 25) AS Title,
    CAST(e.SickLeaveHours AS CHAR(1)) AS [Sick Leave]
FROM HumanResources.Employee e
INNER JOIN Person.Person p
    ON e.BusinessEntityID = p.BusinessEntityID
WHERE NOT e.BusinessEntityID > 5;
GO

Example:

SELECT CAST(10.6496 AS INT) AS trunc1,
       CAST(-10.6496 AS INT) AS trunc2,
       CAST(10.6496 AS NUMERIC) AS round1,
       CAST(-10.6496 AS NUMERIC) AS round2;

Example:

SELECT CAST(10.3496847 AS money);

Example:

DECLARE @x NVARCHAR(10) = 'ab' + NCHAR(0x10000);

SELECT CAST(@x AS NVARCHAR(3));

Example:

USE AdventureWorks2019;
GO

SELECT SUBSTRING(Name, 1, 30) AS ProductName,
    ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS INT) LIKE '33%';
GO

Example:

USE AdventureWorks2019;
GO

SELECT SUBSTRING(Name, 1, 30) AS ProductName,
    ListPrice
FROM Production.Product
WHERE CONVERT(INT, ListPrice) LIKE '33%';
GO

Example:

USE AdventureWorks2019;
GO

SELECT CAST(ROUND(SalesYTD / CommissionPCT, 0) AS INT) AS Computed
FROM Sales.SalesPerson
WHERE CommissionPCT != 0;
GO

Example:

SELECT 'The list price is ' + CAST(ListPrice AS VARCHAR(12)) AS ListPrice
FROM dbo.DimProduct
WHERE ListPrice BETWEEN 350.00 AND 400.00;

Example:

SELECT DISTINCT CAST(EnglishProductName AS CHAR(10)) AS Name,
    ListPrice
FROM dbo.DimProduct
WHERE EnglishProductName LIKE 'Long-Sleeve Logo Jersey, M';
GO

Example:

USE AdventureWorks2019;
GO

SELECT p.FirstName,
    p.LastName,
    s.SalesYTD,
    s.BusinessEntityID
FROM Person.Person AS p
INNER JOIN Sales.SalesPerson AS s
    ON p.BusinessEntityID = s.BusinessEntityID
WHERE CAST(CAST(s.SalesYTD AS INT) AS CHAR(20)) LIKE '2%';
GO

Example:

SELECT CONVERT(XML, '<root><child/></root>')

Example:

SELECT CONVERT(XML, '<root>          <child/>         </root>', 1)

Example:

SELECT CAST('<Name><FName>Carol</FName><LName>Elliot</LName></Name>'  AS XML)

Example:

SELECT GETDATE() AS UnconvertedDateTime,
    CAST(GETDATE() AS NVARCHAR(30)) AS UsingCast,
    CONVERT(NVARCHAR(30), GETDATE(), 126) AS UsingConvertTo_ISO8601;
GO

Example:

SELECT '2006-04-25T15:50:59.997' AS UnconvertedText,
    CAST('2006-04-25T15:50:59.997' AS DATETIME) AS UsingCast,
    CONVERT(DATETIME, '2006-04-25T15:50:59.997', 126) AS UsingConvertFrom_ISO8601;
GO

Example:

--Convert the binary value 0x4E616d65 to a character value.
SELECT CONVERT(CHAR(8), 0x4E616d65, 0) AS [Style 0, binary to character];

Example:

SELECT CONVERT(CHAR(8), 0x4E616d65, 1) AS [Style 1, binary to character];

Example:

SELECT CONVERT(CHAR(8), 0x4E616d65, 2) AS [Style 2, binary to character];

Example:

SELECT CONVERT(BINARY(8), 'Name', 0) AS [Style 0, character to binary];

Example:

SELECT CONVERT(BINARY(4), '0x4E616D65', 1) AS [Style 1, character to binary];

Example:

SELECT CONVERT(BINARY(4), '4E616D65', 2) AS [Style 2, character to binary];

Example:

DECLARE @d1 DATE,
    @t1 TIME,
    @dt1 DATETIME;

SET @d1 = GETDATE();
SET @t1 = GETDATE();
SET @dt1 = GETDATE();
SET @d1 = GETDATE();

-- When converting date to datetime the minutes portion becomes zero.
SELECT @d1 AS [DATE],
    CAST(@d1 AS DATETIME) AS [date as datetime];

-- When converting time to datetime the date portion becomes zero
-- which converts to January 1, 1900.
SELECT @t1 AS [TIME],
    CAST(@t1 AS DATETIME) AS [time as datetime];

-- When converting datetime to date or time non-applicable portion is dropped.
SELECT @dt1 AS [DATETIME],
    CAST(@dt1 AS DATE) AS [datetime as date],
    CAST(@dt1 AS TIME) AS [datetime as time];

Example:

DECLARE @d1 DATE, @dt1 DATETIME , @dt2 DATETIME2

SET @d1 = '1492-08-03'
--This is okay; Minimum YYYY for DATE is 0001

SET @dt2 = CAST(@d1 AS DATETIME2)
--This is okay; Minimum YYYY for DATETIME2 IS 0001

SET @dt1 = CAST(@d1 AS DATETIME)
--This will error with (Msg 242) "The conversion of a date data type to a datetime data type resulted in an out-of-range value."
--Minimum YYYY for DATETIME is 1753

Example:

DECLARE @string VARCHAR(10);
SET @string = 1;
SELECT @string + ' is a string.' AS Result

Example:

DECLARE @notastring INT;
SET @notastring = '1';
SELECT @notastring + ' is not a string.' AS Result

Example:

DECLARE @notastring INT;
SET @notastring = '1';
SELECT @notastring + '1'

Example:

SELECT EnglishProductName AS ProductName, ListPrice
FROM dbo.DimProduct
WHERE CAST(ListPrice AS int) LIKE '3%';

Example:

SELECT EnglishProductName AS ProductName, ListPrice
FROM dbo.DimProduct
WHERE CONVERT(INT, ListPrice) LIKE '3%';

Example:

SELECT ProductKey, UnitPrice,UnitPriceDiscountPct,
       CAST(ROUND (UnitPrice*UnitPriceDiscountPct,0) AS int) AS DiscountPrice
FROM dbo.FactResellerSales
WHERE SalesOrderNumber = 'SO47355'
      AND UnitPriceDiscountPct > .02;

Example:

SELECT EnglishProductName AS Name, ListPrice
FROM dbo.DimProduct
WHERE CAST(CAST(ListPrice AS INT) AS CHAR(20)) LIKE '2%';

Example:

SELECT TOP(1)
   SYSDATETIME() AS UnconvertedDateTime,
   CAST(SYSDATETIME() AS NVARCHAR(30)) AS UsingCast,
   CONVERT(NVARCHAR(30), SYSDATETIME(), 126) AS UsingConvertTo_ISO8601
FROM dbo.DimCustomer;

Example:

SELECT TOP(1)
   '2010-07-25T13:50:38.544' AS UnconvertedText,
CAST('2010-07-25T13:50:38.544' AS DATETIME) AS UsingCast,
   CONVERT(DATETIME, '2010-07-25T13:50:38.544', 126) AS UsingConvertFrom_ISO8601
FROM dbo.DimCustomer;

Origin documentation for msCast_Length

Syntax:

CAST ( expression AS data_type [ ( length ) ] )

No description

Example:

SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary);

Example:

SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS;

Example:

DECLARE @myval DECIMAL(5, 2);
SET @myval = 193.57;

SELECT CAST(CAST(@myval AS VARBINARY(20)) AS DECIMAL(10, 5));

-- Or, using CONVERT
SELECT CONVERT(DECIMAL(10, 5), CONVERT(VARBINARY(20), @myval));
GO

Example:

USE AdventureWorks2019;
GO

SELECT p.FirstName,
    p.LastName,
    SUBSTRING(p.Title, 1, 25) AS Title,
    CAST(e.SickLeaveHours AS CHAR(1)) AS [Sick Leave]
FROM HumanResources.Employee e
INNER JOIN Person.Person p
    ON e.BusinessEntityID = p.BusinessEntityID
WHERE NOT e.BusinessEntityID > 5;
GO

Example:

SELECT CAST(10.6496 AS INT) AS trunc1,
       CAST(-10.6496 AS INT) AS trunc2,
       CAST(10.6496 AS NUMERIC) AS round1,
       CAST(-10.6496 AS NUMERIC) AS round2;

Example:

SELECT CAST(10.3496847 AS money);

Example:

DECLARE @x NVARCHAR(10) = 'ab' + NCHAR(0x10000);

SELECT CAST(@x AS NVARCHAR(3));

Example:

USE AdventureWorks2019;
GO

SELECT SUBSTRING(Name, 1, 30) AS ProductName,
    ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS INT) LIKE '33%';
GO

Example:

USE AdventureWorks2019;
GO

SELECT SUBSTRING(Name, 1, 30) AS ProductName,
    ListPrice
FROM Production.Product
WHERE CONVERT(INT, ListPrice) LIKE '33%';
GO

Example:

USE AdventureWorks2019;
GO

SELECT CAST(ROUND(SalesYTD / CommissionPCT, 0) AS INT) AS Computed
FROM Sales.SalesPerson
WHERE CommissionPCT != 0;
GO

Example:

SELECT 'The list price is ' + CAST(ListPrice AS VARCHAR(12)) AS ListPrice
FROM dbo.DimProduct
WHERE ListPrice BETWEEN 350.00 AND 400.00;

Example:

SELECT DISTINCT CAST(EnglishProductName AS CHAR(10)) AS Name,
    ListPrice
FROM dbo.DimProduct
WHERE EnglishProductName LIKE 'Long-Sleeve Logo Jersey, M';
GO

Example:

USE AdventureWorks2019;
GO

SELECT p.FirstName,
    p.LastName,
    s.SalesYTD,
    s.BusinessEntityID
FROM Person.Person AS p
INNER JOIN Sales.SalesPerson AS s
    ON p.BusinessEntityID = s.BusinessEntityID
WHERE CAST(CAST(s.SalesYTD AS INT) AS CHAR(20)) LIKE '2%';
GO

Example:

SELECT CONVERT(XML, '<root><child/></root>')

Example:

SELECT CONVERT(XML, '<root>          <child/>         </root>', 1)

Example:

SELECT CAST('<Name><FName>Carol</FName><LName>Elliot</LName></Name>'  AS XML)

Example:

SELECT GETDATE() AS UnconvertedDateTime,
    CAST(GETDATE() AS NVARCHAR(30)) AS UsingCast,
    CONVERT(NVARCHAR(30), GETDATE(), 126) AS UsingConvertTo_ISO8601;
GO

Example:

SELECT '2006-04-25T15:50:59.997' AS UnconvertedText,
    CAST('2006-04-25T15:50:59.997' AS DATETIME) AS UsingCast,
    CONVERT(DATETIME, '2006-04-25T15:50:59.997', 126) AS UsingConvertFrom_ISO8601;
GO

Example:

--Convert the binary value 0x4E616d65 to a character value.
SELECT CONVERT(CHAR(8), 0x4E616d65, 0) AS [Style 0, binary to character];

Example:

SELECT CONVERT(CHAR(8), 0x4E616d65, 1) AS [Style 1, binary to character];

Example:

SELECT CONVERT(CHAR(8), 0x4E616d65, 2) AS [Style 2, binary to character];

Example:

SELECT CONVERT(BINARY(8), 'Name', 0) AS [Style 0, character to binary];

Example:

SELECT CONVERT(BINARY(4), '0x4E616D65', 1) AS [Style 1, character to binary];

Example:

SELECT CONVERT(BINARY(4), '4E616D65', 2) AS [Style 2, character to binary];

Example:

DECLARE @d1 DATE,
    @t1 TIME,
    @dt1 DATETIME;

SET @d1 = GETDATE();
SET @t1 = GETDATE();
SET @dt1 = GETDATE();
SET @d1 = GETDATE();

-- When converting date to datetime the minutes portion becomes zero.
SELECT @d1 AS [DATE],
    CAST(@d1 AS DATETIME) AS [date as datetime];

-- When converting time to datetime the date portion becomes zero
-- which converts to January 1, 1900.
SELECT @t1 AS [TIME],
    CAST(@t1 AS DATETIME) AS [time as datetime];

-- When converting datetime to date or time non-applicable portion is dropped.
SELECT @dt1 AS [DATETIME],
    CAST(@dt1 AS DATE) AS [datetime as date],
    CAST(@dt1 AS TIME) AS [datetime as time];

Example:

DECLARE @d1 DATE, @dt1 DATETIME , @dt2 DATETIME2

SET @d1 = '1492-08-03'
--This is okay; Minimum YYYY for DATE is 0001

SET @dt2 = CAST(@d1 AS DATETIME2)
--This is okay; Minimum YYYY for DATETIME2 IS 0001

SET @dt1 = CAST(@d1 AS DATETIME)
--This will error with (Msg 242) "The conversion of a date data type to a datetime data type resulted in an out-of-range value."
--Minimum YYYY for DATETIME is 1753

Example:

DECLARE @string VARCHAR(10);
SET @string = 1;
SELECT @string + ' is a string.' AS Result

Example:

DECLARE @notastring INT;
SET @notastring = '1';
SELECT @notastring + ' is not a string.' AS Result

Example:

DECLARE @notastring INT;
SET @notastring = '1';
SELECT @notastring + '1'

Example:

SELECT EnglishProductName AS ProductName, ListPrice
FROM dbo.DimProduct
WHERE CAST(ListPrice AS int) LIKE '3%';

Example:

SELECT EnglishProductName AS ProductName, ListPrice
FROM dbo.DimProduct
WHERE CONVERT(INT, ListPrice) LIKE '3%';

Example:

SELECT ProductKey, UnitPrice,UnitPriceDiscountPct,
       CAST(ROUND (UnitPrice*UnitPriceDiscountPct,0) AS int) AS DiscountPrice
FROM dbo.FactResellerSales
WHERE SalesOrderNumber = 'SO47355'
      AND UnitPriceDiscountPct > .02;

Example:

SELECT EnglishProductName AS Name, ListPrice
FROM dbo.DimProduct
WHERE CAST(CAST(ListPrice AS INT) AS CHAR(20)) LIKE '2%';

Example:

SELECT TOP(1)
   SYSDATETIME() AS UnconvertedDateTime,
   CAST(SYSDATETIME() AS NVARCHAR(30)) AS UsingCast,
   CONVERT(NVARCHAR(30), SYSDATETIME(), 126) AS UsingConvertTo_ISO8601
FROM dbo.DimCustomer;

Example:

SELECT TOP(1)
   '2010-07-25T13:50:38.544' AS UnconvertedText,
CAST('2010-07-25T13:50:38.544' AS DATETIME) AS UsingCast,
   CONVERT(DATETIME, '2010-07-25T13:50:38.544', 126) AS UsingConvertFrom_ISO8601
FROM dbo.DimCustomer;

Origin documentation for msConvert

Syntax:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

No description

Example:

SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary);

Example:

SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS;

Example:

DECLARE @myval DECIMAL(5, 2);
SET @myval = 193.57;

SELECT CAST(CAST(@myval AS VARBINARY(20)) AS DECIMAL(10, 5));

-- Or, using CONVERT
SELECT CONVERT(DECIMAL(10, 5), CONVERT(VARBINARY(20), @myval));
GO

Example:

USE AdventureWorks2019;
GO

SELECT p.FirstName,
    p.LastName,
    SUBSTRING(p.Title, 1, 25) AS Title,
    CAST(e.SickLeaveHours AS CHAR(1)) AS [Sick Leave]
FROM HumanResources.Employee e
INNER JOIN Person.Person p
    ON e.BusinessEntityID = p.BusinessEntityID
WHERE NOT e.BusinessEntityID > 5;
GO

Example:

SELECT CAST(10.6496 AS INT) AS trunc1,
       CAST(-10.6496 AS INT) AS trunc2,
       CAST(10.6496 AS NUMERIC) AS round1,
       CAST(-10.6496 AS NUMERIC) AS round2;

Example:

SELECT CAST(10.3496847 AS money);

Example:

DECLARE @x NVARCHAR(10) = 'ab' + NCHAR(0x10000);

SELECT CAST(@x AS NVARCHAR(3));

Example:

USE AdventureWorks2019;
GO

SELECT SUBSTRING(Name, 1, 30) AS ProductName,
    ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS INT) LIKE '33%';
GO

Example:

USE AdventureWorks2019;
GO

SELECT SUBSTRING(Name, 1, 30) AS ProductName,
    ListPrice
FROM Production.Product
WHERE CONVERT(INT, ListPrice) LIKE '33%';
GO

Example:

USE AdventureWorks2019;
GO

SELECT CAST(ROUND(SalesYTD / CommissionPCT, 0) AS INT) AS Computed
FROM Sales.SalesPerson
WHERE CommissionPCT != 0;
GO

Example:

SELECT 'The list price is ' + CAST(ListPrice AS VARCHAR(12)) AS ListPrice
FROM dbo.DimProduct
WHERE ListPrice BETWEEN 350.00 AND 400.00;

Example:

SELECT DISTINCT CAST(EnglishProductName AS CHAR(10)) AS Name,
    ListPrice
FROM dbo.DimProduct
WHERE EnglishProductName LIKE 'Long-Sleeve Logo Jersey, M';
GO

Example:

USE AdventureWorks2019;
GO

SELECT p.FirstName,
    p.LastName,
    s.SalesYTD,
    s.BusinessEntityID
FROM Person.Person AS p
INNER JOIN Sales.SalesPerson AS s
    ON p.BusinessEntityID = s.BusinessEntityID
WHERE CAST(CAST(s.SalesYTD AS INT) AS CHAR(20)) LIKE '2%';
GO

Example:

SELECT CONVERT(XML, '<root><child/></root>')

Example:

SELECT CONVERT(XML, '<root>          <child/>         </root>', 1)

Example:

SELECT CAST('<Name><FName>Carol</FName><LName>Elliot</LName></Name>'  AS XML)

Example:

SELECT GETDATE() AS UnconvertedDateTime,
    CAST(GETDATE() AS NVARCHAR(30)) AS UsingCast,
    CONVERT(NVARCHAR(30), GETDATE(), 126) AS UsingConvertTo_ISO8601;
GO

Example:

SELECT '2006-04-25T15:50:59.997' AS UnconvertedText,
    CAST('2006-04-25T15:50:59.997' AS DATETIME) AS UsingCast,
    CONVERT(DATETIME, '2006-04-25T15:50:59.997', 126) AS UsingConvertFrom_ISO8601;
GO

Example:

--Convert the binary value 0x4E616d65 to a character value.
SELECT CONVERT(CHAR(8), 0x4E616d65, 0) AS [Style 0, binary to character];

Example:

SELECT CONVERT(CHAR(8), 0x4E616d65, 1) AS [Style 1, binary to character];

Example:

SELECT CONVERT(CHAR(8), 0x4E616d65, 2) AS [Style 2, binary to character];

Example:

SELECT CONVERT(BINARY(8), 'Name', 0) AS [Style 0, character to binary];

Example:

SELECT CONVERT(BINARY(4), '0x4E616D65', 1) AS [Style 1, character to binary];

Example:

SELECT CONVERT(BINARY(4), '4E616D65', 2) AS [Style 2, character to binary];

Example:

DECLARE @d1 DATE,
    @t1 TIME,
    @dt1 DATETIME;

SET @d1 = GETDATE();
SET @t1 = GETDATE();
SET @dt1 = GETDATE();
SET @d1 = GETDATE();

-- When converting date to datetime the minutes portion becomes zero.
SELECT @d1 AS [DATE],
    CAST(@d1 AS DATETIME) AS [date as datetime];

-- When converting time to datetime the date portion becomes zero
-- which converts to January 1, 1900.
SELECT @t1 AS [TIME],
    CAST(@t1 AS DATETIME) AS [time as datetime];

-- When converting datetime to date or time non-applicable portion is dropped.
SELECT @dt1 AS [DATETIME],
    CAST(@dt1 AS DATE) AS [datetime as date],
    CAST(@dt1 AS TIME) AS [datetime as time];

Example:

DECLARE @d1 DATE, @dt1 DATETIME , @dt2 DATETIME2

SET @d1 = '1492-08-03'
--This is okay; Minimum YYYY for DATE is 0001

SET @dt2 = CAST(@d1 AS DATETIME2)
--This is okay; Minimum YYYY for DATETIME2 IS 0001

SET @dt1 = CAST(@d1 AS DATETIME)
--This will error with (Msg 242) "The conversion of a date data type to a datetime data type resulted in an out-of-range value."
--Minimum YYYY for DATETIME is 1753

Example:

DECLARE @string VARCHAR(10);
SET @string = 1;
SELECT @string + ' is a string.' AS Result

Example:

DECLARE @notastring INT;
SET @notastring = '1';
SELECT @notastring + ' is not a string.' AS Result

Example:

DECLARE @notastring INT;
SET @notastring = '1';
SELECT @notastring + '1'

Example:

SELECT EnglishProductName AS ProductName, ListPrice
FROM dbo.DimProduct
WHERE CAST(ListPrice AS int) LIKE '3%';

Example:

SELECT EnglishProductName AS ProductName, ListPrice
FROM dbo.DimProduct
WHERE CONVERT(INT, ListPrice) LIKE '3%';

Example:

SELECT ProductKey, UnitPrice,UnitPriceDiscountPct,
       CAST(ROUND (UnitPrice*UnitPriceDiscountPct,0) AS int) AS DiscountPrice
FROM dbo.FactResellerSales
WHERE SalesOrderNumber = 'SO47355'
      AND UnitPriceDiscountPct > .02;

Example:

SELECT EnglishProductName AS Name, ListPrice
FROM dbo.DimProduct
WHERE CAST(CAST(ListPrice AS INT) AS CHAR(20)) LIKE '2%';

Example:

SELECT TOP(1)
   SYSDATETIME() AS UnconvertedDateTime,
   CAST(SYSDATETIME() AS NVARCHAR(30)) AS UsingCast,
   CONVERT(NVARCHAR(30), SYSDATETIME(), 126) AS UsingConvertTo_ISO8601
FROM dbo.DimCustomer;

Example:

SELECT TOP(1)
   '2010-07-25T13:50:38.544' AS UnconvertedText,
CAST('2010-07-25T13:50:38.544' AS DATETIME) AS UsingCast,
   CONVERT(DATETIME, '2010-07-25T13:50:38.544', 126) AS UsingConvertFrom_ISO8601
FROM dbo.DimCustomer;

Origin documentation for msConvert_Style

Syntax:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

No description

Example:

SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary);

Example:

SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS;

Example:

DECLARE @myval DECIMAL(5, 2);
SET @myval = 193.57;

SELECT CAST(CAST(@myval AS VARBINARY(20)) AS DECIMAL(10, 5));

-- Or, using CONVERT
SELECT CONVERT(DECIMAL(10, 5), CONVERT(VARBINARY(20), @myval));
GO

Example:

USE AdventureWorks2019;
GO

SELECT p.FirstName,
    p.LastName,
    SUBSTRING(p.Title, 1, 25) AS Title,
    CAST(e.SickLeaveHours AS CHAR(1)) AS [Sick Leave]
FROM HumanResources.Employee e
INNER JOIN Person.Person p
    ON e.BusinessEntityID = p.BusinessEntityID
WHERE NOT e.BusinessEntityID > 5;
GO

Example:

SELECT CAST(10.6496 AS INT) AS trunc1,
       CAST(-10.6496 AS INT) AS trunc2,
       CAST(10.6496 AS NUMERIC) AS round1,
       CAST(-10.6496 AS NUMERIC) AS round2;

Example:

SELECT CAST(10.3496847 AS money);

Example:

DECLARE @x NVARCHAR(10) = 'ab' + NCHAR(0x10000);

SELECT CAST(@x AS NVARCHAR(3));

Example:

USE AdventureWorks2019;
GO

SELECT SUBSTRING(Name, 1, 30) AS ProductName,
    ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS INT) LIKE '33%';
GO

Example:

USE AdventureWorks2019;
GO

SELECT SUBSTRING(Name, 1, 30) AS ProductName,
    ListPrice
FROM Production.Product
WHERE CONVERT(INT, ListPrice) LIKE '33%';
GO

Example:

USE AdventureWorks2019;
GO

SELECT CAST(ROUND(SalesYTD / CommissionPCT, 0) AS INT) AS Computed
FROM Sales.SalesPerson
WHERE CommissionPCT != 0;
GO

Example:

SELECT 'The list price is ' + CAST(ListPrice AS VARCHAR(12)) AS ListPrice
FROM dbo.DimProduct
WHERE ListPrice BETWEEN 350.00 AND 400.00;

Example:

SELECT DISTINCT CAST(EnglishProductName AS CHAR(10)) AS Name,
    ListPrice
FROM dbo.DimProduct
WHERE EnglishProductName LIKE 'Long-Sleeve Logo Jersey, M';
GO

Example:

USE AdventureWorks2019;
GO

SELECT p.FirstName,
    p.LastName,
    s.SalesYTD,
    s.BusinessEntityID
FROM Person.Person AS p
INNER JOIN Sales.SalesPerson AS s
    ON p.BusinessEntityID = s.BusinessEntityID
WHERE CAST(CAST(s.SalesYTD AS INT) AS CHAR(20)) LIKE '2%';
GO

Example:

SELECT CONVERT(XML, '<root><child/></root>')

Example:

SELECT CONVERT(XML, '<root>          <child/>         </root>', 1)

Example:

SELECT CAST('<Name><FName>Carol</FName><LName>Elliot</LName></Name>'  AS XML)

Example:

SELECT GETDATE() AS UnconvertedDateTime,
    CAST(GETDATE() AS NVARCHAR(30)) AS UsingCast,
    CONVERT(NVARCHAR(30), GETDATE(), 126) AS UsingConvertTo_ISO8601;
GO

Example:

SELECT '2006-04-25T15:50:59.997' AS UnconvertedText,
    CAST('2006-04-25T15:50:59.997' AS DATETIME) AS UsingCast,
    CONVERT(DATETIME, '2006-04-25T15:50:59.997', 126) AS UsingConvertFrom_ISO8601;
GO

Example:

--Convert the binary value 0x4E616d65 to a character value.
SELECT CONVERT(CHAR(8), 0x4E616d65, 0) AS [Style 0, binary to character];

Example:

SELECT CONVERT(CHAR(8), 0x4E616d65, 1) AS [Style 1, binary to character];

Example:

SELECT CONVERT(CHAR(8), 0x4E616d65, 2) AS [Style 2, binary to character];

Example:

SELECT CONVERT(BINARY(8), 'Name', 0) AS [Style 0, character to binary];

Example:

SELECT CONVERT(BINARY(4), '0x4E616D65', 1) AS [Style 1, character to binary];

Example:

SELECT CONVERT(BINARY(4), '4E616D65', 2) AS [Style 2, character to binary];

Example:

DECLARE @d1 DATE,
    @t1 TIME,
    @dt1 DATETIME;

SET @d1 = GETDATE();
SET @t1 = GETDATE();
SET @dt1 = GETDATE();
SET @d1 = GETDATE();

-- When converting date to datetime the minutes portion becomes zero.
SELECT @d1 AS [DATE],
    CAST(@d1 AS DATETIME) AS [date as datetime];

-- When converting time to datetime the date portion becomes zero
-- which converts to January 1, 1900.
SELECT @t1 AS [TIME],
    CAST(@t1 AS DATETIME) AS [time as datetime];

-- When converting datetime to date or time non-applicable portion is dropped.
SELECT @dt1 AS [DATETIME],
    CAST(@dt1 AS DATE) AS [datetime as date],
    CAST(@dt1 AS TIME) AS [datetime as time];

Example:

DECLARE @d1 DATE, @dt1 DATETIME , @dt2 DATETIME2

SET @d1 = '1492-08-03'
--This is okay; Minimum YYYY for DATE is 0001

SET @dt2 = CAST(@d1 AS DATETIME2)
--This is okay; Minimum YYYY for DATETIME2 IS 0001

SET @dt1 = CAST(@d1 AS DATETIME)
--This will error with (Msg 242) "The conversion of a date data type to a datetime data type resulted in an out-of-range value."
--Minimum YYYY for DATETIME is 1753

Example:

DECLARE @string VARCHAR(10);
SET @string = 1;
SELECT @string + ' is a string.' AS Result

Example:

DECLARE @notastring INT;
SET @notastring = '1';
SELECT @notastring + ' is not a string.' AS Result

Example:

DECLARE @notastring INT;
SET @notastring = '1';
SELECT @notastring + '1'

Example:

SELECT EnglishProductName AS ProductName, ListPrice
FROM dbo.DimProduct
WHERE CAST(ListPrice AS int) LIKE '3%';

Example:

SELECT EnglishProductName AS ProductName, ListPrice
FROM dbo.DimProduct
WHERE CONVERT(INT, ListPrice) LIKE '3%';

Example:

SELECT ProductKey, UnitPrice,UnitPriceDiscountPct,
       CAST(ROUND (UnitPrice*UnitPriceDiscountPct,0) AS int) AS DiscountPrice
FROM dbo.FactResellerSales
WHERE SalesOrderNumber = 'SO47355'
      AND UnitPriceDiscountPct > .02;

Example:

SELECT EnglishProductName AS Name, ListPrice
FROM dbo.DimProduct
WHERE CAST(CAST(ListPrice AS INT) AS CHAR(20)) LIKE '2%';

Example:

SELECT TOP(1)
   SYSDATETIME() AS UnconvertedDateTime,
   CAST(SYSDATETIME() AS NVARCHAR(30)) AS UsingCast,
   CONVERT(NVARCHAR(30), SYSDATETIME(), 126) AS UsingConvertTo_ISO8601
FROM dbo.DimCustomer;

Example:

SELECT TOP(1)
   '2010-07-25T13:50:38.544' AS UnconvertedText,
CAST('2010-07-25T13:50:38.544' AS DATETIME) AS UsingCast,
   CONVERT(DATETIME, '2010-07-25T13:50:38.544', 126) AS UsingConvertFrom_ISO8601
FROM dbo.DimCustomer;

Origin documentation for msConvert_Length

Syntax:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

No description

Example:

SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary);

Example:

SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS;

Example:

DECLARE @myval DECIMAL(5, 2);
SET @myval = 193.57;

SELECT CAST(CAST(@myval AS VARBINARY(20)) AS DECIMAL(10, 5));

-- Or, using CONVERT
SELECT CONVERT(DECIMAL(10, 5), CONVERT(VARBINARY(20), @myval));
GO

Example:

USE AdventureWorks2019;
GO

SELECT p.FirstName,
    p.LastName,
    SUBSTRING(p.Title, 1, 25) AS Title,
    CAST(e.SickLeaveHours AS CHAR(1)) AS [Sick Leave]
FROM HumanResources.Employee e
INNER JOIN Person.Person p
    ON e.BusinessEntityID = p.BusinessEntityID
WHERE NOT e.BusinessEntityID > 5;
GO

Example:

SELECT CAST(10.6496 AS INT) AS trunc1,
       CAST(-10.6496 AS INT) AS trunc2,
       CAST(10.6496 AS NUMERIC) AS round1,
       CAST(-10.6496 AS NUMERIC) AS round2;

Example:

SELECT CAST(10.3496847 AS money);

Example:

DECLARE @x NVARCHAR(10) = 'ab' + NCHAR(0x10000);

SELECT CAST(@x AS NVARCHAR(3));

Example:

USE AdventureWorks2019;
GO

SELECT SUBSTRING(Name, 1, 30) AS ProductName,
    ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS INT) LIKE '33%';
GO

Example:

USE AdventureWorks2019;
GO

SELECT SUBSTRING(Name, 1, 30) AS ProductName,
    ListPrice
FROM Production.Product
WHERE CONVERT(INT, ListPrice) LIKE '33%';
GO

Example:

USE AdventureWorks2019;
GO

SELECT CAST(ROUND(SalesYTD / CommissionPCT, 0) AS INT) AS Computed
FROM Sales.SalesPerson
WHERE CommissionPCT != 0;
GO

Example:

SELECT 'The list price is ' + CAST(ListPrice AS VARCHAR(12)) AS ListPrice
FROM dbo.DimProduct
WHERE ListPrice BETWEEN 350.00 AND 400.00;

Example:

SELECT DISTINCT CAST(EnglishProductName AS CHAR(10)) AS Name,
    ListPrice
FROM dbo.DimProduct
WHERE EnglishProductName LIKE 'Long-Sleeve Logo Jersey, M';
GO

Example:

USE AdventureWorks2019;
GO

SELECT p.FirstName,
    p.LastName,
    s.SalesYTD,
    s.BusinessEntityID
FROM Person.Person AS p
INNER JOIN Sales.SalesPerson AS s
    ON p.BusinessEntityID = s.BusinessEntityID
WHERE CAST(CAST(s.SalesYTD AS INT) AS CHAR(20)) LIKE '2%';
GO

Example:

SELECT CONVERT(XML, '<root><child/></root>')

Example:

SELECT CONVERT(XML, '<root>          <child/>         </root>', 1)

Example:

SELECT CAST('<Name><FName>Carol</FName><LName>Elliot</LName></Name>'  AS XML)

Example:

SELECT GETDATE() AS UnconvertedDateTime,
    CAST(GETDATE() AS NVARCHAR(30)) AS UsingCast,
    CONVERT(NVARCHAR(30), GETDATE(), 126) AS UsingConvertTo_ISO8601;
GO

Example:

SELECT '2006-04-25T15:50:59.997' AS UnconvertedText,
    CAST('2006-04-25T15:50:59.997' AS DATETIME) AS UsingCast,
    CONVERT(DATETIME, '2006-04-25T15:50:59.997', 126) AS UsingConvertFrom_ISO8601;
GO

Example:

--Convert the binary value 0x4E616d65 to a character value.
SELECT CONVERT(CHAR(8), 0x4E616d65, 0) AS [Style 0, binary to character];

Example:

SELECT CONVERT(CHAR(8), 0x4E616d65, 1) AS [Style 1, binary to character];

Example:

SELECT CONVERT(CHAR(8), 0x4E616d65, 2) AS [Style 2, binary to character];

Example:

SELECT CONVERT(BINARY(8), 'Name', 0) AS [Style 0, character to binary];

Example:

SELECT CONVERT(BINARY(4), '0x4E616D65', 1) AS [Style 1, character to binary];

Example:

SELECT CONVERT(BINARY(4), '4E616D65', 2) AS [Style 2, character to binary];

Example:

DECLARE @d1 DATE,
    @t1 TIME,
    @dt1 DATETIME;

SET @d1 = GETDATE();
SET @t1 = GETDATE();
SET @dt1 = GETDATE();
SET @d1 = GETDATE();

-- When converting date to datetime the minutes portion becomes zero.
SELECT @d1 AS [DATE],
    CAST(@d1 AS DATETIME) AS [date as datetime];

-- When converting time to datetime the date portion becomes zero
-- which converts to January 1, 1900.
SELECT @t1 AS [TIME],
    CAST(@t1 AS DATETIME) AS [time as datetime];

-- When converting datetime to date or time non-applicable portion is dropped.
SELECT @dt1 AS [DATETIME],
    CAST(@dt1 AS DATE) AS [datetime as date],
    CAST(@dt1 AS TIME) AS [datetime as time];

Example:

DECLARE @d1 DATE, @dt1 DATETIME , @dt2 DATETIME2

SET @d1 = '1492-08-03'
--This is okay; Minimum YYYY for DATE is 0001

SET @dt2 = CAST(@d1 AS DATETIME2)
--This is okay; Minimum YYYY for DATETIME2 IS 0001

SET @dt1 = CAST(@d1 AS DATETIME)
--This will error with (Msg 242) "The conversion of a date data type to a datetime data type resulted in an out-of-range value."
--Minimum YYYY for DATETIME is 1753

Example:

DECLARE @string VARCHAR(10);
SET @string = 1;
SELECT @string + ' is a string.' AS Result

Example:

DECLARE @notastring INT;
SET @notastring = '1';
SELECT @notastring + ' is not a string.' AS Result

Example:

DECLARE @notastring INT;
SET @notastring = '1';
SELECT @notastring + '1'

Example:

SELECT EnglishProductName AS ProductName, ListPrice
FROM dbo.DimProduct
WHERE CAST(ListPrice AS int) LIKE '3%';

Example:

SELECT EnglishProductName AS ProductName, ListPrice
FROM dbo.DimProduct
WHERE CONVERT(INT, ListPrice) LIKE '3%';

Example:

SELECT ProductKey, UnitPrice,UnitPriceDiscountPct,
       CAST(ROUND (UnitPrice*UnitPriceDiscountPct,0) AS int) AS DiscountPrice
FROM dbo.FactResellerSales
WHERE SalesOrderNumber = 'SO47355'
      AND UnitPriceDiscountPct > .02;

Example:

SELECT EnglishProductName AS Name, ListPrice
FROM dbo.DimProduct
WHERE CAST(CAST(ListPrice AS INT) AS CHAR(20)) LIKE '2%';

Example:

SELECT TOP(1)
   SYSDATETIME() AS UnconvertedDateTime,
   CAST(SYSDATETIME() AS NVARCHAR(30)) AS UsingCast,
   CONVERT(NVARCHAR(30), SYSDATETIME(), 126) AS UsingConvertTo_ISO8601
FROM dbo.DimCustomer;

Example:

SELECT TOP(1)
   '2010-07-25T13:50:38.544' AS UnconvertedText,
CAST('2010-07-25T13:50:38.544' AS DATETIME) AS UsingCast,
   CONVERT(DATETIME, '2010-07-25T13:50:38.544', 126) AS UsingConvertFrom_ISO8601
FROM dbo.DimCustomer;

Origin documentation for msConvert_Length_Style

Syntax:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

No description

Example:

SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary);

Example:

SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS;

Example:

DECLARE @myval DECIMAL(5, 2);
SET @myval = 193.57;

SELECT CAST(CAST(@myval AS VARBINARY(20)) AS DECIMAL(10, 5));

-- Or, using CONVERT
SELECT CONVERT(DECIMAL(10, 5), CONVERT(VARBINARY(20), @myval));
GO

Example:

USE AdventureWorks2019;
GO

SELECT p.FirstName,
    p.LastName,
    SUBSTRING(p.Title, 1, 25) AS Title,
    CAST(e.SickLeaveHours AS CHAR(1)) AS [Sick Leave]
FROM HumanResources.Employee e
INNER JOIN Person.Person p
    ON e.BusinessEntityID = p.BusinessEntityID
WHERE NOT e.BusinessEntityID > 5;
GO

Example:

SELECT CAST(10.6496 AS INT) AS trunc1,
       CAST(-10.6496 AS INT) AS trunc2,
       CAST(10.6496 AS NUMERIC) AS round1,
       CAST(-10.6496 AS NUMERIC) AS round2;

Example:

SELECT CAST(10.3496847 AS money);

Example:

DECLARE @x NVARCHAR(10) = 'ab' + NCHAR(0x10000);

SELECT CAST(@x AS NVARCHAR(3));

Example:

USE AdventureWorks2019;
GO

SELECT SUBSTRING(Name, 1, 30) AS ProductName,
    ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS INT) LIKE '33%';
GO

Example:

USE AdventureWorks2019;
GO

SELECT SUBSTRING(Name, 1, 30) AS ProductName,
    ListPrice
FROM Production.Product
WHERE CONVERT(INT, ListPrice) LIKE '33%';
GO

Example:

USE AdventureWorks2019;
GO

SELECT CAST(ROUND(SalesYTD / CommissionPCT, 0) AS INT) AS Computed
FROM Sales.SalesPerson
WHERE CommissionPCT != 0;
GO

Example:

SELECT 'The list price is ' + CAST(ListPrice AS VARCHAR(12)) AS ListPrice
FROM dbo.DimProduct
WHERE ListPrice BETWEEN 350.00 AND 400.00;

Example:

SELECT DISTINCT CAST(EnglishProductName AS CHAR(10)) AS Name,
    ListPrice
FROM dbo.DimProduct
WHERE EnglishProductName LIKE 'Long-Sleeve Logo Jersey, M';
GO

Example:

USE AdventureWorks2019;
GO

SELECT p.FirstName,
    p.LastName,
    s.SalesYTD,
    s.BusinessEntityID
FROM Person.Person AS p
INNER JOIN Sales.SalesPerson AS s
    ON p.BusinessEntityID = s.BusinessEntityID
WHERE CAST(CAST(s.SalesYTD AS INT) AS CHAR(20)) LIKE '2%';
GO

Example:

SELECT CONVERT(XML, '<root><child/></root>')

Example:

SELECT CONVERT(XML, '<root>          <child/>         </root>', 1)

Example:

SELECT CAST('<Name><FName>Carol</FName><LName>Elliot</LName></Name>'  AS XML)

Example:

SELECT GETDATE() AS UnconvertedDateTime,
    CAST(GETDATE() AS NVARCHAR(30)) AS UsingCast,
    CONVERT(NVARCHAR(30), GETDATE(), 126) AS UsingConvertTo_ISO8601;
GO

Example:

SELECT '2006-04-25T15:50:59.997' AS UnconvertedText,
    CAST('2006-04-25T15:50:59.997' AS DATETIME) AS UsingCast,
    CONVERT(DATETIME, '2006-04-25T15:50:59.997', 126) AS UsingConvertFrom_ISO8601;
GO

Example:

--Convert the binary value 0x4E616d65 to a character value.
SELECT CONVERT(CHAR(8), 0x4E616d65, 0) AS [Style 0, binary to character];

Example:

SELECT CONVERT(CHAR(8), 0x4E616d65, 1) AS [Style 1, binary to character];

Example:

SELECT CONVERT(CHAR(8), 0x4E616d65, 2) AS [Style 2, binary to character];

Example:

SELECT CONVERT(BINARY(8), 'Name', 0) AS [Style 0, character to binary];

Example:

SELECT CONVERT(BINARY(4), '0x4E616D65', 1) AS [Style 1, character to binary];

Example:

SELECT CONVERT(BINARY(4), '4E616D65', 2) AS [Style 2, character to binary];

Example:

DECLARE @d1 DATE,
    @t1 TIME,
    @dt1 DATETIME;

SET @d1 = GETDATE();
SET @t1 = GETDATE();
SET @dt1 = GETDATE();
SET @d1 = GETDATE();

-- When converting date to datetime the minutes portion becomes zero.
SELECT @d1 AS [DATE],
    CAST(@d1 AS DATETIME) AS [date as datetime];

-- When converting time to datetime the date portion becomes zero
-- which converts to January 1, 1900.
SELECT @t1 AS [TIME],
    CAST(@t1 AS DATETIME) AS [time as datetime];

-- When converting datetime to date or time non-applicable portion is dropped.
SELECT @dt1 AS [DATETIME],
    CAST(@dt1 AS DATE) AS [datetime as date],
    CAST(@dt1 AS TIME) AS [datetime as time];

Example:

DECLARE @d1 DATE, @dt1 DATETIME , @dt2 DATETIME2

SET @d1 = '1492-08-03'
--This is okay; Minimum YYYY for DATE is 0001

SET @dt2 = CAST(@d1 AS DATETIME2)
--This is okay; Minimum YYYY for DATETIME2 IS 0001

SET @dt1 = CAST(@d1 AS DATETIME)
--This will error with (Msg 242) "The conversion of a date data type to a datetime data type resulted in an out-of-range value."
--Minimum YYYY for DATETIME is 1753

Example:

DECLARE @string VARCHAR(10);
SET @string = 1;
SELECT @string + ' is a string.' AS Result

Example:

DECLARE @notastring INT;
SET @notastring = '1';
SELECT @notastring + ' is not a string.' AS Result

Example:

DECLARE @notastring INT;
SET @notastring = '1';
SELECT @notastring + '1'

Example:

SELECT EnglishProductName AS ProductName, ListPrice
FROM dbo.DimProduct
WHERE CAST(ListPrice AS int) LIKE '3%';

Example:

SELECT EnglishProductName AS ProductName, ListPrice
FROM dbo.DimProduct
WHERE CONVERT(INT, ListPrice) LIKE '3%';

Example:

SELECT ProductKey, UnitPrice,UnitPriceDiscountPct,
       CAST(ROUND (UnitPrice*UnitPriceDiscountPct,0) AS int) AS DiscountPrice
FROM dbo.FactResellerSales
WHERE SalesOrderNumber = 'SO47355'
      AND UnitPriceDiscountPct > .02;

Example:

SELECT EnglishProductName AS Name, ListPrice
FROM dbo.DimProduct
WHERE CAST(CAST(ListPrice AS INT) AS CHAR(20)) LIKE '2%';

Example:

SELECT TOP(1)
   SYSDATETIME() AS UnconvertedDateTime,
   CAST(SYSDATETIME() AS NVARCHAR(30)) AS UsingCast,
   CONVERT(NVARCHAR(30), SYSDATETIME(), 126) AS UsingConvertTo_ISO8601
FROM dbo.DimCustomer;

Example:

SELECT TOP(1)
   '2010-07-25T13:50:38.544' AS UnconvertedText,
CAST('2010-07-25T13:50:38.544' AS DATETIME) AS UsingCast,
   CONVERT(DATETIME, '2010-07-25T13:50:38.544', 126) AS UsingConvertFrom_ISO8601
FROM dbo.DimCustomer;

Origin documentation for msSign

Syntax:

SIGN ( numeric_expression )

Returns the positive (+1), zero (0), or negative (-1) sign of the specified expression.

Example:

DECLARE @value REAL
SET @value = -1
WHILE @value < 2
   BEGIN
      SELECT SIGN(@value)
      SET NOCOUNT ON
      SELECT @value = @value + 1
      SET NOCOUNT OFF
   END
SET NOCOUNT OFF
GO

Example:

SELECT SIGN(-125), SIGN(0), SIGN(564);

Origin documentation for msAvg

Syntax:

AVG ( [ ALL | DISTINCT ] expression )
   [ OVER ( [ partition_by_clause ] order_by_clause ) ]

This function returns the average of the values in a group. It ignores null values.

Example:

SELECT AVG(VacationHours)AS 'Average vacation hours',
    SUM(SickLeaveHours) AS 'Total sick leave hours'
FROM HumanResources.Employee
WHERE JobTitle LIKE 'Vice President%';

Example:

SELECT TerritoryID, AVG(Bonus)as 'Average bonus', SUM(SalesYTD) as 'YTD sales'
FROM Sales.SalesPerson
GROUP BY TerritoryID;
GO

Example:

SELECT AVG(DISTINCT ListPrice)
FROM Production.Product;

Example:

SELECT AVG(ListPrice)
FROM Production.Product;

Example:

SELECT BusinessEntityID, TerritoryID
   ,DATEPART(yy,ModifiedDate) AS SalesYear
   ,CONVERT(VARCHAR(20),SalesYTD,1) AS  SalesYTD
   ,CONVERT(VARCHAR(20),AVG(SalesYTD) OVER (PARTITION BY TerritoryID
                                            ORDER BY DATEPART(yy,ModifiedDate)
                                           ),1) AS MovingAvg
   ,CONVERT(VARCHAR(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID
                                            ORDER BY DATEPART(yy,ModifiedDate)
                                            ),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5
ORDER BY TerritoryID,SalesYear;

Example:

SELECT BusinessEntityID, TerritoryID
   ,DATEPART(yy,ModifiedDate) AS SalesYear
   ,CONVERT(VARCHAR(20),SalesYTD,1) AS  SalesYTD
   ,CONVERT(VARCHAR(20),AVG(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate)
                                            ),1) AS MovingAvg
   ,CONVERT(VARCHAR(20),SUM(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate)
                                            ),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5
ORDER BY SalesYear;

Origin documentation for msAvg_OVER

Syntax:

AVG ( [ ALL | DISTINCT ] expression )
   [ OVER ( [ partition_by_clause ] order_by_clause ) ]

This function returns the average of the values in a group. It ignores null values.

Example:

SELECT AVG(VacationHours)AS 'Average vacation hours',
    SUM(SickLeaveHours) AS 'Total sick leave hours'
FROM HumanResources.Employee
WHERE JobTitle LIKE 'Vice President%';

Example:

SELECT TerritoryID, AVG(Bonus)as 'Average bonus', SUM(SalesYTD) as 'YTD sales'
FROM Sales.SalesPerson
GROUP BY TerritoryID;
GO

Example:

SELECT AVG(DISTINCT ListPrice)
FROM Production.Product;

Example:

SELECT AVG(ListPrice)
FROM Production.Product;

Example:

SELECT BusinessEntityID, TerritoryID
   ,DATEPART(yy,ModifiedDate) AS SalesYear
   ,CONVERT(VARCHAR(20),SalesYTD,1) AS  SalesYTD
   ,CONVERT(VARCHAR(20),AVG(SalesYTD) OVER (PARTITION BY TerritoryID
                                            ORDER BY DATEPART(yy,ModifiedDate)
                                           ),1) AS MovingAvg
   ,CONVERT(VARCHAR(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID
                                            ORDER BY DATEPART(yy,ModifiedDate)
                                            ),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5
ORDER BY TerritoryID,SalesYear;

Example:

SELECT BusinessEntityID, TerritoryID
   ,DATEPART(yy,ModifiedDate) AS SalesYear
   ,CONVERT(VARCHAR(20),SalesYTD,1) AS  SalesYTD
   ,CONVERT(VARCHAR(20),AVG(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate)
                                            ),1) AS MovingAvg
   ,CONVERT(VARCHAR(20),SUM(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate)
                                            ),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5
ORDER BY SalesYear;

Origin documentation for msIsNull

Syntax:

ISNULL ( check_expression , replacement_value )

Replaces NULL with the specified replacement value.

Example:

USE AdventureWorks2012;
GO
SELECT AVG(ISNULL(Weight, 50))
FROM Production.Product;
GO

Example:

USE AdventureWorks2012;
GO
SELECT Description, DiscountPct, MinQty, ISNULL(MaxQty, 0.00) AS 'Max Quantity'
FROM Sales.SpecialOffer;
GO

Example:

USE AdventureWorks2012;
GO
SELECT Name, Weight
FROM Production.Product
WHERE Weight IS NULL;
GO

Example:

-- Uses AdventureWorks

SELECT AVG(ISNULL(Weight, 50))
FROM dbo.DimProduct;

Example:

-- Uses AdventureWorks

SELECT ResellerName,
       ISNULL(MinPaymentAmount,0) AS MinimumPayment
FROM dbo.DimReseller
ORDER BY ResellerName;

Example:

-- Uses AdventureWorks

SELECT EnglishProductName, Weight
FROM dbo.DimProduct
WHERE Weight IS NULL;

Origin documentation for msConcat

Syntax:

CONCAT ( string_value1, string_value2 [, string_valueN ] )

This function returns a string resulting from the concatenation, or joining, of two or more string values in an end-to-end manner. (To add a separating value during concatenation, see CONCAT_WS .)

Example:

SELECT CONCAT ( 'Happy ', 'Birthday ', 11, '/', '25' ) AS Result;

Example:

CREATE TABLE #temp (
    emp_name NVARCHAR(200) NOT NULL,
    emp_middlename NVARCHAR(200) NULL,
    emp_lastname NVARCHAR(200) NOT NULL
);
INSERT INTO #temp VALUES( 'Name', NULL, 'Lastname' );
SELECT CONCAT( emp_name, emp_middlename, emp_lastname ) AS Result
FROM #temp;

Origin documentation for msVar

Syntax:

-- Aggregate Function Syntax
VAR ( [ ALL | DISTINCT ] expression )

-- Analytic Function Syntax
VAR ([ ALL ] expression) OVER ( [ partition_by_clause ] order_by_clause)

Returns the statistical variance of all values in the specified expression. May be followed by the OVER clause .

Example:

SELECT VAR(Bonus)
FROM Sales.SalesPerson;
GO

Example:

-- Uses AdventureWorks

SELECT VAR(DISTINCT SalesAmountQuota)AS Distinct_Values, VAR(SalesAmountQuota) AS All_Values
FROM dbo.FactSalesQuota;

Example:

-- Uses AdventureWorks

SELECT CalendarYear AS Year, CalendarQuarter AS Quarter, SalesAmountQuota AS SalesQuota,
       VAR(SalesAmountQuota) OVER (ORDER BY CalendarYear, CalendarQuarter) AS Variance
FROM dbo.FactSalesQuota
WHERE EmployeeKey = 272 AND CalendarYear = 2002
ORDER BY CalendarQuarter;

Origin documentation for msMax

Syntax:

-- Aggregation Function Syntax
MAX( [ ALL | DISTINCT ] expression )

-- Analytic Function Syntax
MAX ([ ALL ] expression) OVER ( <partition_by_clause> [ <order_by_clause> ] )

Returns the maximum value in the expression.

Example:

SELECT MAX(TaxRate)
FROM Sales.SalesTaxRate;
GO

Example:

SELECT DISTINCT Name
       , MIN(Rate) OVER (PARTITION BY edh.DepartmentID) AS MinSalary
       , MAX(Rate) OVER (PARTITION BY edh.DepartmentID) AS MaxSalary
       , AVG(Rate) OVER (PARTITION BY edh.DepartmentID) AS AvgSalary
       ,COUNT(edh.BusinessEntityID) OVER (PARTITION BY edh.DepartmentID) AS EmployeesPerDept
FROM HumanResources.EmployeePayHistory AS eph
JOIN HumanResources.EmployeeDepartmentHistory AS edh
     ON eph.BusinessEntityID = edh.BusinessEntityID
JOIN HumanResources.Department AS d
 ON d.DepartmentID = edh.DepartmentID
WHERE edh.EndDate IS NULL
ORDER BY Name;

Example:

SELECT MAX(name) FROM sys.databases WHERE database_id < 5;

Origin documentation for msAbs

Syntax:

ABS ( numeric_expression )

A mathematical function that returns the absolute (positive) value of the specified numeric expression. ( ABS changes negative values to positive values. ABS has no effect on zero or positive values.)

Example:

SELECT ABS(-1.0), ABS(0.0), ABS(1.0);

Example:

---- ---- ----
1.0  .0   1.0

Example:

DECLARE @i INT;
SET @i = -2147483648;
SELECT ABS(@i);
GO

Origin documentation for msMin

Syntax:

-- Aggregation Function Syntax
MIN ( [ ALL | DISTINCT ] expression )

-- Analytic Function Syntax
MIN ( [ ALL ] expression ) OVER ( [ <partition_by_clause> ] [ <order_by_clause> ] )

Returns the minimum value in the expression. May be followed by the OVER clause .

Example:

SELECT MIN(TaxRate)
FROM Sales.SalesTaxRate;
GO

Example:

SELECT DISTINCT Name
       , MIN(Rate) OVER (PARTITION BY edh.DepartmentID) AS MinSalary
       , MAX(Rate) OVER (PARTITION BY edh.DepartmentID) AS MaxSalary
       , AVG(Rate) OVER (PARTITION BY edh.DepartmentID) AS AvgSalary
       ,COUNT(edh.BusinessEntityID) OVER (PARTITION BY edh.DepartmentID) AS EmployeesPerDept
FROM HumanResources.EmployeePayHistory AS eph
JOIN HumanResources.EmployeeDepartmentHistory AS edh
     ON eph.BusinessEntityID = edh.BusinessEntityID
JOIN HumanResources.Department AS d
 ON d.DepartmentID = edh.DepartmentID
WHERE edh.EndDate IS NULL
ORDER BY Name;

Example:

-- Uses AdventureWorks

SELECT DISTINCT MIN(UnitPrice)
FROM dbo.FactResellerSales
WHERE SalesOrderNumber IN (N'SO43659', N'SO43660', N'SO43664');

Example:

-- Uses AdventureWorks

SELECT DISTINCT MIN(UnitPrice) OVER(PARTITION BY SalesOrderNumber) AS LeastExpensiveProduct,
       SalesOrderNumber
FROM dbo.FactResellerSales
WHERE SalesOrderNumber IN (N'SO43659', N'SO43660', N'SO43664')
ORDER BY SalesOrderNumber;

Origin documentation for msSum

Syntax:

-- Aggregate Function Syntax
SUM ( [ ALL | DISTINCT ] expression )

-- Analytic Function Syntax
SUM ([ ALL ] expression) OVER ( [ partition_by_clause ] order_by_clause)

Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored.

Example:

SELECT Color, SUM(ListPrice), SUM(StandardCost)
FROM Production.Product
WHERE Color IS NOT NULL
    AND ListPrice != 0.00
    AND Name LIKE 'Mountain%'
GROUP BY Color
ORDER BY Color;
GO

Example:

SELECT BusinessEntityID, TerritoryID
   ,DATEPART(yy,ModifiedDate) AS SalesYear
   ,CONVERT(VARCHAR(20),SalesYTD,1) AS  SalesYTD
   ,CONVERT(VARCHAR(20),AVG(SalesYTD) OVER (PARTITION BY TerritoryID
                                            ORDER BY DATEPART(yy,ModifiedDate)
                                           ),1) AS MovingAvg
   ,CONVERT(VARCHAR(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID
                                            ORDER BY DATEPART(yy,ModifiedDate)
                                            ),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5
ORDER BY TerritoryID,SalesYear;

Example:

SELECT BusinessEntityID, TerritoryID
   ,DATEPART(yy,ModifiedDate) AS SalesYear
   ,CONVERT(VARCHAR(20),SalesYTD,1) AS  SalesYTD
   ,CONVERT(VARCHAR(20),AVG(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate)
                                            ),1) AS MovingAvg
   ,CONVERT(VARCHAR(20),SUM(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate)
                                            ),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5
ORDER BY SalesYear;

Example:

-- Uses AdventureWorks

SELECT ProductKey, SUM(SalesAmount) AS TotalPerProduct
FROM dbo.FactInternetSales
WHERE OrderDateKey >= '20030101'
      AND OrderDateKey < '20040101'
GROUP BY ProductKey
ORDER BY ProductKey;

Example:

-- Uses AdventureWorks

SELECT Color, SUM(ListPrice)AS TotalList,
       SUM(StandardCost) AS TotalCost
FROM dbo.DimProduct
GROUP BY Color
ORDER BY Color;

Origin documentation for formatMessage

Syntax:

FORMATMESSAGE ( { msg_number  | ' msg_string ' | @msg_variable} , [ param_value [ ,...n ] ] )

Constructs a message from an existing message in sys.messages or from a provided string. The functionality of FORMATMESSAGE resembles that of the RAISERROR statement. However, RAISERROR prints the message immediately, while FORMATMESSAGE returns the formatted message for further processing.

Example:

SELECT text FROM sys.messages WHERE message_id = 20009 AND language_id = 1033;
DECLARE @var1 VARCHAR(200);
SELECT @var1 = FORMATMESSAGE(20009, 'First Variable', 'Second Variable');
SELECT @var1;

Example:

SELECT FORMATMESSAGE('This is the %s and this is the %s.', 'first variable', 'second variable') AS Result;

Example:

SELECT FORMATMESSAGE('Signed int %i, %d %i, %d, %+i, %+d, %+i, %+d', 5, -5, 50, -50, -11, -11, 11, 11);
SELECT FORMATMESSAGE('Signed int with up to 3 leading zeros %03i', 5);
SELECT FORMATMESSAGE('Signed int with up to 20 leading zeros %020i', 5);
SELECT FORMATMESSAGE('Signed int with leading zero 0 %020i', -55);
SELECT FORMATMESSAGE('Bigint %I64d', 3000000000);
SELECT FORMATMESSAGE('Unsigned int %u, %u', 50, -50);
SELECT FORMATMESSAGE('Unsigned octal %o, %o', 50, -50);
SELECT FORMATMESSAGE('Unsigned hexadecimal %x, %X, %X, %X, %x', 11, 11, -11, 50, -50);
SELECT FORMATMESSAGE('Unsigned octal with prefix: %#o, %#o', 50, -50);
SELECT FORMATMESSAGE('Unsigned hexadecimal with prefix: %#x, %#X, %#X, %X, %x', 11, 11, -11, 50, -50);
SELECT FORMATMESSAGE('Hello %s!', 'TEST');
SELECT FORMATMESSAGE('Hello %20s!', 'TEST');
SELECT FORMATMESSAGE('Hello %-20s!', 'TEST');

Origin documentation for formatMessage_ParamValue

Syntax:

FORMATMESSAGE ( { msg_number  | ' msg_string ' | @msg_variable} , [ param_value [ ,...n ] ] )

Constructs a message from an existing message in sys.messages or from a provided string. The functionality of FORMATMESSAGE resembles that of the RAISERROR statement. However, RAISERROR prints the message immediately, while FORMATMESSAGE returns the formatted message for further processing.

Example:

SELECT text FROM sys.messages WHERE message_id = 20009 AND language_id = 1033;
DECLARE @var1 VARCHAR(200);
SELECT @var1 = FORMATMESSAGE(20009, 'First Variable', 'Second Variable');
SELECT @var1;

Example:

SELECT FORMATMESSAGE('This is the %s and this is the %s.', 'first variable', 'second variable') AS Result;

Example:

SELECT FORMATMESSAGE('Signed int %i, %d %i, %d, %+i, %+d, %+i, %+d', 5, -5, 50, -50, -11, -11, 11, 11);
SELECT FORMATMESSAGE('Signed int with up to 3 leading zeros %03i', 5);
SELECT FORMATMESSAGE('Signed int with up to 20 leading zeros %020i', 5);
SELECT FORMATMESSAGE('Signed int with leading zero 0 %020i', -55);
SELECT FORMATMESSAGE('Bigint %I64d', 3000000000);
SELECT FORMATMESSAGE('Unsigned int %u, %u', 50, -50);
SELECT FORMATMESSAGE('Unsigned octal %o, %o', 50, -50);
SELECT FORMATMESSAGE('Unsigned hexadecimal %x, %X, %X, %X, %x', 11, 11, -11, 50, -50);
SELECT FORMATMESSAGE('Unsigned octal with prefix: %#o, %#o', 50, -50);
SELECT FORMATMESSAGE('Unsigned hexadecimal with prefix: %#x, %#X, %#X, %X, %x', 11, 11, -11, 50, -50);
SELECT FORMATMESSAGE('Hello %s!', 'TEST');
SELECT FORMATMESSAGE('Hello %20s!', 'TEST');
SELECT FORMATMESSAGE('Hello %-20s!', 'TEST');

Origin documentation for decryptByPassPhrase

Syntax:

DecryptByPassPhrase ( { 'passphrase' | @passphrase }
    , { 'ciphertext' | @ciphertext }
  [ , { add_authenticator | @add_authenticator }
    , { authenticator | @authenticator } ] )

This function decrypts data originally encrypted with a passphrase.

Example:

USE AdventureWorks2012;
-- Get the passphrase from the user.
DECLARE @PassphraseEnteredByUser NVARCHAR(128);
SET @PassphraseEnteredByUser
= 'A little learning is a dangerous thing!';

-- Decrypt the encrypted record.
SELECT CardNumber, CardNumber_EncryptedbyPassphrase
    AS 'Encrypted card number', CONVERT(varchar,
    DecryptByPassphrase(@PassphraseEnteredByUser, CardNumber_EncryptedbyPassphrase, 1
    , CONVERT(varbinary, CreditCardID)))
    AS 'Decrypted card number' FROM Sales.CreditCard
    WHERE CreditCardID = '3681';
GO

Origin documentation for decryptByPassPhrase_AddAuthenticator_Authenticator

Syntax:

DecryptByPassPhrase ( { 'passphrase' | @passphrase }
    , { 'ciphertext' | @ciphertext }
  [ , { add_authenticator | @add_authenticator }
    , { authenticator | @authenticator } ] )

This function decrypts data originally encrypted with a passphrase.

Example:

USE AdventureWorks2012;
-- Get the passphrase from the user.
DECLARE @PassphraseEnteredByUser NVARCHAR(128);
SET @PassphraseEnteredByUser
= 'A little learning is a dangerous thing!';

-- Decrypt the encrypted record.
SELECT CardNumber, CardNumber_EncryptedbyPassphrase
    AS 'Encrypted card number', CONVERT(varchar,
    DecryptByPassphrase(@PassphraseEnteredByUser, CardNumber_EncryptedbyPassphrase, 1
    , CONVERT(varbinary, CreditCardID)))
    AS 'Decrypted card number' FROM Sales.CreditCard
    WHERE CreditCardID = '3681';
GO

Origin documentation for cryptGenRandom

Syntax:

CRYPT_GEN_RANDOM ( length [ , seed ] )

This function returns a cryptographic, randomly-generated number, generated by the Crypto API (CAPI). CRYPT_GEN_RANDOM returns a hexadecimal number with a length of a specified number of bytes.

Example:

SELECT CRYPT_GEN_RANDOM(50) ;

Example:

SELECT CRYPT_GEN_RANDOM(4, 0x25F18060) ;

Origin documentation for cryptGenRandom_Seed

Syntax:

CRYPT_GEN_RANDOM ( length [ , seed ] )

This function returns a cryptographic, randomly-generated number, generated by the Crypto API (CAPI). CRYPT_GEN_RANDOM returns a hexadecimal number with a length of a specified number of bytes.

Example:

SELECT CRYPT_GEN_RANDOM(50) ;

Example:

SELECT CRYPT_GEN_RANDOM(4, 0x25F18060) ;

Origin documentation for lead

Syntax:

LEAD ( scalar_expression [ ,offset ] , [ default ] )
    OVER ( [ partition_by_clause ] order_by_clause )

Accesses data from a subsequent row in the same result set without the use of a self-join starting with SQL Server 2012 (11.x). LEAD provides access to a row at a given physical offset that follows the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a following row.

Example:

USE AdventureWorks2012;
GO
SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,
    LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS NextQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 AND YEAR(QuotaDate) IN ('2005','2006');

Example:

USE AdventureWorks2012;
GO
SELECT TerritoryName, BusinessEntityID, SalesYTD,
       LEAD (SalesYTD, 1, 0) OVER (PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS NextRepSales
FROM Sales.vSalesPerson
WHERE TerritoryName IN (N'Northwest', N'Canada')
ORDER BY TerritoryName;

Example:

CREATE TABLE T (a INT, b INT, c INT);
GO
INSERT INTO T VALUES (1, 1, -3), (2, 2, 4), (3, 1, NULL), (4, 3, 1), (5, 2, NULL), (6, 1, 5);

SELECT b, c,
    LEAD(2*c, b*(SELECT MIN(b) FROM T), -c/2.0) OVER (ORDER BY a) AS i
FROM T;

Example:

-- Uses AdventureWorks

SELECT CalendarYear AS Year, CalendarQuarter AS Quarter, SalesAmountQuota AS SalesQuota,
       LEAD(SalesAmountQuota,1,0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS NextQuota,
   SalesAmountQuota - LEAD(SalesAmountQuota,1,0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS Diff
FROM dbo.FactSalesQuota
WHERE EmployeeKey = 272 AND CalendarYear IN (2001,2002)
ORDER BY CalendarYear, CalendarQuarter;

Origin documentation for lead_Default

Syntax:

LEAD ( scalar_expression [ ,offset ] , [ default ] )
    OVER ( [ partition_by_clause ] order_by_clause )

Accesses data from a subsequent row in the same result set without the use of a self-join starting with SQL Server 2012 (11.x). LEAD provides access to a row at a given physical offset that follows the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a following row.

Example:

USE AdventureWorks2012;
GO
SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,
    LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS NextQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 AND YEAR(QuotaDate) IN ('2005','2006');

Example:

USE AdventureWorks2012;
GO
SELECT TerritoryName, BusinessEntityID, SalesYTD,
       LEAD (SalesYTD, 1, 0) OVER (PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS NextRepSales
FROM Sales.vSalesPerson
WHERE TerritoryName IN (N'Northwest', N'Canada')
ORDER BY TerritoryName;

Example:

CREATE TABLE T (a INT, b INT, c INT);
GO
INSERT INTO T VALUES (1, 1, -3), (2, 2, 4), (3, 1, NULL), (4, 3, 1), (5, 2, NULL), (6, 1, 5);

SELECT b, c,
    LEAD(2*c, b*(SELECT MIN(b) FROM T), -c/2.0) OVER (ORDER BY a) AS i
FROM T;

Example:

-- Uses AdventureWorks

SELECT CalendarYear AS Year, CalendarQuarter AS Quarter, SalesAmountQuota AS SalesQuota,
       LEAD(SalesAmountQuota,1,0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS NextQuota,
   SalesAmountQuota - LEAD(SalesAmountQuota,1,0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS Diff
FROM dbo.FactSalesQuota
WHERE EmployeeKey = 272 AND CalendarYear IN (2001,2002)
ORDER BY CalendarYear, CalendarQuarter;

Origin documentation for lead_Offset

Syntax:

LEAD ( scalar_expression [ ,offset ] , [ default ] )
    OVER ( [ partition_by_clause ] order_by_clause )

Accesses data from a subsequent row in the same result set without the use of a self-join starting with SQL Server 2012 (11.x). LEAD provides access to a row at a given physical offset that follows the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a following row.

Example:

USE AdventureWorks2012;
GO
SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,
    LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS NextQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 AND YEAR(QuotaDate) IN ('2005','2006');

Example:

USE AdventureWorks2012;
GO
SELECT TerritoryName, BusinessEntityID, SalesYTD,
       LEAD (SalesYTD, 1, 0) OVER (PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS NextRepSales
FROM Sales.vSalesPerson
WHERE TerritoryName IN (N'Northwest', N'Canada')
ORDER BY TerritoryName;

Example:

CREATE TABLE T (a INT, b INT, c INT);
GO
INSERT INTO T VALUES (1, 1, -3), (2, 2, 4), (3, 1, NULL), (4, 3, 1), (5, 2, NULL), (6, 1, 5);

SELECT b, c,
    LEAD(2*c, b*(SELECT MIN(b) FROM T), -c/2.0) OVER (ORDER BY a) AS i
FROM T;

Example:

-- Uses AdventureWorks

SELECT CalendarYear AS Year, CalendarQuarter AS Quarter, SalesAmountQuota AS SalesQuota,
       LEAD(SalesAmountQuota,1,0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS NextQuota,
   SalesAmountQuota - LEAD(SalesAmountQuota,1,0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS Diff
FROM dbo.FactSalesQuota
WHERE EmployeeKey = 272 AND CalendarYear IN (2001,2002)
ORDER BY CalendarYear, CalendarQuarter;

Origin documentation for lead_Offset_Default

Syntax:

LEAD ( scalar_expression [ ,offset ] , [ default ] )
    OVER ( [ partition_by_clause ] order_by_clause )

Accesses data from a subsequent row in the same result set without the use of a self-join starting with SQL Server 2012 (11.x). LEAD provides access to a row at a given physical offset that follows the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a following row.

Example:

USE AdventureWorks2012;
GO
SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,
    LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS NextQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 AND YEAR(QuotaDate) IN ('2005','2006');

Example:

USE AdventureWorks2012;
GO
SELECT TerritoryName, BusinessEntityID, SalesYTD,
       LEAD (SalesYTD, 1, 0) OVER (PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS NextRepSales
FROM Sales.vSalesPerson
WHERE TerritoryName IN (N'Northwest', N'Canada')
ORDER BY TerritoryName;

Example:

CREATE TABLE T (a INT, b INT, c INT);
GO
INSERT INTO T VALUES (1, 1, -3), (2, 2, 4), (3, 1, NULL), (4, 3, 1), (5, 2, NULL), (6, 1, 5);

SELECT b, c,
    LEAD(2*c, b*(SELECT MIN(b) FROM T), -c/2.0) OVER (ORDER BY a) AS i
FROM T;

Example:

-- Uses AdventureWorks

SELECT CalendarYear AS Year, CalendarQuarter AS Quarter, SalesAmountQuota AS SalesQuota,
       LEAD(SalesAmountQuota,1,0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS NextQuota,
   SalesAmountQuota - LEAD(SalesAmountQuota,1,0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS Diff
FROM dbo.FactSalesQuota
WHERE EmployeeKey = 272 AND CalendarYear IN (2001,2002)
ORDER BY CalendarYear, CalendarQuarter;

Origin documentation for objectName

Syntax:

OBJECT_NAME ( object_id [, database_id ] )

Returns the database object name for schema-scoped objects. For a list of schema-scoped objects, see sys.objects (Transact-SQL) .

Example:

USE AdventureWorks2012;
GO
SELECT DISTINCT OBJECT_NAME(object_id)
FROM master.sys.objects;
GO

Example:

USE AdventureWorks2012;
GO
SELECT DISTINCT OBJECT_SCHEMA_NAME(object_id, 1) AS schema_name
FROM master.sys.objects;
GO

Example:

USE AdventureWorks2012;
GO
DECLARE @MyID INT;
SET @MyID = (SELECT OBJECT_ID('AdventureWorks2012.Production.Product',
    'U'));
SELECT name, object_id, type_desc
FROM sys.objects
WHERE name = OBJECT_NAME(@MyID);
GO

Example:

SELECT DB_NAME(st.dbid) AS database_name,
    OBJECT_SCHEMA_NAME(st.objectid, st.dbid) AS schema_name,
    OBJECT_NAME(st.objectid, st.dbid) AS object_name,
    st.text AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE st.objectid IS NOT NULL;
GO

Example:

SELECT QUOTENAME(DB_NAME(database_id))
    + N'.'
    + QUOTENAME(OBJECT_SCHEMA_NAME(object_id, database_id))
    + N'.'
    + QUOTENAME(OBJECT_NAME(object_id, database_id))
    , *
FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL);
GO

Example:

SELECT name, object_id, type_desc
FROM sys.objects
WHERE name = OBJECT_NAME(274100017);

Origin documentation for objectName_DatabaseId

Syntax:

OBJECT_NAME ( object_id [, database_id ] )

Returns the database object name for schema-scoped objects. For a list of schema-scoped objects, see sys.objects (Transact-SQL) .

Example:

USE AdventureWorks2012;
GO
SELECT DISTINCT OBJECT_NAME(object_id)
FROM master.sys.objects;
GO

Example:

USE AdventureWorks2012;
GO
SELECT DISTINCT OBJECT_SCHEMA_NAME(object_id, 1) AS schema_name
FROM master.sys.objects;
GO

Example:

USE AdventureWorks2012;
GO
DECLARE @MyID INT;
SET @MyID = (SELECT OBJECT_ID('AdventureWorks2012.Production.Product',
    'U'));
SELECT name, object_id, type_desc
FROM sys.objects
WHERE name = OBJECT_NAME(@MyID);
GO

Example:

SELECT DB_NAME(st.dbid) AS database_name,
    OBJECT_SCHEMA_NAME(st.objectid, st.dbid) AS schema_name,
    OBJECT_NAME(st.objectid, st.dbid) AS object_name,
    st.text AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE st.objectid IS NOT NULL;
GO

Example:

SELECT QUOTENAME(DB_NAME(database_id))
    + N'.'
    + QUOTENAME(OBJECT_SCHEMA_NAME(object_id, database_id))
    + N'.'
    + QUOTENAME(OBJECT_NAME(object_id, database_id))
    , *
FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL);
GO

Example:

SELECT name, object_id, type_desc
FROM sys.objects
WHERE name = OBJECT_NAME(274100017);

Origin documentation for round

Syntax:

ROUND ( numeric_expression , length [ ,function ] )

Returns a numeric value, rounded to the specified length or precision.

Example:

SELECT ROUND(123.9994, 3), ROUND(123.9995, 3);
GO

Example:

SELECT ROUND(150.75, 0);
GO
SELECT ROUND(150.75, 0, 1);
GO

Origin documentation for round_Function

Syntax:

ROUND ( numeric_expression , length [ ,function ] )

Returns a numeric value, rounded to the specified length or precision.

Example:

SELECT ROUND(123.9994, 3), ROUND(123.9995, 3);
GO

Example:

SELECT ROUND(150.75, 0);
GO
SELECT ROUND(150.75, 0, 1);
GO

Origin documentation for getAnsiNull

Syntax:

GETANSINULL ( [ 'database' ] )

Returns the default nullability for the database for this session.

Example:

USE AdventureWorks2012;
GO
SELECT GETANSINULL('AdventureWorks2012')
GO

Origin documentation for getAnsiNull_Database

Syntax:

GETANSINULL ( [ 'database' ] )

Returns the default nullability for the database for this session.

Example:

USE AdventureWorks2012;
GO
SELECT GETANSINULL('AdventureWorks2012')
GO

Origin documentation for ntile

Syntax:

NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )

Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

Example:

USE AdventureWorks2012;
GO
SELECT p.FirstName, p.LastName
    ,NTILE(4) OVER(ORDER BY SalesYTD DESC) AS Quartile
    ,CONVERT(NVARCHAR(20),s.SalesYTD,1) AS SalesYTD
    , a.PostalCode
FROM Sales.SalesPerson AS s
INNER JOIN Person.Person AS p
    ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address AS a
    ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
    AND SalesYTD <> 0;
GO

Example:

USE AdventureWorks2012;
GO
DECLARE @NTILE_Var INT = 4;

SELECT p.FirstName, p.LastName
    ,NTILE(@NTILE_Var) OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS Quartile
    ,CONVERT(NVARCHAR(20),s.SalesYTD,1) AS SalesYTD
    ,a.PostalCode
FROM Sales.SalesPerson AS s
INNER JOIN Person.Person AS p
    ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address AS a
    ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
    AND SalesYTD <> 0;
GO

Example:

-- Uses AdventureWorks

SELECT e.LastName, NTILE(4) OVER(ORDER BY SUM(SalesAmountQuota) DESC) AS Quartile,
       CONVERT (VARCHAR(13), SUM(SalesAmountQuota), 1) AS SalesQuota
FROM dbo.DimEmployee AS e
INNER JOIN dbo.FactSalesQuota AS sq
    ON e.EmployeeKey = sq.EmployeeKey
WHERE sq.CalendarYear = 2003
    AND SalesTerritoryKey IS NOT NULL AND SalesAmountQuota <> 0
GROUP BY e.LastName
ORDER BY Quartile, e.LastName;

Example:

-- Uses AdventureWorks

SELECT e.LastName, NTILE(2) OVER(PARTITION BY e.SalesTerritoryKey ORDER BY SUM(SalesAmountQuota) DESC) AS Quartile,
       CONVERT (VARCHAR(13), SUM(SalesAmountQuota), 1) AS SalesQuota
   ,st.SalesTerritoryCountry
FROM dbo.DimEmployee AS e
INNER JOIN dbo.FactSalesQuota AS sq
    ON e.EmployeeKey = sq.EmployeeKey
INNER JOIN dbo.DimSalesTerritory AS st
    ON e.SalesTerritoryKey = st.SalesTerritoryKey
WHERE sq.CalendarYear = 2003
GROUP BY e.LastName,e.SalesTerritoryKey,st.SalesTerritoryCountry
ORDER BY st.SalesTerritoryCountry, Quartile;

Origin documentation for decryptByKeyAutoAsymKey

Syntax:

DecryptByKeyAutoAsymKey ( akey_ID , akey_password
    , { 'ciphertext' | @ciphertext }
  [ , { add_authenticator | @add_authenticator }
  [ , { authenticator | @authenticator } ] ] )

This function decrypts encrypted data. To do this, it first decrypts a symmetric key with a separate asymmetric key, and then decrypts the encrypted data with the symmetric key extracted in the first "step".

Example:

--Create the keys and certificate.
USE AdventureWorks2012;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mzkvdMlk979438teag$$ds987yghn)(*&4fdg^';
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mzkvdMlk979438teag$$ds987yghn)(*&4fdg^';
CREATE ASYMMETRIC KEY SSN_AKey
    WITH ALGORITHM = RSA_2048 ;
GO
CREATE SYMMETRIC KEY SSN_Key_02 WITH ALGORITHM = DES
    ENCRYPTION BY ASYMMETRIC KEY SSN_AKey;
GO
--
--Add a column of encrypted data.
ALTER TABLE HumanResources.Employee
    ADD EncryptedNationalIDNumber2 varbinary(128);
OPEN SYMMETRIC KEY SSN_Key_02
   DECRYPTION BY ASYMMETRIC KEY SSN_AKey;
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber2
    = EncryptByKey(Key_GUID('SSN_Key_02'), NationalIDNumber);
GO
--Close the key used to encrypt the data.
CLOSE SYMMETRIC KEY SSN_Key_02;
--
--There are two ways to decrypt the stored data.
--
--OPTION ONE, using DecryptByKey()
--1. Open the symmetric key.
--2. Decrypt the data.
--3. Close the symmetric key.
OPEN SYMMETRIC KEY SSN_Key_02
   DECRYPTION BY ASYMMETRIC KEY SSN_AKey;
SELECT NationalIDNumber, EncryptedNationalIDNumber2
    AS 'Encrypted ID Number',
    CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber2))
    AS 'Decrypted ID Number'
    FROM HumanResources.Employee;
CLOSE SYMMETRIC KEY SSN_Key_02;
--
--OPTION TWO, using DecryptByKeyAutoAsymKey()
SELECT NationalIDNumber, EncryptedNationalIDNumber2
    AS 'Encrypted ID Number',
    CONVERT(nvarchar, DecryptByKeyAutoAsymKey ( AsymKey_ID('SSN_AKey') , NULL ,EncryptedNationalIDNumber2))
    AS 'Decrypted ID Number'
    FROM HumanResources.Employee;
GO

Origin documentation for decryptByKeyAutoAsymKey_AddAuthenticator

Syntax:

DecryptByKeyAutoAsymKey ( akey_ID , akey_password
    , { 'ciphertext' | @ciphertext }
  [ , { add_authenticator | @add_authenticator }
  [ , { authenticator | @authenticator } ] ] )

This function decrypts encrypted data. To do this, it first decrypts a symmetric key with a separate asymmetric key, and then decrypts the encrypted data with the symmetric key extracted in the first "step".

Example:

--Create the keys and certificate.
USE AdventureWorks2012;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mzkvdMlk979438teag$$ds987yghn)(*&4fdg^';
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mzkvdMlk979438teag$$ds987yghn)(*&4fdg^';
CREATE ASYMMETRIC KEY SSN_AKey
    WITH ALGORITHM = RSA_2048 ;
GO
CREATE SYMMETRIC KEY SSN_Key_02 WITH ALGORITHM = DES
    ENCRYPTION BY ASYMMETRIC KEY SSN_AKey;
GO
--
--Add a column of encrypted data.
ALTER TABLE HumanResources.Employee
    ADD EncryptedNationalIDNumber2 varbinary(128);
OPEN SYMMETRIC KEY SSN_Key_02
   DECRYPTION BY ASYMMETRIC KEY SSN_AKey;
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber2
    = EncryptByKey(Key_GUID('SSN_Key_02'), NationalIDNumber);
GO
--Close the key used to encrypt the data.
CLOSE SYMMETRIC KEY SSN_Key_02;
--
--There are two ways to decrypt the stored data.
--
--OPTION ONE, using DecryptByKey()
--1. Open the symmetric key.
--2. Decrypt the data.
--3. Close the symmetric key.
OPEN SYMMETRIC KEY SSN_Key_02
   DECRYPTION BY ASYMMETRIC KEY SSN_AKey;
SELECT NationalIDNumber, EncryptedNationalIDNumber2
    AS 'Encrypted ID Number',
    CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber2))
    AS 'Decrypted ID Number'
    FROM HumanResources.Employee;
CLOSE SYMMETRIC KEY SSN_Key_02;
--
--OPTION TWO, using DecryptByKeyAutoAsymKey()
SELECT NationalIDNumber, EncryptedNationalIDNumber2
    AS 'Encrypted ID Number',
    CONVERT(nvarchar, DecryptByKeyAutoAsymKey ( AsymKey_ID('SSN_AKey') , NULL ,EncryptedNationalIDNumber2))
    AS 'Decrypted ID Number'
    FROM HumanResources.Employee;
GO

Origin documentation for decryptByKeyAutoAsymKey_AddAuthenticator_Authenticator

Syntax:

DecryptByKeyAutoAsymKey ( akey_ID , akey_password
    , { 'ciphertext' | @ciphertext }
  [ , { add_authenticator | @add_authenticator }
  [ , { authenticator | @authenticator } ] ] )

This function decrypts encrypted data. To do this, it first decrypts a symmetric key with a separate asymmetric key, and then decrypts the encrypted data with the symmetric key extracted in the first "step".

Example:

--Create the keys and certificate.
USE AdventureWorks2012;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mzkvdMlk979438teag$$ds987yghn)(*&4fdg^';
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mzkvdMlk979438teag$$ds987yghn)(*&4fdg^';
CREATE ASYMMETRIC KEY SSN_AKey
    WITH ALGORITHM = RSA_2048 ;
GO
CREATE SYMMETRIC KEY SSN_Key_02 WITH ALGORITHM = DES
    ENCRYPTION BY ASYMMETRIC KEY SSN_AKey;
GO
--
--Add a column of encrypted data.
ALTER TABLE HumanResources.Employee
    ADD EncryptedNationalIDNumber2 varbinary(128);
OPEN SYMMETRIC KEY SSN_Key_02
   DECRYPTION BY ASYMMETRIC KEY SSN_AKey;
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber2
    = EncryptByKey(Key_GUID('SSN_Key_02'), NationalIDNumber);
GO
--Close the key used to encrypt the data.
CLOSE SYMMETRIC KEY SSN_Key_02;
--
--There are two ways to decrypt the stored data.
--
--OPTION ONE, using DecryptByKey()
--1. Open the symmetric key.
--2. Decrypt the data.
--3. Close the symmetric key.
OPEN SYMMETRIC KEY SSN_Key_02
   DECRYPTION BY ASYMMETRIC KEY SSN_AKey;
SELECT NationalIDNumber, EncryptedNationalIDNumber2
    AS 'Encrypted ID Number',
    CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber2))
    AS 'Decrypted ID Number'
    FROM HumanResources.Employee;
CLOSE SYMMETRIC KEY SSN_Key_02;
--
--OPTION TWO, using DecryptByKeyAutoAsymKey()
SELECT NationalIDNumber, EncryptedNationalIDNumber2
    AS 'Encrypted ID Number',
    CONVERT(nvarchar, DecryptByKeyAutoAsymKey ( AsymKey_ID('SSN_AKey') , NULL ,EncryptedNationalIDNumber2))
    AS 'Decrypted ID Number'
    FROM HumanResources.Employee;
GO

Origin documentation for asymKeyProperty_KeyId_AlgorithmDesc

Syntax:

ASYMKEYPROPERTY (Key_ID , 'algorithm_desc' | 'string_sid' | 'sid')

This function returns the properties of an asymmetric key.

Example:

SELECT
ASYMKEYPROPERTY(256, 'algorithm_desc') AS Algorithm,
ASYMKEYPROPERTY(256, 'string_sid') AS String_SID,
ASYMKEYPROPERTY(256, 'sid') AS SID ;
GO

Origin documentation for asymKeyProperty_StringSid

Syntax:

ASYMKEYPROPERTY (Key_ID , 'algorithm_desc' | 'string_sid' | 'sid')

This function returns the properties of an asymmetric key.

Example:

SELECT
ASYMKEYPROPERTY(256, 'algorithm_desc') AS Algorithm,
ASYMKEYPROPERTY(256, 'string_sid') AS String_SID,
ASYMKEYPROPERTY(256, 'sid') AS SID ;
GO

Origin documentation for asymKeyProperty_Sid

Syntax:

ASYMKEYPROPERTY (Key_ID , 'algorithm_desc' | 'string_sid' | 'sid')

This function returns the properties of an asymmetric key.

Example:

SELECT
ASYMKEYPROPERTY(256, 'algorithm_desc') AS Algorithm,
ASYMKEYPROPERTY(256, 'string_sid') AS String_SID,
ASYMKEYPROPERTY(256, 'sid') AS SID ;
GO

Origin documentation for encryptByPassPhrase

Syntax:

EncryptByPassPhrase ( { 'passphrase' | @passphrase }
    , { 'cleartext' | @cleartext }
  [ , { add_authenticator | @add_authenticator }
    , { authenticator | @authenticator } ] )

Encrypt data with a passphrase using the TRIPLE DES algorithm with a 128 key bit length.

Note

SQL Server 2017 and later versions encrypts data with a passphrase using an AES256 key.

Example:

USE AdventureWorks2012;
GO
-- Create a column in which to store the encrypted data.
ALTER TABLE Sales.CreditCard
    ADD CardNumber_EncryptedbyPassphrase VARBINARY(256);
GO
-- First get the passphrase from the user.
DECLARE @PassphraseEnteredByUser NVARCHAR(128);
SET @PassphraseEnteredByUser
    = 'A little learning is a dangerous thing!';

-- Update the record for the user's credit card.
-- In this case, the record is number 3681.
UPDATE Sales.CreditCard
SET CardNumber_EncryptedbyPassphrase = EncryptByPassPhrase(@PassphraseEnteredByUser
    , CardNumber, 1, CONVERT(varbinary, CreditCardID))
WHERE CreditCardID = '3681';
GO

Origin documentation for encryptByPassPhrase_AddAuthenticator_Authenticator

Syntax:

EncryptByPassPhrase ( { 'passphrase' | @passphrase }
    , { 'cleartext' | @cleartext }
  [ , { add_authenticator | @add_authenticator }
    , { authenticator | @authenticator } ] )

Encrypt data with a passphrase using the TRIPLE DES algorithm with a 128 key bit length.

Note

SQL Server 2017 and later versions encrypts data with a passphrase using an AES256 key.

Example:

USE AdventureWorks2012;
GO
-- Create a column in which to store the encrypted data.
ALTER TABLE Sales.CreditCard
    ADD CardNumber_EncryptedbyPassphrase VARBINARY(256);
GO
-- First get the passphrase from the user.
DECLARE @PassphraseEnteredByUser NVARCHAR(128);
SET @PassphraseEnteredByUser
    = 'A little learning is a dangerous thing!';

-- Update the record for the user's credit card.
-- In this case, the record is number 3681.
UPDATE Sales.CreditCard
SET CardNumber_EncryptedbyPassphrase = EncryptByPassPhrase(@PassphraseEnteredByUser
    , CardNumber, 1, CONVERT(varbinary, CreditCardID))
WHERE CreditCardID = '3681';
GO

Origin documentation for decryptByKeyAutoCert

Syntax:

DecryptByKeyAutoCert ( cert_ID , cert_password
    , { 'ciphertext' | @ciphertext }
  [ , { add_authenticator | @add_authenticator }
  [ , { authenticator | @authenticator } ] ] )

This function decrypts data with a symmetric key. That symmetric key automatically decrypts with a certificate.

Example:

--Create the keys and certificate.
USE AdventureWorks2012;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mzkvdlk979438teag$$ds987yghn)(*&4fdg^';
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mzkvdlk979438teag$$ds987yghn)(*&4fdg^';
CREATE CERTIFICATE HumanResources037
   WITH SUBJECT = 'Sammamish HR',
   EXPIRY_DATE = '10/31/2009';
CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = DES
    ENCRYPTION BY CERTIFICATE HumanResources037;
GO
----Add a column of encrypted data.
ALTER TABLE HumanResources.Employee
    ADD EncryptedNationalIDNumber varbinary(128);
OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037 ;
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber
    = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);
GO
--
--Close the key used to encrypt the data.
CLOSE SYMMETRIC KEY SSN_Key_01;
--
--There are two ways to decrypt the stored data.
--
--OPTION ONE, using DecryptByKey()
--1. Open the symmetric key
--2. Decrypt the data
--3. Close the symmetric key
OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037;
SELECT NationalIDNumber, EncryptedNationalIDNumber
    AS 'Encrypted ID Number',
    CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
    AS 'Decrypted ID Number'
    FROM HumanResources.Employee;
CLOSE SYMMETRIC KEY SSN_Key_01;
--
--OPTION TWO, using DecryptByKeyAutoCert()
SELECT NationalIDNumber, EncryptedNationalIDNumber
    AS 'Encrypted ID Number',
    CONVERT(nvarchar, DecryptByKeyAutoCert ( cert_ID('HumanResources037') , NULL ,EncryptedNationalIDNumber))
    AS 'Decrypted ID Number'
    FROM HumanResources.Employee;

Origin documentation for decryptByKeyAutoCert_AddAuthenticator

Syntax:

DecryptByKeyAutoCert ( cert_ID , cert_password
    , { 'ciphertext' | @ciphertext }
  [ , { add_authenticator | @add_authenticator }
  [ , { authenticator | @authenticator } ] ] )

This function decrypts data with a symmetric key. That symmetric key automatically decrypts with a certificate.

Example:

--Create the keys and certificate.
USE AdventureWorks2012;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mzkvdlk979438teag$$ds987yghn)(*&4fdg^';
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mzkvdlk979438teag$$ds987yghn)(*&4fdg^';
CREATE CERTIFICATE HumanResources037
   WITH SUBJECT = 'Sammamish HR',
   EXPIRY_DATE = '10/31/2009';
CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = DES
    ENCRYPTION BY CERTIFICATE HumanResources037;
GO
----Add a column of encrypted data.
ALTER TABLE HumanResources.Employee
    ADD EncryptedNationalIDNumber varbinary(128);
OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037 ;
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber
    = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);
GO
--
--Close the key used to encrypt the data.
CLOSE SYMMETRIC KEY SSN_Key_01;
--
--There are two ways to decrypt the stored data.
--
--OPTION ONE, using DecryptByKey()
--1. Open the symmetric key
--2. Decrypt the data
--3. Close the symmetric key
OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037;
SELECT NationalIDNumber, EncryptedNationalIDNumber
    AS 'Encrypted ID Number',
    CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
    AS 'Decrypted ID Number'
    FROM HumanResources.Employee;
CLOSE SYMMETRIC KEY SSN_Key_01;
--
--OPTION TWO, using DecryptByKeyAutoCert()
SELECT NationalIDNumber, EncryptedNationalIDNumber
    AS 'Encrypted ID Number',
    CONVERT(nvarchar, DecryptByKeyAutoCert ( cert_ID('HumanResources037') , NULL ,EncryptedNationalIDNumber))
    AS 'Decrypted ID Number'
    FROM HumanResources.Employee;

Origin documentation for decryptByKeyAutoCert_AddAuthenticator_Authenticator

Syntax:

DecryptByKeyAutoCert ( cert_ID , cert_password
    , { 'ciphertext' | @ciphertext }
  [ , { add_authenticator | @add_authenticator }
  [ , { authenticator | @authenticator } ] ] )

This function decrypts data with a symmetric key. That symmetric key automatically decrypts with a certificate.

Example:

--Create the keys and certificate.
USE AdventureWorks2012;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mzkvdlk979438teag$$ds987yghn)(*&4fdg^';
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mzkvdlk979438teag$$ds987yghn)(*&4fdg^';
CREATE CERTIFICATE HumanResources037
   WITH SUBJECT = 'Sammamish HR',
   EXPIRY_DATE = '10/31/2009';
CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = DES
    ENCRYPTION BY CERTIFICATE HumanResources037;
GO
----Add a column of encrypted data.
ALTER TABLE HumanResources.Employee
    ADD EncryptedNationalIDNumber varbinary(128);
OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037 ;
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber
    = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);
GO
--
--Close the key used to encrypt the data.
CLOSE SYMMETRIC KEY SSN_Key_01;
--
--There are two ways to decrypt the stored data.
--
--OPTION ONE, using DecryptByKey()
--1. Open the symmetric key
--2. Decrypt the data
--3. Close the symmetric key
OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037;
SELECT NationalIDNumber, EncryptedNationalIDNumber
    AS 'Encrypted ID Number',
    CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
    AS 'Decrypted ID Number'
    FROM HumanResources.Employee;
CLOSE SYMMETRIC KEY SSN_Key_01;
--
--OPTION TWO, using DecryptByKeyAutoCert()
SELECT NationalIDNumber, EncryptedNationalIDNumber
    AS 'Encrypted ID Number',
    CONVERT(nvarchar, DecryptByKeyAutoCert ( cert_ID('HumanResources037') , NULL ,EncryptedNationalIDNumber))
    AS 'Decrypted ID Number'
    FROM HumanResources.Employee;

Origin documentation for triggerNestLevel

Syntax:

TRIGGER_NESTLEVEL ( [ object_id ] , [ 'trigger_type' ] , [ 'trigger_event_category' ] )

Returns the number of triggers executed for the statement that fired the trigger. TRIGGER_NESTLEVEL is used in DML and DDL triggers to determine the current level of nesting.

Example:

IF ( (SELECT TRIGGER_NESTLEVEL( OBJECT_ID('xyz') , 'AFTER' , 'DML' ) ) > 5 )
   RAISERROR('Trigger xyz nested more than 5 levels.',16,-1)

Example:

IF ( ( SELECT TRIGGER_NESTLEVEL ( ( SELECT object_id FROM sys.triggers
WHERE name = 'abc' ), 'AFTER' , 'DDL' ) ) > 5 )
   RAISERROR ('Trigger abc nested more than 5 levels.',16,-1)

Example:

IF ( (SELECT trigger_nestlevel() ) > 5 )
   RAISERROR
      ('This statement nested over 5 levels of triggers.',16,-1)

Origin documentation for triggerNestLevel_TriggerType

Syntax:

TRIGGER_NESTLEVEL ( [ object_id ] , [ 'trigger_type' ] , [ 'trigger_event_category' ] )

Returns the number of triggers executed for the statement that fired the trigger. TRIGGER_NESTLEVEL is used in DML and DDL triggers to determine the current level of nesting.

Example:

IF ( (SELECT TRIGGER_NESTLEVEL( OBJECT_ID('xyz') , 'AFTER' , 'DML' ) ) > 5 )
   RAISERROR('Trigger xyz nested more than 5 levels.',16,-1)

Example:

IF ( ( SELECT TRIGGER_NESTLEVEL ( ( SELECT object_id FROM sys.triggers
WHERE name = 'abc' ), 'AFTER' , 'DDL' ) ) > 5 )
   RAISERROR ('Trigger abc nested more than 5 levels.',16,-1)

Example:

IF ( (SELECT trigger_nestlevel() ) > 5 )
   RAISERROR
      ('This statement nested over 5 levels of triggers.',16,-1)

Origin documentation for triggerNestLevel_TriggerType_TriggerEventCategory

Syntax:

TRIGGER_NESTLEVEL ( [ object_id ] , [ 'trigger_type' ] , [ 'trigger_event_category' ] )

Returns the number of triggers executed for the statement that fired the trigger. TRIGGER_NESTLEVEL is used in DML and DDL triggers to determine the current level of nesting.

Example:

IF ( (SELECT TRIGGER_NESTLEVEL( OBJECT_ID('xyz') , 'AFTER' , 'DML' ) ) > 5 )
   RAISERROR('Trigger xyz nested more than 5 levels.',16,-1)

Example:

IF ( ( SELECT TRIGGER_NESTLEVEL ( ( SELECT object_id FROM sys.triggers
WHERE name = 'abc' ), 'AFTER' , 'DDL' ) ) > 5 )
   RAISERROR ('Trigger abc nested more than 5 levels.',16,-1)

Example:

IF ( (SELECT trigger_nestlevel() ) > 5 )
   RAISERROR
      ('This statement nested over 5 levels of triggers.',16,-1)

Origin documentation for triggerNestLevel_ObjectId

Syntax:

TRIGGER_NESTLEVEL ( [ object_id ] , [ 'trigger_type' ] , [ 'trigger_event_category' ] )

Returns the number of triggers executed for the statement that fired the trigger. TRIGGER_NESTLEVEL is used in DML and DDL triggers to determine the current level of nesting.

Example:

IF ( (SELECT TRIGGER_NESTLEVEL( OBJECT_ID('xyz') , 'AFTER' , 'DML' ) ) > 5 )
   RAISERROR('Trigger xyz nested more than 5 levels.',16,-1)

Example:

IF ( ( SELECT TRIGGER_NESTLEVEL ( ( SELECT object_id FROM sys.triggers
WHERE name = 'abc' ), 'AFTER' , 'DDL' ) ) > 5 )
   RAISERROR ('Trigger abc nested more than 5 levels.',16,-1)

Example:

IF ( (SELECT trigger_nestlevel() ) > 5 )
   RAISERROR
      ('This statement nested over 5 levels of triggers.',16,-1)

Origin documentation for triggerNestLevel_ObjectId_TriggerType

Syntax:

TRIGGER_NESTLEVEL ( [ object_id ] , [ 'trigger_type' ] , [ 'trigger_event_category' ] )

Returns the number of triggers executed for the statement that fired the trigger. TRIGGER_NESTLEVEL is used in DML and DDL triggers to determine the current level of nesting.

Example:

IF ( (SELECT TRIGGER_NESTLEVEL( OBJECT_ID('xyz') , 'AFTER' , 'DML' ) ) > 5 )
   RAISERROR('Trigger xyz nested more than 5 levels.',16,-1)

Example:

IF ( ( SELECT TRIGGER_NESTLEVEL ( ( SELECT object_id FROM sys.triggers
WHERE name = 'abc' ), 'AFTER' , 'DDL' ) ) > 5 )
   RAISERROR ('Trigger abc nested more than 5 levels.',16,-1)

Example:

IF ( (SELECT trigger_nestlevel() ) > 5 )
   RAISERROR
      ('This statement nested over 5 levels of triggers.',16,-1)

Origin documentation for triggerNestLevel_ObjectId_TriggerType_TriggerEventCategory

Syntax:

TRIGGER_NESTLEVEL ( [ object_id ] , [ 'trigger_type' ] , [ 'trigger_event_category' ] )

Returns the number of triggers executed for the statement that fired the trigger. TRIGGER_NESTLEVEL is used in DML and DDL triggers to determine the current level of nesting.

Example:

IF ( (SELECT TRIGGER_NESTLEVEL( OBJECT_ID('xyz') , 'AFTER' , 'DML' ) ) > 5 )
   RAISERROR('Trigger xyz nested more than 5 levels.',16,-1)

Example:

IF ( ( SELECT TRIGGER_NESTLEVEL ( ( SELECT object_id FROM sys.triggers
WHERE name = 'abc' ), 'AFTER' , 'DDL' ) ) > 5 )
   RAISERROR ('Trigger abc nested more than 5 levels.',16,-1)

Example:

IF ( (SELECT trigger_nestlevel() ) > 5 )
   RAISERROR
      ('This statement nested over 5 levels of triggers.',16,-1)

Origin documentation for dateBucket

Syntax:

DATE_BUCKET (datepart, number, date [, origin ] )

This function returns the date-time value corresponding to the start of each date-time bucket from the timestamp defined by the origin parameter, or the default origin value of 1900-01-01 00:00:00.000 if the origin parameter isn't specified.

See Date and Time Data Types and Functions (Transact-SQL) for an overview of all Transact-SQL date and time data types and functions.

Example:

DECLARE @date DATETIME2 = '2020-04-15 21:22:11';
SELECT DATE_BUCKET(WEEK, 1, @date);

Example:

DECLARE @date DATETIME2 = '2020-04-15 21:22:11';
SELECT DATE_BUCKET(WEEK, 2, @date);
SELECT DATE_BUCKET(WEEK, 3, @date);
SELECT DATE_BUCKET(WEEK, 4, @date);
SELECT DATE_BUCKET(WEEK, 6, @date);

Example:

DECLARE @date DATETIME2 = '2020-04-15 21:22:11';
SELECT DATE_BUCKET(WEEK, 5, @date);

Example:

DECLARE @date DATETIME2 = '2020-06-15 21:22:11';
DECLARE @origin DATETIME2 = '2019-01-01 00:00:00';
SELECT DATE_BUCKET(WEEK, 5, @date, @origin);

Example:

DECLARE @date DATETIME2 = '2020-04-30 00:00:00';
SELECT DATE_BUCKET(DAY, 2147483648, @date);

Example:

SELECT DATE_BUCKET(DAY, 10, SYSUTCDATETIME());

Example:

DECLARE @date DATETIME2 = '2020-04-30 21:21:21';
SELECT 'Week', DATE_BUCKET(WEEK, 1, @date)
UNION ALL
SELECT 'Day', DATE_BUCKET(DAY, 1, @date)
UNION ALL
SELECT 'Hour', DATE_BUCKET(HOUR, 1, @date)
UNION ALL
SELECT 'Minutes', DATE_BUCKET(MINUTE, 1, @date)
UNION ALL
SELECT 'Seconds', DATE_BUCKET(SECOND, 1, @date);

Example:

DECLARE @days INT = 365,
    @datetime DATETIME2 = '2000-01-01 01:01:01.1110000';
    /* 2000 was a leap year */;
SELECT DATE_BUCKET(DAY, @days, @datetime);

Example:

SELECT DATE_BUCKET(WEEK, 1, CAST(ShipDate AS DATETIME2)) AS ShippedDateBucket
    , SUM(OrderQuantity) AS SumOrderQuantity
    , SUM(UnitPrice) AS SumUnitPrice
FROM dbo.FactInternetSales FIS
WHERE ShipDate BETWEEN '2011-01-03 00:00:00.000'
        AND '2011-02-28 00:00:00.000'
GROUP BY DATE_BUCKET(WEEK, 1, CAST(ShipDate AS DATETIME2))
ORDER BY ShippedDateBucket;

Example:

SELECT DATE_BUCKET(WEEK, 10, SYSDATETIME());

Example:

SELECT DATE_BUCKET(WEEK, (
    SELECT TOP 1 CustomerKey
    FROM dbo.DimCustomer
    WHERE GeographyKey > 100
    ), (
    SELECT MAX(OrderDate)
    FROM dbo.FactInternetSales
));

Example:

SELECT DATE_BUCKET(WEEK, (10/2), SYSDATETIME());

Example:

SELECT DISTINCT DATE_BUCKET(DAY, 30, CAST([ShipDate] AS DATETIME2)) AS DateBucket
    , FIRST_VALUE([SalesOrderNumber]) OVER (
        ORDER BY DATE_BUCKET(DAY, 30, CAST([ShipDate] AS DATETIME2))
        ) AS First_Value_In_Bucket
    , LAST_VALUE([SalesOrderNumber]) OVER (
        ORDER BY DATE_BUCKET(DAY, 30, CAST([ShipDate] AS DATETIME2))
        ) AS Last_Value_In_Bucket
FROM [dbo].[FactInternetSales]
WHERE ShipDate BETWEEN '2011-01-03 00:00:00.000'
        AND '2011-02-28 00:00:00.000'
ORDER BY DateBucket;
GO

Example:

DECLARE @date DATETIME2 = '2020-06-15 21:22:11';
DECLARE @origin DATETIME2 = '2019-01-01 00:00:00';
SELECT DATE_BUCKET(HOUR, 2, @date, @origin);

Origin documentation for dateBucket_Origin

Syntax:

DATE_BUCKET (datepart, number, date [, origin ] )

This function returns the date-time value corresponding to the start of each date-time bucket from the timestamp defined by the origin parameter, or the default origin value of 1900-01-01 00:00:00.000 if the origin parameter isn't specified.

See Date and Time Data Types and Functions (Transact-SQL) for an overview of all Transact-SQL date and time data types and functions.

Example:

DECLARE @date DATETIME2 = '2020-04-15 21:22:11';
SELECT DATE_BUCKET(WEEK, 1, @date);

Example:

DECLARE @date DATETIME2 = '2020-04-15 21:22:11';
SELECT DATE_BUCKET(WEEK, 2, @date);
SELECT DATE_BUCKET(WEEK, 3, @date);
SELECT DATE_BUCKET(WEEK, 4, @date);
SELECT DATE_BUCKET(WEEK, 6, @date);

Example:

DECLARE @date DATETIME2 = '2020-04-15 21:22:11';
SELECT DATE_BUCKET(WEEK, 5, @date);

Example:

DECLARE @date DATETIME2 = '2020-06-15 21:22:11';
DECLARE @origin DATETIME2 = '2019-01-01 00:00:00';
SELECT DATE_BUCKET(WEEK, 5, @date, @origin);

Example:

DECLARE @date DATETIME2 = '2020-04-30 00:00:00';
SELECT DATE_BUCKET(DAY, 2147483648, @date);

Example:

SELECT DATE_BUCKET(DAY, 10, SYSUTCDATETIME());

Example:

DECLARE @date DATETIME2 = '2020-04-30 21:21:21';
SELECT 'Week', DATE_BUCKET(WEEK, 1, @date)
UNION ALL
SELECT 'Day', DATE_BUCKET(DAY, 1, @date)
UNION ALL
SELECT 'Hour', DATE_BUCKET(HOUR, 1, @date)
UNION ALL
SELECT 'Minutes', DATE_BUCKET(MINUTE, 1, @date)
UNION ALL
SELECT 'Seconds', DATE_BUCKET(SECOND, 1, @date);

Example:

DECLARE @days INT = 365,
    @datetime DATETIME2 = '2000-01-01 01:01:01.1110000';
    /* 2000 was a leap year */;
SELECT DATE_BUCKET(DAY, @days, @datetime);

Example:

SELECT DATE_BUCKET(WEEK, 1, CAST(ShipDate AS DATETIME2)) AS ShippedDateBucket
    , SUM(OrderQuantity) AS SumOrderQuantity
    , SUM(UnitPrice) AS SumUnitPrice
FROM dbo.FactInternetSales FIS
WHERE ShipDate BETWEEN '2011-01-03 00:00:00.000'
        AND '2011-02-28 00:00:00.000'
GROUP BY DATE_BUCKET(WEEK, 1, CAST(ShipDate AS DATETIME2))
ORDER BY ShippedDateBucket;

Example:

SELECT DATE_BUCKET(WEEK, 10, SYSDATETIME());

Example:

SELECT DATE_BUCKET(WEEK, (
    SELECT TOP 1 CustomerKey
    FROM dbo.DimCustomer
    WHERE GeographyKey > 100
    ), (
    SELECT MAX(OrderDate)
    FROM dbo.FactInternetSales
));

Example:

SELECT DATE_BUCKET(WEEK, (10/2), SYSDATETIME());

Example:

SELECT DISTINCT DATE_BUCKET(DAY, 30, CAST([ShipDate] AS DATETIME2)) AS DateBucket
    , FIRST_VALUE([SalesOrderNumber]) OVER (
        ORDER BY DATE_BUCKET(DAY, 30, CAST([ShipDate] AS DATETIME2))
        ) AS First_Value_In_Bucket
    , LAST_VALUE([SalesOrderNumber]) OVER (
        ORDER BY DATE_BUCKET(DAY, 30, CAST([ShipDate] AS DATETIME2))
        ) AS Last_Value_In_Bucket
FROM [dbo].[FactInternetSales]
WHERE ShipDate BETWEEN '2011-01-03 00:00:00.000'
        AND '2011-02-28 00:00:00.000'
ORDER BY DateBucket;
GO

Example:

DECLARE @date DATETIME2 = '2020-06-15 21:22:11';
DECLARE @origin DATETIME2 = '2019-01-01 00:00:00';
SELECT DATE_BUCKET(HOUR, 2, @date, @origin);

Origin documentation for dbName

Syntax:

DB_NAME ( [ database_id ] )

This function returns the name of a specified database.

Example:

SELECT DB_NAME() AS [Current Database];
GO

Example:

USE master;
GO
SELECT DB_NAME(3) AS [Database Name];
GO

Example:

SELECT DB_NAME() AS [Current Database];

Example:

SELECT DB_NAME(database_id) AS [Database], database_id
FROM sys.databases;

Origin documentation for dbName_DatabaseId

Syntax:

DB_NAME ( [ database_id ] )

This function returns the name of a specified database.

Example:

SELECT DB_NAME() AS [Current Database];
GO

Example:

USE master;
GO
SELECT DB_NAME(3) AS [Database Name];
GO

Example:

SELECT DB_NAME() AS [Current Database];

Example:

SELECT DB_NAME(database_id) AS [Database], database_id
FROM sys.databases;

Origin documentation for decryptByAsymKey

Syntax:

DecryptByAsymKey (Asym_Key_ID , { 'ciphertext' | @ciphertext }
    [ , 'Asym_Key_Password' ] )

This function uses an asymmetric key to decrypt encrypted data.

Example:

SELECT CONVERT(NVARCHAR(max),
    DecryptByAsymKey( AsymKey_Id('JanainaAsymKey02'),
    ProtectedData, N'pGFD4bb925DGvbd2439587y' ))
AS DecryptedData
FROM [AdventureWorks2012].[Sales].[ProtectedData04]
WHERE Description = N'encrypted by asym key''JanainaAsymKey02''';
GO

Origin documentation for decryptByAsymKey_AsymKeyPassword

Syntax:

DecryptByAsymKey (Asym_Key_ID , { 'ciphertext' | @ciphertext }
    [ , 'Asym_Key_Password' ] )

This function uses an asymmetric key to decrypt encrypted data.

Example:

SELECT CONVERT(NVARCHAR(max),
    DecryptByAsymKey( AsymKey_Id('JanainaAsymKey02'),
    ProtectedData, N'pGFD4bb925DGvbd2439587y' ))
AS DecryptedData
FROM [AdventureWorks2012].[Sales].[ProtectedData04]
WHERE Description = N'encrypted by asym key''JanainaAsymKey02''';
GO

Origin documentation for signByAsymKey

Syntax:

SignByAsymKey( Asym_Key_ID , @plaintext [ , 'password' ] )

Signs plaintext with an asymmetric key

Example:

-- Create a table in which to store the data
CREATE TABLE [SignedData04](Description NVARCHAR(max), Data NVARCHAR(max), DataSignature VARBINARY(8000));
GO
-- Store data together with its signature
DECLARE @clear_text_data NVARCHAR(max);
set @clear_text_data = N'Important numbers 2, 3, 5, 7, 11, 13, 17,
      19, 23, 29, 31, 37, 41, 43, 47, 53, 59, 61, 67, 71, 73, 79,
      83, 89, 97';
INSERT INTO [SignedData04]
    VALUES( N'data encrypted by asymmetric key ''PrimeKey''',
    @clear_text_data, SignByAsymKey( AsymKey_Id( 'PrimeKey' ),
    @clear_text_data, N'pGFD4bb925DGvbd2439587y' ));
GO

Origin documentation for signByAsymKey_Password

Syntax:

SignByAsymKey( Asym_Key_ID , @plaintext [ , 'password' ] )

Signs plaintext with an asymmetric key

Example:

-- Create a table in which to store the data
CREATE TABLE [SignedData04](Description NVARCHAR(max), Data NVARCHAR(max), DataSignature VARBINARY(8000));
GO
-- Store data together with its signature
DECLARE @clear_text_data NVARCHAR(max);
set @clear_text_data = N'Important numbers 2, 3, 5, 7, 11, 13, 17,
      19, 23, 29, 31, 37, 41, 43, 47, 53, 59, 61, 67, 71, 73, 79,
      83, 89, 97';
INSERT INTO [SignedData04]
    VALUES( N'data encrypted by asymmetric key ''PrimeKey''',
    @clear_text_data, SignByAsymKey( AsymKey_Id( 'PrimeKey' ),
    @clear_text_data, N'pGFD4bb925DGvbd2439587y' ));
GO

Origin documentation for signByCert

Syntax:

SignByCert ( certificate_ID , @cleartext [ , 'password' ] )

Signs text with a certificate and returns the signature.

Example:

DECLARE @SensitiveData NVARCHAR(max);
SET @SensitiveData = N'Saddle Price Points are
    2, 3, 5, 7, 11, 13, 17, 19, 23, 29';
INSERT INTO [SignedData04]
    VALUES( N'data signed by certificate ''ABerglundCert07''',
    @SensitiveData, SignByCert( Cert_Id( 'ABerglundCert07' ),
    @SensitiveData, N'pGFD4bb925DGvbd2439587y' ));
GO

Origin documentation for signByCert_Password

Syntax:

SignByCert ( certificate_ID , @cleartext [ , 'password' ] )

Signs text with a certificate and returns the signature.

Example:

DECLARE @SensitiveData NVARCHAR(max);
SET @SensitiveData = N'Saddle Price Points are
    2, 3, 5, 7, 11, 13, 17, 19, 23, 29';
INSERT INTO [SignedData04]
    VALUES( N'data signed by certificate ''ABerglundCert07''',
    @SensitiveData, SignByCert( Cert_Id( 'ABerglundCert07' ),
    @SensitiveData, N'pGFD4bb925DGvbd2439587y' ));
GO

Origin documentation for percentRank

Syntax:

PERCENT_RANK( )
    OVER ( [ partition_by_clause ] order_by_clause )

Calculates the relative rank of a row within a group of rows in SQL Server. Use PERCENT_RANK to evaluate the relative standing of a value within a query result set or partition. PERCENT_RANK is similar to the CUME_DIST function.

Example:

USE AdventureWorks2012;
GO
SELECT Department, LastName, Rate,
       CUME_DIST () OVER (PARTITION BY Department ORDER BY Rate) AS CumeDist,
       PERCENT_RANK() OVER (PARTITION BY Department ORDER BY Rate ) AS PctRank
FROM HumanResources.vEmployeeDepartmentHistory AS edh
    INNER JOIN HumanResources.EmployeePayHistory AS e
    ON e.BusinessEntityID = edh.BusinessEntityID
WHERE Department IN (N'Information Services',N'Document Control')
ORDER BY Department, Rate DESC;

Origin documentation for objectSchemaName

Syntax:

OBJECT_SCHEMA_NAME ( object_id [, database_id ] )

Returns the database schema name for schema-scoped objects. For a list of schema-scoped objects, see sys.objects (Transact-SQL) .

Example:

SELECT DISTINCT OBJECT_SCHEMA_NAME(object_id)
FROM master.sys.objects;

Example:

SELECT DISTINCT OBJECT_SCHEMA_NAME(object_id, 1) AS schema_name
FROM master.sys.objects;

Example:

SELECT DB_NAME(st.dbid) AS database_name,
    OBJECT_SCHEMA_NAME(st.objectid, st.dbid) AS schema_name,
    OBJECT_NAME(st.objectid, st.dbid) AS object_name,
    st.text AS query_statement
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE st.objectid IS NOT NULL;
GO

Example:

SELECT QUOTENAME(DB_NAME(database_id))
    + N'.'
    + QUOTENAME(OBJECT_SCHEMA_NAME(object_id, database_id))
    + N'.'
    + QUOTENAME(OBJECT_NAME(object_id, database_id))
    , *
FROM sys.dm_db_index_operational_stats(null, null, null, null);
GO

Origin documentation for objectSchemaName_DatabaseId

Syntax:

OBJECT_SCHEMA_NAME ( object_id [, database_id ] )

Returns the database schema name for schema-scoped objects. For a list of schema-scoped objects, see sys.objects (Transact-SQL) .

Example:

SELECT DISTINCT OBJECT_SCHEMA_NAME(object_id)
FROM master.sys.objects;

Example:

SELECT DISTINCT OBJECT_SCHEMA_NAME(object_id, 1) AS schema_name
FROM master.sys.objects;

Example:

SELECT DB_NAME(st.dbid) AS database_name,
    OBJECT_SCHEMA_NAME(st.objectid, st.dbid) AS schema_name,
    OBJECT_NAME(st.objectid, st.dbid) AS object_name,
    st.text AS query_statement
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE st.objectid IS NOT NULL;
GO

Example:

SELECT QUOTENAME(DB_NAME(database_id))
    + N'.'
    + QUOTENAME(OBJECT_SCHEMA_NAME(object_id, database_id))
    + N'.'
    + QUOTENAME(OBJECT_NAME(object_id, database_id))
    , *
FROM sys.dm_db_index_operational_stats(null, null, null, null);
GO

Origin documentation for suserSid

Syntax:

SUSER_SID ( [ 'login' ] [ , Param2 ] )

Returns the security identification number (SID) for the specified login name.

Example:

SELECT SUSER_SID();

Example:

SELECT SUSER_SID('sa');
GO

Example:

SELECT SUSER_SID('London\Workstation1');
GO

Example:

USE AdventureWorks2012;
GO
CREATE TABLE sid_example
(
login_sid   VARBINARY(85) DEFAULT SUSER_SID(),
login_name  VARCHAR(30) DEFAULT SYSTEM_USER,
login_dept  VARCHAR(10) DEFAULT 'SALES',
login_date  DATETIME DEFAULT GETDATE()
);
GO
INSERT sid_example DEFAULT VALUES;
GO

Example:

SELECT SUSER_SNAME(SUSER_SID('TestComputer\User', 0));

Origin documentation for suserSid_Param2

Syntax:

SUSER_SID ( [ 'login' ] [ , Param2 ] )

Returns the security identification number (SID) for the specified login name.

Example:

SELECT SUSER_SID();

Example:

SELECT SUSER_SID('sa');
GO

Example:

SELECT SUSER_SID('London\Workstation1');
GO

Example:

USE AdventureWorks2012;
GO
CREATE TABLE sid_example
(
login_sid   VARBINARY(85) DEFAULT SUSER_SID(),
login_name  VARCHAR(30) DEFAULT SYSTEM_USER,
login_dept  VARCHAR(10) DEFAULT 'SALES',
login_date  DATETIME DEFAULT GETDATE()
);
GO
INSERT sid_example DEFAULT VALUES;
GO

Example:

SELECT SUSER_SNAME(SUSER_SID('TestComputer\User', 0));

Origin documentation for suserSid_Login

Syntax:

SUSER_SID ( [ 'login' ] [ , Param2 ] )

Returns the security identification number (SID) for the specified login name.

Example:

SELECT SUSER_SID();

Example:

SELECT SUSER_SID('sa');
GO

Example:

SELECT SUSER_SID('London\Workstation1');
GO

Example:

USE AdventureWorks2012;
GO
CREATE TABLE sid_example
(
login_sid   VARBINARY(85) DEFAULT SUSER_SID(),
login_name  VARCHAR(30) DEFAULT SYSTEM_USER,
login_dept  VARCHAR(10) DEFAULT 'SALES',
login_date  DATETIME DEFAULT GETDATE()
);
GO
INSERT sid_example DEFAULT VALUES;
GO

Example:

SELECT SUSER_SNAME(SUSER_SID('TestComputer\User', 0));

Origin documentation for suserSid_Login_Param2

Syntax:

SUSER_SID ( [ 'login' ] [ , Param2 ] )

Returns the security identification number (SID) for the specified login name.

Example:

SELECT SUSER_SID();

Example:

SELECT SUSER_SID('sa');
GO

Example:

SELECT SUSER_SID('London\Workstation1');
GO

Example:

USE AdventureWorks2012;
GO
CREATE TABLE sid_example
(
login_sid   VARBINARY(85) DEFAULT SUSER_SID(),
login_name  VARCHAR(30) DEFAULT SYSTEM_USER,
login_dept  VARCHAR(10) DEFAULT 'SALES',
login_date  DATETIME DEFAULT GETDATE()
);
GO
INSERT sid_example DEFAULT VALUES;
GO

Example:

SELECT SUSER_SNAME(SUSER_SID('TestComputer\User', 0));

Origin documentation for decryptByKey

Syntax:

DecryptByKey ( { 'ciphertext' | @ciphertext }
    [ , add_authenticator, { authenticator | @authenticator } ] )

This function uses a symmetric key to decrypt data.

Note

This syntax is not supported by serverless SQL pool in Azure Synapse Analytics.

For dedicated SQL pools in Azure Synapse Analytics, result set caching should not be used in conjunction with DECRYPTBYKEY. If this cryptographic function must be used, ensure you have result set caching disabled (either at session-level or database-level ) at the time of execution.

Example:

-- First, open the symmetric key with which to decrypt the data.
OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037;
GO

-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.
SELECT NationalIDNumber, EncryptedNationalID
    AS 'Encrypted ID Number',
    CONVERT(nvarchar, DecryptByKey(EncryptedNationalID))
    AS 'Decrypted ID Number'
    FROM HumanResources.Employee;
GO

Example:

-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY CreditCards_Key11
   DECRYPTION BY CERTIFICATE Sales09;
GO

-- Now list the original card number, the encrypted card number,
-- and the decrypted ciphertext. If the decryption worked,
-- the original number will match the decrypted number.
SELECT CardNumber, CardNumber_Encrypted
    AS 'Encrypted card number', CONVERT(nvarchar,
    DecryptByKey(CardNumber_Encrypted, 1 ,
    HashBytes('SHA1', CONVERT(varbinary, CreditCardID))))
    AS 'Decrypted card number' FROM Sales.CreditCard;
GO

Example:

-- Create the database
CREATE DATABASE TestingDecryptByKey
GO

USE [TestingDecryptByKey]

-- Create the table and view
CREATE TABLE TestingDecryptByKey.dbo.Test(val VARBINARY(8000) NOT NULL);
GO
CREATE VIEW dbo.TestView AS SELECT CAST(DecryptByKey(val) AS VARCHAR(30)) AS DecryptedVal FROM TestingDecryptByKey.dbo.Test;
GO

-- Create the key, and certificate
USE TestingDecryptByKey;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ItIsreallyLong1AndSecured!Passsword#';
CREATE CERTIFICATE TestEncryptionCertificate WITH SUBJECT = 'TestEncryption';
CREATE SYMMETRIC KEY TestEncryptSymmmetricKey WITH ALGORITHM = AES_256, IDENTITY_VALUE = 'It is place for test',
KEY_SOURCE = 'It is source for test' ENCRYPTION BY CERTIFICATE TestEncryptionCertificate;

-- Insert rows into the table
DECLARE @var VARBINARY(8000), @Val VARCHAR(30);
SELECT @Val = '000-123-4567';
OPEN SYMMETRIC KEY TestEncryptSymmmetricKey DECRYPTION BY CERTIFICATE TestEncryptionCertificate;
SELECT @var = EncryptByKey(Key_GUID('TestEncryptSymmmetricKey'), @Val);
SELECT CAST(DecryptByKey(@var) AS VARCHAR(30)), @Val;
INSERT INTO dbo.Test VALUES(@var);
GO

-- Switch to master
USE [Master];
GO

-- Results show the date inserted
SELECT DecryptedVal FROM TestingDecryptByKey.dbo.TestView;

-- Results are NULL because we are not in the context of the TestingDecryptByKey Database
SELECT CAST(DecryptByKey(val) AS VARCHAR(30)) AS DecryptedVal FROM TestingDecryptByKey.dbo.Test;
GO

-- Clean up resources
USE TestingDecryptByKey;

DROP SYMMETRIC KEY TestEncryptSymmmetricKey REMOVE PROVIDER KEY;
DROP CERTIFICATE TestEncryptionCertificate;

Use [Master]
DROP DATABASE TestingDecryptByKey;
GO

Origin documentation for decryptByKey_AddAuthenticator_Authenticator

Syntax:

DecryptByKey ( { 'ciphertext' | @ciphertext }
    [ , add_authenticator, { authenticator | @authenticator } ] )

This function uses a symmetric key to decrypt data.

Note

This syntax is not supported by serverless SQL pool in Azure Synapse Analytics.

For dedicated SQL pools in Azure Synapse Analytics, result set caching should not be used in conjunction with DECRYPTBYKEY. If this cryptographic function must be used, ensure you have result set caching disabled (either at session-level or database-level ) at the time of execution.

Example:

-- First, open the symmetric key with which to decrypt the data.
OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037;
GO

-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.
SELECT NationalIDNumber, EncryptedNationalID
    AS 'Encrypted ID Number',
    CONVERT(nvarchar, DecryptByKey(EncryptedNationalID))
    AS 'Decrypted ID Number'
    FROM HumanResources.Employee;
GO

Example:

-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY CreditCards_Key11
   DECRYPTION BY CERTIFICATE Sales09;
GO

-- Now list the original card number, the encrypted card number,
-- and the decrypted ciphertext. If the decryption worked,
-- the original number will match the decrypted number.
SELECT CardNumber, CardNumber_Encrypted
    AS 'Encrypted card number', CONVERT(nvarchar,
    DecryptByKey(CardNumber_Encrypted, 1 ,
    HashBytes('SHA1', CONVERT(varbinary, CreditCardID))))
    AS 'Decrypted card number' FROM Sales.CreditCard;
GO

Example:

-- Create the database
CREATE DATABASE TestingDecryptByKey
GO

USE [TestingDecryptByKey]

-- Create the table and view
CREATE TABLE TestingDecryptByKey.dbo.Test(val VARBINARY(8000) NOT NULL);
GO
CREATE VIEW dbo.TestView AS SELECT CAST(DecryptByKey(val) AS VARCHAR(30)) AS DecryptedVal FROM TestingDecryptByKey.dbo.Test;
GO

-- Create the key, and certificate
USE TestingDecryptByKey;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ItIsreallyLong1AndSecured!Passsword#';
CREATE CERTIFICATE TestEncryptionCertificate WITH SUBJECT = 'TestEncryption';
CREATE SYMMETRIC KEY TestEncryptSymmmetricKey WITH ALGORITHM = AES_256, IDENTITY_VALUE = 'It is place for test',
KEY_SOURCE = 'It is source for test' ENCRYPTION BY CERTIFICATE TestEncryptionCertificate;

-- Insert rows into the table
DECLARE @var VARBINARY(8000), @Val VARCHAR(30);
SELECT @Val = '000-123-4567';
OPEN SYMMETRIC KEY TestEncryptSymmmetricKey DECRYPTION BY CERTIFICATE TestEncryptionCertificate;
SELECT @var = EncryptByKey(Key_GUID('TestEncryptSymmmetricKey'), @Val);
SELECT CAST(DecryptByKey(@var) AS VARCHAR(30)), @Val;
INSERT INTO dbo.Test VALUES(@var);
GO

-- Switch to master
USE [Master];
GO

-- Results show the date inserted
SELECT DecryptedVal FROM TestingDecryptByKey.dbo.TestView;

-- Results are NULL because we are not in the context of the TestingDecryptByKey Database
SELECT CAST(DecryptByKey(val) AS VARCHAR(30)) AS DecryptedVal FROM TestingDecryptByKey.dbo.Test;
GO

-- Clean up resources
USE TestingDecryptByKey;

DROP SYMMETRIC KEY TestEncryptSymmmetricKey REMOVE PROVIDER KEY;
DROP CERTIFICATE TestEncryptionCertificate;

Use [Master]
DROP DATABASE TestingDecryptByKey;
GO

Origin documentation for charIndex

Syntax:

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )

This function searches for one character expression inside a second character expression, returning the starting position of the first expression if found.

Example:

DECLARE @document VARCHAR(64);
SELECT @document = 'Reflectors are vital safety' +
                   ' components of your bicycle.';
SELECT CHARINDEX('bicycle', @document);
GO

Example:

DECLARE @document VARCHAR(64);

SELECT @document = 'Reflectors are vital safety' +
                   ' components of your bicycle.';
SELECT CHARINDEX('vital', @document, 5);
GO

Example:

DECLARE @document VARCHAR(64);

SELECT @document = 'Reflectors are vital safety' +
                   ' components of your bicycle.';
SELECT CHARINDEX('bike', @document);
GO

Example:

USE tempdb;
GO
--perform a case sensitive search
SELECT CHARINDEX ( 'TEST',
       'This is a Test'
       COLLATE Latin1_General_CS_AS);

Example:

USE tempdb;
GO
SELECT CHARINDEX ( 'Test',
       'This is a Test'
       COLLATE Latin1_General_CS_AS);

Example:

USE tempdb;
GO
SELECT CHARINDEX ( 'TEST',
       'This is a Test'
       COLLATE Latin1_General_CI_AS);
GO

Example:

SELECT CHARINDEX('is', 'This is a string');

Example:

SELECT CHARINDEX('is', 'This is a string', 4);

Example:

SELECT TOP(1) CHARINDEX('at', 'This is a string') FROM dbo.DimCustomer;

Origin documentation for charIndex_StartLocation

Syntax:

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )

This function searches for one character expression inside a second character expression, returning the starting position of the first expression if found.

Example:

DECLARE @document VARCHAR(64);
SELECT @document = 'Reflectors are vital safety' +
                   ' components of your bicycle.';
SELECT CHARINDEX('bicycle', @document);
GO

Example:

DECLARE @document VARCHAR(64);

SELECT @document = 'Reflectors are vital safety' +
                   ' components of your bicycle.';
SELECT CHARINDEX('vital', @document, 5);
GO

Example:

DECLARE @document VARCHAR(64);

SELECT @document = 'Reflectors are vital safety' +
                   ' components of your bicycle.';
SELECT CHARINDEX('bike', @document);
GO

Example:

USE tempdb;
GO
--perform a case sensitive search
SELECT CHARINDEX ( 'TEST',
       'This is a Test'
       COLLATE Latin1_General_CS_AS);

Example:

USE tempdb;
GO
SELECT CHARINDEX ( 'Test',
       'This is a Test'
       COLLATE Latin1_General_CS_AS);

Example:

USE tempdb;
GO
SELECT CHARINDEX ( 'TEST',
       'This is a Test'
       COLLATE Latin1_General_CI_AS);
GO

Example:

SELECT CHARINDEX('is', 'This is a string');

Example:

SELECT CHARINDEX('is', 'This is a string', 4);

Example:

SELECT TOP(1) CHARINDEX('at', 'This is a string') FROM dbo.DimCustomer;

Origin documentation for lag

Syntax:

LAG (scalar_expression [,offset] [,default])
    OVER ( [ partition_by_clause ] order_by_clause )

Accesses data from a previous row in the same result set without the use of a self-join starting with SQL Server 2012 (11.x). LAG provides access to a row at a given physical offset that comes before the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row.

Example:

USE AdventureWorks2012;
GO
SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,
       LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 AND YEAR(QuotaDate) IN ('2005','2006');

Example:

USE AdventureWorks2012;
GO
SELECT TerritoryName, BusinessEntityID, SalesYTD,
       LAG (SalesYTD, 1, 0) OVER (PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS PrevRepSales
FROM Sales.vSalesPerson
WHERE TerritoryName IN (N'Northwest', N'Canada')
ORDER BY TerritoryName;

Example:

CREATE TABLE T (a INT, b INT, c INT);
GO
INSERT INTO T VALUES (1, 1, -3), (2, 2, 4), (3, 1, NULL), (4, 3, 1), (5, 2, NULL), (6, 1, 5);

SELECT b, c,
    LAG(2*c, b*(SELECT MIN(b) FROM T), -c/2.0) OVER (ORDER BY a) AS i
FROM T;

Example:

-- Uses AdventureWorks

SELECT CalendarYear, CalendarQuarter, SalesAmountQuota AS SalesQuota,
       LAG(SalesAmountQuota,1,0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS PrevQuota,
       SalesAmountQuota - LAG(SalesAmountQuota,1,0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS Diff
FROM dbo.FactSalesQuota
WHERE EmployeeKey = 272 AND CalendarYear IN (2001, 2002)
ORDER BY CalendarYear, CalendarQuarter;

Origin documentation for lag_Offset

Syntax:

LAG (scalar_expression [,offset] [,default])
    OVER ( [ partition_by_clause ] order_by_clause )

Accesses data from a previous row in the same result set without the use of a self-join starting with SQL Server 2012 (11.x). LAG provides access to a row at a given physical offset that comes before the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row.

Example:

USE AdventureWorks2012;
GO
SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,
       LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 AND YEAR(QuotaDate) IN ('2005','2006');

Example:

USE AdventureWorks2012;
GO
SELECT TerritoryName, BusinessEntityID, SalesYTD,
       LAG (SalesYTD, 1, 0) OVER (PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS PrevRepSales
FROM Sales.vSalesPerson
WHERE TerritoryName IN (N'Northwest', N'Canada')
ORDER BY TerritoryName;

Example:

CREATE TABLE T (a INT, b INT, c INT);
GO
INSERT INTO T VALUES (1, 1, -3), (2, 2, 4), (3, 1, NULL), (4, 3, 1), (5, 2, NULL), (6, 1, 5);

SELECT b, c,
    LAG(2*c, b*(SELECT MIN(b) FROM T), -c/2.0) OVER (ORDER BY a) AS i
FROM T;

Example:

-- Uses AdventureWorks

SELECT CalendarYear, CalendarQuarter, SalesAmountQuota AS SalesQuota,
       LAG(SalesAmountQuota,1,0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS PrevQuota,
       SalesAmountQuota - LAG(SalesAmountQuota,1,0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS Diff
FROM dbo.FactSalesQuota
WHERE EmployeeKey = 272 AND CalendarYear IN (2001, 2002)
ORDER BY CalendarYear, CalendarQuarter;

Origin documentation for lag_Offset_Default

Syntax:

LAG (scalar_expression [,offset] [,default])
    OVER ( [ partition_by_clause ] order_by_clause )

Accesses data from a previous row in the same result set without the use of a self-join starting with SQL Server 2012 (11.x). LAG provides access to a row at a given physical offset that comes before the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row.

Example:

USE AdventureWorks2012;
GO
SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,
       LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 AND YEAR(QuotaDate) IN ('2005','2006');

Example:

USE AdventureWorks2012;
GO
SELECT TerritoryName, BusinessEntityID, SalesYTD,
       LAG (SalesYTD, 1, 0) OVER (PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS PrevRepSales
FROM Sales.vSalesPerson
WHERE TerritoryName IN (N'Northwest', N'Canada')
ORDER BY TerritoryName;

Example:

CREATE TABLE T (a INT, b INT, c INT);
GO
INSERT INTO T VALUES (1, 1, -3), (2, 2, 4), (3, 1, NULL), (4, 3, 1), (5, 2, NULL), (6, 1, 5);

SELECT b, c,
    LAG(2*c, b*(SELECT MIN(b) FROM T), -c/2.0) OVER (ORDER BY a) AS i
FROM T;

Example:

-- Uses AdventureWorks

SELECT CalendarYear, CalendarQuarter, SalesAmountQuota AS SalesQuota,
       LAG(SalesAmountQuota,1,0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS PrevQuota,
       SalesAmountQuota - LAG(SalesAmountQuota,1,0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS Diff
FROM dbo.FactSalesQuota
WHERE EmployeeKey = 272 AND CalendarYear IN (2001, 2002)
ORDER BY CalendarYear, CalendarQuarter;

Origin documentation for certprivateKey

Syntax:

CERTPRIVATEKEY
    (
          cert_ID
        , ' encryption_password '
      [ , ' decryption_password ' ]
    )

This function returns the private key of a certificate in binary format. This function takes three arguments.

Only users with access to the certificate private key can use this function. This function returns the private key in PVK format.

Example:

CREATE DATABASE TEST1;
GO
USE TEST1
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Use 5tr0ng P^55Words'
GO
CREATE CERTIFICATE Shipping04
WITH SUBJECT = 'Sammamish Shipping Records',
EXPIRY_DATE = '20401031';
GO
SELECT CERTPRIVATEKEY(CERT_ID('Shipping04'), 'jklalkaa/; uia3dd');

Origin documentation for certprivateKey_DecryptionPassword

Syntax:

CERTPRIVATEKEY
    (
          cert_ID
        , ' encryption_password '
      [ , ' decryption_password ' ]
    )

This function returns the private key of a certificate in binary format. This function takes three arguments.

Only users with access to the certificate private key can use this function. This function returns the private key in PVK format.

Example:

CREATE DATABASE TEST1;
GO
USE TEST1
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Use 5tr0ng P^55Words'
GO
CREATE CERTIFICATE Shipping04
WITH SUBJECT = 'Sammamish Shipping Records',
EXPIRY_DATE = '20401031';
GO
SELECT CERTPRIVATEKEY(CERT_ID('Shipping04'), 'jklalkaa/; uia3dd');

Origin documentation for cumeDist

Syntax:

CUME_DIST( )
    OVER ( [ partition_by_clause ] order_by_clause )

For SQL Server, this function calculates the cumulative distribution of a value within a group of values. In other words, CUME_DIST calculates the relative position of a specified value in a group of values. Assuming ascending ordering, the CUME_DIST of a value in row r is defined as the number of rows with values less than or equal to that value in row r , divided by the number of rows evaluated in the partition or query result set. CUME_DIST is similar to the PERCENT_RANK function.

Example:

USE AdventureWorks2012;
GO
SELECT Department, LastName, Rate,
       CUME_DIST () OVER (PARTITION BY Department ORDER BY Rate) AS CumeDist,
       PERCENT_RANK() OVER (PARTITION BY Department ORDER BY Rate ) AS PctRank
FROM HumanResources.vEmployeeDepartmentHistory AS edh
    INNER JOIN HumanResources.EmployeePayHistory AS e
    ON e.BusinessEntityID = edh.BusinessEntityID
WHERE Department IN (N'Information Services',N'Document Control')
ORDER BY Department, Rate DESC;

Origin documentation for dbId

Syntax:

DB_ID ( [ 'database_name' ] )

This function returns the database identification (ID) number of a specified database.

Example:

SELECT DB_ID() AS [Database ID];
GO

Example:

SELECT DB_ID(N'AdventureWorks2008R2') AS [Database ID];
GO

Example:

DECLARE @db_id INT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks2012');
SET @object_id = OBJECT_ID(N'AdventureWorks2012.Person.Address');
IF @db_id IS NULL
  BEGIN;
    PRINT N'Invalid database';
  END;
ELSE IF @object_id IS NULL
  BEGIN;
    PRINT N'Invalid object';
  END;
ELSE
  BEGIN;
    SELECT * FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL);
  END;
GO

Example:

SELECT DB_ID();

Example:

SELECT DB_ID('AdventureWorksPDW2012');

Origin documentation for dbId_DatabaseName

Syntax:

DB_ID ( [ 'database_name' ] )

This function returns the database identification (ID) number of a specified database.

Example:

SELECT DB_ID() AS [Database ID];
GO

Example:

SELECT DB_ID(N'AdventureWorks2008R2') AS [Database ID];
GO

Example:

DECLARE @db_id INT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks2012');
SET @object_id = OBJECT_ID(N'AdventureWorks2012.Person.Address');
IF @db_id IS NULL
  BEGIN;
    PRINT N'Invalid database';
  END;
ELSE IF @object_id IS NULL
  BEGIN;
    PRINT N'Invalid object';
  END;
ELSE
  BEGIN;
    SELECT * FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL);
  END;
GO

Example:

SELECT DB_ID();

Example:

SELECT DB_ID('AdventureWorksPDW2012');

Origin documentation for isMember_Group

Syntax:

IS_MEMBER ( { 'group' | 'role' } )

Indicates whether the current user is a member of the specified Microsoft Windows group or SQL Server database role. The IS_MEMBER function is not supported for Azure Active Directory Groups.

Example:

-- Test membership in db_owner and print appropriate message.
IF IS_MEMBER ('db_owner') = 1
   PRINT 'Current user is a member of the db_owner role'
ELSE IF IS_MEMBER ('db_owner') = 0
   PRINT 'Current user is NOT a member of the db_owner role'
ELSE IF IS_MEMBER ('db_owner') IS NULL
   PRINT 'ERROR: Invalid group / role specified';
GO

-- Execute SELECT if user is a member of ADVWORKS\Shipping.
IF IS_MEMBER ('ADVWORKS\Shipping') = 1
   SELECT 'User ' + USER + ' is a member of ADVWORKS\Shipping.';
GO

Origin documentation for isMember_Role

Syntax:

IS_MEMBER ( { 'group' | 'role' } )

Indicates whether the current user is a member of the specified Microsoft Windows group or SQL Server database role. The IS_MEMBER function is not supported for Azure Active Directory Groups.

Example:

-- Test membership in db_owner and print appropriate message.
IF IS_MEMBER ('db_owner') = 1
   PRINT 'Current user is a member of the db_owner role'
ELSE IF IS_MEMBER ('db_owner') = 0
   PRINT 'Current user is NOT a member of the db_owner role'
ELSE IF IS_MEMBER ('db_owner') IS NULL
   PRINT 'ERROR: Invalid group / role specified';
GO

-- Execute SELECT if user is a member of ADVWORKS\Shipping.
IF IS_MEMBER ('ADVWORKS\Shipping') = 1
   SELECT 'User ' + USER + ' is a member of ADVWORKS\Shipping.';
GO

Origin documentation for hasPermsByName

Syntax:

HAS_PERMS_BY_NAME ( securable , securable_class , permission
    [ , sub-securable ] [ , sub-securable_class ] )

Evaluates the effective permission of the current user on a securable. A related function is fn_my_permissions .

Example:

SELECT HAS_PERMS_BY_NAME(null, null, 'VIEW SERVER STATE');

Example:

SELECT HAS_PERMS_BY_NAME('Ps', 'LOGIN', 'IMPERSONATE');

Example:

SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'ANY');

Example:

EXECUTE AS user = 'Pd'
GO
SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'ANY');
GO
REVERT;
GO

Example:

SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'CREATE PROCEDURE')
    & HAS_PERMS_BY_NAME('S', 'SCHEMA', 'ALTER') AS _can_create_procs,
    HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'CREATE TABLE') &
    HAS_PERMS_BY_NAME('S', 'SCHEMA', 'ALTER') AS _can_create_tables;

Example:

SELECT HAS_PERMS_BY_NAME
(QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name),
    'OBJECT', 'SELECT') AS have_select, * FROM sys.tables

Example:

SELECT HAS_PERMS_BY_NAME('Sales.SalesPerson', 'OBJECT', 'INSERT');

Example:

SELECT HAS_PERMS_BY_NAME('AdventureWorks2012.Sales.SalesPerson',
    'OBJECT', 'INSERT');

Example:

SELECT name AS column_name,
    HAS_PERMS_BY_NAME('T', 'OBJECT', 'SELECT', name, 'COLUMN')
    AS can_select
    FROM sys.columns AS c
    WHERE c.object_id=object_id('T');

Origin documentation for hasPermsByName_SubSecurable

Syntax:

HAS_PERMS_BY_NAME ( securable , securable_class , permission
    [ , sub-securable ] [ , sub-securable_class ] )

Evaluates the effective permission of the current user on a securable. A related function is fn_my_permissions .

Example:

SELECT HAS_PERMS_BY_NAME(null, null, 'VIEW SERVER STATE');

Example:

SELECT HAS_PERMS_BY_NAME('Ps', 'LOGIN', 'IMPERSONATE');

Example:

SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'ANY');

Example:

EXECUTE AS user = 'Pd'
GO
SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'ANY');
GO
REVERT;
GO

Example:

SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'CREATE PROCEDURE')
    & HAS_PERMS_BY_NAME('S', 'SCHEMA', 'ALTER') AS _can_create_procs,
    HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'CREATE TABLE') &
    HAS_PERMS_BY_NAME('S', 'SCHEMA', 'ALTER') AS _can_create_tables;

Example:

SELECT HAS_PERMS_BY_NAME
(QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name),
    'OBJECT', 'SELECT') AS have_select, * FROM sys.tables

Example:

SELECT HAS_PERMS_BY_NAME('Sales.SalesPerson', 'OBJECT', 'INSERT');

Example:

SELECT HAS_PERMS_BY_NAME('AdventureWorks2012.Sales.SalesPerson',
    'OBJECT', 'INSERT');

Example:

SELECT name AS column_name,
    HAS_PERMS_BY_NAME('T', 'OBJECT', 'SELECT', name, 'COLUMN')
    AS can_select
    FROM sys.columns AS c
    WHERE c.object_id=object_id('T');

Origin documentation for hasPermsByName_SubSecurable_SubSecurableClass

Syntax:

HAS_PERMS_BY_NAME ( securable , securable_class , permission
    [ , sub-securable ] [ , sub-securable_class ] )

Evaluates the effective permission of the current user on a securable. A related function is fn_my_permissions .

Example:

SELECT HAS_PERMS_BY_NAME(null, null, 'VIEW SERVER STATE');

Example:

SELECT HAS_PERMS_BY_NAME('Ps', 'LOGIN', 'IMPERSONATE');

Example:

SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'ANY');

Example:

EXECUTE AS user = 'Pd'
GO
SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'ANY');
GO
REVERT;
GO

Example:

SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'CREATE PROCEDURE')
    & HAS_PERMS_BY_NAME('S', 'SCHEMA', 'ALTER') AS _can_create_procs,
    HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'CREATE TABLE') &
    HAS_PERMS_BY_NAME('S', 'SCHEMA', 'ALTER') AS _can_create_tables;

Example:

SELECT HAS_PERMS_BY_NAME
(QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name),
    'OBJECT', 'SELECT') AS have_select, * FROM sys.tables

Example:

SELECT HAS_PERMS_BY_NAME('Sales.SalesPerson', 'OBJECT', 'INSERT');

Example:

SELECT HAS_PERMS_BY_NAME('AdventureWorks2012.Sales.SalesPerson',
    'OBJECT', 'INSERT');

Example:

SELECT name AS column_name,
    HAS_PERMS_BY_NAME('T', 'OBJECT', 'SELECT', name, 'COLUMN')
    AS can_select
    FROM sys.columns AS c
    WHERE c.object_id=object_id('T');

Origin documentation for typeId

Syntax:

TYPE_ID ( [ schema_name ] type_name )

Returns the ID for a specified data type name.

Example:

USE tempdb;
GO
CREATE TYPE NewType FROM int;
GO
CREATE SCHEMA NewSchema;
GO
CREATE TYPE NewSchema.NewType FROM int;
GO
SELECT TYPE_ID('NewType') AS [1 Part Data Type ID],
       TYPE_ID('NewSchema.NewType') AS [2 Part Data Type ID];
GO

Example:

SELECT TYPE_NAME(TYPE_ID('datetime')) AS [TYPE_NAME]
    ,TYPE_ID('datetime') AS [TYPE_ID];
GO

Example:

SELECT TYPE_NAME(TYPE_ID('datetime')) AS typeName,
    TYPE_ID('datetime') AS typeID FROM table1;

Origin documentation for typeId_SchemaName

Syntax:

TYPE_ID ( [ schema_name ] type_name )

Returns the ID for a specified data type name.

Example:

USE tempdb;
GO
CREATE TYPE NewType FROM int;
GO
CREATE SCHEMA NewSchema;
GO
CREATE TYPE NewSchema.NewType FROM int;
GO
SELECT TYPE_ID('NewType') AS [1 Part Data Type ID],
       TYPE_ID('NewSchema.NewType') AS [2 Part Data Type ID];
GO

Example:

SELECT TYPE_NAME(TYPE_ID('datetime')) AS [TYPE_NAME]
    ,TYPE_ID('datetime') AS [TYPE_ID];
GO

Example:

SELECT TYPE_NAME(TYPE_ID('datetime')) AS typeName,
    TYPE_ID('datetime') AS typeID FROM table1;

Origin documentation for jsonArray

Syntax:

JSON_ARRAY ( [ <json_array_value> [,...n] ] [ <json_null_clause> ]  )

<json_array_value> ::= value_expression

<json_null_clause> ::=
	  NULL ON NULL
	| ABSENT ON NULL

Constructs JSON array text from zero or more expressions.

Example:

SELECT JSON_ARRAY();

Example:

SELECT JSON_ARRAY('a', 1, 'b', 2)

Example:

SELECT JSON_ARRAY('a', 1, 'b', NULL)

Example:

SELECT JSON_ARRAY('a', 1, NULL, 2 NULL ON NULL)

Example:

SELECT JSON_ARRAY('a', JSON_OBJECT('name':'value', 'type':1))

Example:

SELECT JSON_ARRAY('a', JSON_OBJECT('name':'value', 'type':1), JSON_ARRAY(1, null, 2 NULL ON NULL))

Example:

DECLARE @id_value nvarchar(64) = NEWID();
SELECT JSON_ARRAY(1, @id_value, (SELECT @@SPID));

Example:

SELECT s.session_id, JSON_ARRAY(s.host_name, s.program_name, s.client_interface_name)
FROM sys.dm_exec_sessions AS s
WHERE s.is_user_process = 1;

Origin documentation for symKeyProperty_KeyId_AlgorithmDesc

Syntax:

SYMKEYPROPERTY ( Key_ID , 'algorithm_desc' | 'string_sid' | 'sid' )

Returns the algorithm of a symmetric key created from an EKM module.

Example:

SELECT SYMKEYPROPERTY(256, 'algorithm_desc') AS Algorithm ;
GO

Origin documentation for symKeyProperty_StringSid

Syntax:

SYMKEYPROPERTY ( Key_ID , 'algorithm_desc' | 'string_sid' | 'sid' )

Returns the algorithm of a symmetric key created from an EKM module.

Example:

SELECT SYMKEYPROPERTY(256, 'algorithm_desc') AS Algorithm ;
GO

Origin documentation for symKeyProperty_Sid

Syntax:

SYMKEYPROPERTY ( Key_ID , 'algorithm_desc' | 'string_sid' | 'sid' )

Returns the algorithm of a symmetric key created from an EKM module.

Example:

SELECT SYMKEYPROPERTY(256, 'algorithm_desc') AS Algorithm ;
GO

Origin documentation for permissions

Syntax:

PERMISSIONS ( [ objectid [ , 'column' ] ] )

Returns a value containing a bitmap that indicates the statement, object, or column permissions of the current user.

Important

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use fn_my_permissions and Has_Perms_By_Name instead. Continued use of the PERMISSIONS function may result in slower performance.

Example:

IF PERMISSIONS()&2=2
   CREATE TABLE test_table (col1 INT)
ELSE
   PRINT 'ERROR: The current user cannot create a table.';

Example:

IF PERMISSIONS(OBJECT_ID('AdventureWorks2012.Person.Address','U'))&8=8
   PRINT 'The current user can insert data into Person.Address.'
ELSE
   PRINT 'ERROR: The current user cannot insert data into Person.Address.';

Example:

IF PERMISSIONS(OBJECT_ID('AdventureWorks2012.Person.Address','U'))&0x80000=0x80000
   PRINT 'INSERT on Person.Address is grantable.'
ELSE
   PRINT 'You may not GRANT INSERT permissions on Person.Address.';

Origin documentation for permissions_Objectid

Syntax:

PERMISSIONS ( [ objectid [ , 'column' ] ] )

Returns a value containing a bitmap that indicates the statement, object, or column permissions of the current user.

Important

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use fn_my_permissions and Has_Perms_By_Name instead. Continued use of the PERMISSIONS function may result in slower performance.

Example:

IF PERMISSIONS()&2=2
   CREATE TABLE test_table (col1 INT)
ELSE
   PRINT 'ERROR: The current user cannot create a table.';

Example:

IF PERMISSIONS(OBJECT_ID('AdventureWorks2012.Person.Address','U'))&8=8
   PRINT 'The current user can insert data into Person.Address.'
ELSE
   PRINT 'ERROR: The current user cannot insert data into Person.Address.';

Example:

IF PERMISSIONS(OBJECT_ID('AdventureWorks2012.Person.Address','U'))&0x80000=0x80000
   PRINT 'INSERT on Person.Address is grantable.'
ELSE
   PRINT 'You may not GRANT INSERT permissions on Person.Address.';

Origin documentation for permissions_Objectid_Column

Syntax:

PERMISSIONS ( [ objectid [ , 'column' ] ] )

Returns a value containing a bitmap that indicates the statement, object, or column permissions of the current user.

Important

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use fn_my_permissions and Has_Perms_By_Name instead. Continued use of the PERMISSIONS function may result in slower performance.

Example:

IF PERMISSIONS()&2=2
   CREATE TABLE test_table (col1 INT)
ELSE
   PRINT 'ERROR: The current user cannot create a table.';

Example:

IF PERMISSIONS(OBJECT_ID('AdventureWorks2012.Person.Address','U'))&8=8
   PRINT 'The current user can insert data into Person.Address.'
ELSE
   PRINT 'ERROR: The current user cannot insert data into Person.Address.';

Example:

IF PERMISSIONS(OBJECT_ID('AdventureWorks2012.Person.Address','U'))&0x80000=0x80000
   PRINT 'INSERT on Person.Address is grantable.'
ELSE
   PRINT 'You may not GRANT INSERT permissions on Person.Address.';

Origin documentation for isJson

Syntax:

ISJSON ( expression [, json_type_constraint] )

Tests whether a string contains valid JSON.

Example:

DECLARE @param <data type>
SET @param = <value>

IF (ISJSON(@param) > 0)
BEGIN
     -- Do something with the valid JSON value of @param.
END

Example:

SELECT id, json_col
FROM tab1
WHERE ISJSON(json_col) = 1

Example:

SELECT id, json_col
FROM tab1
WHERE ISJSON(json_col, SCALAR) = 1

Example:

SELECT ISJSON('true', VALUE)

Example:

SELECT ISJSON('test string', VALUE)

Example:

SELECT ISJSON('"test string"', SCALAR)

Origin documentation for isJson_JsonTypeConstraint

Syntax:

ISJSON ( expression [, json_type_constraint] )

Tests whether a string contains valid JSON.

Example:

DECLARE @param <data type>
SET @param = <value>

IF (ISJSON(@param) > 0)
BEGIN
     -- Do something with the valid JSON value of @param.
END

Example:

SELECT id, json_col
FROM tab1
WHERE ISJSON(json_col) = 1

Example:

SELECT id, json_col
FROM tab1
WHERE ISJSON(json_col, SCALAR) = 1

Example:

SELECT ISJSON('true', VALUE)

Example:

SELECT ISJSON('test string', VALUE)

Example:

SELECT ISJSON('"test string"', SCALAR)

Origin documentation for userName

Syntax:

USER_NAME ( [ id ] )

Returns a database user name from a specified identification number.

Example:

SELECT USER_NAME(13);
GO

Example:

SELECT USER_NAME();
GO

Example:

SELECT name FROM sysusers WHERE name = USER_NAME(1);
GO

Example:

SELECT USER_NAME();
GO
EXECUTE AS USER = 'Zelig';
GO
SELECT USER_NAME();
GO
REVERT;
GO
SELECT USER_NAME();
GO

Example:

SELECT USER_NAME();

Example:

SELECT name FROM sysusers WHERE name = USER_NAME(1);

Origin documentation for userName_Id

Syntax:

USER_NAME ( [ id ] )

Returns a database user name from a specified identification number.

Example:

SELECT USER_NAME(13);
GO

Example:

SELECT USER_NAME();
GO

Example:

SELECT name FROM sysusers WHERE name = USER_NAME(1);
GO

Example:

SELECT USER_NAME();
GO
EXECUTE AS USER = 'Zelig';
GO
SELECT USER_NAME();
GO
REVERT;
GO
SELECT USER_NAME();
GO

Example:

SELECT USER_NAME();

Example:

SELECT name FROM sysusers WHERE name = USER_NAME(1);

Origin documentation for countBig

Syntax:

-- Aggregation Function Syntax
COUNT_BIG ( { [ [ ALL | DISTINCT ] expression ] | * } )

-- Analytic Function Syntax
COUNT_BIG ( [ ALL ] { expression | * } ) OVER ( [ <partition_by_clause> ] )

This function returns the number of items found in a group. COUNT_BIG operates like the COUNT function. These functions differ only in the data types of their return values. COUNT_BIG always returns a bigint data type value. COUNT always returns an int data type value.

Origin documentation for countBig_Distinctness

Syntax:

-- Aggregation Function Syntax
COUNT_BIG ( { [ [ ALL | DISTINCT ] expression ] | * } )

-- Analytic Function Syntax
COUNT_BIG ( [ ALL ] { expression | * } ) OVER ( [ <partition_by_clause> ] )

This function returns the number of items found in a group. COUNT_BIG operates like the COUNT function. These functions differ only in the data types of their return values. COUNT_BIG always returns a bigint data type value. COUNT always returns an int data type value.

Origin documentation for countBig_STAR

Syntax:

-- Aggregation Function Syntax
COUNT_BIG ( { [ [ ALL | DISTINCT ] expression ] | * } )

-- Analytic Function Syntax
COUNT_BIG ( [ ALL ] { expression | * } ) OVER ( [ <partition_by_clause> ] )

This function returns the number of items found in a group. COUNT_BIG operates like the COUNT function. These functions differ only in the data types of their return values. COUNT_BIG always returns a bigint data type value. COUNT always returns an int data type value.

Origin documentation for userId

Syntax:

USER_ID ( [ 'user' ] )

Returns the identification number for a database user.

Important

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use DATABASE_PRINCIPAL_ID instead.

Example:

USE AdventureWorks2012;
SELECT USER_ID('Harold');
GO

Origin documentation for userId_User

Syntax:

USER_ID ( [ 'user' ] )

Returns the identification number for a database user.

Important

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use DATABASE_PRINCIPAL_ID instead.

Example:

USE AdventureWorks2012;
SELECT USER_ID('Harold');
GO

Origin documentation for isSrvroleMember

Syntax:

IS_SRVROLEMEMBER ( 'role' [ , 'login' ] )

Indicates whether a SQL Server login is a member of the specified server role.

Example:

IF IS_SRVROLEMEMBER ('sysadmin') = 1
   print 'Current user''s login is a member of the sysadmin role'
ELSE IF IS_SRVROLEMEMBER ('sysadmin') = 0
   print 'Current user''s login is NOT a member of the sysadmin role'
ELSE IF IS_SRVROLEMEMBER ('sysadmin') IS NULL
   print 'ERROR: The server role specified is not valid.';

Example:

SELECT IS_SRVROLEMEMBER('diskadmin', 'Contoso\Pat');

Origin documentation for isSrvroleMember_Login

Syntax:

IS_SRVROLEMEMBER ( 'role' [ , 'login' ] )

Indicates whether a SQL Server login is a member of the specified server role.

Example:

IF IS_SRVROLEMEMBER ('sysadmin') = 1
   print 'Current user''s login is a member of the sysadmin role'
ELSE IF IS_SRVROLEMEMBER ('sysadmin') = 0
   print 'Current user''s login is NOT a member of the sysadmin role'
ELSE IF IS_SRVROLEMEMBER ('sysadmin') IS NULL
   print 'ERROR: The server role specified is not valid.';

Example:

SELECT IS_SRVROLEMEMBER('diskadmin', 'Contoso\Pat');

Origin documentation for keyName_Ciphertext

Syntax:

KEY_NAME ( ciphertext | key_guid )

Returns the name of the symmetric key from either a symmetric key GUID or cipher text.

Example:

USE master;
GO
DECLARE @guid uniqueidentifier ;
SELECT @guid = key_guid FROM sys.symmetric_keys
WHERE name = '##MS_ServiceMasterKey##' ;
-- Demonstration of passing a GUID to KEY_NAME to receive a name
SELECT KEY_NAME(@guid) AS [Name of Key];

Example:

-- Create a symmetric key
CREATE SYMMETRIC KEY TestSymKey
   WITH ALGORITHM = AES_128,
   KEY_SOURCE = 'The square of the hypotenuse is equal to the sum of the squares of the sides',
   IDENTITY_VALUE = 'Pythagoras'
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y' ;
GO
-- Create a table for the demonstration
CREATE TABLE DemoKey
(IDCol INT IDENTITY PRIMARY KEY,
SecretCol VARBINARY(256) NOT NULL)
GO
-- Open the symmetric key if not already open
OPEN SYMMETRIC KEY TestSymKey
    DECRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y';
GO
-- Insert a row into the DemoKey table
DECLARE @key_GUID uniqueidentifier;
SELECT @key_GUID = key_guid FROM sys.symmetric_keys
WHERE name LIKE 'TestSymKey' ;
INSERT INTO DemoKey(SecretCol)
VALUES ( ENCRYPTBYKEY (@key_GUID, 'EncryptedText'))
GO
-- Verify the DemoKey data
SELECT * FROM DemoKey;
GO
-- Decrypt the data
DECLARE @ciphertext VARBINARY(256);
SELECT @ciphertext = SecretCol
FROM DemoKey WHERE IDCol = 1 ;
SELECT CAST (
DECRYPTBYKEY( @ciphertext)
AS VARCHAR(100) ) AS SecretText ;
-- Use KEY_NAME to view the name of the key
SELECT KEY_NAME(@ciphertext) AS [Name of Key] ;

Origin documentation for keyName_KeyGuid

Syntax:

KEY_NAME ( ciphertext | key_guid )

Returns the name of the symmetric key from either a symmetric key GUID or cipher text.

Example:

USE master;
GO
DECLARE @guid uniqueidentifier ;
SELECT @guid = key_guid FROM sys.symmetric_keys
WHERE name = '##MS_ServiceMasterKey##' ;
-- Demonstration of passing a GUID to KEY_NAME to receive a name
SELECT KEY_NAME(@guid) AS [Name of Key];

Example:

-- Create a symmetric key
CREATE SYMMETRIC KEY TestSymKey
   WITH ALGORITHM = AES_128,
   KEY_SOURCE = 'The square of the hypotenuse is equal to the sum of the squares of the sides',
   IDENTITY_VALUE = 'Pythagoras'
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y' ;
GO
-- Create a table for the demonstration
CREATE TABLE DemoKey
(IDCol INT IDENTITY PRIMARY KEY,
SecretCol VARBINARY(256) NOT NULL)
GO
-- Open the symmetric key if not already open
OPEN SYMMETRIC KEY TestSymKey
    DECRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y';
GO
-- Insert a row into the DemoKey table
DECLARE @key_GUID uniqueidentifier;
SELECT @key_GUID = key_guid FROM sys.symmetric_keys
WHERE name LIKE 'TestSymKey' ;
INSERT INTO DemoKey(SecretCol)
VALUES ( ENCRYPTBYKEY (@key_GUID, 'EncryptedText'))
GO
-- Verify the DemoKey data
SELECT * FROM DemoKey;
GO
-- Decrypt the data
DECLARE @ciphertext VARBINARY(256);
SELECT @ciphertext = SecretCol
FROM DemoKey WHERE IDCol = 1 ;
SELECT CAST (
DECRYPTBYKEY( @ciphertext)
AS VARCHAR(100) ) AS SecretText ;
-- Use KEY_NAME to view the name of the key
SELECT KEY_NAME(@ciphertext) AS [Name of Key] ;

Origin documentation for jsonObject

Syntax:

JSON_OBJECT ( [ <json_key_value> [,...n] ] [ json_null_clause ] )

<json_key_value> ::= json_key_name : value_expression

<json_null_clause> ::=
	  NULL ON NULL
	| ABSENT ON NULL

Constructs JSON object text from zero or more expressions.

Example:

SELECT JSON_OBJECT();

Example:

SELECT JSON_OBJECT('name':'value', 'type':1)

Example:

SELECT JSON_OBJECT('name':'value', 'type':NULL ABSENT ON NULL)

Example:

SELECT JSON_OBJECT('name':'value', 'type':JSON_ARRAY(1, 2))

Example:

SELECT JSON_OBJECT('name':'value', 'type':JSON_OBJECT('type_id':1, 'name':'a'))

Example:

DECLARE @id_key nvarchar(10) = N'id',@id_value nvarchar(64) = NEWID();
SELECT JSON_OBJECT('user_name':USER_NAME(), @id_key:@id_value, 'sid':(SELECT @@SPID))

Example:

SELECT s.session_id, JSON_OBJECT('security_id':s.security_id, 'login':s.login_name, 'status':s.status) as info
FROM sys.dm_exec_sessions AS s
WHERE s.is_user_process = 1;

Origin documentation for objectId

Syntax:

OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . ]
  object_name' [ ,'object_type' ] )

Returns the database object identification number of a schema-scoped object.

Important

Objects that are not schema-scoped, such as DDL triggers, cannot be queried by using OBJECT_ID. For objects that are not found in the sys.objects catalog view, obtain the object identification numbers by querying the appropriate catalog view. For example, to return the object identification number of a DDL trigger, use SELECT OBJECT_ID FROM sys.triggers WHERE name = DatabaseTriggerLog`' .

Example:

USE master;
GO
SELECT OBJECT_ID(N'AdventureWorks2012.Production.WorkOrder') AS 'Object ID';
GO

Example:

USE AdventureWorks2012;
GO
IF OBJECT_ID (N'dbo.AWBuildVersion', N'U') IS NOT NULL
DROP TABLE dbo.AWBuildVersion;
GO

Example:

DECLARE @db_id INT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks2012');
SET @object_id = OBJECT_ID(N'AdventureWorks2012.Person.Address');
IF @db_id IS NULL
  BEGIN;
    PRINT N'Invalid database';
  END;
ELSE IF @object_id IS NULL
  BEGIN;
    PRINT N'Invalid object';
  END;
ELSE
  BEGIN;
    SELECT * FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL);
  END;
GO

Example:

SELECT OBJECT_ID('AdventureWorksPDW2012.dbo.FactFinance') AS 'Object ID';

Origin documentation for objectId_ObjectType

Syntax:

OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . ]
  object_name' [ ,'object_type' ] )

Returns the database object identification number of a schema-scoped object.

Important

Objects that are not schema-scoped, such as DDL triggers, cannot be queried by using OBJECT_ID. For objects that are not found in the sys.objects catalog view, obtain the object identification numbers by querying the appropriate catalog view. For example, to return the object identification number of a DDL trigger, use SELECT OBJECT_ID FROM sys.triggers WHERE name = DatabaseTriggerLog`' .

Example:

USE master;
GO
SELECT OBJECT_ID(N'AdventureWorks2012.Production.WorkOrder') AS 'Object ID';
GO

Example:

USE AdventureWorks2012;
GO
IF OBJECT_ID (N'dbo.AWBuildVersion', N'U') IS NOT NULL
DROP TABLE dbo.AWBuildVersion;
GO

Example:

DECLARE @db_id INT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks2012');
SET @object_id = OBJECT_ID(N'AdventureWorks2012.Person.Address');
IF @db_id IS NULL
  BEGIN;
    PRINT N'Invalid database';
  END;
ELSE IF @object_id IS NULL
  BEGIN;
    PRINT N'Invalid object';
  END;
ELSE
  BEGIN;
    SELECT * FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL);
  END;
GO

Example:

SELECT OBJECT_ID('AdventureWorksPDW2012.dbo.FactFinance') AS 'Object ID';

Origin documentation for objectId_DatabaseName

Syntax:

OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . ]
  object_name' [ ,'object_type' ] )

Returns the database object identification number of a schema-scoped object.

Important

Objects that are not schema-scoped, such as DDL triggers, cannot be queried by using OBJECT_ID. For objects that are not found in the sys.objects catalog view, obtain the object identification numbers by querying the appropriate catalog view. For example, to return the object identification number of a DDL trigger, use SELECT OBJECT_ID FROM sys.triggers WHERE name = DatabaseTriggerLog`' .

Example:

USE master;
GO
SELECT OBJECT_ID(N'AdventureWorks2012.Production.WorkOrder') AS 'Object ID';
GO

Example:

USE AdventureWorks2012;
GO
IF OBJECT_ID (N'dbo.AWBuildVersion', N'U') IS NOT NULL
DROP TABLE dbo.AWBuildVersion;
GO

Example:

DECLARE @db_id INT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks2012');
SET @object_id = OBJECT_ID(N'AdventureWorks2012.Person.Address');
IF @db_id IS NULL
  BEGIN;
    PRINT N'Invalid database';
  END;
ELSE IF @object_id IS NULL
  BEGIN;
    PRINT N'Invalid object';
  END;
ELSE
  BEGIN;
    SELECT * FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL);
  END;
GO

Example:

SELECT OBJECT_ID('AdventureWorksPDW2012.dbo.FactFinance') AS 'Object ID';

Origin documentation for objectId_DatabaseName_ObjectType

Syntax:

OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . ]
  object_name' [ ,'object_type' ] )

Returns the database object identification number of a schema-scoped object.

Important

Objects that are not schema-scoped, such as DDL triggers, cannot be queried by using OBJECT_ID. For objects that are not found in the sys.objects catalog view, obtain the object identification numbers by querying the appropriate catalog view. For example, to return the object identification number of a DDL trigger, use SELECT OBJECT_ID FROM sys.triggers WHERE name = DatabaseTriggerLog`' .

Example:

USE master;
GO
SELECT OBJECT_ID(N'AdventureWorks2012.Production.WorkOrder') AS 'Object ID';
GO

Example:

USE AdventureWorks2012;
GO
IF OBJECT_ID (N'dbo.AWBuildVersion', N'U') IS NOT NULL
DROP TABLE dbo.AWBuildVersion;
GO

Example:

DECLARE @db_id INT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks2012');
SET @object_id = OBJECT_ID(N'AdventureWorks2012.Person.Address');
IF @db_id IS NULL
  BEGIN;
    PRINT N'Invalid database';
  END;
ELSE IF @object_id IS NULL
  BEGIN;
    PRINT N'Invalid object';
  END;
ELSE
  BEGIN;
    SELECT * FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL);
  END;
GO

Example:

SELECT OBJECT_ID('AdventureWorksPDW2012.dbo.FactFinance') AS 'Object ID';

Origin documentation for objectId_DatabaseName_SchemaName

Syntax:

OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . ]
  object_name' [ ,'object_type' ] )

Returns the database object identification number of a schema-scoped object.

Important

Objects that are not schema-scoped, such as DDL triggers, cannot be queried by using OBJECT_ID. For objects that are not found in the sys.objects catalog view, obtain the object identification numbers by querying the appropriate catalog view. For example, to return the object identification number of a DDL trigger, use SELECT OBJECT_ID FROM sys.triggers WHERE name = DatabaseTriggerLog`' .

Example:

USE master;
GO
SELECT OBJECT_ID(N'AdventureWorks2012.Production.WorkOrder') AS 'Object ID';
GO

Example:

USE AdventureWorks2012;
GO
IF OBJECT_ID (N'dbo.AWBuildVersion', N'U') IS NOT NULL
DROP TABLE dbo.AWBuildVersion;
GO

Example:

DECLARE @db_id INT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks2012');
SET @object_id = OBJECT_ID(N'AdventureWorks2012.Person.Address');
IF @db_id IS NULL
  BEGIN;
    PRINT N'Invalid database';
  END;
ELSE IF @object_id IS NULL
  BEGIN;
    PRINT N'Invalid object';
  END;
ELSE
  BEGIN;
    SELECT * FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL);
  END;
GO

Example:

SELECT OBJECT_ID('AdventureWorksPDW2012.dbo.FactFinance') AS 'Object ID';

Origin documentation for objectId_DatabaseName_SchemaName_ObjectType

Syntax:

OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . ]
  object_name' [ ,'object_type' ] )

Returns the database object identification number of a schema-scoped object.

Important

Objects that are not schema-scoped, such as DDL triggers, cannot be queried by using OBJECT_ID. For objects that are not found in the sys.objects catalog view, obtain the object identification numbers by querying the appropriate catalog view. For example, to return the object identification number of a DDL trigger, use SELECT OBJECT_ID FROM sys.triggers WHERE name = DatabaseTriggerLog`' .

Example:

USE master;
GO
SELECT OBJECT_ID(N'AdventureWorks2012.Production.WorkOrder') AS 'Object ID';
GO

Example:

USE AdventureWorks2012;
GO
IF OBJECT_ID (N'dbo.AWBuildVersion', N'U') IS NOT NULL
DROP TABLE dbo.AWBuildVersion;
GO

Example:

DECLARE @db_id INT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks2012');
SET @object_id = OBJECT_ID(N'AdventureWorks2012.Person.Address');
IF @db_id IS NULL
  BEGIN;
    PRINT N'Invalid database';
  END;
ELSE IF @object_id IS NULL
  BEGIN;
    PRINT N'Invalid object';
  END;
ELSE
  BEGIN;
    SELECT * FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL);
  END;
GO

Example:

SELECT OBJECT_ID('AdventureWorksPDW2012.dbo.FactFinance') AS 'Object ID';

Origin documentation for objectId_SchemaName

Syntax:

OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . ]
  object_name' [ ,'object_type' ] )

Returns the database object identification number of a schema-scoped object.

Important

Objects that are not schema-scoped, such as DDL triggers, cannot be queried by using OBJECT_ID. For objects that are not found in the sys.objects catalog view, obtain the object identification numbers by querying the appropriate catalog view. For example, to return the object identification number of a DDL trigger, use SELECT OBJECT_ID FROM sys.triggers WHERE name = DatabaseTriggerLog`' .

Example:

USE master;
GO
SELECT OBJECT_ID(N'AdventureWorks2012.Production.WorkOrder') AS 'Object ID';
GO

Example:

USE AdventureWorks2012;
GO
IF OBJECT_ID (N'dbo.AWBuildVersion', N'U') IS NOT NULL
DROP TABLE dbo.AWBuildVersion;
GO

Example:

DECLARE @db_id INT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks2012');
SET @object_id = OBJECT_ID(N'AdventureWorks2012.Person.Address');
IF @db_id IS NULL
  BEGIN;
    PRINT N'Invalid database';
  END;
ELSE IF @object_id IS NULL
  BEGIN;
    PRINT N'Invalid object';
  END;
ELSE
  BEGIN;
    SELECT * FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL);
  END;
GO

Example:

SELECT OBJECT_ID('AdventureWorksPDW2012.dbo.FactFinance') AS 'Object ID';

Origin documentation for objectId_SchemaName_ObjectType

Syntax:

OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . ]
  object_name' [ ,'object_type' ] )

Returns the database object identification number of a schema-scoped object.

Important

Objects that are not schema-scoped, such as DDL triggers, cannot be queried by using OBJECT_ID. For objects that are not found in the sys.objects catalog view, obtain the object identification numbers by querying the appropriate catalog view. For example, to return the object identification number of a DDL trigger, use SELECT OBJECT_ID FROM sys.triggers WHERE name = DatabaseTriggerLog`' .

Example:

USE master;
GO
SELECT OBJECT_ID(N'AdventureWorks2012.Production.WorkOrder') AS 'Object ID';
GO

Example:

USE AdventureWorks2012;
GO
IF OBJECT_ID (N'dbo.AWBuildVersion', N'U') IS NOT NULL
DROP TABLE dbo.AWBuildVersion;
GO

Example:

DECLARE @db_id INT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks2012');
SET @object_id = OBJECT_ID(N'AdventureWorks2012.Person.Address');
IF @db_id IS NULL
  BEGIN;
    PRINT N'Invalid database';
  END;
ELSE IF @object_id IS NULL
  BEGIN;
    PRINT N'Invalid object';
  END;
ELSE
  BEGIN;
    SELECT * FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL);
  END;
GO

Example:

SELECT OBJECT_ID('AdventureWorksPDW2012.dbo.FactFinance') AS 'Object ID';

Origin documentation for suserSName

Syntax:

SUSER_SNAME ( [ server_user_sid ] )

Returns the login name associated with a security identification number (SID).

Example:

SELECT SUSER_SNAME();
GO

Example:

SELECT SUSER_SNAME(0x010500000000000515000000a065cf7e784b9b5fe77c87705a2e0000);
GO

Example:

USE AdventureWorks2012;
GO
CREATE TABLE sname_example
(
login_sname sysname DEFAULT SUSER_SNAME(),
employee_id uniqueidentifier DEFAULT NEWID(),
login_date  datetime DEFAULT GETDATE()
);
GO
INSERT sname_example DEFAULT VALUES;
GO

Example:

SELECT SUSER_SNAME();
GO
EXECUTE AS LOGIN = 'WanidaBenShoof';
SELECT SUSER_SNAME();
REVERT;
GO
SELECT SUSER_SNAME();
GO

Example:

SELECT SUSER_SNAME(0x01);
GO

Example:

SELECT SUSER_SNAME() AS CurrentLogin;
GO

Origin documentation for suserSName_ServerUserSid

Syntax:

SUSER_SNAME ( [ server_user_sid ] )

Returns the login name associated with a security identification number (SID).

Example:

SELECT SUSER_SNAME();
GO

Example:

SELECT SUSER_SNAME(0x010500000000000515000000a065cf7e784b9b5fe77c87705a2e0000);
GO

Example:

USE AdventureWorks2012;
GO
CREATE TABLE sname_example
(
login_sname sysname DEFAULT SUSER_SNAME(),
employee_id uniqueidentifier DEFAULT NEWID(),
login_date  datetime DEFAULT GETDATE()
);
GO
INSERT sname_example DEFAULT VALUES;
GO

Example:

SELECT SUSER_SNAME();
GO
EXECUTE AS LOGIN = 'WanidaBenShoof';
SELECT SUSER_SNAME();
REVERT;
GO
SELECT SUSER_SNAME();
GO

Example:

SELECT SUSER_SNAME(0x01);
GO

Example:

SELECT SUSER_SNAME() AS CurrentLogin;
GO

Origin documentation for format

Syntax:

FORMAT( value, format [, culture ] )

Returns a value formatted with the specified format and optional culture. Use the FORMAT function for locale-aware formatting of date/time and number values as strings. For general data type conversions, use CAST or CONVERT.

Example:

DECLARE @d DATE = '11/22/2020';
SELECT FORMAT( @d, 'd', 'en-US' ) 'US English'
      ,FORMAT( @d, 'd', 'en-gb' ) 'British English'
      ,FORMAT( @d, 'd', 'de-de' ) 'German'
      ,FORMAT( @d, 'd', 'zh-cn' ) 'Chinese Simplified (PRC)';

SELECT FORMAT( @d, 'D', 'en-US' ) 'US English'
      ,FORMAT( @d, 'D', 'en-gb' ) 'British English'
      ,FORMAT( @d, 'D', 'de-de' ) 'German'
      ,FORMAT( @d, 'D', 'zh-cn' ) 'Chinese Simplified (PRC)';

Example:

DECLARE @d DATE = GETDATE();
SELECT FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS 'Date'
       ,FORMAT(123456789,'###-##-####') AS 'Custom Number';

Example:

SELECT TOP(5) CurrencyRateID, EndOfDayRate
            ,FORMAT(EndOfDayRate, 'N', 'en-us') AS 'Numeric Format'
            ,FORMAT(EndOfDayRate, 'G', 'en-us') AS 'General Format'
            ,FORMAT(EndOfDayRate, 'C', 'en-us') AS 'Currency Format'
FROM Sales.CurrencyRate
ORDER BY CurrencyRateID;

Example:

SELECT TOP(5) CurrencyRateID, EndOfDayRate
      ,FORMAT(EndOfDayRate, 'N', 'de-de') AS 'Numeric Format'
      ,FORMAT(EndOfDayRate, 'G', 'de-de') AS 'General Format'
      ,FORMAT(EndOfDayRate, 'C', 'de-de') AS 'Currency Format'
FROM Sales.CurrencyRate
ORDER BY CurrencyRateID;

Example:

SELECT FORMAT(cast('07:35' as time), N'hh.mm');   --> returns NULL
SELECT FORMAT(cast('07:35' as time), N'hh:mm');   --> returns NULL

Example:

SELECT FORMAT(cast('07:35' as time), N'hh\.mm');  --> returns 07.35
SELECT FORMAT(cast('07:35' as time), N'hh\:mm');  --> returns 07:35

Example:

SELECT FORMAT(SYSDATETIME(), N'hh:mm tt'); -- returns 03:46 PM
SELECT FORMAT(SYSDATETIME(), N'hh:mm t'); -- returns 03:46 P

Example:

select FORMAT(CAST('2018-01-01 01:00' AS datetime2), N'hh:mm tt') -- returns 01:00 AM
select FORMAT(CAST('2018-01-01 01:00' AS datetime2), N'hh:mm t')  -- returns 01:00 A

Example:

select FORMAT(CAST('2018-01-01 14:00' AS datetime2), N'hh:mm tt') -- returns 02:00 PM
select FORMAT(CAST('2018-01-01 14:00' AS datetime2), N'hh:mm t') -- returns 02:00 P

Example:

select FORMAT(CAST('2018-01-01 14:00' AS datetime2), N'HH:mm') -- returns 14:00

Origin documentation for format_Culture

Syntax:

FORMAT( value, format [, culture ] )

Returns a value formatted with the specified format and optional culture. Use the FORMAT function for locale-aware formatting of date/time and number values as strings. For general data type conversions, use CAST or CONVERT.

Example:

DECLARE @d DATE = '11/22/2020';
SELECT FORMAT( @d, 'd', 'en-US' ) 'US English'
      ,FORMAT( @d, 'd', 'en-gb' ) 'British English'
      ,FORMAT( @d, 'd', 'de-de' ) 'German'
      ,FORMAT( @d, 'd', 'zh-cn' ) 'Chinese Simplified (PRC)';

SELECT FORMAT( @d, 'D', 'en-US' ) 'US English'
      ,FORMAT( @d, 'D', 'en-gb' ) 'British English'
      ,FORMAT( @d, 'D', 'de-de' ) 'German'
      ,FORMAT( @d, 'D', 'zh-cn' ) 'Chinese Simplified (PRC)';

Example:

DECLARE @d DATE = GETDATE();
SELECT FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS 'Date'
       ,FORMAT(123456789,'###-##-####') AS 'Custom Number';

Example:

SELECT TOP(5) CurrencyRateID, EndOfDayRate
            ,FORMAT(EndOfDayRate, 'N', 'en-us') AS 'Numeric Format'
            ,FORMAT(EndOfDayRate, 'G', 'en-us') AS 'General Format'
            ,FORMAT(EndOfDayRate, 'C', 'en-us') AS 'Currency Format'
FROM Sales.CurrencyRate
ORDER BY CurrencyRateID;

Example:

SELECT TOP(5) CurrencyRateID, EndOfDayRate
      ,FORMAT(EndOfDayRate, 'N', 'de-de') AS 'Numeric Format'
      ,FORMAT(EndOfDayRate, 'G', 'de-de') AS 'General Format'
      ,FORMAT(EndOfDayRate, 'C', 'de-de') AS 'Currency Format'
FROM Sales.CurrencyRate
ORDER BY CurrencyRateID;

Example:

SELECT FORMAT(cast('07:35' as time), N'hh.mm');   --> returns NULL
SELECT FORMAT(cast('07:35' as time), N'hh:mm');   --> returns NULL

Example:

SELECT FORMAT(cast('07:35' as time), N'hh\.mm');  --> returns 07.35
SELECT FORMAT(cast('07:35' as time), N'hh\:mm');  --> returns 07:35

Example:

SELECT FORMAT(SYSDATETIME(), N'hh:mm tt'); -- returns 03:46 PM
SELECT FORMAT(SYSDATETIME(), N'hh:mm t'); -- returns 03:46 P

Example:

select FORMAT(CAST('2018-01-01 01:00' AS datetime2), N'hh:mm tt') -- returns 01:00 AM
select FORMAT(CAST('2018-01-01 01:00' AS datetime2), N'hh:mm t')  -- returns 01:00 A

Example:

select FORMAT(CAST('2018-01-01 14:00' AS datetime2), N'hh:mm tt') -- returns 02:00 PM
select FORMAT(CAST('2018-01-01 14:00' AS datetime2), N'hh:mm t') -- returns 02:00 P

Example:

select FORMAT(CAST('2018-01-01 14:00' AS datetime2), N'HH:mm') -- returns 14:00

Origin documentation for percentileDisc

Syntax:

PERCENTILE_DISC ( numeric_literal ) WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] )
    OVER ( [ <partition_by_clause> ] )

Computes a specific percentile for sorted values in an entire rowset or within a rowset's distinct partitions in SQL Server. For a given percentile value P , PERCENTILE_DISC sorts the expression values in the ORDER BY clause. It then returns the value with the smallest CUME_DIST value given (with respect to the same sort specification) that is greater than or equal to P . For example, PERCENTILE_DISC (0.5) will compute the 50th percentile (that is, the median) of an expression. PERCENTILE_DISC calculates the percentile based on a discrete distribution of the column values. The result is equal to a specific column value.

Example:

USE AdventureWorks2012;

SELECT DISTINCT Name AS DepartmentName
      ,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ph.Rate)
                            OVER (PARTITION BY Name) AS MedianCont
      ,PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY ph.Rate)
                            OVER (PARTITION BY Name) AS MedianDisc
FROM HumanResources.Department AS d
INNER JOIN HumanResources.EmployeeDepartmentHistory AS dh
    ON dh.DepartmentID = d.DepartmentID
INNER JOIN HumanResources.EmployeePayHistory AS ph
    ON ph.BusinessEntityID = dh.BusinessEntityID
WHERE dh.EndDate IS NULL;

Example:

-- Uses AdventureWorks

SELECT DISTINCT DepartmentName
       ,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY BaseRate)
        OVER (PARTITION BY DepartmentName) AS MedianCont
       ,PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY BaseRate)
        OVER (PARTITION BY DepartmentName) AS MedianDisc
FROM dbo.DimEmployee;

Origin documentation for jsonQuery

Syntax:

JSON_QUERY ( expression [ , path ] )

Extracts an object or an array from a JSON string.

To extract a scalar value from a JSON string instead of an object or an array, see JSON_VALUE (Transact-SQL) . For info about the differences between JSON_VALUE and JSON_QUERY , see Compare JSON_VALUE and JSON_QUERY .

Example:

SELECT PersonID,FullName,
  JSON_QUERY(CustomFields,'$.OtherLanguages') AS Languages
FROM Application.People

Example:

SELECT StockItemID, StockItemName,
         JSON_QUERY(Tags) as Tags,
         JSON_QUERY(CONCAT('["',ValidFrom,'","',ValidTo,'"]')) ValidityPeriod
FROM Warehouse.StockItems
FOR JSON PATH

Origin documentation for jsonQuery_Path

Syntax:

JSON_QUERY ( expression [ , path ] )

Extracts an object or an array from a JSON string.

To extract a scalar value from a JSON string instead of an object or an array, see JSON_VALUE (Transact-SQL) . For info about the differences between JSON_VALUE and JSON_QUERY , see Compare JSON_VALUE and JSON_QUERY .

Example:

SELECT PersonID,FullName,
  JSON_QUERY(CustomFields,'$.OtherLanguages') AS Languages
FROM Application.People

Example:

SELECT StockItemID, StockItemName,
         JSON_QUERY(Tags) as Tags,
         JSON_QUERY(CONCAT('["',ValidFrom,'","',ValidTo,'"]')) ValidityPeriod
FROM Warehouse.StockItems
FOR JSON PATH

Origin documentation for rand

Syntax:

RAND ( [ seed ] )

Returns a pseudo-random float value from 0 through 1, exclusive.

Example:

SELECT RAND(100), RAND(), RAND()

Example:

DECLARE @counter SMALLINT;
SET @counter = 1;
WHILE @counter < 5
   BEGIN
      SELECT RAND() Random_Number
      SET @counter = @counter + 1
   END;
GO

Origin documentation for rand_Seed

Syntax:

RAND ( [ seed ] )

Returns a pseudo-random float value from 0 through 1, exclusive.

Example:

SELECT RAND(100), RAND(), RAND()

Example:

DECLARE @counter SMALLINT;
SET @counter = 1;
WHILE @counter < 5
   BEGIN
      SELECT RAND() Random_Number
      SET @counter = @counter + 1
   END;
GO

Origin documentation for tryCast

Syntax:

TRY_CAST ( expression AS data_type [ ( length ) ] )

Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

Example:

SELECT
    CASE WHEN TRY_CAST('test' AS float) IS NULL
    THEN 'Cast failed'
    ELSE 'Cast succeeded'
END AS Result;
GO

Example:

SET DATEFORMAT dmy;
SELECT TRY_CAST('12/31/2010' AS datetime2) AS Result;
GO

Example:

SELECT TRY_CAST(4 AS xml) AS Result;
GO

Example:

SET DATEFORMAT mdy;
SELECT TRY_CAST('12/31/2010' AS datetime2) AS Result;
GO

Origin documentation for tryCast_Length

Syntax:

TRY_CAST ( expression AS data_type [ ( length ) ] )

Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

Example:

SELECT
    CASE WHEN TRY_CAST('test' AS float) IS NULL
    THEN 'Cast failed'
    ELSE 'Cast succeeded'
END AS Result;
GO

Example:

SET DATEFORMAT dmy;
SELECT TRY_CAST('12/31/2010' AS datetime2) AS Result;
GO

Example:

SELECT TRY_CAST(4 AS xml) AS Result;
GO

Example:

SET DATEFORMAT mdy;
SELECT TRY_CAST('12/31/2010' AS datetime2) AS Result;
GO

Origin documentation for log

Syntax:

-- Syntax for SQL Server, Azure SQL Database

LOG ( float_expression [, base ] )

Returns the natural logarithm of the specified float expression in SQL Server.

Example:

DECLARE @var FLOAT = 10;
SELECT 'The LOG of the variable is: ' + CONVERT(VARCHAR, LOG(@var));
GO

Example:

SELECT LOG (EXP (10));

Example:

SELECT LOG(10);

Origin documentation for log_Base

Syntax:

-- Syntax for SQL Server, Azure SQL Database

LOG ( float_expression [, base ] )

Returns the natural logarithm of the specified float expression in SQL Server.

Example:

DECLARE @var FLOAT = 10;
SELECT 'The LOG of the variable is: ' + CONVERT(VARCHAR, LOG(@var));
GO

Example:

SELECT LOG (EXP (10));

Example:

SELECT LOG(10);

Origin documentation for eomonth

Syntax:

EOMONTH ( start_date [, month_to_add ] )

This function returns the last day of the month containing a specified date, with an optional offset.

Example:

DECLARE @date DATETIME = '12/1/2011';
SELECT EOMONTH ( @date ) AS Result;
GO

Example:

DECLARE @date VARCHAR(255) = '12/1/2011';
SELECT EOMONTH ( @date ) AS Result;
GO

Example:

DECLARE @date DATETIME = GETDATE();
SELECT EOMONTH ( @date ) AS 'This Month';
SELECT EOMONTH ( @date, 1 ) AS 'Next Month';
SELECT EOMONTH ( @date, -1 ) AS 'Last Month';
GO

Origin documentation for eomonth_MonthToAdd

Syntax:

EOMONTH ( start_date [, month_to_add ] )

This function returns the last day of the month containing a specified date, with an optional offset.

Example:

DECLARE @date DATETIME = '12/1/2011';
SELECT EOMONTH ( @date ) AS Result;
GO

Example:

DECLARE @date VARCHAR(255) = '12/1/2011';
SELECT EOMONTH ( @date ) AS Result;
GO

Example:

DECLARE @date DATETIME = GETDATE();
SELECT EOMONTH ( @date ) AS 'This Month';
SELECT EOMONTH ( @date, 1 ) AS 'Next Month';
SELECT EOMONTH ( @date, -1 ) AS 'Last Month';
GO

Origin documentation for decryptByCert

Syntax:

DecryptByCert ( certificate_ID , { 'ciphertext' | @ciphertext }
    [ , { 'cert_password' | @cert_password } ] )

This function uses the private key of a certificate to decrypt encrypted data.

Example:

SELECT CONVERT(NVARCHAR(max), DecryptByCert(Cert_Id('JanainaCert02'),
    ProtectedData, N'pGFD4bb925DGvbd2439587y'))
FROM [AdventureWorks2012].[ProtectedData04]
WHERE Description
    = N'data encrypted by certificate '' JanainaCert02''';
GO

Origin documentation for decryptByCert_CertPassword

Syntax:

DecryptByCert ( certificate_ID , { 'ciphertext' | @ciphertext }
    [ , { 'cert_password' | @cert_password } ] )

This function uses the private key of a certificate to decrypt encrypted data.

Example:

SELECT CONVERT(NVARCHAR(max), DecryptByCert(Cert_Id('JanainaCert02'),
    ProtectedData, N'pGFD4bb925DGvbd2439587y'))
FROM [AdventureWorks2012].[ProtectedData04]
WHERE Description
    = N'data encrypted by certificate '' JanainaCert02''';
GO

Origin documentation for str

Syntax:

STR ( float_expression [ , length [ , decimal ] ] )

Returns character data converted from numeric data. The character data is right-justified, with a specified length and decimal precision.

Example:

SELECT STR(123.45, 6, 1);
GO

Example:

SELECT STR(123.45, 2, 2);
GO

Example:

SELECT STR (FLOOR (123.45), 8, 3);
GO

Origin documentation for str_Length

Syntax:

STR ( float_expression [ , length [ , decimal ] ] )

Returns character data converted from numeric data. The character data is right-justified, with a specified length and decimal precision.

Example:

SELECT STR(123.45, 6, 1);
GO

Example:

SELECT STR(123.45, 2, 2);
GO

Example:

SELECT STR (FLOOR (123.45), 8, 3);
GO

Origin documentation for str_Length_Decimal

Syntax:

STR ( float_expression [ , length [ , decimal ] ] )

Returns character data converted from numeric data. The character data is right-justified, with a specified length and decimal precision.