SQL SERVER Encyclopedia- SQL SERVER Tips & Sugegstions- BuggFixing & Trouble Shooting

Row Oriented Security Using Triggers

2010-04-27T04:36:00.000-07:00

Overview
This article introduces a simple model for row-oriented security. It is based on a small number of security tables and a few triggers. The model is independent of SQL Server's own security, and may be used in other database products with minimal change.

The basic premise of this model is that some user table acts as a gateway to all other tables. Row-oriented security uses a single table to list which rows of the gateway table may be accessed by a given user. Such users may access related rows in other tables if access has been granted to the corresponding gateway rows.

It also assumes that users will access the database through approved applications only. That's because the model puts a tiny part of the security burden on the front-end applications instead of the database server (where most of the security processing occurs). In particular, those apps can implement additional row-oriented and column-oriented security without additional programming.

This model has been successfully used by a large Medical faculty for several years.

Example

To make things really simple, suppose our database contains exactly one table Employee for which, say, an HR application has been written:

Fig. 1

In that application, we would like each user to be restricted to various rows depending on the roles that user has been assigned. We would like those restrictions to be defined in a set of security tables that the application reads at run-time, and which may be modified in real-time by an administrator.

The rows in Employee that each role may access will be defined by filters on that table, such as:

SELECT EmployeeId AS SelectId FROM Employee WHERE City = 'Vancouver'Such filters simply list key values of the gateway table with a fixed name SelectId. Each role may have any number of filters assigned to it and each user may have multiple roles. For convenience, the gateway table's key and SelectId are INT columns (although that can be easily changed).

We also want the rows that any role (and user) can see to accurately reflect the current state of the data (an employee may move at any time, for example) or the filters currently assigned to it (they may be inserted or deleted at any time).

Furthermore, the app itself should automatically impose additional row- and column-oriented constraints, such as preventing record deletions or hiding certain fields, depending on the restrictions entered into the security tables by the administrator.

The model presented here does that, using special security tables that designate what roles users belong to, what rows each role may access in the gateway table, what kind of editing may be done on those rows, and what columns will be hidden by the apps.

Functionality

Users, roles and restrictions are defined by an organization using special security tables added to the database.

A row-oriented restriction is nothing more than a filter defining what rows of the gateway table may be accessed by a given role. Such filters may also be defined using additional tables, such as lookup tables.

Users may be assigned any number of roles. They automatically inherit the "sum" of all security restrictions on those roles.

The filtering mechanism for invoking security works dynamically, using triggers that fire whenever the gateway table is modified:

If any row in the gateway table becomes visible to a security filter (because some underlying data has suddenly changed) then that row is immediately available to any role whose security includes that filter.

Correspondingly, if any row in the gateway table becomes invisible to a security filter then that row is no longer available to any role whose security included that filter (unless some other security filter for that role still allows it).

Furthermore, any changes to the set of security filters is immediately witnessed by all users, using triggers that fire whenever one of them is added or deleted:

If any security filter is added, then each row in the gateway table defined by it is immediately available to any role whose security includes that filter.

Correspondingly, if any security filter is deleted then each row of the gateway table defined by it is no longer available to any role whose security included that filter (unless some other security filter for that role still allows it).

Implementation

The entire system is written using triggers and stored procedures. So it is basically independent of any desktop or web application, which need only include some standard, front-end security code to use it. That's why only "approved" applications are allowed to access the database. Of course, one may bypass the security model by simply ignoring the security tables. That allows developers to test applications that are security-free, before imposing user restrictions prior to deployment. Because a single relationship connects the security tables to the gateway table, adding security to any approved app simply involves "fooling" the app by replacing the gateway table with a recordset filtered by security. In that way, legacy apps may have row-oriented security bolted onto them fairly quickly.

For convenience, a VB.NET security manager interactively defines users, roles and restrictions. Any changes to security are witnessed by users when they log in through an approved app.

This system is ideal for a Citrix server farm (where it was originally developed), where only approved applications access data and the underlying recordsets and code are hidden from the user.

Security Model

Zoom in
Open in new window

Fig. 2

The lkpUser and lkpRole tables define users and roles, connected by the tblUserRole table.

The lkpView table defines various "views" (not SQL views) a role might use to access data from the same table under various contexts. To keep things simple for this article, there's only one view and everybody has it.

The key security table is tblRoleAccessSelect. For each record, the foreign key SelectId points to a record in the gateway table Employee for a given Role and View. Approved applications no longer see Employee (which has been renamed and hidden), but rather a subset of it silently filtered by the security table tblRoleAccessSelect. Users will not be aware of this restriction except that rows in the filtered table will appear/disappear whenever changes are made to user data or the tblRoleAccessSelectFilter table. That's because the key security table is dynamically populated by whatever filters have been added or deleted in the tblRoleAccessSelectFilter table.

The tblRoleAccessSelectFilter table assigns any number of filters to each role and view. Any such filter is simply a query with one column SelectId that lists what rows are available in the Employee table (which is our gateway table for his article).

The main problem is to dynamically change tblRoleAccessSelect whenever user data changes (which will affect the output of those filters), or when changes are made to the tblRoleAccessSelectFilter table. Triggers and stored procedures do this automatically.

The lkpAccessRow and lkpAccessColumn tables define additional row- and column- oriented restrictions that each app could automatically impose using front-end code provided by the model:

Fig. 3

For rows, you may allow/disallow editing, adding, or deleting. For columns, you may allow hiding or locking (or specify full control). A given user may have several roles and views, so the highest priority wins in each case.

The tblRoleAccessRow table assigns available row-level restrictions to various roles and views.

The tblRoleAccessColumn table assigns available column-level restrictions to various roles and views.

It's these tables that the HR application's front-end code uses to decide how to edit rows that have been made available to it, and what columns it can see. This code may be further customized if you define more restrictions in the lkpAccessRow and lkpAccessColumn tables.

How The Model Works (server side)

The key security table tblRoleAccessSelect points to those records in the gateway table Employee that may be seen by any given role and view.

If a row is updated/inserted into the gateway table, the following trigger passes its key to the stored proc sqRoleAccessSelectSync.

This proc insures that the correct rows in the key security table are inserted or deleted, to re-synchronize with all of the filters assigned to each role and view:

ALTER TRIGGER [dbo].[TR_Employee_Update_Insert] ON [dbo].[Employee]

AFTER UPDATE, INSERT

AS

-- No counting

SET NOCOUNT ON

-- Declarations

DECLARE @MyId INT

-- If exactly one record updated/inserted

IF (SELECT COUNT(*) FROM INSERTED) = 1

BEGIN

-- Determine key of record

SELECT @MyId = (SELECT EmployeeId FROM INSERTED)

-- Synchronize security table for record updated/inserted

EXECUTE sqRoleAccessSelectSync @MyId

END

In other words, when user data changes, the key security table will always be in sync with the filters initially used to populate it.

The sole task of the stored proc sqRoleAccessSelectSync is to insure that for each role, view and filter in the table tblRoleAccessSelectFilter, the row (role, view, Id) belongs to the table tblRoleAccessSelect if and only if Id is selected by at least one of the filters in that table with the same role and view:

ALTER PROCEDURE [dbo].[sqRoleAccessSelectSync]

(

@Id INT

)

AS

/*

For each Role, View, and Filter in tblRoleAccessSelectFilter,

check if @Id belongs to that filter's result set.

Insure that (Role, View, @Id) belongs to RoleAccessSelect if, and only if,

@Id belongs to at least one of the result sets for that Role and View.

Note that each Role, View combination may be associated with multiple Filters.

So if @Id belongs to the result set of one filter, care must be taken to avoid

deleting the corresponding row in RoleAccessSelect if @Id does not belong to the

result set of another filter with the same Role and View.

Avoid this by ordering the Filters by Role and View and using a flag to check

for an inserted @Id for all Filters of a given Role and View.

Reset that flag when a new Role and View combination appears.

*/

-- Declarations

DECLARE @RoleIdCurrent INT -- current Role for all Filters being checked

DECLARE @ViewIdCurrent INT -- current View for all Filters being checked

DECLARE @ynInserted INT -- inserted flag for all Filters being checked with given Role, View

-- Declarations for cursor

DECLARE @RoleId INT

DECLARE @ViewId INT

DECLARE @FilterName NVARCHAR(256)

-- Cursor for tblRoleAccessSelectFilter listing Roles, Views, and Filters (ordered by Role, View)

DECLARE curTable CURSOR

FOR

SELECT RoleId, ViewId, FilterName FROM dbo.tblRoleAccessSelectFilter ORDER BY RoleId, ViewId

FOR READ ONLY

-- Set inserted flag to off

SET @ynInserted = 0

-- Open cursor

OPEN curTable

-- Fetch first row

FETCH NEXT FROM curTable INTO @RoleId, @ViewId, @FilterName

-- Save current Role, View combination for which inserted check will be monitored

SET @RoleIdCurrent = @RoleId

SET @ViewIdCurrent = @ViewId

-- Do this while rows are fetched and inserted @Id does not exist for current Role, View

-- If inserted @Id exists, reset inserted flag to prevent possible deletion later on for current Role, View

-- Reset inserted flag to off if new Role, View combination appear

While @@FETCH_STATUS = 0

BEGIN

-- Check if @ID is in filter and insert/delete corresponding row into tblRoleAccessSelect if it is/is not

-- But only do this if inserted flag is off (otherwise there's nothing more to do for current Row, View)

IF @ynInserted = 0

EXECUTE sqRoleAccessSelectSync2

@Id = @Id,

@RoleId = @RoleId,

@ViewId = @ViewId,

@FilterName = @FilterName,

@ynInserted = @ynInserted OUTPUT

FETCH NEXT FROM curTable INTO @RoleId, @ViewId, @FilterName

-- If Row, View combination changes, reset inserted flag to off and continue

IF (@RoleId <> @RoleIdCurrent) OR (@ViewId <> @ViewIdCurrent)

BEGIN

SET @ynInserted = 0

SET @RoleIdCurrent = @RoleId

SET @ViewIdCurrent = @ViewId

END

END

-- Close cursor

CLOSE curTable

-- Deallocate cursor

DEALLOCATE curTable

It does this by calling the stored proc sqRoleAccessSelectSync2 which insures that the row (role, view, Id) belongs to the table tblRoleAccessSelect if and only if Id is selected by the passed filter. However, this proc passes back to its caller a flag indicating whether an insertion occurred (or the row (role, view, Id) was already found to exist) so that the calling program knows when to avoid deleting rows unnecessarily: ALTER PROCEDURE [dbo].[sqRoleAccessSelectSync2]

(

@Id INT,

@RoleId INT,

@ViewId INT,

@FilterName NVARCHAR(256),

@ynInserted INT OUTPUT

)

AS

/*

Insure that (RoleId, ViewId, @Id) belongs to RoleAccessSelect

if @Id belongs to the result set of filter @FilterName (otherwise delete it).

Note that this call could delete (RoleId, ViewId, @Id) in tblRoleAccessSelect after

an earlier call inserted it, or discovered it was already inserted, for the same

RoleId, ViewId.

So the calling script must insure that this doesn't happen by monitoring the value

of @ynInserted set by this call.

*/

BEGIN

SET NOCOUNT ON

-- Declarations

DECLARE @SQLString NVARCHAR(1028)

-- Set inserted flag off

SET @ynInserted = 0

-- Check if @Id belongs to the result set of filter @FilterName

-- Use temporary table to avoid result sets in caller

-- Note that SelectId is always a single column in @FilterName, by convention

SET @SQLString = N'SELECT TOP 1 * INTO #t FROM dbo.' + @FilterName + ' WHERE (SelectId = ' + CAST(@Id AS NVARCHAR(10)) + ')'

EXEC sp_executesql @SQLString

-- If it is

IF (@@ROWCOUNT = 1)

BEGIN

-- Check if it is in tblRoleAccessSelect with given Role and View

SET @SQLString = N'SELECT SelectId FROM tblRoleAccessSelect WHERE SelectId = ' + CAST(@Id AS NVARCHAR(10)) + ' AND RoleId = ' + CAST(@RoleId AS NVARCHAR(10)) + ' AND ViewId = ' + CAST(@ViewId AS NVARCHAR(10))

EXEC sp_executesql @SQLString

-- If it isn't

IF (@@ROWCOUNT = 0)

BEGIN

-- Insert it

SET @SQLString = N'INSERT INTO tblRoleAccessSelect VALUES (' + CAST(@RoleId AS NVARCHAR(10)) + ',' + CAST(@ViewId AS NVARCHAR(10)) + ',' + CAST(@Id AS NVARCHAR(10)) + ')'

EXEC sp_executesql @SQLString

-- Set inserted flag on (because it has just been inserted)

SET @ynInserted = 1

END

-- If it is

ELSE

-- Set inserted flag on (because it was already inserted)

SET @ynInserted = 1

END

ELSE

-- If it isn't, then it can't be for this (or any other) Role, View combination

BEGIN

-- So delete it for current Role, View combination

SET @SQLString = N'DELETE FROM tblRoleAccessSelect WHERE RoleId = ' + CAST(@RoleId AS NVARCHAR(10)) + ' AND ViewId = ' + CAST(@ViewId AS NVARCHAR(10)) + ' AND SelectId = ' + CAST(@Id AS NVARCHAR(10))

EXEC sp_executesql @SQLString

END

END

It should be noted that if a row is deleted from the Employee table, the (cascading delete) relationship connecting it to the key security table takes affect.

A subtle condition on the model is that if a row can/cannot be seen in the gateway table by a given filter, then it remains that way no matter what other other rows are modified. For example, if a filter sees an Employee because they live in Vancouver, that wouldn't change if someone else moves to/from that city. For a bizarre example, a filter that sees everything if and only if there are exactly 100 rows would be invalid. This condition is always satisfied by ordinary filtering for our purposes.

Of course, we must also handle the event where rows have been inserted/deleted in the tblRoleAccessSelectFilter table (updates are not allowed).

For insertions, the following trigger TR_tblRoleAccessSelectFilter_Insert on that table is used to populate the key security table with key values for the inserted role and view that aren't already present (to avoid duplicates):

ALTER TRIGGER [dbo].[TR_tblRoleAccessSelectFilter_Insert] ON [dbo].[tblRoleAccessSelectFilter]

FOR INSERT

AS

BEGIN

-- Declarations

DECLARE @SQLString NVARCHAR(1028)

DECLARE @RoleId INT

DECLARE @ViewId INT

DECLARE @FilterName NVARCHAR(256)

-- Do nothing unless INSERTED contains exactly one row

IF @@ROWCOUNT <> 1

RETURN

-- Avoid extra result sets

SET NOCOUNT ON

-- Get RoleId, ViewId, Filter from record that's been INSERTED

SELECT @RoleId = (SELECT RoleId FROM INSERTED)

SELECT @ViewId = (SELECT ViewId FROM INSERTED)

SELECT @FilterName = (SELECT FilterName FROM INSERTED)

-- Insert into tblRoleAccessSelect

-- Note that @FilterName must name an existing filter (otherwise insertion will fail)

SET @SQLString =

N'INSERT INTO tblRoleAccessSelect(RoleId,ViewId,SelectId) SELECT D1.RoleId,D1.ViewId,D1.SelectId FROM

(SELECT ' + CAST(@RoleId as NVARCHAR(10)) + ' AS RoleId,' + CAST(@ViewId as NVARCHAR(10)) + ' AS ViewId,' + @FilterName + '.SelectId FROM ' + @FilterName + ') D1 LEFT OUTER JOIN tblRoleAccessSelect ON ' + 'D1.RoleId = tblRoleAccessSelect.RoleId AND ' + 'D1.ViewId = tblRoleAccessSelect.ViewId AND D1.SelectId = tblRoleAccessSelect.SelectId WHERE tblRoleAccessSelect.SelectId IS NULL'

EXEC sp_executesql @SQLString

END

-- Finalization

SET NOCOUNT OFF

Deletions are a little more complicated:

ALTER TRIGGER [dbo].[TR_tblRoleAccessSelectFilter_Delete] ON [dbo].[tblRoleAccessSelectFilter]

FOR DELETE

AS

BEGIN

-- Declarations

DECLARE @SQLString NVARCHAR(2048) -- outer SQL string

DECLARE @SQLString1 NVARCHAR(2048) -- inner SQL string consisting of UNIONs (variable number)

DECLARE @RoleId INT

DECLARE @ViewId INT

DECLARE @FilterName NVARCHAR(256)

DECLARE @RoleIdSave INT

DECLARE @ViewIdSave INT

DECLARE @FilterNameSave NVARCHAR(256)

-- Do nothing unless DELETED contains exactly one row

IF (SELECT COUNT(*) FROM DELETED) <> 1

RETURN

-- Avoid extra result sets

SET NOCOUNT ON

-- Get @RoleId, @ViewId, @Filter from DELETED record

SELECT @RoleId = (SELECT RoleId FROM DELETED)

SELECT @ViewId = (SELECT ViewId FROM DELETED)

SELECT @FilterName = (SELECT FilterName FROM DELETED)

-- Save Role, View and Filter for use in building SQL outer string

SET @RoleIdSave = @RoleId

SET @ViewIdSave = @ViewId

SET @FilterNameSave = @FilterName

-- Start building inner SQL string

SET @SQLString1 = 'SELECT SelectId FROM ' + @FilterName

-- Cursor for tblRoleAccessSelectFilter listing Filters

-- with the same Roles, Views as DELETED record

DECLARE curTable CURSOR

FOR

SELECT FilterName FROM dbo.tblRoleAccessSelectFilter

WHERE

RoleId = @RoleId AND

ViewId = @ViewId

FOR READ ONLY

-- Open cursor

OPEN curTable

-- Fetch next row (note that deleted row does not belong to cursor)

FETCH NEXT FROM curTable INTO @FilterName

-- Continue building SQL inner string

While @@FETCH_STATUS = 0

BEGIN

SET @SQLString1 = @SQLString1 + ' UNION ALL SELECT SelectId FROM ' + @FilterName

FETCH NEXT FROM curTable INTO @FilterName

END

-- Delete from tblRoleAccessSelect (explanation below)

SET @SQLString =

N'DELETE FROM tblRoleAccessSelect WHERE

RoleId = ' + CAST(@RoleIdSave AS NVARCHAR(10)) + ' AND

ViewId = ' + CAST(@ViewIdSave AS NVARCHAR(10)) + ' AND

SelectId IN

(

SELECT tblRoleAccessSelect.SelectId FROM tblRoleAccessSelect

INNER JOIN ' +

@FilterNameSave +

' ON

tblRoleAccessSelect.SelectId = ' + @FilterNameSave + '.SelectId

INNER JOIN

(

SELECT SelectId FROM

(' +

@SQLString1 +

') d1

GROUP BY SelectId

HAVING COUNT(*) = 1

) d2

ON

tblRoleAccessSelect.SelectId = d2.SelectId

)'

/*

Sample deletion for filter ftr1 but where ftr2 has same Role, View.

Avoid deleting any SelectId belonging to ftr1 if it also belongs to ftr2.

DELETE

FROM tblRoleAccessSelect WHERE

RoleId = 1 AND

ViewId = 1 AND

SelectId IN

(

-- Find the SelectId values in tblRoleAccessSelect belonging to ftr1

SELECT tblRoleAccessSelect.SelectId FROM tblRoleAccessSelect

INNER JOIN

ftr1

ON

tblRoleAccessSelect.SelectId = ftr1.SelectId

-- But make sure they don't also belong to ftr2

INNER JOIN

(

-- UNION of SelectId for two filters without duplication (but where COUNT = 1).

-- This guarantees that each SelectId belongs to exactly one filter.

SELECT SelectId FROM

(

-- UNION of SelectId for two filters (with duplication)

-- This is the inner SQL string

SELECT SelectId FROM ftr1

UNION ALL

SELECT SelectId FROM ftr2

) d1

GROUP BY SelectId

HAVING COUNT(*) = 1

) d2

ON

tblRoleAccessSelect.SelectId = d2.SelectId

)

*/

EXEC sp_executesql @SQLString

-- Close cursor

CLOSE curTable

-- Deallocate cursor

DEALLOCATE curTable

-- Finalization

SET NOCOUNT OFF

END

Here we have to be careful not to delete a row in the key security table if some other filter with the same role and view still wants it present.

How The Model Works (client side)

Each client application runs a hidden piece of security code when any form is opened, which automatically provides additional row- or column- oriented security. For example, the following snippet locks or hides any control referenced by the tblRoleAccessColumn table:

Dim reader As SqlDataReader = cmd.ExecuteReader()

While reader.Read() ' Loop through constraints for current test

MySecurity(0) = reader.Item(0).ToString ' ColumnName

MySecurity(1) = reader.Item(1).ToString ' AccessColumnName

For Each ctrl As Control In Me.Controls ' Loop through all controls

' If this is one of the following controls

If (TypeOf ctrl Is TextBox) Or _

(TypeOf ctrl Is Label) Or _

(TypeOf ctrl Is Button) Or _

(TypeOf ctrl Is ListBox) Or _

(TypeOf ctrl Is ListView) Then

If MySecurity(0) = ctrl.Name Then ' Referenced by current constraint

ctrl.Enabled = True ' Enable it and make it visible

ctrl.Visible = True

If MySecurity(1) = "Lock" Then ' Lock it if required

ctrl.Enabled = False

End If

If MySecurity(1) = "Hide" Then ' Hide it if required

ctrl.Visible = False

End If

If MySecurity(1) = "Full" Then ' Full access if required

ctrl.Enabled = True

ctrl.Visible = True

End If

End If

End If

Next

End While

reader.Close()

Strictly speaking, the ColumnName column in the tblRoleAccessColumn table is the form's control name, not the column name of the underlying table. Of course, an app and form name should also be part of the tblRoleAccessColumn table's definition (for greater flexibility) but this is omitted for simplicity.

A Visual Basic 2005 Express source and executable package for maintaining the model (along with a front-end application and sample database) may be found in the Resource Section below.

Example

In this example there are two Users, two Roles, and one View. The Employee table has 100 randomly generated records.

User1 has Role1 while User2 has Role1 and Role2 (all views are View1 so it will be ignored in what follows).

Role1 has filter ftr1, which lists those Employees whose names start with A or B:

CREATE VIEW dbo.ftr1

AS

SELECT EmployeeId AS SelectId

FROM dbo.Employee

WHERE (SUBSTRING(LastName, 1, 1) IN ('A', 'B'))

It also has ftr2 (Employees whose names start with B or C).

Role2 has filters ftr3 (Employees whose names start with C or D) and ftr4 (Employees whose names start with E).

When User2 and View1 are selected, clicking RoleAccessSelect yields 42 (Role, View, Select) combinations:

Zoom in
Open in new window

Fig. 4

A given Employee may appear multiple times if the selected User belongs to multiple Roles (in this case 28 Employees appear 42 times with different Roles and Views).

The SQL query generated for this choice of User, Role and View is:

SELECT DISTINCT

lkpRole.RoleName,

lkpView.ViewName,

Employee.LastName,

tblRoleAccessSelect.SelectId FROM

Employee INNER JOIN

tblRoleAccessSelect ON

Employee.EmployeeId = tblRoleAccessSelect.SelectId INNER JOIN

lkpRole ON

tblRoleAccessSelect.RoleId = lkpRole.RoleId INNER JOIN

tblUserRole ON

lkpRole.RoleId = tblUserRole.RoleId INNER JOIN

lkpView ON

tblRoleAccessSelect.ViewId = lkpView.ViewId INNER JOIN

lkpUser ON

tblUserRole.UserId = lkpUser.UserId

WHERE

(lkpUser.UserName = 'User2') AND

(1=1) AND

(lkpView.ViewName = 'View1')

ORDER BY

lkpRole.RoleName,

lkpView.ViewName,

Employee.LastName

Meanwhile, on any apps using the standard security snap-in:

Role1 hides the control tbEmail

Role2 hides the controls tbBirthDate and tbSIN and locks the control tbEmail

Any controls that don't exist are simply ignored.

When the application opens, 28 unique records may be seen by User2 and View1 when Employees is clicked:

Zoom in
Open in new window

Fig. 5

The security stored proc sqMain generates the SQL that drives this listing:

SELECT

EmployeeId,

LastName,

FirstName,

Address,

City,

Postal,

Prov,

Phone,

Email,

BirthDate,

[SIN]

FROM

Employee

WHERE EmployeeId IN

(

SELECT DISTINCT

tblRoleAccessSelect.SelectId

FROM

tblRoleAccessSelect INNER JOIN

lkpView ON

tblRoleAccessSelect.ViewId = lkpView.ViewId INNER JOIN

tblUserRole ON

tblRoleAccessSelect.RoleId = tblUserRole.RoleId

WHERE

UserId = 2 AND

ViewName = 'View1'

)

ORDER BY

LastName,

FirstName

After selecting an Employee, clicking the Test Security button will lock the Email control and hide the BirthDate and SIN controls (locking has greater priority than hiding, although this priority can be changed in the tblRoleAccessColumn table).

Summary
This article has presented a simple model for implementing row-oriented security in SQL Server. Although it puts some of the security burden on the client application (so it's more vulnerable) it has proven useful in situations where access to front-end applications is tightly controlled. Also, it's easy to deploy and surprisingly efficient (even when the key security table is large). Furthermore, legacy applications may use it with a simple addition to its front-end code.

Encrypting Connection Strings in Web.config

2010-02-02T21:36:00.000-08:00

One of the best practices in ASP.NET is to save your database connection strings in the Web.config file instead of hard-coding it in your code. This allows you to change database servers easily, without needing to modify your code. As an additional protection, it is always better to use integrated Windows security to access your database, rather than using SQL Server authentication, and thus including your SQL server credentials in the connection string. Either way, it's not such a good idea to save your connection strings as plain text in Web.config -- you should ideally encrypt the connection strings so that it leaves no chance for a potential hacker to easily get more information about your database server.

In ASP.NET 2.0, Microsoft has taken this further by allowing you to encrypt the connection strings in Web.config, all without much plumbing on your part.

Using the DataProtectionConfigurationProvider and RSAProtectedConfigurationProvider for Encryption

To see how you can encrypt the connection strings stored in the Web.config file, you will configure a GridView control to bind to an SqlDataSource control. The connection string used by the SqlDataSource control is saved in the Web.config file. You then encrypt the connection strings, using the two Protection Configuration Providers available in .NET 2.0.

1. Launch Visual Studio 2005 and create a new Website project. Name the project as C:\EncryptConfig.

2. Populate the default form with a GridView control and configure it to use an SqlDataSource control. Configure the SqlDataSource control using the Choose Data Source drop-down list in the GridView Tasks menu (see Figure 1) to connect to the Authors table in the pubs database in SQL Server 2000. In particular, ensure that the connection string to the database is stored in Web.config.

Figure 1: Configuring the GridView control.

The Source View of the GridView and SqlDataSource controls look like this:

DataSourceID="SqlDataSource1" AutoGenerateColumns="False">

SortExpression="au_id">

SortExpression="au_lname">

SortExpression="au_fname">

SortExpression="phone">

SortExpression="address">

SortExpression="city">

SortExpression="state">

SortExpression="zip">

DataField="contract">

SelectCommand="SELECT * FROM [authors]"

ConnectionString="">

3. The default form should now look very much like Figure 2.

Figure 2: The GridView and SqlDataSource control.

4. The Web.config file will now contain the connection string:

connectionString="Data Source="(local)";

Initial Catalog=pubs;Integrated Security=True"

providerName="System.Data.SqlClient" />

...

5. Switch to the code-behind of the default form and add in the EncryptConnStr() method. The EncryptConnStr() method first retrieves the Web.config file and then applies encryption to the specified section (, in this case) of the file using the Protection Configuration Provider indicated (passed in via the protectionProvider parameter).

Imports System.Configuration

Imports System.Web.Security

Public Sub EncryptConnStr(ByVal protectionProvider As String)

'---open the web.config file

Dim config As Configuration = _

ConfigurationManager.OpenWebConfiguration( _

Request.ApplicationPath)

'---indicate the section to protect

Dim section As ConfigurationSection = _

config.Sections("connectionStrings")

'---specify the protection provider

section.SectionInformation.ProtectSection(protectionProvider)

'---Apple the protection and update

config.Save()

End Sub

6. Also, add in the DecryptConnStr() method. The DecryptConnStr() method will decrypt the encrypted connection strings in web.config:

Public Sub DecryptConnStr()

Dim config As Configuration = _

ConfigurationManager.OpenWebConfiguration( _

Request.ApplicationPath)

Dim section As ConfigurationSection = _

config.Sections("connectionStrings")

section.SectionInformation.UnProtectSection()

config.Save()

End Sub

7. Note that the UnProtectSection() method, unlike ProtectSection(), does not require a provider name. When a section is encrypted, information regarding the provider that performed the encryption is stored in the Web.config file. UnProtectSection will use that information to determine which provider to use to decrypt the data.

8. There are two protection configuration providers available for your use -- DataProtectionConfigurationProvider and RSAProtectedConfigurationProvider. The DataProtectionConfigurationProvider uses the Windows DPAPI to perform encryption. The RSAProtectedConfigurationProvider uses the public-key algorithm available in the .NET Framework's RSACryptoServiceProvider class to perform encryption.

9. To test the EncryptConnStr() method, call it in the Form_Load event (you should only call the Encypt() method once) with the indicated Protection Configuration Provider:

Protected Sub Page_Load(ByVal sender As Object, _

ByVal e As System.EventArgs) _

Handles Me.Load

Encrypt("DataProtectionConfigurationProvider")

'--or--

' Encrypt("RSAProtectedConfigurationProvider")

End Sub

10. If you use the DataProtectionConfigurationProvider provider, your connection string will now look like this:

inheritedByChildren="False" />

AQAAANCMnd8.............p4nZCszebgXs=

...

11. If you use the RSAProtectedConfigurationProvider provider, your connection string will now look like this:

inheritedByChildren="False" />

xmlns="http://www.w3.org/2001/04/xmlenc#">

"http://www.w3.org/2001/04/xmlenc#tripledes-cbc" />

"http://www.w3.org/2001/04/xmlenc#rsa-1_5" />

RSA Key

J7HkJ/9i...c2usLA=

k6w9KUZ...5p2AP5gQ==

...

12. Notice that the section added to Web.config contains information needed to decrypt the connection strings. More importantly, doesn't contain the decryption key. For example, if you use the Windows DataProtectionConfigurationProvider, the decryption key is auto-generated and saved in the Windows Local Security Authority (LSA).

13. The really cool thing about encrypting the Web.config file is that the process of decrypting the required connection string is totally transparent to the developer. Controls and code that need to access the connection string will automatically know how to encrypt the encrypted information. However, if you want to decrypt the Web.config file so that you can make modifications to it, simply call the DecryptConnStr() method.

You can check if a section is protected by using the IsProtected property, like this:

If Not section.SectionInformation.IsProtected Then

section.SectionInformation.ProtectSection(protectionProvider)

config.Save()

End If

You can encrypt almost all the sections in Web.config, with the exceptions of some sections such as and . This is because these sections are accessed by parts of the unmanaged code in ASP.NET.

Programmatically Add a New Connection String

You can programmatically add a new connection string to the Web.config file, even though it is encrypted. The following AddConnString() method adds a new connection string named NorthWindConnectionString to an encrypted Web.config:

Public Sub AddConnString()

'---add a connection string to Web.config

Dim config As Configuration = _

ConfigurationManager.OpenWebConfiguration( _

Request.ApplicationPath)

config.ConnectionStrings.ConnectionStrings.Add _

(New ConnectionStringSettings("NorthwindConnectionString", _

"server=localhost;database=northwind;integrated security=true"))

config.Save()

End Sub

If you were to decrypt the Web.config file, you will find the newly added connection string:

(local)";Initial Catalog=pubs;Integrated Security=True"

providerName="System.Data.SqlClient" />

Summary

In this article, I have discussed how easy it is to encrypt connection strings in the Web.config file using the two Protection Configuration Providers -- DataProtectionConfigurationProvider and RSAProtectedConfigurationProvider -- for encryption. Since it is now so easy to perform encryption in Web.config, there is really no reason for not doing so.

SQL Server 2005 Paging – The Holy Grail

2010-02-02T21:11:00.000-08:00

Introduction

The paging and ranking functions introduced in 2005 are old news by now, but the typical ROW_NUMBER OVER() implementation only solves part of the problem.

Nearly every application that uses paging gives some indication of how many pages (or total records) are in the total result set. The challenge is to query the total number of rows, and return only the desired records with a minimum of overhead? The holy grail solution would allow you to return one page of the results and the total number of rows with no additional I/O overhead.

In this article, we're going to explore four approaches to this problem and discuss their relative strengths and weaknesses. For the purposes of comparison, we'll be using I/O as a relative benchmark.

The 'two-bites' approach

The most basic approach is the 'two-bites' approach. In this approach you, effectively, run your query twice; querying the total rows in one pass, and querying your result set in the second. The code is pretty straightforward:

DECLARE @startRow INT ; SET @startrow = 50

SELECTCOUNT(*) AS TotRows

FROM [INFORMATION_SCHEMA].columns

;WITH cols

AS

(

SELECT table_name, column_name,

ROW_NUMBER() OVER(ORDER BY table_name, column_name) AS seq

FROM [INFORMATION_SCHEMA].columns

)

SELECT table_name, column_name

FROM cols

WHERE seq BETWEEN @startRow AND @startRow + 49

ORDERBY seq

It gives the desired results, but this approach doubles the cost of the query because you query your underlying tables twice:

(1 row(s) affected)

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'syscolpars'. Scan count 1, logical reads 46, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'sysschobjs'. Scan count 1, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(50 row(s) affected)

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'syscolpars'. Scan count 1, logical reads 46, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'sysschobjs'. Scan count 1, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The temp table approach

The 'two-bites' approach is especially undesirable if your paged query is very expensive and complex. A common workaround is to write the superset into a temporary table, then query out the subset. This is also the most common way to implement paging pre-2005 (in this case, ROW_NUMBER is superfluous).

DECLARE @startRow INT ; SET @startrow = 50

CREATETABLE #pgeResults(

id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

table_name VARCHAR(255),

column_name VARCHAR(255)

)

INSERTINTO #pgeResults(Table_name, column_name)

SELECT table_name, column_name

FROM [INFORMATION_SCHEMA].columns

ORDERBY [table_name], [column_name]

SELECT@@ROWCOUNT AS TotRows

SELECT Table_Name, Column_Name

FROM #pgeResults

WHERE id between @startrow and @startrow + 49

ORDERBY id

DROPTABLE #pgeResults

Looking at the query plan, you can see that your underlying tables are queried only once but the I/O stats show us that you take an even bigger hit populating the temporary table.

Table '#pgeResults_________________________________________________________________________________________________________000000001A9F'. Scan count 0, logical reads 5599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'syscolpars'. Scan count 1, logical reads 46, physical reads 0, read-ahead reads 14, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'sysschobjs'. Scan count 1, logical reads 34, physical reads 0, read-ahead reads 39, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(2762 row(s) affected)

(1 row(s) affected)

(50 row(s) affected)

Table '#pgeResults_________________________________________________________________________________________________________000000001A9F'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

In this case, it would be better to query the tables twice. Maybe some new 2005 functionality can yield a better solution.

The COUNT(*) OVER() Approach

OVER() can also be used with Aggregate Window Functions. For our purposes this means we can do a COUNT(*) without the need for a GROUP BY clause, returning the total count in our result set. The code definitely looks much cleaner and, if your application permits it, you can simply return one dataset (eliminating the overhead of writing to a temp table).

DECLARE @startRow INT ; SET @startrow = 50

;WITH cols

AS

(

SELECT table_name, column_name,

ROW_NUMBER() OVER(ORDER BY table_name, column_name) AS seq,

COUNT(*) OVER() AS totrows

FROM [INFORMATION_SCHEMA].columns

)

SELECT table_name, column_name, totrows

FROM cols

WHERE seq BETWEEN @startRow AND @startRow + 49

ORDERBY seq

Unfortunately this approach has it's own hidden overhead:

Table 'Worktable'. Scan count 3, logical reads 5724, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'syscolpars'. Scan count 1, logical reads 46, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'sysschobjs'. Scan count 1, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Where did that come from? In this case, SQL Server implements the COUNT(*) OVER() by dumping all the data into a hidden spool table, which it then aggregates and joins back to your main output. It does this to avoid re scanning the underlying tables. Although this approach looks the cleanest, it introduces the most overhead.

I've spent most of today cleaning up and old data-paging proc that is both very inefficient and frequently called enough for me to notice it. I've explored probably a dozen other approaches to solving this problem before I came up with the solution below. For the sake of brevity—and because they rest are pretty obscure and equally inefficient—we'll now skip to the best solution.

The Holy Grail

In theory, ROW_NUMBER() gives you all the information you need because it assigns a sequential number to every single row in your result set. It all falls down, of course, when you only return a subset of your results that don't include the highest sequential number. The solution is to return a 2nd column of sequential numbers, in the reverse order. The total number of the records will always be the sum of the two fields on any given row minus 1 (unless one of your sequences is zero-bound).

DECLARE @startRow INT ; SET @startrow = 50

;WITH cols

AS

(

SELECT table_name, column_name,

ROW_NUMBER() OVER(ORDER BY table_name, column_name) AS seq,

ROW_NUMBER() OVER(ORDER BY table_name DESC, column_name desc) AS totrows

FROM [INFORMATION_SCHEMA].columns

)

SELECT table_name, column_name, totrows + seq -1 as TotRows

FROM cols

WHERE seq BETWEEN @startRow AND @startRow + 49

ORDERBY seq

This approach gives us our page of data and the total number of rows with zero additional overhead! (well, maybe one or two ms of CPU time, but that's it) The I/O statistics are identical to querying just the subset of records.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'syscolpars'. Scan count 1, logical reads 46, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'sysschobjs'. Scan count 1, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Compare the stats above with the stats and query below (just returning one page of data).

;WITH cols

AS

(

SELECT table_name, column_name,

ROW_NUMBER() OVER(ORDER BY table_name, column_name) AS seq

FROM [INFORMATION_SCHEMA].columns

)

SELECT table_name, column_name

FROM cols

WHERE seq BETWEEN @startRow AND @startRow + 49

ORDERBY seq

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'syscolpars'. Scan count 1, logical reads 46, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'sysschobjs'. Scan count 1, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Conclusion

I have found this approach to be best suited for smaller resultsets from complex queries where I/O is the primary bottleneck.

Introduction to Common Table Expressions

2010-01-13T21:19:00.000-08:00

You can use common table expressions (CTEs) starting with SQL Server 2005 to simplify your T-SQL queries. CTEs allow you to separate part of your T-SQL logic from your main query instead of using a view, correlated subquery, or temp table. You define the CTE at the beginning of the statement, and then you can use the CTE in your main query just as if it were a table or view.

CTE Syntax

Once you get acquainted with CTEs, you will probably find them easy to use. Start with the word WITH, followed by a name for your CTE. Follow the name of the CTE with the word AS and a set of parentheses. Inside the parentheses, type in a valid SELECT query. You can then use the CTE in your main query just as if the CTE was a table or view. You can use the CTE in a SELECT, UPDATE, INSERT or DELETE statement, but the CTE itself will be a SELECT statement. Listing 1 shows two sample queries.
Listing 1: Simple CTE examples

WITH emp AS (
    SELECT EmployeeID, FirstName, LastName, E.Title, ManagerID
    FROM HumanResources.Employee AS E 
    INNER JOIN Person.Contact AS C ON E.ContactID = C.ContactID 
    )
SELECT A.EmployeeID, A.FirstName, A.LastName, A.Title,
    A.ManagerID, B.FirstName AS MgrFirstName, 
    B.LastName AS MgrLastName, B.Title AS MgrTitle
FROM emp AS A 
INNER JOIN emp AS B ON A.ManagerID = B.EmployeeID;

WITH emp AS (
    SELECT EmployeeID, FirstName, LastName, E.Title, ManagerID
    FROM HumanResources.Employee AS E 
    INNER JOIN Person.Contact AS C ON E.ContactID = C.ContactID 
    ),
mgr (EmployeeID, MgrFirstName, MgrLastName, MgrTitle) AS (
    SELECT EmployeeID, FirstName, LastName, E.Title
    FROM HumanResources.Employee AS E 
    INNER JOIN Person.Contact AS C ON E.ContactID = C.ContactID 
)
SELECT emp.EmployeeID, FirstName, LastName, Title, ManagerID,
    MgrFirstName, MgrLastName, MgrTitle
FROM emp 
INNER JOIN mgr ON emp.ManagerID = mgr.EmployeeID;

The first query demonstrates how the CTE can be used multiple times within the main query. The main query joins emp to itself to list all the employees who have a manager along with the manager’s name and title. The second query demonstrates that you can define more than one CTE in the same statement. Separate the CTE definitions with a comma. The second CTE, defining the managers, also shows how you can specify aliases for the CTE right after the CTE name instead of relying on the names in the CTE definition. One more thing to keep in mind is that, if the CTE is part of a batch, the previous statement must end in a semi-colon. I have often seen CTE queries with a semi-colon right before the word WITH as a precautionary measure.

Uses for CTEs

Now that you know how to create a CTE, you may wonder when you should use one. One of my favorite uses is to fix a poorly performing correlated subquery. Developers often use correlated subqueries to add one or more aggregate expressions to an otherwise non-aggregate query. Listing 2 shows a correlated subquery and how to get the same results using a better performing CTE.
Listing 2: Use a CTE to fix a correlated subquery

SELECT SalesOrderID, CustomerID, 
    (SELECT COUNT(*) FROM Sales.SalesOrderHeader 
     WHERE CustomerID = S.CustomerID) AS CountOfSales,
    (SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader 
     WHERE CustomerID = S.CustomerID) AS AvgSale,
    (SELECT MIN(TotalDue) FROM Sales.SalesOrderHeader 
     WHERE CustomerID = S.CustomerID) AS LowestSale,
    (SELECT MAX(TotalDue) FROM Sales.SalesOrderHeader 
     WHERE CustomerID = S.CustomerID) AS HighestSale
FROM Sales.SalesOrderHeader AS S;

WITH csales AS (
    SELECT COUNT(*) AS CountOfSales, AVG(TotalDue) AS AvgSale, 
        MIN(TotalDue) AS LowestSale, MAX(TotalDue) AS HighestSale,
        CustomerID 
     FROM Sales.SalesOrderHeader
     GROUP BY CustomerID)
SELECT SalesOrderID, S.CustomerID, CountOfSales, AvgSale, 
     LowestSale, HighestSale 
FROM Sales.SalesOrderHeader AS S 
INNER JOIN csales ON S.CustomerID = csales.CustomerID;

The first query lists all of the sales orders and the customer ID for each. It uses four subqueries to calculate the count of sales, average sale, minimum and maximum sales for the customers. The second query produces the same results by including an aggregate query in the CTE. On my computer, the first computer takes three times longer to run than the second query!

Another fantastic T-SQL feature you will find in SQL Server 2005 and 2008 is the ROW_NUMBER and other ranking functions. You can add row numbers to your queries using ROW_NUMBER, but you can not filter the results by including the ROW_NUMBER function in the WHERE clause. Listing 3 shows how you can combine the ROW_NUMBER function with a CTE to solve this problem.
Listing 3: Use the ROW_NUMBER function with a CTE

WITH emp AS (
    SELECT ROW_NUMBER() OVER(ORDER BY LastName, FirstName) AS RowNumber,
         EmployeeID, FirstName, LastName 
    FROM HumanResources.Employee AS e 
    INNER JOIN Person.Contact AS c ON e.ContactID = c.ContactID)
SELECT RowNumber, EmployeeID, FirstName, LastName
FROM emp
WHERE RowNumber BETWEEN 11 AND 20
ORDER BY RowNumber;

Another interesting way you can use a CTE is to join a CTE to a previous CTE. Take a look at Listing 4 to see how this works.

Listing 4: One CTE based on another CTE

WITH maxDate AS (
    SELECT MAX(OrderDate) AS MaxOrderDate, CustomerID
    FROM Sales.SalesOrderHeader
    GROUP BY CustomerID),
orders AS (
    SELECT SalesOrderID, soh.CustomerID, OrderDate 
    FROM Sales.SalesOrderHeader AS soh 
    INNER JOIN maxDate ON soh.CustomerID = maxDate.CustomerID 
         AND soh.OrderDate = maxDate.MaxOrderDate)
SELECT CustomerID,ProductID, sod.SalesOrderID,OrderDate 
FROM orders 
INNER JOIN Sales.SalesOrderDetail AS sod ON sod.SalesOrderID = orders.salesOrderID;

The query in Listing 4 returns a list of the customers along with the products ordered on the most recent order. The first CTE, maxDate, returns the maximum order date for each customer. The second CTE joins the first CTE, maxDate, to the Sales.SalesOrderHeader table, returning a list of customers and the most recent order ID. Finally, the main query joins the Sales.SalesOrderDetail table to the orders CTE.

Conclusion

You can use CTEs to separate part of the logic of your query instead of using correlated subqueries and temp tables. Once you start using CTEs in your T-SQL code, you will probably find lots of ways to use them.

SQL Server 2008 T-SQL Enhancements Part - I

2009-12-29T00:31:00.000-08:00

Introduction

SQL Server 2008 has come up with different compelling new features. One of them is T-SQL Enhancements which increase the productivity of developers by reducing overall development time. In this first article I will write about few of the T-SQL enhancements and in the second article I will write more about the new data types introduced in SQL Server 2008.

Intellisense Enhancements

We can keep our context, find the information we need, insert T-SQL language elements directly into our code, and even have IntelliSense complete our typing for us. This can speed up software development by reducing the amount of keyboard input required and minimize references to external documentation. Enhancements include expanded T-SQL language coverage and a new colorization system.
This feature works like the automatic syntax-checking in Visual Studio. As we type, it automatically fills out the syntax for Transact-SQL, and for database objects - even variables we’ve declared earlier (see the images below). The feature is configurable; we can turn it off or on.

Zoom in | Open in new window

Syntax Enhancements

There are actually three syntax enhancements as follows:
· Initialization of variables at the same time of its declaration: Now we can initialize variables inline as part of the variable declaration statement instead of using separate DECLARE and SET statements. It works with most of the data types, including SQLCLR data types, but not TEXT, NTEXT, or IMAGE data types.
For Example:


	Zoom in \| Open in new window

·        Compound Operators: Compound operators are same as what we have in C++ and C#; they execute some operation and set an original value to the result of the operation. They help in writing cleaner and abbreviate code. (It also works in the SET clause of an UPDATE statement). These are list of supported compound operators in SQL Server 2008.
o   += Add and assign
o   -= Subtract and assign
o   *= Multiply and assign
o   /= Divide and assign
o   %= Modulo and assign
o   &= Bitwise AND and assign
o   ^= Bitwise XOR and assign
o   |= Bitwise OR and assign
For Example:

Zoom in | Open in new window

· Row Constructors (a.k.a. Table Value Constructors): Transact-SQL is enhanced to allow multiple value inserts within a single INSERT statement; it means multiple row predicates in the VALUES clause.
For Example:


	Zoom in \| Open in new window

Object Dependencies Enhancements

The object dependencies improvement provides reliable discovery of dependencies between objects through newly introduced catalog view and dynamic management functions. Dependency information is always up-to-date for both schema-bound (where the object A cannot be deleted because object B depends on it) and non-schema-bound (where Object A can be deleted or may not even have been created, however Object B still depends on it) objects. Dependencies are tracked for stored procedures, tables, views, functions, triggers, user-defined types, XML schema-collections, and more. SQL Server 2008 introduces three new objects that provide object dependency information:
·        sys.sql_expression_dependenciescatalog view : It provides object dependencies by name, it holds one record for each dependency on a user-defined object in the current database.
·        sys.dm_sql_referenced_entitiesDMF : It provides all entities that the input entity depends on, returns one row for each user defined object referenced by name in the definition of the specified referencing entity.

·        sys.dm_sql_referencing_entitiesDMF: It provides all entities that depend on the input entity, it returns one record for each user defined object in the current database that references another user defined object by name.
There are two ways we can see the object dependencies by using SSMS(Right click on the object and then click on View Dependencies) or by writing queries against above mentioned view and DMFs as given below:


	Zoom in \| Open in new window

Using the FORCESEEK Table Hint

The FORCESEEK table hint may be useful when the query plan uses a table or index scan operator on a table or view, but an index seek operator may be more efficient (for example, in case of high selectivity). The FORCESEEK table hint forces the query optimizer to use only an index seek operation as the access path to the data in the table or view referenced in the query. We can use this table hint to override the default plan chosen by the query optimizer to avoid performance issues caused by an inefficient query plan. For example, if a plan contains table or index scan operators, and the corresponding tables cause a high number of reads during the execution of the query, forcing an index seek operation may yield better query performance. This is especially true when inaccurate cardinality or cost estimations cause the optimizer to favor scan operations at plan compilation time.

Zoom in | Open in new window

One of the scenarios where this hint can be useful is working around with Parameter Sniffing (It is a technique by which the SQL Server query optimizer to sniff the parameter value from the query during first invocation and generates an optimized execution plan based on that value). Let see this in action by running below queries against AdventureWorks database and analyzing different scenarios.
Scenario 1: The first query returns 450 rows and has low selectivity compared to the second query which return just 16 records, hence first query uses Index Scan rather than the Index Seek and Lookup of second query.

Zoom in | Open in new window

Scenario 2: In this scenario, I am running the same queries as above but this time I am using variables to pass values to queries instead of hard-coding the value. If we look at the execution plan generated, we see both the queries are using the same Index Scan even though the parameter values are different, and as we saw in scenario 1 the second query has high selectivity and should have used Index Seek and Lookup. This is happening because on execution of first query the SQL Query optimizer does not know the parameter value until runtime as I have used variables and makes a hard coded guess on selectivity and creates the execution plan on the basis of it and cache it, the second query uses the same cached execution plan. (Look at the query cost of both the queries and compare it with the scenario 1).

Zoom in | Open in new window

Scenario 3: So what to do if the queries use parameter and most of the time these variable values result in high selectivity, how can be force Query Optimizer to do Index Seek instead of Index Scan. We have two options here; either to use FORCESEEK hint or use RECOMPILE option as given.

Zoom in | Open in new window

FORCESEEK applies to both clustered and non-clustered index seek operations. It can be specified for any table or view in the FROM clause of a SELECT statement and in the FROM clause of an UPDATE or DELETE statement.
Note: Because the SQL Server query optimizer typically selects the best execution plan for a query, Microsoft recommends using hints only as a last resort by experienced developers and database administrators. In other words, use hints as a last resort for performance tuning (the optimizer does a good job most of the times).

GROUPING SETS

GROUPING SETS allow us to write one query that produces multiple groupings and returns a single result set. The result set is equivalent to a UNION ALL of differently grouped rows. By using GROUPING SETS, we can focus on the different levels of information (groupings) our business needs, rather than the mechanics of how to combine several query results. GROUPING SETS enables us to write reports with multiple groupings easily, with improved query performance. As the number of possible groupings increases, the simplicity and performance benefits provided by GROUPING SETS become even greater.

Zoom in | Open in new window

In other words, a GROUP BY clause that uses GROUPING SETS can generate a result set equivalent to that generated by a UNION ALL of multiple simple GROUP BY clauses because the GROUP BY clauses are invalid in combination.

Auto generated SQL Server keys - uniqueidentifier or IDENTITY

2009-12-22T01:04:00.000-08:00

there are a number of ways you can auto-generate key values for your tables. The most common ways are via the use of the IDENTITY column property or by specifying a uniqueidentifier (GUID) data type along with defaulting with either the NEWID() or NEWSEQUENTIALID() function. Futhermore, GUIDs are heavily used in SQL Server Replication to uniquely identify rows in Merge Replication or Transactional Replication with updating subscriptions. The most common, well known way to auto-generate a key value is via the use of the IDENTITY column property on a column that's typically declared as an integer. Once defined, the engine will automatically generate a sequential number based on the way the property has been declared on the column. The IDENTITY property takes an initial seed value as its first parameter and an increment value as its second parameter.
Consider the following example which creates and inserts into identity based tables that define the primary key as a clustered index:

SET NOCOUNT ON
GO
USE MASTER
GO
CREATE DATABASE MSSQLTIPS
GO

USE MSSQLTIPS
GO
-- Start at 1 and increment by 1
CREATE TABLE IDENTITY_TEST1
(
ID INT IDENTITY(1,1) PRIMARY KEY,
TESTCOLUMN CHAR(2000) DEFAULT REPLICATE('X',2000)
)
GO

-- Start at 10 and increment by 10
CREATE TABLE IDENTITY_TEST2
(
ID INT IDENTITY(10,10) PRIMARY KEY,
TESTCOLUMN CHAR(2000) DEFAULT REPLICATE('X',2000)
)
GO

-- Start at 1000 and increment by 5
CREATE TABLE IDENTITY_TEST3
(
ID INT IDENTITY(1000,5) PRIMARY KEY,
TESTCOLUMN CHAR(2000) DEFAULT REPLICATE('X',2000)
)
GO

-- INSERT 1000 ROWS INTO EACH TEST TABLE
DECLARE @COUNTER INT
SET @COUNTER = 1

WHILE (@COUNTER <= 1000)
BEGIN
   INSERT INTO IDENTITY_TEST1 DEFAULT VALUES
   INSERT INTO IDENTITY_TEST2 DEFAULT VALUES
   INSERT INTO IDENTITY_TEST3 DEFAULT VALUES
   SET @COUNTER = @COUNTER + 1
END
GO

SELECT TOP 3 ID FROM IDENTITY_TEST1
SELECT TOP 3 ID FROM IDENTITY_TEST2
SELECT TOP 3 ID FROM IDENTITY_TEST3
GO

Another way to auto-generate key values is to specify your column as a type of uniqueidentifier and DEFAULT using NEWID() or NEWSEQUENTIALID(). Unlike IDENTITY, a DEFAULT constraint must be used to assign a GUID value to the column.
How do NEWID() and NEWSEQUENTIALID() differ? NEWID() randomly generates a guaranteed unique value based on the identification number of the server's network card plus a unique number from the CPU clock. In contrast, NEWSEQUENTIALID() generates these values in sequential order as opposed to randomly.
Let's create new tables that use a uniqueidentifier along with both NEWID() and NEWSEQUENTIALID()

USE MSSQLTIPS
GO

CREATE TABLE NEWID_TEST
(
ID UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
TESTCOLUMN CHAR(2000) DEFAULT REPLICATE('X',2000)
)
GO
CREATE TABLE NEWSEQUENTIALID_TEST
(
ID UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
TESTCOLUMN CHAR(2000) DEFAULT REPLICATE('X',2000)
)
GO

-- INSERT 1000 ROWS INTO EACH TEST TABLE
DECLARE @COUNTER INT
SET @COUNTER = 1

WHILE (@COUNTER <= 1000)
BEGIN
   INSERT INTO NEWID_TEST DEFAULT VALUES
   INSERT INTO NEWSEQUENTIALID_TEST DEFAULT VALUES
   SET @COUNTER = @COUNTER + 1
END
GO

SELECT TOP 3 ID FROM NEWID_TEST
SELECT TOP 3 ID FROM NEWSEQUENTIALID_TEST
GO

As you can see, the first table which uses NEWID() generates random values while the second table that uses NEWSEQUENTIALID() generates sequential values. As opposed to the integers generated by the IDENTITY approach, the GUID values generated are not as friendly to look at or work with. There is one other item to note. SQL Server keeps the last generated identity value in memory which can be retrieved right after an INSERT using SCOPE_IDENTITY(), @@IDENTITY, or CHECK_IDENT (depending on the scope you require). There is nothing similar to capture the last generated GUID value. If you use a GUID, you'll have to create your own mechanism to capture the last inserted value (i.e. retrieve the GUID prior to insertion or use the SQL Server 2005 OUTPUT clause).
Now that we understand how to auto generate key values and what they look like, let's examine the storage impacts of each approach. As part of the previously created table definitions, I added a column of CHAR(2000) to mimic the storage of additional column data. Let's examine the physical storage of the data:

USE MSSQLTIPS
GO
SELECT OBJECT_NAME([OBJECT_ID]) as tablename, avg_fragmentation_in_percent, fragment_count, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), null, null, null, null)
ORDER BY tablename
GO

Looking at this output, you can see that the NEWID() test table is very fragmented as evidenced by its fragmentation percentage of 98%. Furthermore, you can see that the rows were dispersed among 490 pages. This is due to the page splitting that occurred due to the random nature of the key generation. In contrast, the IDENTITY and NEWSEQUENTIALID() test tables show minimal fragmentation since their auto generated keys occur in sequential order. As a result, they don't suffer from the page splitting condition that plagues the NEWID() approach. Though you can defragment the NEWID() table, the random nature of the key generation will still cause page splitting and fragmentation with all future table INSERTs. However, page splitting can be minimized by specifying an appropriate FILL FACTOR.
Looking at the NEWSEQUENTIALID() test table, we see it generated fewer pages than the NEWID() approach but it still generated more pages than the IDENTITY approach. Why is this? It's because the uniqueidentifier data type consumes 16 bytes of disk space as opposed to the 4 bytes used by the integer data type that was used for the IDENTITY. Considering that SQL Server pages are generally capped at 8K or roughly 8060 bytes (as of SQL Server 2005, there is a row-overflow mechanism that can kick in but that's for another discussion), this leads to more pages generated for the NEWSEQUENTIALID() approach as opposed to the IDENTITY approach.
Examining the database table space used, we see that the tables using the IDENTITY approach used the least amount disk space.

