SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Moving Indexes


Moving Indexes

Author
Message
Thom_Bolin
Thom_Bolin
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 291
Comments posted to this topic are about the item Moving Indexes

Thom Bolin - MCITP SQLServer 2005
NTXDBA Blog[url=http://ntxdba.blogspot.com/][/url]
Anipaul
Anipaul
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9793 Visits: 1407
As a new author you have done a very good job. Good article.



cy-dba
cy-dba
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1563 Visits: 963
Great job! I like how you break down each part of the script with brief descriptions.
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67727 Visits: 9671
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.
Thomas LeBlanc
Thomas LeBlanc
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4604 Visits: 915
Get job...

I agree with Ninja's_RGR'us about a single file to download.

Thomas

Thomas LeBlanc, MCITP DBA 2005, 2008 & MCDBA 2000
http://thesmilingdba.blogspot.com/
Thom_Bolin
Thom_Bolin
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 291
Files are now available for download at the bottom of the article, sorry for the delay.

Thom Bolin - MCITP SQLServer 2005
NTXDBA Blog[url=http://ntxdba.blogspot.com/][/url]
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67727 Visits: 9671
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 BigGrin.
SanjayAttray
SanjayAttray
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5717 Visits: 1619
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.
TheMOTU
TheMOTU
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 77
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
AND I.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 = ''

PRINT
'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
guillermo.pinales
guillermo.pinales
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 63
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 Sad
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search