-- Partition dynamic sql içeren sorgu komutu -- Partition Scheme ve Partition Function drop eden sql komutu SET NOCOUNT ON --Aşağıdaki script aylık partitiona göre yazılmıştır DECLARE @dbname NVARCHAR(100), --database adı @tableName NVARCHAR(100), -- partition yapılacak tablo adı @year int, -- Partition başlangıç yılı @quarter int, -- kaç QUARTER partition yapmak istiyorsunuz. Normalde 4 QUARTER olacağı için. --Artık quarter, sql normalde QUARTER partition yapacağım dediğiniz zaman, 4.quarterdan sonra yeni yılın Quarterına geçiş olur. @totalPartYear int, -- Partition yapılacak toplam yıl sayısı. Kaç yıllık veriyi partitionlamak istiyorsunuz. -- NOT: Programlama dilinde 0 başlangıç sayısıdır. @loopNumA int, -- döngü içinde, yılı kaçar kaçar arttıralım., mesela başlangıç yılı 2012+loopNumA 1 mi olacak, 2 iki mi olacak @filename NVARCHAR(500), -- Partition dosyalarının ekleneceği dosya dizini (path) @initialsize int, -- Dosyanın başlangıç boyutu @filegrowth int, -- Dosyanın otomatik büyüme boyutu. Patition yapılan dosyanın kaç MB kaç MB büyümesini istersin? @sql NVARCHAR(MAX), -- FILE GROUP ve FILE EKLEMEK için sql komut statırı @sql1 NVARCHAR(MAX), -- Partition FUNCTION oluşturulurken kullanılan komut satırı @dateForFunc NVARCHAR(MAX), -- Partition ##dateTmpForpFnc tablosundaki kayıtları yan yana yazdırmak için atanan parametre @boundarySide NVARCHAR(10), -- Partition BOUNDARY direction.(LEFT or RIGHT) @dataType NVARCHAR(20), -- Partition Function KEY FIELD data tipi. Hem fonksiyon oluştururken hem de tablo oluştururken bu field gerekmektedir. @sql2 NVARCHAR(MAX), -- Partition SCHEMA oluşturulurken kullanılan sql komut satırı @groupValue NVARCHAR(MAX), -- Partition ##groupForpScheme tablosundaki kayıtları yanyana yazdırmak için atanan değer @clusteredIndexColumnName NVARCHAR(100), -- @sqlIndex NVARCHAR(MAX), @sqlDropSchemeFnc nvarchar(2000) -- Partition Scheme ve Partition Function drop eden sql komutu SET @dbname = 'TEST' SET @tableName = 'prItemBarcode' SET @year = 2016 SET @filename = 'D:\MSSQLSERVER\USERDATA\PARTITIONED\' SET @initialsize = 100 SET @filegrowth = 10 SET @totalPartYear = 1 SET @boundarySide = 'RIGHT' SET @dataType = 'datetime' SET @loopnumA = 0 SET @clusteredIndexColumnName = 'CreatedDate' -- Eğer Partition Scheme ve function varsa drop eder. Bu objeler drop olmadan partition dosyalarını drop edemezsiniz. SET @sqlDropSchemeFnc = +'IF EXISTS(SELECT [name] FROM '+@dbname+'.sys.partition_functions)' +char(13)+char(10) +'DROP PARTITION FUNCTION pfnc_'+@tablename +char(13)+char(10) +'GO'+char(13)+char(10) +'IF EXISTS(SELECT [name] FROM '+@dbname+'.sys.partition_schemes)' +char(13)+char(10) +'DROP PARTITION SCHEME pscheme_'+@tableName PRINT(@sqlDropSchemeFnc) IF EXISTS (SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '##dateTmpForpFnc') DROP TABLE ##dateTmpForpFnc CREATE TABLE ##dateTmpForpFnc (datevalue varchar(19)) IF EXISTS (SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '##groupForpScheme') DROP TABLE ##groupForpScheme CREATE TABLE ##groupForpScheme (groupValue VARCHAR(100)) WHILE (@loopNumA <=@totalPartYear) BEGIN SET @quarter = 1 WHILE (@quarter<=4) BEGIN SET @sql='USE '+@dbname +char(13)+char(10) +'GO' +char(13)+char(10) +'ALTER DATABASE '+@dbname+' ADD FILEGROUP ['+@tableName+'G'+'_'+CAST(@year+@loopNumA AS NVARCHAR(4))+'QUARTER'+RIGHT(100+CAST(@quarter AS NVARCHAR(2)),2)+']' +char(13)+char(10) +'GO' +char(13)+char(10) +'ALTER DATABASE '+@dbname+' ADD FILE (name = '+@tableName+'F_'+CAST(@year+@loopNumA AS NVARCHAR(4))+'QUARTER'+RIGHT(100+CAST(@quarter AS NVARCHAR(2)),2)+', FILENAME = '+''''+@filename+@tableName+'F_'+CAST(@year+@loopNumA AS NVARCHAR(4))+'QUARTER'+RIGHT(100+CAST(@quarter AS NVARCHAR(2)),2)+'.ndf'''+',' +' size = '+ CAST(@initialsize AS NVARCHAR(5))+' MB, '+'filegrowth = '+CAST(@filegrowth AS NVARCHAR(5))+' MB) TO FILEGROUP '+@tableName+'G'+'_'+CAST(@year+@loopNumA AS NVARCHAR(4))+'QUARTER'+RIGHT(100+CAST(@quarter AS NVARCHAR(2)),2) +char(13)+char(10) +'GO' +char(13)+char(10) -- DECLARE @pfncYear NVARCHAR(500)=NULL INSERT INTO ##dateTmpForpFnc SELECT CAST(@year+@loopNumA AS NVARCHAR(4))+'-'+RIGHT(100+CAST(@quarter AS NVARCHAR(2)),2)+'-01T00:00:00' INSERT INTO ##groupForpScheme SELECT @tableName+'G'+'_'+CAST(@year+@loopNumA AS NVARCHAR(4))+'QUARTER'+RIGHT(100+CAST(@quarter AS NVARCHAR(2)),2) SET @quarter = @quarter + 1 print(@sql) IF ((@loopNumA = @totalPartYear) AND @quarter = 5) BEGIN SET @loopNumA = @loopNumA + 1 SET @quarter = 1 SET @sql='USE '+@dbname +char(13)+char(10) +'GO'+char(13)+char(10) +'ALTER DATABASE '+@dbname+' ADD FILEGROUP ['+@tableName+'G'+'_'+CAST(@year+@loopNumA AS NVARCHAR(4))+'QUARTER'+RIGHT(100+CAST(@quarter AS NVARCHAR(2)),2)+']' +char(13)+char(10) +'GO'+char(13)+char(10) +'ALTER DATABASE '+@dbname+' ADD FILE (name = '+@tableName+'F_'+CAST(@year+@loopNumA AS NVARCHAR(4))+'QUARTER'+RIGHT(100+CAST(@quarter AS NVARCHAR(2)),2)+', FILENAME = '+''''+@filename+@tableName+'F_'+CAST(@year+@loopNumA AS NVARCHAR(4))+'QUARTER'+RIGHT(100+CAST(@quarter AS NVARCHAR(2)),2)+'.ndf'''+',' +' size = '+ CAST(@initialsize AS NVARCHAR(5))+' MB, '+'filegrowth = '+CAST(@filegrowth AS NVARCHAR(5))+' MB) TO FILEGROUP '+@tableName+'G'+'_'+CAST(@year+@loopNumA AS NVARCHAR(4))+'QUARTER'+RIGHT(100+CAST(@quarter AS NVARCHAR(2)),2) +char(13)+char(10) +'GO' +char(13)+char(10) print(@sql) INSERT INTO ##dateTmpForpFnc SELECT CAST(@year+@loopNumA AS NVARCHAR(4))+'-'+RIGHT(100+CAST(@quarter AS NVARCHAR(2)),2)+'-01T00:00:00' INSERT INTO ##groupForpScheme SELECT @tableName+'G'+'_'+CAST(@year+@loopNumA AS NVARCHAR(4))+'QUARTER'+RIGHT(100+CAST(@quarter AS NVARCHAR(2)),2) INSERT INTO ##groupForpScheme SELECT 'PRIMARY' SELECT @dateForFunc = COALESCE(@dateForFunc +''', ','') +'N'''+CAST((LTRIM(RTRIM(datevalue))) AS NVARCHAR(19)) FROM ##dateTmpForpFnc T SET @sql1 = 'BEGIN TRANSACTION ' +char(13)+char(10) +'CREATE PARTITION FUNCTION [pfnc_'+@tableName+']('+@dataType+') AS RANGE '+@boundarySide+' FOR VALUES ('+@dateForFunc+''')' +char(13)+char(10) +char(13)+char(10) PRINT(@sql1) SELECT @groupValue = COALESCE(@groupValue +', ','') +'['+CAST((LTRIM(RTRIM(groupValue))) AS NVARCHAR(100))+']' FROM ##groupForpScheme T SET @sql2 = 'CREATE PARTITION SCHEME [pscheme_'+@tableName+'] AS PARTITION [pfnc_'+@tableName+'] TO ('+@groupValue+')' +char(13)+char(10) +'COMMIT TRANSACTION' +char(13)+char(10) +char(13)+char(10) +'--Bu bölümde tabloyu oluştur. Aşağıdaki tablo örnek tablodur.' +char(13)+char(10) +'CREATE TABLE TABLOADI (ID INT,Name varchar(30)....) ON [pscheme_'+@tableName+'] ('+@clusteredIndexColumnName+')' +char(13)+char(10) +char(13)+char(10) +'CREATE CLUSTERED INDEX [ClusteredIndex_on_'+@tableName+'_'+LEFT(REPLACE(REPLACE(CONVERT(VARCHAR(20), GETDATE(), 126),'-',''),':',''),15)+'] ON [dbo].['+@tableName+']' +char(13)+char(10) +'(['+@clusteredIndexColumnName+'] ASC )WITH(SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [pscheme_'+@tableName+'] ('+@clusteredIndexColumnName+')' PRINT (@sql2) RETURN END END SET @loopNumA = @loopNumA + 1 END