exec sp_spaceused IDENTITY_TEST1
GO
exec sp_spaceused IDENTITY_TEST2
GO
exec sp_spaceused IDENTITY_TEST3
GO
exec sp_spaceused NEWID_TEST
GO
exec sp_spaceused NEWSEQUENTIALID_TEST
GO

Now also consider this, since a uniqueidentifier data type consumes 16 bytes of data, the size of any defined non-clustered indexes on a table using a GUID as a clustered index are also affected because the leaf level of these non-clustered indexes contains the clustered index key as a pointer. As a result, the size of any non-clustered indexes would end up being larger than if an IDENTITY were defined as integer or bigint.
It's evident that using IDENTITY to auto-generate key values offers a few advantages over the GUID approaches:

IDENTITY generated values are configurable, easy to read, and easier to work with
Fewer database pages are required to satisfy query requests
In comparison to NEWID(), page splitting (and its associated overhead) is eliminated
Database size is minimized
System functions exist to capture the last generated IDENTITY value (i.e. SCOPE_IDENTITY(), etc)
Some system functions - such as MIN() and MAX(), for instance - cannot be used on uniqueidentifier columns

Transactions Part 2

2009-12-17T01:06:00.000-08:00

n order to qualify as a transaction an operation must meet several qualifications. These qualifications are known as the ACID properties of transactions. ACID is an acronym that stands for:

Atomic - This refers to the primary idea of the first two articles, that each part of a transaction must succeed or they all must fail together.
Consistent - When a transaction is complete the database must be left in a consistent state. All rules (constraints) must be applied. This also applies to the physical data structures such as indexes and doubly linked lists which must be left in a consistent state. The physical data structures are entirely SQL Server's problem and will not be dealt with here.
Isolated - The data modifications made by a transaction must be independent of any other transactions. This is the focus of this article so we will get into much more detail.
Durable - Once committed the changes made by a transaction must be "permanent." I use quotes because, of course, few changes in a database are ever really permanent, since the data is subject to being changed by subsequent transactions. However, the changes must be recoverable in the event of system failure. Again, this is almost entirely the job of SQL Server, the ability that a DBA has to affect this property would more appropriately be covered in a discussion of backup and recovery strategies.

Locking
Locks are the mechanism used by SQL Server to control concurrency and isolation. Concurrency and isolation are pretty much the inverse of each other. The intelligent use of Isolation Levels (and by extension Lock Hints) allows the knowledgeable DBA to adjust queries so that they meet the transactions' need for isolation, and hopefully support an acceptable level of concurrency.
By default, whenever data is read or modified, SQL Server places locks on the data. These locks prevent two transactions from attempting to modify the same data at the same time. Once a transaction is done reading or altering the data, the locks are released and subsequent transactions can obtain locks on those resources. Locks are granted on a "First come, first served" basis. There are three kinds of locks that we are concerned with:

Shared locks are normally obtained by any SELECT query. As the name implies, other connections can still read the data (obtain their own shared lock), but will not be able to modify it until the shared locks are released.
Update locks are a kind of heavyweight shared lock; only one connection can obtain an Update lock on a given resource.Update locks allow a connection to have the option to either upgrade the lock to Exclusive and modify the data, or downgrade to a shared lock if it won't modify the data. Having an Update Lock will not prevent another transaction from obtaining Shared locks.
Finally, Exclusive locks must be obtained if data is to be modified. Once an Exclusive lock is granted on a resource other connections must wait until the Exclusive lock is released to obtain ANY kind of lock on that resource.

When I mention "resource" normally you can think of a single row of data since in most cases SQL Server will lock only a single row. However, if your transaction will update a number of rows, SQL Server might obtain Page locks, or even a Table lock. This concept is known as Lock Granularity and it has no effect on the types of locks obtained by a query. Setting the Transaction Isolation Level has no effect on the granularity of Locks obtained. However, Lock Granularity can have a huge impact on performance and concurrency; but with one exception--Range Locks, which will be discussed near the end of this article--a detailed discussion of this topic is beyond the scope of this series of articles.I have greatly simplified a very complex mechanism, but that should be enough detail to facilitate an understanding of the main topic.
Transaction Isolation Levels
There are four Transaction Isolation Levels supported by SQL Server. The table below comes from Books Online and is a handy reference on how the levels differ from each other.The key to understanding Transaction Isolation Levels is to realize that all they do is control how long Shared locks are held within a transaction.

Isolation Level Dirty	Read	Nonrepeatable Read	Phantom
Read Uncommitted	Yes	Yes	Yes
Read Committed	No	Yes	Yes
Repeatable Read	No	No	Yes
Serializable	No	No	No

You might be asking "OK, but what is a Phantom?" I'm glad you asked! Let's look at each of these conditions and what they mean to our transactions.
A Dirty Read is not a magazine that you wouldn't show to your mother! Dirty Reads are when a transaction is allowed to read uncommitted data. Notice that Dirty Reads are only allowed (appropriately enough) under the Read uncommitted Isolation Level. For our examples we'll revisit the simple table from Part 1. Here is the DDL for the table and a simple script to populate it with a bit of data:

CREATE TABLE TranTest (
 Col1 int IDENTITY,
 Col2 int)
DECLARE @count int
SET @count = 0
WHILE @count <15
Begin
INSERT TranTest (Col2)
VALUES (0)
SET @count = @count + 1
END

**Note: In the following examples I have intentionally left out any error handling for the sake of clarity and brevity.**

Connection 1	Connection 2	Comments
BEGIN TRAN INSERT INTO TranTest (Col2) VALUES (2)		INSERT a new row in the table but don't COMMIT
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED GO SELECT * FROM TranTest WHERE Col2 = 2	Set the Isolation Level Returns 1 row showing that connection 2 can read the new row
ROLLBACK
	SELECT * FROM TranTest WHERE Col2 = 2	Returns 0 rows

In this example Connection 2 was actually able to read a row that never logically existed. This is possible because under the Read Uncommitted Level, the SELECT statement didn't even attempt to obtain a shared lock on the rows it was reading and it ignored the Exclusive locks that Connection 1 had. Now let's take a look at the default behavior using the Read Committed Isolation Level:

Connection 1	Connection 2	Comments
BEGIN TRAN INSERT INTO TranTest (Col2) VALUES (2)		INSERT a new row in the table but don't COMMIT
	SET TRANSACTION ISOLATION LEVEL READ COMMITTED GO SELECT * FROM TranTest WHERE Col2 = 2	The query is blocked because of the exclusive lock held by Connection 1
ROLLBACK		The Connection 2 query returns 0 rows

Since Connection 2 was not able to obtain a shared lock on the row, it waited until Connection 1 released its Exclusive lock. So Dirty Reads are allowed only in the Read Uncommitted Isolation Level but you don't necessarily have to use the SET TRANSACTION ISOLATION LEVEL command to get there. You can get the same result by using the NOLOCK hint in your queries.
SELECT *
FROM TranTest with(NOLOCK)
WHERE Col2 = 2
The main difference between these two methods is that this last example effectively places the connection into Read Uncommitted only for the duration of that query; subsequent queries would revert back to Read Committed. I'll discuss Lock Hints more a bit later but I wanted to introduce the concept and give a brief example here.
A Non-Repeatable Read occurs when a transaction reads data multiple times and gets different results. This can happen because under the Read Committed Isolation Level, shared locks are held only as long as it takes to read the data. As soon as a row is read the lock is released, assuming row level locking is being used. This behavior is great for concurrency, but can cause some "interesting" results. Look at the following example:

Connection 1	Connection 2	Comments
SET TRANSACTION ISOLATION LEVEL READ COMMITTED GO BEGIN TRAN SELECT * FROM TranTest WHERE Col2 = 2		Begin a transaction and run the first query Returns 0 rows
	UPDATE TranTest SET Col2 = 2	This assumes Autocommit Mode
SELECT * FROM TranTest WHERE Col2 = 2 COMMIT		Run the same query again and end the transaction Returns 15 rows

The default behavior of SQL Server means that the data can change on you right in the middle of a transaction. If we bump up the Isolation Level to Repeatable Read, this isn't allowed:

Connection 1	Connection 2	Comments
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ GO BEGIN TRAN SELECT * FROM TranTest WHERE Col2 = 2		Begin a transaction and run the first query Returns 15 rows
	UPDATE TranTest SET Col2 = 1	Set Col2 back to 1, but the transaction is blocked by Connection 1
SELECT * FROM TranTest WHERE Col2 = 2 COMMIT		Run the same query again and end the transaction Returns 15 rows
		Connection 2 completes, 15 rows affected

Repeatable Read offers a bit more isolation (stability) to your transactions, but it comes at the price of reduced concurrency. Connection 2 was only able to proceed with its UPDATE after Connection 1 released its Shared Locks on the data. Note that increasing the Isolation Level did not change the types of locks that were acquired by either transaction, it forced Connection 1 to hold on to the Shared Locks until the entire transaction completed rather than releasing them as soon as the data was read. Thus Connection 2 could not upgrade its Update Locks to Exclusive Locks and entered a blocked state until Connection 1 committed.
Now let's look at Phantoms:

Connection 1	Connection 2	Comments
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ GO BEGIN TRAN SELECT * FROM TranTest WHERE Col2 = 2		Begin a transaction and run the first query Returns 15 rows
	INSERT INTO TranTest (Col2) VALUES (2)	Add a new row
SELECT * FROM TranTest WHERE Col2 = 2 COMMIT		Run the same query again and end the transaction Returns 16 rows

The extra row in the second result set is our Phantom. So we see that Repeatable Read doesn't always guarantee a repeatable read. It only ensures that rows in your result set won't be changed by another transaction until you are done with them, it doesn't prevent another transaction from adding rows.

Connection 1	Connection 2	Comments
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE GO BEGIN TRAN SELECT * FROM TranTest WHERE Col2 = 2		Begin a transaction and run the first query Returns 16 rows
	INSERT INTO TranTest (Col2) VALUES (2)	Add a new row, but the transaction is blocked by Connection 1
SELECT * FROM TranTest WHERE Col2 = 2 COMMIT		Run the same query again and end the transaction Returns 16 rows
		Connection 2 completes

You might think that SQL Server has to lock the entire table to prevent Phantoms. The good news is that it doesn't--as long as you have an index and your query is selective enough to not force a table scan or full index scan. The way SQL Server handles this is by what is known as a Range Lock.SQL Server grants only those locks necessary to satisfy the query. To demonstrate this point lets add a clustered index to our table.
CREATE CLUSTERED INDEX IE01 ON TranTest(Col2)
Now do the following:

Connection 1	Connection 2	Comments
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE GO BEGIN TRAN SELECT * FROM TranTest WHERE Col2 BETWEEN 1 AND 10		Begin a transaction and run the first query Returns 10 rows
	INSERT INTO TranTest (Col2) VALUES (17)	Add a new row outside the range of values in Connection 1's query.
SELECT * FROM TranTest WHERE Col1 BETWEEN 1 AND 10 COMMIT		Run the same query again and end the transaction Returns 10 rows

Notice that the INSERT did not wait for Connection 1 to complete because the row it was inserting fell outside the range of locks that Connection 1 held. If you run through the same steps again but switch the INSERT to include a value somewhere between 1 and 10 the INSERT will be blocked. This should demonstrate that setting the Isolation Level to Serializable may not have as large a negative impact on concurrency as you might expect. The major factors that determine how big the impact will be are: 1) the types of transactions you are running i.e. INSERTs vs. UPDATEs and DELETEs. And 2) how efficient the indexing scheme and transactions are.
Lock Hints
Earlier I showed an example of using a Lock Hint rather than the SET TRANSACTION ISOLATION LEVEL command. The chart below shows the various Isolation Levels and their equivalent Lock Hints:

Isolation level	Lock Hints
Read Uncommitted	READUNCOMMITTED
	NOLOCK
Read Committed	READCOMMITTED
Repeatable Read	REPEATABLEREAD
Serializable	SERIALIZABLE
	HOLDLOCK
*Special	READPAST

*The READPAST Lock Hint is kind of midway between Read Committed and Read Uncommitted. READPAST only works on a query running in the Read Committed Isolation Level and it only works on SELECT queries.READPAST tells SQL Server to ignore any records that have locks on them. Unlike NOLOCK, READPAST doesn't ignore the locks, it ignores locked rows so rather than generating potential Dirty Reads, it can generate an incomplete result set. I.e. some records that meet the search criteria may not be returned because they were locked by another process and skipped. There are other Lock Hints that specify lock granularity, but I won't cover them here.
I know conventional wisdom says that you should avoid using Lock Hints, but I suspect that's primarily because most folks don't know what they are doing and are likely to cause more harm than good. I don't use Lock Hints extensively, and I almost never use Lock Hints to specify granularity. I have found that unless I'm trying to diagnose or troubleshoot something, SQL Server does a better job of managing Lock Granularity than I ever could. The Lock Hints I have covered here have proven to be very useful when SQL Server's default transaction behavior isn't what I need. In fact, I tend to use Lock Hints more than setting the Isolation Levels because Lock Hints only affect the query in which they are specified so you don't have to bother setting the Isolation Level back to what it was and worry about the effect it might have on other queries in the same connection.
I hope that this exploration of Locks and Transaction Isolation Levels has helped clarify what often seems to be a mystery. Understanding these concepts will significantly reduce the "@#$!" factor in your application design and troubleshooting efforts.

Transactions - Part 1

2009-12-16T01:46:00.000-08:00

Everyone knows that a transaction is defined as a "Unit of work." The transaction completes only if all the individual steps are successful, otherwise it all rolls back as if nothing ever happened, right? That simple definition belies some devilishly complex behavior on the part of SQL Server. Odds are that if you are a DBA or developer SQL Server's transaction handling behavior has left you scratching your head and muttering "What in the world...?" at some point in your career.
It has become evident that many of us don't really understand how to put the power of transactions to work. For instance, just placing several SQL commands between a BEGIN TRAN and a COMMIT or ROLLBACK does not ensure that they will act as a single transaction. To get consistent, reliable results you need to understand Transaction Modes, Transaction Isolation Levels, locking, nesting, error handling, Savepoints, XACT_ABORT, bound connections, and just for good measure you need to understand how ANSI_DEFAULTS and your choice of provider affect transaction behavior.
Transaction Modes SQL Server supports three Transaction Modes: Autocommit, Explicit, and Implicit.

Autocommit Mode is the default behavior for SQL Server. In this mode each SQL statement that is issued acts as a single transaction and commits on completion so there is no need--or even an opportunity--to issue a COMMIT or ROLLBACK. Autocommit is the default behavior for ADO, OLE DB, ODBC, or DB-Library connections.
Explicit Mode is entered whenever you issue a BEGIN TRAN command and ends when you COMMIT the top level transaction (see below for a discussion of Nested Transactions) or issue a ROLLBACK. There is no configuration setting that turns Explicit Mode on and off. Once your explicit transaction is committed or rolled back SQL Server immediately returns to the transaction mode it was in before.
Implicit Mode is a bit trickier for most of us to grasp. By default IMPLICIT_TRANSACTIONS is off (in other words Autocommit Mode). If you issue the SET IMPLICIT_TRANSACTIONS ON command a transaction is started for each SQL statement listed in the table below, unless there is already an open transaction, but is not committed on completion. Subsequent commands join the transaction until you issue a COMMIT or ROLLBACK. SET ANSI_DEFAULTS ON puts SQL Server into Implicit Mode.
The following SQL statements start transactions.

ALTER TABLE INSERT

CREATE OPEN*

DELETE REVOKE

DROP SELECT*

FETCH* TRUNCATE TABLE

GRANT UPDATE

