Database Reference
In-Depth Information
As an alternative solution, you may consider creating a composite index with two columns
(InstallationId,
Unique_Id_Within_Installation).
The combination of those two columns guarantees uniqueness across multiple
installations and databases, similar to that of uniqueidentifiers. You can use integer identity or sequence to generate
Unique_Id_Within_Installation
value, which will reduce the fragmentation of the index.
In cases where a composite index is not desirable, you can create a system-wide unique value by using a
byte-mask on the
bigint
column. High four bytes can be used as
InstallationId
. Low four bytes can be used as
Unique_Id_Within_Installation
value.
Another common use-case is security, where uniqueidentifier is used as a security token or a random object id.
One possible improvement in this scenario is creating a calculated column using the
CHECKSUM()
function, indexing it
afterwards without creating the index on uniqueidentifier column. The code is shown in Listing 6-6.
Listing 6-6.
Using CHECKSUM(): Table structure
create table dbo.Articles
(
ArticleId int not null identity(1,1),
ExternalId uniqueidentifier not null
constraint DEF_Articles_ExternalId
default newid(),
ExternalIdCheckSum as checksum(ExternalId),
/* Other Columns */
);
create unique clustered index IDX_Articles_ArticleId
on dbo.Articles(ArticleId);
create nonclustered index IDX_Articles_ExternalIdCheckSum
on dbo.Articles(ExternalIdCheckSum);
■
Tip
You do not need to persist a calculated column in order to index it.
Even though the
IDX_Articles_ExternalIdCheckSum
index is going to be heavily fragmented, it will be more
compact as compared to the index on the uniqueidentifier column (a 4 bytes key versus 16 bytes). It also improves the
performance of batch operations because of faster sorting, which also requires less memory to proceed.
One thing that you must keep in mind is that the result of the
CHECKSUM()
function is not guaranteed to be
unique. You should include both predicates to the queries as shown in Listing 6-7.
Listing 6-7.
Using CHECKSUM(): Selecting data
select ArticleId /* Other Columns */
from dbo.Articles
where checksum(@ExternalId) = ExternalIdCheckSum and ExternalId = @ExternalId
■
You can use the same technique in cases where you need to index string columns larger than 900 bytes, which
is the maximum size of a nonclustered index key. even though the index on the calculated column would not support
range scan operations, it could be used for singleton lookups.
Tip
