|
|
Posted by Stu on 10/02/37 11:55
It's OK to have a clustered index that is seperate from your
nonclustered primary key, even if the two indexes cover the same
columns. In fact, I usually build my indexes in this way in case I
ever have to move the clustered index to a different column and I don't
want to mess with my established foreign key constraints.
That being said, I would simply add the clustered index to each table
and not worry about dropping the pre-existing primary key constraint.
It'll take a while, but it will work.
Stu
pb648174 wrote:
> 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
Navigation:
[Reply to this message]
|