Скрипт для партицирования в Юконе нужен ?

[Torin]

Ага, ну вот.
Сразу, чтобы бы ввести понимание - работает только в Юконе (уровень совместимости неважен), причем Энтерпрайз версии (Или Девелопер - там где партиционирование разрешенно).
Сначала надо создать файловые группы с файлами данных (у меня DATAGROUP1, DATAGROUP2 и т.д.)
Потом функцию и схему

CREATE PARTITION FUNCTION axDataAreaPF (varchar(3))
AS RANGE LEFT FOR VALUES ('mce', 'mso', 'mea', 'mpc', 'pal', 'pgt', 'psa','pgh');
GO

CREATE PARTITION SCHEME axDataAreaPS
AS PARTITION axDataAreaPF
TO ([DATAGROUP1], [DATAGROUP2], [DATAGROUP3], [DATAGROUP4], [DATAGROUP5], [DATAGROUP6], [DATAGROUP7], [DATAGROUP8], [DATAGROUP9], [DATAGROUP10]);
GO

Где у нас 'mce', 'mso', 'mea', 'mpc', 'pal', 'pgt', 'psa','pgh' - это разные DataArea
По особенностям операторов лучьше почитать в хелпе - есть нюансы с LEFT и лишней группой в схеме.

Далее, само интересное. Этим скриптом я перебираю объекты, проверяю, что они еще не партициионированны и, собственно, do it. Скрипт можно запускать много раз - если ничего не изменилось или не было ошибок, то и делать ничего не будет.

USE AX64SP3;
SET LOCK_TIMEOUT -1;
GO

DECLARE @table_name varchar(100), @table_id int, @table_partition_type varchar(2), @table_partition_name  varchar(100)
DECLARE @index_name varchar(100), @index_type tinyint, @index_is_unique tinyint, @index_is_primary_key tinyint, @index_is_disabled tinyint, @index_allow_row_locks tinyint, @index_allow_page_locks tinyint, @index_partition_type varchar(2), @index_partition_name  varchar(100)
DECLARE @column_name varchar(100), @column_is_descending_key  tinyint, @column_first tinyint
DECLARE @SQL as nvarchar(4000)
	
DECLARE table_cursor CURSOR FOR
	select DISTINCT o.name, o.object_id, ds.type as type, ISNULL(pf.name, ds.name) as name 
	from 
		sys.partitions AS p
		INNER JOIN sys.indexes AS i ON p.object_id = i.object_id AND p.index_id = i.index_id
		INNER JOIN sys.objects AS o ON o.object_id = i.object_id
		INNER JOIN sys.data_spaces ds ON i.data_space_id=ds.data_space_id
		LEFT JOIN sys.partition_schemes ps ON i.data_space_id=ps.data_space_id
		LEFT JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
	where o.type='U' and i.type=0 and SCHEMA_NAME(o.schema_id) = 'dbo'
	ORDER BY o.name

OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @table_name, @table_id, @table_partition_type, @table_partition_name

