SQL Server 2017 SELECT…INTO 创建的新表指定到文件组

SELECT…INTO 在 SQL Server 中也是常见的一个功能，过去用此方法创建的新表只能存储到默认的文件组，但是 SQL Server 2017 可以创建到指定的文件组中。

使用 SELECT…INTO 需要了解的地方：

SELECT...INTO 为大容量操作，大容量操作是最小日志记录操作；

使用 SELECT... INTO 语句可以是比创建表，然后填充具有 INSERT 语句的表更高效；

启用事务复制时，将完全记录 SELECT INTO 操作，即使处于大容量日志恢复模式下；

不能使用 SELECT…INTO 创建分区表；

SELECT...INTO 不使用源表的分区方案；

源表中定义的索引、约束和触发器不会转移到新表中；

指定 ORDER BY 子句无法确保按指定顺序插入行；

稀疏列属性不会转移到新表中的列；

当选择列表中包含计算列时，新表中的相应列并不是计算列；

在选择现有标识列并将其插入到新表时，新列将继承 IDENTITY 属性；除非以下条件中的一个为真，列将被创建为 NOT NULL 而不继承 IDENTITY 属性：

1. SELECT 语句包含一个联接。

2. 多个 SELECT 语句由 UNION 联接。

3. 标识列在选择列表内多次列出。

4. 标识列是表达式的一部分。

5. 标识列来自远程数据源。

测试 SELECT…INTO 创建的新表指定到文件组：

--创建测试数据
use master
go
create database Demo
go
use Demo
go
create table Test(
id int not null identity(1,1),
userName varchar(50),
addTime datetime constraint df_addtime default(getdate()) 
) on [primary]
go
--插入100行数据
set nocount on
insert into Test(userName) select 'kk'+convert(varchar(10),@@identity) 
go 100
set nocount off
go

--创建文件组及文件
alter database Demo add filegroup fg;
go
alter database Demo
add file
(
	name='fg_data',
	filename = 'D:\SQLServer\Data2017/Demo1.mdf'
)
to filegroup fg;
go

--注意：SELECT…INTO 创建的新表指定到文件组
select * 
into newTest on fg
from Test
go

--查看结果：
SELECT tbl.name AS TableName,fg.name AS [FileGroupName],CAST(p.rows AS float) AS [RowCount]
FROM sys.tables AS tbl
INNER JOIN sys.indexes AS idx 
	ON idx.object_id = tbl.object_id and (idx.index_id < 2  or (tbl.is_memory_optimized = 1 
	and idx.index_id = (select min(index_id) from sys.indexes where object_id = tbl.object_id)))
INNER JOIN sys.partitions AS p ON p.object_id=CAST(tbl.object_id AS int) AND p.index_id=idx.index_id
LEFT OUTER JOIN sys.destination_data_spaces AS dds ON dds.partition_scheme_id = idx.data_space_id and dds.destination_id = p.partition_number
LEFT OUTER JOIN sys.filegroups AS fg ON fg.data_space_id = dds.data_space_id or fg.data_space_id = idx.data_space_id
WHERE (p.partition_number=1)
go

参考： https://docs.microsoft.com/zh-cn/sql/t-sql/queries/select-into-clause-transact-sql