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

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.

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.

StackExchange Database Administrators Q#86480, answer score: 10