Code Review

I need to get information on all the unique constraints for a table. I
needed info on whether a constraint is clustered or not, what filegroup
it resides in, columns that comprise the unique index (in a comma
separated list) and it's name. So I wrote the code (see below).
However, SQL is not my first language, so I was looking to see whether a
seasoned SQL expert can recommend anyway to speed up this query. Or
maybe I should take change my approach?
SET NOCOUNT ON
create table #UniqueConstraints
(
ID int IDENTITY,
Name varchar(255),
IndexID int,
IsClustered bit NULL,
FileGroup varchar(255),
Columns varchar(255) NULL
)
create table #ColumnList
(
ID int IDENTITY,
Name varchar(255)
)
-- set to non-clustered by default
insert #UniqueConstraints(Name, IndexID, FileGroup, IsClustered)
SELECT I.name,I.indid,FILEGROUP_NAME(I.groupid) as filegroup, 0
FROM sysindexes I, sysconstraints c
WHERE I.id=OBJECT_ID('dbo.Customers') AND (((I.status & 0x800)=0x800) OR
((I.status & 0x1000)=0x1000))
and i.id = c.id
and (C.status & 0xf)=2
and OBJECT_NAME(C.constid) = I.name
ORDER BY I.indid
-- mark clustered indexees
update #UniqueConstraints
set IsClustered = 1
where IndexID = 1
-- now get columns
DECLARE @.min_ID int, @.indexID int, @.minColumn_ID int
DECLARE @.columnList varchar(255), @.columnName varchar(255), @.comma
varchar(1)
SELECT @.min_ID = min(ID) from #UniqueConstraints
WHILE @.min_ID IS NOT NULL BEGIN
-- get the index id
SELECT @.indexID = IndexID from #UniqueConstraints where ID = @.min_ID
-- freshen up this table
truncate table #ColumnList
-- get the list of involved columns
INSERT #ColumnList(Name)
SELECT COL_NAME(id, colid)
FROM sysindexkeys
WHERE id=OBJECT_ID('dbo.Customers') AND indid=@.indexID ORDER BY
keyno
-- convert the table contents into a comma separated list
SELECT @.minColumn_ID = min(ID) from #ColumnList
-- reinitialize
SET @.comma = ''
SET @.columnList = ''
WHILE @.minColumn_ID IS NOT NULL BEGIN
SELECT @.columnName = Name FROM #ColumnList WHERE ID = @.minColumn_ID
SET @.columnList = @.columnList + @.comma + @.columnName
set @.comma = ','
SELECT @.minColumn_ID = min(ID) FROM #ColumnList WHERE ID >
@.minColumn_ID
END
UPDATE #UniqueConstraints
SET Columns = @.columnList
WHERE ID = @.min_ID
-- get the next value from the table
SELECT @.min_ID = min(ID) FROM #UniqueConstraints WHERE ID > @.min_ID
END
select Name, IsClustered, FileGroup, Columns from #UniqueConstraints
drop table #UniqueConstraints
drop table #ColumnListThe first part of your requirement can be solved in an easier way, like
this:
SELECT i.name,
INDEXPROPERTY(i.id,i.name,'IsClustered') as IsClustered,
g.groupname as FileGroup
FROM sysobjects o
INNER JOIN sysindexes i ON i.id=o.parent_obj AND i.name=o.name
INNER JOIN sysfilegroups g ON i.groupid=g.groupid
WHERE o.xtype='UQ'
AND o.parent_obj=OBJECT_ID('dbo.Customers')
The comma separated list of the columns that are part of each unique
constraint is a little bit more complicated. I would use an UDF, like
this:
CREATE FUNCTION dbo.IndexColumns(@.id int, @.indid int)
RETURNS nvarchar(4000)
AS BEGIN
DECLARE @.List nvarchar(4000), @.ColName sysname
DECLARE Columns CURSOR LOCAL READ_ONLY FOR
SELECT COL_NAME(id,colid) FROM sysindexkeys
WHERE id=@.id AND indid=@.indid
ORDER BY keyno
OPEN Columns
WHILE 1=1 BEGIN
FETCH NEXT FROM Columns INTO @.ColName
IF @.@.FETCH_STATUS<>0 BREAK
SET @.List=ISNULL(@.List+',','')+@.ColName
END
CLOSE Columns
DEALLOCATE Columns
RETURN @.List
END
GO
SELECT i.name,
INDEXPROPERTY(i.id,i.name,'IsClustered') as IsClustered,
g.groupname as FileGroup,
dbo.IndexColumns(i.id,i.indid) AS Columns
FROM sysobjects o
INNER JOIN sysindexes i ON i.id=o.parent_obj AND i.name=o.name
INNER JOIN sysfilegroups g ON i.groupid=g.groupid
WHERE o.xtype='UQ'
AND o.parent_obj=OBJECT_ID('dbo.Customers')
Another way would be to rely on the fact that there are a maximum of 16
columns for an index:
SELECT i.name,
INDEXPROPERTY(i.id,i.name,'IsClustered') as IsClustered,
g.groupname as FileGroup, (
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=1
)+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=2
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=3
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=4
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=5
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=6
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=7
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=8
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=9
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=10
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=11
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=12
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=13
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=14
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=15
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=16
),'') AS Columns
FROM sysobjects o
INNER JOIN sysindexes i ON i.id=o.parent_obj AND i.name=o.name
INNER JOIN sysfilegroups g ON i.groupid=g.groupid
WHERE o.xtype='UQ'
AND o.parent_obj=OBJECT_ID('dbo.Customers')
There is a small difference between the above solutions and your
solution: in the above solutions, the columns in the comma separated
list are presented in the order that is used when the constraint was
defined; in your solution, they are presented in the order in which
they appear in the table.
Razvan

