Querying Stored Procedure and System Function Parameter Information

Tested on: SQL Server 2016 Developer Edition
Accurate as of: January, 2018

The following snippet generates a SQL Server View that displays information about the parameters that need to be passed in to System Objects. The data type information that is returned in this view is human readable (i.e. `nvarchar(200) NULL`), rather than the numeric type id, etc.).

USAGE EXAMPLE:

SELECT * FROM dbo.vwMSObjectParameters p ORDER BY p.object_id asc, p.parameter_id asc

EXAMPLE OUTPUT:

CREATE VIEW dbo.vwMSObjectParameters
AS
SELECT
p.object_id as [Object_Id], 
schema_name(o.schema_id) as [ObjSchema],
o.[name] as [ObjName],
p.[name] as [ObjParameter],
p.parameter_id as [Parameter_Id],
[ObjParameterDataType] = CONCAT(ISNULL(TYPE_NAME(p.system_type_id), t.name),
   CASE WHEN ISNULL(TYPE_NAME(p.system_type_id), t.name) IN ('nchar','nvarchar','char','binary','varchar','varbinary','ntext','sql_variant','text')
     THEN CONCAT('(',
                            CASE WHEN p.max_length=-1 THEN 'MAX'
                            ELSE CONVERT(varchar(4), p.max_length)
                            END
                            ,')')
    ELSE 
                '' END
   ,CASE WHEN p.is_output = 1 THEN ' OUTPUT' ELSE '' END 
   ,CASE WHEN p.default_value IS NOT NULL THEN ' DEFAULT ' + CONVERT(varchar(8000), p.default_value) ELSE '' END
            -- I have excluded the encryption information columns
   ,CASE WHEN p.is_nullable = 0 THEN ' NOT NULL' ELSE ' NULL' END
       )
   from  sys.all_objects o 
INNER JOIN sys.all_parameters p on o.object_id=p.object_id
INNER JOIN sys.types t on p.user_type_id = t.user_type_id
 
 
--- Rick's Code Snippet Archive 
--- Snippet #A46CDFAA2DDF4E2A99E91600E33B2927 (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-parameter-information-for-system-objects/

Questions or comments about this script? Be a part of the conversation. It only takes a minute to post a comment.

In this Script

sys.all_parameters     sys.all_objects     SCHEMA_NAME     CREATE VIEW     OBJECT_ID     SCHEMA_ID     sys.types     TYPE_NAME     CONVERT     CONCAT     ISNULL     SELECT     CASE     FROM     NOT     IN    

sys.all_parameters (Transact-SQL)

Shows the union of all parameters that belong to user-defined or system objects.

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

object_id

int
name	sysname
parameter_id	int
system_type_id	tinyint
user_type_id	int
max_length	smallint
precision	tinyint
scale	tinyint
is_output	bit
is_cursor_ref	bit
has_default_value	bit
is_xml_document	bit
default_value	sql_variant
xml_collection_id	int

Related Topics:   Catalog Views   Object Catalog Views   Querying the SQL Server System Catalog FAQ   sys.parameters   sys.system_parameters

System Catalog Views:   dbo.sysdac_instances   sys.all_columns   sys.all_objects   sys.all_sql_modules   sys.all_views   sys.change_tracking_databases   sys.change_tracking_tables   sys.column_store_dictionaries   sys.column_store_row_groups   sys.column_store_segments   sys.column_type_usages   sys.data_spaces   sys.database_automatic_tuning_options   sys.database_credentials   sys.database_event_session_actions   sys.database_event_session_events   sys.database_event_session_fields   sys.database_event_session_targets   sys.database_event_sessions   sys.database_mirroring_witnesses

TOP

sys.all_objects (Transact-SQL)

Shows the UNION of all schema-scoped user-defined objects and system objects.

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

name

sysname
object_id	int
principal_id	int
schema_id	int
parent_object_id	int
type	char(2)
type_desc	nvarchar(60)
create_date	datetime
modify_date	datetime
is_ms_shipped	bit
is_published	bit
is_schema_published	bit

Related Topics:   Catalog Views   Object Catalog Views   sys.objects   sys.system_objects

System Catalog Views:   dbo.sysdac_instances   sys.all_columns   sys.all_sql_modules   sys.all_views   sys.change_tracking_databases   sys.change_tracking_tables   sys.column_store_dictionaries   sys.column_store_row_groups   sys.column_store_segments   sys.column_type_usages   sys.data_spaces   sys.database_automatic_tuning_options   sys.database_credentials   sys.database_event_session_actions   sys.database_event_session_events   sys.database_event_session_fields   sys.database_event_session_targets   sys.database_event_sessions   sys.database_mirroring_witnesses

