Click here to Skip to main content
12,451,446 members (54,633 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

44.2K views
6 bookmarked
Posted

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

, 3 Jun 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
Explains basic SQL Server concepts and some 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

Diagram 1 - SQL Server Database Engine and the Windows Operating System

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.

Diagram 2 - Database Engine Components

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.

Query optimization of a SELECT statement

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

No Biography provided

You may also be interested in...

Pro
Pro

Comments and Discussions

 
General[My vote of 1] Could be a good start but needs work sorry Pin
spoodygoon3-Jun-10 14:47
memberspoodygoon3-Jun-10 14:47 
GeneralMy vote of 1 Pin
icestatue3-Jun-10 9:36
membericestatue3-Jun-10 9:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160826.1 | Last Updated 3 Jun 2010
Article Copyright 2010 by Richard Espinoza
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid