text: ﻿CREATE TABLE dbo.Resource
  (
      ResourceTypeId              smallint                NOT NULL,
      ResourceId                  varchar(64)             COLLATE Latin1_General_100_CS_AS NOT NULL,
      Version                     int                     NOT NULL,
      IsHistory                   bit                     NOT NULL,
      ResourceSurrogateId         bigint                  NOT NULL,
      IsDeleted                   bit                     NOT NULL,
      RequestMethod               varchar(10)             NULL,
      RawResource                 varbinary(max)          NOT NULL,
      IsRawResourceMetaSet        bit                     NOT NULL DEFAULT 0,
      SearchParamHash             varchar(64)             NULL,
  
      CONSTRAINT PKC_Resource PRIMARY KEY CLUSTERED (ResourceTypeId, ResourceSurrogateId) WITH (DATA_COMPRESSION = PAGE) ON PartitionScheme_ResourceTypeId(ResourceTypeId),
      CONSTRAINT CH_Resource_RawResource_Length CHECK (DATALENGTH(RawResource) > 0)
  )
  
  ALTER TABLE dbo.Resource SET ( LOCK_ESCALATION = AUTO )
  
  CREATE UNIQUE NONCLUSTERED INDEX IX_Resource_ResourceTypeId_ResourceId_Version ON dbo.Resource
  (
      ResourceTypeId,
      ResourceId,
      Version
  )
  ON PartitionScheme_ResourceTypeId(ResourceTypeId)
  
  CREATE UNIQUE NONCLUSTERED INDEX IX_Resource_ResourceTypeId_ResourceId ON dbo.Resource
  (
      ResourceTypeId,
      ResourceId
  )
  INCLUDE -- We want the query in UpsertResource, which is done with UPDLOCK AND HOLDLOCK, to not require a key lookup
  (
      Version,
      IsDeleted
  )
  WHERE IsHistory = 0
  ON PartitionScheme_ResourceTypeId(ResourceTypeId)
  
  CREATE UNIQUE NONCLUSTERED INDEX IX_Resource_ResourceTypeId_ResourceSurrgateId ON dbo.Resource
  (
      ResourceTypeId,
      ResourceSurrogateId
  )
  WHERE IsHistory = 0 AND IsDeleted = 0
  ON PartitionScheme_ResourceTypeId(ResourceTypeId)
  