TOP

SCHEMA_NAME (Transact-SQL)

Returns the schema name associated with a schema ID.

SCHEMA_NAME ( [ schema_id ] )

/* 

 A. Returning the name of the default schema of the caller

*/
  
SELECT SCHEMA_NAME();  





/* 

 B. Returning the name of a schema by using an ID

*/
  
SELECT SCHEMA_NAME(1);

Related Topics:   Expressions   Metadata Functions   SCHEMA_ID   sys.database_principals   sys.schemas   WHERE

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

TOP

CREATE VIEW (Transact-SQL)

Creates a virtual table whose contents (columns and rows) are defined by a query. Use this statement to create a view of the data in one or more tables in the database. For example, a view can be used for the following purposes:

– To focus, simplify, and customize the perception each user has of the database.

– As a security mechanism by allowing users to access data through the view, without granting the users permissions to directly access the underlying base tables.

– To provide a backward compatible interface to emulate a table whose schema has changed.

Permissions: Requires CREATE VIEW permission in the database and ALTER permission on the schema in which the view is being created.

-- Syntax for SQL Server and Azure SQL Database  
  
CREATE [ OR ALTER ] VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]   
[ WITH &lgt;view_attribute> [ ,...n ] ]   
AS select_statement   
[ WITH CHECK OPTION ]   
[ ; ]  
  
&lgt;view_attribute> ::=   
{  
    [ ENCRYPTION ]  
    [ SCHEMABINDING ]  
    [ VIEW_METADATA ]       
}   
  
  
  
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
CREATE VIEW [ schema_name . ] view_name [  ( column_name [ ,...n ] ) ]   
AS &lgt;select_statement>   
[;]  
  
&lgt;select_statement> ::=  
    [ WITH &lgt;common_table_expression> [ ,...n ] ]  
    SELECT &lgt;select_criteria>

/* 

 Partitioned Views

*/
  
--Partitioned view as defined on Server1  
CREATE VIEW Customers  
AS  
--Select from local member table.  
SELECT *  
FROM CompanyData.dbo.Customers_33  
UNION ALL  
--Select from member table on Server2.  
SELECT *  
FROM Server2.CompanyData.dbo.Customers_66  
UNION ALL  
--Select from mmeber table on Server3.  
SELECT *  
FROM Server3.CompanyData.dbo.Customers_99;  





/* 

 Conditions for Creating Partitioned Views

*/
  
        C1 ::= < simple_interval > [ OR < simple_interval > OR ...]  
        < simple_interval > :: =   
        < col > { < | > | \<= | >= | = < value >}   
        | < col > BETWEEN < value1 > AND < value2 >  
        | < col > IN ( value_list )  
        | < col > { > | >= } < value1 > AND  
        < col > { < | <= } < value2 >  
        




/* 

 A. Using a simple CREATE VIEW

*/
  
CREATE VIEW hiredate_view  
AS   
SELECT p.FirstName, p.LastName, e.BusinessEntityID, e.HireDate  
FROM HumanResources.Employee e   
JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID ;  
GO  
  





/* 

 B. Using WITH ENCRYPTION

*/
  
CREATE VIEW Purchasing.PurchaseOrderReject  
WITH ENCRYPTION  
AS  
SELECT PurchaseOrderID, ReceivedQty, RejectedQty,   
    RejectedQty / ReceivedQty AS RejectRatio, DueDate  
FROM Purchasing.PurchaseOrderDetail  
WHERE RejectedQty / ReceivedQty > 0  
AND DueDate > CONVERT(DATETIME,'20010630',101) ;  
GO  
  





/* 

 C. Using WITH CHECK OPTION

*/
  
CREATE VIEW dbo.SeattleOnly  
AS  
SELECT p.LastName, p.FirstName, e.JobTitle, a.City, sp.StateProvinceCode  
FROM HumanResources.Employee e  
INNER JOIN Person.Person p  
ON p.BusinessEntityID = e.BusinessEntityID  
    INNER JOIN Person.BusinessEntityAddress bea   
    ON bea.BusinessEntityID = e.BusinessEntityID   
    INNER JOIN Person.Address a   
    ON a.AddressID = bea.AddressID  
    INNER JOIN Person.StateProvince sp   
    ON sp.StateProvinceID = a.StateProvinceID  
