[SQL Server]SELECT INTO ON FILEGROUP(SQL Server 2017)

資料庫初始檔案規劃時,除了主要的檔案群組(PRIMARY),我們通常會和DBA大人商量新增一到兩個檔案群組放到更快存取速度的磁碟,當新增資料表時,我們就可以依據資料表受歡迎程度(熱度)來設定不同的選擇,但如果遇到資料表是以SELECT INTO產生出來的,以往就只能放在預設的檔案群組(通常是PRIMARY),  如果想讓SELECT INTO的資料表要放到其他檔案群組,只能改資料庫的預設檔案群組來解決。
 

究竟SELECT INTO ON FILEGROUP有沒有實現的機會?!,

  • 10年前的2007年,在微軟 connect也有人提出這個需求,不過當時SQL Server Engine Team將問題狀態改為已關閉,解決方法為:不修正
  • 10年後,SQL Server 2017解決了這個問題,現在我們可以下SELECT INTO ON FILEGROUP了,感恩Seafood ,讚嘆Seafood!

快來試試!
 


建立測試資料庫及檔案群組

USE master;
GO
CREATE DATABASE MyDB
ON PRIMARY
  ( NAME='MyDB_Primary',
    FILENAME=
       'E:\SQL2017\MSSQL14.SQL14\MSSQL\DATA\MyDB.mdf',
    SIZE=4MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB),
FILEGROUP MyDB_FG1
  ( NAME = 'MyDB_FG1_Dat1',
    FILENAME =
       'E:\SQL2017\MSSQL14.SQL14\MSSQL\DATA\MyDB_FG1_1.ndf',
    SIZE = 1MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB),
  ( NAME = 'MyDB_FG1_Dat2',
    FILENAME =
       'E:\SQL2017\MSSQL14.SQL14\MSSQL\DATA\MyDB_FG1_2.ndf',
    SIZE = 1MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB)
LOG ON
  ( NAME='MyDB_log',
    FILENAME =
       'E:\SQL2017\MSSQL14.SQL14\MSSQL\DATA\MyDB.ldf',
    SIZE=1MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB);
GO

 

所以我們現在有兩個檔案群組了(PRIMARY + MyDBFG1)。

 

建立測試資料表MyTable1,並且觀察MyTable1的物件資訊

USE MyDB;
CREATE TABLE MyTable1
  ( cola int PRIMARY KEY,
    colb char(8) )
GO

sp_help MyTable1

 

檔案群組位於Primary

 


SELECT INTO ON FILEGROUP(SQL2017)

馬上來試試新的指定檔案群組方式

USE MyDB;
SELECT * INTO MyTable2 ON MyDB_FG1 FROM MyTable1 
sp_help MyTable2

 

輕鬆的指定到MyDB_FG1檔案群組

 


修改預設檔案群組

如果版本還是SQL Server 2017以前的,我們得修改預設檔案群組

ALTER DATABASE MyDB 
  MODIFY FILEGROUP MyDB_FG1 DEFAULT;
GO

 

測試看看

USE MyDB;
SELECT * INTO MyTable3  FROM MyTable1 
sp_help MyTable3

 

指定到MyDB_FG1檔案群組

 


小結

  • 10年之前,我不認識妳,十年之後,我們是朋友。

 


參考

SELECT INTO ON FILEGROUP (connect.microsoft)

SELECT - INTO Clause (Transact-SQL)