IF @ObjectID IS NULL BEGIN RAISERROR('Table not found', 16, 1); RETURN; END;
EXEC dbo.sp_add_schedule @schedule_name = N'Weekly_Sunday_2AM', @freq_type = 8, @freq_interval = 1, @active_start_time = 20000;
-- 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; sql server 2012 enterprise
SET @ObjectID = OBJECT_ID(@SchemaName + '.' + @TableName);
-- Process each partition SET @PartitionNumber = 1; IF @ObjectID IS NULL BEGIN RAISERROR('Table not found',
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;';
-- 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]); @freq_type = 8
-- Create large fact table with partitioning CREATE TABLE dbo.SalesFact ( SaleID INT IDENTITY(1,1), SaleDate DATETIME, ProductID INT, CustomerID INT, Amount DECIMAL(18,2), Quantity INT, CONSTRAINT PK_SalesFact PRIMARY KEY (SaleID, SaleDate) ) ON ps_DateRange(SaleDate);