Friday, June 17, 2005

Creating covering indexes in SQL Server 2005

CREATE INDEX INCLUDE statement (SQL Server 2005)

I've been testing the new INCLUDE statement in 2005.

From the BOL:

INCLUDE ( column [ ,... n ] )

Specifies the nonkey columns to
be added to the leaf level of the nonclustered index. The nonclustered index can
be unique or nonunique.

The maximum number of included nonkey columns is 1,023 columns; the minimum
number is 1 column.

Column names cannot be repeated in the INCLUDE list and cannot be used
simultaneously as both key and nonkey columns. For more information, see Index
with Included Columns.

All data types are allowed except text, ntext, and image. The index must be
created or rebuilt offline (ONLINE = OFF) if any one of the specified nonkey
columns are varchar(max), nvarchar(max), or varbinary(max) data types.

Computed columns that are deterministic and either precise or imprecise can
be included columns. Computed columns derived from image, ntext, text,
varchar(max), nvarchar(max), varbinary(max), and xml data types can be included
nonkey columns as long as the computed column data types is allowable as an
included column. For more information, see Creating Indexes on Computed

Didn't have much time but I did a couple of quick tests. Just created a simple table with a covered index 'the old way' and then 'the new way'. From what I've seen until now the duration is not much less but inserting 10000 records in to the table takes about 10000 reads less with the INCLUDE index. From the info above I suppose it has something to do with the fact that the nonkey column is only present on the leaf level and not throughout the whole tree like a key column.

As soon as I find some time to test is a little deeper I'll try to let you know!

SQL Server 2005 - June CTP

Oh yeah, I forgot to mention it :-s
As you probably already know... SQL Server June 2005 Community Technology Preview is available!

Go go go!

AWE vs SQL Server 2000 SP4

There appears to be a fix for the AWE problem in SP4 of SQL Server 2000.
The bad news is that it's not available yet at least not to the public, although it should be available through PSS.

Report Builder available in Standard Edition of SQL Server 2005 Reporting Services

Some good news from Microsoft, and I quote Paul Flessner:

In February, we announced the product line for SQL Server 2005. As part of
this announcement, we revealed that Reporting Services would ship with
and Enterprise Editions and that Report Builder would be available
Enterprise Edition. Based on your feedback, and because we believe that
reporting is a core scenario for all customers, we've made the decision to
include Reporting Services in all versions for SQL Server 2005 including
Express, Workgroup, Standard and Enterprise Editions. In addition, Report
Builder will be available with Workgroup, Standard and Enterprise
I think it's great that Microsoft listens to their users... keep up the good work!

Thursday, June 16, 2005

How to avoid "Parameter Sniffing" in SQL Server 2005

Good news for people with 'parameter sniffing' problems.
Although Ken Henderson gave us a nice solution some time ago for SQL Server 2000, SQL Server 2005 will support the OPTIMIZE FOR statement. Adding this to a query makes sure the plan is optimized for the value you specify. And when all else fails you can even force a particular query plan with the new USE PLAN statement. I'll try to give some examples as soon as I find some time.