Guest User

Untitled

a guest
Dec 13th, 2018
89
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.96 KB | None | 0 0
  1. -- 1. create empty db with second filegroup
  2. CREATE DATABASE [idxtest]
  3. ON PRIMARY
  4. ( NAME = N'idxtest', FILENAME = N'c:SqlDataDataSql2014idxtest.mdf' , SIZE = 10MB , FILEGROWTH = 1MB ),
  5. FILEGROUP [def_test]
  6. ( NAME = N'def_test', FILENAME = N'c:SqlDataDataSql2014def_test.ndf' , SIZE = 10MB , FILEGROWTH = 1MB )
  7. LOG ON
  8. ( NAME = N'idxtest_log', FILENAME = N'c:SqlDataDataSql2014idxtest_log.ldf' , SIZE = 10MB , FILEGROWTH = 1MB);
  9. GO
  10.  
  11. USE [idxtest]
  12.  
  13. -- 2. show default filegroup
  14. select name from sys.filegroups where is_default=1;
  15.  
  16. name
  17. --------------
  18. PRIMARY
  19.  
  20. -- 3. change default filegroup to [def_test]
  21. ALTER DATABASE [idxtest] MODIFY FILEGROUP [def_test] DEFAULT
  22.  
  23. The filegroup property 'DEFAULT' has been set.
  24.  
  25. -- 4. demonstrate new default filegroup
  26. select name from sys.filegroups where is_default=1;
  27.  
  28. name
  29. --------------
  30. def_test
  31.  
  32. -- 5. test table creation (1)
  33. CREATE TABLE tab1 (x int) ON [DEFAULT]
  34.  
  35. -- 6. show filegroup
  36. SELECT object_name(i.object_id) TabName, i.[name] IdxName, f.[name] FileGroupName
  37. FROM sys.indexes i INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
  38. WHERE object_name(i.object_id) = 'tab1'
  39.  
  40. TabName IdxName FileGroupName
  41. -------------- -------------- --------------
  42. tab1 NULL def_test
  43.  
  44. -- 7. change default filegroup to [PRIMARY]
  45. ALTER DATABASE [idxtest] MODIFY FILEGROUP [PRIMARY] DEFAULT
  46.  
  47. The filegroup property 'DEFAULT' has been set.
  48.  
  49. -- 8. show default filegroup
  50. select name from sys.filegroups where is_default=1;
  51.  
  52. name
  53. --------------
  54. PRIMARY
  55.  
  56. -- 9. test index creation
  57. create index idx_tab1$1 on tab1(x) on [DEFAULT]
  58.  
  59. -- 10. show filegroup
  60. SELECT object_name(i.object_id) TabName, i.[name] IdxName, f.[name] FileGroupName
  61. FROM sys.indexes i INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
  62. WHERE object_name(i.object_id) = 'tab1'
  63.  
  64. TabName IdxName FileGroupName
  65. -------------- -------------- --------------
  66. tab1 NULL def_test
  67. tab1 idx_tab1$1 def_test
Add Comment
Please, Sign In to add comment