Introduction

Inheriting a large SQL Server table with suboptimal indexing can be a daunting task, especially when dealing with a 10 TB table. In this article, we’ll explore a real-world scenario where a table uses a uniqueidentifier as its clustered primary key and has an unnecessary identity int column. We’ll discuss the steps to efficiently optimize this table by replacing the clustered primary key with a bigint identity column and creating a nonclustered index on the uniqueidentifier column, all while minimizing downtime.

Understanding the Current Table Structure

Before we dive into the optimization process, let’s understand the current table structure:

• The table has a uniqueidentifier column as its clustered primary key.
• There is an additional identity int column that is not needed.
• The table size is approximately 10 TB.

Using a uniqueidentifier as a clustered primary key can lead to performance issues, especially for large tables. The random nature of GUIDs causes fragmentation and inefficient index usage.

Step 1: Create a New bigint identity Column

The first step in optimizing the table is to add a new bigint identity column that will serve as the new clustered primary key. To do this, execute the following SQL statement:

ALTER TABLE YourTableName ADD NewID bigint IDENTITY(1,1);

This statement adds a new column named NewID with an identity property that automatically generates sequential values starting from 1 and incrementing by 1.

Step 2: Create a Nonclustered Index on the uniqueidentifier Column

To maintain the uniqueness of the uniqueidentifier column and efficiently search based on its values, create a nonclustered index on that column. Use the following SQL statement:

CREATE UNIQUE NONCLUSTERED INDEX IX_YourTableName_UniqueIdentifier 
ON YourTableName (UniqueIdentifierColumn);

This statement creates a unique nonclustered index named IX_YourTableName_UniqueIdentifier on the UniqueIdentifierColumn.

Step 3: Drop the Existing identity int Column

Since the existing identity int column is not needed, you can drop it to free up space and simplify the table structure. Execute the following SQL statement:

ALTER TABLE YourTableName DROP COLUMN ExistingIdentityIntColumn;

Replace ExistingIdentityIntColumn with the actual name of the unnecessary identity int column.

Step 4: Make the bigint identity Column the Primary Key

Now that you have created the new bigint identity column and dropped the unnecessary identity int column, it’s time to make the bigint identity column the primary key. Use the following SQL statement:

ALTER TABLE YourTableName 
DROP CONSTRAINT PK_YourTableName_UniqueIdentifier;

ALTER TABLE YourTableName 
ADD CONSTRAINT PK_YourTableName_NewID 
PRIMARY KEY CLUSTERED (NewID);

This statement drops the existing primary key constraint on the uniqueidentifier column and adds a new primary key constraint on the NewID column, making it the clustered index.

Minimizing Downtime

To minimize downtime during this optimization process, consider the following approaches:

1. Perform the changes during off-peak hours when the table is least accessed.
2. Use the ONLINE option while creating indexes to allow concurrent access to the table.
3. Break down the process into smaller batches to avoid long-running transactions.
4. Test the optimization steps on a non-production environment first to ensure smooth execution.

Conclusion

Optimizing a large SQL Server table with a suboptimal primary key can significantly improve query performance and overall database efficiency. By replacing the uniqueidentifier clustered primary key with a bigint identity column and creating a nonclustered index on the uniqueidentifier column, you can effectively restructure the table while minimizing downtime. Remember to carefully plan and test the optimization process before executing it on a production environment.

For more information on clustered and nonclustered indexes in SQL Server, refer to the official Microsoft documentation.