Есть ли способ конвертировать некластеризованный индекс в Первичный ключ к кластерному? (SQL Server 2008)

Ответ 1

Если ваш существующий PK ссылается на многие другие таблицы, вы потратите много утомительных и подверженных ошибкам минут, нажимая script, чтобы удалить все ссылки FK и заново создать их.

SQL Server Management Studio может сделать это за вас. То, что вы, возможно, не поняли, состоит в том, что вы можете иметь только один кластерный индекс в таблице, потому что кластеризованный индекс представляет собой физический порядок строк; это означает, что вам сначала нужно вызвать кластерный индекс и отключить кластеризацию. Тогда и только тогда вы можете подтянуть еще один указатель и включить кластеризацию.

Вам нужно сделать это из конструктора таблиц, затем щелкните правой кнопкой мыши и выберите Indexes/Keys.... Сначала найдите существующий кластерный индекс (возможно, первичный ключ) и измените Create as Clustered на Нет. Затем перейдите к другому индексу и измените Create as Clustered на Да. Если таблица большая, операция может быть отключена во время сохранения; вы можете обойти это, если SSMS генерирует изменение script (щелкните правой кнопкой мыши по дизайну после изменения индексов, и вы увидите эту опцию). Затем вы можете запустить этот script в окне запроса без тайм-аута.

Если вы посмотрите на это изменение script, вы увидите всю работу, которую он делает, создавая промежуточные таблицы и переключающие ключи; это боль, чтобы написать это вручную. Пусть SSMS сделает это за вас.

Ответ 2

Вы не можете преобразовать его на место - вам нужно сначала отказаться от ограничения первичного ключа (который также автоматически отключит некластеризованный индекс "позади" ограничения первичного ключа), а затем заново создаст его как кластерный индекс

ALTER TABLE dbo.YourTable
DROP CONSTRAINT PK_YourTable

а затем заново создайте его как кластеризованное:

ALTER TABLE dbo.YourTable
ADD CONSTRAINT PK_YourTable
PRIMARY KEY CLUSTERED (YourPKField)

Ответ 3

Фактически вы можете изменить некластеризованный PK и сделать его Clustered с помощью этого синтаксиса

CREATE UNIQUE CLUSTERED INDEX [PK_Customer] on Customer(CustomerID) WITH DROP_EXISTING

Сначала нужно очистить свои ограничения: S

Ответ 4

Вам также придется отказаться от ограничений FK, изменить PK и затем воссоздать ограничения FK после того, как новый PK будет на месте. Ограничения внешнего ключа требуют, чтобы ссылочный ключ был уникальным, первичный ключ по определению уникален, поэтому отбрасывание PK недопустимо, а ограничения FK находятся на месте, ссылаясь на PK.

Переход к кластерному индексу переписывает таблицу. Каждый раз, когда я рассматриваю переход к/из кластерного индекса или изменение кластерного индекса, я бы пересмотрел его необходимость и выбор ключей для кластерного индекса - особенно если он не будет уникальным или увеличивающимся (например, дата-время для метки времени), Помните, что кластеризованный индекс на самом деле не является индексом - это порядок данных на страницах. Вот почему кластеризация на увеличивающемся ключе помогает при разбиении страниц при добавлении данных в таблицу.

Ответ 5

Из-за надзора за настройками модератора данных, проведенного давным разработчиком много лет назад, у меня была целая база данных, полная таблиц с некластеризованными индексами на ПК и без кластеризованных индексов. Я нашел статью Qaru с кодом, который пропускал FK, сбрасывал ограничение PK; повторно добавили ограничение PK в качестве кластеризованного индекса, а затем снова добавили FK: Изменить первичный ключ с некластеризованного на кластеризованный Я пересмотрел этот код, чтобы включить в него несколько замечательных комментариев Qaru; сделал цикл по всем таблицам в моей БД (кроме двух, которые я исключил из кода); обнаружил, что он создал ненадежные внешние ключи (по sp_Blitz); исправил это и вуаля! приведенный ниже код, кажется, работает достаточно хорошо для добавления кластеризованных индексов ко всем таблицам кучи в БД - если все таблицы в БД имеют идентификаторы поля идентификаторов PK, кроме исключенных.

/* Script to take tables with a primary key but not a clustered index to being tables
with a primary key and a clustered index on the PK field.  Foreign keys are dropped and recreated.
Much borrowing from a great piece of code from Qaru

What if you have a table or two you don't want a clustered index on?
Lines 38 and 39 are for tables you want excluded from the process.  Input your own table names here,
if any.

After it finishes, test the altered db against an unaltered version of the db and check
that it recreated all constraints/FKs.  The only diff should be the clustered index vs non-clustered.

Revisions made by Ed Z 10/2019:
Original script processes one table only - I wrapped it in a loop that processes all heap tables in a database.
Original script prints the commands - this script both prints them and runs them.  If you want to be safe - 
just comment out the 5 exec statements and inspect the printed commands; then run them if you wish.  
Original script creates untrusted foreign keys (per sp_Blitz) - this script creates trusted foreign keys.
*/

SET NOCOUNT ON;

