1

I need to load data from a legacy SQL Server database which uses numeric primary keys into a new database with a different schema that uses sequential Guids for primary keys. In the new system the sequential Guids are generated by the application - it uses the Azure SQL db which doesn't support NEWSEQUENTIALID().

For performance reasons and as there is a lot of data restructuring I want to convert using SQL scripts (i.e. loading data directly into SQL and not going via the application) and I am planning to add a new Id column to each legacy table for the Ids and populating the Ids in each legacy table before inserting into the new schema

I found this which helpfully provided this SQL to generate unique sequential Ids which I can use to generate my new Ids

SELECT 
    CAST(CAST(NEWID() AS BINARY(10)) + CAST(GETDATE() AS BINARY(6)) AS UNIQUEIDENTIFIER)

UPDATE sometable 
SET Id = CAST(CAST(NEWID() AS BINARY(10)) + CAST(GETDATE() AS BINARY(6)) AS UNIQUEIDENTIFIER)

But I am a confused why the sequential part of the id is at the end of the Guid - wouldn't that cause index fragmentation?

Would it better to change the SQL to this and have the sequential part of the id at the start?

CAST(CAST(GETDATE() AS BINARY(6)) + CAST(NEWID() AS BINARY(10)) AS UNIQUEIDENTIFIER)

Thanks in advance for any insights into which is better

Community
  • 1
  • 1
RPG II
  • 41
  • 1
  • 4
  • http://sqlblog.com/blogs/alberto_ferrari/archive/2007/08/31/how-are-guids-sorted-by-sql-server.aspx – Martin Smith Apr 21 '16 at 20:09
  • Thanks for the link - I never knew that about sorting Guids - so the original version of the SQL to generate sequential Ids is fine – RPG II Apr 22 '16 at 07:50

0 Answers0