It might be a stretch; but I might be willing to bet that a review of most T-SQL code would reveal that commenting code is a practice that is inconsistently executed. This may be especially true in an environment that has many developers and DBAs maintaining the same database.

Here are some suggestions of commenting stored procedure code:

- Notation of a stored procedure’s intent and creation information.
- Notation of a stored procedure’s change log.
- Notation of specific modifications to code.
- Description of a block of code.

Below is a sample of a well commented stored procedure:

/*
****************************************************************************************
Stored Proc:  dbo.MyTable_Update
Created By:   Bobby Flay (bflay@email.com)
Created Date:  1/26/200 ...

One of the great things about being a database professional is that learning is constant.

A few days ago I blogged about temporary tables and table variables and within that blog I noted that one benefit of table variables was that they resided in memory where temporary tables resided in TempDB database. A great friend of mine pointed out to me that while that is true at a basic level, it is not wholly true. When the table variable exceeds memory limits it is written to the TempDB database.

This was a very excellent point. In order to test this concept, I used the fn_virtualfilestats in SQL Server Management Studio against the AdventureWorks database. (Refer to the following link for the details regarding the syntax of this function: http://msdn2.microsoft.com/en-us/library/ms187309.aspx)

I execut ...

I was watching a very interesting movie quite a few months ago. In this movie, the primary character hopped into his sports car in the middle of the day, backed out of his driveway and proceeded to travel down a deserted street in the middle of New York City. I have never visited the “Big Apple” but I would suspect that this occurrence would be quite an anomaly.

It would be reasonable to assume that having only a singular transaction occur within a SQL Server database would be an equivalent anomaly to the one presented in the movie previously mentioned. To this, the delicate balance between data concurrency and data consistency begins.

Transaction isolation level in stored procedures is the traffic cop on the street of transactions. They prevent those nasty collisions that backup the flow of data. While a detailed description of all transaction isolation levels is another blog entry, I wanted to explore the Evil-Read Trio that can occur if careful consideration of these tran ...

When selecting data from a database in a multiuser environment locking becomes rather important. Locks protect the data consistency of the database but also limit data concurrency. The extent of the lock’s affect on data concurrency depends upon the type of lock that is applied.

When a SELECT statement is executed, the default locking behavior is to apply a shared lock. A shared lock allows other users to also read the data concurrently while restricting it from being modified until the transaction is completed. The granularity of the lock determines the scope of the affect of the shared lock on the database.

A lock can occur at the database, table, row, extent or page level. The default granularity of a shared lock is the page level; but there are several situations that may cause the query optimizer to escalate the level of locking. A table scan, for example, may cause the level of the locking to be elevated to the table level preventing all ro ...

I recently ran across some articles that discussed the philosophical implications of Quantum Mechanics which made for very interesting and thought provoking reading. A piece of those articles really grabbed my brain: It was the concept of a probabilistic universe vs. a deterministic universe. While I cannot even begin to claim to be a philosopher, a theologian or a quantum mechanic, I thought it would be interesting to express these concepts, at an extremely simplified manner, in what I consider my realm: SQL Server.

According to the American Heritage Dictionary, the definition of determinism is: “The philosophical doctrine that every state of affairs, including every human event, act, and decision is the inevitable consequence of antecedent states of affairs.”

In SQL Server terms determinism might best be described as follows: The occurrence of a specific set of data does not appear randomly. While the exact event or act that begins the process of the dat ...

There will certainly come a time in the development of a database where you will encounter the need for either a temporary table or a table variable. The first question that any developer must ask is whether the need for these items is truly necessary. In some cases, a derived or correlated query can address the need at hand. Utilizing a UNION join, in some cases, could also be an alternative to consider.

Once it is determined that the use of either a temporary table or table variable is indeed appropriate for the task at hand, the next question that comes to mind is the selection of the optimal choice. Before we dive into the comparison between these options, let's take a quick look at the syntax:

To create a temporary table, the syntax is as follows:

CREATE TABLE #TEMP (
FIELD1 INT,
FIELD2 VARCHAR(50) NULL,
FIELD3 VARCHAR(50) NULL
)

The use of local variables within T-SQL is something that is very common. The two methods in which to assign a value to a local variable are using the SET or SELECT methods. I have seen both used rather frequently and interchangeably in my years of reviewing T-SQL.

The way that the SET method is used when assigning a value to a local variable is:

    SET @localvariable = ‘Test’

The way that the SELECT method is used when assigning a value to a local variable is:

    SELECT @localvariable = ‘Test’

Having a choice, the natural question is which should be used?

If you follow the ...

While evaluating execution plans one may notice an occurrence that is called a bookmark lookup. These bookmark lookups can have a damaging affect on the performance of your query or stored procedure. A bookmark lookup occurs when there is data being retrieved using a non-clustered index.

To understand how bookmark lookups work, we must begin at an item called a "page". A page is 8 kilobytes of data. The page is where the reading, writing and storage of the database occurs. The leaf-level, or node page, contains either key values or pointers to the underlying data.

If the leaf-level contains key values, the data that is stored at the leaf-level is stored in ascending order. These key values are called a clustered index. In this case the leaf-node contains actual data that can be recalled directly from the leaf-level.

If the leaf-level contains a pointer, or bookmark, to a heap table it is called a non-clustered index. A heap table is ...