Indexing question

• Mh-397891

  SSCertifiable

  Points: 6704

  More actions

  April 20, 2010 at 9:03 pm

  #219494

  Some of the columns in our database has datatypes of text and varbinary(max), as we cannot do online indexing for those columns, I am thinking of checking the data types of the columns and based on the data types I want to either do online or offline indexing.

  Here is part of my code:

  fetch next from indexes into @tablename, @indexname, @frag;

  if (@frag >= 30)

  begin

  check the data types of the columns if in (text, varbinary(max))

  set @cmd = 'ALTER INDEX [' + rtrim(@indexname) + '] ON db..[' + @tablename +'] REBUILD PARTITION = ALL

  WITH (ONLINE=OFF, SORT_IN_TEMPDB = OFF);'

  else

  set @cmd = 'ALTER INDEX [' + rtrim(@indexname) + '] ON

  db..[' + @tablename +'] REBUILD PARTITION = ALL WITH (ONLINE=ON, SORT_IN_TEMPDB = OFF);'

  Please let me know how I can check the data types of the columns before deciding to do online or offline indexing.

  Thanks!

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738713

  More actions

  April 20, 2010 at 9:43 pm

  #1155079

  Check out sys.index_columns (http://msdn.microsoft.com/en-us/library/ms175105.aspx) and you can get the columns. If not, join with sys.columns and you should get the types.

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  April 20, 2010 at 10:24 pm

  #1155087

  You can also Information_Schema.Column View to get information about all the columns in a database..

  sample query

  SELECT

  TABLE_NAME

  ,COLUMN_NAME

  ,DATA_TYPE

  ,CHARACTER_MAXIMUM_LENGTH

  ,NUMERIC_PRECISION

  ,NUMERIC_SCALE

  ,ORDINAL_POSITION

  ,IS_NULLABLE

  FROM

  INFORMATION_SCHEMA.COLUMNS

  ORDER BY

  TABLE_NAME

  ,COLUMN_NAME

  To know more about Information_Schema views, click here

  Cheers!!