Wednesday, September 30, 2009

Filtered Indexes in SQL Server 2008

SQL Server 2008 introduces Filtered Indexes which is an index with a WHERE clause. Doesn’t it sound awesome especially for a table that has huge amount of data and you often select only a subset of that data? For example, you have a lot of NULL values in a column and you want to retrieve records with only non-NULL values or in another scenario you have several categories of data in a particular column, but you often retrieve data only for a particular category value. This article talks about what a Filtered Index is, how it differs from other indexes, its usage scenario, its benefits and limitations.

Executing dynamic SQL scripts on remote SQL Server with EXEC AT statement

With SQL Server 2000, we had OPENQUERY and OPENROWSET to execute a pass-through query on the specified server, but it has several inherent limitations. Starting with SQL Server 2005 we have another more elegant way using "EXEC AT" to execute a pass-through query on the specified linked server which also addresses several shortcomings of OPENQUERY and OPENROWSET table functions. This article talks about this new way of executing dynamic SQL scripts on the remote server in SQL Server 2005 and 2008.