I was recently setting up a SQL membership provider with ASP.Net, and was curious as to how the User ID's were being generated.  Typically if a database table is going to have a large number of rows, you don't want to use NewID() to populate a GUID (aka UniqueIdentifier) primary key.  The problem is noted in the much-referenced article by Jimmy Nilsson where he basically came up with the GUID.Comb to address this.  This is now used by NHibernate to solve the issue of inefficiency of SQL Server GUID PK operations. 

SQL Server 2005 attempts to address this issue through the use of defaulting a GUID used in a PK with the NewSequentialID() default value in place of the old NewID().  Basically, instead of generating a random GUID, the NewSequentialID generates GUIDs in a sequentially increasing fashion.  Here's a good article to explain why this is beneficial.

So back to the subject, I was wondering why this function was not used in the SQL generated for the membership providers by the ASPNet_RegSql tool.  This tool basically generates scripts to set up SQL Server to be used as a membership provider.  It's probably because this tool needs to generate code to be used by both SQL 2000 and SQL 2005 and NewSequentialID only works on 2005.  I wanted to switch the aspnet_Users table to default to NewSequentialID and update the related stored procedures accordingly.

To start:

You should run the ASPNet_RegSQL utility in a way that outputs the script instead of running it directly against the database.  This is done using the sqlexportonly argument.  In the output script, the first thing to do is locate the create statement for the aspnet_Users table.  Here, the default for the UserId column should be changed to NewSequentialID() as highlighted below:

IF (NOT EXISTS (SELECT name
                FROM sysobjects
                WHERE (name = N'aspnet_Users')
                  AND (type = 'U')))
BEGIN
  PRINT 'Creating the aspnet_Users table...'
  CREATE TABLE [dbo].aspnet_Users (
    ApplicationId    uniqueidentifier    NOT NULL FOREIGN KEY REFERENCES [dbo].aspnet_Applications(ApplicationId),
    UserId           uniqueidentifier    NOT NULL PRIMARY KEY NONCLUSTERED DEFAULT NEWSEQUENTIALID(),
    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)
END

Next:

Locate the aspnet_Users_CreateUser stored procedure. This needs to be updated a little more carefully. Because NewSequentialID cannot be used as a standalone function, we have to rely on the default table value to produce the UserID and then use the INSERT...OUTPUT functionality to retrieve the UserID into an output parameter.  This is demonstrated below:

CREATE PROCEDURE [dbo].aspnet_Users_CreateUser
    @ApplicationId    uniqueidentifier,
    @UserName         nvarchar(256),
    @IsUserAnonymous  bit,
    @LastActivityDate DATETIME,
    @UserId           uniqueidentifier OUTPUT
AS
BEGIN
    IF( @UserId IS NOT NULL )
    BEGIN
        IF( EXISTS( SELECT UserId FROM dbo.aspnet_Users
                    WHERE @UserId = UserId ) )
            RETURN -1
    END

    DECLARE @InsertedRows TABLE (UserID UNIQUEIDENTIFIER)

    INSERT dbo.aspnet_Users (ApplicationId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
    OUTPUT INSERTED.UserID INTO @InsertedRows
    VALUES (@ApplicationId, @UserName, LOWER(@UserName), @IsUserAnonymous, @LastActivityDate)

    SELECT @UserId = UserID FROM @InsertedRows

    RETURN 0
END

Finally:

If roles are being used, the dbo.aspnet_UsersInRoles_AddUsersToRoles stored procedure must be updated.  This is scripted as a big SQL string.  About 3/4 of the way down, you will see a call to insert into dbo.aspnet_Users.  The only change here is to remove the UserID field from the list of fields to insert and remove the corresponding NewID() call that populates it.  This forces the insert to use the table default value, which is the NewSequentialID().

INSERT dbo.aspnet_Users (ApplicationId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
  SELECT @AppId, Name, LOWER(Name), 0, @CurrentTimeUtc
  FROM   @tbNames

 

That's it!  Now just run the resulting script on your database!  Now the provider is using NewSequentialID instead of NewID.  I doubt this matters much if you have a few hundred user accounts, but a few thousand could make a difference here....and why not because it's so much fun.

Here's a link where you can download an alternate script that uses the Guid.Comb function instead of NewSequentialID.

kick it on DotNetKicks.com