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.
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
[DEFAULT]
here is "default for the index", not "default for the database"