MS SQL Server에서 테이블을 다른 파일 그룹으로 이동하려면 어떻게 해야 합니까?

MS SQL Server에서 테이블을 다른 파일 그룹으로 이동하려면 어떻게 해야 합니까?

있습니다SQL Server 2008 Ent그리고.OLTP database큰 테이블이 두 개 있는이 테이블을 다른 테이블로 이동하는 방법filegroup서비스 중단 없이?이제 약 100-130개의 레코드가 삽입되고 30-50개의 레코드가 매초마다 업데이트됩니다.에는 약 와 6개의 레코드가 .field의 필드(한필포함드)) )geography).

나는 구글에서 해결책을 찾고 있지만, 모든 해결책은 다음을 포함합니다.

두 번째 테이블 만들기, 첫 번째 테이블의 행 삽입, 첫 번째 테이블 삭제 등

이 문제를 해결하기 위해 파티셔닝 기능을 사용할 수 있습니까?

테이블을 새 파일 그룹으로 이동하려면 원하는 새 파일 그룹에서 테이블에 클러스터된 인덱스(결국 클러스터된 인덱스가 테이블 데이터임)를 재생성해야 합니다.

다음과 같은 작업을 수행할 수 있습니다.

CREATE CLUSTERED INDEX CIX_YourTable
   ON dbo.YourTable(YourClusteringKeyFields)
   WITH DROP_EXISTING
   ON [filegroup_name]

또는 클러스터된 인덱스가 고유한 경우:

CREATE UNIQUE CLUSTERED INDEX CIX_YourTable
   ON dbo.YourTable(YourClusteringKeyFields)
   WITH DROP_EXISTING
   ON [filegroup_name]

그러면 새 클러스터된 인덱스가 생성되고 기존 인덱스가 삭제되고 지정한 파일 그룹에 새 클러스터된 인덱스가 생성됩니다. 즉, etvoila. 테이블 데이터가 새 파일 그룹으로 이동되었습니다.

사용 가능한 모든 옵션에 대한 자세한 내용은 CREATE INDEX의 MSDN 문서를 참조하십시오.

물론 이건 아직 이별에 관한 것은 아니지만, 그것은 완전히 다른 이야기입니다.

이 질문에 답하기 위해서, 우리는 먼저 이해해야 합니다.

• 테이블에 인덱스가 없으면 테이블의 데이터를 힙이라고 합니다.
• 테이블에 클러스터된 인덱스가 있으면 해당 인덱스가 테이블 데이터입니다.따라서 클러스터된 인덱스를 이동하면 데이터도 이동합니다.

첫 번째 단계는 이동할 테이블에 대한 자세한 정보를 찾는 것입니다.이 작업은 다음 T-SQL을 실행하여 수행합니다.

sp_help N'<<your table name>>'

'Data_located_on_filegroup'이라는 이름의 파일입니다.테이블 데이터가 어떤 파일 그룹에 있는지 쉽게 알 수 있습니다.그러나 더 중요한 것은 테이블의 인덱스에 대한 정보를 보여주는 출력입니다.테이블 인덱스에 대한 정보만 보려면 다음을 실행하십시오.sp_helpindex N'<<your table name>>'테이블에 1) 인덱스가 없거나(따라서 힙), 2) 단일 인덱스 또는 3) 다중 인덱스가 있을 수 있습니다.이 ' 이동할입니다.index_description은 'clustered, unique, ...'라는 이름으로 됩니다.인덱스가 기본 키인 경우에도 이동할 수 있습니다.

위쿼리의 및하여 index_name index_key를 합니다.<<blanks>>다음 쿼리의 경우:

CREATE UNIQUE CLUSTERED INDEX [<<name of clustered index>>]
ON [<<table name>>]([<<column name the index is on - from index_keys above>>])
WITH (DROP_EXISTING = ON, ONLINE = ON)
ON <<name of file group you want to move the index to>>

그DROP EXISTING, ONLINE위의 옵션이 중요합니다. DROP EXISTING인덱스가 중복되지 않는지 확인합니다.ONLINE테이블을 이동하는 동안 테이블을 온라인 상태로 유지합니다(현재는 Enterprise 버전에서만 사용 가능).

이동 중인 인덱스가 클러스터된 인덱스가 아닌 경우 대체UNIQUE CLUSTERED에 이닌아로NONCLUSTERED

힙 테이블을 이동하려면 해당 테이블에 클러스터된 인덱스를 추가한 다음 위의 문을 실행하여 다른 파일 그룹으로 이동한 다음 인덱스를 삭제합니다.

자, 돌아가서 도망가sp_help테이블에서 결과를 확인하여 테이블 및 인덱스 데이터가 현재 어디에 있는지 확인합니다.