WHILE @@FETCH_STATUS = 0
BEGIN
	IF 
		EXISTS(select [name] FROM sys.columns WHERE object_id = @table_id AND UPPER(name) = 'DATAAREAID')
		AND NOT @table_name LIKE 'SYS%'
	BEGIN
		PRINT '--' + @table_name
		IF NOT EXISTS (Select [name] FROM sys.indexes WHERE [type] = 1 AND object_id = @table_id) AND @table_partition_type = 'FG'
		BEGIN
			PRINT '----MOVE TO PARTITION'
			SELECT @SQL = 'CREATE CLUSTERED INDEX tmp_cluster ON ' +@table_name+ '(DATAAREAID) ON axDataAreaPS(DATAAREAID);'
			--PRINT @SQL
			EXEC(@SQL)
			
			SELECT @SQL = 'DROP INDEX tmp_cluster ON ' + @table_name + ';'
			--PRINT @SQL
			EXEC(@SQL)
		END

		Declare index_cursor CURSOR FOR
			Select i.name, i.type, i.is_unique,  i.is_primary_key, i.is_disabled, i.allow_row_locks, i.allow_page_locks, ds.type as type, ISNULL(pf.name, ds.name) as name 
			FROM sys.partitions AS p
				INNER JOIN sys.indexes AS i ON p.object_id = i.object_id AND p.index_id = i.index_id
				INNER JOIN sys.objects AS o ON o.object_id = i.object_id
				INNER JOIN sys.data_spaces ds ON i.data_space_id=ds.data_space_id
				LEFT JOIN sys.partition_schemes ps ON i.data_space_id=ps.data_space_id
				LEFT JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
			WHERE i.object_id = @table_id and i.type>0
			Order by i.type

		OPEN index_cursor
		FETCH NEXT FROM index_cursor INTO @index_name, @index_type, @index_is_unique, @index_is_primary_key, @index_is_disabled, @index_allow_row_locks, @index_allow_page_locks, @index_partition_type, @index_partition_name

		WHILE @@FETCH_STATUS = 0
		BEGIN
			IF @index_partition_type = 'FG'
			BEGIN
				PRINT '----' + @index_name
				SELECT @SQL = 
				CASE WHEN @index_is_primary_key = 1 THEN 
					'ALTER TABLE ' + @table_name + ' DROP CONSTRAINT ' + @index_name +' WITH (ONLINE = OFF); ' 
					+  ' ALTER TABLE ' + @table_name + ' WITH NOCHECK ADD CONSTRAINT ' + @index_name + ' PRIMARY KEY ' +CASE WHEN @index_type = 1 THEN ' CLUSTERED ' ELSE ' ' END 
				ELSE
					'CREATE ' + CASE WHEN @index_type = 1 THEN 'CLUSTERED' WHEN @index_is_unique =1 THEN  'UNIQUE' ELSE '' END +' INDEX ' + @index_name + ' ON '+ @table_name
				END

				Declare column_cursor CURSOR FOR
				Select t3.[name], t2.is_descending_key 
				FROM	sys.indexes t1
						INNER JOIN sys.index_columns t2 ON t1.object_id=t2.object_id AND t1.index_id = t2.index_id
						INNER JOIN sys.columns t3 ON t1.object_id=t3.object_id AND t2.column_id = t3.column_id
				WHERE t1.[name] =@index_name and t1.object_id = @table_id
				Order by t2.index_column_id
				
				SELECT @SQL = @SQL + '('
				SELECT @column_first = 1

				OPEN column_cursor
				FETCH NEXT FROM column_cursor INTO @column_name, @column_is_descending_key
				WHILE @@FETCH_STATUS = 0
				BEGIN
					SELECT @SQL = @SQL + CASE WHEN @column_first=1 THEN '' ELSE ', ' END + @column_name + CASE WHEN @column_is_descending_key = 1 THEN ' DESC' ELSE ' ' END
					SELECT @column_first = 0
					FETCH NEXT FROM column_cursor  INTO @column_name, @column_is_descending_key
				END
				CLOSE column_cursor
				DEALLOCATE column_cursor
				SELECT @SQL = @SQL +
					CASE WHEN @index_is_primary_key = 1 THEN 
						') WITH (FILLFACTOR = 75, ONLINE = OFF) ON axDataAreaPS(DATAAREAID);'
					ELSE
						') WITH (DROP_EXISTING = ON, ONLINE = OFF) ON axDataAreaPS(DATAAREAID);'
					END
				--PRINT @SQL
				EXEC(@SQL)
			END
		FETCH NEXT FROM index_cursor INTO @index_name, @index_type, @index_is_unique, @index_is_primary_key, @index_is_disabled, @index_allow_row_locks, @index_allow_page_locks, @index_partition_type, @index_partition_name
		END
		CLOSE index_cursor
		DEALLOCATE index_cursor
	PRINT 'GO' 	
	END
	FETCH NEXT FROM table_cursor INTO @table_name, @table_id, @table_partition_type, @table_partition_name
END

CLOSE table_cursor
DEALLOCATE table_cursor

Вместо EXEC можно расскомментировать PRINT - тогда результатом работы будет SQL скрипт, который можно разглядывать и выполнять по частям ручками.
AS IS, конечно