*Notice that SELECT, OPEN, and FETCH start transactions but COMMIT and ROLLBACK make only limited sense for them because they don't modify data. Nevertheless the transaction is held open until explicitly ended, depending on your Transaction Isolation Level, or Lock Hints used in the query this can have a major impact on concurrency if you're not careful.
There is a widely circulated myth that CREATE, ALTER, DROP, and TRUNCATE statements can't be rolled back, they can be if you are in Implicit or Explicit Mode. In Autocommit Mode nothing can be rolled back since any SQL statement automatically commits upon completion.
A demonstration seems be in order: (issue each statement in turn from Query Analyzer, if you execute them all as a batch it still works, but won't be as clear.)
```
SET IMPLICIT_TRANSACTIONS ON

CREATE TABLE TranTest (
 Col1 int IDENTITY,
 Col2 int)

SELECT @@TRANCOUNT 
--Returns 1, indicating that there is one open transaction
--Now let's throw some data into our table with a simple loop
DECLARE @count int
SET @count = 0
WHILE @count <15
 Begin
 INSERT TranTest (Col2)
 VALUES (0)
 SET @count = @count + 1
 END
SELECT @@TRANCOUNT 
/*
This still returns 1 indicating that all our statements so far are part of the same 
transaction. Of course it could also mean that each previous statement was its own 
transaction and was committed when the next statement was executed and only the last 
statement's transaction is now left open, but I will show you that this is not the case.
*/
SELECT * FROM TranTest --We see our data.
COMMIT 
--Makes our table and data changes permanent. 
-- Notice there is no corresponding BEGIN TRAN since we are in Implicit Mode.
SELECT @@TRANCOUNT 
--This should now return 0 to show that there are no open transactions
--Now let's alter the table and add a few more rows to show that the 
-- statements are all lumped into a single transaction
ALTER TABLE TRANTEST ADD Col3 int
DECLARE @count int
SET @count = 0
WHILE @count <15
 Begin
 INSERT TranTest (Col2)
 VALUES (0)
 SET @count = @count + 1
 END
SELECT * FROM TranTest 
--Now we see Col3 and the additional data
SELECT @@TRANCOUNT 
--Returns 1
ROLLBACK
SELECT * FROM TranTest 
/*
Now you see it...Now you don't. Both the ALTER and INSERTs are rolled back 
showing that all the statements are grouped into a single transaction until a
COMMIT or ROLLBACK is issued.. 
*/
```
Nested Transactions
Transactions can be nested, but some aspects of nested transactions may be a bit surprising for the uninitiated; it does make sense though, so let's take a look. First, unlike stored procedures which are limited to 32 levels, there is apparently no practical limit to the number of nesting levels for transactions. I quit trying to reach the limit when I got to well over 200 million levels--which is way more than I'll ever need! @@TRANCOUNT's return data type is an integer so it would probably run into problems if your nesting level exceeded 2.14 billion or so... To nest transactions you simply string together two or more BEGIN TRAN statements without a COMMIT or ROLLBACK statement "between" them. It should be obvious that transactions can only be nested when in Explicit Mode. Or to be more precise, upon issuing the required BEGIN TRAN statements SQL enters Explicit Mode.
```
BEGIN TRAN --We enter Explicit Mode here
INSERT TranTest (Col2)
VALUES (1)
SELECT @@TRANCOUNT
--Returns 1
SELECT * FROM TranTest
BEGIN TRAN
 DELETE TranTest
  WHERE Col1 = (SELECT MIN(Col1) FROM TranTest)
 SELECT @@TRANCOUNT 
 --Returns 2 indicating that there are now two open transactions.
 SELECT * FROM TranTest 
 --We can see that both the INSERT and DELETE appear to have taken effect.
COMMIT 
--Decrements @@TRANCOUNT by one but doesn't really commit the inner transaction.
ROLLBACK 
--Rolls back both transactions and sets @@TRANCOUNT to 0
```
Remember that a transaction--to include any nested transactions--must all commit or rollback together. Because of this, the first COMMIT really does nothing more than closes the inner transaction, it can't make the changes permanent since the inner transaction depends on the outcome of any higher level transactions. The COMMIT that is issued when @@TRANCOUNT is 1 is what I call the "Golden COMMIT" and finally makes all the changes permanent. Even though the inner COMMIT doesn't seem to do much, you can't neglect to issue it. The entire transaction is only committed after one COMMIT has been issued for every open transaction. In contrast ROLLBACK will always roll back all open transactions; "if one fails, they all fail" A result of ROLLBACK's behavior is that if the ROLLBACK is issued and then you try to issue a COMMIT or ROLLBACK you will get a 3902 or 3903 error respectively which indicates that there are no open transactions to COMMIT or ROLLBACK. For this, and other reasons I'll get into later, you should include error handling in your SQL code. If you check @@TRANCOUNT before issuing a COMMIT or ROLLBACK you can pretty well eliminate 3902 and 3903 errors. Just to cement the concept, try the following example:
```
DECLARE @Count int
SET @Count = 0
WHILE @Count < 100
BEGIN
  BEGIN TRAN 
  --We aren't doing any work here, just nesting transactions...
  SET @Count = @Count + 1
END
SELECT @@TRANCOUNT 
--Should return 100
COMMIT
SELECT @@TRANCOUNT 
--"99 bottles of beer on the wall..."
ROLLBACK
SELECT @@TRANCOUNT 
--"Go to Jail, don't pass GO..." We're right back to 0
```
The idea of nesting transactions applies to triggers and stored procedures too. If you have a complex set of stored procedures that call each other it can get difficult to follow all the possible paths, but any nested transactions will act exactly as I have already described. I should mention that there is always the error caveat that can really throw a monkey wrench into things. I'll deal with errors in some detail later, but will completely ignore errors for now. Below is a simple example of how you can nest a transaction across stored procedures:
```
CREATE PROC TranProc1
AS
BEGIN TRAN --Start the first level transaction
 INSERT TranTest (Col2)
 VALUES (1)
 EXEC TranProc2 --Call the other procedure from within the transaction
COMMIT --First level

CREATE PROC TranProc2
AS
BEGIN TRAN --Start the second level transaction
 DELETE TranTest
 WHERE Col1 = (SELECT MIN(Col1) FROM TranTest)
COMMIT --Second level
```
It doesn't matter where the BEGIN TRAN and COMMIT statements reside between the two procedures.
```
CREATE PROC TranProc1
AS
BEGIN TRAN --Start the first level transaction
 INSERT TranTest (Col2)
 VALUES (1)
 BEGIN TRAN --Start the second level transaction
 EXEC TranProc2
 COMMIT --Second level
COMMIT --First level
```
This stored procedure would do exactly the same thing as the first example, assuming that the second procedure lacked the BEGIN TRAN and COMMIT statements. Actually, even if the second procedure had the BEGIN...COMMIT statements in it, this procedure would work fine and perform the same function, we would just have three levels of transactions, the second of which would do nothing. So where you place your BEGIN...COMMIT is pretty much a matter of preference, not function. So far we have discussed SQL Server's Transaction Modes and the idea of nested transactions. I have tried to show how these two concepts relate to each other and hinted at how they relate to some of the other concepts listed in the introduction.

Optimizer Join Methods

2009-12-15T05:18:00.000-08:00

Optimizer Join Methods

SQL Server uses three join operators to process joins within a query. For smaller result sets, the optimizer might use a nested loop join but for larger results, a hash join or merge join may be used. The Query Optimizer always tries to identify the fastest way to join tables and will sometimes rearrange or sort tables in order to utilize a faster join method.
Nested loops
The nested loop join, also called a nested iteration, is the preferred join method for simple queries. The nested loop join operator has two inputs: an outer table and an inner table. When viewed in an execution plan, the top input for the nested loop join operator is the outer table while the bottom input to the nested loop join operator is the inner table. The outer table is located just below the nested loop operator in the execution plan.
The nested loop can be thought of as a set of loops, with the outer table being the outer loop and the inner table being the inner loop. The looping mechanism that a nested loop uses takes a row from the outer table and uses that row to scan the inner table for matching rows. If there is a matching row, the output is produced and if there is no matching rows; the mechanism will just go back to the outer table again and take the next row, repeating the scan of the inner table for matches. This process continues until all the rows in the outer table have been processed.
You can see how many times the inner table has been scanned by viewing the Executes column for the inner table in the execution plan. This will tell you how many rows of data exist in the outer table. Generally, the inner table or outer table selection for the nested loop is not important, but the optimizer tries to choose a smaller table for the inner loop if there is no index on both of the tables.
In cases where the inner table is very large, there is a possibility that the data pages which are accessed for the inner loop scan will be discarded between scans due to memory constraints on the server. The optimizer tries to see if the smaller table will be useful for the inner loop to keep the number of data pages small which may result in the pages not being flushed from the cache. If this will not work, it will choose another inner table, if there is no index on it. If there is an index, then it might choose a bigger table as an inner table just to speed up the scan process.
The optimizer might choose to perform a sort on some occasions to improve the seeking in the inner input. If we are joining the Customer table with the Orders table in the Northwind database, the customerIDs in the Orders table are random. If the Orders table is chosen as an outer table, then the outer loop takes each row of the outer table and searches the inner table, and then tries to find the match for the outer table. Because the customer IDs are not ordered in the outer table, the optimizer goes to the inner table may have to bring back data page that were previously placed into the cache for an earlier outer loop row if that data has been discarded. In this situation, it would be very useful for the optimizer to perform a sort on the Order table so that all the CustomerIDs will be group together allowing the data pages to be used multiple times in a row. So if you see a sort on the outer table in the execution plan, it's not bad. It's there to improve seeking capabilities or to adjust the performance of the query.
Nested loop joins are good in the fact that they consume very little memory and are usually superior to merge and hash joins on smaller inputs. One rule of thumb is to try to make sure that the joining columns always have an index on them, which may allow the optimizer to utilize a nested loop.
Example of Nested Loop Join
USE pubs
GO

SET STATISTICS PROFILE ON
GO

SELECT a.au_id, a.au_fname + ' ' + a.au_lname, ta.title_id
FROM dbo.authors a
INNER JOIN dbo.titleauthor ta
ON a.au_id = ta.au_id
GO

Execution Plan (Abridged)
SELECT a.au_id, a.au_fname + ' ' + a.au_lname, ta.title_id FROM dbo.authors a
INNER JOIN dbo.titleauthor ta ON a.au_id = ta.au_id
|--Compute Scalar(DEFINE:([Expr1002]=[a].[au_fname]+' '+[a].[au_lname]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([a].[au_id]))
|--Index Scan(OBJECT:([pubs].[dbo].[authors].[ncl_authors_phone] AS [a]))
|--Index Seek(OBJECT:([pubs].[dbo].[titleauthor].[auidind] AS [ta]), SEEK:([ta].[au_id]=[a].[au_id])

Merge Join:
Like the nested loop join, the merge join has two inputs with indexes being required on both inputs. The merge mechanism gets data rows from both inputs and compares each row of data. A matching row is created if both compared rows are equal. If the rows from both inputs are not equal, the row of data with the lower value is discarded and another row is obtained from the same input as the discarded row. The merge mechanism continues until all rows of data from both inputs are processed and all data has been merged into one result set.
With merge joins you might see a MANY-TO-MANY:() merge join in predicate along with the merge join operator. This means that the merge mechanism is performing a MANY-TO-MANY join within the tables. Often during the MANY-TO-MANY join operation, temporary tables are created for the MANY-TO-MANY join. This table creation will usually display a Table Spool operator just above the outer table as well as just above the inner table. The reason it generates this Table Spool is, in the case of many to many, it has to revisit the duplicate rows again; it has to rewind to the first duplicate value again. So in case of rewinding or revisiting the duplicates, it will be much more optimized if a temporary table is created and it visits those keys in the temporary table, rather than going back to the data page and reading the data row there.
Merge joins will be considered for all join clauses, except for CROSS JOIN and FULL JOIN clauses, with at least one of the joined columns having unique data. The presence of an ORDER BY clause will increase the chance that a merge join will be used. Merge joins are very fast if the columns are pre-indexed or pre-sorted. The optimizer might perform sorting on the fly, even if the columns are not in a sorted order, based on the cost if it decides if this sort is less costly than a corresponding nested loop join or hash join.
Example of Merge Join
SET STATISTICS PROFILE ON
GO

USE northwind
GO

SELECT * FROM dbo.orders o
INNER JOIN dbo.[order details] od
ON o.orderid = od.orderid
GO

Execution Plan (abridged)
SELECT * FROM dbo.orders o INNER JOIN dbo.[order details] od ON o.orderid = od.orderid
|--Merge Join(Inner Join, MERGE:([o].[OrderID])=([od].[OrderID]), RESIDUAL:([od].[OrderID]=[o].[OrderID]))
|--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders] AS [o]), ORDERED FORWARD)
|--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Order Details].[PK_Order_Details] AS [od]), ORDERED FORWARD)

Hash Join
As with the other join types, hash joins takes two inputs, a build input and a probe input with the smaller input being the probe input. When viewing an execution plan, the build input will be shown as the upper input and the probe input is shown as the bottom input. Hash joins utilizes two phases: the build phase and the probe phase.
The build phase consists of the build input being used to build the hash buckets using a hashing function. Hash buckets are like cells in memory, with the buckets being used to group the data into different groups according to the hash keys.
This grouping takes place when each row of data is taken from the build input on the tables and a hash function is performed to come up with the hash key. The hash key is an expression which consists of the set of columns in the equality predicate. In grouping operations, the columns of the group by list are the hash key. In set operations such as intersection, as well as in the removal of duplicates, the hash key consists of all columns in GROUP BY clause are used as the hash key.
If the hash key has a unique contiguous key, the key is know as minimal perfect hashing with every hash having its own slot with no gaps between the slots in the hash index. If the hash key is unique but noncontiguous, it is known as a perfect hashing with every hash having its own slot but there will be gaps between the slots in the hash index.
Whichever type of hash key is created, the hash key is used to place the rows of data into different hash buckets. With the possibility of having multiple hash buckets whose values are stored as a linked list and these linked lists are scanned when the probe input comes.
The probe phase consists of the optimizer taking one record at a time from the probe input and evaluating the hash key from the probe input to the hash buckets. The optimizer finds the appropriate hash bucket and scans all the values in that bucket by scanning the linked list values. During the scanning, the optimizer also tries to find out if there is a match to the hash key. If there is a match, an output is produced. If there is no match, then the optimizer continues with the next full value. Because the optimizer has to build the hash tables, the build input is completely read before actually reading the probe input. Then the optimizer goes to the probe table and reads all the rows in the probe input. The optimizer generally chooses a small table as a build table because all of the hash tables will be stored in memory. When the optimizer can place all the hash buckets into memory, the hash is called a “In-Memory Hash Join”. This fact makes hash joins generally memory-intensive.
Ideally, all these hash tables will be sitting in the memory itself, if they're small enough but if they're not small enough, SQL Optimizer will partition the build input and the probe input into multiple files and store them on a disk with each of these partitions being brought into the memory as they're needed. Using the hash function on the hash keys guarantees that any two joining records must be in the same pair of files; therefore, the problem of joining two large inputs has been reduced to multiple instances of the same problem, but of smaller size - a prototypical “divide-and-conquer” algorithm. In other words, we simply apply the same algorithm again to each pair of partition files -with a modified hash function, of course. This type of hash join using multiple files is called a “Grace Hash Join” and since the disk is involved, additional I/O are needed to get the partitions from the list to the memory. This process will cause performance degradation.
If the build input is so large that inputs for a standard external merge sorts would require multiple merge levels, multiple partitioning steps and multiple partitioning levels, a Recursive Hash Join will be used. If only some of the partitions are large, additional partitioning steps are used for only those specific partitions and in order to make all partitioning steps as fast as possible, large, asynchronous I/O operations are used so that a single thread can keep multiple disk drives busy.
If the build input is larger but not a lot larger than the available memory, elements of in-memory hash join and grace hash join are combined in a single step, producing a hybrid hash join.
Sometimes a residual predicate may be involved. If so, it will also use additional columns for when it is trying to compare the probe input value with a build probe input value. It generally uses a hash key value of the probe input versus the hash key value of the build input. There are some situations where it has to do a comparison of the actual column values of the probe input versus the actual column values of the build input. So in those situations you might see a residual predicate in the execution plan.
During the phases of the hash join the SQL Server optimizer might determine that the wrong table was used for the build input. In this case the optimizer will reverse the build and probe inputs in a process called “Role Reversal” in order to assure that the smaller of the two inputs is being used for the probe input.
Hash joins are very useful for ad hoc types of queries. Ad hoc queries are the queries for which we don't know what type of columns are used in the WHERE clause, so we cannot have indexes on all the columns. There is no index on a column, and those columns are mentioned in the WHERE clause, then hash join is the best one, because it scans the whole table and then performs the join on those tables.
When both join inputs are large and of similar size, performs as good as merge join, but when join inputs are large but differ significantly in size, a hash join will usually outperform a merge join by a fair margin. Remember, hash joins are both memory and CPU intensive and in the case of grace and recursive hash joins they can also require additional I/O costs.
Example of Hash Join
USE pubs
GO

SELECT * INTO #ttable1 FROM dbo.authors

SELECT * INTO #ttable2 FROM dbo.titleauthor

SELECT a.au_id, b.title_id
FROM #ttable1 a
INNER JOIN #ttable2 b
ON a.au_id = b.au_id

drop table #ttable1
drop table #ttable2
GO

