How To: Generate a script to drop all Indexes

Well…here it is!
I know some of you are thinking, why would you ever do that?
In my case, I had to upload the entire database (about 40GB) to an external FTP-server.
It saved me a few GB’s to drop the indexes first and send them a SQL-script to (re-)create them 😉

'DROP INDEX ' + + '.' + + '.' + '[' + + ']'
FROM sys.objects as SO
INNER JOIN sys.schemas SS
ON SO.schema_id = SS.schema_id
INNER JOIN sys.indexes as SI
ON SO.Object_Id = SI.Object_Id
WHERE LEFT(si.Name,2) IN ('IX') --'IX' is the used naming prefix.

Filter out NULL values in Indexes

Received a great tip from my colleague on how to filter out NULL values in Indexes.

How to use a WHERE-clause within an Index? Well take a look!

ColumnName ASC

WITH ...

Columnstore Indexes for SQL Server 11.0 release (code named “Denali”)

Microsoft released a new whitepaper on Columnstore Indexes:

Summary: The SQL Server 11.0 release (code named “Denali”) introduces a new data warehouse query acceleration feature based on a new type of index called the columnstore. This new index, combined with enhanced query processing features, improves data warehouse query performance by hundreds to thousands of times in some cases, and can routinely give a tenfold speedup for a broad range of decision support queries. This can allow end users to get more business value from their data through fast, interactive exploration. IT workers can reduce development costs and ETL times since columnstore indexes limit or eliminate the need to rely on pre-built aggregates, including user-defined summary tables, and indexed (materialized) views. Furthermore, columnstore indexes can greatly improve ROLAP performance, making ROLAP more attractive.

SQL Server Denali columnstore indexes are “pure” column stores, not a hybrid, because they store all data for separate columns on separate pages. This improves I/O scan performance and buffer hit rates. SQL Server is the first major database product to support a pure columnstore index.

Download the whitepaper here.