Code Review

I need to get information on all the unique constraints for a table. I
needed info on whether a constraint is clustered or not, what filegroup
it resides in, columns that comprise the unique index (in a comma
separated list) and it's name. So I wrote the code (see below).
However, SQL is not my first language, so I was looking to see whether a
seasoned SQL expert can recommend anyway to speed up this query. Or
maybe I should take change my approach?
SET NOCOUNT ON
create table #UniqueConstraints
(
ID int IDENTITY,
Name varchar(255),
IndexID int,
IsClustered bit NULL,
FileGroup varchar(255),
Columns varchar(255) NULL
)
create table #ColumnList
(
ID int IDENTITY,
Name varchar(255)
)
-- set to non-clustered by default
insert #UniqueConstraints(Name, IndexID, FileGroup, IsClustered)
SELECT I.name,I.indid,FILEGROUP_NAME(I.groupid) as filegroup, 0
FROM sysindexes I, sysconstraints c
WHERE I.id=OBJECT_ID('dbo.Customers') AND (((I.status & 0x800)=0x800) OR
((I.status & 0x1000)=0x1000))
and i.id = c.id
and (C.status & 0xf)=2
and OBJECT_NAME(C.constid) = I.name
ORDER BY I.indid
-- mark clustered indexees
update #UniqueConstraints
set IsClustered = 1
where IndexID = 1
-- now get columns
DECLARE @.min_ID int, @.indexID int, @.minColumn_ID int
DECLARE @.columnList varchar(255), @.columnName varchar(255), @.comma
varchar(1)
SELECT @.min_ID = min(ID) from #UniqueConstraints
WHILE @.min_ID IS NOT NULL BEGIN
-- get the index id
SELECT @.indexID = IndexID from #UniqueConstraints where ID = @.min_ID
-- freshen up this table
truncate table #ColumnList
-- get the list of involved columns
INSERT #ColumnList(Name)
SELECT COL_NAME(id, colid)
FROM sysindexkeys
WHERE id=OBJECT_ID('dbo.Customers') AND indid=@.indexID ORDER BY
keyno
-- convert the table contents into a comma separated list
SELECT @.minColumn_ID = min(ID) from #ColumnList
-- reinitialize
SET @.comma = ''
SET @.columnList = ''
WHILE @.minColumn_ID IS NOT NULL BEGIN
SELECT @.columnName = Name FROM #ColumnList WHERE ID = @.minColumn_ID
SET @.columnList = @.columnList + @.comma + @.columnName
set @.comma = ','
SELECT @.minColumn_ID = min(ID) FROM #ColumnList WHERE ID >
@.minColumn_ID
END
UPDATE #UniqueConstraints
SET Columns = @.columnList
WHERE ID = @.min_ID
-- get the next value from the table
SELECT @.min_ID = min(ID) FROM #UniqueConstraints WHERE ID > @.min_ID
END
select Name, IsClustered, FileGroup, Columns from #UniqueConstraints
drop table #UniqueConstraints
drop table #ColumnListThe first part of your requirement can be solved in an easier way, like
this:
SELECT i.name,
INDEXPROPERTY(i.id,i.name,'IsClustered') as IsClustered,
g.groupname as FileGroup
FROM sysobjects o
INNER JOIN sysindexes i ON i.id=o.parent_obj AND i.name=o.name
INNER JOIN sysfilegroups g ON i.groupid=g.groupid
WHERE o.xtype='UQ'
AND o.parent_obj=OBJECT_ID('dbo.Customers')
The comma separated list of the columns that are part of each unique
constraint is a little bit more complicated. I would use an UDF, like
this:
CREATE FUNCTION dbo.IndexColumns(@.id int, @.indid int)
RETURNS nvarchar(4000)
AS BEGIN
DECLARE @.List nvarchar(4000), @.ColName sysname
DECLARE Columns CURSOR LOCAL READ_ONLY FOR
SELECT COL_NAME(id,colid) FROM sysindexkeys
WHERE id=@.id AND indid=@.indid
ORDER BY keyno
OPEN Columns
WHILE 1=1 BEGIN
FETCH NEXT FROM Columns INTO @.ColName
IF @.@.FETCH_STATUS<>0 BREAK
SET @.List=ISNULL(@.List+',','')+@.ColName
END
CLOSE Columns
DEALLOCATE Columns
RETURN @.List
END
GO
SELECT i.name,
INDEXPROPERTY(i.id,i.name,'IsClustered') as IsClustered,
g.groupname as FileGroup,
dbo.IndexColumns(i.id,i.indid) AS Columns
FROM sysobjects o
INNER JOIN sysindexes i ON i.id=o.parent_obj AND i.name=o.name
INNER JOIN sysfilegroups g ON i.groupid=g.groupid
WHERE o.xtype='UQ'
AND o.parent_obj=OBJECT_ID('dbo.Customers')
Another way would be to rely on the fact that there are a maximum of 16
columns for an index:
SELECT i.name,
INDEXPROPERTY(i.id,i.name,'IsClustered') as IsClustered,
g.groupname as FileGroup, (
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=1
)+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=2
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=3
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=4
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=5
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=6
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=7
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=8
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=9
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=10
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=11
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=12
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=13
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=14
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=15
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=16
),'') AS Columns
FROM sysobjects o
INNER JOIN sysindexes i ON i.id=o.parent_obj AND i.name=o.name
INNER JOIN sysfilegroups g ON i.groupid=g.groupid
WHERE o.xtype='UQ'
AND o.parent_obj=OBJECT_ID('dbo.Customers')
There is a small difference between the above solutions and your
solution: in the above solutions, the columns in the comma separated
list are presented in the order that is used when the constraint was
defined; in your solution, they are presented in the order in which
they appear in the table.
Razvan

