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.
Best Answer
To get partition elimination, your queries need to be explicit about the
bit
data type, for example: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 tobit
for you, once, when the function is created. Still, it would be nicer/better practice to write:It might be easier all round to avoid these potential issues by making the
Archived
column aninteger
instead, with aCHECK
constraint to enforce the allowed values.