테이블에 둘 이상의 인덱스가 있는 경우 위의 문을 실행하여 클러스터된 인덱스를 이동한 후sp_helpindex클러스터된 인덱스는 새 파일 그룹에 있지만 나머지 인덱스는 여전히 원래 파일 그룹에 있음을 나타냅니다.테이블은 계속 정상적으로 작동하지만 인덱스를 다른 파일 그룹에 배치해야 하는 이유가 있습니다.그룹에 시키려면 각합니다.CREATE [NONCLUSTERED, or other] ... DROP EXISTING... 이동 중인 인덱스 유형에 따라 필요에 따라 선택합니다.

파티셔닝은 하나의 솔루션이지만 서비스 중단 없이 클러스터된 인덱스를 새 파일 그룹으로 "이동"할 수 있습니다(일부 상황에 따라 아래 링크 참조).

CREATE CLUSTERED /*oops*/ INDEX ... WITH (DROP_EXISTING = ON, ONLINE = ON, ...) ON newfilegroup

클러스터된 인덱스는 데이터이며 이는 파일 그룹을 이동하는 것과 동일합니다.

CREATE INDEX를 참조하십시오.

이는 기본 키가 클러스터되어 있는지 여부에 따라 달라지며, 이에 따라 수행 방법이 달라집니다.

하면 "합니다. 예를 들어 "" 열은 "기본" 열로 이동합니다int, bit, datetime

varchar(max), varbinary테이블을 다시 만들어야 하는 기타 "거품" 열.고맙게도 SSMS에서는 테이블 "설계" 창에서 "텍스트 파일 그룹"을 변경한 다음 변경 사항을 저장하는 반자동으로 이 작업을 수행할 수 있습니다.

(2021년부터 빠른 업데이트):또는 테이블의 모든 값에 대해 새 파일 그룹을 가리키는 임시 "파티셔닝" 규칙(파티셔닝 규칙은 데이터가 어떤 파일 그룹으로 이동할지 결정하는 함수)을 만들 수 있습니다.이 파티셔닝 체계를 적용하면 실제로 데이터가 이동합니다.

더 자세한 정보가 필요하시면 https://www.jitbit.com/alexblog/153-moving-sql-table-textimage-to-a-new-filegroup/ 에 블로그를 올렸습니다.

