SQL Server Concepts and Best Practices to Build Transact SQL Stored Procedures

Introduction

Microsoft SQL Server has a lot of components that you can use. If you want a DBMS, you the SQL Server Database Engine is a really good choice.

SQL Server Database Engine

The SQL Server Database Engine includes:

• Core service for storing
• Processing, and securing data
• Replication
• Full-text search, and tools for managing relational and XML data

Database Engine Components

Relational Engine

The core of the database engine is responsible for all the relevant security decisions.

Storage Engine

This is a resource provider, and manages the resources by using the Operating System. When the relational engine tries to execute a query for the first time, it calls the Storage engine to retrieve the object, put the object in memory, and returns the pointer to the Execution engine.

Relational Engine Tasks

• Establish the user context
• Check the syntax of each Transact SQL query
• Compile each query
• Check permissions and determines if the query can be executed by the user associated with the request
• Optimizes the query request
• Builds and stores the query plan on the cache, and executes the query

Query Processing

The Query Optimizer checks inputs and determines the most efficient way to process information and builds the query execution plan that consists of a sequence in which the tables can be accessed and the methods used to get the information.

Best Practices to Build SQL Statements

Sub Queries vs. Joins

• The performance of a query can be similar to using a sub query or a join
• Difference: a sub query can require a Query Optimizer to perform additional steps

Query with Date Range

One of the difficulties when a programmer works with Transact SQL is working with date ranges; some useful ways to work with them are shown in the next examples:

Example 1:

use Northwind 
declare @iniDate as varchar(15)
declare @endDate as varchar(15)
set @iniDate = '19960710'
set @endDate = '19960716'
select * from dbo.Orders 
where ShippedDate between @iniDate and @endDate

Example 2:

declare @iniDate as varchar(8)
declare @endDate as varchar(8)
set @iniDate = '20080801'
set @endDate = '20081125'

select * from  sysobjects 
where crdate  between @iniDate and dateadd (d, 1, cast(@endDate as datetime))

Logical Operations and the Use of Parenthesis

You also must know that the following sentences are different; you must be clear about the concepts of logic and mathematics:

Example 1:

select * from dbo.Orders 
where EmployeeID = 5 and ShipRegion = 'RJ' OR ShipRegion = 'SP'

Example 2:

select * from dbo.Orders 
where EmployeeID = 5 and (ShipRegion = 'RJ' OR ShipRegion = 'SP')

Conditionals and “IF” Sentences

Some of the sentences that you write using IF sentences can be rewritten in a different way, see these examples:

Example 1:

use Northwind 
declare @chrNOMBRE  as nvarchar(40)
set @chrNOMBRE  = 'Queso'
--set @chrNOMBRE  = '‘

IF @chrNOMBRE = '' 
select * from dbo.Products
ELSE 
select * from dbo.Products where ProductName like '%'+@chrNOMBRE+'%'

This is equivalent to:

select * from dbo.Products 
where ProductName like '%' + UPPER(@chrNOMBRE) + '%'

Example 2:

use Northwind 
Declare @productID as int 
set  @productID = 3
if @productID = 0 
begin
      select * from dbo.Products 
end
else
begin
      select * from dbo.Products 
      where ProductID = @productID 
end

It’s equivalent to:

use Northwind 
Declare @productID as int 
set  @productID = 0
select * from dbo.Products 
where ProductID = case @productID when 0 then ProductID else @productID end

Rows Count

The best way to make a row count is by using a constant inside the function instead of using “*”; that’s because SQL Server first gets all the information of the columns and rows required and then counts the data. The sentence below:

SELECT     @NUM=COUNT(*) FROM Products

Is better if you rewrite in this way:

SELECT     @NUM= COUNT (1) FROM  Products

Null Values

You cannot compare null values on a sentence like in the next example:

Example 1:

select * from dbo.Orders where ShipRegion = null

Is better if you do the following:

select * from dbo.Orders where ShipRegion is null 

Example 2:

declare @ShipRegion nvarchar(15)
set @ShipRegion = ''
select * from dbo.Orders 
where ShipRegion = case when @ShipRegion = '' then ShipRegion else @ShipRegion end

Having that in the previous declaration, the column “ShipRegion” allows nulls, the declaration must be rewritten as follows:

declare @ShipRegion nvarchar(15)
set @ShipRegion = ''
select * from dbo.Orders 
where isnull(ShipRegion, '') = case when @ShipRegion = '' 
      then isnull(ShipRegion, '') else @ShipRegion end

Get Values from a Table

use Northwind 
Declare @productID as int 
Declare @productName as nvarchar(40)
Declare @QuantityPerUnit as nvarchar(40)
Declare @UnitPrice as money

/*You can do this:*/
set  @productID = (select top 1 ProductID from dbo.Products)
set  @productName = (select top 1 ProductName from dbo.Products)

/*but, Is better to do the following:*/
select top 1 @productID = ProductID, @productName = ProductName, 
            @QuantityPerUnit = QuantityPerUnit, @UnitPrice = UnitPrice 
from dbo.Products 

select top 1 @productID, @productName, @QuantityPerUnit, @UnitPrice

Considerations When Coding Stored Procedures with Transact SQL

• Use standards
• Write comments (that helps future reviews and corrections)
• Try to avoid the use of cursors
• Don't write queries with “SELECT *”
• Use "SET NOCOUNT ON"
• Be careful with the use of transactions
• Avoid using more than one action in a Stored Procedure
• Use “RETURN” if you are going to get a value from the database

References

• Editions and Components of SQL Server 2008: http://msdn.microsoft.com/en-us/library/ms144275.aspx
• http://msdn.microsoft.com/en-us/library/cc280362(v=SQL.100).aspx
• http://technet.microsoft.com/en-us/library/cc917631.aspx