Sunday, May 26, 2013

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