Home Forums SQL Server 2005 T-SQL (SS2K5) Primary Key creation - before and after bulk insert RE: Primary Key creation - before and after bulk insert

  • If the clus index key is an IDENTITY column, you can create that beforehand; SQL "knows" the INSERTs will always be sequential.

    You may be able to create the clus index beforehand if you make sure the data is sorted in clus key sequence when loaded. That is:

    CREATE TABLE tablename ( ..., UNIQUE CLUSTERED (col1, col2) )

    INSERT INTO tablename

    SELECT col1, col2, ...

    FROM ...

    WHERE ...

    ORDER BY col1, col2

    You will have to test this to be sure.

    Scott Pletcher, SQL Server MVP 2008-2010