2

Am I missing something?

Documentation clearly states, that:

ON "default" Applies to: SQL Server 2008 through SQL Server 2017 and Azure SQL Database.

Creates the specified index on the default filegroup.

see: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-2017

Although, it seems that ON [DEFAULT] is ignored and index would be created on the table's filegroup, unless you explicitly specify some other filegroup.

Consider (tested on 2014/2016/2017, patched to the latest):

-- 1. create empty db with second filegroup
CREATE DATABASE [idxtest]
 ON  PRIMARY 
( NAME = N'idxtest', FILENAME = N'c:\SqlData\Data\Sql2014\idxtest.mdf' , SIZE = 10MB , FILEGROWTH = 1MB ), 
 FILEGROUP [def_test] 
( NAME = N'def_test', FILENAME = N'c:\SqlData\Data\Sql2014\def_test.ndf' , SIZE = 10MB , FILEGROWTH = 1MB )
 LOG ON 
( NAME = N'idxtest_log', FILENAME = N'c:\SqlData\Data\Sql2014\idxtest_log.ldf' , SIZE = 10MB , FILEGROWTH = 1MB);
GO

USE [idxtest]

-- 2. show default filegroup
select name from sys.filegroups where is_default=1;

name
--------------
PRIMARY

-- 3. change default filegroup to [def_test]
ALTER DATABASE [idxtest] MODIFY FILEGROUP [def_test] DEFAULT

The filegroup property 'DEFAULT' has been set.

-- 4. demonstrate new default filegroup
select name from sys.filegroups where is_default=1;

name
--------------
def_test

-- 5. test table creation (1)
CREATE TABLE tab1 (x int) ON [DEFAULT]

-- 6. show filegroup
SELECT object_name(i.object_id) TabName, i.[name] IdxName, f.[name] FileGroupName 
FROM sys.indexes i INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
WHERE object_name(i.object_id) = 'tab1' 

TabName        IdxName        FileGroupName
-------------- -------------- --------------
tab1           NULL           def_test

-- 7. change default filegroup to [PRIMARY]
ALTER DATABASE [idxtest] MODIFY FILEGROUP [PRIMARY] DEFAULT

The filegroup property 'DEFAULT' has been set.

-- 8. show default filegroup
select name from sys.filegroups where is_default=1;

name
--------------
PRIMARY

-- 9. test index creation
create index idx_tab1$1 on tab1(x) on [DEFAULT]

-- 10. show filegroup
SELECT object_name(i.object_id) TabName, i.[name] IdxName, f.[name] FileGroupName 
FROM sys.indexes i INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
WHERE object_name(i.object_id) = 'tab1' 

TabName        IdxName        FileGroupName
-------------- -------------- --------------
tab1           NULL           def_test
tab1           idx_tab1$1     def_test

-- TY

  • 1
    To quote the manual you linked to: "This is because indexes, by default, are placed on the same filegroups as their underlying tables". It looks like the meaning of [DEFAULT] here is "default for the index", not "default for the database" – mustaccio Dec 14 '18 at 2:00
  • If we read further: "The term default, in this context, is not a keyword. It is an identifier for the default filegroup and must be delimited, as in ON "default" or ON [default]" Having ON [default] picking up a default filegroup as I understand it for tables, while index would be picking up the filegroup of the parent object as default, is too much of ambiguity. – AlexM Dec 14 '18 at 18:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.