為什麼沒有在我指定的文件組上創建我的 新索引/表？

@@Version 的結果

Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) 
Sep 21 2011 22:45:45 
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

這是數據庫定義

CREATE DATABASE [Scratch] ON  PRIMARY 
( NAME = N'Scratch_mdf', FILENAME = N'N:\MSSQL\TEST\Primary\Scratch_mdf.mdf' , SIZE = 17664KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
FILEGROUP [DATA]  DEFAULT 
( NAME = N'Scratch_dat1a', FILENAME = N'N:\MSSQL\TEST\Data\Scratch_dat1a.ndf' , SIZE = 14539584KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
( NAME = N'Scratch_dat1b', FILENAME = N'N:\MSSQL\TEST\Data\Scratch_dat1b.ndf' , SIZE = 12016128KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
FILEGROUP [INDEX] 
( NAME = N'Scratch_idx1', FILENAME = N'N:\MSSQL\TEST\Index\Scratch_idx1.ndf' , SIZE = 92864KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
( NAME = N'Scratch_idx2', FILENAME = N'N:\MSSQL\TEST\Index\Scratch_idx2.ndf' , SIZE = 84416KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
( NAME = N'Scratch_idx3', FILENAME = N'N:\MSSQL\TEST\Index\Scratch_idx3.ndf' , SIZE = 84416KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
( NAME = N'Scratch_idx4', FILENAME = N'N:\MSSQL\TEST\Index\Scratch_idx4.ndf' , SIZE = 92864KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
FILEGROUP [PM_G0] 
( NAME = N'PM_data0', FILENAME = N'N:\MSSQL\PDB1\Data\PM_data0.ndf' , SIZE = 768000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
LOG ON 
( NAME = N'Scratch_log1', FILENAME = N'N:\MSSQL\TEST\Log\Scratch_log1.ldf' , SIZE = 833024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%) 
GO

ALTER DATABASE [Scratch] SET COMPATIBILITY_LEVEL = 100
GO

這是創建表和添加聚集索引的程式碼。在這兩種情況下，我都指定了文件組 PM_G0，但是當我在文件組 DATA 上檢查它時。

CREATE TABLE Scratch.dbo.STUPID_TEST (dummycol int) ON [PM_G0];
CREATE CLUSTERED INDEX ix_test ON stupid_test(dummycol) ON PM_G0; 
-- Tried the filegroup name with/without brackets just in case

我正在檢查第一個堆的位置，然後使用以下查詢檢查聚集索引：

SELECT o.name objectname, fg.name filegroupname, sdf.name as datafile,i.name indexname, partition_id, partition_number, [rows]
FROM sys.partitions p
INNER JOIN sys.objects o ON o.object_id=p.object_id
INNER JOIN sys.tables t ON o.object_id = t.object_id
INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id
INNER JOIN sys.filegroups fg ON fg.data_space_id = i.data_space_id
INNER JOIN sys.database_files sdf ON sdf.data_space_id=fg.data_space_id
WHERE o.name = 'STUPID_TEST'

有趣的測試。我將預設文件組更改為

$$ index $$. 確認$$ index $$實際上是 sys.filegroups 中的預設索引然後刪除並重新創建了我的聚集索引，它仍然去了$$ DATA $$文件組。下面的程式碼

ALTER DATABASE Scratch MODIFY FILEGROUP [index] DEFAULT;
GO
DROP INDEX dbo.STUPID_TEST.IX_test;
CREATE CLUSTERED INDEX ix_test ON stupid_test(dummycol) ON [PM_G0];
GO

首先感謝所有在這里和推特上試圖幫助我的人。然而，我們終於找到了答案。事實證明，有人在模型上創建了一個數據庫觸發器。觸發器解析每個創建索引/表命令並將其更改為 DATA 文件組（如果它是堆或聚集索引）和 INDEX 文件組用於其他所有內容。然後，我們能夠將觸發器追溯到政策。

引用自：https://dba.stackexchange.com/questions/65505