Partitioning of database tables

Copper Contributor

I have written a query that partitions the tables of a database with a certain criteria.

Tables with more than 50 records.

-------------------------------------

USE [Post]
SET NOCOUNT ON;
BEGIN TRANSACTION
begin try
CREATE PARTITION FUNCTION [PF](datetime2(7)) AS RANGE LEFT FOR VALUES (N'2023-03-20', N'2023-09-22', N'2024-03-19', N'2024-09-21', N'2025-03-20', N'2025-09-22', N'2026-03-20')
CREATE PARTITION SCHEME [PS] AS PARTITION [PF] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY],[PRIMARY], [PRIMARY], [PRIMARY],[PRIMARY])
--delete[] all Table fk dependency
Declare @Fk_Name nvarchar(100),@T_Name nvarchar(100),@S_Name nvarchar(100);
Declare @RT_Name nvarchar(100),@RS_Name nvarchar(100);
Declare @DatabaseName nvarchar(100);
Declare @SQL nvarchar(2000);
Declare @SQL1 nvarchar(2000);
DECLARE @tab AS TABLE (col1 nVARCHAR(500), col2 nvarchar(500),col3 nvarchar(500),col4 nvarchar(500),col5 nvarchar(500),col6 nvarchar(500));
--DECLARE @tab3 AS TABLE (col11 nVARCHAR(100), col12 nvarchar(100),col13 nvarchar(100),col14 nVARCHAR(100), col15 nvarchar(100)); 
DEclare @column1 nvarchar(100);
Declare @Str nVARCHAR(100);
Declare @count as int;
Declare @count_11 as int;
DECLARE @retval nvarchar(100) ; 
----------------------------------Count of Table Candidatefor Partitioning----------------------
Declare @Count_Table as int;
set @Count_Table=(select count (*) from 
(SELECT distinct
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [Table For Partition Candidate]
      , (sPTN.Rows) as pp
FROM 
      sys.objects AS sOBJ
      INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
  and SCHEMA_NAME(sOBJ.schema_id)<>'basicinfo' and 
  sOBJ.name not in
  (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME not in 
    (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%creationdatetime%'))
       )as t where t.pp>50)
   ----------------------------------Start Of Loop-----------------------
if ( @Count_Table>0)
begin
Declare cr1 scroll Cursor  
for 
select distinct [Table_For_Partition_Candidate] from 
(SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [Table_For_Partition_Candidate]
      , (sPTN.Rows) as pp
FROM 
      sys.objects AS sOBJ
      INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
  and SCHEMA_NAME(sOBJ.schema_id)<>'basicinfo' and 
  sOBJ.name not in
  (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME not in 
    (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%creationdatetime%'))
       )as t where t.pp>50
  
Open cr1
         fetch next from cr1 into  @DatabaseName;
while( @Count_Table>0)
   begin
           print '------------------------------------'+@DatabaseName+'------------------------------------'
       
            set  @SQL= 'SELECT distinct fk.name,OBJECT_Schema_NAME(fk.parent_object_id),OBJECT_NAME(fk.parent_object_id),
                        OBJECT_Schema_NAME(fk.referenced_object_id),OBJECT_NAME(fk.referenced_object_id),
                        c_parent.name FROM sys.foreign_keys fk inner join sys.foreign_key_columns fkc 
                        on  fkc.constraint_object_id = fk.object_id iNNER JOIN sys.tables t_parent ON t_parent.object_id = fk.parent_object_id
                        INNER JOIN sys.columns c_parent ON fkc.parent_column_id = c_parent.column_id AND c_parent.object_id = t_parent.object_id 
                        INNER JOIN sys.tables t_child ON t_child.object_id = fk.referenced_object_id INNER JOIN sys.columns c_child
                        ON c_child.object_id = t_child.object_id
                        WHERE fk.referenced_object_id = OBJECT_ID( '''+@DatabaseName+''')';
    INSERT into @tab EXEC sp_executesql @SQL;
------------------                
    
set @Count_11=(select count (*) from @tab);
 Declare crqq scroll Cursor 
for 
 select * from @tab
open crqq
while(@Count_11>0)
begin
fetch next from crqq into   @Fk_Name  ,@S_Name,@T_Name, @RT_Name ,@RS_Name ,@column1 ;
 
    
       -- select * from @tab
         begin try
            Print('ALTER TABLE ['+@S_Name+'].['+@T_Name+'] DROP CONSTRAINT ['+@FK_Name+']');
     -- IF NOT EXISTS(SELECT * FROM sys.indexes WHERE Name = '+@FK_Name+')
            SET @SQL='ALTER TABLE ['+@S_Name+'].['+@T_Name+'] DROP CONSTRAINT ['+@FK_Name+']';
            EXEC sp_executesql @SQL
            end try
            begin catch
            Print('ALTER TABLE ['+@S_Name+'].['+@T_Name+'] DROP CONSTRAINT ['+@FK_Name+']-----Faild');
            end catch
    set @count_11-=1;
            end
            Close crqq
            Deallocate crqq  
            set @retval=(SELECT top(1) name   FROM sys.indexes WHERE object_id = OBJECT_ID( ''+@DatabaseName+'') AND index_id = 1 AND is_primary_key = 1)
           
             if @retval is not Null
              begin
              --print @sql
               begin try
               set @sql='ALTER TABLE '+ @DatabaseName +' DROP CONSTRAINT ['+@retval+'] WITH ( ONLINE = OFF )'
               EXEC sp_executesql @SQL
               print @sql
               end try 
               begin catch
               set @sql='ALTER TABLE '+ @DatabaseName +' DROP CONSTRAINT ['+@retval+'] WITH ( ONLINE = OFF )------Failed'
               print @sql
               end catch
               begin try
               set @SQL='ALTER TABLE '+ @DatabaseName +' ADD  CONSTRAINT['+@retval+'] PRIMARY KEY NONCLUSTERED 
                (
             [Id] ASC
                )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]'
               print @sql
            EXEC sp_executesql @SQL
            end try
            Begin catch
            set @SQL='ALTER TABLE '+ @DatabaseName +' ADD  CONSTRAINT['+@retval+'] PRIMARY KEY NONCLUSTERED 
                (
             [Id] ASC
                )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]-----Failed'
               print @sql
            end catch
              end
            if @retval is  NUll
            set @Retval='EMPTY';
           begin try
           set @SQL='CREATE  CLUSTERED INDEX [ClusteredIndex_on_PS_'+@retval+ '] ON ' + @DatabaseName +'( [CreationDateTime] ) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PS]([CreationDateTime])'
            EXEC sp_executesql @SQL
            print @sql
             set @SQL='DROP INDEX [ClusteredIndex_on_PS_'+@retval+'] ON ' + @DatabaseName
            EXEC sp_executesql @SQL
            print @sql
            --set @SQL='CREATE  CLUSTERED INDEX [ClusteredIndex_on_PS_'+@retval+ '] ON ' + @DatabaseName +'( [CreationDateTime],[ID]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PS]([CreationDateTime])'
            --EXEC sp_executesql @SQL
           -- print @sql
            end try
            begin catch
             set @SQL='CREATE CLUSTERED INDEX [ClusteredIndex_on_PS_'+@retval+'] ON '+@DatabaseName+'
              (
           [CreationDateTime]
              )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PS]([CreationDateTime])-----Faild or'+'CREATE  CLUSTERED INDEX [ClusteredIndex_on_PS_'+@retval+ '] ON ' + @DatabaseName +'( [ID] ) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ONLINE = OFF) ON [PS]([CreationDateTime])-------faild'
             print @sql
            end catch
             Declare p scroll Cursor        
             for(select * from @tab)
               set @count=(select count(col1) from @tab);
             Open p
              while(@count>0)
        begin
      fetch next from p into @Fk_Name  ,@S_Name,@T_Name, @RT_Name ,@RS_Name ,@column1 ;
                   begin try
      SET @SQL='ALTER TABLE ['+@S_Name+'].['+@T_Name+'] WITH CHECK ADD  CONSTRAINT ['+@FK_Name+'] FOREIGN KEY(['+@column1+']) REFERENCES '+@DatabaseName+'([ID])';
      Print('ALTER TABLE ['+@S_Name+'].['+@T_Name+'] WITH CHECK ADD  CONSTRAINT ['+@FK_Name+'] FOREIGN KEY(['+@column1+']) REFERENCES '+@DatabaseName+'([ID])');
     EXEC sp_executesql @SQL
          SET @SQL='ALTER TABLE ['+@S_Name+'].['+@T_Name+']  CHECK   CONSTRAINT ['+@FK_Name+']';
      Print('ALTER TABLE ['+@S_Name+'].['+@T_Name+']  CHECK   CONSTRAINT ['+@FK_Name+']');
                 EXEC sp_executesql @SQL
                 end try
                 begin catch
                 Print('ALTER TABLE ['+@S_Name+'].['+@T_Name+'] WITH CHECK ADD  CONSTRAINT ['+@FK_Name+'] FOREIGN KEY(['+@column1+']) REFERENCES '+@DatabaseName+'([ID])----Faild');
                  Print('ALTER TABLE ['+@S_Name+'].['+@T_Name+']  CHECK   CONSTRAINT ['+@FK_Name+']---Faild');
                 end catch
      set @count-=1;
    end
             Close p
             Deallocate p
             DELETE FROM  @tab where 1=1;
           -----------------------End of Loop------------------------
        fetch next from cr1 into  @DatabaseName;
        set @Count_Table-=1;
           end
Close cr1
Deallocate cr1
 end--if
end try
begin catch
print 'All Operation Faild Because Dont Create PF and PS'
end catch
COMMIT TRANSACTION
 
 
 
 
2 Replies
And what is the problem/issue/question?
Tables with more then 50 records and you want to partition it?
hello.
I partitioned my main table in sql server which has over 12,000,000 records and archived the records before a certain date. My question is, without changing the queries that were written before partitioning this table, if the desired record is not in the partitioned table, will it be automatically searched for that record in the archive table or not? for Exmaple: my Partitioned table is Ordering.Parcel by CreationDateTime datetime(7) partitioned column. I Archived this table and move records before date '20-03-2024' move to Ordering.Parcel_Archived. Now the following query is automatically searched in table Ordering.Parcel_Archived if no record is returned in table Ordering.Parcel or not?
select Id, First_name from Ordering.Parcel where CreationDateTime < '20-03-2024'
it is very difficult to change or add to my written queries in my program.
----
tanks a lot.