WHERE a.City = 'Seattle'  
WITH CHECK OPTION ;  
GO  





/* 

 D. Using built-in functions within a view

*/
  
CREATE VIEW Sales.SalesPersonPerform  
AS  
SELECT TOP (100) SalesPersonID, SUM(TotalDue) AS TotalSales  
FROM Sales.SalesOrderHeader  
WHERE OrderDate > CONVERT(DATETIME,'20001231',101)  
GROUP BY SalesPersonID;  
GO  






/* 

 E. Using partitioned data

*/
  
--Create the tables and insert the values.  
CREATE TABLE dbo.SUPPLY1 (  
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 1 and 150),  
supplier CHAR(50)  
);  
CREATE TABLE dbo.SUPPLY2 (  
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 151 and 300),  
supplier CHAR(50)  
);  
CREATE TABLE dbo.SUPPLY3 (  
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 301 and 450),  
supplier CHAR(50)  
);  
CREATE TABLE dbo.SUPPLY4 (  
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 451 and 600),  
supplier CHAR(50)  
);  
GO  
INSERT dbo.SUPPLY1 VALUES ('1', 'CaliforniaCorp'), ('5', 'BraziliaLtd')  
, ('231', 'FarEast'), ('280', 'NZ')  
, ('321', 'EuroGroup'), ('442', 'UKArchip')  
, ('475', 'India'), ('521', 'Afrique');  
GO  
--Create the view that combines all supplier tables.  
CREATE VIEW dbo.all_supplier_view  
WITH SCHEMABINDING  
AS  
SELECT supplyID, supplier  
  FROM dbo.SUPPLY1  
UNION ALL  
SELECT supplyID, supplier  
  FROM dbo.SUPPLY2  
UNION ALL  
SELECT supplyID, supplier  
  FROM dbo.SUPPLY3  
UNION ALL  
SELECT supplyID, supplier  
  FROM dbo.SUPPLY4;  





/* 

 F. Creating a simple view

*/
  
CREATE VIEW DimEmployeeBirthDates AS  
SELECT FirstName, LastName, BirthDate   
FROM DimEmployee;  





/* 

 G. Create a view by joining two tables

*/
  
CREATE VIEW view1  
AS 
SELECT fis.CustomerKey, fis.ProductKey, fis.OrderDateKey, 
  fis.SalesTerritoryKey, dst.SalesTerritoryRegion  
FROM FactInternetSales AS fis   
LEFT OUTER JOIN DimSalesTerritory AS dst   
ON (fis.SalesTerritoryKey=dst.SalesTerritoryKey);

Related Topics:   ALTER TABLE   ALTER VIEW   Create a Stored Procedure   DELETE   DROP VIEW   EVENTDATA   INSERT   sp_help   sp_helptext   sp_refreshview   sp_rename   sys.dm_sql_referenced_entities   sys.dm_sql_referencing_entities   sys.views   UPDATE

CREATE Statements:   CREATE AGGREGATE   CREATE APPLICATION ROLE   CREATE ASSEMBLY   CREATE ASYMMETRIC KEY   CREATE AVAILABILITY GROUP   CREATE BROKER PRIORITY   CREATE CERTIFICATE   CREATE COLUMN ENCRYPTION KEY   CREATE COLUMN MASTER KEY   CREATE COLUMNSTORE INDEX   CREATE CONTRACT   CREATE CREDENTIAL   CREATE CRYPTOGRAPHIC PROVIDER   CREATE DATABASE   CREATE DATABASE   CREATE DATABASE   CREATE DATABASE   CREATE DATABASE AUDIT SPECIFICATION   CREATE DATABASE ENCRYPTION KEY   CREATE DATABASE SCOPED CREDENTIAL

TOP

OBJECT_ID (Transact-SQL)

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   COLUMNPROPERTY   DATABASE_PRINCIPAL_ID   DATABASEPROPERTYEX   DB_ID   DB_NAME   FILE_ID   FILE_IDEX   FILE_NAME   FILEGROUP_ID   FILEGROUP_NAME   FILEGROUPPROPERTY   FILEPROPERTY   FULLTEXTCATALOGPROPERTY   FULLTEXTSERVICEPROPERTY

TOP

SCHEMA_ID (Transact-SQL)

Returns the schema ID associated with a schema name.