SQL Server Books Online에서 발췌한 내용은 다음과 같습니다. "클러스터된 인덱스의 리프 레벨과 데이터 페이지는 정의상 동일하기 때문에,클러스터된 인덱스를 생성하고 partition_vmdk_name 또는 ON filegroup_name 절을 사용하여 테이블이 생성된 파일 그룹에서 새 파티션 구성표 또는 파일 그룹으로 테이블을 효과적으로 이동합니다." (출처 - http://msdn.microsoft.com/en-us/library/ms188783.aspx) (http://wwww.fsqltips)com/sqlservertip/2442/sql-server-syslog-filegroups 간 데이터 이동/)

marc_s의 수락된 답변과 같은 다른 친구들이 이미 말했듯이 스크린샷은 SSMS GUI를 사용하여 그것을 할 수 있는 다른 방법을 제공합니다.

탭에서 할 수 .

테이블을 다른 파일 그룹으로 이동하려면 어떻게 해야 합니까?

참고: 테이블을 다른 파일 그룹으로 이동하는 것은 Enterprise Edition에서만 작동합니다.

1단계:

어떤 파일 그룹 테이블이 있는지 확인합니다.

-- Query to check the tables and their current filegroup:

SELECT    tbl.name AS [Table Name], 
          CASE WHEN dsidx.type='FG' THEN dsidx.name ELSE '(Partitioned)' END AS [File Group] 
FROM      sys.tables AS tbl 
JOIN      sys.indexes AS idx 
ON        idx.object_id = tbl.object_id 
AND       idx.index_id <= 1 
LEFT JOIN sys.data_spaces AS dsidx 
ON        dsidx.data_space_id = idx.data_space_id 
ORDER BY  [File Group], [Table Name] 

2단계:

기존 테이블/테이블을 새 파일 그룹으로 이동

테이블을 이동할 파일 그룹이 아직 없는 경우 보조 파일 그룹을 만든 다음 테이블을 이동하십시오.

테이블을 다른 파일 그룹으로 이동하려면 테이블의 클러스터된 인덱스를 새 파일 그룹으로 이동해야 합니다.클러스터된 인덱스의 리프 수준에는 실제로 테이블 데이터가 포함됩니다.따라서 다음과 같이 DROP_EXITING 절을 사용하여 클러스터된 인덱스를 단일 문으로 이동할 수 있습니다.

CREATE UNIQUE CLUSTERED INDEX [Index_Name] ON [SchemaName].[TableName]
(
    [ClusteredIndexKeyFields]
)WITH (DROP_EXISTING = ON, ONLINE = ON) ON [FilegroupName]
GO

3단계:

나머지 비클러스터형 인덱스를 보조 파일 그룹으로 이동

아래에 설명된 구문을 사용하여 비클러스터형 인덱스를 수동으로 이동해야 합니다.

--1st check the index information using the following sp
sp_helpindex [YourTableName]


--Now by using the following query you can move the remaining indexes to secondary filegroup
CREATE NONCLUSTERED INDEX [Index_Name] ON [SchemaName].[TableName]
(
    [IndexKeyFields]
)WITH (DROP_EXISTING = ON, ONLINE = ON) ON [FilegroupName]
GO

힙을 다른 파일 그룹으로 이동:

힙을 다른 파일 그룹으로 이동하는 유일한 방법은 새 파일 그룹에 클러스터된 인덱스를 임시로 추가한 다음 필요한 경우 삭제하는 것입니다.

SSMS에서 테이블을 확장하고 이동할 테이블을 확장하고 색인을 확장한 후 클러스터된 색인을 마우스 오른쪽 단추로 클릭한 다음 "스크립트 색인" -> "삭제 및 작성 대상"을 클릭합니다.

그러면 클러스터된 인덱스를 삭제하고 원래 인덱스와 동일한 사양으로 새 인덱스를 생성하는 스크립트가 포함된 쿼리 창이 열립니다.

에서 쿼리창서에 있습니다."ALTER TABLE <> ADD CONSTRAINT"문,문 "뒤에 있는 이 주 "Secondary하려면 " 문", "ON"을 변경합니다. 예를 들어 테이블이 주 파일 그룹에 있고 "Secondary" 이름을 가진 파일 그룹으로 이동하려는 경우 변경합니다."ON [PRIMARY]""ON [SECONDARY]".
을 변경하십시오."ONLINE = OFF""ONLINE = ON".

스크립트를 실행하면 원본이 삭제되고 지정된 파일 그룹에 새 스크립트가 생성됩니다.

Management Studio를 통해 테이블을 다른 파일 그룹으로 이동하는 절차는 매우 간단하고 간단합니다.

• 각 인덱스의 FileGroup 속성을 변경하여 클러스터되지 않은 모든 인덱스를 새 파일 그룹으로 이동

• 클러스터 인덱스를 비클러스터로 변경하고 이전 단계와 마찬가지로 파일 그룹 변경

• 다음 명령을 사용하거나 IDE를 통해 "새 파일 그룹"으로 새 임시 클러스터 인덱스를 추가합니다.

     CREATE CLUSTERED INDEX [PK_temp]
  ON YOURTABLE([Id])
    ON NEWFILEGROUP
  

  (위의 명령은 모든 데이터를 새 파일 그룹으로 이동시킵니다.)

• 위의 임시 PK를 삭제합니다(PK가 제대로 작동할 때!).

• IDE를 통해 기본 클러스터 인덱스를 다시 클러스터 인덱스로 변경합니다.

위 단계의 이점은 기존 FK 관계를 중단할 필요가 없습니다.또한 IDE를 사용하면 오류 상태에서 데이터가 손실되는 것을 방지할 수 있습니다.

참고: 파일 그룹에 대해 디스크 할당량이 사용 가능하지 않은지 확인하거나 올바르게 설정하십시오.그렇지 않으면 "filegroup is full" 예외가 발생합니다!

CREATE CLUSTERED INDEX IXC_Products_Product_id
ON dbo.Products(Product_id)
WITH (DROP_EXISTING = ON) ON MyNewFileGroup

동일한 문제가 있었는데, 이것이 제가 생각해 낸 스크립트입니다(테스트 결과 예상대로 작동함).

DECLARE @Target_Filegroup sysname = N'XXX';
-----------------------------------------------------------------------------------------
;WITH [IX] AS(
SELECT
[Schema]                 = SCHEMA_NAME(so.[schema_id]) COLLATE DATABASE_DEFAULT,
[Object_Name]            = so.[name] COLLATE DATABASE_DEFAULT,
[Object_Type]            = so.[type],
[Is_Published]           = so.[is_published],
[Is_Schema_Published]    = so.[is_schema_published],
[IX_Name]                = ix.[name] COLLATE DATABASE_DEFAULT,
[IX_Type]                = ix.[type],
[IX_Type_Desc]           = ix.[type_desc] COLLATE DATABASE_DEFAULT,
[Is_PK]                  = ix.[is_primary_key],
[Is_Unique]              = ix.[is_unique],
[IX_Data_Space]          = ds.[name] COLLATE DATABASE_DEFAULT,
[Is_UC]                  = ix.[is_unique_constraint],
[FF]                     = ix.[fill_factor],
[Is_Padded]              = ix.[is_padded],
[Is_Disabled]            = ix.[is_disabled],
[Is_Hypothetical]        = ix.[is_hypothetical],
[Allow_Row_Locks]        = ix.[allow_row_locks],
[Allow_Page_Locks]       = ix.[allow_page_locks],
[Has_Filter]             = ix.[has_filter],
[Filter]                 = ix.[filter_definition] COLLATE DATABASE_DEFAULT,
--[auto_created]         = ix.[auto_created],
--[optimize_seq_key]     = ix.[optimize_for_sequential_key],
[Indexed_Columns] = STUFF(( SELECT [text()] = CONCAT(', ', QUOTENAME(COL_NAME(ic.[object_id],ic.[column_id])))
                            FROM sys.index_columns ic
                            WHERE ic.[object_id] = so.[object_id]
                            AND ic.[index_id] = ix.[index_id]
                            AND ic.[is_included_column] = 0
                            ORDER BY ic.[key_ordinal]
                            FOR XML PATH('')
                            ), 1, 2, '') COLLATE DATABASE_DEFAULT,
[Indexed_Columns_Order] = STUFF(( SELECT [text()] = CONCAT(', ', QUOTENAME(COL_NAME(ic.[object_id],ic.[column_id])), CASE [is_descending_key] WHEN 1 THEN ' DESC' ELSE ' ASC' END)
                            FROM sys.index_columns ic
                            WHERE ic.[object_id] = so.[object_id]
                            AND ic.[index_id] = ix.[index_id]
                            AND ic.[is_included_column] = 0
                            ORDER BY ic.[key_ordinal]
                            FOR XML PATH('')
                            ), 1, 2, '') COLLATE DATABASE_DEFAULT,
[Included_Columns] = STUFF(( SELECT [text()] = CONCAT(', ', QUOTENAME(COL_NAME(ic.[object_id],ic.[column_id])))
                            FROM sys.index_columns ic
                            WHERE ic.[object_id] = so.[object_id]
                            AND ic.[index_id] = ix.[index_id]
                            AND ic.[is_included_column] = 1
                            ORDER BY ic.[key_ordinal]
                            FOR XML PATH('')
                            ), 1, 2, '') COLLATE DATABASE_DEFAULT
FROM sys.objects so
LEFT JOIN sys.indexes ix ON so.[object_id] = ix.[object_id]
LEFT JOIN sys.data_spaces ds ON ix.[data_space_id] = ds.[data_space_id]
WHERE so.[type] IN ('U', 'V')
AND so.[is_ms_shipped] = 0
AND ix.[type] IS NOT NULL --| so we get heaps, and indexed views 
)
SELECT
[Schema], [Object_Name], [Object_Type],
--[Is_Published], [Is_Schema_Published],
[IX_Name],
[IX_Data_Space],
[IX_Move_SQL] = CASE WHEN [IX_Data_Space] <> @Target_Filegroup AND [IX_Type] IN (1,2) THEN CONCAT(
                                                'CREATE ', CASE [Is_Unique] WHEN 1 THEN 'UNIQUE ' END, [IX_Type_Desc], ' INDEX ', QUOTENAME([IX_Name]),
                                                ' ON ', QUOTENAME([Schema]), '.', QUOTENAME([Object_Name]), ' (', [Indexed_Columns_Order], ')',
                                                CASE WHEN [Included_Columns] IS NOT NULL THEN CONCAT(' INCLUDE (', [Included_Columns], ')') END,
                                                CASE WHEN [Has_Filter] = 1 THEN CONCAT(' WHERE ', [Filter]) END,
                                                ' WITH (PAD_INDEX=', CASE [Is_Padded] WHEN 1 THEN 'ON' ELSE 'OFF' END,
                                                ', FILLFACTOR=', CASE WHEN [FF] = 0 THEN '100' ELSE CAST([FF] as varchar(3)) COLLATE DATABASE_DEFAULT END,
                                                ', ALLOW_ROW_LOCKS=', CASE [Allow_Row_Locks] WHEN 1 THEN 'ON' ELSE 'OFF' END,
                                                ', ALLOW_PAGE_LOCKS=', CASE [Allow_Page_Locks] WHEN 1 THEN 'ON' ELSE 'OFF' END,
                                                ', DROP_EXISTING=ON ',')',
                                                ' ON ', QUOTENAME(@Target_Filegroup), ';')

                                                 END COLLATE DATABASE_DEFAULT
FROM [IX]
ORDER BY [Object_Type] ASC, [Schema] ASC , [Object_Name] ASC;

언급URL : https://stackoverflow.com/questions/2438019/how-can-i-move-a-table-to-another-filegroup-in-ms-sql-server