Newsequentialid error validating
Index fragmentation occurs when a new page is inserted into an existing index, and the index does not have space for the page.
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.
Rows Reserved Space Actual space Index Size Unused Space My Int 1,000,000 12,936 KB 12,864 KB 56 KB 16 KB My Big Int 1,000,000 16,904 KB 16,808 KB 88 KB 8 KB My Guid 1,000,000 34,760 KB 34,552 KB 160 KB 48 KB My Guid Seq 1,000,000 24,968 KB 24,768 KB 176 KB 24 KB And now the critique... BIGINT columns have a numerical range of -2^63 to 2^63, taking up 8 bytes.
UNIQUEIDENTIFIER columns can have 2^122 possible combinations, but store a whopping 16 bytes; this results in noticeably larger table sizes when using a UNIQUEIDENTIFIER over the recommended key data types.
I've written two tests for the UNIQUEIDENTIFIER data type - one to test Identity creation using NEWID() and NEWSEQUENTIALID() respectively. I've added a little bit extra formatting for clarity: Table name No. I think it's safe to say that most developers understand how basic disk allocation works - but often enterprise storage systems are a lot more complex than your developer box. INT columns have a numerical range of -2^31 to 2^31, and store only 4 bytes.
Here's a list of common reasons for using a GUID in your database design: From the above, the only really acceptable scenario is no. There aren't many tables in the world that hold 9.2 quintillion records, but if you happen to have one I can guarantee that choosing a data type for its primary key is the least of your problems (and you probably won't be reading this anyway)!
So here's a list of reasons - with code examples and supporting facts - as to why GUIDs are a bad design choice when it comes to SQL Server identity and key columns.
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.