Dynamic SQL Table Partition To Improve Query Performance

In this blog, we learn about SQL dynamic Table Partition.

SQL Table Dynamic partition on Database primary file group

Execute below query and dynamically get Date range for creating partition function & Partition scheme.

-- STEP 1 
create proc Usp_CreateDynamicTablePartiton
as
begin
declare @Primary varchar(max)='[PRIMARY]';
declare @PrimaryCount int=0
declare @Sql nvarchar(max)
declare @colList varchar(max)
set nocount on;
declare @t as table(StartDate datetime,Number int)
insert into @t SELECT 
    CAST(DATEADD(M,3*(NUMBER-1),DATEADD(YYYY,DATEDIFF(YYYY,1,T.TrDate),0)) AS DATE) [QT START DATE], NUMBER AS [QUARTER NAME]
FROM MASTER..SPT_VALUES 
cross apply
(
Select distinct TrtDate from dbo.Tbl_Transaction with(nolock)
) T
WHERE TYPE='P' 
    AND NUMBER BETWEEN 1 AND 6

	;with TblC
	as
	(
	Select distinct StartDate from @t
	)
	--select * from TblC
	Select @primaryCount=(Select distinct count(1) from TblC)

	SELECT
  @colList = STUFF (
    (
      SELECT
        DISTINCT ',' + QUOTENAME(cast(StartDate as date))
		from @t
		FOR XML PATH(''),
        Type
    ).value('.', 'NVARCHAR(MAX)'),
    1,
    1,
    ''
  )
  
  Set  @colList= Replace(@colList,'[','N''')
  Set @colList= Replace(@colList,']','T00:00:00''')
  
  --Select @primaryCount

  Select @Primary= CASE when Replicate('[PRIMARY],',@primaryCount+1) like'%,'
  Then LEFT(Replicate('[PRIMARY],',@primaryCount+1),
  Len(Replicate('[PRIMARY],',@primaryCount+1))-1)
  else Replicate('[PRIMARY],',@primaryCount+1) end

  --Select @Primary
  declare @IndexNum varchar(max)
  Set @indexNum = FORMAT(GETDATE(),'yyyymmddhhmmss')
  
  SET @Sql = 'CREATE PARTITION FUNCTION [Pfn_Transaction](datetime) AS RANGE LEFT FOR VALUES ('+@colList+')'

  SET @Sql = @Sql + ' 
  
  CREATE PARTITION SCHEME [PScheme_Transaction] AS PARTITION [Pfn_Transaction] TO ('+@Primary+')'

  --SELECT @Sql

  -- STEP 2 Uncomment Index And Execute
  SET @Sql = @SQl + ' 

  CREATE CLUSTERED INDEX [ClusteredIndex_on_PScheme_Transaction_'+@IndexNum+'] ON dbo.Tbl_Transaction
(
	[login_date]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PScheme_Transaction]([login_date]) '

SET @Sql= @Sql + ' 

 --DROP INDEX [ClusteredIndex_on_PScheme_Transaction_'+@IndexNum+'] ON dbo.Tbl_Transaction '

--Select @Sql
exec sp_executesql @Sql

--DROP INDEX [ClusteredIndex_on_PScheme_Transaction_637672198925007456] ON dbo.Tbl_Transaction
end