clustered vs. non clustered

Giganews Newsgroups
Subject: clustered vs. non clustered
Posted by:  pb648174 (goog…@webpaul.net)
Date: 14 Aug 2006

I've been doing a bit of reading and have read in quite a few places
that an identity column is a good clustered index and that all or at
least most tables should have a clustered index. The tool I used to
generate tables made them all with non clustered indexes so I would
like to drop all of them and generate clustered indexes. So my
questions is a) good idea? and b) how? There are foreign key references
to most of them so those would need to be dropped first and then
re-created after the clustered one was created and that could cascade
(I think?)

Any existing scripts out there that might do this? I found something
similar and modified it, the sql is included below. This gives me the
list of all the columns I need, I just need to get the foreign keys for
each from here before each one and generate all the create/drop
scripts.

All the columns I am looking to do this for are called "Id" making this
somewhat simpler. I'm just looking to incrementally make the SQL side
better and don't want to rewrite a bunch of application level code to
make the column names ISO compliant, etc.

/*
-- Returns whether the column is ASC or DESC
CREATE FUNCTION dbo.GetIndexColumnOrder
(
    @object_id INT,
    @index_id TINYINT,
    @column_id TINYINT
)
RETURNS NVARCHAR(5)
AS
BEGIN
    DECLARE @r NVARCHAR(5)
    SELECT @r = CASE INDEXKEY_PROPERTY
    (
        @object_id,
        @index_id,
        @column_id,
        'IsDescending'
    )
        WHEN 1 THEN N' DESC'
        ELSE N''
    END
    RETURN @r
END

-- Returns the list of columns in the index
CREATE FUNCTION dbo.GetIndexColumns
(
    @table_name SYSNAME,
    @object_id INT,
    @index_id TINYINT
)
RETURNS NVARCHAR(4000)
AS
BEGIN
    DECLARE
        @colnames NVARCHAR(4000),
        @thisColID INT,
        @thisColName SYSNAME

    SET @colnames = INDEX_COL(@table_name, @index_id, 1)
        + dbo.GetIndexColumnOrder(@object_id, @index_id, 1)

    SET @thisColID = 2
    SET @thisColName = INDEX_COL(@table_name, @index_id, @thisColID)
        + dbo.GetIndexColumnOrder(@object_id, @index_id, @thisColID)

    WHILE (@thisColName IS NOT NULL)
    BEGIN
        SET @thisColID = @thisColID + 1
        SET @colnames = @colnames + ', ' + @thisColName

        SET @thisColName = INDEX_COL(@table_name, @index_id,
@thisColID)
            + dbo.GetIndexColumnOrder(@object_id, @index_id,
@thisColID)
    END
    RETURN @colNames
END

CREATE VIEW dbo.vAllIndexes
AS
begin
    SELECT
        TABLE_NAME = OBJECT_NAME(i.id),
        INDEX_NAME = i.name,
        COLUMN_LIST = dbo.GetIndexColumns(OBJECT_NAME(i.id), i.id,
i.indid),
        IS_CLUSTERED = INDEXPROPERTY(i.id, i.name, 'IsClustered'),
        IS_UNIQUE = INDEXPROPERTY(i.id, i.name, 'IsUnique'),
        FILE_GROUP = g.GroupName
    FROM
        sysindexes i
    INNER JOIN
        sysfilegroups g
    ON
        i.groupid = g.groupid
    WHERE
        (i.indid BETWEEN 1 AND 254)
        -- leave out AUTO_STATISTICS:
        AND (i.Status & 64)=0
        -- leave out system tables:
        AND OBJECTPROPERTY(i.id, 'IsMsShipped') = 0
end
*/

SELECT
    v.*
FROM
    dbo.vAllIndexes v
INNER JOIN
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
ON
    T.CONSTRAINT_NAME = v.INDEX_NAME
    AND T.TABLE_NAME = v.TABLE_NAME
    AND T.CONSTRAINT_TYPE = 'PRIMARY KEY'
    AND v.COLUMN_LIST = 'Id'
    AND v.IS_CLUSTERED = 0
ORDER BY v.TABLE_NAME

Replies