-- Insert sample data INSERT INTO dbo.SalesFact (SaleDate, ProductID, CustomerID, Amount, Quantity) SELECT DATEADD(day, number, '2023-12-31'), (number % 100) + 1, (number % 50) + 1, RAND(CHECKSUM(NEWID())) * 1000, (number % 10) + 1 FROM master..spt_values WHERE type = 'P' AND number < 50000;
-- Get max partition number SELECT @MaxPartition = MAX(partition_number) FROM sys.partitions WHERE object_id = @ObjectID; sql server 2012 enterprise
IF @ObjectID IS NULL BEGIN RAISERROR('Table not found', 16, 1); RETURN; END; -- Insert sample data INSERT INTO dbo
-- Create clustered columnstore index on error log (Enterprise 2012+) CREATE CLUSTERED COLUMNSTORE INDEX CCI_ErrorLog ON dbo.ErrorLog WITH (MAXDOP = 4, COMPRESSION_DELAY = 0); GO -- Create partitioned table (Enterprise feature) CREATE PARTITION FUNCTION pf_DateRange (DATETIME) AS RANGE RIGHT FOR VALUES ( '2024-01-01', '2024-04-01', '2024-07-01', '2024-10-01', '2025-01-01' ); CREATE PARTITION SCHEME ps_DateRange AS PARTITION pf_DateRange ALL TO ([PRIMARY]); Quantity) SELECT DATEADD(day
EXEC dbo.sp_add_jobstep @job_name = N'Enterprise_PartitionOptimization', @step_name = N'Optimize Sales Partition', @command = N'EXEC dbo.Enterprise_OptimizeTablePartitions ''dbo'', ''SalesFact'', ''PAGE'', 4, 5;';