SCHEMA_ID ( [ schema_name ] )

/* 

 A. Returning the default schema ID of a caller

*/
  
SELECT SCHEMA_ID();  





/* 

 B. Returning the schema ID of a named schema

*/
  
SELECT SCHEMA_ID('dbo');

Related Topics:   Metadata Functions   SCHEMA_NAME   sys.schemas

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

TOP

sys.types (Transact-SQL)

Contains a row for each system and user-defined type.

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

name

sysname
system_type_id	tinyint
user_type_id	int
schema_id	int
principal_id	int
max_length	smallint
precision	tinyint
scale	tinyint
collation_name	sysname
is_nullable	bit
is_user_defined	bit
is_assembly_type	bit
default_object_id	int
rule_object_id	int
is_table_type	bit

Related Topics:   ALTER AUTHORIZATION   Catalog Views   OBJECTPROPERTY   Querying the SQL Server System Catalog FAQ   Scalar Types Catalog Views

Scalar Types Catalog Views:   sys.assembly_types

TOP

TYPE_NAME (Transact-SQL)

Returns the unqualified type name of a specified type ID.

TYPE_NAME ( type_id )

/* 

 Examples

*/
  
SELECT o.name AS obj_name, c.name AS col_name,  
       TYPE_NAME(c.user_type_id) AS type_name  
FROM sys.objects AS o   
JOIN sys.columns AS c  ON o.object_id = c.object_id  
WHERE o.name = 'Vendor'  
ORDER BY col_name;  
GO  





/* 

 Examples: Azure SQL Data Warehouse and Parallel Data Warehouse

*/
  
SELECT TYPE_NAME(36) AS Type36, TYPE_NAME(239) AS Type239;  
GO

Related Topics:   Metadata Functions   sys.types   TYPE_ID   TYPEPROPERTY

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

TOP

CONVERT (Transact-SQL)

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

TOP

CONCAT (Transact-SQL)

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   QUOTENAME   REPLACE   REPLICATE   REVERSE   RIGHT   RTRIM   SOUNDEX   SPACE

TOP

ISNULL (Transact-SQL)

Replaces NULL with the specified replacement value.

ISNULL ( check_expression , replacement_value )

/* 

 A. Using ISNULL with AVG

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





/* 

 B. Using ISNULL

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





/* 

 C. Testing for NULL in a WHERE clause

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





/* 

 D. Using ISNULL with AVG

*/
  
-- Uses AdventureWorks  
  
SELECT AVG(ISNULL(Weight, 50))  
FROM dbo.DimProduct;  





/* 

 E. Using ISNULL

*/
  
-- Uses AdventureWorks  
  
SELECT ResellerName,   
       ISNULL(MinPaymentAmount,0) AS MinimumPayment  
FROM dbo.DimReseller  
ORDER BY ResellerName;  
  





/* 

 F. Using IS NULL to test for NULL in a WHERE clause

*/
  
-- Uses AdventureWorks  
  
SELECT EnglishProductName, Weight  
FROM dbo.DimProduct  
WHERE Weight IS NULL;

Related Topics:   COALESCE   expression   Expressions   IS NULL   System Functions   WHERE

System Functions:   BINARY_CHECKSUM   CHECKSUM   COMPRESS   CONNECTIONPROPERTY   CONTEXT_INFO   CURRENT_REQUEST_ID   CURRENT_TRANSACTION_ID   DECOMPRESS   ERROR_LINE   ERROR_MESSAGE   ERROR_NUMBER   ERROR_PROCEDURE   ERROR_SEVERITY   ERROR_STATE   FORMATMESSAGE   GET_FILESTREAM_TRANSACTION_CONTEXT   GETANSINULL   HOST_ID   HOST_NAME   ISNUMERIC

TOP

SELECT Clause (Transact-SQL)

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   IS NULL   PIVOT and UNPIVOT   PREDICT   READTEXT   TOP   UPDATE   UPDATETEXT   WHERE   WRITETEXT

TOP

CASE (Transact-SQL)

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   NULLIF

TOP

FROM (Transact-SQL)

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   IS NULL   PIVOT and UNPIVOT   PREDICT   READTEXT   TOP   UPDATE   UPDATETEXT   WHERE   WRITETEXT

TOP

NOT (Transact-SQL)

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   LIKE   OR   SOME and ANY

TOP

IN (Transact-SQL)

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   AND   ANY   BETWEEN   EXISTS   LIKE   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.