Category: indexing

How to Script Out Indexes from SQL Server

on May 5, 2016

Category: indexing , sql-server , database-administration

Sometimes you need to script out all the indexes in a database. Maybe you’re concerned something has changed since they were last checked in. Or maybe the indexes aren’t checked into source control, and you’re working on fixing that. (Important!)

Either way, sometimes you need to do it, and it’s not fun through the GUI. I needed to write some fresh demo code for this recently, and I needed it to give the details for partitioned tables using data compression, and I thought I’d share.

Links and Notes from SQL Server Index Formulas, Huntington Beach

on April 5, 2016

Category: indexing

Tags: indexing , links , precon , sqlsaturday , training

I gave a day long session, “SQL Server Index Formulas, Problems and Solutions” in Huntington Beach, CA on April 1. The class was a great group of students, and we had a lively discussion and lots of questions.

Here’s a topic we diagrammed in class, as well as links to extra resources.

Truncate Table with Partitions Fails if You Have Non-Aligned Indexes

on March 22, 2016

Category: indexing

Tags: error , table-partitioning , truncate

SQL Server 2016 brought in a cool new little feature for table partitioning: you can now truncate individual partitions. There’s one little gotcha, though: you can only do this if all the indexes on the tables are “aligned”.

Find the Partitioning Key on an Existing Table with Partition_Ordinal

By Kendra Little on March 15, 2016

Category: indexing

Tags: partitioning , partition-ordinal , metadata

@SQLSista asked a fun question on Twitter recently about how to figure out the partitioning key on an existing table:

Bad Partitioned HEAP predates me. No clustered index, Primary Key on ID column and multiple datetime columns. How to find the partitioning key? #sqlhelp

SQL Code Basics: Reusable Event Logging Utility

on March 3, 2016

Category: indexing

When I created the SQLIndexWorkbook database [which was later renamed to BabbyNames], I purposefully shipped minimal code. I plan to add more as I build it, but I wanted to keep the first version dead simple.