T-SQL Database Development - Sky is Limit T-SQL Less Common used features (4 of 4)

4. T-SQL Database Development - Sky is Limit T-SQL Less Common used features (4 of 4)

Hey Now,

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
  a. Alias
  b. Scripting
  c. Commands
  d. Temp Tables
  e. Select Into
  f. Nested Select
  g. Cursors
  h. Case vs. If
  i. Dependencies
  j. Grants
  k. Schemas
  l. Template Explorer
  m. Datetime
  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
Select t.longTableNameID
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.1 Declare
  c.2 SET
  c.3 If
  c.4 Case
  c.5 Union
  c.6 Like
  c.7 Top
  c.8 With No Lock
  c.9 Sum
  c.10 Avg
 
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
BEGIN
Select * From newTablePeopleName
END

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
Select nameOfPerson
From newTablePeopleName
UNION
Select nameOfPerson
From oldTablePeepsName

c.6 LIKE -- Used when querying for only part of a word
-- example to return the Record with Catto in NameOfPerson field
Select *
From newTablePeopleName
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 *
From newTablePeopleName

c.8 With No Lock - Use to not lock the table
-- example of with no lock
Select *
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
FROM newTablePeopleName

c.10 AVG - Command to calculate the Average
-- Example of Average of Number of Orders
SELECT AVG(numberOfOrders) AS Average_Quantity
FROM newTablePeopleName

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
AS
BEGIN
-- 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 =
(
Select newTableid
From [newTablePeopleName]
)
SET @nameOfPerson =
(
Select nameOfPerson
From [newTablePeopleName]
)
-- Insert Values into temp Table
INSERT INTO #tempTablePeeps
(
ttpPeepID
, ttpPeepName
)
VALUES
(
@tableID
, @nameOfPerson
)
-- Select Values from Temp Table
Select ttpPeepID as ID
, ttpPeepName as PeepsName
From #tempTablePeeps
Drop Table #tempTablePeeps
END

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
SELECT
newTableid
, nameOfPerson
INTO #tempTable
FROM [newTablePeopleName]
-- 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.
SELECT
newTableid
, nameOfPerson
, IDENTITY( int ) AS workingID
INTO #tempTable
FROM [newTablePeopleName]

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
SELECT nameOfPerson
FROM [newTablePeopleName]
WHERE newTableid =
( SELECT newTableid
FROM newTablePeopleName
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
TO [Catto_Person_User]
GO

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
[sqlserver].[schema].[object]
[sql1].[dbo].[newTablePeopleName]

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)

Later,
Catto