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 ...

When querying large datasets the strategic assembly of the query's tables are as important as the assembly of the WHERE clause. The joining of a sub-query instead of a standard joining of tables can result in performance gains. The benefit comes from the order of execution; the sub-query is executed prior to the joining of the other tables which could result in attempting to join less rows of data.

The following query unions two tables that join to the PRODUCT table:

SELECT  
        Production.Product.ProductID,
        Production.Product.Name,
        Production.Product.ProductNumber,
        Production.TransactionHi ...

Like most real world developers and DBAs, I am working with multiple SQL Server versions at the same time. I currently work with SQL Server 2000 in one project while working with SQL Server 2005 in another. To further this experience, I recently had a discussion with a peer who just completed a project to move SQL Server 7 to SQL Server 2000. Some businesses are more eager to adopt the latest and greatest software while others take the “wait and see” or “if it ain’t broke don’t fix it” approach.

We now find ourselves on the cusp of another version of SQL Server. This really begins the new Microsoft eighteen month release plan. This plan is very aggressive; but so is the software and database marketplace. SQL Server has become quite a complex platform and frequent improvement and innovation is not only desired but essential to its survival and competitiveness.

Regardless of whether one feels that this new version is too soon or just in time, it is fast approaching and Microso ...