Dynamic SQL Table Partition To Improve Query Performance

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