Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Friday, March 20, 2009

Recently had a need to ensure that some tables were indexed in a particular order, mostly due to a very small maintenance window.  I did not want to create any supporting tables to do this, so we quickly modified the reindex script to include a priority and list the tables in the query.  If there were more than just a handful, our thought was to use the extended properties of each table to add a priority, and then order by that priority for the reindexing.

The quick way for a few tables was just changing the select statement to:

SELECT table_name, table_schema,
 Case when table_name in ('Client','conflict') Then 1
    When table_name in ('appointment') then 2
  Else 3 End As Priority
FROM information_schema.tables a
WHERE a.table_type = 'BASE TABLE'
 AND a.table_name <> 'dtproperties'
 AND NOT (a.table_name LIKE 'tmp%')
 ORDER BY Priority,table_name 

If you have many tables to do, and you need very fine grain control, than use the extended properties, add one to each table that you need to control.  It is not necessary to add it to every table unless you want to control the order of all.  Below is the script example to do this for two tables.


Create Table BobTest (bobTestId int identity(1,1) primary key,
    bob_description varchar(100))
Create Table z_ReindexFirst (testid int identity(1,1) primary key)

EXEC sys.sp_addextendedproperty @name=N'ReindexPriority', @value=N'1' ,
     @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',
     @level1name=N'BobTest'

EXEC sys.sp_addextendedproperty @name=N'ReindexPriority', @value=N'2' ,
     @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',
     @level1name=N'z_ReindexFirst'

SELECT CAST(e.name AS VARCHAR(1000)) As ext_prop_name, 
       Coalesce(CAST(e.value AS VARCHAR(1000)),0) As ext_prop_value,
        t.name
       FROM sys.tables AS t 
       LEFT OUTER JOIN sys.extended_properties AS e 
                        ON t.[object_id] = e.major_id 
                        AND e.minor_id = 0
       WHERE e.name is null or e.name='ReindexPriority'
ORDER BY e.value desc,t.name

drop table bobTest
drop table z_ReindexFirst
Friday, March 20, 2009 11:53:03 AM (Central Standard Time, UTC-06:00) |  | SQL Server#
Search
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
Default Trace - Heavy load, turn it...
SQL 2008 R2 License / Cost = Open S...
Door Installation
Recent Posts
Archive
October, 2019 (1)
September, 2019 (1)
August, 2019 (1)
May, 2019 (1)
April, 2019 (1)
March, 2019 (1)
February, 2019 (1)
January, 2019 (1)
December, 2018 (1)
November, 2018 (3)
October, 2018 (1)
June, 2018 (1)
May, 2018 (3)
April, 2018 (2)
February, 2018 (3)
January, 2018 (3)
November, 2017 (1)
October, 2017 (1)
August, 2017 (1)
June, 2017 (2)
May, 2017 (2)
April, 2017 (2)
March, 2017 (1)
February, 2017 (1)
December, 2016 (2)
October, 2016 (2)
September, 2016 (1)
August, 2016 (1)
July, 2016 (1)
March, 2016 (2)
February, 2016 (3)
December, 2015 (4)
November, 2015 (6)
September, 2015 (1)
August, 2015 (2)
July, 2015 (1)
March, 2015 (2)
January, 2015 (1)
December, 2014 (3)
November, 2014 (1)
July, 2014 (2)
June, 2014 (2)
May, 2014 (3)
April, 2014 (3)
March, 2014 (1)
December, 2013 (1)
October, 2013 (1)
August, 2013 (1)
July, 2013 (1)
June, 2013 (2)
May, 2013 (1)
March, 2013 (3)
February, 2013 (3)
January, 2013 (1)
December, 2012 (3)
November, 2012 (1)
October, 2012 (1)
September, 2012 (1)
August, 2012 (1)
July, 2012 (4)
June, 2012 (3)
April, 2012 (1)
March, 2012 (3)
February, 2012 (3)
January, 2012 (4)
December, 2011 (3)
October, 2011 (2)
September, 2011 (2)
August, 2011 (8)
July, 2011 (4)
June, 2011 (3)
May, 2011 (3)
April, 2011 (1)
March, 2011 (2)
February, 2011 (3)
January, 2011 (1)
September, 2010 (1)
August, 2010 (2)
May, 2010 (2)
April, 2010 (3)
March, 2010 (1)
February, 2010 (4)
January, 2010 (1)
December, 2009 (3)
November, 2009 (2)
October, 2009 (2)
September, 2009 (5)
August, 2009 (4)
July, 2009 (8)
June, 2009 (2)
May, 2009 (3)
April, 2009 (9)
March, 2009 (6)
February, 2009 (3)
January, 2009 (8)
December, 2008 (8)
November, 2008 (4)
October, 2008 (14)
September, 2008 (10)
August, 2008 (7)
July, 2008 (7)
June, 2008 (11)
May, 2008 (14)
April, 2008 (12)
March, 2008 (17)
February, 2008 (10)
January, 2008 (13)
December, 2007 (7)
November, 2007 (8)
Links
Categories
Admin Login
Sign In
Blogroll