3

I am utilizing partition schemes in order to logically isolate data based on a TenantId column. TenantId is the partition column and clustering index across all tables that require tenant isolation. I would like to create unique, non-clustered indexes on the [Tenant] table that also holds TenantAlias and TenantName. I cannot create the unique indexes on the partition scheme unless they are a part of the partitioning column as a composite primary key. This fact leads me to worry about performance implications (especially in larger contexts), but this may be a misguided thought.

Even with the potential performance implications, given that the combination of TenantId, TenantAlias, and TenantName will always be a unique composition, I'm falling towards including the non-partitioning columns onto the clustered index. However, I would like to know if it's possible otherwise, if there are detriments to not including it in the composite key, or if it's better to avoid placing them on the partition scheme at all.

For optimal context, let's set up the security schema, file group, partition function, and partition scheme to a database called [IsolationExample].

IF (SCHEMA_ID('Auth') IS NULL)
BEGIN
    EXEC ('CREATE SCHEMA [Auth] AUTHORIZATION [dbo]')
END;
GO

ALTER DATABASE [IsolationExample]
ADD FILEGROUP [Auth]
GO

ALTER DATABASE [IsolationExample]
ADD FILE (
    NAME = N'IsolationExample_Auth', FILENAME = N'E:\MSSQL\Data\IsolationExample_Auth.ndf', SIZE = 512MB , MAXSIZE = UNLIMITED, FILEGROWTH = 512MB
    )
TO FILEGROUP [Auth]
GO

IF EXISTS (SELECT 1 FROM sys.partition_schemes WHERE [name] = 'PS_Tenant_Isolation') DROP PARTITION SCHEME [PS_Tenant_Isolation];
IF EXISTS (SELECT 1 FROM sys.partition_functions WHERE [name] = 'PF_Tenant_Isolation') DROP PARTITION FUNCTION [PF_Tenant_Isolation];

IF NOT EXISTS (SELECT 1 FROM sys.partition_functions WHERE [name] = 'PF_Tenant_Isolation')
BEGIN
CREATE PARTITION FUNCTION [PF_Tenant_Isolation] ([int])
    -- RANGE RIGHT values will be SPLIT based on external logic to add the correct values (1,2,3 ...) that corresponds with [Tenant].[TenantId].
    AS RANGE RIGHT FOR VALUES (0);
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.partition_schemes WHERE [name] = 'PS_Tenant_Isolation')
BEGIN
CREATE PARTITION SCHEME [PS_Tenant_Isolation]
    AS PARTITION [PF_Tenant_Isolation]
    ALL TO ([Auth]);
END;
GO

Option 1

The first attempted DDL operation does not work:

DROP TABLE IF EXISTS [Auth].[Tenant];
IF OBJECT_ID('[Auth].[Tenant]', 'U') IS NULL
BEGIN
    CREATE TABLE [Auth].[Tenant] (
        [TenantId] [int] IDENTITY(1,1) NOT NULL
        ,[TenantAlias] [varchar](3) NOT NULL
        ,[TenantName] [varchar](256) NOT NULL
        ,CONSTRAINT [PK_Tenant_TenantId] PRIMARY KEY CLUSTERED ([TenantId] ASC)
        ,CONSTRAINT [UQ_Tenant_TenantAlias] UNIQUE NONCLUSTERED ([TenantAlias] ASC)
        ,CONSTRAINT [UQ_Tenant_TenantName] UNIQUE NONCLUSTERED ([TenantName] ASC)
    ) ON [PS_Tenant_Isolation]([TenantId]);
END;
GO

Column 'TenantId' is partitioning column of the index 'UQ_Tenant_TenantName'. Partition columns for a unique index must be a subset of the index key.

Option 2

If I place the DDL operations outside of the scope of the CREATE TABLE operation, it also tosses the same error.

DROP TABLE IF EXISTS [Auth].[Tenant];
IF OBJECT_ID('[Auth].[Tenant]', 'U') IS NULL
BEGIN
    CREATE TABLE [Auth].[Tenant] (
        [TenantId] [int] IDENTITY(1,1) NOT NULL
        ,[TenantAlias] [varchar](3) NOT NULL
        ,[TenantName] [varchar](256) NOT NULL
        ,CONSTRAINT [PK_Tenant_TenantId] PRIMARY KEY CLUSTERED ([TenantId] ASC)
    ) ON [PS_Tenant_Isolation]([TenantId]);
    CREATE UNIQUE NONCLUSTERED INDEX [UQ_Tenant_TenantAlias] ON [Auth].[Tenant]([TenantAlias] ASC) ON [PS_Tenant_Isolation]([TenantId]);
    CREATE UNIQUE NONCLUSTERED INDEX [UQ_Tenant_TenantName] ON [Auth].[Tenant]([TenantName] ASC) ON [PS_Tenant_Isolation]([TenantId]);
