SQL Server – Disabling Every Index on a Table

SQL Server, ,

From SQL Server 2005 onwards, disabling a specific index on a table is very straightforward. This can be achieved by the following:

ALTER INDEX <index name> ON <table name> DISABLE

Then to re-enable the index:

ALTER INDEX <index name> ON <table name> REBUILD

To disable or rebuild all the indexes on a table, replace <index name> in the above statements with the keyword ALL, as follows:

ALTER INDEX ALL ON <table name> REBUILD

An interesting point is that if a table has a clustered index and this clustered index is disabled, all other indexes are disabled. Of more interest is that fact that once the clustered index is disabled, you can’t access the data or insert into the table or in actual fact do anything else except drop or rebuild the index! If someone can explain why anyone would want to disable the clustered index when it has this effect, I’d be very happy to hear why.

Another point to note is that when the clustered index is re-enabled, the other indexes are not automatically re-enabled with it.

The point of this post is that sometimes there is a need to disable all the non-clustered indexes on a particular table, probably to improve performance when doing an insert of a large number of rows. Of course, you can add several “ALTER INDEX .. DISABLE” lines to a script, but it’s more convenient to not have to worry about which indexes exist and what their names are.

The following T-SQL script will perform this function, disabling all the non-clustered indexes on a particular table and re-enabling them after doing some work:

-- Set the name of the schema and table here
DECLARE @Schema sysname
SET @Schema = 'dbo'
DECLARE @Table sysname
SET @Table = 'TableWithSomeIndexes'
 
-- Get the non-clustered indexes
DECLARE @Indexes TABLE(Name sysname)
INSERT INTO @Indexes(Name)
SELECT ind.name
FROM sys.indexes ind
WHERE ind.object_id = OBJECT_ID(@SCHEMA + '.' + @TABLE)
AND ind.Type != 1 -- 1 is clustered
AND ind.is_disabled = 0
 
-- Disable the indexes
DECLARE @sql1 NVARCHAR(MAX)
SELECT @sql1 = isnull(@sql1, '') + 'ALTER INDEX ' + name + ' ON ' + @SCHEMA + '.' + @TABLE + ' DISABLE '
FROM @Indexes
EXEC SP_EXECUTESQL @sql1
 
-- Do your work here....
/*
INSERT INTO EmptyTable
SELECT LotsOfRows
FROM ComplicatedQuery
*/
 
-- Now we re-enable the indexes that we disabled
DECLARE @sql2 NVARCHAR(MAX)
SELECT @sql2 = isnull(@sql2, '') + 'ALTER INDEX ' + name + ' ON ' + @SCHEMA + '.' + @TABLE + ' REBUILD '
FROM @Indexes
EXEC SP_EXECUTESQL @sql2

The script has been tested on SQL Server 2008 but should work on SQL Server 2005.

.Net String Formatting

.Net,

A while ago, I was wanting to format a value in a particular way and needed some detail on .Net format strings. While Googling around, I came across this fantastic “cheat sheet” from John Sheehan on his blog.

OK, so the information’s all available on MSDN, but this has it all together in one place, easy to read, easy to see. Fantastic!

SQL Search from Red Gate

SQL Server, ,

I’ve recently been introduced to a fantastic free tool from Red Gate, who provide several software packages for use with SQL Server. I’ve used their SQL Compare tool for a few years now and it’s been great, but only gets used on an occasional basis.

SQL Search, however, has become my best friend! It’s an add-in that integrates into SQL Server Management Studio and allows you to search for specific text across all your database objects. Once it’s indexed the database, it’s incredibly quick, in fact instantaneous (I’m working at the moment on a database with around 1000 tables and 3000 stored procedures).

One of the first things I do when I start working on a new database is script out all the stored procedures and triggers and save the result so that I can find where particular tables or columns are used. With SQL Search, I no longer need to do this and don’t have the disadvantage of the scripted objects going out of date.

SQL Search has almost become my starting point for my use of SQL Server. If I want to view or modify a particular stored procedure, it’s easier to type part of the name into SQL Search than it is to search for it in object explorer. If I’m just wanting to view it, it’s instantly available in the preview pane as soon as I click it in the search results. If I want to go further, the very useful “Select Object in Explorer” link is available.

There are a couple of improvements I’d like to see. Firstly, at the moment, if two words are entered it returns results containing either of the words. This isn’t the default behaviour I would have chosen, it makes more sense to me that the results should contain both the words. Changing this or allowing a boolean search would be a definite advantage.

Secondly, it would be nice to see the code in the preview pane coloured coded in the same way as in Management Studio rather than being presented in plain text.

Overall though, a fantastic product and best of all, it’s free (for now at least!).