Newsequentialid error validating default

Regardless of the reasons for this, I think you'll agree that having to perform index maintenance after 324 new records is pretty unreasonable.

We haven't even touched upon the differences between Internal and External fragmentation, CLUSTERED and NON CLUSTERED indices, and how to set up index specific properties such as fill factors.

I'm using primitive benchmarking techniques, but it still should give you a good idea of what you're up against - I will be inserting 1 million records into each table I test, and each table will have a single column with the data type and / or indices that are being tested.

First off, create a database - I've called mine "test", cos that's how I roll.

A while back I also wrote a script to assist with automated index management which I still find quite handy, though it should be noted that the more complex your database, the more specific you should be about individual indices.

The only thing that's left for me to say on index fragmentation is this: if you absolutely must use a UNIQUEIDENTIFIER as your primary key (you don't, btw), then ALWAYS use NEWSEQUENTIALID() over NEWID().

Pages can be inserted or moved around for lots of different reasons, but the most common are inserting new records, and updating indexed columns in existing records.

However, if your reason for using a GUID is that you want a unique identifier that's difficult to guess, then you're shit out of luck.

Unfortunately NEWSEQUENTIALID() generated identifiers are predictable. NEWSEQUENTIALID() also inherently helps to keep indices contiguous - filling data and index pages fully before creating a new page.

As it stands, this doesn't mean too much - as you'd want to avoid getting to 99% fragmentation anyway (by managing any indices that become more than 5% fragmented - but that's a whole other article).

So, I also ran the above test with 5% and 30% fragmentation values, and both got to 324 records before becoming fragmented.

Leave a Reply