Keep your legs warm and indexes narrow

Here comes the change in the schema: row-level security will be no more be based on User Name, but User Local Id.

User Local Id will be integer value, which makes it much better choice for the clustered index.
The comparisons will be faster, the less space will be required, and all the non-clustered indexes will benefit from this change as well, since they all use the clustered index key to refer to the row.

The main database will be slightly modified, so that every user will get integer value automatically assigned.

ALTER TABLE dbo.aspnet_Users

ADD UserLocalId INTEGER IDENTITY NOT NULL;

GO

And 2 covering non-clustered indexes will help the faster retrieval of the user identity information.

CREATE NONCLUSTERED INDEX aspnet_Users_UserNameIdx ON dbo.aspnet_Users (UserName) INCLUDE (UserLocalId)

GO

CREATE NONCLUSTERED INDEX aspnet_Users_UserLocalIdIdx ON dbo.aspnet_Users (UserLocalId) INCLUDE (UserName)

GO

Application will still know nothing about the new column. The user name still be used to get and store the data.

public static SqlCommand PrepareCommand(SqlConnection connection, string commandText)

{

       var command = connection.CreateCommand();

       command.CommandType = CommandType.StoredProcedure;

       command.CommandText = commandText;

       var userNameParam = command.CreateParameter();

       userNameParam.ParameterName = StoredProcedures.Params_UserName;

       userNameParam.SqlDbType = SqlDbType.NVarChar;

       userNameParam.SqlValue = Thread.CurrentPrincipal.Identity.Name;

       command.Parameters.Add(userNameParam);

       return command;

}

2 new UDF's will be used to fetch the user id by name and vice versa.


dbo.GetUserLocalIdByName
dbo.GetUserNameByLocalId

CREATE PROCEDURE dbo.GetCategoriesSP(@UserName NVARCHAR(256))

AS

BEGIN

      SET NOCOUNT ON;

     

      DECLARE @UserLocalId INT;

      SET @UserLocalId = dbo.GetUserLocalIdByName(@UserName);

      SELECT *

      FROM dbo.GetCategories()

      WHERE UserLocalId = @UserLocalId;

END