September 14, 2008 at 1:01 am
Comments posted to this topic are about the item Moving Indexes
September 15, 2008 at 1:14 am
As a new author you have done a very good job. Good article.
September 15, 2008 at 7:16 am
Great job! I like how you break down each part of the script with brief descriptions.
September 15, 2008 at 7:30 am
Awesome job Tom.
I however have a suggestion for all those articles with 15 different code snippets. Could you include a download link where we can get the whole thing with a single copy / paste?
It would save a lot of time and garantee that we don't screw it up in the process.
September 15, 2008 at 8:37 am
Get job...
I agree with Ninja's_RGR'us about a single file to download.
Thomas
Thomas LeBlanc, MVP Data Platform Consultant
September 15, 2008 at 9:19 am
September 15, 2008 at 9:24 am
Thom (9/15/2008)
Files are now available for download at the bottom of the article, sorry for the delay.
NP, that was faster than the usual poster :D.
September 15, 2008 at 1:27 pm
Great article. I had an index move task in last week of this month and definitely try your scripts on testing env. and let you know results, how it went through.
SQL DBA.
September 15, 2008 at 11:13 pm
The complete version for SQL2005/2008 is below. It includes schema... plenty of monkeys use multiple schemas..
Enjoy.
SET NOCOUNT ON
DECLARE
@table_schema_name nvarchar(128),
@table_name nvarchar(128),
@index_name nvarchar(128),
@key_ordinal smallint,
@column_name nvarchar(128),
@sort_order varchar(50),
@is_included_column int,
@index_type_desc varchar(50),
@unique_type varchar(50),
@ignore_dup_key varchar(50),
@fill_factor tinyint,
@is_padded varchar(50),
@allow_row_locks varchar(50),
@allow_page_locks varchar(50),
-------------------------------------------
@table_schema_name_ws nvarchar(128),
@table_name_ws nvarchar(128),
@index_name_ws nvarchar(128),
@key_ordinal_ws smallint,
-------------------------------------------
@text1_create varchar(800),
@text1_create_columns varchar(800),
@text1_create_end varchar(50),
@text2_include_start varchar(800),
@text2_include_columns varchar(50),
@text2_include_end varchar(50),
@text4_with varchar(800),
@text4_with_end varchar(50)
SET @text1_create_end = ')'
SET @text2_include_start = 'INCLUDE ('
SET @text2_include_end = ')'
SET @text4_with_end = ')'
DECLARE C01 CURSOR FOR
SELECT
S.name AS table_schema_name,
O.name AS table_name,
I.name AS index_name,
CASE IC.key_ordinal WHEN 0 THEN 999 ELSE IC.key_ordinal END AS key_ordinal,
C.name AS column_name,
CASE IC.is_descending_key WHEN 0 THEN 'ASC' ELSE 'DESC' END AS sort_order,
IC.is_included_column,
I.type_desc AS index_type_desc,
CASE I.is_unique WHEN 1 THEN 'UNIQUE' ELSE '' END AS unique_type,
CASE I.ignore_dup_key WHEN 1 THEN 'ON' ELSE 'OFF' END AS ignore_dup_key,
I.fill_factor,
CASE I.is_padded WHEN 1 THEN 'ON' ELSE 'OFF' END AS is_padded,
CASE I.allow_row_locks WHEN 1 THEN 'ON' ELSE 'OFF' END AS allow_row_locks,
CASE I.allow_page_locks WHEN 1 THEN 'ON' ELSE 'OFF' END AS allow_page_locks
FROM
sys.indexes I
INNER JOIN
sys.objects O
ON O.object_id = I.object_id AND O.type_desc = 'USER_TABLE'
INNER JOIN
sys.schemas S
ON S.schema_id = O.schema_id
INNER JOIN
sys.data_spaces D
ON D.data_space_id = I.data_space_id AND D.name = 'PRIMARY'
INNER JOIN
sys.index_columns IC
ON IC.object_id = I.object_id AND IC.index_id = I.index_id
INNER JOIN
sys.columns C
ON C.object_id = IC.object_id AND C.column_id = IC.column_id
WHERE
I.type_desc IN ('NONCLUSTERED')
AND I.is_hypothetical = 0
ANDI.is_primary_key = 0-- Exclude primary key constraints
AND I.is_unique_constraint = 0-- Exclude unique constraints
AND I.is_disabled <> 1-- Exclude disabled indexes
ORDER BY 1, 2, 3, 4
OPEN C01
FETCH C01 INTO
@table_schema_name,
@table_name,
@index_name,
@key_ordinal,
@column_name,
@sort_order,
@is_included_column,
@index_type_desc,
@unique_type,
@ignore_dup_key,
@fill_factor,
@is_padded,
@allow_row_locks,
@allow_page_locks
SELECT @table_schema_name_ws = @table_schema_name
SELECT @table_name_ws = @table_name
SELECT @index_name_ws = @index_name
WHILE @@FETCH_STATUS = 0
BEGIN
WHILE @@FETCH_STATUS = 0
AND @table_schema_name_ws = @table_schema_name
BEGIN
WHILE @@FETCH_STATUS = 0
AND @table_schema_name_ws = @table_schema_name
AND @table_name_ws = @table_name
BEGIN
SET @text1_create_columns = ''
SET @text2_include_columns = ''
'DROP INDEX [' +
@index_name_ws + '] ON [' +
@table_schema_name_ws + '].[' +
@table_name_ws + '];'
PRINT ' '
SELECT @text1_create =
'CREATE ' +
@unique_type +
' ' + @index_type_desc +
' INDEX [' +
@index_name_ws + '] ON [' +
@table_schema_name_ws + '].[' +
@table_name_ws + '] ('
SELECT @text4_with =
'WITH (PAD_INDEX = ' + @is_padded + ', STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, ' +
'IGNORE_DUP_KEY = ' + @ignore_dup_key + ', ONLINE = OFF, ' +
'FILLFACTOR = ' + CAST(@fill_factor AS VARCHAR(50)) + ', ' +
'ALLOW_ROW_LOCKS = ' + @allow_row_locks + ', ' +
'ALLOW_PAGE_LOCKS = ' + @allow_page_locks
WHILE @@FETCH_STATUS = 0
AND @table_schema_name_ws = @table_schema_name
AND @table_name_ws = @table_name
AND @index_name_ws = @index_name
BEGIN
IF @is_included_column = 1
BEGIN
SELECT @text2_include_columns = @text2_include_columns + @column_name + ','
END
ELSE
BEGIN
SELECT @text1_create_columns = @text1_create_columns + @column_name + ','
END
FETCH C01 INTO
@table_schema_name,
@table_name,
@index_name,
@key_ordinal,
@column_name,
@sort_order,
@is_included_column,
@index_type_desc,
@unique_type,
@ignore_dup_key,
@fill_factor,
@is_padded,
@allow_row_locks,
@allow_page_locks
END
IF @@FETCH_STATUS = 0 SELECT @index_name_ws = @index_name
SELECT @text1_create_columns = '' + SUBSTRING(@text1_create_columns, 1, (DATALENGTH(@text1_create_columns) - 1))
PRINT @text1_create
PRINT @text1_create_columns
PRINT @text1_create_end
IF DATALENGTH(@text2_include_columns) > 0
BEGIN
PRINT @text2_include_start
PRINT '' + SUBSTRING(@text2_include_columns, 1, (DATALENGTH(@text2_include_columns) - 1))
PRINT @text2_include_end
END
PRINT @text4_with
PRINT @text4_with_end
PRINT 'ON INDEX;'
PRINT '----'
END
IF @@FETCH_STATUS = 0 SELECT @table_name_ws = @table_name
END
IF @@FETCH_STATUS = 0
SELECT @table_schema_name_ws = @table_schema_name
END
CLOSE C01
DEALLOCATE C01
September 16, 2008 at 8:35 am
There's something I noticed while testing the script, but it seems like the scripts seems to be seeing the statistics as indexes. I don't know if this is the correct approach so i decided to add the next line to the first query.
and dbo.sysindexes.name not like '_WA_Sys_%'
Thanks for it by the way. I've been trying to do this for some time but never got the time for it 🙁
September 16, 2008 at 8:47 am
I think this wipes out the stats : AND I.is_hypothetical = 0
I'm not sure though, I have not done such tasks in a very long while.
Check out INDEX_PROPERTIES as well.
September 16, 2008 at 4:46 pm
Stats? I think it would be a good practice to update stats every so often. This is outside the scope of moving indexes... Mmmm?
September 16, 2008 at 4:56 pm
My tests do not show any _WA_ :w00t:
September 17, 2008 at 4:57 am
I have to say as a novice in the DBA continuum... I really appreciate the detailed comments surrounding the code snippets explaining why each piece was done this way (specifically the WHERE clauses with the system table columns). Frequently, I get bogged down in the details of included code as I am digging through BOL to figure out why someone included specific criteria.
Great info - thanks!
J.
October 22, 2008 at 4:45 pm
Excellent article, Thom! I love working with system tables, they hold so much cool information and really show how SQL Server is a meta-data system. Conveniently, I think I'll have an index move in the near future, so your code will really come in handy.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply