How do I create a partition using a bit field in SQL Server 2014

Question

I have a table where data is archived. I need to keep this data as it is rarely viewed.

Instead of moving archived data to a separate table, I wanted to create a partition on the Archived field. When I use the designer to partition, the value look all wrong.

The table currently has 42387343 rows (29518148 archived,12869195 active), but this will rapidly increase.

I'm using a Clustered ColumnStore Index

Can / Should / How do I partition on a bit field?

I used this script, but it made the queries much slower:

BEGIN TRANSACTION
CREATE PARTITION FUNCTION [Archived](bit) AS RANGE LEFT FOR VALUES (N'0', N'1')

CREATE PARTITION SCHEME [Archived] AS PARTITION [Archived] TO ([PRIMARY], [PRIMARY],     [PRIMARY])

DROP INDEX [IX_AttributeDataLog] ON [dbo].[AttributeDataLog] WITH ( ONLINE = OFF )

CREATE CLUSTERED INDEX [ClusteredIndex_on_Archived_635544995752812167] ON [dbo].[AttributeDataLog]
(
[Archivable]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [Archived]([Archivable])

DROP INDEX [ClusteredIndex_on_Archived_635544995752812167] ON [dbo].[AttributeDataLog]

CREATE CLUSTERED COLUMNSTORE INDEX [IX_AttributeDataLog] ON [dbo].[AttributeDataLog]
 WITH (DROP_EXISTING = OFF, DATA_COMPRESSION = COLUMNSTORE) ON [Archived]([Archivable])

COMMIT TRANSACTION

Every query will include the IsArchived field (except the queries that run to set IsArchived, and some rarely used historical reports). I would normally put it in its own table, but there are changes that can set archived data, to non archived. These changes can happen at any time.

Answer 1

To get partition elimination, your queries need to be explicit about the bit data type, for example:

...WHERE Archivable = CONVERT(bit, 0)
-- NOT
...WHERE Archivable = 0

Without this, the risk of truncation in the implicit conversion means partition elimination is not applied.

The implicit conversion in the CREATE PARTITION FUNCTION statement is not a contributing factor, because SQL Server will do the conversion to bit for you, once, when the function is created. Still, it would be nicer/better practice to write:

CREATE PARTITION FUNCTION [Archived](bit) 
AS RANGE LEFT 
FOR VALUES
(
    CONVERT(bit, 0), 
    CONVERT(bit, 1)
);

It might be easier all round to avoid these potential issues by making the Archived column an integer instead, with a CHECK constraint to enforce the allowed values.