I had recently completed Creating Partitioning and adding new index for table with 664 million records to improve query performance for Dashboard usage
--PART1
-- Creating a Partition Function
CREATE PARTITION FUNCTION IntegerPartitionFunction (INT)
AS RANGE LEFT FOR VALUES (2019, 2020, 2021, 2022, 2023, 2024, 2025, 2026, 2027, 2028, 2029 );
--The result for this RANGE LEFT assignment is
CREATE PARTITION SCHEME IntegerPartitionScheme
AS PARTITION IntegerPartitionFunction
ALL TO ([PRIMARY]) -- Because all data currently in One File group
GO
--CREATE PARTITION SCHEME IntegerPartitionScheme
--AS PARTITION IntegerPartitionFunction
--TO (
--[PARTITION_FG1], [PARTITION_FG2], [PARTITION_FG3], [PARTITION_FG4],
--[PARTITION_FG5], [PARTITION_FG6], [PARTITION_FG7], [PARTITION_FG9],
--[PARTITION_FG9], [PARTITION_FG10], [PARTITION_FG11] ,
--[PRIMARY])
-- check Partition schemas
SELECT ps.name, pf.name, boundary_id,value
FROM sys.partition_schemes ps
INNER JOIN sys.partition_functions pf ON pf.function_id=ps.function_id
INNER JOIN sys.partition_range_values prf ON pf.function_id=prf.function_id
GO
--PART2
--Index #1
CREATE NONCLUSTERED INDEX IX_FACT_1
ON dbo.FACT_SALES_DATA
(
[YearId]
)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON IntegerPartitionScheme(YearId)
GO
--Index #2
CREATE NONCLUSTERED INDEX IX_FACT_2
ON dbo.FACT_SALES_DATA
(
[SId] ASC ,
[YearId] ASC ,
[MonthId] ASC
)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON IntegerPartitionScheme(YearId)
GO