END;
GO

Column 'TenantId' is partitioning column of the index 'UQ_Tenant_TenantName'. Partition columns for a unique index must be a subset of the index key.

*Option 3

If I do not place the unique indexes on the partition scheme, the DDL operation succeeds, but as one would expect it places the indexes on the specified file group directly. In my mind, this defeats the entire purpose of the partition scheme as we cannot leverage the performance benefits of the partitioning column. But I may be wrong on this and would like to be corrected if so.

DROP TABLE IF EXISTS [Auth].[Tenant];
IF OBJECT_ID('[Auth].[Tenant]', 'U') IS NULL
BEGIN
    CREATE TABLE [Auth].[Tenant] (
        [TenantId] [int] IDENTITY(1,1) NOT NULL
        ,[TenantAlias] [varchar](3) NOT NULL
        ,[TenantName] [varchar](256) NOT NULL
        ,CONSTRAINT [PK_Tenant_TenantId] PRIMARY KEY CLUSTERED ([TenantId] ASC)
    ) ON [PS_Tenant_Isolation]([TenantId]);
    CREATE UNIQUE NONCLUSTERED INDEX [UQ_Tenant_TenantAlias] ON [Auth].[Tenant]([TenantAlias] ASC) ON [Auth];
    CREATE UNIQUE NONCLUSTERED INDEX [UQ_Tenant_TenantName] ON [Auth].[Tenant]([TenantName] ASC) ON [Auth];
END;
GO

Tenant Isolation - Option 3

Option 4 - Composite Key

Lastly, if I include them as a composite key, the DDL operation works and places them on the partition scheme. This is the "desired" end behavior where the indexes are on the partition scheme, but I want to know if it is appropriate to include them as a composite key on the clustered index at all.

DROP TABLE IF EXISTS [Auth].[Tenant];
IF OBJECT_ID('[Auth].[Tenant]', 'U') IS NULL
BEGIN
    CREATE TABLE [Auth].[Tenant] (
        [TenantId] [int] IDENTITY(1,1) NOT NULL
        ,[TenantAlias] [varchar](3) NOT NULL
        ,[TenantName] [varchar](256) NOT NULL
        ,CONSTRAINT [PK_Tenant_TenantId] PRIMARY KEY CLUSTERED ([TenantId] ASC, [TenantAlias] ASC, [TenantName] ASC)
    ) ON [PS_Tenant_Isolation]([TenantId]);
END;
GO

Am I better served with option 3 or option 4, or perhaps an option 5 that I am unaware of?

I would also like to think outside of the context of purely this [Tenant] table and carry the same general understanding onto other tables that require additional unique keys as well. For example, a [Document] table with potentially billions of rows, where in addition to TenantId there is a bigint identity column of DocumentId as well as a unique DocumentBatchName.

  • You'll get the partitioning benefits indirectly using option 3 when your indexes are not covering indexes - the RID lookups will still benefit from knowing which partition to go to. I use the composite key approach 99% of the time though, and you're right to build it such that the partitioning key is on the left - doing a "DocumentBatchName, DocumentId" sort of deal bit me pretty hard, initially, because I am an idiot and didn't think my indexes through – Avarkx Mar 13 '18 at 15:36
3

The question for me becomes more about what are you trying to make unique, versus what are you trying to partition.

In option 4, adding the alias and name to the primary key puts you in an akward situation where only the ID, Alias, Name tuple is actually unique:

CREATE TABLE [Auth].[Tenant] (
    [TenantId] [int] IDENTITY(1,1) NOT NULL
    ,[TenantAlias] [varchar](3) NOT NULL
    ,[TenantName] [varchar](256) NOT NULL
    ,[Other] INTEGER
    ,CONSTRAINT [PK_Tenant_TenantId] PRIMARY KEY CLUSTERED ([TenantId] ASC, [TenantAlias] ASC, [TenantName] ASC)
) ON [PS_Tenant_Isolation]([TenantId]);

INSERT  INTO Auth.Tenant ( TenantAlias, TenantName )
VALUES  ( 'A', 'Apple' ),
        ( 'A', 'Apple' ),
        ( 'B', 'Banana' );

SELECT  *
FROM    Auth.Tenant 

In a scenario where the Other column above represents a bunch of columns containing tenant data, you'd be duplicating a ton of entirely unnecessary data in the clustered key, of all places, with TenantName being part of that index. Moaning about size issues aside, you've also got a much bigger problem, going about it this way:

UPDATE  Auth.Tenant
    SET TenantName = 'Orange'
WHERE   TenantID = 2;

SELECT  *
FROM    Auth.Tenant 

DROP TABLE Auth.Tenant;

