4. T-SQL Database Development - Sky is Limit T-SQL Less Common used features (4 of 4)
This is the fourth T-SQL post of four to provide a stellar understanding of database development using T-SQL. Sky is Limit T-SQL Less Common used features that display T-SQL’s flexibility & power.
4. T-SQL’s Sky is the Limit
d. Temp Tables
e. Select Into
f. Nested Select
h. Case vs. If
l. Template Explorer
n. Try Catch Blocks
a. Alias - Alias are nice since they can simplify queries. Instead of referencing an object with a long name we can create an alias
-- Example Alias created for the table longTableName
From tblLongTableName t
b. Scripting - A script can be from current objects. A table or SP can be quickly scripted to create, modify or drop the object. This is useful when we would like to create rollbacks of sp or need to script a table that you may want to create in another db. In Object Explorer you can right click on the object such as a table then chooses ‘Script as’ --> then chooses Create, Alter or Drop.
c. Commands – commands not used in every query
c.8 With No Lock
c.1 DECLARE - Declare is an assignment statement used when we need to declare a variable in a script or spend
-- Example of Declare
DECLARE @loopCounter int
DECLARE @name varchar (50)
DECLARE @theDate datetime
c.2 SET - Set is an assignment statement used to set variables
-- Examples of SET
SET @loopCounter = 1
SET @name = 'Catto'
c.3 IF - IF is a nice command to use to check for conditions
-- Example of If Statement in T-SQL the rows will be selected if LoopCounter = 1
IF @loopCounter = 1
Select * From newTablePeopleName
c.4 CASE - Evaluates a list of expressions and returns on of many possible results.
-- Example Case Statement
c.5 UNION -- Union can be useful when we want to combine two similar queries
-- Example of a union selecting same column name from two different tables
c.6 LIKE -- Used when querying for only part of a word
-- example to return the Record with Catto in NameOfPerson field
WHERE (nameOfPerson LIKE '%Catt%')
c.7 TOP - Top is used when we only want to select the top N rows from a table. If the table is very large then we select all the rows it may take some time. If we state top 200 the query will return quickly & only return the top 200 records.
-- Example selecting top 10 from table
Select TOP 10 *
c.8 With No Lock - Use to not lock the table
-- example of with no lock
From newTablePeopleName WITH (NOLOCK)
c.9 SUM - Command to calculate the sum
-- Example of Sum of Number of Orders
SELECT SUM(numberOfOrders) AS Total_Quantity
c.10 AVG - Command to calculate the Average
-- Example of Average of Number of Orders
SELECT AVG(numberOfOrders) AS Average_Quantity
d. TEMP TABLES - Temporary Tables are useful when you have a SP & the query is a little more complex than just a one line select statement. A common way I like to use them to create a temp table declare some variable, select my desired values & assign them to my variables, then insert them into the temp table. Toward the end of the proc simply select from the temp table & then drop the temp table. This is beneficial since it's often easier to create, populate (with temp variables) & drop a temp table rather than creating a massive nested select statement. Check out the following code to understand better.
-- Example of SP using Temp Tables:
ALTER PROCEDURE p_getNames
-- Delcare Variables
DECLARE @tableID int
DECLARE @nameOfPerson varchar (50)
-- CREATE temp table tempTablePeeps
CREATE TABLE #tempTablePeeps
ttpPeepID int NULL
, ttpPeepName varchar(50) NULL
-- Select & set variables
SET @tableID =
SET @nameOfPerson =
-- Insert Values into temp Table
INSERT INTO #tempTablePeeps
-- Select Values from Temp Table
Select ttpPeepID as ID
, ttpPeepName as PeepsName
Drop Table #tempTablePeeps
e. Select Into - Select Into is a nice way to inert into values into a table such as temp table
-- Example: Select into temp table
-- Example: In the next nifty Select into statement an identity column is created on the temp table which could be quite useful especially if the newTablePeopleName table didn't have an identity.
, IDENTITY( int ) AS workingID
f. Nested Select - Select statements can be used with Select statements. This creates extremely powerful queries.
-- Example Nested Select / Sub Select where we are selecting simply put the name where id = 1
WHERE newTableid =
( SELECT newTableid
WHERE nameOfPerson = 'Catto'
g. Cursors - Cursors all us to take a subset of data & output the data many different ways. When we work with Cursors it's always best practice to CLOSE & DEALLOCATE.
h. Case vs. If - Case statement vs. If conditions is an interesting performance face off puck drop. For example there are conditions we could need to check say 3 choices (orange, blue & black). We could right 3 IF statements or we could use the Case statement. Both accomplish the task & the question arises which give us better perf?
i. Dependencies - We can view dependencies on an object which is useful sometimes. For example if we right click on a table in Object explorer & select 'View Dependencies' it will bring open a dialog box with all the object that depend on it.
j. Grants - We sometimes need to Grant Permission for objects such as a Stored Procedures if we wanted to Grant Execute Permission to a User. Another example would be to Grant Select permission on a table to a username
-- Example of Grant Statement
GRANT EXECUTE ON p_getNames
k. Schemas - Schemas are not used to often but can be useful. Most of the db's we use will have the default schema dbo (database owner).
-- example of schemas
l. Template Explorer - IN SQL Server 2008 Management Studio there is a nice feature in the view menu called 'Template Explorer' It provides some templates for common functions. I like to look there when there is a task I'm not doing every day such as adding a constraint to a table. We can open it by the view menu, template explorer (alt+v, L).
m. DateTime Formatting - Using the classic ever prevalent datatype datetime the format is as such 2/13/2010 12:00:00 AM which doesn't look great if we only want a date. In SQL Server 2008 there are some new date data types which are nice. Below is a nice example of how I used T-SQL to take an ugly date & make it pretty.
-- Example of how to make a pretty
SET @prettyDate =
SELECT CAST(DATEPART(month, @uglyDate ) as varchar(2)) + '.' + CAST(DATEPART(day, @uglyDate ) as varchar(2)) + '.' + CAST(DATEPART(year, @uglyDate ) as varchar(4))
n. TRY CATCH Block - In SQL Server 2005 the try catch blocks were introduced. They work just like try catch blocks in .NET.
This is the fourth T-SQL post of four to provide a stellar understanding of database development using T-SQL. As we’ve seen here in the four post series T-SQL is powerful way of obtaining data.
Here are the previous posts in the series:
T-SQL Database Development IDE & Queries (1 of 4)
T-SQL Database Development Design View vs. Query Editor Window (2 of 4)
T-SQL Database Development Stored Procedures (3 of 4)