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 Index creation uses extremely much diskspace

Hi,

I have a database in which I have a table that is about 20GB (+/- 9.000.000 records).

This table is created in the filegroup 'SRC_TABLES'.

When I want to create my clustered index on this table - in the same filegroup - it starts to write a lot of data to the harddisk. There is only 18GB free on the harddisk so it fails with the error that he can't create enough free space in the filegroup.

This is the query I use. As you can see it's just a standard clustered index query:

ALTER TABLE dbo.SRC_GRIDFEE_LINE_ITEM_SWITCH ADD CONSTRAINT
PK_SRC_GRIDFEE_LINE_ITEM_SWITCH PRIMARY KEY CLUSTERED
(
GRIDFEE_LINE_ITEM_ID
) ON [SRC_TABLES]
GO

I freed up some space and eventually he created the clustered index, but used more than 20GB of HD-space to do so.
I don't understand why he uses so much diskspace to create that clustered index. If the clustered index is finally created it appears to be only about 4GB.

Thanks in advance.

Thread moved from SSIS Forum to something more appropriate.

In answer to the question, try Books Online, for example "Creating Clustered Indexes"

When a clustered index structure is created, disk space for both the old (source) and new (target) structures is required in their respective files and filegroups. The old structure is not deallocated until the complete transaction commits. Additional temporary disk space for sorting may also be required. For more information, see Determining Index Disk Space Requirements.

|||

Thx for moving. Noticed it too late.

For the clustered index... Been there, done that

Doesn't really provide me with the answers that I need.

Anyone?

|||

!! UPDATE !!

According to the properties windows my table is more than 20GB and my indexes are 35MB !?

I have one index on the table --> the primary key which is clustered.

If I look at what can be found in Books Online (look at DarrenSQL's post), this means that SQL Server needs more than 20GB of extra disk space to create a stupid index of only 35MB?

This doesn't make sence to me...

Can anyone help me out here?