Execution Plan (Abridged)
SELECT a.au_id, b.title_id FROM #ttable1 a INNER JOIN #ttable2 b ON a.au_id = b.au_id
|--Hash Match(Inner Join, HASH:([a].[au_id])=([b].[au_id]), RESIDUAL:([b].[au_id]=[a].[au_id]))
|--Table Scan(OBJECT:([tempdb].[dbo].[#ttable1_____________________________00000000001A] AS [a]))
|--Table Scan(OBJECT:([tempdb].[dbo].[#ttable2_____________________________00000000001A] AS [b]))

In this case, a simple index on the join columns will change the join method to a different type of join instead of a hash join.
CREATE NONCLUSTERED INDEX ncl_ttable1 ON #ttable1(au_id)

Execution Plan (Abridged)
SELECT a.au_id, b.title_id FROM #ttable1 a INNER JOIN #ttable2 b ON a.au_id = b.au_id
|--Nested Loops(Inner Join, OUTER REFERENCES:([b].[au_id])) |--Table Scan(OBJECT:([tempdb].[dbo].[#ttable2_____________________________00000000001A] AS [b]))
|--Index Seek(OBJECT:([tempdb].[dbo].[#ttable1_____________________________00000000001A].[ncl_ttable1]

Summary
Understanding the different types of joins used by the optimizer will help developers and DBAs understand how the optimizer is routing their queries. Developers often create queries without knowing that it would only take a few “tweaks” to produce an execution plan that utilizes one optimizer join method over another. These small “tweaks” can have dramatic effects on the optimization of the query and the ultimate satisfaction of the query by the end-users.
Knowing how to read and understand execution plans and the joins used by the optimizer can be one of those “tools” that a DBA can have in their tool chest that separates them from all the other DBAs in their company.

How do I get the IDENTITY / AUTONUMBER value for the row I inserted?

2009-12-14T23:09:00.000-08:00

PRINT IDENT_CURRENT('table')

PRINT SCOPE_IDENTITY()

CREATE PROCEDURE myProc
@param1 INT
AS
BEGIN
SET NOCOUNT ON
INSERT INTO someTable
(
intColumn
)
VALUES
(
@param1
)
SELECT NEWID = SCOPE_IDENTITY()
END

<%
fakeValue = 5
set conn = CreateObject("ADODB.Connection")
conn.open ""
set rs = conn.execute("EXEC myProc @param1=" & fakeValue)
response.write "New ID was " & rs(0)
rs.close: set rs = nothing
conn.close: set conn = nothing
%>

SELECT NEWID = SCOPE_IDENTITY()

SELECT NEWID = @@IDENTITY

CREATE TRIGGER triggerInsert_tablename ON tablename FOR INSERT AS
BEGIN
SELECT @@IDENTITY
-- rest of trigger's logic...
END
GO

CREATE TABLE Table1
(
i INTEGER IDENTITY(1,1) PRIMARY KEY,
x INTEGER NOT NULL UNIQUE
)
GO

CREATE TRIGGER trg_Table1 ON Table1
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
INSERT INTO Table1 (x)
SELECT x FROM Inserted
END
GO

INSERT INTO Table1 (x) VALUES (1)
GO

SELECT SCOPE_IDENTITY(), IDENT_CURRENT('Table1')

------ ------
NULL 1

Execution Plans

2009-12-14T04:58:00.000-08:00

When I started using SQL Server, I was not using the “Show Execution Plan” properly to analyse the query. I always thought that when I compose a query, it is the best it can be; I never even think of performance on my query and I did not give much importance to looking at the “Execution Plan” on my query. In the initial days of my career, I was happy to retrieve the data, not even knowing how it was returned to me and what sort of “Execution Plan” was used on the query. I presumed SQL Server would handle the query performance. I think it is the nature for software engineers, when starting out, or when first learning new technology, not to take the time to learn everything they really need to know before writing a code. Perhaps this is because of competition and immaturity in the IT field.
Days are starting moving and data has been growing on the database file. On one fine day, my customer was not happy with the performance on the query using the application. He has come to me with an unhappy face stating that he was spending extra time to finish his office work due to the slowness of the queries. Initially, I told the customer to increase the system resources, like increase the hard disk in the machine as a temporary solution. Albeit, hard disk cost is cheap but it is not the permanent solution for the performance degradation in the query. He agreed in half-mind that he will do the required things from his side but he asked me to re-look and fine-tune the queries for a permanent solution instead of suggesting he keep on increasing the system resources. I have to consider his personal opinion because customer satisfaction is important in the IT industry. I have promised to my customer that I will re-look and fine-tune the queries.
HOW?
In the initial days of my career, I know the basic things in MS-SQL Server. To be frank, I was not having any idea on my mind whilst doing promise to my customer. But, I personally felt that I would do something to achieve the task with the help of “GOOGLE” and “BOL”. Thanks to “GOOGLE” and “BOL”.
I was reading MS-SQL books, BOL help and searching on websites. I have heard and crossed the concept of “Show Execution Plan”. We can set this option ON using SQL Query Analyzer. Show Execution Plan is an important graphical tool that enables the developer and DBA to analyse, assist and optimise the query and improve the performance of the query.
Show Execution Plan displays different icons for a different task. I am mainly interested on “Table Scan”, “Index Scan” & “Index Seek” & “Clustered Index Scan” & “Clustered Index Seek” icons in this article. May be I could be writing in my future article on other icons.
As the days and years have moved along like a formula 1 race car, I have decided that it is time for me to fully understand how “Table Scan”, “Index Scan” & “Index Seek” & “Clustered Index Scan” & “ClusteredIndex Seek” icons work.
Now, I am ready to analyse the queries and upgrade the performance on my queries. Before analysing the queries, a few questions have been raised in my mind.
When did MS-SQL Server use “Table Scan”?
When did MS-SQL Server use “Index Scan”?
When did MS-SQL Server use “Index Seek”?
When did MS-SQL Server use “Clustered Index Scan”?
When did MS-SQL Server use “Clustered Index Seek”?
I am mainly concern that in what bases MS-SQL Server would use one of this option to analyse the query. I now have taken that time, and here is what I learned. This information should be useful to new developers and DBAs. I have decided to write this article to share my knowledge to help other to get better idea on these methods.
If you like, you can read this article as is, or in front of a SQL Server, following along with my exercises.
Getting Started
To explain the SQL Server “Table Scan”, “Index Scan” & “Index Seek” & “Clustered Index Scan” & “Clustered Index Seek” using Show Execution Plan; let’s start by creating a table and adding some sample data using the following script. I presume you have got a database to use the following script; otherwise you will need to create one.

Create Table PerformanceIssue
(
   PRID UniqueIdentifier NOT NULL,
   PRCode Int NOT NULL,
   PRDesc Varchar (100) NOT NULL
)
ON [PRIMARY]

Table has been created and this table now needs some data; so let’s add 100,000 records in this table using this script. Script will take few minutes to finish executing insert statement for 100,000 records in a table, please bear with the query to get finish.

Declare @Loop  Int
Declare @PRID  UniqueIdentifier
Declare @ PRDesc Varchar (100)

Set @Loop = 1
Set @ PRDesc = ''

WHILE @Loop <= 100000
 BEGIN
   Set @PRID =   NewID()
   Set @PRDesc = ' PerformanceIssue - ' +  Convert( Varchar(10),@Loop )
   Insert Into  PerformanceIssue Values (@PRID, @Loop, @PRDesc)
   Set @Loop = @Loop + 1
 END

Script has been successful completed. Data has been inserted in the table.
Now, let’s view the contents of the table by executing the following command in Query Analyzer:

Select PRID,  PRCode, PRDesc
 From PerformanceIssue
GO

I would not display the output of the table due to 100,000 records and it will occupy more pages to display the output. As you would expect, the data we inserted earlier has been displayed in Query Analyzer.
As I told you before about the article, I would be explaining the “Table Scan”, “Index Scan” & “Index Seek” & “Clustered Index Scan” & “Clustered Index Seek”. Which one could be the ideal one that will upgrade the performance in the database system?
SQL Server returns data as it stored and we would like to know that what scan mechanism MS-SQL server has incorporate to retrieve the data. Let’s start with the “Table Scan” topic. This got me to think about the following. When does “Table Scan” happen in SQL Server for a table?
Let’s enable the “Show Execution Plan” in Query Analyzer using Query select “Show Execution Plan” or Alt + Q. Ctr + K is a shortcut method.
Let’s view the graphical execution plan for the query.

Select PRID,  PRCode, PRDesc 
From PerformanceIssue
GO

Query has been executed and saw the executions plan for the query. MS-SQL server for the query has used “Table Scan” method. Data have been retrieved using "Table Scan" method. I was asking myself why the “Table Scan” has happen and in what bases MS-SQL server uses "Table Scan" method. It is a simple table that contains 100,000 records. Is it because I have asked MS-SQL server to retrieve 100,000 records from the table? When I was thinking in different angles. Some perplex in my mind. Now, my question how do I prevent "Table Scan" method on my query. I was not having idea what scan mechanism MS-SQL server has used when I was executing the query. I decided to optimise the query. How? I selected two columns [PRID, PRCode] out of three columns in the select statement.

Select PRID,  PRCode
 From  PerformanceIssue
go

Query has been executed and execution plan is same as my previous query. I still want to optimise the query and decided to have only one [PRID] column in the select statement.

Select PRID 
 From PerformanceIssue

GO

Query has been executed and execution plan is same as my first query. Estimated row size was showing difference on the three-execution plan. We are not giving much importance to the Estimated row size attribute. Quickly I decided to retrieve only one record and see what could be the execution plan for the query. I have executed the following query.

Select PRID,  PRCode, PRDesc 
 From PerformanceIssue
 Where PRID = 'D386C151-5F74-4C2A-B527-86FEF9712955'
-- PRID GUID value might be differ in your machine

GO

Execution of the query was over and execution plan has been displayed.

Query has incorporated “Table Scan” method to display the data. I saw the execution plan and still execution plan uses “Table Scan” method on my query.
Then, I need to think some other alternative solution by spending some sleepless nights to avoid "Table Scan" method. "Sleepless nights", yes, it is common in IT industry. I was talking myself how do I solve this problem. First think was come to my mind was "Index" in a table. Currently, I haven't added an “Index" in PerformanceIssue table. Is it because there is no index in the table? Query has used "Table Scan" method. Is that the case, I decided to create non-clustered index on PRID column in PerformanceIssue table. Is this true adding index will solve my queries using "Table Scan" method? We will discuss those questions on "Index Scan" & “Index Seek” topic. Let's now move on to "Index Scan" & “Index Seek” topic.

Index Scan & Index Seek

Let’s create a non-clustered index on PRID column on my table.

CREATE UNIQUE NONCLUSTERED INDEX UNC_PRID
  ON PerformanceIssue (PRID)
GO

Non-Clustered index has been created successfully. Index has been created. I presume you have knowledge on non-clustered index and how non-clustered index works in MS-SQL Server.

Let's execute the select statement and view the graphical execution plan for the query.

Select PRID,  PRCode, PRDesc 
 From PerformanceIssue
go

Query has been executed and saw the executions plan for the query. "Table Scan" method has been used on the query. I was really shocked to see the "Table Scan" method used on the query. I was more perplexed and rest less why the “Table Scan” has happen? Why MS-SQL Server has not utilise the non-clustered index on my query. I was asking myself when would "Index Scan" method come to the picture. I decided to optimise the query. I have selected two columns [PRID, PRCode] out of three columns.

Select PRID,  PRCode From  PerformanceIssue
GO

Query has been executed and execution plan is same as my previous query. I still want to optimise the query and decided to have only one [PRID] column in the select statement.

Select PRID 
 From PerformanceIssue
GO

Query has been executed and saw the execution plan for the query.

"Index Scan" has been used on the query. I am glad to see the "Index Scan" has been used. My query is using "Index Scan" method and my next question is when would “Index Scan” method happen and how? I have created an index on "PRID" column and selected indexed column [PRID] in the select statement. Query has been executed and MS-SQL server scans all the index pages and uses "Index Scan" method. We have clubbed the indexed and non-indexed columns in the select statement. MS-SQL server was not able recognized and use "Index Scan" method for the previous two queries. MS-SQL Server would use "Index Scan" method only indexed column on the select statement. I do not know how exactly MS-SQL Server does behind the code. After this practical knowledge, understand and execution of the queries, I have come to conclusion that we select only the indexed column in the select statement. MS-SQL server would use "Index Scan" method for the query.
I was refreshing myself how do I incorporate “Index Seek” method. When I was using the word “Seek”, first think would strike to my mind was finding a particular/specific one. For an example, I want to find out the MS-SQL server scholars and experts in the earth. The keyword/filter is "MS-SQL Server" on skillset column. I will filter down the scholars and experts name from knowledge table using the skillset column. My output would be restricted to the particular hunt.
Let’s see how to incorporate the “Index Seek” using our PerformanceIssue table. We have already executed the query with different combination. Execution plan has been display the "Table Scan" and "Index Scan" method. We will see how to get the "Index Seek" in the execution plan using our query. After thinking all the options, I have decided to use WHERE clause in the select statement. I confirmed to use three different select statements using WHERE clause. To find out which select statement MS-SQL Server uses "Index Seek" in the execution plan. I started with the 1^st select statement with the WHERE clause.

Select PRID,  PRCode, PRDesc 
 From PerformanceIssue
 Where  PRCode = 8
go

Query has been executed and "Table scan" has been used in the execution plan.

I moved on to use the 2^nd select statement with the WHERE clause.

Select PRID,  PRCode, PRDesc
 From PerformanceIssue
 Where  PRDesc = ' PerformanceIssue - 8'
go

Query has been executed and "Table scan" has been used in the execution plan.

I moved on to use the 3^rd select statement with the WHERE clause.

Select PRID,  PRCode, PRDesc
 From PerformanceIssue 
 Where PRID = 'D386C151-5F74-4C2A-B527-86FEF9712955'

-- PRID GUID value might be differ in your machine
go

Query has been executed and "Index Seek" along with "Bookmark Lookup" has been used in the execution plan. We have understand after execution the three select statement with the WHERE clause. "Index Seek" has been used when we use the indexed column [PRID] in the WHERE clause. We have selected non-indexed column in the select statement. Execution plan has used "Index Seek" and "Bookmark Lookup". "Bookmark Lookup" has been used to find out the non-index column value from the table or Clustered Index. We can ignore the "Bookmark Lookup" in the execution plan by removing non-indexed column from the select statement. Execution plan would be "Index Seek". This does not make meaningful to the select statement to retrieve the PRID value which we already passed in the WHERE clause.
I personally consider "Index Seek" is better than "Index Scan" and "Table Scan" in couple of points to upgrade the performance in the system.

Index Seek" does not need to scan through "Table" and "Index" page as “Table Scan” and “Index Scan” does.
Index Seek" will search and retrieve the hunted row [used in WHERE clause] much faster than the "Index Scan" and "Table Scan".

After I finish all the combination, I was explaining to my colleagues. One my colleague asked a genuine question to me. When would MS-SQL server use “Clustered Index Scan” and “Clustered Index Seek”? I was speechless at that point of time. I appreciated him and I decided to find the use of “Clustered Index Scan” and “Clustered Index Seek” topics.
I decided to find the use of “Clustered Index Scan” and “Clustered Index Seek”. I should have “Clustered index” in a table to get the “Clustered Index Scan” and “Clustered Index Seek” in the execution plan. I decided to drop the existing non-clustered index and create a clustered index on PRCode column in PerformanceIssue table. Let's now move on to " Clustered Index Scan" & “Clustered Index Seek” topic.
Clustered Index Scan & Clustered Index Seek
The following script will drop the existing index on PRID column in PerformanceIssue table create a clustered index on PRCode column in PerformanceIssue table.

Drop Index  PerformanceIssue.UNC_PRID
GO
CREATE UNIQUE CLUSTERED INDEX UC_PRCode
  ON  PerformanceIssue( PRCode)
go
-------------
Clustered index has been created successfully.

I will execute the select statement to find out the “Clustered Index Scan” in the execution plan. I have executed the following query.
Select PRID, PRCode, PRDesc From PerformanceIssue go Query has been executed and "Clustered Index Scan" has been used in the execution plan.

We should have a "Clustered index" in a table to get the "Clustered Index Scan" in the execution plan for the select statement. We could have a single column or more than one column in the select statement, execution plan would use "Clustered Index Scan". When do we have "Clustered Index Seek" in the execution plan? How do we get the "Clustered Index Seek" in the execution plan?
We will see how to get the "Clustered Index Seek" in the execution plan using our query. After I was rolling my head, I took decision to use WHERE clause in the select statement. I confirmed to use three different select statements using WHERE clause. To find out which select statement MS-SQL Server uses "Clustered Index Seek" in the execution plan. I started with the 1^st select statement with the WHERE clause.
Select PRID, PRCode, PRDesc From PerformanceIssue Where PRDesc = ' PerformanceIssue - 8' go Query has been executed and "Clustered Index Scan" has been used in the execution plan.

I decided to use the 2^nd select statement with the WHERE clause.
Select PRID, PRCode, PRDesc From PerformanceIssue Where PRID = 'D386C151-5F74-4C2A-B527-86FEF9712955' -- PRID GUID value might be differ in your machine go Query has been executed and "Clustered Index Scan" has been used in the execution plan.

I decided to use the 3^rd select statement with the WHERE clause.
Select PRID, PRCode, PRDesc From PerformanceIssue Where PRCode = 8 go Query has been executed and “Clustered Index Seek” has been used in the execution plan.

Query has been executed and "Clustered Index Seek" has been used in the execution plan. We have understand after execution the three select statement with the WHERE clause. "Clustered Index Seek" has been used when we use the indexed column [ PRCode] in the WHERE clause. We have selected non-indexed column in the select statement. Execution plan has used "Clustered Index Seek". Execution plan will not use "Bookmark Lookup" for the non-indexed columns in the select statement for the clustered index table.
I personally feel "Clustered Index Seek" is better than "Clustered Index Scan" and "Index Seek" in couple of points to upgrade the performance in the system.

Clustered Index Seek" does not need to scan through all the Clustered index pages.
Clustered Index Seek" does not create a “Bookmark Lookup” comparing to "Index Seek" for the non-indexed columns in the select statement.

I understand the execution plan and gained practical knowledge on execution plan. I know which scan mechanism will improve the performance in the system and bring back the customer smiling face. Now, I will have couple of beer to have peaceful sleep.

Index has been created.

I presume you have knowledge on non-clustered index and how non-clustered index works in MS-SQL Server.

SQL Aggregate Functions and NULL

2009-12-13T21:52:00.003-08:00

This article was written to help answer some of those questions by explaining how SQL aggregate functions handle NULLs, and to describe some other "gotcha's" when dealing with SQL aggregate functions. Each section of the article ends with a summary of "gotcha's" to look out for when using these function.
The ANSI SQL-92 Standard defines five aggregate functions (which are also referred to in the SQL-92 standard as set functions.) SQL NULLs receive special internal handling that the database designer/programmer needs to be aware of when using these functions. This article describes how ANSI SQL-92 aggregate functions handle NULLs, per the ANSI SQL-92 standard.
I reference the ANSI SQL-92 Standard throughout the article, and the sample code requires the Microsoft SQL Server Northwind Sample Database to be run. All samples were run on SQL Server 2000.

Aggregate Functions

ANSI SQL-92 defines five aggregate functions. For all set functions that specify a column name, the following process takes place behind the scenes:

The search expression (the WHERE clause) is applied.
The value expression (scalar functions on the column like COALESCE()) is applied to each row of the results of Step 1.
NULL values are eliminated from our results*.

*NOTE: COUNT(*) is a special case, which we describe in detail below.
ANSI SQL-92 specifies that a warning message will be returned when NULLs are eliminated by set functions. You can turn on this option by using the SET ANSI_WARNINGS ON command. The SET ANSI_WARNINGS ON option returns the following error message "Warning: Null value is eliminated by an aggregate or other set operation."
The SET ANSI_WARNINGS ON also affects how SQL Server handles division by zero, arithmetic overflow and character/binary data truncation behavior. Before changing your SET ANSI_WARNINGS option, be sure to reference the following MSDN article: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_6d2r.asp

Gotcha: SET ANSI_WARNINGS ON affects several aspects of SQL Server behavior. Be sure to check http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_6d2r.asp before changing this option.

COUNT()

The COUNT() set function has two forms:

COUNT(column) – Returns the count of rows in a table, less NULLs
COUNT(*) – Returns the cardinality (total number of rows) of a table

The following code demonstrates the difference between COUNT(column) and COUNT(*):

SELECT COUNT(*) AS TotalRows, COUNT(region) AS NonNULLRows, COUNT(*) - COUNT(region) AS NULLRows
FROM [Northwind].[dbo].[suppliers]

This query returns the following result:

TotalRows	NonNULLRows	NULLRows
29	9	20

TotalRows is COUNT(*), the cardinality (total number of rows) of the suppliers table
NonNULLRows is COUNT(region), the total number of rows of the suppliers table in which region is not NULL
NULLRows is COUNT(*) – COUNT(region), the difference between the cardinality of the suppliers table and the number of rows in which region is not NULL; basically this returns the number of rows in which region *is* NULL.

The fact that COUNT(*) and COUNT(column) treat NULLs differently can be used to your advantage in many situations such as calculating the NULLRows column above.
Notice that we can get the NULLRows count from above with the following query as well:

SELECT COUNT(*) AS NULLRows
FROM [Northwind].[dbo].[suppliers]
WHERE region IS NULL

This will not work if you specify the region column in your count, however. Here we'll take a look at why it is important to understand the order in which SQL performs the steps in a SELECT statement that contains an aggregate function. This query returns zero as the NULLRows count:

SELECT COUNT(region) AS NULLRows
FROM [Northwind].[dbo].[suppliers]
WHERE region IS NULL

Why does this query return zero? Remember, by definition COUNT(column) applies the value expression, the search expression and then eliminates NULLs before giving you a final count! Here's a play-by-play of what happens in our example:

The WHERE clause eliminates every row in which region is not NULL.
The value expression in this example is simply a column name - region.
The COUNT(region) function eliminates every row in which region is NULL.
Since we've explicitly eliminated all non-NULL columns with the WHERE clause and implicitly eliminated all NULL columns with the COUNT(region) statement, we end up with a zero rows.

Gotcha: COUNT(column) eliminates NULL values; COUNT(*) returns the cardinality (total number of rows) for an entire table. The order in which SQL performs the steps in aggregate calculations can have implications for your results.

SUM()

SUM(), like the other set functions, applies the search expression, then the value expression, eliminates NULLs and finally returns a sum of the remaining rows. It is important to note that SUM() excludes NULLs, as NULL scalar arithmetic normally returns NULL. For instance, let's do a scalar addition of all the values in the reportsto column of the employees table:

SELECT 2 + NULL + 2 + 2 + 2 + 5 + 5 + 2 + 5

This SELECT statement will return NULL, since NULL plus anything always returns NULL. If we use the SUM() function on the table, however, we can rest assured that NULLs are eliminated from our result:

SELECT SUM(reportsto) AS RowSum
FROM [Northwind].[dbo].[employees]

The result of this query:

RowSum
25

Gotcha: NULLs are eliminated by the SUM() function; in scalar arithmetic, however, NULL added to anything equals NULL.

AVG() NULL-handling

The AVG() function returns the average of the column, excluding NULLs. There are a couple of "gotcha's" concerning the AVG() function. The first "gotcha" is that NULLs are excluded, as with other set functions, so your answer may not be what you expect. The second "gotcha" is that, by definition, AVG() returns a number of the same scale as the column it is performed on. Again, this could throw your answer off a bit.
We'll tackle the NULL issue first. AVG() specifically excludes NULLs when it calculates averages. The following query divides the sum of the reportsto column by the count of non-NULL rows. In our example, one row is excluded from the calculation:

SELECT AVG(reportsto) AS ColAvg
FROM [Northwind].[dbo].[employees]

Our result is:

ColAvg
3

This is the same result as if we performed this query:

SELECT SUM(reportsto)/COUNT(reportsto)
FROM [Northwind].[dbo].[employees]

So how does excluding NULLs affect our average? We can use COALESCE() function to see.

SELECT AVG(COALESCE(reportsto, 0)) AS ColAvg
FROM [Northwind].[dbo].[employees]

The COALESCE(reportsto, 0) statement forces AVG() to count NULL rows as zeroes; forcing every single row to be counted. This gives us the following result:

ColAvg
2

We can get the same result using the SUM() and COUNT() set functions:

SELECT SUM(reportsto)/COUNT(*) AS ColAvg
FROM [Northwind].[dbo].[employees]

ColAvg
2

Here we are performing a SUM() on the rows that are not NULL, then dividing by the total number of rows, effectively treating the NULL rows as zeroes in our average. In this instance we're dealing with smaller numbers and only nine rows. With larger numbers and more rows the difference is more pronounced.
So which method should you use? That depends on the result you're looking for. Consider an example of calculating the average payroll for a department. Suppose you have the following table:

Name	Weekly Pay
Joe M.	400.00
Lisa D.	500.00

Our average is $450.00 per week. Now let's say that we hire a new guy, but his weekly pay has not been put into the system yet. Now we have:

Name	Weekly Pay
Joe M.	400.00
Lisa D.	500.00
Tom J.	NULL

If we include Tom's missing salary in our average (treat it like 0.00), our average goes down to $300.00 per week. If we leave Tom out of it for now – the default for AVG() – we get $450.00 per week again.
So which method is correct? Ignore NULLs or treat them as zeroes in your averages? That depends entirely on your objectives and corporate policies. The main thing is to be consistent in your calculations. If you ignore NULLs in calculating averages, use that same methodology every time; otherwise your results will not be comparable from year to year, quarter to quarter or even day to day.

Gotcha: AVG() eliminates NULLs when calculating your average.

AVG() Scale

The second issue is scale. In our examples, the averages we received were calculated and returned with the same scale (digits after the decimal point) as the column; in this case the column is an INT data type, so no decimals were returned. We'll apply the CAST operator to see what would have been returned if we were using a floating-point data type:

SELECT CAST(AVG(reportsto) AS FLOAT) AS ColAvg
FROM [Northwind].[dbo].[employees]

ColAvg
3.0

So far, not much has changed. We received 3 previously and 3.0 this time. So let's look at the equivalent average using SUM() and COUNT():

SELECT CAST(SUM(reportsto) AS FLOAT)/COUNT(reportsto) AS ColAvg
FROM [Northwind].[dbo].[employees]

ColAvg
3.125

Notice that we are forcing floating point precision now by casting the reportsto column as a FLOAT. We can get this same result by moving the CAST inside the AVG() function, like this:

SELECT (AVG(CAST(reportsto AS FLOAT))) AS ColAvg
FROM [Northwind].[dbo].[employees]

So how did we end up with a different value the second time? Since AVG() calculates using the same scale as the column data type, our INT column average is calculated with no decimal places. In the first example, we CAST the result of the AVG() function to a FLOAT type, but the AVG() was calculated as an INT first.
In the second example, we CAST the SUM() of the reportsto column to a floating point type, and then divided; so we didn't lose any digits after the decimal point in the calculation. As shown, we can get the same result by using CAST on the column inside the AVG() function. This is particularly important to note in financial and scientific calculations, where the scale is particularly important to our calculations.
If your scale requirements are greater than the scale of your column, you might need to use the SUM(column)/COUNT(column) method of calculating your average and CAST it to a floating or fixed-point type, or CAST inside of the AVG() function.

Gotcha: AVG() also uses the precision (total number of digits) and scale (number of digits after the decimal point) of the column you are performing the AVG() on.

MIN(), MAX()

The MIN() and MAX() functions return the minimum and maximum values of a column, respectively. Like the other set functions, these also eliminate NULLs before returning a result.
For numeric data types, MIN() and MAX() simply return the minimum or maximum number in the column. For character data types, the values are compared using standard SQL form: the shorter of the values is right-padded with 'pad characters' (usually spaces). This means that character columns with trailing spaces are mixed in with character columns without trailing spaces. As an example:

IF 'hi' = 'hi '
  PRINT 'Equal'
ELSE
  PRINT 'Not Equal'

Depending on your collation settings, string comparisons might be case-sensitive or case-insensitive.

Gotcha: NULLs are eliminated by MIN() and MAX() functions. For character data types, shorter values are right-padded with 'pad characters' (often spaces) before comparing them.

Conclusion

SQL set functions are very useful in calculating basic statistical values. By knowing how SQL set functions handle NULL values you can ensure that you always get accurate, high-quality results.

Read Only Tables

2009-12-11T05:12:00.000-08:00

Most SQL Server DBAs probably know that you can have a read only database for those situations where you don't want anything changed, like an archived set of data.
I had someone query the other day about setting a table to read only. They'd known you could do an entire database, but what about a table?

The Easy Way

Most people that want to set something to read only usually want to prevent most people from changing things, but they want to be able to make a change themselves. Usually this is someone that is administering the file and they mark the file as read-only. Users can access the file, but not change it. If the administrator wants to make a chance, they unmark the read-only flag, make their change, and save it, setting the read-only flag again.
While tables in SQL Server don't have any specific "read-only" setting, there is a simple way to ensure they are read only. It's similar to the ACL permissions that exist in the file system. You just grant them SELECT permissions on the table with no other permissions.
This means that as you setup your roles and make use of the fixed database roles that are built into SQL Server, you need to be careful of permissions granted to those roles. In general, you will not want to grant INSERT/UPDATE/DELETE permissions to any role that does not need to change the table. This means the builtin db_datawriter should not be assigned.
If you find yourself in a situation where you want a group of people to have read-only access, but they are members of other roles that have access, you can easily solve this issue as well. Create a new role for the read-only group and DENY permissions for INSERT, UPDATE, and DELETE on the table. This will override the permissions granted from other roles.
Note: One caveat here. Permissions granted on a column basis are not overridden by DENY. This is a bug that may be corrected by the time you read this, so check this before you implement a role with DENY permissions.
The nice thing about this approach is that some users see the table as read-only and others can make changes. However since the user does not have permissions errors are reported to the calling application. If the application doesn't handle these errors well, there could be issues with this method.

The Hard Way

There is a second way to make a table read-only, which was the first thing to come to mind for me. Since I've been doing DDL trigger work with SQL Server 2005, this was the first thing I thought of when I was asked the question.
You can setup a trigger on a table that fires for the INSERT, UPDATE, and DELETE events and rolls back any changes. This is simple to do and for a table called Customers, you would compile this:

create trigger trCustomers
 on Customers
 for insert, update, delete
as
rollback transaction
go

This trigger will allow a user to make a change, immediately undo the change by rolling back the implicit transaction, and report success to the user. If you've ever been the victim of this, it can be really annoying, but it does prevent all changes to the table. The downside of this approach is that no one can make changes to the table without the trigger being disabled or removed, depending on your version of SQL Server.
There is one other downside of this method. If you have users that are unaware of its implementation, then your phone may ring more often than you'd like because their data changes are not being recorded.

Conclusion

Setting a table to read only isn't a complicated process, but the two methods each have their advantages and disadvantages. Personally I think the first method is the best way to handle things, but if you do not want errors returned to users, then the second way may work better in your environment.
One last variation on the read-only table is the logging table. Often once something is logged, for troubleshooting, tracking, or regulatory purposes, you do not want it changed, or possibly even read. I have seen systems that granted only INSERT permissions or maybe INSERT and SELECT permissions on a table to ensure that users could only add data to it. In this case, you might want to also add a trigger to prevent updates or deletes and ensure that your data is protected.

The Pitfall of "Not Equal To"

2009-12-11T05:04:00.000-08:00

INTRODUCTION

The "Not Equal To" (<>) operator is useful when you need to filter your data and exclude some rows from the result of query, but there are cases that when you really mean MyColumn<>'SomeValue', you should not use this operator in order to get the correct result!
Here is an example: Suppose that a Testing Center registers students for IT exams. Obviously each student can take more than one exam. Let's create the required tables and populate them with some rows:

CREATE TABLE Students (
StID INT PRIMAEY KEY,
StName NVARCHAR(50))
GO

INSERT Students VALUES (1,'Jack')
INSERT Students VALUES (2,'Anna')
INSERT Students VALUES (3,'Bob')
GO

CREATE TABLE StudentExam (
StID INT,
ExamName VARCHAR(50))

INSERT StudentExam VALUES (1,'SQL Server')
INSERT StudentExam VALUES (2,'VB.NET')
INSERT StudentExam VALUES (2,'C#.NET')
INSERT StudentExam VALUES (1,'XML')

In a perfect design, we would need another table called Exams which stores the full specifications of each exam, but I have denormalized it with StudentExam table for the sake of simplification. Now we are asked to prepare a report that lists information of students who have taken SQL Server exam:

SELECT s.* FROM Students s
 JOIN StudentExam se
 ON s.StID=se.StID
  WHERE se.ExamName='SQL Server'

StID        StName
----------- --------------------------------------------------
1           Jack

(1 row(s) affected)

Well, everything is OK so far, but the issue turns up when we are asked to query the students who have NOT taken SQL Server exam. The first thing that might come to mind is replacing the "Equal" operator in WHERE clause with "Not Equal To" operator:

SELECT s.* FROM Students s
 JOIN StudentExam se
 ON s.StID=se.StID
  WHERE se.ExamName<>'SQL Server'

StID        StName
----------- --------------------------------------------------
1           Jack
2           Anna
2           Anna

(3 row(s) affected)

Note that although Bob did not appear in the result because he has not taken any exam to satisfy an INNER JOIN, again Jack is there!
The subtle reason of this is that Jack still satisfies the WHERE clause because of his second exam, XML! This exam causes the WHERE clause return "True" when he is being checked by the condition. Basically you must take care of this problem when dealing with one-to-many joins. If the relationship between Students and StudentExam was one-to-one (each student could take only one exam), the previous query would work fine.
To produce the correct report, you can simply use a subquery to build the list of students who have taken SQL Server exam:

SELECT StID FROM StudentExam WHERE ExamName='SQL Server'

Then any student that does not appear in this list, must be returned for our report:

SELECT * FROM Students WHERE StID NOT IN
(SELECT StID FROM StudentExam WHERE ExamName='SQL Server')

As you see in the graphical execution plan, an OUTER JOIN is performed by the Query Optimizer. Alternatively you may write this query and use an OUTER JOIN yourself:

SELECT s.* FROM Students s LEFT JOIN
 (SELECT StID FROM StudentExam WHERE ExamName='SQL Server') se
 ON s.StID=se.StID
  WHERE se.StID IS NULL

Using the OUTPUT Clause to Capture Identity Values on Multi-Row Inserts

2009-12-10T22:08:00.000-08:00

SQL Server 2005 introducted the OUTPUT clause which we can use to capture values from the inserted and deleted virtual tables. Previously this data was only available through triggers. We can use this in an INSERT ... SELECT statement to capture all the inserted identity values. Previously this required some type of loop or temporarily altering the target table.

We'll start with two tables: a product table and table of products to insert. The scenario is a vendor that sends you a complete list of all their products and you only need to insert the rows that don't already exist. However you need to insert those new rows into multiple tables. The following script will create the tables in tempdb based on data in AdventureWorks.

USE tempdb
GO
IF  EXISTS (SELECT * FROM sys.objects 
  WHERE object_id = OBJECT_ID(N'[dbo].[Product]') AND type in (N'U'))
 DROP TABLE [dbo].[Product]
GO
IF  EXISTS (SELECT * FROM sys.objects 
  WHERE object_id = OBJECT_ID(N'[dbo].ProductsToInsert') AND type in (N'U'))
 DROP TABLE [dbo].ProductsToInsert
GO

CREATE TABLE Product (
 ProductID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
 [Name] NVARCHAR(50) NOT NULL,
 ProductNumber NVARCHAR(25) NOT NULL,
 ListPrice MONEY NOT NULL)
GO
CREATE UNIQUE INDEX IX_Product_ProductNumber ON Product ( ProductNumber )
GO

CREATE TABLE ProductsToInsert (
 RowID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
 [Name] NVARCHAR(50) NOT NULL,
 ProductNumber NVARCHAR(25) NOT NULL,
 ListPrice MONEY NOT NULL,
 InsertedIdentityValue INT NULL)
GO
INSERT Product ([Name], ProductNumber, ListPrice)
SELECT TOP 450 [Name], ProductNumber, ListPrice
FROM AdventureWorks.Production.Product
ORDER BY SellStartDate, ProductID
GO
INSERT ProductsToInsert ([Name], ProductNumber, ListPrice)
SELECT  [Name], ProductNumber, ListPrice
FROM AdventureWorks.Production.Product
GO

The Product table has an identity column as its primary key. Product number is a natural key on the table. The ProductsToInsert table has the ProductNumber column and a column for whatever identity value is inserted when we put the row into the Product table. You'll notice I only put 450 of the products in the Product table to start with but all 504 in the ProductsToInsert table.
A simple script to insert the new products looks like this:

use tempdb
GO
INSERT Product ([Name], ProductNumber, ListPrice)
SELECT 
    [Name], ProductNumber, ListPrice
FROM
    ProductsToInsert I
WHERE
    NOT EXISTS (SELECT 1 
                FROM Product 
                WHERE ProductNumber = I.ProductNumber)

That inserts the 54 products that weren't in the table previously. We can use the OUTPUT clause to return back the rows that were inserted. That looks like this:

INSERT Product ([Name], ProductNumber, ListPrice)
    OUTPUT inserted.ProductID,
           inserted.[Name],
           inserted.ProductNumber,
           inserted.ListPrice
SELECT 
 [Name], ProductNumber, ListPrice
FROM
 ProductsToInsert I
WHERE
 NOT EXISTS (SELECT 1 FROM Product 
    WHERE ProductNumber = I.ProductNumber)

When that statement is run it returns the following recordset back to the client:

ProductID Name                                ProductNumber         ListPrice
----------- ----------------------------------- --------------------- -------------
        451 LL Bottom Bracket                   BB-7421                       53.99
        452 ML Bottom Bracket                   BB-8107                      101.24
        453 HL Bottom Bracket                   BB-9108                      121.49

. . . 

        504 HL Touring Handlebars               HB-T928                       91.57

(54 row(s) affected)

That is almost what we want. We have the identity values in the result set but we don't have a way to work with the data and we don't have a way to tie it back to the original source row. We'll need to add two things. First we'll need to store this result set in a table variable. I'll also remove a few columns to make it easier to read and add an update statement to save the identity value. That script looks like this:

DECLARE @InsertedRows TABLE (ProductID INT, ProductNumber NVARCHAR(25) )

INSERT Product ([Name], ProductNumber, ListPrice)
    OUTPUT inserted.ProductID,
           inserted.ProductNumber
    INTO @InsertedRows
SELECT 
 [Name], ProductNumber, ListPrice
FROM
 ProductsToInsert AS I
WHERE
 NOT EXISTS (SELECT 1 FROM Product 
    WHERE ProductNumber = I.ProductNumber)

UPDATE  ProductsToInsert
SET     InsertedIdentityValue = T.ProductID
FROM    ProductsToInsert I
JOIN    @InsertedRows T ON T.ProductNumber = I.ProductNumber

SELECT  RowID, ProductNumber, InsertedIdentityValue
FROM    ProductsToInsert
WHERE   InsertedIdentityValue IS NOT NULL

We declare a table variable to store the results of the OUTPUT clause. We use the OUTPUT INTO syntax to store the results into the table variable. Next we use the table variable to update the source table with the inserted identity columns. After that a simple SELECT statement returns the new values which we can easily use in other statements:

RowID ProductNumber             InsertedIdentityValue
----------- ------------------------- ---------------------
        451 HB-T721                                     503
        452 HB-T928                                     504
        453 FB-9873                                     502

. . . 

        504 BK-R19B-52                                  470

(54 row(s) affected)

The OUTPUT clause can also be used with UPDATE and DELETE statements and return values from either the inserted or deleted table. In its simplest form the OUTPUT clause greatly simplifies importing data into SQL Server.

Creative Ways to Use the TOP Clause

2009-12-09T22:08:00.000-08:00

Have you ever wanted to return the last 100 rows of a given set of records? What about the second set of 100 records? Or possibly, you have wanted to return the first so many billing records for each billing month. This article will discuss how to use the TOP clause to help solve those requests where you want to restrict the number of records returned based on a record count. But first I will discuss the new features incorporated into the TOP clause with SQL Server 2005.
New Features in TOP Clause Implemented with SQL Server 2005
SQL Server 2005 brought a couple of new features to the TOP clause. The syntax of the TOP clause in SQL Server 2005 now looks like this:

TOP (expression) [PERCENT]
     [ WITH TIES ]

In SQL Server 2005 you are allowed to specify the number of rows you want to return with an expression, where as in prior versions you where only allowed to hard code a number. Being able to specify a variable allows you to more easily control the number of rows returned programmatically.
The second new feature is the “WITH TIES” options. This option instructs SQL Server to bring back additional rows should the last row have the same order by value as the next row. This option is only valid in a SELECT statement that contains an ORDER BY clause.
To demonstrate how to use these new TOP clause features let me go through a couple of examples.

Sample Data for Examples

Prior to going through my examples, I will need a table with some test data. Below is my sample data and a script to populate my sample TopDemo table:

PaymentID	Amount	PayDate
1	12.23	1/2/2006
2	73.18	1/10/2006
3	92.20	1/21/2006
4	62.20	1/21/2006
5	12.45	1/23/2006
6	10.99	2/4/2006
7	34.23	2/7/2006
8	34.87	2/12/2006
9	58.32	2/15/2006
10	34.23	2/23/2006
11	98.26	3/1/2006
12	45.32	3/15/2006
13	47.30	3/16/2006
14	35.21	3/24/2006
15	57.23	3/26/2006
16	24.56	3/30/2006
17	34.23	4/4/2006
18	23.99	4/6/2006
19	56.32	4/8/2006
20	47.53	6/25/2006
21	30.56	7/7/2006
22	65.34	7/8/2006
23	44.55	8/4/2006
24	84.23	8/8/2006
25	23.56	8/22/2006
26	45.77	9/12/2006
27	65.90	9/25/2006

set nocount on
create table TopDemo (
PaymentID int identity, 
Amount money, 
PayDate DateTime
)
insert into TopDemo(Amount, PayDate) values (12.23,'1/2/2006')
insert into TopDemo(Amount, PayDate) values (73.18,'1/10/2006')
insert into TopDemo(Amount, PayDate) values (92.20,'1/21/2006')
insert into TopDemo(Amount, PayDate) values (62.20,'1/21/2006')
insert into TopDemo(Amount, PayDate) values (12.45,'1/23/2006')
insert into TopDemo(Amount, PayDate) values (10.99,'2/4/2006')
insert into TopDemo(Amount, PayDate) values (34.23,'2/7/2006')
insert into TopDemo(Amount, PayDate) values (34.87,'2/12/2006')
insert into TopDemo(Amount, PayDate) values (58.32,'2/15/2006')
insert into TopDemo(Amount, PayDate) values (34.23,'2/23/2006')
insert into TopDemo(Amount, PayDate) values (98.26,'3/1/2006')
insert into TopDemo(Amount, PayDate) values (45.32,'3/15/2006')
insert into TopDemo(Amount, PayDate) values (47.30,'3/16/2006')
insert into TopDemo(Amount, PayDate) values (35.21,'3/24/2006')
insert into TopDemo(Amount, PayDate) values (57.23,'3/26/2006')
insert into TopDemo(Amount, PayDate) values (24.56,'3/30/2006')
insert into TopDemo(Amount, PayDate) values (34.23,'4/4/2006')
insert into TopDemo(Amount, PayDate) values (23.99,'4/6/2006')
insert into TopDemo(Amount, PayDate) values (56.32,'4/8/2006')
insert into TopDemo(Amount, PayDate) values (47.53,'6/25/2006')
insert into TopDemo(Amount, PayDate) values (30.56,'7/7/2006')
insert into TopDemo(Amount, PayDate) values (65.34,'7/8/2006')
insert into TopDemo(Amount, PayDate) values (44.55,'8/4/2006')
insert into TopDemo(Amount, PayDate) values (84.23,'8/8/2006')
insert into TopDemo(Amount, PayDate) values (23.56,'8/22/2006')
insert into TopDemo(Amount, PayDate) values (45.77,'9/12/2006')
insert into TopDemo(Amount, PayDate) values (65.90,'9/25/2006')

Using an EXPRESSION in the TOP Clause

SQL Server 2005 makes it easier to programmatically control the number of records you want to return when you use the TOP clause. With these changes to the TOP clause, you can now use a variable to identify the number of rows to return. Below is an example that returns the top 3 records from my TopDemo table:

declare @top int
set @top = 3
select top (@top) * from TopDemo

Here I defined an integer variable @top and set its value to 3. I then used that variable in the SELECT statement immediately following the TOP clause and enclosed it in parentheses. When the SQL Server 2005 engine executes this command, it evaluates the value of the variable @top to determine the number of rows to return. In my example, I return the following 3 records:

PaymentID   Amount                PayDate
----------- --------------------- -----------------------
1           12.23                 2006-01-02 00:00:00.000
2           73.18                 2006-01-10 00:00:00.000
3           92.20                 2006-01-21 00:00:00.000

Now if you have some code that determines the number of records you want to return, it is a simple matter of just getting that record number into a variable, then placing the variable into a TOP clause to constrain your returned record set to a specific number of records. Being able to dynamically control the number of rows returned in SQL Server 2005 means you no longer have to using dynamic SQL to accomplish this task.

Using the WITH TIES option

SQL Server 2005 also offers a WITH TIES option. This option allows you to specify that you want to return all the records that have the same value as last records returned by the TOP clause, based on the ORDER BY clause. Here is an example that demonstrates the WITH TIES option:

select top 3 with ties * from TopDemo 
order by PayDate

Here I am specifying that I want to return the top 3 records and any additional records that have the same PayDate as the third record. When I run this query this is my result set:

PaymentID   Amount                PayDate
----------- --------------------- -----------------------
1           12.23                 2006-01-02 00:00:00.000
2           73.18                 2006-01-10 00:00:00.000
3           92.20                 2006-01-21 00:00:00.000
4           62.20                 2006-01-21 00:00:00.000

As you can see, 4 records were returned. This is because the third and forth record have the same PayDate. So now, if you want to return all the records that have the same ORDER BY value, but still what to loosely constrain your returned set to a number of records, then the WITH TIES option should help.

Returning the Second Set of 4 Records

Some times, you don’t want to return the top set of so many records, but you want to return the next set of so many records. Here is an example that returns the second set of 4 records:

select top 4 * from TopDemo a 
 where PaymentID in (select top 8 PaymentID from TopDemo) 
    order by PaymentID desc

In order to return the second set of 4 records I used a correlated subquery.
The correlated subquery returns “PaymentID” column values for the top 8 records. I then return only the first 4 matching TopDemo records that have one of the 8 PaymentID’s returned from the subquery. Since I requested my final set to be to be sorted in descending order by the PaymentID I will get the second set of 4 records. Here is the result set from the above query:

PaymentID   Amount                PayDate
----------- --------------------- -----------------------
8           34.87                 2006-02-12 00:00:00.000
7           34.23                 2006-02-07 00:00:00.000
6           10.99                 2006-02-04 00:00:00.000
5           12.45                 2006-01-23 00:00:00.000

As you can see, this set is ordered in descending sequence by PaymentID. If you really wanted the payments to be sorted sequentially by PaymentID, starting with the lowest PaymentID first, then you would have to run the following T-SQL statement, which sorts the final results set in ascending order by PaymentID:

select * from 
  (select top 4 * from TopDemo a 
   where PaymentID in (select top 8 PaymentID from TopDemo) 
   order by PaymentID desc) a
order by PaymentID

Here I just placed an alias named “a” around my original query’s result set, so I could re-sort my four records into PaymentID order.

How to get the BOTTOM 5 records

SQL Server does not supply a BOTTOM clause, but that doesn’t mean you can’t easily get the BOTTOM so many records. It is relatively simple to use the TOP clause to retrieve the bottom “x” number of records. Here is an example where I retrieved the bottom 5 records based on PayDate from my TopDemo table.

select top 5 * from TopDemo 
order by PayDate desc

In order to return the bottom five records all I had to do was specify the descending clause on my order by statement. This returned the bottom 5 records based on PayDate, although the final record set returned is ordered in descending order by PayDate. Below is the result set returned when I run the above query:

PaymentID   Amount                PayDate
----------- --------------------- -----------------------
27          65.90                 2006-09-25 00:00:00.000
26          45.77                 2006-09-12 00:00:00.000
25          23.56                 2006-08-22 00:00:00.000
24          84.23                 2006-08-08 00:00:00.000
23          44.55                 2006-08-04 00:00:00.000

Now normally you probably would want to return the bottom so many records and have those records be ordered based on a key in ascending order, instead of having them sort like my above results set where the PayDate is in descending order. This can be solved by wrapping another query around the above query, like so:

select * from (select top 5 * from TopDemo 
order by PayDate desc) a
order by PayDate

The above query will order my final result set in ascending PayDate order. Below is the result set of the above query:

PaymentID   Amount                PayDate
----------- --------------------- -----------------------
23          44.55                 2006-08-04 00:00:00.000
24          84.23                 2006-08-08 00:00:00.000
25          23.56                 2006-08-22 00:00:00.000
26          45.77                 2006-09-12 00:00:00.000
27          65.90                 2006-09-25 00:00:00.000

How to get the First Record of Each Month

For my last example, I will show you how you can use the TOP clause to return the first TopDemo payment record for each PayDate month. To accomplish this I will once again use a correlated subquery to identify the top payment record for each month, as the code below demonstrates:

select * from TopDemo a 
 where PayDate = (select top 1 PayDate from TopDemo 
    where month(PayDate) = Month(a.PayDate)
    order by PayDate) 
order by PayDate

Here the inner query (“select top 1.....”) a correlated subquery takes the month identification from the outer query to select the first PayDate for the that specific month. The first PayDate of the month is then used in the WHERE clause to return the first payment for each month. Below is the result set from the above correlated subquery:

PaymentID   Amount                PayDate
----------- --------------------- -----------------------
1           12.23                 2006-01-02 00:00:00.000
6           10.99                 2006-02-04 00:00:00.000
11          98.26                 2006-03-01 00:00:00.000
17          34.23                 2006-04-04 00:00:00.000
20          47.53                 2006-06-25 00:00:00.000
21          30.56                 2006-07-07 00:00:00.000
23          44.55                 2006-08-04 00:00:00.000
26          45.77                 2006-09-12 00:00:00.000

In my T-SQL code above, the inner query returns 8 different PayDate’s, one for the first payment date of each month that existed in my TopDemo table. These unique payment dates were then used in the outer query to return any TopDemo records that had one of those 8 payment dates.

Conclusion

The TOP clause is useful in helping you constrain your set to specific number of records. The new SQL Server 2005 features of the TOP clause now provides you with some alternatives for how to write your code for some specific data retrieve criteria. Being able to dynamically control the number of records returned is a big help when you don’t know exactly how many records you want to return. Now you can use the WITH TIES option as an easy method to return those records that have the same value as the last record in your TOP criteria.

Dealing With Upper and Lower Case Data

2009-12-09T04:07:00.000-08:00

When working with data in SQL Server you might run across different needs to change the case of the data. For instance, you might want to convert all the characters in a varchar column to upper case, lower case, or change only the first character of a column value to upper case. In addition, you might want to compare different character strings and either have them be equal or not equal when the characters are the same but the case is different. In this article, I will show you different examples of how to deal with different situations related to the case of character strings.

What is Collation?

Each server, database, or character column has a default collation. A collation is a definition for how each character is represented, stored and compared. Part of the collation setting, known as case sensitivity, determines if an upper case “A” and a low case “a” when compared against either other are the same or different. If the collation is case-insensitive then a lower case “a” and an upper case “A” when compared are the same. So when using case insensitive data the following two strings are equal, “AaAaA” and “aaaaa”. On the other hand, if your collation setting is case-sensitive then the preceding two strings of A’s will not be equal. Therefore, when you are working with data you need to understand what the case sensitivity is for the character data you are dealing with.

UPPER and LOWER Function

Within SQL Server, two functions are provided to manage the case of character strings. These functions are UPPER and LOWER. The UPPER function accepts a character string as a parameter and returns the same character string where all the characters have been converted to upper case. If the character string “AbCd1234” is passed to the UPPER function, then this function returns “ABCD1234”. The LOWER function does just the opposite and converts all characters it receives to lower case, so if “AbCd1234” is passed to the LOWER function then it would return “abcd1234”.

Converting the First Character of Each Column to Upper Case

For my first example let me show you how to convert the first character of each column value to upper case and lower casing the rest of the column. Here is the T-SQL code for my example:

SET NOCOUNT ON 
IF exists (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[mytable]') 
 and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [mytable]
GO
CREATE TABLE [mytable] (
A CHAR(10))
go
INSERT INTO mytable VALUES('aAaA')
INSERT INTO mytable VALUES('bBbB')
INSERT INTO mytable VALUES('cCcC')
INSERT INTO mytable VALUES('dDdD')
INSERT INTO mytable VALUES('DDdD')
-- Uppercase first letter of each value in column
SELECT UPPER(SUBSTRING(A,1,1)) + LOWER(SUBSTRING(A,2,LEN(A))) 
 [Upper Case First Letter Only] 
FROM mytable
DROP TABLE mytable

In this example, I created a table “mytable” that contained a single column. I then insert five different records into this table. The column in each row contains a character string that has a mixed set of cases. In the SELECT statement above, I use the substring function to get the first character of column “A”. I then pass that first character to the UPPER function. This upper cases the first character of column “A”. I then use the concatenation operator (+) to append the rest of column “A” to that upper cased first character. Before the rest of column “A” is concatenated to the first character, it is lower cased by using the LOWER function on the substring of column “A” starting at second character of column “A”. Here is the output I get when I run the above script:

Upper Case First Letter Only
----------------------------
Aaaa 
Bbbb 
Cccc 
Dddd 
Dddd

Proper Casing a Persons Name

In this example, I will take a column that contains a persons name and proper case the name. By proper case I mean I will take the first character of each name (First, Middle, Last, etc.) and upper case it and then lower case the rest of each name.
The bulk of my proper case example is coded in a T-SQL function. The function handles proper casing any number of names. It also handles multiple spaces between names, Irish type names, and with apostrophe or parentheses within in the name. Here is the code for my proper case function:

IF OBJECT_ID (N'dbo.ProperCase', N'FN') IS NOT NULL
    DROP FUNCTION dbo.ProperCase
GO
CREATE FUNCTION ProperCase (@String VARCHAR(8000)) RETURNS VARCHAR(8000) AS BEGIN DECLARE @TempString VARCHAR(8000) DECLARE @PS VARCHAR(8000)
SET @PS = ''
-- lower case entire string
SET @TempString = lower(@String)
WHILE patindex('%[-( '']%',@TempString) > 0 BEGIN
  -- Check to see if first character of @TempString is whitespace
  IF (patindex('%[-( '']%',SUBSTRING(@TempString,1,1)) > 0)
  BEGIN
    SET @PS = @PS + SUBSTRING(@TempString,1,1)
  END
  ELSE -- @TempString starts with a Name
  BEGIN
   IF SUBSTRING(@TempString,1,2) = 'mc'
   BEGIN
     SET @PS = @PS + 'Mc'
     SET @TempString = SUBSTRING(@Tempstring,3,LEN(@TempString))
   END
   IF SUBSTRING(@TempString,1,3) = 'mac'
   BEGIN
     SET @PS = @PS + 'Mac'
     SET @TempString = SUBSTRING(@Tempstring,4,LEN(@TempString))
   END
      
    -- upper case first character and return string up to the next space
    SET @PS = @PS + UPPER(SUBSTRING(@TempString,1,1)) +
     SUBSTRING(@TempString,2,patindex('%[-( '']%',@TempString)-1)
    
    
  END
  -- truncation string that we have already processed
  
  SET @TempString = SUBSTRING(@TempString,
     patindex('%[-( '']%',@TempString)+1,LEN(@TempString))
  -- Trim off leading spaces
  SET @TempString = LTRIM(@TempString)
END
IF SUBSTRING(@TempString,1,2) = 'mc'
BEGIN
  SET @PS = @PS + 'Mc'
   SET @TempString = SUBSTRING(@Tempstring,3,LEN(@TempString))
END
IF SUBSTRING(@TempString,1,3) = 'mac'
BEGIN
  SET @PS = @PS + 'Mac'
  SET @TempString = SUBSTRING(@Tempstring,4,LEN(@TempString))
END
-- proper case last word/name
SET @PS = @PS + UPPER(SUBSTRING(@TempString,1,1)) +
SUBSTRING(@TempString,2,LEN(@TempString))
-- check for spaces in front of special characters
SET @PS = Replace(@PS,' -','-')
SET @PS = Replace(@PS,' ''','''')

RETURN (@PS)
END

Here is an example of how to execute my ProperCase function:

CREATE TABLE #temp (
PersonName VARCHAR(100))
INSERT INTO #temp VALUES('GREGory A. LARSEN')
INSERT INTO #temp VALUES('TODD JOShUA   SMITH')
INSERT INTO #temp VALUES('HARRY  JOnes')
INSERT INTO #temp VALUES('Danielle i. RobBins')
INSERT INTO #temp VALUES('mr. john d. johnson')
INSERT INTO #temp VALUES('Jon o''connor') 
INSERT INTO #temp VALUES('mARtiN mCGalpan')
INSERT INTO #temp VALUES('sARah   maCdonald')
INSERT INTO #temp VALUES('maRy   Smith-foster')
INSERT INTO #temp VALUES('Robert (bob)  smith')
SELECT dbo.ProperCase(PersonName) [Proper Case Names] FROM #temp
DROP TABLE #temp

When I run the above code, I get the following output:

Proper Case Names
-------------------------------------
Gregory A. Larsen
Todd Joshua   Smith
Harry  Jones
Danielle I. Robbins
Mr. John D. Johnson
Jon O'Connor
Martin McGalpan
Sarah MacDonald
Mary Smith-Foster
Robert (Bob) Smith

My code above, that calls my proper case function, first creates and populates a temporary table that contains a PersonName column where the names are specified in a mix of upper and lower case characters. In the SELECT statement, I call the ProperCase function and pass it the PersonName column. The ProperCase function takes the passed parameter string and first converts all the characters to lower case using the LOWER function and sets a variable @TempString to the lower case value. The function then processes through the @TempString with a WHILE loop looking for special characters in the name (dash, parentheses, spaces or a quote). Each pass through the WHILE loop, the @TempString is evaluated to determine if the first character is one of these special characters, or is it the first character of a name. If a special character is found the special character is added to the @PS variable. When a new name is found, (first character is not a special character) then the case of the first character is converted to upper case, and then both the first character of the name and the rest of the name are added to the @PS string. The @TempString is shortened to only include the characters after the first space. The WHILE loop continues processing until the @TempString contains no more special character. Once the WHILE loop completes, the final part of the name is then proper cased and added to the @PS variable. The @PS variable is then returned to the calling SELECT statement.
The function makes it easy to implement a process to convert a character string to proper case. Having the proper case code embedded in a function simplifies the code for the SELECT statement to proper case a column.

Comparing Columns with Different Case

As stated earlier the collation of a character string will determine how SQL Server compares character strings. If you store your data using a case-insensitive format then when comparing the character string “AAAA” and “aaaa” they will be equal. If you want to be able to compare two case-insensitive strings and have the comparison be false when the two strings are the same except for the case of the characters then you need to use the COLLATE clause when comparing the two strings. To demonstrate look at the following code:

CREATE TABLE #mytable (
C CHAR(10) COLLATE Latin1_General_CI_AS)
INSERT INTO #mytable VALUES('aaaa')
SELECT C FROM #mytable WHERE C = 'AAAA' 
SELECT C FROM #mytable WHERE C = 'AAAA' COLLATE Latin1_General_CS_AS
DROP TABLE #mytable

When I run this code on my machine, I get the following output.

C
----------
aaaa      
(1 row(s) affected)
C
----------
(0 row(s) affected)

Here you can see the WHERE statement in the first SELECT statement returned the single record from #mytable. The WHERE statement evaluates to true even though the value of column C contains 4 lower case a’s and it was being compared to 4 upper case A’s. This WHERE statement was true because both the column C and the character string “AAAA” have a collation of Latin1_General_CI_AS, which is a case-insensitive collation. A character string is assigned the default collation of the database when the string is not associated with a COLLATE clause. To make sure the 4 upper case A’s don’t match the 4 lower case a’s in column C I need to specify a case-sensitive collation on one side of the equal operator. By specifically casting the character string “AAAA” to a case-sensitive collation using the “COLLATE Latin_General_CS_AS” clause in the second SELECT statement I forced the sting on the right of the equal operator to be case sensitive, which caused “aaaa” to not equal “AAAA”.

Conclusion

This article provided you with some examples of how to physically change the case of some column values and how to set the collation of a character string. Depending on your application’s needs you might need to convert or compare character strings that contain upper and lower case characters. The use of the UPPER and LOWER functions allows you to convert your character strings to whatever case you need. The COLLATE clause helps you explicitly convert a string of characters to the desired collation. When comparing character strings it is important to understand the case-sensitivity of all strings to make sure the outcome of your comparison is what you expect.

How To Store IP Addresses in SQL Server

2009-12-09T03:44:00.000-08:00

What is the best way to store an IP Address in SQL Server

Imagine that you are asked to design a database for a data analysis team to perform web site traffic analysis. This brief will have many requirements and amongst them will be the need to store IP addresses.
Search engines such as Google have a fixed range of IP addresses so the team can easily separate out traffic from true visitors Vs traffic from bots. Although the team have a good rudimentary knowledge of T-SQL the data must be relatively simple to query. So with regard to IP addresses the requirement is as follows:

Store IP addresses efficiently
Allow retrieval of IP addresses in a machine readable format
Allow simple querying on a range or ranges of IP addresses

The data type question

If traffic to your web site is high then the choice of data types is going to be important. You could keep the IP address as a VARCHAR(15) but given the nature of what an IP address actually is, 4 integers in the range 0 to 255, this seems a trifle wasteful.
So what possiblities do we have? Let us consider the ip address 192.168.0.5

Method	Storage	Comment
VARCHAR(15)	Between 7 and 15 bytes	Stores the IP address in human readable but this is wasteful.
BIGINT	8 bytes	We can represent our IP address as 192168000005. It is stretching the definition of human readability somewhat but this depends on your audience.
INT	4 bytes	Our IP address is no longer human readable being represented as 1084751877.
Four separate TINYINT fields	4 bytes	Our address is now both efficient and human readable just as Joe Celko pointed out.

SQL 2005 CLR User Defined Types

SQL2005 provides us with one other possiblity. The .NET assembly user defined type.
I was fortunate to go on the Microsoft "Updating Your Database Development Skills to Microsoft SQL Server 2005" (Course 2734B) which included an IP address UDT. As I am not sure of the copyright issues surrounding the code for the UDT I am not including the source code here but the UDT provided the following functionality.

Accept an ip address in the form nnn.nnn.nnn.nnn
Return the individual bytes of an IP address
Return the string representation of the IP address
Return a varbinary representation of the IP address
Return a string with the PING command and the IP address. I removed this from the code as it was irrelevant.

There are similar functions available on the web.

Test methodology

I decided to create five tables and into each put 1 million rows. Each table would contain a single INT IDENTITY field (IPID)acting as the primary key, plus the chosen method of storing an IP address.

Table	Comment
IPAddressSource	IP Address stored as 4 separate TINY INT fields
IPAddressINT	IP Address stored as a single INT field
IPAddressBIGINT	IP Address stored as a single BIGINT field
IPAddressVARCHAR	IP Address stored as a single VARCHAR(15) field
IPAddressUDT	IP Address stored in a .NET CLR data type

After population I ran an sp_spaceused on each table using the stored procedures described in Automated Monitoring Database Size Using sp_spaceused. For those who caught the article first time around I have added the equivalent procedure for SQL2000 in the discussion forum attached to the article.

Initial results

TableName	Rows	Reserved	Data	IndexSize	Unused
dbo.IPAddressBIGINT	1,000,000	20,872	20,784	88	0
dbo.IPAddressINT	1,000,000	16,904	16,808	72	16
dbo.IPAddressSource	1,000,000	16,904	16,808	72	24
dbo.IPAddressUDT	1,000,000	49,928	49696	192	40
dbo.IPAddressVARCHAR	1,000,000	30,088	29,960	120	8

Straight away we can see that the table using the CLR UDT is by far the worst in terms of storage size taking nearly 50MB to store our million rows.
Running an sp_help reveals why.

Column_name	Type	Computed	Length	...etc
IPID	int	no	4
IPAddress	IPAddress	no	37

There is very little information available on the storage of data in CLR UDTs but what appears to happen is that the instance of the datatype (including structure and data) is serialised and stored in a VARBINARY type format in the field.
This sort of begs the question as to what real-world use are CLR UDTs?
The motivation to try a CLR IPAddress type was that a single type could encapsulate the IP address. The type is atomic.
Storing the data as 4 TINYINT fields means that any one of the four parts of the IP address is meaningless in its own right. It is not atomic.
In this case we can see that there is a heavy overhead in gaining atomicity.

The indexing question

We have established that for readability and efficient storage of data storing an IP address as 4 separate TINYINT fields is the most efficient. But what about searching on IP Addresses?
What I need to be able to do is to identify ranges of IP addresses that correspond to Web Bots. Given the sheer volume of records I am going to need to discover the most efficient method of doing this.
The SQL Query engine decides for itself whether or not there is a benefit in using an index. If the index is not regarded as being selective enough the engine will simply ignore the index.
To give an example I had a table containing details of cars where the primary key was make, model and year.
WHERE make='FORD' resulted in a TABLE SCAN
WHERE make='MASERATI' resulted in an INDEX SEEK
The only difference in the WHERE clause was the value being searched for.
Again if you look at the discussion for Lee Everest's article to make the index as selective as possible Joe Celko suggests that an index be placed across the four TINYINT columns representing the IP Address but from the right-most first, in effect recording the IP address backwards. As the digits in an IP address vary the most in the right hand portion and least in the left hand portion this should make the index as selective as it can possibly be.
My first port of call was to create such an index on my dbo.IPAddressSource table.
The next step was to devise a query that would search for a range of IP addresses such as 72.100.5.25 to 74.50.25.1


/*
    Define parameters to test various ranges of IP addresses
*/

DECLARE
 @LowIP1 TINYINT , @HighIP1 TINYINT ,
 @LowIP2 TINYINT , @HighIP2 TINYINT ,
 @LowIP3 TINYINT , @HighIP3 TINYINT ,
 @LowIP4 TINYINT , @HighIP4 TINYINT 

SELECT 
 @LowIP1 = 72 , @HighIP1 = 74 ,
 @LowIP2 = 100 , @HighIP2 = 50 ,
 @LowIP3 = 5 , @HighIP3 = 25 ,
 @LowIP4 = 25 , @HighIP4 = 1 
 
SELECT * FROM dbo.IPAddressSource
WHERE
 (
  IP1= @LowIP1 
  AND (
   IP2>@LowIP2
   OR (IP2=@LowIP2 AND ((IP3 = @LowIP3 AND IP4>=@LowIP4) OR IP3>@LowIP3))
  )
 )
 OR (IP1>@LowIP1 AND IP1<@HighIP1)
OR
 (
  IP1 = @HighIP1
  AND (
   IP2 < @HighIP2
   OR (IP2=@HighIP2  AND ((IP3=@HighIP3 AND IP4<=@HighIP4) OR IP3<@HighIP3))
 )
)

An alternative approach

We have established that storing an IP address as 4 TINYINT fields achieves two objectives

Storing the IP address in an efficient format
Storing the IP address in a format that is easily read by a human being

The problem areas are as follows

Searching for a range of addresses requires convoluted WHERE conditions
The query is expensive

As an alternative experiment I decided to create a new table called dbo.IPAddressCALC as follows
IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='IPAddressCalc') CREATE TABLE dbo.IPAddressCalc( IPID int NOT NULL, IP1 tinyint NOT NULL, IP2 tinyint NOT NULL, IP3 tinyint NOT NULL, IP4 tinyint NOT NULL, IPAddress AS CAST( ((CAST(IP1 AS BIGINT)* 16777216) + ( CAST(IP2 AS BIGINT)* 65536) + ( CAST(IP3 AS BIGINT)*256) + IP4) -2147483648 AS INT), CONSTRAINT PK_IPAddressCalc PRIMARY KEY CLUSTERED ( IPID ASC) ) GO Our IPAddress field is a calculated field using the bitmasking technique and contains an INT value.
To aid our search I placed an index on this column. To search this column now our query would become
SELECT * FROM dbo.IPAddressCalc WHERE IPAddress BETWEEN @Low AND @High We would probably create function to return the values for @Low and @High from supplied IP addresses.
Our WHERE clause is much simpler and it is now easy to write a query that can handle multiple ranges of ip addresses.

Wrapping it all up

The final step is to example sp_spaceused results for our two tables. We are interested in the amount of data and index space taken up by each item.

TableName	Rows	Reserved	Data	IndexSize	Unused
dbo.IPAddressCalc	1,000,000	30,864	16,808	13,952	104
dbo.IPAddressSource	1,000,000	27,856	16,808	10,960	88

Adding a calculated column has no affect on the data held within the table, however, over 1 million rows the index takes up just under 3MB more space.
The final note to add is that our analysis tables would be populated using some form of bulk loading process of the previous day's results. Placing an index on a calculated field of a highly dynamic table could potentially cause performance problems.

Addendum

To develop this solution I generated 1 million random IP addresses.
For those of us who have cursed the SQL Server RAND function my population query was as follows

IF NOT EXISTS(SELECT 1 FROM dbo.IPAddressSource)
 BEGIN
  SET ROWCOUNT 1000000
  INSERT INTO dbo.IPAddressSource(IP1, IP2, IP3, IP4)
  SELECT 
   FLOOR(256*RAND(cast(cast(left(newid(),8) as varbinary  ) as int )) ),
   FLOOR(256*RAND(cast(cast(left(newid(),8) as varbinary  ) as int )) ),
   FLOOR(256*RAND(cast(cast(left(newid(),8) as varbinary  ) as int )) ),
   FLOOR(256*RAND(cast(cast(left(newid(),8) as varbinary  ) as int )) )

  FROM master.dbo.sysobjects AS O1 ,
  master.dbo.sysobjects AS O2 

  SET ROWCOUNT 0
 END

The method works because NEWID() returns a number that is supposed to be unique in time and space and consists fo a consistant format containing hexadecimal strings.
What I am doing is taking the first block of 8 characters, converting the resulting string to a VARBINARY value and this resulting value is then converted into an integer value.
I don't actually care what the integer value is as I am simply feeding it into the RAND function as a seed.

As we can see the WHERE clause is a bit convoluted and this is just searching for one range of ip addresses.

Keep a check on your IDENTITY columns in SQL Server

2009-12-09T01:35:00.000-08:00

The IDENTITY columns, or 'auto number' columns as some people call them, are auto incrementing columns provided by SQL Server. There can only be one IDENTITY column per table. You just have to provide a base value, and an increment value, and SQL Server will take care of incrementing this column automatically. Some people like these, and some don't, but the truth is, IDENTITY columns are gaining popularity, and many production systems, including critical ones are using IDENTITY columns these days. So, it is important to keep an eye on these columns, to make sure they are not reaching the limit of their base data type. For example, if you created an IDENTITY column of smallint datatype, its values can go upto 32767. If you try to insert anymore rows, you will get the following error:

Server: Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting IDENTITY to data type smallint.
Arithmetic overflow occurred.

If this table happens to be a part of a critical production system, then you are in trouble. You will have to do something about it to resolve it. If the data from this table can be deleted, then delete the data using TRUNCATE TABLE command. TRUNCATE TABLE resets the IDENTITY column to its base value. The DELETE command doesn't do this. But then, if this table is referenced by a foreign key, then TRUNCATE TABLE is not allowed on this table. Your other option is to run DBCC CHECKIDENT on your table with RESEED option.

An IDENTITY column of tinyint datatype can go upto 255, smallint can go upto 32767, int can go upto 2147483647 and bigint can go upto 9223372036854775807.

You can proactively monitor these IDENTITY columns, to avoid getting into such problems. If you can see in advance, that an IDENTITY column is reaching its limit, then you could do something about it, before it reaches the limit. The other day, one of my friends was trying to automate a process that checks all the IDENTITY columns in a database and reports on how far away those columns are from the limit. He was using a cursor to go through all the tables in the database, and running a "SELECT MAX(IdentityCol) FROM TableName" on all the tables that have an IDENTITY column. It would take ages to run on a database with many big tables. It can be simplified into one simple query using IDENT_CURRENT function. That's what I did, and thought it will be useful for other DBAs as well. So, here I am writing about it.

There are three different versions of this procedure. First one is for SQL Server 2005. The second and third versions work in SQL Server 2000. You will have to create this procedure in the database of your interest. And run it as shown below:

EXEC dbo.CheckIdentities
GO

This procedure below, displays information about all IDENTITY columns in the database, and shows you the percentage of IDENTITY values already used. If you are seeing any IDENTITY columns that have used up 80% or more values, then you need to start thinking about it. You could customise this procedure to automatically email you or log an error if there are any IDENTITY columns that are nearing the limit. You could also schedule this procedure as an SQL Agent job, so that it checks these columns regularly. Any new IDENTITY columns added to the database will automatically get picked up by this query.

A quick note about 64 bit SQL Server. Even though SQL Server 64 bit editions can access a lot more memory inherently, than the 32 bit systems could - the IDENTITY columns are still limited to the limits imposed by the base datatypes. I'm writing this because, someone recently asked me if int data type can store a higher number in 64 bit server, compared to a 32 bit server.

Here's a screen shot of output from the AdventureWorks sample database in SQL Server 2005.


/* The SQL Server 2005 version of the stored procedure. It uses new catalog views */


CREATE PROC dbo.CheckIdentities
AS
BEGIN
 SET NOCOUNT ON

 SELECT QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' +  QUOTENAME(t.name) AS TableName, 
  c.name AS ColumnName,
  CASE c.system_type_id
   WHEN 127 THEN 'bigint'
   WHEN 56 THEN 'int'
   WHEN 52 THEN 'smallint'
   WHEN 48 THEN 'tinyint'
  END AS 'DataType',
  IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + '.' + t.name) AS CurrentIdentityValue,
  CASE c.system_type_id
   WHEN 127 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + '.' + t.name) * 100.) / 9223372036854775807
   WHEN 56 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + '.' + t.name) * 100.) / 2147483647
   WHEN 52 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + '.' + t.name) * 100.) / 32767
   WHEN 48 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + '.' + t.name) * 100.) / 255
  END AS 'PercentageUsed' 
 FROM sys.columns AS c 
  INNER JOIN
  sys.tables AS t 
  ON t.[object_id] = c.[object_id]
 WHERE c.is_identity = 1
 ORDER BY PercentageUsed DESC
END



If you try to create the above stored procedure in SQL Server 2000, you will get the following error:


Server: Msg 195, Level 15, State 10, Procedure a, Line 4
'SCHEMA_NAME' is not a recognized function name.

So, here are some SQL Server 2000 compatible versions.


/* The SQL Server 2000 version of the stored procedure. Uses system tables. This should work in SQL Server 7.0 too */


CREATE PROC dbo.CheckIdentities
AS
BEGIN
 SET NOCOUNT ON

 SELECT QUOTENAME(USER_NAME(t.uid))+ '.' +  QUOTENAME(t.name) AS TableName, 
  c.name AS ColumnName,
  CASE c.xtype
   WHEN 127 THEN 'bigint'
   WHEN 56 THEN 'int'
   WHEN 52 THEN 'smallint'
   WHEN 48 THEN 'tinyint'
  END AS 'DataType',
  IDENT_CURRENT(USER_NAME(t.uid)  + '.' + t.name) AS CurrentIdentityValue,
  CASE c.xtype
   WHEN 127 THEN (IDENT_CURRENT(USER_NAME(t.uid)  + '.' + t.name) * 100.) / 9223372036854775807
   WHEN 56 THEN (IDENT_CURRENT(USER_NAME(t.uid)  + '.' + t.name) * 100.) / 2147483647
   WHEN 52 THEN (IDENT_CURRENT(USER_NAME(t.uid)  + '.' + t.name) * 100.) / 32767
   WHEN 48 THEN (IDENT_CURRENT(USER_NAME(t.uid)  + '.' + t.name) * 100.) / 255
  END AS 'PercentageUsed' 
 FROM syscolumns AS c 
  INNER JOIN
  sysobjects AS t 
  ON t.id = c.id
 WHERE COLUMNPROPERTY(t.id, c.name, 'isIdentity') = 1
 AND OBJECTPROPERTY(t.id, 'isTable') = 1
 ORDER BY PercentageUsed DESC
END



/* The SQL Server 2000 version of the stored procedure. Uses INFORMATION_SCHEMA views. */


CREATE PROC dbo.CheckIdentities
AS
BEGIN
 SET NOCOUNT ON

 SELECT QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME)  AS TableName, 
  c.COLUMN_NAME AS ColumnName,
  c.DATA_TYPE AS 'DataType',
  IDENT_CURRENT(t.TABLE_SCHEMA  + '.' + t.TABLE_NAME) AS CurrentIdentityValue,
  CASE c.DATA_TYPE
   WHEN 'bigint' THEN (IDENT_CURRENT(t.TABLE_SCHEMA  + '.' + t.TABLE_NAME) * 100.) / 9223372036854775807
   WHEN 'int' THEN (IDENT_CURRENT(t.TABLE_SCHEMA  + '.' + t.TABLE_NAME) * 100.) / 2147483647
   WHEN 'smallint' THEN (IDENT_CURRENT(t.TABLE_SCHEMA  + '.' + t.TABLE_NAME) * 100.) / 32767
   WHEN 'tinyint' THEN (IDENT_CURRENT(t.TABLE_SCHEMA  + '.' + t.TABLE_NAME) * 100.) / 255
  END AS 'PercentageUsed' 
 FROM INFORMATION_SCHEMA.COLUMNS AS c 
  INNER JOIN
  INFORMATION_SCHEMA.TABLES AS t 
  ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
 WHERE COLUMNPROPERTY(OBJECT_ID(t.TABLE_SCHEMA + '.' + t.TABLE_NAME), c.COLUMN_NAME, 'isIdentity') = 1
 AND c.DATA_TYPE IN ('bigint', 'int', 'smallint', 'tinyint')
 AND t.TABLE_TYPE = 'BASE TABLE'
 ORDER BY PercentageUsed DESC
END

How to Use GROUP BY in SQL Server

2009-12-08T07:12:00.000-08:00

This article has been updated through SQL Server 2005.)
Here's the schema we'll be using along with some sample data:

create table Orders 
(
    OrderID int primary key, 
    Customer varchar(10),
    OrderDate datetime, 
    ShippingCost money
)

create table OrderDetails
(
    DetailID int primary key,
    OrderID int references Orders(OrderID),
    Item varchar(10),
    Amount money
)

go

insert into Orders
select 1,'ABC', '2007-01-01', 40 union all
select 2,'ABC', '2007-01-02', 30 union all
select 3,'ABC', '2007-01-03', 25 union all
select 4,'DEF', '2007-01-02', 10

insert into OrderDetails
select 1, 1, 'Item A', 100 union all
select 2, 1, 'Item B', 150 union all
select 3, 2, 'Item C', 125 union all
select 4, 2, 'Item B', 50 union all
select 5, 2, 'Item H', 200 union all
select 6, 3, 'Item X', 100 union all
select 7, 4, 'Item Y', 50 union all
select 8, 4, 'Item Z', 300

Determining the Virtual Primary Key of a Result Set

Let's examine our sample data by joining these tables together to return Orders along with the OrderDetails:

select 
  o.orderID, o.customer, o.orderdate, o.shippingCost, od.DetailID, od.Item, od.Amount
from 
  orders o
inner join 
  OrderDetails od on o.OrderID = od.OrderID

orderID     customer   orderdate      shippingCost  DetailID    Item       Amount 
----------- ---------- -------------- ------------- ---------   ---------- ----------
1           ABC        2007-01-01     40.0000       1           Item A     100.0000
1           ABC        2007-01-01     40.0000       2           Item B     150.0000
2           ABC        2007-01-02     30.0000       3           Item C     125.0000
2           ABC        2007-01-02     30.0000       4           Item B     50.0000
2           ABC        2007-01-02     30.0000       5           Item H     200.0000
3           ABC        2007-01-03     25.0000       6           Item X     100.0000
4           DEF        2007-01-02     10.0000       7           Item Y     50.0000
4           DEF        2007-01-02     10.0000       8           Item Z     300.0000

(8 row(s) affected)

Remember that Orders has a one-to-many or parent/child relationship with OrderDetails: thus, one order can have many details. When we join them together, the Order columns are repeated over and over for each OrderDetail. This is normal, standard SQL behavior and what happens when you join tables in a one-to-many relation. Our result contains one row per OrderDetail, and those OrderDetail rows are never repeated, since it is not being joined to any further tables that will produce duplicate rows.
Thus, we could say that our result set has a virtual primary key of DetailID; there will never be a duplicate OrderDetail row in the data. We can count and add up all OrderDetail columns and never worry about double counting values. However, we can not do the same for the Orders table, since its rows are duplicated in the results. Remember this as we move forward.

A Typical Summary Report Example

Here is a good example using Orders and OrderDetails that demonstrates various aggregate functions and typical things to look for:
For each Customer, we want to return the total number of orders, the number of items ordered, the total order amount, and the total shipping cost.
All of this information lives in the Orders and the OrderDetails tables, and we know how to join them together, we just need to summarize those results now. We don't want to return all 8 rows and see all of the details; we just want to return 2 rows: one per customer, with the corresponding summary calculations.

Grouping and Summarizing Primary Rows

Since we want to return 1 row for each Customer, we can simply add GROUP BY Customer to the end of the SELECT. We can return the Customer column since we are grouping on it, and we can add any other columns as long as they are summarized in an aggregate function. We can also use the COUNT(*) aggregate function to return the number of rows per group. Since each row in our data corresponds to an order item, grouping by Customer and selecting Customer and COUNT(*) will return the number of OrderDetails per Customer:

select 
  o.Customer, count(*) as ItemCount

from 
  Orders o
inner join 
  OrderDetails od on o.OrderID = od.OrderID
group by 
  o.Customer

Customer   ItemCount   
---------- ----------- 
ABC        6
DEF        2

(2 row(s) affected)

Next, let’s add the total order amount per customer. Since our join resulted in one row per item detail (remember: when joining Orders to OrderDetails, the result set has a virtual primary key of DetailID) we know that no rows in the OrderDetails table were duplicated in our results. Therefore, a simple SUM() of the Amount column from the OrderDetails table is all we need to return the total order amount per customer:

select 
  o.Customer, count(*) as ItemCount, sum(od.Amount) as OrderAmount

from 
  Orders o
inner join 
  OrderDetails od on o.OrderID = od.OrderID
group by 
  o.Customer

Customer   ItemCount   OrderAmount           
---------- ----------- --------------------- 
ABC        6           725.0000
DEF        2           350.0000

(2 row(s) affected)

So far, so good! Only two more calculations to go: total orders per customer, and total shipping cost. Both of these values come from the Orders table.
If you recall, our join resulted in one row per OrderDetail, and that meant that the Orders table had duplicate rows. We need to return two calculations from our Orders table -- total number of Orders, and total Shipping cost. We already know that COUNT(*) returns the number of OrderDetails, so that won’t work for us. Perhaps COUNT(OrderID) will return the total number of orders? Let's try it:

select 
  o.Customer, count(*) as ItemCount, sum(od.Amount) as OrderAmount, 
  count(o.OrderID) as OrderCount
from 
  Orders o
inner join 
  OrderDetails od on o.OrderID = od.OrderID
group by 
  o.Customer

Customer   ItemCount   OrderAmount           OrderCount  
---------- ----------- --------------------- ----------- 
ABC        6           725.0000              6           
DEF        2           350.0000              2           

(2 row(s) affected)

Notice that the OrderCount column returned is the same as the ItemCount, and definitely not the number of orders per customer. That is because, by definition in SQL, COUNT(expression) just returns the total number of rows in which that expression is not null. Since OrderID is never null, it returns the total row count per Customer, and since each row corresponds with an OrderDetail item, we get the number of OrderDetail items per customer, not the number of Orders per customer.

Using COUNT(Distinct)

Luckily, there is a DISTINCT feature that we can use in our COUNT() aggregate function that will help us here. Count(Distinct expression) means: " return the total number of distinct values for the specified expression." So, if we write COUNT(Distinct OrderID), it will return the distinct number of OrderID values per Customer. Since each OrderID value corresponds to an Order (it is the primary key of the Orders table), we can use this to calculate our Order count:

select 
  o.Customer, count(*) as ItemCount, sum(od.Amount) as OrderAmount, 
  count(distinct o.OrderID) as OrderCount
from 
   Orders o
inner join 
  OrderDetails od on o.OrderID = od.OrderID
group by 
  o.Customer

Customer   ItemCount   OrderAmount           OrderCount  
---------- ----------- --------------------- ----------- 
ABC        6           725.0000              3
DEF        2           350.0000              1

(2 row(s) affected)

Great! Looks good, makes sense, now we are getting somewhere.

Beware of SUMMING Duplicate Values

Moving on, let’s add an expression to calculate total shipping cost per customer. Well, we know we have a ShippingCost column in our data from the Orders table, so let's try just adding SUM(ShippingCost) to our SELECT:

select 
  o.Customer, count(*) as ItemCount, sum(od.Amount) as OrderAmount, 
  count(distinct o.OrderID) as OrderCount, sum(o.ShippingCost) as TotalShipping
from 
  Orders o
inner join 
  OrderDetails od on o.OrderID = od.OrderID
group by 
  o.Customer

Customer   ItemCount   OrderAmount           OrderCount  TotalShipping         
---------- ----------- --------------------- ----------- --------------------- 
ABC        6           725.0000              3           195.0000
DEF        2           350.0000              1           20.0000

(2 row(s) affected)

Looks like we are good to go, right? Well -- not really, look at our TotalShipping column. Customer ABC has a total shipping of 195. Yet, in our Orders table, we see that the customer has 3 orders, with shipping values of 40,30 and 25. 40+30+25=95, not 195! What is going on here? Well, like the COUNT(*) expression, remember that the SUM() is acting not upon our tables themselves, but the result of the JOIN that we expressed in our SELECT. The JOIN from Orders to OrderDetails meant that rows from the Orders table were duplicated, remember? So, if we SUM() a column in our Orders table when it is joined to the Details, we are summing up duplicate values and our result will be too high. You can verify this by reviewing the results returned by the join from Orders to OrderDetails and manually adding them up by hand.
It is very important to understand this; when writing any JOINS, you need to identify which tables can and cannot have duplicate rows returned. The basic rule is very simple:
If Table A has one-to-many relation with Table B, and Table A is JOINED to Table B, then Table A will have duplicate rows in the result. The final result will have a virtual primary key equal to that of Table B's.
So, we know we can SUM() and COUNT() columns from Table B with no problem, but we cannot do that with columns from Table A because the duplicate values will skew our results.

SQL Server 2008 - The Power of Merge

2009-12-07T21:34:00.000-08:00

Introduction

Here,i had presented a stored procedure which saves a sales order information into Order Header and Order Detail tables. If you have ever worked on an order processing application, you would realize that saving a modified sales order is little tricky. There may be new rows added to the sales order. There may be rows which are updated and there may be rows that should be deleted. If you have the freedom to delete all the rows from order details table and re-insert everything, you are lucky. But many of the times you cannot simply delete the order details table because there may be additional information like Quantity-picked etc, which is updated from other parts of the application. In those scenarios, you need to perform a DELETE-UPDATE-INSERT operation to save the information correctly. The following is the pseudo code that I had used in my stored procedure.

1 /*

2 Pseudo code used for saving sales order information with SQL Server 2005

3

4 -- save order header information

5 If OrderNumber found in OrderHeader

6 Update the information

7 Else

8 Insert the information

9 end

10

11 -- save order detail information

12 Delete from Order detail table all items not in the order info

13 Update Order detail for all items present in the order info

14 Insert into order details all new items in the order info

15 */

SQL Server 2008 introduces MERGE, a new keyword which performs INSERT, UPDATE and DELETE operations at one go. With SQL Server 2008, you can perform the above operation as simple as the following pseudo code.

1 /*

2 Pseudo code for saving the same order with the MERGE statement of SQL Server 2008

3

4 -- save order header information

5 MERGE order info to Order Header table

6

7 -- save order detail information

8 MERGE order info to order detail table

9 */

No, I did not miss anything. You can write the code in just 2 lines. The rest of this article presents a SQL Server 2008 stored procedure which demonstrates this.
The Data

Here is the structure of the order data that we have. This XML has the order header and detail information. Our stored procedure needs to store the information in both tables. Some rows need to be updated, some inserted and some deleted.

We need two tables to store the order information. Here is the script to create the tables.

1 CREATE TABLE [dbo].[OrderHeader](

2 [OrderNumber] [varchar](20) NULL,

3 [CustomerNumber] [varchar](20) NULL,

4 [OrderDate] [datetime] NULL

5 ) ON [PRIMARY]

6

7 GO

8

9 CREATE TABLE [dbo].[OrderDetails](

10 [OrderNumber] [varchar](20) NULL,

11 [ItemNumber] [varchar](20) NULL,

12 [Qty] [int] NULL,

13 [Rate] [money] NULL

14 ) ON [PRIMARY]

15

16 GO
Enter the Dragon

Let us see the Stored Procedure which uses the MERGE keyword.

1 CREATE PROCEDURE [dbo].[MergeSalesOrder]

2 (

3 @OrderInfo XML

4 )

5 AS

6

7 SET NOCOUNT ON

8

9 /*

10 I am not using a TRY..CATCH or BEGIN TRAN to simplify the code.

11 */

12

13 /*

14 Code to save order header. I am creating a CTE over the XML data to simplify

15 the code.

16 */

17

18 ;WITH OrderInfo AS (

19 SELECT

20 x.h.value('@OrderNumber', 'VARCHAR(20)') AS OrderNumber,

21 x.h.value('@CustomerNumber', 'VARCHAR(20)') AS CustomerNumber,

22 x.h.value('@OrderDate', 'VARCHAR(20)') AS OrderDate

23 FROM @OrderInfo.nodes('/OrderInfo/OrderHeader') AS x(h)

24 )

25 MERGE OrderHeader AS h

26 USING OrderInfo AS o

27 ON (h.OrderNumber = o.OrderNumber)

28 WHEN MATCHED THEN

29 UPDATE SET h.CustomerNumber = o.CustomerNumber, h.OrderDate = o.OrderDate

30 WHEN NOT MATCHED THEN

31 INSERT (OrderNumber, CustomerNumber, OrderDate)

32 VALUES (o.OrderNumber, o.CustomerNumber, o.OrderDate)

33 ;

34

35 /*

36 Save Order Detail Information

37 */

38

39 ;WITH OrderInfo AS (

40 SELECT

41 x.h.value('(../../OrderHeader/@OrderNumber)[1]', 'VARCHAR(20)') AS OrderNumber,

42 x.h.value('@ItemNumber', 'VARCHAR(20)') AS ItemNumber,

43 x.h.value('@Qty', 'INT') AS Qty,

44 x.h.value('@Rate', 'MONEY') AS Rate

45 FROM @OrderInfo.nodes('/OrderInfo/ItemInfo/Item') AS x(h)

46 )

47 MERGE OrderDetails AS d

48 USING OrderInfo AS o

49 ON (d.OrderNumber = o.OrderNumber AND d.ItemNumber = o.ItemNumber)

50 WHEN MATCHED THEN

51 UPDATE SET

52 d.ItemNumber = o.ItemNumber,

53 d.Qty = o.Qty,

54 d.Rate = o.Rate

55 WHEN NOT MATCHED THEN

56 INSERT (OrderNumber, ItemNumber, Qty, Rate)

57 VALUES (o.OrderNumber, o.ItemNumber, o.Qty, o.Rate)

58 WHEN SOURCE NOT MATCHED THEN

59 DELETE

60 ;

61

62 /*

63 Points to note:

64 1. MERGE statement should be terminated with a semi colon

65 2. The JOIN (USING...ON) should not result in duplicate records.

66 3. When the records in the SOURCE and TARGET matches, MATCHED becomes true

67 4. When the record is not in the TARGET, NOT MATCHED becomes true

68 5. When the record is not in the SOURCE, SOURCE NOT MATCHED becomes true.

69 */
Execute the code

It is time to execute the code. Use the following code to execute the stored procedure.

1 EXECUTE [MergeSalesOrder] '

15 Let us check the results

16 */

17 SELECT * FROM OrderHeader

18 SELECT * FROM OrderDetails

19

20 /*

21 OrderNumber CustomerNumber OrderDate

22 -------------------- -------------------- -----------------------

23 20070101 J0001 2007-07-08 00:00:00.000

24

25 (1 row(s) affected)

26

27 OrderNumber ItemNumber Qty Rate

28 -------------------- -------------------- ----------- ---------------------

29 20070101 A001 10 100.00

30 20070101 A002 11 200.00

31 20070101 A003 12 300.00

32 20070101 A004 13 400.00

33 20070101 A005 14 500.00

34

35 (5 row(s) affected)

36 */

The above code shows that the order is saved correctly. Now lets us modify the order info. Let us delete a row, add a new row and modify an existing row. Here is the code. Note that Item A005 is deleted. Item A006 is added and item A001 is modified. Let us execute the code and see the results.

1 EXECUTE [MergeSalesOrder] '

2

3

4

5

6

7

8

9

10

11

12 '

13

14 SELECT * FROM OrderHeader

15 SELECT * FROM OrderDetails

16

17 /*

18 OUTPUT:

19

20 OrderNumber CustomerNumber OrderDate

21 -------------------- -------------------- -----------------------

22 20070101 J0001 2007-07-08 00:00:00.000

23

24 (1 row(s) affected)

25

26 OrderNumber ItemNumber Qty Rate

27 -------------------- -------------------- ----------- ---------------------

28 20070101 A001 15 150.00

29 20070101 A002 11 200.00

30 20070101 A003 12 300.00

31 20070101 A004 13 400.00

32 20070101 A006 16 600.00

33

34 (5 row(s) affected)

35

36 */
Conclusions

I found the MERGE keyword very powerful and friendly. It reduces the complexity of the code and provides a simple interface to perform a complex operation. I like it and I suppose many of you around there would like it too.

Working with Datetime

2009-12-07T02:21:00.000-08:00

Working with Datetime
By Leo Peysakhovich, 2007/09/21 (first published: 2004/12/22)
Total article views: 28489 | Views in the last 30 days: 13
Rate this | Join the discussion | Briefcase | Print
Some handy examples of working with datetime

Date / Time data types are probably amongst the most used data ones. Based on the Microsoft definition, date and time data types are for representing date and time of day. Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

The smalldatetime data type stores dates and times of day with less precision than datetime. SQL Server stores smalldatetime values as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight. Dates range from January 1, 1900, through June 6, 2079, with accuracy to the minute.

Let’s consider a case when we need to format values stored in datetime columns . In many cases (especially for the data transfer processes) the user will specify the output format. For example, our users prefer dates to be displayed as 2 digits day, 2 digits month, i.e. ‘01/09/2004’. Usually after the conversion this date displayed as ‘1/9/2004’.As you can see the difference is to be able not suppress leading zeroes in a given field.

A simple trick to achieve this effect is to make your output independent from length of the actual value and to always return the necessary number of characters.

declare @cust table (customer_id int, customer_nm varchar(50))

insert into @cust (customer_id,customer_nm) values (11111,'TestNM 1')
insert into @cust (customer_id,customer_nm) values (6511111,'TestNM 1')
insert into @cust (customer_id,customer_nm) values (92311111,'TestNM 1')

select
Right('0000000000' + Cast(customer_id as varchar),10) as customer_id
, customer_nm
from @cust

customer_id customer_nm
----------- --------------------------------------------------
0000011111 TestNM 1
0006511111 TestNM 1
0092311111 TestNM 1
The same trick can be used for the datetime data

select right( '00' + Cast(datepart(dd, getdate()) as varchar), 2)

select right( ''00' + Cast(datepart(mm, getdate()) as varchar), 2)
Here are some examples of using datetime data type. A transaction/modification very often requires some unique identifier. In light-load transactional systems (1-2 transactions per second) you can produce a unique id based on the timing characteristics of the transaction itself.. I use this method for years and have no problems. Theoretically it is possible that two modifications will get the same id, but the chance of it happening is not significant and can be mitigated with appropriate exception handling.

CREATE FUNCTION dbo.UDF_ID (@dt datetime)
Returns bigint
as
Begin

declare @modid bigint

select @modid = cast(year(@dt) as char(4))+ right(('0' +
cast(month(@dt) as varchar(3) )),2) +
right(('0' + cast( DATEPART(dd,@dt) as varchar(3))),2) +
right(('0' + cast( DATEPART(hh,@dt) as varchar(3))),2) +
right(('0' + cast( DATEPART(mi,@dt) as varchar(3))),2) +
right(('0' + cast( DATEPART(ss,@dt) as varchar(3))),2) +
right(('00' + cast( DATEPART(ms,@dt) as varchar(6))),3)

return @modid

End
There are many clients in the company I am working for. And each client required different date format for the reports and data transfers. You can facilitate the presentation of date-time value in different formats by utilizing a user defined function accepting two parameters: date-time value itself and a format type variable.
Sometimes it is required to use a UTC date instead of your company’s customary format. The next code fragment shows a user defined function that converts the saved time into UTC time.

CREATE FUNCTION dbo.UDF_UTCDATE (@dt datetime, @now datetime, @utcnow datetime)
Returns datetime as
Begin

declare @utcdate datetime

select @utcdate = DATEADD(ss, DATEDIFF(ss, @now, @utcnow) , @dt )

return @utcdate
End
There are three parameters to this function. The second and the third are always the same, and the first one is your datetime field or variable. The second one is always getdate(), and the third one is always getutcdate(). Parameter 2 and 3 are required to be able to bypass the user defined function restrictions stating that non-deterministic functions can’t be used inside of the user defined function.

Select dbcentral.dbo.udf_utcdate(order_dt, getdate(), getutcdate() ), order_id
From customer_orders
Where customer_id = 10
Conclusion
There are many ways to use datetime data type. This article illustrated some of them without venturing into a discussion on their performance characteristics, ability to produce unique values or any other aspects, which may be subjects of other publications.

Avoid enclosing Indexed Columns in a Function in the WHERE clause

2009-12-07T01:53:00.000-08:00

When a function is wrapped around an indexed column in the WHERE clause it will usually prevent the proper usage of that index. This article gives an example and discusses what other options are available.

I still remember the first time I ever saw this truly crush a server. I was doing performance tuning at a client site. We still hadn't convinced their developers that the DBA's needed to review their T-SQL code before it went into production. One morning we noticed performance problems on the server. Disk reads would go way, way up. SPIDs would begin to block. Response time on their web site slowed to a crawl -- if it responded at all.

After first insisting that no new database code was rolled out they finally admitted they'd changed one line of one WHERE clause in a single stored procedure. That change brought the database server to a stop. They had added a YEAR function around an indexed date column so that a report would only pull back a single year. That changed the query plan enough to start generating table scans.

A script to simulate this looks like this:

USE AdventureWorks
GO
IF EXISTS (SELECT * FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[Sales].[SalesOrderHeader]')
AND name = N'IX_SalesOrderHeader_OrderDate')
DROP INDEX [IX_SalesOrderHeader_OrderDate] ON [Sales].[SalesOrderHeader]
GO
CREATE INDEX IX_SalesOrderHeader_OrderDate ON Sales.SalesOrderHeader(OrderDate)
GO

SELECT SalesOrderID,
OrderDate
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2003
AND MONTH(OrderDate) = 7

SELECT SalesOrderID,
OrderDate
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '7/1/2003' AND '7/31/2003'
GO

IF EXISTS (SELECT * FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[Sales].[SalesOrderHeader]')
AND name = N'IX_SalesOrderHeader_OrderDate')
DROP INDEX [IX_SalesOrderHeader_OrderDate] ON [Sales].[SalesOrderHeader]
GOThis script creates an index on OrderDate. In my sample queries I'm only pulling back a single month. The first query has a cost of 0.080 and does a scan of the index. The second query has a cost of 0.004 and does a seek on the index. Yes, the costs are very low because I don't have a large set of data. The first query still has a cost twenty times higher than the second.

When you wrap a function around an indexed column SQL Server must compute the value of the function for each row in the table. When you just compare the indexed column to a scalar value or the result of a function then SQL Server can use that value to seek into the index. The only time you can use a function around an indexed column is if you use an indexed computed column that matches the function you're using.

This type of coding is mostly used for date fields when computing ranges such as querying for the previous week. Hopefully this little tip will save you from making the same mistake those developers did.

Conditional Statements in WHERE Clauses

2009-12-07T01:30:00.001-08:00

Ever had a query where you wished you could be able to specify the operator (equal, not equal, greater than, etc.) for each column contained in your WHERE clause, without having to use ugly string concatenations and the infamous EXEC keyword? Here we'll see an example of how this can be achieved with the use of a plain SQL query together with some CASE statements.

The Scenario
We will use the Customers table from the sample Northwind database for our example. Let's suppose you want to query the Customers table for the following:

All records that contain the word "the" in the company name
All records for companies located in Germany, excluding companies starting with the letter "A"

Normally, you could create a dynamic statement consisting of multiple IF statements and strings to be concatenated to a final variable which should be executed with the use of the EXEC keyword. But in some cases this is not desirable, and you would like something more versatile (although maybe slightly less efficient).

The code

Let's see how we can form a query to serve the above specs. Take a look at this code. Be sure to read the comments included in the code for an explanation of what is happening.

-- Declare some local variables. Actually, we are creating a pair of variables
-- for each column included in our WHERE clause.
-- The first variable represents the value we are filtering and the second
-- represents the "operator" for the filter.

declare @companyName varchar(255)
declare @companyNameOp varchar(2)
declare @country varchar(255)
declare @countryOp varchar(2)

-- Let's set some sample values now. The values you see here represent the second
-- of the two scenarios described above, i.e. all records for companies located in Germany,
-- excluding companies starting with the letter A

-- Operators are defined here with arbitrary, two-letter values.
-- Of course you could define your own set of operators, with different
-- naming conventions. For our example, here's the meaning of each possible
-- value:

-- ne = not equal
-- eq = equal
-- bg = begins with
-- ed = ends with
-- ct = contains

-- For our example, we are using only varchar fields in our WHERE clause.
-- It is very easy, though, to define operators for other data types as well.

set @companyname = 'A%'
set @companynameOp = 'ne'
set @country = 'Germany'
set @countryOp = 'eq'

-- Ok, now let's form our query.

select
customerid, companyname, country
from
customers
where
case @companyNameOp
when '' then 1 -- Operator not defined, get everything
when 'eq' then -- Operator is "equals"
case when companyname like @companyName then 1 else 0 end
when 'bg' then -- Operator is "begins with"
case when companyname like @companyName +'%' then 1 else 0 end
when 'ed' then -- Operator is "ends with"
case when companyname like '%' + @companyName then 1 else 0 end
when 'ct' then -- Operator is "contains"
case when companyname like '%' + @companyName +'%' then
1 else 0 end
when 'ne' then -- Operator is "not equal"
case when companyname not like @companyName then 1 else 0 end end =1

AND

-- Same approach for the second field

case @countryOp
when '' then 1
when 'eq' then
case when country like @country then 1 else 0 end
when 'bg' then
case when country like @country +'%' then 1 else 0 end
when 'ed' then
case when country like '%' + @country then 1 else 0 end
when 'ct' then
case when country like '%' + @country +'%' then 1 else 0 end
when 'ne' then
case when country not like @country then 1 else 0 end
end =1

Conclusion
The conditional WHERE clauses are based on the simple principle defined by the query "SELECT something FROM sometable WHERE 1=1" As you can see, all CASE statements evaluate to either 1 or 0, so the comparison with 1 can either be false or true for each row.

Of course, you can define your own set of operators (like operators for numeric values) and you can extend your queries to include more fields. The query, as defined here, lets you also NOT define an operator, meaning that nothing will be filtered by the specific field connected to the operator.

Please note that this article serves only as a starting point. You may need to put extra effort (and add extra functionality) in case something like this is required in a production environment, but in my personal opinion, this approach is particularly useful when dealing with customizable query wizards or similar stuff in applications, especially when some kind of custom reporting is involved. Such queries can be easily transformed to stored procedures with all parameters optional and, having some additional checks, return resultsets filtered only by the parameters (and operators) given each time.

NULL Versus NULL?

2009-12-06T23:37:00.000-08:00

No Two NULLs Are Created Equal...
If you recall from the original Four Rules article, the basis of ANSI SQL three-valued logic (3VL) is that NULL is not equal to anything else. It is not less than, greater than, or even unequal to anything else either. Because NULL is not an actual value, but rather a placeholder for an unknown value, all comparisons with NULL result in UNKNOWN. Even comparing a NULL to another NULL is just comparing two placeholders for unknown values, so the result again is UNKNOWN.

Tip: In reference to NULL comparisons, be sure to keep Rule #3 in mind. Microsoft has deprecated SET ANSI_NULLS, and according to Books Online it will be removed in a future version of SQL Server. If you currently have code that relies on SET ANSI_NULLS OFF, it might be a good time to start considering what it will take to make that code ANSI SQL-92 NULL-compliant.

We even generated some samples to demonstrate this. One of these samples is reproduced here in Listing 1.

Listing 1. Demonstrating that NULL is not equal to NULL

SET ANSI_NULLS ON
DECLARE @val CHAR(4)
SET @val = NULL
SET ANSI_NULLS ON
IF @val = NULL
PRINT 'TRUE'
ELSE IF NOT(@val = NULL)
PRINT 'FALSE'
ELSE
PRINT 'UNKNOWN'
NULL: Confusing the Smartest People in the World Since (at least) 1986
If all this doesn't hit home immediately, don't take it too hard. Even Microsoft seems to have difficulty sorting through it. Point in fact: SQL Server 2005 Books Online (BOL) still has bad information concerning NULL comparisons. In fact, as of the time of this writing I counted no less than ten pages in BOL that stated the result of a comparison with NULL is either FALSE or NULL. Only two pages that I found (the pages describing "IS [NOT] NULL" and "SET ANSI_NULLS") actually got it right. Fortunately we know better: the result of comparing NULL with anything is UNKNOWN.

So why all the confusion? Most likely it's because in queries only rows for which the WHERE clause condition evaluates to TRUE are returned. Rows that evaluate to FALSE or UNKNOWN are not returned. For some folks this might seem to indicate FALSE and UNKNOWN are equivalent. They're not, as we'll see in Listings 2 and 3.

Listing 2. Sample SELECT with NULL comparison in the WHERE clause

SELECT TOP 100 *
FROM sys.syscomments
WHERE id = NULL

The result above of course returns no rows. According to Books Online this is because "id = NULL" evaluates to FALSE. If this is true, however, Listing 3 below should return all rows.

Listing 3. The "opposite" of Listing 2

SELECT TOP 100 *
FROM sys.syscomments
WHERE NOT(id = NULL)

If "id = NULL" really evaluates to FALSE for every row, then "NOT(id = NULL)" should evaluate to TRUE for every row. Of course it doesn't, and again no rows are returned. And we already know the reason: it's because "id = NULL" evaluates to UNKNOWN, and "NOT(id = NULL)" also evaluates to UNKNOWN.

Microsoft has already been notified of this problem in BOL and hopefully it will be fixed soon.

...All NULLs Are Created Not Distinct
So now we've firmly established that comparisons with NULL never evaluate to TRUE or FALSE, that NULL is never equal to NULL, and that NULL comparisons always result in UNKNOWN... Now it's time to list the exceptions. (You didn't think it would be that simple did you?)

I really wanted to call this section All NULLs Are Created Equal, but that just happens to be wrong. In order to simulate NULL equality, and to keep from contradicting themselves in the process, the ANSI SQL-92 standard decreed that two NULL values should be considered "not distinct". The definition of not distinct in the ANSI standard includes any two values that return TRUE for an equality test (e.g., 3 = 3, 4 = 4, etc.), or any two NULLs.

This simulated NULL equality is probably most used in the GROUP BY clause, which groups all NULL values into a single partition. SQL-92 defines a partition as a grouping of not distinct values. Listing 4 below shows GROUP BY handling of NULL.

Listing 4. GROUP BY and NULL

CREATE TABLE #test (val INT);

INSERT INTO #test (val) VALUES (NULL);
INSERT INTO #test (val) VALUES (NULL);
INSERT INTO #test (val) VALUES (NULL);
INSERT INTO #test (val) VALUES (NULL);
INSERT INTO #test (val) VALUES (1);
INSERT INTO #test (val) VALUES (2);
INSERT INTO #test (val) VALUES (3);
INSERT INTO #test (val) VALUES (3);

SELECT COUNT(*) AS num, val
FROM #test
GROUP BY val;

DROP TABLE #test;

Figure 2 shows the result.
Figure 2. Result of GROUP BY with NULLs

Notice the NULL values are all treated as not distinct by GROUP BY, and are all grouped together. Unique constraints also use the ANSI definition of not distinct as opposed to equal since you can only insert one NULL in a column with a unique constraint. Consider Listing 5 which shows this.

Listing 5. Unique Constraint and NULL

CREATE TABLE #test (val INT CONSTRAINT unq_val UNIQUE);

INSERT INTO #test (val) VALUES (NULL);
INSERT INTO #test (val) VALUES (NULL);

DROP TABLE #test;

This example throws an exception when it tries to insert the second NULL in the val column:

(1 row(s) affected)
Msg 2627, Level 14, State 1, Line 4
Violation of UNIQUE KEY constraint 'unq_val'. Cannot insert duplicate key in
object 'dbo.#test'.
The statement has been terminated.

Other statements and operators that use the concept of not distinct to simulate NULL equality include:

PARTITION BY clause of OVER()
UNION operator
DISTINCT keyword
INTERSECT operator
EXCEPT operator

NULLs Flock Together
The ORDER BY clause in SELECT queries places all NULL values together when it orders your results. SQL Server treats NULLs as the "lowest possible values" in your results. What this means is NULL will always come before your non-NULL results when you sort in ascending order, and after your non-NULL results when you sort in descending order. Listing 6 shows ORDER BY and NULL in action.

Listing 6. ORDER BY and NULL

CREATE TABLE #test (val INT);

INSERT INTO #test (val) VALUES (NULL);
INSERT INTO #test (val) VALUES (NULL);
INSERT INTO #test (val) VALUES (1);
INSERT INTO #test (val) VALUES (2);

SELECT val
FROM #test
ORDER BY val;

DROP TABLE #test;

The results are shown in Figure 3.

Figure 3. Result of ORDER BY with NULL

The same holds true for the ORDER BY clause of OVER, which is used to order your results when used with ranking functions like ROW_NUMBER and aggregate functions like SUM.

And Now For Something Entirely Different
Now that we've established the "exceptions" for NULL comparisons, let's look at something entirely different. When a NULL value is inserted into a nullable column with a check constraint that doesn't check for IS NOT NULL, something strange seems to happen. Consider Listing 7.

Listing 7. Check constraints and NULL

CREATE TABLE #test (val INT CONSTRAINT ck_val CHECK(val < 0 AND val = 0 AND val > 0));

INSERT INTO #test (val) VALUES (NULL);
INSERT INTO #test (val) VALUES (NULL);

SELECT val
FROM #test
ORDER BY val;

DROP TABLE #test;

In this example we've added a check constraint to the sample table that enforces the following rule:

The value inserted must be less than zero
*and* the value inserted must be equal to zero
*and* the value inserted must be greater than zero

You and I know from 4th grade math (remember number lines?) that there is no value that can ever fulfill these requirements. No value can be less than zero, equal to zero, and greater than zero all at the same time. Also based on what we've already talked about, any comparisons with NULL result in UNKNOWN. You might expect an attempt to insert any value into the table would fail.

However, check constraints operate under a different set of rules from the SELECT, INSERT, UPDATE, and DELETE DML statements. The DML statements, when combined with a WHERE clause, perform their action only on rows for which the WHERE clause condition evaluates to TRUE. The DML statements will exclude rows that evaluate to FALSE or UNKNOWN.

Check constraints, on the other hand, cause INSERT and UPDATE statements to fail only if the check constraint condition evaluates to FALSE. This means that the checks will succeed if the condition evaluates to either UNKNOWN or TRUE.

Of course you'd probably never create a check constraint as restrictive as the one in the example, and if you want to prevent NULLs from being inserted into a column, either declare the column NOT NULL or add "val IS NOT NULL" as a check constraint condition. Don't expect a check constraint that evaluates to UNKNOWN to cause an INSERT or UPDATE to fail.

ALTER TABLE	INSERT
CREATE	OPEN*
DELETE	REVOKE
DROP	SELECT*
FETCH*	TRUNCATE TABLE
GRANT	UPDATE

SQL SERVER Encyclopedia- SQL SERVER Tips & Sugegstions- BuggFixing & Trouble Shooting

Row Oriented Security Using Triggers

Encrypting Connection Strings in Web.config

SQL Server 2005 Paging – The Holy Grail

Introduction to Common Table Expressions

CTE Syntax

Uses for CTEs

Conclusion

SQL Server 2008 T-SQL Enhancements Part - I

Introduction

Intellisense Enhancements

Syntax Enhancements

Object Dependencies Enhancements

Using the FORCESEEK Table Hint

GROUPING SETS

Auto generated SQL Server keys - uniqueidentifier or IDENTITY

Transactions Part 2

Transactions - Part 1

Nested Transactions

Optimizer Join Methods

Optimizer Join Methods

How do I get the IDENTITY / AUTONUMBER value for the row I inserted?

Execution Plans

Index Scan & Index Seek

SQL Aggregate Functions and NULL

Aggregate Functions

COUNT()

SUM()

AVG() NULL-handling

AVG() Scale

MIN(), MAX()

Conclusion

Read Only Tables

The Easy Way

The Hard Way

Conclusion

The Pitfall of "Not Equal To"

INTRODUCTION

Using the OUTPUT Clause to Capture Identity Values on Multi-Row Inserts

Creative Ways to Use the TOP Clause

Sample Data for Examples

Using an EXPRESSION in the TOP Clause

Using the WITH TIES option

Returning the Second Set of 4 Records

How to get the BOTTOM 5 records

How to get the First Record of Each Month

Conclusion

Dealing With Upper and Lower Case Data

What is Collation?

UPPER and LOWER Function

Converting the First Character of Each Column to Upper Case

Proper Casing a Persons Name

Comparing Columns with Different Case

Conclusion

How To Store IP Addresses in SQL Server

What is the best way to store an IP Address in SQL Server

The data type question

SQL 2005 CLR User Defined Types

Test methodology

Initial results

The indexing question

An alternative approach

Wrapping it all up

Addendum

Keep a check on your IDENTITY columns in SQL Server

How to Use GROUP BY in SQL Server

Determining the Virtual Primary Key of a Result Set

A Typical Summary Report Example

Grouping and Summarizing Primary Rows

Using COUNT(Distinct)

Beware of SUMMING Duplicate Values

SQL Server 2008 - The Power of Merge

Working with Datetime

Avoid enclosing Indexed Columns in a Function in the WHERE clause

Conditional Statements in WHERE Clauses

NULL Versus NULL?