Tested on server version: SQL Server 2016 Developer Edition
Reference info accurate on: January, 2018
The following script demonstrates how to generate each column’s DDL from metadata in Transact-SQL.
Example Output:
T-SQL
DECLARE @rundate datetime2 = getdate();
DECLARE @default_collation nvarchar(128); -- = 'SQL_Latin1_General_CP1_CI_AS';
SELECT @default_collation = convert(sysname, serverproperty('collation'))
SELECT TABLE_CATALOG
,TABLE_SCHEMA
,TABLE_NAME
,COLUMN_NAME
,ORDINAL_POSITION
,OBJECT_ID(QUOTENAME(TABLE_CATALOG) + '.' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) AS [OBJECT_ID]
,COLUMNPROPERTY(OBJECT_ID(QUOTENAME(TABLE_CATALOG) + '.' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)),COLUMN_NAME,'ColumnId') AS COLUMN_ID
,DATA_TYPE
,CHARACTER_MAXIMUM_LENGTH
,CASE WHEN DATA_TYPE IN ('decimal','numeric', 'float', 'real', 'datetime2', 'time', 'datetimeoffset')
THEN CASE WHEN DATETIME_PRECISION IS NOT NULL THEN DATETIME_PRECISION ELSE NUMERIC_PRECISION END
ELSE NULL
END AS [PRECISION] --only populated for decimal, numeric, float, real, datetime2, time, datetimeoffset
,CASE WHEN DATA_TYPE IN ('decimal','numeric') THEN NUMERIC_SCALE ELSE NULL END AS [SCALE] --only populated for decimal,numeric
,COLUMN_DEFAULT AS [DEFAULT]
,CALCULATION = (SELECT CONCAT([definition],CASE WHEN is_persisted = 1 THEN ' PERSISTED' ELSE '' END) FROM sys.computed_columns WHERE [object_id] = OBJECT_ID(QUOTENAME(TABLE_CATALOG) + '.' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) and [name] = COLUMN_NAME)
,[DDL] = CONCAT(DATA_TYPE,
CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
THEN CONCAT('('
,CASE WHEN CHARACTER_MAXIMUM_LENGTH=-1 THEN 'MAX' ELSE convert(varchar(4),CHARACTER_MAXIMUM_LENGTH) END
,')')
WHEN DATA_TYPE IN ('decimal','numeric','float', 'real', 'datetime2', 'time', 'datetimeoffset')
THEN CONCAT(
'('
,CONVERT(varchar(4), CASE WHEN DATETIME_PRECISION IS NOT NULL THEN DATETIME_PRECISION ELSE NUMERIC_PRECISION END)
,CASE WHEN DATA_TYPE IN ('decimal','numeric') THEN CONCAT(',', convert(varchar(4),NUMERIC_SCALE)) ELSE '' END
,')')
ELSE '' END
--,CASE WHEN c.is_filestream = 1 THEN ' FILESTREAM ' ELSE '' END
,CASE WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(TABLE_CATALOG) + '.' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)),COLUMN_NAME,'IsIdentity') = 1 THEN ' IDENTITY (1,1)' ELSE '' END
,CASE WHEN NULLIF(isc.COLLATION_NAME, @default_collation) IS NULL THEN '' ELSE CONCAT(' COLLATE ',isc.COLLATION_NAME) END
,CASE WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(TABLE_CATALOG) + '.' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)),COLUMN_NAME,'IsComputed') = 1 THEN
(SELECT CONCAT(' AS ',[definition],CASE WHEN is_persisted = 1 THEN ' PERSISTED' ELSE '' END) FROM sys.computed_columns WHERE [object_id] = OBJECT_ID(QUOTENAME(TABLE_CATALOG) + '.' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) and [name] = COLUMN_NAME)
ELSE '' END
,CASE WHEN COLUMN_DEFAULT IS NOT NULL THEN ' DEFAULT' + COLUMN_DEFAULT ELSE '' END
--,CASE WHEN c.is_identity=1 THEN ' IDENTITY ' ELSE '' END
,CASE WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(TABLE_CATALOG) + '.' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)),COLUMN_NAME,'IsRowGuidCol') = 1 THEN ' ROWGUIDCOL ' ELSE '' END
,CASE WHEN isc.IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE ' NULL' END
)
,CASE WHEN DATA_TYPE IN ('nvarchar','nchar', 'text', 'varchar', 'char', 'ntext')
THEN NULLIF(isc.COLLATION_NAME, @default_collation)
--THEN CASE WHEN isc.COLLATION_NAME=@default_collation THEN NULL ELSE isc.COLLATION_NAME END
END AS COLLATION_NAME
,DB_ID(TABLE_CATALOG)
,@RunDate as UPDATED
FROM INFORMATION_SCHEMA.COLUMNS isc
WHERE TABLE_NAME NOT LIKE '!_%' ESCAPE '!'
AND TABLE_CATALOG NOT IN ('master','tempdb','model','msdb') ORDER BY TABLE_SCHEMA,TABLE_NAME,ORDINAL_POSITION ASC;
--- Rick's Code Snippet Archive
--- Snippet #BD0F8725C28CC405748BCE99F45FBB70 (TSQL)
--- For the latest version of this code or to post a question or comment about it, visit:
--- http://www.SevenDaysOfSchema.com/tsql-examples/return-ddl-from-metadata/
Questions or Comments?
Be a part of the conversation. It only takes a minute to post a question or comment about this script.
In this Script
INFORMATION_SCHEMA.COLUMNS sys.computed_columns SERVERPROPERTY COLUMNPROPERTY OBJECT_ID QUOTENAME CONVERT GETDATE IS NULL NULLIF CONCAT SELECT WHERE DB_ID LIKE FROM CASE NOT AND IN
Reference Info
INFORMATION_SCHEMA.COLUMNS (Transact-SQL) 
Returns one row for each column that can be accessed by the current user in the current database.
To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA.view_name.
/* Remarks */ USE AdventureWorks2012; GO SELECT TABLE_NAME, COLUMN_NAME, COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID') AS COLUMN_ID FROM AdventureWorks2012.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Person'; GO
Columns Returned
| TABLE_CATALOG | nvarchar(128) |
| TABLE_SCHEMA | nvarchar(128) |
| TABLE_NAME | nvarchar(128) |
| COLUMN_NAME | nvarchar(128) |
| ORDINAL_POSITION | int |
| COLUMN_DEFAULT | nvarchar(4000) |
| IS_NULLABLE | varchar(3) |
| DATA_TYPE | nvarchar(128) |
| CHARACTER_MAXIMUM_LENGTH | int |
| CHARACTER_OCTET_LENGTH | int |
| NUMERIC_PRECISION | tinyint |
| NUMERIC_PRECISION_RADIX | smallint |
| NUMERIC_SCALE | int |
| DATETIME_PRECISION | smallint |
| CHARACTER_SET_CATALOG | nvarchar(128) |
| CHARACTER_SET_SCHEMA | nvarchar(128) |
| CHARACTER_SET_NAME | nvarchar(128) |
| COLLATION_CATALOG | nvarchar(128) |
| COLLATION_SCHEMA | nvarchar(128) |
| COLLATION_NAME | nvarchar(128) |
| DOMAIN_CATALOG | nvarchar(128) |
| DOMAIN_SCHEMA | nvarchar(128) |
| DOMAIN_NAME | nvarchar(128) |
Related Topics: COLUMNS_UPDATED Information Schema Views sys.columns sys.configurations sys.objects sys.sql_modules sys.syscharsets sys.types
System Information Schema Views: CHECK_CONSTRAINTS COLUMN_DOMAIN_USAGE COLUMN_PRIVILEGES CONSTRAINT_COLUMN_USAGE CONSTRAINT_TABLE_USAGE DOMAIN_CONSTRAINTS DOMAINS KEY_COLUMN_USAGE PARAMETERS REFERENTIAL_CONSTRAINTS ROUTINE_COLUMNS ROUTINES SCHEMATA TABLE_CONSTRAINTS TABLE_PRIVILEGES TABLES VIEW_COLUMN_USAGE VIEW_TABLE_USAGE VIEWS
sys.computed_columns (Transact-SQL)
Contains a row for each column found in sys.columns that is a computed column.
Permissions: The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. See Metadata Visibility Configuration.
Columns Returned
| definition | nvarchar(max) |
| uses_database_collation | bit |
| is_persisted | bit |
Related Topics: Catalog Views Object Catalog Views
Object Catalog Views: sys.allocation_units sys.assembly_modules sys.check_constraints sys.columns sys.default_constraints sys.event_notifications sys.events sys.extended_procedures sys.foreign_key_columns sys.foreign_keys sys.function_order_columns sys.hash_indexes sys.identity_columns sys.index_columns sys.key_constraints sys.masked_columns sys.memory_optimized_tables_internal_attributes sys.numbered_procedure_parameters sys.numbered_procedures sys.parameters
Returns property information about the server instance.
Permissions: All users can query the server properties.
SERVERPROPERTY ( 'propertyname' )
/*
ServerName Property
*/
EXEC sp_dropserver 'current_server_name';
GO
EXEC sp_addserver 'new_server_name', 'local';
GO
/*
Examples
*/
SELECT
SERVERPROPERTY('MachineName') AS ComputerName,
SERVERPROPERTY('ServerName') AS InstanceName,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel;
GO
Related Topics: Editions and Components of SQL Server 2016
Metadata Functions: APP_NAME APPLOCK_MODE APPLOCK_TEST ASSEMBLYPROPERTY COL_LENGTH COL_NAME COLUMNPROPERTY DATABASE_PRINCIPAL_ID DATABASEPROPERTYEX DB_ID DB_NAME FILE_ID FILE_IDEX FILE_NAME FILEGROUP_ID FILEGROUP_NAME FILEGROUPPROPERTY FILEPROPERTY FULLTEXTCATALOGPROPERTY FULLTEXTSERVICEPROPERTY
Returns information about a column or parameter.
COLUMNPROPERTY ( id , column , property )
/*
Examples
*/
USE AdventureWorks2012;
GO
SELECT COLUMNPROPERTY( OBJECT_ID('Person.Person'),'LastName','PRECISION')AS 'Column Length';
GO
Related Topics: Metadata Functions TYPEPROPERTY
Metadata Functions: APP_NAME APPLOCK_MODE APPLOCK_TEST ASSEMBLYPROPERTY COL_LENGTH COL_NAME DATABASE_PRINCIPAL_ID DATABASEPROPERTYEX DB_ID DB_NAME FILE_ID FILE_IDEX FILE_NAME FILEGROUP_ID FILEGROUP_NAME FILEGROUPPROPERTY FILEPROPERTY FULLTEXTCATALOGPROPERTY FULLTEXTSERVICEPROPERTY INDEX_COL
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``'.
OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . ] object_name' [ ,'object_type' ] )
/*
A. Returning the object ID for a specified object
*/
USE master;
GO
SELECT OBJECT_ID(N'AdventureWorks2012.Production.WorkOrder') AS 'Object ID';
GO
/*
B. Verifying that an object exists
*/
USE AdventureWorks2012;
GO
IF OBJECT_ID (N'dbo.AWBuildVersion', N'U') IS NOT NULL
DROP TABLE dbo.AWBuildVersion;
GO
/*
C. Using OBJECT_ID to specify the value of a system function parameter
*/
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
/*
D: Returning the object ID for a specified object
*/
SELECT OBJECT_ID('AdventureWorksPDW2012.dbo.FactFinance') AS 'Object ID';
Related Topics: Metadata Functions OBJECT_DEFINITION OBJECT_NAME sys.dm_db_index_operational_stats sys.objects
Metadata Functions: APP_NAME APPLOCK_MODE APPLOCK_TEST ASSEMBLYPROPERTY COL_LENGTH COL_NAME DATABASE_PRINCIPAL_ID DATABASEPROPERTYEX DB_ID DB_NAME FILE_ID FILE_IDEX FILE_NAME FILEGROUP_ID FILEGROUP_NAME FILEGROUPPROPERTY FILEPROPERTY FULLTEXTCATALOGPROPERTY FULLTEXTSERVICEPROPERTY
Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.
QUOTENAME ( 'character_string' [ , 'quote_character' ] )
/*
Examples
*/
SELECT QUOTENAME('abc[]def');
/*
Examples: Azure SQL Data Warehouse and Parallel Data Warehouse
*/
SELECT QUOTENAME('abc def');
Related Topics: CONCAT CONCAT_WS FORMATMESSAGE PARSENAME REPLACE REVERSE String Functions STRING_AGG STRING_ESCAPE STUFF TRANSLATE
String Functions: ASCII CHAR CHARINDEX CONCAT CONCAT_WS DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE
Converts an expression of one data type to another.
For example, the following examples change the input datatype, into two other datatypes, with different levels of precision.
``
/*
CAST and CONVERT (Transact-SQL)
*/
SELECT 9.5 AS Original, CAST(9.5 AS int) AS int,
CAST(9.5 AS decimal(6,4)) AS decimal;
SELECT 9.5 AS Original, CONVERT(int, 9.5) AS int,
CONVERT(decimal(6,4), 9.5) AS decimal;
/*
Truncating and rounding results
*/
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));
/*
Supplementary characters (surrogate pairs)
*/
DECLARE @x NVARCHAR(10) = 'ab' + NCHAR(0x10000);
SELECT CAST (@x AS NVARCHAR(3));
/*
A. Using both CAST and CONVERT
*/
-- Use CAST
USE AdventureWorks2012;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS int) LIKE '3%';
GO
-- Use CONVERT.
USE AdventureWorks2012;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CONVERT(int, ListPrice) LIKE '3%';
GO
/*
B. Using CAST with arithmetic operators
*/
USE AdventureWorks2012;
GO
SELECT CAST(ROUND(SalesYTD/CommissionPCT, 0) AS int) AS Computed
FROM Sales.SalesPerson
WHERE CommissionPCT != 0;
GO
/*
C. Using CAST to concatenate
*/
SELECT 'The list price is ' + CAST(ListPrice AS varchar(12)) AS ListPrice
FROM dbo.DimProduct
WHERE ListPrice BETWEEN 350.00 AND 400.00;
/*
D. Using CAST to produce more readable text
*/
SELECT DISTINCT CAST(EnglishProductName AS char(10)) AS Name, ListPrice
FROM dbo.DimProduct
WHERE EnglishProductName LIKE 'Long-Sleeve Logo Jersey, M';
/*
E. Using CAST with the LIKE clause
*/
USE AdventureWorks2012;
GO
SELECT p.FirstName, p.LastName, s.SalesYTD, s.BusinessEntityID
FROM Person.Person AS p
JOIN Sales.SalesPerson AS s
ON p.BusinessEntityID = s.BusinessEntityID
WHERE CAST(CAST(s.SalesYTD AS int) AS char(20)) LIKE '2%';
GO
/*
F. Using CONVERT or CAST with typed XML
*/
CONVERT(XML, '<root><child/></root>')
/*
G. Using CAST and CONVERT with datetime data
*/
SELECT
GETDATE() AS UnconvertedDateTime,
CAST(GETDATE() AS nvarchar(30)) AS UsingCast,
CONVERT(nvarchar(30), GETDATE(), 126) AS UsingConvertTo_ISO8601 ;
GO
/*
H. Using CONVERT with binary and character data
*/
--Convert the binary value 0x4E616d65 to a character value.
SELECT CONVERT(char(8), 0x4E616d65, 0) AS [Style 0, binary to character];
/*
I. Converting date and time data types
*/
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];
/*
J. Using CAST and CONVERT
*/
SELECT EnglishProductName AS ProductName, ListPrice
FROM dbo.DimProduct
WHERE CAST(ListPrice AS int) LIKE '3%';
/*
K. Using CAST with arithmetic operators
*/
SELECT ProductKey, UnitPrice,UnitPriceDiscountPct,
CAST(ROUND (UnitPrice*UnitPriceDiscountPct,0) AS int) AS DiscountPrice
FROM dbo.FactResellerSales
WHERE SalesOrderNumber = 'SO47355'
AND UnitPriceDiscountPct > .02;
/*
L. Using CAST with the LIKE clause
*/
SELECT EnglishProductName AS Name, ListPrice
FROM dbo.DimProduct
WHERE CAST(CAST(ListPrice AS int) AS char(20)) LIKE '2%';
/*
M. Using CAST and CONVERT with datetime data
*/
SELECT TOP(1)
SYSDATETIME() AS UnconvertedDateTime,
CAST(SYSDATETIME() AS nvarchar(30)) AS UsingCast,
CONVERT(nvarchar(30), SYSDATETIME(), 126) AS UsingConvertTo_ISO8601
FROM dbo.DimCustomer;
Related Topics: Data Type Conversion FORMAT SELECT STR System Functions Write International Transact-SQL Statements
T-SQL Functions: CERT_ID CERTPROPERTY CHOOSE COLLATIONPROPERTY CRYPT_GEN_RANDOM HAS_DBACCESS IIF LOGINPROPERTY PUBLISHINGSERVERNAME SESSIONPROPERTY TERTIARY_WEIGHTS TEXTPTR TEXTVALID USER
Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running.
NOTE: SYSDATETIME and SYSUTCDATETIME have more fractional seconds precision than GETDATE and GETUTCDATE. SYSDATETIMEOFFSET includes the system time zone offset. SYSDATETIME, SYSUTCDATETIME, and SYSDATETIMEOFFSET can be assigned to a variable of any of the date and time types.
For an overview of all T-SQL date and time data types and functions, see Date and Time Data Types and Functions.
GETDATE ( )
/*
Remarks
*/
DECLARE @dt datetimeoffset = switchoffset (CONVERT(datetimeoffset, GETDATE()), '-04:00');
SELECT * FROM t
WHERE c1 > @dt OPTION (RECOMPILE);
/*
A. Getting the current system date and time
*/
SELECT SYSDATETIME()
,SYSDATETIMEOFFSET()
,SYSUTCDATETIME()
,CURRENT_TIMESTAMP
,GETDATE()
,GETUTCDATE();
/*
B. Getting the current system date
*/
SELECT CONVERT (date, SYSDATETIME())
,CONVERT (date, SYSDATETIMEOFFSET())
,CONVERT (date, SYSUTCDATETIME())
,CONVERT (date, CURRENT_TIMESTAMP)
,CONVERT (date, GETDATE())
,CONVERT (date, GETUTCDATE());
/*
C. Getting the current system time
*/
SELECT CONVERT (time, SYSDATETIME())
,CONVERT (time, SYSDATETIMEOFFSET())
,CONVERT (time, SYSUTCDATETIME())
,CONVERT (time, CURRENT_TIMESTAMP)
,CONVERT (time, GETDATE())
,CONVERT (time, GETUTCDATE());
/*
D. Getting the current system date and time
*/
SELECT SYSDATETIME()
,CURRENT_TIMESTAMP
,GETDATE();
/*
E. Getting the current system date
*/
SELECT CONVERT (date, SYSDATETIME())
,CONVERT (date, CURRENT_TIMESTAMP)
,CONVERT (date, GETDATE());
/*
F. Getting the current system time
*/
SELECT CONVERT (time, SYSDATETIME())
,CONVERT (time, CURRENT_TIMESTAMP)
,CONVERT (time, GETDATE());
Related Topics: CAST and CONVERT
Date and Time Data Types and Functions: CURRENT_TIMESTAMP DATEADD DATEDIFF DATEDIFF_BIG DATEFROMPARTS DATENAME DATETIME2FROMPARTS DATETIMEFROMPARTS DATETIMEOFFSETFROMPARTS DAY EOMONTH GETUTCDATE ISDATE MONTH SET DATEFIRST SET DATEFORMAT SET LANGUAGE SMALLDATETIMEFROMPARTS sp_helplanguage SWITCHOFFSET
Determines whether a specified expression is NULL.
expression IS [ NOT ] NULL
/* Examples */ USE AdventureWorks2012; GO SELECT Name, Weight, Color FROM Production.Product WHERE Weight < 10.00 OR Color IS NULL ORDER BY Name; GO /* Examples: Azure SQL Data Warehouse and Parallel Data Warehouse */ -- Uses AdventureWorks SELECT FirstName, LastName, MiddleName FROM DIMEmployee WHERE MiddleName IS NOT NULL ORDER BY LastName DESC;
Related Topics: CASE CREATE PROCEDURE CREATE TABLE Data Types expression Expressions INSERT LIKE Logical Operators Operators SELECT sp_help UPDATE WHERE
T-SQL Query Elements: CONTAINS EXPLAIN FREETEXT FROM GROUP BY HAVING PIVOT and UNPIVOT PREDICT READTEXT SELECT TOP UPDATE UPDATETEXT WHERE WRITETEXT
Returns a null value if the two specified expressions are equal. For example, SELECT NULLIF(4,4) AS Same, NULLIF(5,7) AS Different; returns NULL for the first column (4 and 4) because the two input values are the same. The second column returns the first value (5) because the two input values are different.
NULLIF ( expression , expression )
/*
A. Returning budget amounts that have not changed
*/
CREATE TABLE dbo.budgets
(
dept tinyint IDENTITY,
current_year decimal NULL,
previous_year decimal NULL
);
INSERT budgets VALUES(100000, 150000);
INSERT budgets VALUES(NULL, 300000);
INSERT budgets VALUES(0, 100000);
INSERT budgets VALUES(NULL, 150000);
INSERT budgets VALUES(300000, 250000);
GO
SET NOCOUNT OFF;
SELECT AVG(NULLIF(COALESCE(current_year,
previous_year), 0.00)) AS 'Average Budget'
FROM budgets;
GO
/*
B. Comparing NULLIF and CASE
*/
USE AdventureWorks2012;
GO
SELECT ProductID, MakeFlag, FinishedGoodsFlag,
NULLIF(MakeFlag,FinishedGoodsFlag)AS 'Null if Equal'
FROM Production.Product
WHERE ProductID < 10;
GO
SELECT ProductID, MakeFlag, FinishedGoodsFlag,'Null if Equal' =
CASE
WHEN MakeFlag = FinishedGoodsFlag THEN NULL
ELSE MakeFlag
END
FROM Production.Product
WHERE ProductID < 10;
GO
/*
C: Returning budget amounts that contain no data
*/
CREATE TABLE budgets (
dept tinyint,
current_year decimal(10,2),
previous_year decimal(10,2)
);
INSERT INTO budgets VALUES(1, 100000, 150000);
INSERT INTO budgets VALUES(2, NULL, 300000);
INSERT INTO budgets VALUES(3, 0, 100000);
INSERT INTO budgets VALUES(4, NULL, 150000);
INSERT INTO budgets VALUES(5, 300000, 300000);
SELECT dept, NULLIF(current_year,
previous_year) AS LastBudget
FROM budgets;
Related Topics: CASE decimal and numeric System Functions
T-SQL Language Elements: BEGIN DISTRIBUTED TRANSACTION BEGIN TRANSACTION BEGIN…END BREAK CASE CLOSE COALESCE COMMIT TRANSACTION COMMIT WORK CONTINUE CREATE DIAGNOSTICS SESSION DEALLOCATE DECLARE CURSOR EXCEPT and INTERSECT EXECUTE FETCH GO GOTO IF…ELSE NULL and UNKNOWN
Returns a string that is the result of concatenating two or more string values. (To add a separating value during concatenation, see CONCAT_WS.)
CONCAT ( string_value1, string_value2 [, string_valueN ] )
/*
A. Using CONCAT
*/
SELECT CONCAT ( 'Happy ', 'Birthday ', 11, '/', '25' ) AS Result;
/*
B. Using CONCAT with NULL values
*/
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;
Related Topics: CONCAT_WS FORMATMESSAGE QUOTENAME REPLACE REVERSE String Functions STRING_AGG STRING_ESCAPE STUFF TRANSLATE
String Functions: ASCII CHAR CHARINDEX CONCAT_WS DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE
Retrieves one or more rows or columns from the database. The UNION, EXCEPT, and INTERSECT operators can be used between queries to combine or compare their results into one result set. <Rick’s Tip> The SELECT syntax used for Azure SQL Data Warehouse and Parallel Data Warehouse differs from that used for SQL Server and Azure SQL Database.
Permissions: Selecting data requires SELECT permission on the table or view, which could be inherited from a higher scope such as SELECT permission on the schema or CONTROL permission on the table. Or requires membership in the db_datareader or db_owner fixed database roles, or the sysadmin fixed server role. Creating a new table using SELECTINTO also requires both the CREATETABLE permission, and the ALTERSCHEMA permission on the schema that owns the new table.
-- Syntax for SQL Server and Azure SQL Database
&lgt;SELECT statement> ::=
[ WITH { [ XMLNAMESPACES ,] [ &lgt;common_table_expression> [,...n] ] } ]
&lgt;query_expression>
[ ORDER BY { order_by_expression | column_position [ ASC | DESC ] }
[ ,...n ] ]
[ &lgt;FOR Clause>]
[ OPTION ( &lgt;query_hint> [ ,...n ] ) ]
&lgt;query_expression> ::=
{ &lgt;query_specification> | ( &lgt;query_expression> ) }
[ { UNION [ ALL ] | EXCEPT | INTERSECT }
&lgt;query_specification> | ( &lgt;query_expression> ) [...n ] ]
&lgt;query_specification> ::=
SELECT [ ALL | DISTINCT ]
[TOP ( expression ) [PERCENT] [ WITH TIES ] ]
&lgt; select_list >
[ INTO new_table ]
[ FROM { &lgt;table_source> } [ ,...n ] ]
[ WHERE &lgt;search_condition> ]
[ &lgt;GROUP BY> ]
[ HAVING &lgt; search_condition > ]
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse
[ WITH &lgt;common_table_expression> [ ,...n ] ]
SELECT &lgt;select_criteria>
[;]
&lgt;select_criteria> ::=
[ TOP ( top_expression ) ]
[ ALL | DISTINCT ]
{ * | column_name | expression } [ ,...n ]
[ FROM { table_source } [ ,...n ] ]
[ WHERE &lgt;search_condition> ]
[ GROUP BY &lgt;group_by_clause> ]
[ HAVING &lgt;search_condition> ]
[ ORDER BY &lgt;order_by_expression> ]
[ OPTION ( &lgt;query_option> [ ,...n ] ) ]
/* A. Using SELECT to retrieve rows and columns */ SELECT * FROM DimEmployee ORDER BY LastName; /* B. Using SELECT with column headings and calculations */ SELECT FirstName, LastName, BaseRate, BaseRate * 40 AS GrossPay FROM DimEmployee ORDER BY LastName; /* C. Using DISTINCT with SELECT */ SELECT DISTINCT Title FROM DimEmployee ORDER BY Title; /* D. Using GROUP BY */ SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales FROM FactInternetSales GROUP BY OrderDateKey ORDER BY OrderDateKey; /* E. Using GROUP BY with multiple groups */ SELECT OrderDateKey, PromotionKey, AVG(SalesAmount) AS AvgSales, SUM(SalesAmount) AS TotalSales FROM FactInternetSales GROUP BY OrderDateKey, PromotionKey ORDER BY OrderDateKey; /* F. Using GROUP BY and WHERE */ SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales FROM FactInternetSales WHERE OrderDateKey > '20020801' GROUP BY OrderDateKey ORDER BY OrderDateKey; /* G. Using GROUP BY with an expression */ SELECT SUM(SalesAmount) AS TotalSales FROM FactInternetSales GROUP BY (OrderDateKey * 10); /* H. Using GROUP BY with ORDER BY */ SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales FROM FactInternetSales GROUP BY OrderDateKey ORDER BY OrderDateKey; /* I. Using the HAVING clause */ SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales FROM FactInternetSales GROUP BY OrderDateKey HAVING OrderDateKey > 20010000 ORDER BY OrderDateKey;
Related Topics: Hints SELECT Examples
T-SQL Query Elements: CONTAINS EXPLAIN FREETEXT FROM GROUP BY HAVING PIVOT and UNPIVOT PREDICT READTEXT TOP UPDATE UPDATETEXT WHERE WRITETEXT
Specifies the search condition for the rows returned by the query.
[ WHERE &lgt;search_condition> ]
/*
A. Finding a row by using a simple equality
*/
-- Uses AdventureWorks
SELECT EmployeeKey, LastName
FROM DimEmployee
WHERE LastName = 'Smith' ;
/*
B. Finding rows that contain a value as part of a string
*/
-- Uses AdventureWorks
SELECT EmployeeKey, LastName
FROM DimEmployee
WHERE LastName LIKE ('%Smi%');
/*
C. Finding rows by using a comparison operator
*/
-- Uses AdventureWorks
SELECT EmployeeKey, LastName
FROM DimEmployee
WHERE EmployeeKey <= 500;
/*
D. Finding rows that meet any of three conditions
*/
-- Uses AdventureWorks
SELECT EmployeeKey, LastName
FROM DimEmployee
WHERE EmployeeKey = 1 OR EmployeeKey = 8 OR EmployeeKey = 12;
/*
E. Finding rows that must meet several conditions
*/
-- Uses AdventureWorks
SELECT EmployeeKey, LastName
FROM DimEmployee
WHERE EmployeeKey <= 500 AND LastName LIKE '%Smi%' AND FirstName LIKE '%A%';
/*
F. Finding rows that are in a list of values
*/
-- Uses AdventureWorks
SELECT EmployeeKey, LastName
FROM DimEmployee
WHERE LastName IN ('Smith', 'Godfrey', 'Johnson');
/*
G. Finding rows that have a value between two values
*/
-- Uses AdventureWorks
SELECT EmployeeKey, LastName
FROM DimEmployee
WHERE EmployeeKey Between 100 AND 200;
Related Topics: DELETE MERGE Predicates Search Condition SELECT UPDATE
T-SQL Query Elements: CONTAINS EXPLAIN FREETEXT FROM GROUP BY HAVING PIVOT and UNPIVOT PREDICT READTEXT TOP UPDATE UPDATETEXT WRITETEXT
Returns the database identification (ID) number.
Permissions: If the caller of DB_ID is not the owner of the database and the database is not master or tempdb, the minimum permissions required to see the corresponding row are ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission, or CREATE DATABASE permission in the master database. The database to which the caller is connected can always be viewed in sys.databases.
IMPORTANT: By default, the public role has the VIEW ANY DATABASE permission, allowing all logins to see database information. To block a login from the ability to detect a database, REVOKE the VIEW ANY DATABASE permission from public, or DENY the VIEW ANY DATABASE permission for individual logins.
DB_ID ( [ 'database_name' ] )
/*
A. Returning the database ID of the current database
*/
SELECT DB_ID() AS [Database ID];
GO
/*
B. Returning the database ID of a specified database
*/
SELECT DB_ID(N'AdventureWorks2008R2') AS [Database ID];
GO
/*
C. Using DB_ID to specify the value of a system function parameter
*/
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
/*
D. Return the ID of the current database
*/
SELECT DB_ID();
/*
E. Return the ID of a named database.
*/
SELECT DB_ID('AdventureWorksPDW2012');
Related Topics: DB_NAME Metadata Functions sys.databases sys.dm_db_index_operational_stats
Metadata Functions: APP_NAME APPLOCK_MODE APPLOCK_TEST ASSEMBLYPROPERTY COL_LENGTH COL_NAME DATABASE_PRINCIPAL_ID DATABASEPROPERTYEX DB_NAME FILE_ID FILE_IDEX FILE_NAME FILEGROUP_ID FILEGROUP_NAME FILEGROUPPROPERTY FILEPROPERTY FULLTEXTCATALOGPROPERTY FULLTEXTSERVICEPROPERTY INDEX_COL
Determines whether a specific character string matches a specified pattern. A pattern can include regular characters and wildcard characters. During pattern matching, regular characters must exactly match the characters specified in the character string. However, wildcard characters can be matched with arbitrary fragments of the character string. Using wildcard characters makes the LIKE operator more flexible than using the = and != string comparison operators. If any one of the arguments is not of character string data type, the SQL Server Database Engine converts it to character string data type, if it is possible.
-- Syntax for SQL Server and Azure SQL Database match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ] -- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse match_expression [ NOT ] LIKE pattern
/*
Remarks
*/
-- Uses AdventureWorks
CREATE PROCEDURE FindEmployee @EmpLName char(20)
AS
SELECT @EmpLName = RTRIM(@EmpLName) + '%';
SELECT p.FirstName, p.LastName, a.City
FROM Person.Person p JOIN Person.Address a ON p.BusinessEntityID = a.AddressID
WHERE p.LastName LIKE @EmpLName;
GO
EXEC FindEmployee @EmpLName = 'Barb';
GO
/*
Pattern Matching by Using LIKE
*/
-- ASCII pattern matching with char column
CREATE TABLE t (col1 char(30));
INSERT INTO t VALUES ('Robert King');
SELECT *
FROM t
WHERE col1 LIKE '% King'; -- returns 1 row
-- Unicode pattern matching with nchar column
CREATE TABLE t (col1 nchar(30));
INSERT INTO t VALUES ('Robert King');
SELECT *
FROM t
WHERE col1 LIKE '% King'; -- no rows returned
-- Unicode pattern matching with nchar column and RTRIM
CREATE TABLE t (col1 nchar (30));
INSERT INTO t VALUES ('Robert King');
SELECT *
FROM t
WHERE RTRIM(col1) LIKE '% King'; -- returns 1 row
/*
Using the % Wildcard Character
*/
-- Uses AdventureWorks
SELECT Name
FROM sys.system_views
WHERE Name LIKE 'dm%';
GO
/*
A. Using LIKE with the % wildcard character
*/
-- Uses AdventureWorks
SELECT p.FirstName, p.LastName, ph.PhoneNumber
FROM Person.PersonPhone AS ph
INNER JOIN Person.Person AS p
ON ph.BusinessEntityID = p.BusinessEntityID
WHERE ph.PhoneNumber LIKE '415%'
ORDER by p.LastName;
GO
/*
B. Using NOT LIKE with the % wildcard character
*/
-- Uses AdventureWorks
SELECT p.FirstName, p.LastName, ph.PhoneNumber
FROM Person.PersonPhone AS ph
INNER JOIN Person.Person AS p
ON ph.BusinessEntityID = p.BusinessEntityID
WHERE ph.PhoneNumber NOT LIKE '415%' AND p.FirstName = 'Gail'
ORDER BY p.LastName;
GO
/*
C. Using the ESCAPE clause
*/
USE tempdb;
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'mytbl2')
DROP TABLE mytbl2;
GO
USE tempdb;
GO
CREATE TABLE mytbl2
(
c1 sysname
);
GO
INSERT mytbl2 VALUES ('Discount is 10-15% off'), ('Discount is .10-.15 off');
GO
SELECT c1
FROM mytbl2
WHERE c1 LIKE '%10-15!% off%' ESCAPE '!';
GO
/*
D. Using the [ ] wildcard characters
*/
-- Uses AdventureWorks
SELECT BusinessEntityID, FirstName, LastName
FROM Person.Person
WHERE FirstName LIKE '[CS]heryl';
GO
/*
E. Using LIKE with the % wildcard character
*/
-- Uses AdventureWorks
SELECT FirstName, LastName, Phone
FROM DimEmployee
WHERE phone LIKE '612%'
ORDER by LastName;
/*
F. Using NOT LIKE with the % wildcard character
*/
-- Uses AdventureWorks
SELECT FirstName, LastName, Phone
FROM DimEmployee
WHERE phone NOT LIKE '612%'
ORDER by LastName;
/*
G. Using LIKE with the _ wildcard character
*/
-- Uses AdventureWorks
SELECT FirstName, LastName, Phone
FROM DimEmployee
WHERE phone LIKE '6_2%'
ORDER by LastName;
Related Topics: Built-in Functions expression Expressions SELECT WHERE
Logical Operators: ALL AND ANY BETWEEN EXISTS IN NOT OR SOME and ANY
Specifies the tables, views, derived tables, and joined tables used in DELETE, SELECT, and UPDATE statements in SQL Server 2017. In the SELECT statement, the FROM clause is required except when the select list contains only constants, variables, and arithmetic expressions (no column names).
Permissions: Requires the permissions for the DELETE, SELECT, or UPDATE statement.
-- Syntax for SQL Server and Azure SQL Database
[ FROM { &lgt;table_source> } [ ,...n ] ]
&lgt;table_source> ::=
{
table_or_view_name [ [ AS ] table_alias ]
[ &lgt;tablesample_clause> ]
[ WITH ( &lgt; table_hint > [ [ , ]...n ] ) ]
| rowset_function [ [ AS ] table_alias ]
[ ( bulk_column_alias [ ,...n ] ) ]
| user_defined_function [ [ AS ] table_alias ]
| OPENXML &lgt;openxml_clause>
| derived_table [ [ AS ] table_alias ] [ ( column_alias [ ,...n ] ) ]
| &lgt;joined_table>
| &lgt;pivoted_table>
| &lgt;unpivoted_table>
| @variable [ [ AS ] table_alias ]
| @variable.function_call ( expression [ ,...n ] )
[ [ AS ] table_alias ] [ (column_alias [ ,...n ] ) ]
| FOR SYSTEM_TIME &lgt;system_time>
}
&lgt;tablesample_clause> ::=
TABLESAMPLE [SYSTEM] ( sample_number [ PERCENT | ROWS ] )
[ REPEATABLE ( repeat_seed ) ]
&lgt;joined_table> ::=
{
&lgt;table_source> &lgt;join_type> &lgt;table_source> ON &lgt;search_condition>
| &lgt;table_source> CROSS JOIN &lgt;table_source>
| left_table_source { CROSS | OUTER } APPLY right_table_source
| [ ( ] &lgt;joined_table> [ ) ]
}
&lgt;join_type> ::=
[ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [ &lgt;join_hint> ] ]
JOIN
&lgt;pivoted_table> ::=
table_source PIVOT &lgt;pivot_clause> [ [ AS ] table_alias ]
&lgt;pivot_clause> ::=
( aggregate_function ( value_column [ [ , ]...n ])
FOR pivot_column
IN ( &lgt;column_list> )
)
&lgt;unpivoted_table> ::=
table_source UNPIVOT &lgt;unpivot_clause> [ [ AS ] table_alias ]
&lgt;unpivot_clause> ::=
( value_column FOR pivot_column IN ( &lgt;column_list> ) )
&lgt;column_list> ::=
column_name [ ,...n ]
&lgt;system_time> ::=
{
AS OF &lgt;date_time>
| FROM &lgt;start_date_time> TO &lgt;end_date_time>
| BETWEEN &lgt;start_date_time> AND &lgt;end_date_time>
| CONTAINED IN (&lgt;start_date_time> , &lgt;end_date_time>)
| ALL
}
&lgt;date_time>::=
&lgt;date_time_literal> | @date_time_variable
&lgt;start_date_time>::=
&lgt;date_time_literal> | @date_time_variable
&lgt;end_date_time>::=
&lgt;date_time_literal> | @date_time_variable
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse
FROM { &lgt;table_source> [ ,...n ] }
&lgt;table_source> ::=
{
[ database_name . [ schema_name ] . | schema_name . ] table_or_view_name [ AS ] table_or_view_alias
| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
| &lgt;joined_table>
}
&lgt;joined_table> ::=
{
&lgt;table_source> &lgt;join_type> &lgt;table_source> ON search_condition
| &lgt;table_source> CROSS JOIN &lgt;table_source>
| left_table_source { CROSS | OUTER } APPLY right_table_source
| [ ( ] &lgt;joined_table> [ ) ]
}
&lgt;join_type> ::=
[ INNER ] [ &lgt;join hint> ] JOIN
| LEFT [ OUTER ] JOIN
| RIGHT [ OUTER ] JOIN
| FULL [ OUTER ] JOIN
&lgt;join_hint> ::=
REDUCE
| REPLICATE
| REDISTRIBUTE
/*
Arguments
*/
SELECT p.ProductID, v.BusinessEntityID
FROM Production.Product AS p
JOIN Purchasing.ProductVendor AS v
ON (p.ProductID = v.ProductID);
/*
A. Using a simple FROM clause
*/
SELECT TerritoryID, Name
FROM Sales.SalesTerritory
ORDER BY TerritoryID ;
/*
B. Using the TABLOCK and HOLDLOCK optimizer hints
*/
BEGIN TRAN
SELECT COUNT(*)
FROM HumanResources.Employee WITH (TABLOCK, HOLDLOCK) ;
/*
C. Using the SQL-92 CROSS JOIN syntax
*/
wql
SELECT e.BusinessEntityID, d.Name AS Department
FROM HumanResources.Employee AS e
CROSS JOIN HumanResources.Department AS d
ORDER BY e.BusinessEntityID, d.Name ;
/*
D. Using the SQL-92 FULL OUTER JOIN syntax
*/
-- The OUTER keyword following the FULL keyword is optional.
SELECT p.Name, sod.SalesOrderID
FROM Production.Product AS p
FULL OUTER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
ORDER BY p.Name ;
/*
E. Using the SQL-92 LEFT OUTER JOIN syntax
*/
SELECT p.Name, sod.SalesOrderID
FROM Production.Product AS p
LEFT OUTER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
ORDER BY p.Name ;
/*
F. Using the SQL-92 INNER JOIN syntax
*/
-- By default, SQL Server performs an INNER JOIN if only the JOIN
-- keyword is specified.
SELECT p.Name, sod.SalesOrderID
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
ORDER BY p.Name ;
/*
G. Using the SQL-92 RIGHT OUTER JOIN syntax
*/
SELECT st.Name AS Territory, sp.BusinessEntityID
FROM Sales.SalesTerritory AS st
RIGHT OUTER JOIN Sales.SalesPerson AS sp
ON st.TerritoryID = sp.TerritoryID ;
/*
H. Using HASH and MERGE join hints
*/
SELECT p.Name AS ProductName, v.Name AS VendorName
FROM Production.Product AS p
INNER MERGE JOIN Purchasing.ProductVendor AS pv
ON p.ProductID = pv.ProductID
INNER HASH JOIN Purchasing.Vendor AS v
ON pv.BusinessEntityID = v.BusinessEntityID
ORDER BY p.Name, v.Name ;
/*
I. Using a derived table
*/
SELECT RTRIM(p.FirstName) + ' ' + LTRIM(p.LastName) AS Name, d.City
FROM Person.Person AS p
INNER JOIN HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityID
INNER JOIN
(SELECT bea.BusinessEntityID, a.City
FROM Person.Address AS a
INNER JOIN Person.BusinessEntityAddress AS bea
ON a.AddressID = bea.AddressID) AS d
ON p.BusinessEntityID = d.BusinessEntityID
ORDER BY p.LastName, p.FirstName;
/*
J. Using TABLESAMPLE to read data from a sample of rows in a table
*/
SELECT *
FROM Sales.Customer TABLESAMPLE SYSTEM (10 PERCENT) ;
/*
K. Using APPLY
*/
SELECT DeptID, DeptName, DeptMgrID, EmpID, EmpLastName, EmpSalary
FROM Departments d CROSS APPLY dbo.GetReports(d.DeptMgrID) ;
/*
L. Using CROSS APPLY
*/
USE master;
GO
SELECT dbid, object_id, query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);
GO
/*
M. Using FOR SYSTEM_TIME
*/
SELECT DepartmentNumber,
DepartmentName,
ManagerID,
ParentDepartmentNumber
FROM DEPARTMENT
FOR SYSTEM_TIME AS OF '2014-01-01'
WHERE ManagerID = 5;
/*
N. Using the INNER JOIN syntax
*/
-- Uses AdventureWorks
SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName
FROM FactInternetSales AS fis
INNER JOIN DimProduct AS dp
ON dp.ProductKey = fis.ProductKey;
/*
O. Using the LEFT OUTER JOIN and RIGHT OUTER JOIN syntax
*/
-- Uses AdventureWorks
SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName
FROM FactInternetSales AS fis
LEFT OUTER JOIN DimProduct AS dp
ON dp.ProductKey = fis.ProductKey;
/*
P. Using the FULL OUTER JOIN syntax
*/
-- Uses AdventureWorks
SELECT dst.SalesTerritoryKey, dst.SalesTerritoryRegion, fis.SalesOrderNumber
FROM DimSalesTerritory AS dst
FULL OUTER JOIN FactInternetSales AS fis
ON dst.SalesTerritoryKey = fis.SalesTerritoryKey
ORDER BY fis.SalesOrderNumber;
/*
Q. Using the CROSS JOIN syntax
*/
-- Uses AdventureWorks
SELECT dst.SalesTerritoryKey, fis.SalesOrderNumber
FROM DimSalesTerritory AS dst
CROSS JOIN FactInternetSales AS fis
ORDER BY fis.SalesOrderNumber;
/*
R. Using a derived table
*/
-- Uses AdventureWorks
SELECT CustomerKey, LastName
FROM
(SELECT * FROM DimCustomer
WHERE BirthDate > '01/01/1970') AS DimCustomerDerivedTable
WHERE LastName = 'Smith'
ORDER BY LastName;
/*
S. REDUCE join hint example
*/
-- Uses AdventureWorks
EXPLAIN SELECT SalesOrderNumber
FROM
(SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName
FROM DimProduct AS dp
INNER REDUCE JOIN FactInternetSales AS fis
ON dp.ProductKey = fis.ProductKey
) AS dTable
ORDER BY SalesOrderNumber;
/*
T. REPLICATE join hint example
*/
-- Uses AdventureWorks
EXPLAIN SELECT SalesOrderNumber
FROM
(SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName
FROM DimProduct AS dp
INNER REPLICATE JOIN FactInternetSales AS fis
ON dp.ProductKey = fis.ProductKey
) AS dTable
ORDER BY SalesOrderNumber;
/*
U. Using the REDISTRIBUTE hint to guarantee a Shuffle move for a distribution incompatible join
*/
-- Uses AdventureWorks
EXPLAIN
SELECT dp.ProductKey, fis.SalesOrderNumber, fis.TotalProductCost
FROM DimProduct AS dp
INNER REDISTRIBUTE JOIN FactInternetSales AS fis
ON dp.ProductKey = fis.ProductKey;
Related Topics: CONTAINSTABLE DELETE FREETEXTTABLE INSERT OPENQUERY OPENROWSET Operators UPDATE WHERE
T-SQL Query Elements: CONTAINS EXPLAIN FREETEXT GROUP BY HAVING PIVOT and UNPIVOT PREDICT READTEXT TOP UPDATE UPDATETEXT WRITETEXT
Evaluates a list of conditions and returns one of multiple possible result expressions.
The CASE expression has two formats:
- The simple CASE expression compares an expression to a set of simple expressions to determine the result.
-
The searched CASE expression evaluates a set of Boolean expressions to determine the result.
Both formats support an optional ELSE argument.
CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.
-- Syntax for SQL Server and Azure SQL Database
Simple CASE expression:
CASE input_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
Searched CASE expression:
CASE
WHEN Boolean_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse
CASE
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
/*
Remarks
*/
WITH Data (value) AS
(
SELECT 0
UNION ALL
SELECT 1
)
SELECT
CASE
WHEN MIN(value) <= 0 THEN 0 WHEN MAX(1/value) >= 100 THEN 1
END
FROM Data ;
/*
A. Using a SELECT statement with a simple CASE expression
*/
USE AdventureWorks2012;
GO
SELECT ProductNumber, Category =
CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE 'Not for sale'
END,
Name
FROM Production.Product
ORDER BY ProductNumber;
GO
/*
B. Using a SELECT statement with a searched CASE expression
*/
USE AdventureWorks2012;
GO
SELECT ProductNumber, Name, "Price Range" =
CASE
WHEN ListPrice = 0 THEN 'Mfg item - not for resale'
WHEN ListPrice < 50 THEN 'Under $50' WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250' WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
ELSE 'Over $1000'
END
FROM Production.Product
ORDER BY ProductNumber ;
GO
/*
C. Using CASE in an ORDER BY clause
*/
SELECT BusinessEntityID, SalariedFlag
FROM HumanResources.Employee
ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC
,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END;
GO
/*
D. Using CASE in an UPDATE statement
*/
USE AdventureWorks2012;
GO
UPDATE HumanResources.Employee
SET VacationHours =
( CASE
WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40 ELSE (VacationHours + 20.00) END ) OUTPUT Deleted.BusinessEntityID, Deleted.VacationHours AS BeforeValue, Inserted.VacationHours AS AfterValue WHERE SalariedFlag = 0; /* E. Using CASE in a SET statement */ USE AdventureWorks2012; GO CREATE FUNCTION dbo.GetContactInformation(@BusinessEntityID int) RETURNS @retContactInformation TABLE ( BusinessEntityID int NOT NULL, FirstName nvarchar(50) NULL, LastName nvarchar(50) NULL, ContactType nvarchar(50) NULL, PRIMARY KEY CLUSTERED (BusinessEntityID ASC) ) AS -- Returns the first name, last name and contact type for the specified contact. BEGIN DECLARE @FirstName nvarchar(50), @LastName nvarchar(50), @ContactType nvarchar(50); -- Get common contact information SELECT @BusinessEntityID = BusinessEntityID, @FirstName = FirstName, @LastName = LastName FROM Person.Person WHERE BusinessEntityID = @BusinessEntityID; SET @ContactType = CASE -- Check for employee WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e WHERE e.BusinessEntityID = @BusinessEntityID) THEN 'Employee' -- Check for vendor WHEN EXISTS(SELECT * FROM Person.BusinessEntityContact AS bec WHERE bec.BusinessEntityID = @BusinessEntityID) THEN 'Vendor' -- Check for store WHEN EXISTS(SELECT * FROM Purchasing.Vendor AS v WHERE v.BusinessEntityID = @BusinessEntityID) THEN 'Store Contact' -- Check for individual consumer WHEN EXISTS(SELECT * FROM Sales.Customer AS c WHERE c.PersonID = @BusinessEntityID) THEN 'Consumer' END; -- Return the information to the caller IF @BusinessEntityID IS NOT NULL BEGIN INSERT @retContactInformation SELECT @BusinessEntityID, @FirstName, @LastName, @ContactType; END; RETURN; END; GO SELECT BusinessEntityID, FirstName, LastName, ContactType FROM dbo.GetContactInformation(2200); GO SELECT BusinessEntityID, FirstName, LastName, ContactType FROM dbo.GetContactInformation(5); /* F. Using CASE in a HAVING clause */ USE AdventureWorks2012; GO SELECT JobTitle, MAX(ph1.Rate)AS MaximumRate FROM HumanResources.Employee AS e JOIN HumanResources.EmployeePayHistory AS ph1 ON e.BusinessEntityID = ph1.BusinessEntityID GROUP BY JobTitle HAVING (MAX(CASE WHEN Gender = 'M' THEN ph1.Rate ELSE NULL END) > 40.00
OR MAX(CASE WHEN Gender = 'F'
THEN ph1.Rate
ELSE NULL END) > 42.00)
ORDER BY MaximumRate DESC;
/*
G. Using a SELECT statement with a CASE expression
*/
-- Uses AdventureWorks
SELECT ProductAlternateKey, Category =
CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE 'Not for sale'
END,
EnglishProductName
FROM dbo.DimProduct
ORDER BY ProductKey;
/*
H. Using CASE in an UPDATE statement
*/
-- Uses AdventureWorks
UPDATE dbo.DimEmployee
SET VacationHours =
( CASE
WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40
ELSE (VacationHours + 20.00)
END
)
WHERE SalariedFlag = 0;
Related Topics: CHOOSE COALESCE expression Expressions IIF SELECT
T-SQL Language Elements: BEGIN DISTRIBUTED TRANSACTION BEGIN TRANSACTION BEGIN…END BREAK CLOSE COALESCE COMMIT TRANSACTION COMMIT WORK CONTINUE CREATE DIAGNOSTICS SESSION DEALLOCATE DECLARE CURSOR EXCEPT and INTERSECT EXECUTE FETCH GO GOTO IF…ELSE NULL and UNKNOWN
Negates a Boolean input.
[ NOT ] boolean_expression
/* Examples */ -- Uses AdventureWorks SELECT ProductID, Name, Color, StandardCost FROM Production.Product WHERE ProductNumber LIKE 'BK-%' AND Color = 'Silver' AND NOT StandardCost > 400; GO /* Examples: Azure SQL Data Warehouse and Parallel Data Warehouse */ -- Uses AdventureWorks SELECT ProductKey, CustomerKey, OrderDateKey, ShipDateKey FROM FactInternetSales WHERE SalesOrderNumber LIKE 'SO6%' AND NOT ProductKey < 400;
Related Topics: Built-in Functions expression Expressions Operators SELECT WHERE
Logical Operators: ALL AND ANY BETWEEN EXISTS IN OR SOME and ANY
Combines two Boolean expressions and returns TRUE when both expressions are TRUE. When more than one logical operator is used in a statement, the AND operators are evaluated first. You can change the order of evaluation by using parentheses.
boolean_expression AND boolean_expression
/* A. Using the AND operator */ -- Uses AdventureWorks SELECT BusinessEntityID, LoginID, JobTitle, VacationHours FROM HumanResources.Employee WHERE JobTitle = 'Marketing Assistant' AND VacationHours > 41 ; /* B. Using the AND operator in an IF statement */ IF 1 = 1 AND 2 = 2 BEGIN PRINT 'First Example is TRUE' END ELSE PRINT 'First Example is FALSE'; GO IF 1 = 1 AND 2 = 17 BEGIN PRINT 'Second Example is TRUE' END ELSE PRINT 'Second Example is FALSE' ; GO
Related Topics: Built-in Functions Operators SELECT WHERE
Logical Operators: ALL ANY BETWEEN EXISTS IN OR SOME and ANY
Determines whether a specified value matches any value in a subquery or a list.
test_expression [ NOT ] IN
( subquery | expression [ ,...n ]
)
/*
A. Comparing OR and IN
*/
-- Uses AdventureWorks
SELECT p.FirstName, p.LastName, e.JobTitle
FROM Person.Person AS p
JOIN HumanResources.Employee AS e
ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.JobTitle = 'Design Engineer'
OR e.JobTitle = 'Tool Designer'
OR e.JobTitle = 'Marketing Assistant';
GO
/*
B. Using IN with a subquery
*/
-- Uses AdventureWorks
SELECT p.FirstName, p.LastName
FROM Person.Person AS p
JOIN Sales.SalesPerson AS sp
ON p.BusinessEntityID = sp.BusinessEntityID
WHERE p.BusinessEntityID IN
(SELECT BusinessEntityID
FROM Sales.SalesPerson
WHERE SalesQuota > 250000);
GO
/*
C. Using NOT IN with a subquery
*/
-- Uses AdventureWorks
SELECT p.FirstName, p.LastName
FROM Person.Person AS p
JOIN Sales.SalesPerson AS sp
ON p.BusinessEntityID = sp.BusinessEntityID
WHERE p.BusinessEntityID NOT IN
(SELECT BusinessEntityID
FROM Sales.SalesPerson
WHERE SalesQuota > 250000);
GO
/*
D. Using IN and NOT IN
*/
-- Uses AdventureWorks
SELECT * FROM FactInternetSalesReason
WHERE SalesReasonKey
IN (SELECT SalesReasonKey FROM DimSalesReason);
/*
E. Using IN with an expression list
*/
-- Uses AdventureWorks
SELECT FirstName, LastName
FROM DimEmployee
WHERE FirstName IN ('Mike', 'Michael');
Related Topics: ALL Built-in Functions CASE expression Expressions Operators SELECT SOME | ANY WHERE
Logical Operators: ALL ANY BETWEEN EXISTS OR SOME and ANY
About the Archive This script is part of my personal code snippet library, which I’ve been posting to this site using the WordPress automation processes I’m working on.
