SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

ON Keyword for File Group in SQL Server 2017

By Jayendra Viswanathan,

SELECT INTO clause is introduced in SQL Server 2017 in which we can load table into a specific FileGroup. Lets us see an example.

USE [Master]

GO


DROP DATABASE IF EXISTS EMPDB;


CREATE DATABASE EMPDB;

GO

First Stage  : After executing above step if you run sp_helpdb EMPDB, You will see the FILEGROUP as Primary which is the default.

Data_located_on_filegroup
PRIMARY
USE [Master]

GO

ALTER DATABASE EMPDB ADD FILEGROUP [SECONDARY]

GO


ALTER DATABASE EMPDB 

ADD FILE 

( 

NAME = N'EMPDB_FG1', 

FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\EMPDB_FG1.ndf' , 

SIZE = 23456KB , 

FILEGROWTH = 34567KB 

) 

TO FILEGROUP [SECONDARY]

GO

Second Stage : After executing above step if you run sp_helpdb EMPDB, You will see the FILEGROUP as PRIMARY which is the default and a new FILEGROP as SECONDARY will be added.

filegroup
PRIMARY
SECONDARY
USE EMPDB


CREATE TABLE MyEmployee

(

EName VARCHAR(50),

EDept VARCHAR(15)

) ON [PRIMARY]

GO

INSERT INTO MyEmployee VALUES('Jim','HR')

GO


SELECT EName, EDept INTO Emp_PRIMARY FROM MyEmployee 



SELECT EName, EDept  INTO Emp_SECONDARY ON [SECONDARY] FROM MyEmployee 

Third Stage : After executing above step if you run sp_helpdb Emp_PRIMARY, You will see the FILEGROUP for EMP_PRIMARY table as PRIMARY.

Data_located_on_filegroup

PRIMARY

If you run sp_helpdb Emp_SECONDARY, you will see the FILEGROUP for EMP_SECONDARY table as SECONDARY.

Data_located_on_filegroup

SECONDARY

ON keyword File Group is a very useful feature in SQL Server 2017.

Total article views: 124 | Views in the last 30 days: 7
 
Related Articles
FORUM

Backup FileGroup and Restore on Secondary Database

Backup FileGroup and Restore on Secondary Database with same filegroups

FORUM

Secondary Filegroup when ?

Confused over the use of secondary filegroup

FORUM

Primary Filegroup

Puzzled by Primary Filegroup size

FORUM

How to merge SQL 2005 database files

primary and secondary database file

FORUM

Restoring database - Filegroups

Restoring database - Filegroups

Tags
 
Contribute