Using SQL Server 2016+
I have a products table that requires partioning. It currently has a primary Identity key which is used as part of a full text index search. We need to partition the table a a field that is not the primary key ( a division id ) to aid with importing data and seperating data out onto multiple drives. Example below is a simple case without the partitions all on Primary but enough to demonstrate.
DROP TABLE IF EXISTS dbo.Products
BEGIN TRY DROP PARTITION SCHEME division_id_scheme END TRY BEGIN CATCH END CATCH
BEGIN TRY DROP PARTITION FUNCTION division_id_function END TRY BEGIN CATCH END CATCH
BEGIN TRY DROP FULLTEXT CATALOG ProductNames END TRY BEGIN CATCH END CATCH
CREATE PARTITION FUNCTION division_id_function ( INT ) AS RANGE LEFT FOR VALUES ( 1, 2, 3 )
CREATE PARTITION SCHEME division_id_scheme AS PARTITION division_id_function ALL TO ( [PRIMARY] )
CREATE TABLE dbo.Products ( ID INT IDENTITY(1, 1)
, Catalogueref NVARCHAR(50) NOT NULL
, ProductName NVARCHAR(255) NOT NULL
, DivisionID INT NOT NULL
, CONSTRAINT pk_Products PRIMARY KEY ( DivisionID, ID )) ON division_id_scheme (DivisionID);
INSERT INTO dbo.Products ( Catalogueref, ProductName, DivisionID )
VALUES ( N'ABC123', N'Product Apple', 1 )
, ( N'ABC124', N'Product Ball', 1 )
, ( N'ABC125', N'Product Cat', 2 )
, ( N'ABC126', N'Product Dog', 2 )
, ( N'ABC127', N'Product Egg', 3 )
, ( N'ABC128', N'Product Fox', 3 )
, ( N'ABC129', N'Product Grape', 3 )
SELECT * FROM dbo.Products
This creates and partions the table exactly as expected. The issue comes when we try to create the full text index. The full text index requires a unique key that is defined against a single column.
CREATE FULLTEXT CATALOG ProductNames
--This Fails because pk_Products is not a single column unique index
CREATE FULLTEXT INDEX ON dbo.Products (ProductName) KEY INDEX pk_Products ON ProductNames
Msg 7653, Level 16, State 2, Line 29
'pk_Products' is not a valid index to enforce a full-text search key. A full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defined on a non-deterministic or imprecise nonpersisted computed column, does not have a filter, and has maximum size of 900 bytes. Choose another index for the full-text key.
However we cannot create a unique key against a single column as we require two columns in the primary key for partitioning.
-- This fails because unique index must be a subset of the index (which is multi-column)
DROP INDEX IF EXISTS ui_CatRef ON dbo.Products
CREATE UNIQUE NONCLUSTERED INDEX ui_CatRef ON dbo.Products (Catalogueref)
Both DivisionID and ID are required in the primary key as Division ID is not unique, but is how we are partitioning data. We have already tried several options including:
Neither of which work as a unique index must include the partitioned fields.
Any suggestions on how we can partition by the division, but still maintain a primary key for full text searching would be greatfully received.
You can create unique index on primary filegroup to avoid this issue:
CREATE UNIQUE NONCLUSTERED INDEX ui_CatRef ON dbo.Products (id) on [primary]
CREATE FULLTEXT INDEX ON dbo.Products (ProductName) KEY INDEX ui_CatRef ON ProductNames
I gotta say though that this solution feels a bit hacky to me, so please make sure it doesn't lead to any unintended consequences later