DECLARE @PKTableName VARCHAR(100), 
        @PKName varchar(100),
        @FKName varchar(100),
        @sql varchar(max),
        @PKcolumnName varchar(30),
        @table VARCHAR(100),
        @FKColumnName VARCHAR(100)

SELECT @PKTableName = ''

-- loop thru all the Heap tables; assumes they have a primary key identity field.
WHILE 1 = 1
BEGIN
    select @PKTableName = MIN(so.name) 
    from sys.indexes si inner join sys.objects so on si.object_id = so.object_id
    where so.is_ms_shipped = 0  
    and si.type_desc = 'Heap'
    and so.name not like '<a table name you want excluded>'  -- exclude a couple of tables that lack a unique key
    and so.name not like '<a table name you want excluded>'
    and so.name > @PKTableName

    IF @PKTableName is null
        BREAK

    --initialize
    select @PKName = '', @FKName = '', @PKcolumnName = '', @sql = '', @table = '', @FKColumnName = ''

    IF  EXISTS (SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(N'[dbo].[FKAgainstTableList]'))
    BEGIN 
        DROP TABLE FKAgainstTableList
    END
    --CREATE TABLE FKAgainstTableList (ForeignKey VARCHAR(30),[Table] VARCHAR(30))
    --SET @PKTableName = 'MYTABLE'
    set @PKName = (SELECT name FROM sys.indexes WHERE OBJECT_NAME(object_id) = @PKTableName AND is_primary_key = 1)
    set @PKcolumnName = (SELECT name FROM sys.columns WHERE OBJECT_NAME(object_id) = @PKTableName AND is_identity =1)
    /* OR use, if you are not sure there is only one column in the primary key or for primary keys that are not identity fields:
    SELECT @PKcolumnName=column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 AND table_name = @PKTableName
    */
    -- PRINT @PKcolumnName  -- debug

     SELECT  OBJECT_NAME(sys.foreign_key_columns.parent_object_id) [Table],sys.columns.name [FKColumnName],sys.foreign_keys.name [FKName] 
        INTO FKAgainstTableList
        FROM sys.foreign_keys INNER JOIN sys.foreign_key_columns 
        ON sys.foreign_keys.object_id = sys.foreign_key_columns.constraint_object_id
        INNER JOIN sys.columns ON sys.columns.object_id = sys.foreign_keys.parent_object_id AND sys.columns.column_id = sys.foreign_key_columns.parent_column_id
        WHERE OBJECT_NAME(sys.foreign_keys.referenced_object_id) = @PKTableName

    DECLARE table_cur1 CURSOR  FOR
        SELECT  * FROM FKAgainstTableList

        PRINT @sql

    -------------------------------Disable constraint on FK Tables
    OPEN table_cur1
    FETCH NEXT FROM table_cur1 INTO @table,@FKColumnName,@FKName
    WHILE   @@FETCH_STATUS = 0
        BEGIN
            SET @sql ='ALTER TABLE '[email protected]+' DROP CONSTRAINT '+ @FKName
            PRINT @sql
            EXEC(@sql)
            FETCH NEXT FROM table_cur1 INTO @table,@FKColumnName,@FKName
        END
    CLOSE table_cur1
    DEALLOCATE table_cur1
    --------------------------------DROP AND recreate CLUSTERED pk
    IF  EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(@PKTableName) AND name = @PKName)
    BEGIN
        SET @sql = 'ALTER TABLE '[email protected]+' DROP CONSTRAINT '+ @PKName
        PRINT @sql
        EXEC(@sql)
    END
    SET @sql = 'ALTER TABLE '[email protected] +' ADD  CONSTRAINT '[email protected]+' PRIMARY KEY CLUSTERED ('[email protected]+' ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]'
    PRINT(@sql)
    EXEC(@sql)
    --------------------------------Enable FK constraints on FK tables.
    DECLARE table_cur2 CURSOR  FOR
        SELECT  * FROM FKAgainstTableList
    OPEN table_cur2
    FETCH NEXT FROM table_cur2 INTO @table,@FKColumnName,@FKName
    WHILE   @@FETCH_STATUS = 0
        BEGIN
            SET @sql = 'ALTER TABLE '[email protected]+' WITH NOCHECK ADD  CONSTRAINT  '+ @FKName+' FOREIGN KEY(['[email protected]+'])
            REFERENCES ['[email protected]+'] (['[email protected]+'])'
            PRINT(@sql)
            EXEC(@sql)

--          SET @sql = 'ALTER TABLE '[email protected]+' CHECK CONSTRAINT  '[email protected]  -- this created untrusted foreign keys
            SET @sql = 'ALTER TABLE '[email protected]+' WITH CHECK CHECK CONSTRAINT  '[email protected]  -- assumes all FK relations are in order
            PRINT(@sql)
            EXEC(@sql)

            FETCH NEXT FROM table_cur2 INTO @table,@FKColumnName,@FKName

             END
    CLOSE table_cur2
    DEALLOCATE table_cur2
    DROP TABLE FKAgainstTableList
END  -- end while loop

SET NOCOUNT OFF