Archive for the ‘TSQL’ Category

Speeding up aggregates with indexed views

March 25, 2012 by · 3 Comments
Filed under: Performance, TSQL 
Social Tagging: • • • • •

Ever so often, queries include some aggregates data. In a datawarehouse environment, they are frequently used for metric and Key Performance Indicator (KPI) calculations. Aggregates are also used a lot for reporting purposes and for statistical computation. When a query perform poorly, our first instinct is to add an index to speed it up. Creating a […]

Table variable for large tables (vs temporary tables)

September 13, 2011 by · 2 Comments
Filed under: Performance, TSQL 
Social Tagging: • • • • • • • •

The main reason why Microsoft introduced table variable in SQL Server 2000 is to reduce stored procedure recompilations (a recompilation occurs when the stored procedure execution plan is recreated). Table variables also have a well defined scope (the current procedure or function) and they induce less logging and locking (because their transaction last for a […]

Using NOT IN operator with null values

August 18, 2011 by · 7 Comments
Filed under: Performance, TSQL 
Social Tagging: • • • •

The IN operator compares a value with a list of values. It must however be used with care when we are dealing with nulls. Let’s create a table containing three city names and a null value. The goal is check whether a city is in the list or not.

How NOT to retrieve IDENTITY value

May 6, 2011 by · Leave a Comment
Filed under: TSQL 
Social Tagging: • • • • • • •

It is a common business case to have to reuse the auto-generated SQL Server’s IDENTITY value. One way to deal with the problem is to use the system function @@IDENTITY. For example: