T-SQL – Basic selects

The shortest select which returns single cell in aliased column.

  SELECT 'fakeData' AS ColumnA 

Read current time. The first two return datetime and the last one returns datetime2. The first one is SQL-92 standard.

 SELECT CURRENT_TIMESTAMP, GETDATE(), SYSDATETIME() 

Read current day.

 SELECT DATEPART(DAYOFYEAR, CURRENT_TIMESTAMP) 

Select top 5 rows sorting by price.

 SELECT TOP 5 * FROM [Production].[Products] 
ORDER BY unitprice DESC 

Select top 5% rows sorting by price.

 SELECT TOP 5 PERCENT * FROM [Production].[Products]
ORDER BY unitprice DESC 

Skip n rows and select next m rows (can be used for paging).

 SELECT * FROM [Production].[Products]
ORDER BY unitprice DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY 

Filter rows. Remember to use filtering value data type exactly like in column to avoid casting.
Query without casting when CITY column data type is nvarchar(15):

SELECT * FROM [Production].[Suppliers]
WHERE CITY = N'London'

Query which causes casting from nvarchar to varchar (and is slower due to that fact):

SELECT * FROM [Production].[Suppliers]
WHERE CITY = 'London'
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s