Clustered constraint v clustered index

I inherited a database where the designer uses unique clustered constraints instead of indexes for primary keys.

Now as far as I can make out, there is no performance issue because it is clustered. My question is, why only create a unique clustered constraint instead of a unique clustered index?

My thinking is that a primary key should be ceated as an index rather than a constraint. Does it matter?

Hi Kenster.

A unique clustered constraint, a unique clustered index, and a primary key are all really different objects/types.

A primary key is a constraint, so when you create a primary key, you'll end up with a primary key constraint to enforce it...a primary key uses a special unique constraint behind the scenes as well.

A unique constraint (whether clustered or not) uses a similar structure as a unique index (again, whether clustered or not) to enforce uniqueness (and to provide seeking when appropriate)...i.e. a b-tree structure...

In reality, the differences between creating a unique index and a unique constraint are pretty minimal (there are a few though...just do a web search for it and you'll find lots of articles discussing it).

HTH,

|||So is one better than the other when creating a primary key?|||

When you create a primary key, you have no option - you get a primary key constraint, period...behind the scenes, you end up with a unique index on the columns that make up the key and absolutely no null values are allowed.

As for a unique constraint vs. unique index, it's not to say that one is any better than the other, they both provide very, very, very similar functionality - If you create a unique constraint, you end up with a unique index behind the scenes.

Differences between constraints vs. indexes:

The columns that make up the unique key can allow nulls but not for more than one complete key.

A unique key can be referenced by a foreign key constraint and a column which has only a unique index cannot be referenced.

Constraints are checked before indexes and this can lead to a large multi-row insert/select or update to fail before modification.

There's probably some more as well...