It's really just a very weak model at this point.

In order to address the uniqueness requirement and gain the benefits of partitioning as well, you're really looking at doing your UNIQUE work on the Auth filegroup, then partitioning your data via the PS_Tenant_Isolation function separately.

IF ( OBJECT_ID( 'Auth.Tenant', 'U' ) IS NULL )
BEGIN
    --DROP TABLE Auth.Tenant;
    CREATE TABLE Auth.Tenant
    (
        TenantID                INTEGER IDENTITY( 1, 1 ) NOT NULL,
        TenantAlias             VARCHAR( 3 ) NOT NULL,
        TenantName              VARCHAR( 256 ) NOT NULL
    );

    ALTER TABLE Auth.Tenant
    ADD CONSTRAINT PK__Tenant
        PRIMARY KEY CLUSTERED ( TenantID )
    ON  [Auth];

    ALTER TABLE Auth.Tenant
    ADD CONSTRAINT UQ__Tenant__TenantAlias
        UNIQUE ( TenantAlias )
    ON  [Auth];

    ALTER TABLE Auth.Tenant
    ADD CONSTRAINT UQ__Tenant_TenantName
        UNIQUE ( TenantName )
    ON  [Auth];

    INSERT  INTO Auth.Tenant ( TenantAlias, TenantName )
    VALUES  ( 'A', 'Apple' ),
            ( 'B', 'Banana' );
END;
GO

IF ( OBJECT_ID( 'Auth.TenantData', 'U' ) IS NULL )
BEGIN
    --DROP TABLE Auth.TenantData;
    CREATE TABLE Auth.TenantData
    (
        TenantDataID            INTEGER IDENTITY( 1, 1 ) NOT NULL,
        TenantID                INTEGER NOT NULL,
        Other                   BIT
    );

    ALTER TABLE Auth.TenantData
    ADD CONSTRAINT PK__TenantData
        PRIMARY KEY CLUSTERED ( TenantID, TenantDataID )
    ON  [PS_Tenant_Isolation]( TenantID );

    ALTER TABLE Auth.TenantData
    ADD CONSTRAINT FK__TenantData__Tenant
        FOREIGN KEY ( TenantID )
        REFERENCES Auth.Tenant ( TenantID );

    INSERT  INTO Auth.TenantData ( TenantID, Other )
    VALUES  ( 1, 1 ),
            ( 2, 0 );
END;
GO

SELECT  *
FROM    Auth.Tenant;

SELECT  *
FROM    Auth.TenantData;

SELECT  ObjectName = OBJECT_NAME( ps.object_id ), 
        IndexName = i.name,
        DataSpaceName = ds.name,
        ps.partition_number, ps.row_count   
FROM    sys.dm_db_partition_stats ps
INNER JOIN sys.indexes i
    ON  ps.object_id = i.object_id
    AND ps.index_id = i.index_id
INNER JOIN sys.data_spaces ds
    ON  i.data_space_id = ds.data_space_id
WHERE   ps.object_id IN (   SELECT  OBJECT_ID( 'Auth.Tenant' )
                            UNION ALL
                            SELECT  OBJECT_ID( 'Auth.TenantData' ) );

DROP TABLE Auth.TenantData;
DROP TABLE Auth.Tenant;

Now your smallish "lookup" table for your Tenants can have properly unique indexes, and the data you are trying to partition for those tenants can benefit from the partitioning schema.

Edit: As per the comments, I'll add that non-aligned indexes do not necessarily lose all the benefits of a partitioning schema, as RID lookups can most certainly take advantage of knowing what partition they're in.

  • Good point on option 4. I failed to recognize that this would not net me uniqueness. However, given that the table [Tenant] is essentially the "lookup" table and used as the basis for partitioning (a trigger on the table alters the range right value of the partition function), its unique in only the TenantId, TenantAlias, and TenantName columns. There are only two additional columns, one TenantActive [bit] and TenantTier [tinyint]. The additional reliance on a lookup table in [TenantData] seems to add more redundancy, and would be another consideration in EF Core. – PicoDeGallo Mar 13 '18 at 17:29
  • I think, based on your comment to the OP, I may further delve into option 3 for this specific model. The mystery on this option was whether or not SQL Server would be able to properly leverage the partition scheme. – PicoDeGallo Mar 13 '18 at 17:30
  • @PicoDeGallo That should be fine, I had to add a bunch of other information to my answer to illustrate potential pitfalls here, but the take-away can certainly be that the non-aligned indexes don't necessarily lose all benefit of the partitioning functions, absolutely! – Avarkx Mar 13 '18 at 17:45
  • Absolutely, thank you for the further insight and your time. Fresh eyes are always appreciated. – PicoDeGallo Mar 13 '18 at 18:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.