T-SQL Quick Reference - SELECT

Topic	Using ESCAPE keyword
Example 1	WHERE Description LIKE '%/%%' ESCAPE '/'
Example 2
Remarks
Topic	Declaring and Assigning Values to Variables
Example 1	Pre-2008 method: DECLARE @AddressLine1 nvarchar(60) SET @AddressLine1 = 'Heiderplatz' SELECT AddressID, AddressLine1 FROM Person.Address WHERE AddressLine1 LIKE '%' + @AddressLine1 + '%'
Example 2	2008 method: DECLARE @AddressLine1 nvarchar(60) = 'Heiderplatz' SELECT AddressID, AddressLine1 FROM Person.Address WHERE AddressLine1 LIKE '%' + @AddressLine1 + '%'
Remarks
Topic	Use TOP Keyword with Ordered Results
Example 1	select top 10 * from  [Person].[Address] order by ModifiedDate; select top 10 percent * from  [Person].[Address] order by ModifiedDate
Example 2	declare @Percentage float = 1 select top (@Percentage) percent * from [Person].[Address] order by ModifiedDate declare @Percentage integer = 1 select top (@Percentage) * from [Person].[Address] order by ModifiedDate
Remarks
Topic	Declaring and Assigning Values to Variables
Example 1	Pre-2008 method: DECLARE @AddressLine1 nvarchar(60) SET @AddressLine1 = 'Heiderplatz' SELECT AddressID, AddressLine1 FROM Person.Address WHERE AddressLine1 LIKE '%' + @AddressLine1 + '%'
Example 2	2008 method: DECLARE @AddressLine1 nvarchar(60) = 'Heiderplatz' SELECT AddressID, AddressLine1 FROM Person.Address WHERE AddressLine1 LIKE '%' + @AddressLine1 + '%'
Remarks
Topic	Use TOP Keyword with Ordered Results
Example 1	select top 10 * from  [Person].[Address] order by ModifiedDate; select top 10 percent * from  [Person].[Address] order by ModifiedDate
Example 2	declare @Percentage float = 1 select top (@Percentage) percent * from [Person].[Address] order by ModifiedDate declare @Percentage integer = 1 select top (@Percentage) * from [Person].[Address] order by ModifiedDate
Remarks
Topic	Using Column Aliases
Example 1	select top 1 AddressID AS "ADDRESS ID", AddressID "Address ID" from [Person].[Address]
Example 2
Remarks
Topic	Performing String Concatenation
Example 1	select convert(varchar(25),AddressID) + ' Is in City ' + City from [Person].[Address]
Example 2	declare @DatabaseName varchar(200) = '' select @DatabaseName = @DatabaseName + d.name + ',' from master.sys.databases d order by d.name select @DatabaseName GO
Remarks
Topic	SELECT .. INTO (CATS equivalent)
Example 1	select * into Person.Address_empty from Person.Address where 1=2
Example 2
Remarks	Caution: Although the structure of the selected columns is reproduced, the constraints, indexes, and other separate objects dependent on the source table are not copied.
Topic	Using Derived Tables
Example 1	SELECT DISTINCT s.PurchaseOrderNumber FROM Sales.SalesOrderHeader s INNER JOIN (SELECT SalesOrderID FROM Sales.SalesOrderDetail WHERE UnitPrice BETWEEN 1000 AND 2000) d ON s.SalesOrderID = d.SalesOrderID
Example 2	SELECT DISTINCT s.PurchaseOrderNumber FROM Sales.SalesOrderHeader s, (SELECT SalesOrderID FROM Sales.SalesOrderDetail WHERE UnitPrice BETWEEN 1000 AND 2000) d where s.SalesOrderID = d.SalesOrderID
Remarks
Topic	Using CROSS APPLY & OUTER APPLY
Example 1	SELECT d.name,v.FileId,v.BytesRead,v.BytesWritten FROM master.sys.databases d cross apply fn_virtualfilestats(DB_ID(d.name),null) v ; GO
Example 2	SELECT d.name,v.FileId,v.BytesRead,v.BytesWritten FROM master.sys.databases d outer apply fn_virtualfilestats(DB_ID(d.name),null) v ; GO
Remarks	The outer apply display non-matching rows in  the base table
Topic	Using TABLESAMPLE
Example 1	select * from person.address tablesample system (1 percent)
Example 2
Remarks	The percentage is the percentage of the table’s data pages. Once the sample pages are selected, all rows for the selected pages are returned.
Topic	Using PIVOT and UNPIVOT
Example 1	select * from (select addressid,city from person.address)  a pivot (count(addressid) for city in ([Spokane],[Columbus])) as b
Example 2	select name, b.proprety_type,b.property_value from (select name,convert(varchar(50),create_date) create_date, convert(varchar(50),collation_name) collation_name  from master.sys.databases) a unpivot (property_value for proprety_type in ([create_date],[collation_name]) ) b
Remarks	The list of pivoted column names cannot already exist in the base table or view query columns being pivoted. For UNPIVOT, all columns have to be of the same data type AND length