Forum Discussion
Partitioning of database tables
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 Frank_keough_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 Frank_keough_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 Frank_keough_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.
