Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- 1. create empty db with second filegroup
- CREATE DATABASE [idxtest]
- ON PRIMARY
- ( NAME = N'idxtest', FILENAME = N'c:SqlDataDataSql2014idxtest.mdf' , SIZE = 10MB , FILEGROWTH = 1MB ),
- FILEGROUP [def_test]
- ( NAME = N'def_test', FILENAME = N'c:SqlDataDataSql2014def_test.ndf' , SIZE = 10MB , FILEGROWTH = 1MB )
- LOG ON
- ( NAME = N'idxtest_log', FILENAME = N'c:SqlDataDataSql2014idxtest_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
Add Comment
Please, Sign In to add comment