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
| 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
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
| 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
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
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
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
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
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
| 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
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
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 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
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
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
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
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
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
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.
