April 24, 2011 at 11:41 pm
hi,
how can we identifed which column is cluster index column or non cluster index.
April 25, 2011 at 3:05 am
Assuming I understand your question, join sys.indexes to sys.index_columns (and then to sys.columns)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 25, 2011 at 3:24 am
Below SQL will give you the result ,
SELECT Object_name(si.object_id) AS TABLE_NAME,
sc.name AS COLUMN_NAME,
CASE si.index_id
WHEN 1 THEN 'CLUSTERED'
ELSE 'NONCLUSTERED'
END AS INDEX_TYPE
FROM SYS.INDEX_COLUMNS si
INNER JOIN SYS.SYSCOLUMNS sc
ON si.column_id = sc.colid
AND si.object_id = sc.id
WHERE object_id = Object_id(<<table_name>>)
April 25, 2011 at 5:54 am
i have one table. how can i define this column is cluster index or this column is non cluster index.
April 25, 2011 at 6:07 am
When you create an index, you indicate whether it's a clustered index or a nonclustered index. Creating a primary key will by default create a clustered index to enforce it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 25, 2011 at 6:25 am
Or even simple option :
sp_helpindex 'TblName'
sp_help also has nice info.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply