Geek Answers Handbook

Changing newid () to newsequentialid () in an existing table

Currently we have several tables that use newid () for the primary key. This causes large amounts of fragmentation. Therefore, I would like to modify the column to use newsequentialid () instead.

I assume that existing data will remain rather fragmented, but new data will be less fragmented. This would mean that I have to wait a while before changing the PK index from non-clustered to clustered.

My question is: does anyone have any experience? Is there something that I forgot that I have to be careful?

+4
source share
4 answers

If you switch to sequential commands and at the same time reinstall the index, you will eliminate fragmentation. I don’t understand why you just want to wait until the fragmented page links are rearranged in continuous extents.

As you said, did you take any measurements to show that fragmentation really affects your system? Just looking at the index and seeing that "75% is fragmented" does not mean that it affects the access time. There are many other factors that come into play (life expectancy in the buffer pool, read and write speed, locality of sequential operations, concurrency operations, etc. Etc.). Although switching from guides to sequential ends is usually safe, you can introduce problems yet. For example, you can see a conflicting page title for an intensive OLTP system with an insert because it creates a page with a hot spot where inserts accumulate.

+3
source

You might consider using comb guides , as opposed to newsequentialid.

cast( cast(NewID() as binary(10)) + cast(GetDate() as binary(6)) as uniqueidentifier)

Combined guides are a combination of purely random directions along with the nonrandomness of the current time and time, so that successive generations of comb guides are next to each other and generally in ascending order. Combined hints have various advantages over newsequentialid, including the fact that they are not a black box, that you can use this formula outside the default limit, and that you can use this formula outside of SQL Server.

+4
source

Thank yfeldblum Your simple and concise explanation of GUID COMB really helped me. I really looked to reverse this post: I had to shy away from newsequentialid() because I was trying to migrate db SQL Server 2012 to Azure, and the newsequentialid() function is not supported there.

I managed to change all of my default PK tables to COMB GUID with the following syntax:

 ALTER TABLE [dbo].[Company] ADD CONSTRAINT [DF__Company__Company_ID__72E6D332] DEFAULT (CONVERT([uniqueidentifier],CONVERT([binary](10),newid(),0)+CONVERT([binary](6),getdate(),0),0)) FOR [CompanyId] GO

My SQL2012 db now lives happily in the Azure cloud.

0
source

If it is SQL Server, you generate Guid by calling newid (). This is not suitable for primary keys. Use a solid identifier column for the primary key and make it Guid a surrogate key (and row row column).

-4
source

More articles:


All Articles