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.