Geek Answers Handbook

Why are primary keys generated by the aspnet_regsql GUID?

I am developing an ASP.NET website in Visual Studio 2010 (SP1, thank you very much). I want to use the built-in membership providers and .NET roles for SQL Server 2008.

I have been developing Microsoft technologies for a very long time and have wiped my elbows with some of the best DBA SQL servers in the business. Each of them told me to avoid GUIDS as primary keys when building a database table that would:

  • You have a very high record rate.
  • Have a large volume of attachments and deletions.

Cause. Because the primary key is a clustered index!

This basically means that every record inserted into a table must obey the constraints of the index. Therefore, if the index is sorted by ASC, the record with the newly created GUID must be physically wedged in the appropriate sequence to the corresponding data table.

This would be great for a table with several thousand records or so. SQL Server would only have to rearrange the position. However, if the data table contains several million records and finds that it should insert a new record on line 216. this would take a considerable amount of time (by web standards). He must physically move all of these lines down to insert a new one.

, - . Microsoft DBS, , GUID ... ASPNET_REGSQL GUID ?

- ? SQL Profiler 2008 , GUIDS ?

+5
2

; , , -, , ID. , - , , .

- , SQL . , - ; , , -, , , , . , SQL " , "; , SQL , , , , .

- , , , , .

+3

GUID . , , , , , , , . GUID - , .

GUIDS ... !

, , .

, , SqlMembershipProvider, , .

SQL script InstallCommon.sql script %WINDIR%\Microsoft.NET\Framework\v4.0.30319:

  CREATE TABLE [dbo].aspnet_Users (
    ApplicationId    uniqueidentifier    NOT NULL FOREIGN KEY REFERENCES [dbo].aspnet_Applications(ApplicationId),
    UserId           uniqueidentifier    NOT NULL PRIMARY KEY NONCLUSTERED DEFAULT NEWID(),
    UserName         nvarchar(256)       NOT NULL,
    LoweredUserName  nvarchar(256)       NOT NULL,
    MobileAlias      nvarchar(16)        DEFAULT NULL,
    IsAnonymous      bit                 NOT NULL DEFAULT 0,
    LastActivityDate DATETIME            NOT NULL)

   CREATE UNIQUE CLUSTERED INDEX aspnet_Users_Index ON [dbo].aspnet_Users(ApplicationId, LoweredUserName)
   CREATE NONCLUSTERED INDEX aspnet_Users_Index2 ON [dbo].aspnet_Users(ApplicationId, LastActivityDate)

, (UserId) PRIMARY KEY NONCLUSTERED CLUSTERED ApplicationId LoweredUserName.

+1

More articles:


All Articles