Ms SQL Server

Creating Database

Lecturer: Heng Bora Page 1

 Ms SQL Server

I- Database Files
SQL Server databases have three types of files:

1- Primary data files

The primary data file is the starting point of the database and points to the other files in the database.
Every database has one primary data file. The recommended file name extension for primary data
files is .mdf.

2- Secondary data files

Secondary data files make up all the data files, other than the primary data file. Some databases may
not have any secondary data files, while others have several secondary data files. The recommended
file name extension for secondary data files is .ndf.

3- Log files

Log files hold all the log information that is used to recover the database. There must be at least one
log file for each database, although there can be more than one. The recommended file name
extension for log files is .ldf.

II-Database FileGroup
Database objects and files can be grouped together in filegroups for allocation and administration
purposes. There are two types of filegroups:

1- Primary

The primary filegroup contains the primary data file and any other files not specifically assigned to
another filegroup. All pages for the system tables are allocated in the primary filegroup.

2- User-defined

User-defined filegroups are any filegroups that are specified by using the FILEGROUP keyword in a
CREATE DATABASE or ALTER DATABASE statement.

Log files are never part of a filegroup. Log space is managed separately from data space.

No file can be a member of more than one filegroup. Tables, indexes, and large object data can be
associated with a specified filegroup. In this case, all their pages will be allocated in that filegroup, or
the tables and indexes can be partitioned. The data of partitioned tables and indexes is divided into
units each of which can be placed in a separate filegroup in a database. For more information about
partitioned tables and indexes, see Partitioned Tables and Indexes.

One filegroup in each database is designated the default filegroup. When a table or index is created
without specifying a filegroup, it is assumed all pages will be allocated from the default filegroup.
Only one filegroup at a time can be the default filegroup. Members of the db_owner fixed database
role can switch the default filegroup from one filegroup to another. If no default filegroup is specified,
the primary filegroup is the default filegroup.

Lecturer: Heng Bora Page 2

 Ms SQL Server

File and Filegroup Example

The following example creates a database on an instance of SQL Server. The database has a primary
data file, a user-defined filegroup, and a log file. The primary data file is in the primary filegroup and
the user-defined filegroup has two secondary data files. An ALTER DATABASE statement makes
the user-defined filegroup the default. A table is then created specifying the user-defined filegroup.

USE master;
GO
-- Create the database with the default data
-- filegroup and a log file. Specify the
-- growth increment and the max size for the
-- primary data file.

CREATE DATABASE MyDB

ON PRIMARY
( NAME='MyDB_Primary',
FILENAME=
'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\MyDB_Prm.mdf',
SIZE=4MB,
MAXSIZE=10MB,
FILEGROWTH=1MB),
FILEGROUP MyDB_FG1

( NAME = 'MyDB_FG1_Dat1',
FILENAME =
'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\MyDB_FG1_1.ndf',
SIZE = 1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB),

( NAME = 'MyDB_FG1_Dat2',
FILENAME =
'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\MyDB_FG1_2.ndf',
SIZE = 1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB)

LOG ON
( NAME='MyDB_log',
FILENAME =
'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\MyDB.ldf',
SIZE=1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB);

GO

ALTER DATABASE MyDB

MODIFY FILEGROUP MyDB_FG1 DEFAULT;
GO

-- Create a table in the user-defined filegroup.

USE MyDB;
CREATE TABLE MyTable
( cola int PRIMARY KEY,
colb char(8) )

Lecturer: Heng Bora Page 3

 Ms SQL Server

ON MyDB_FG1;
GO

The following illustration summarizes the results of the previous example.

III- System Database

1- The master Database

The master database stores all the system-level information for SQL Server. The data stored by the
master database includes information for: configuration settings, logon accounts, linked servers and
endpoints, user database file locations, and properties.

Due to the nature of the data stored, SQL Server cannot operate without the master database. So it is a
very good idea to backup this database after changing the SQL Server configuration, modifying, adding,
or removing any databases.

2- The model Database

SQL Server uses the model database for creating new databases. When the “create database” statement is
used, SQL Server copies the contents of the model database to the newly created database. If there are
any common database objects that would prove useful in all databases created by SQL Server, it is
possible to add those objects to the model database. Then when a database is created by the SQL Server
instance, the user defined objects will be copied to it along with the default objects. Since SQL Server
recreates the tempdb database every time it is started, the model database is required in order for SQL
Server to start.

Lecturer: Heng Bora Page 4

 Ms SQL Server

3- The msdb Database

The msdb database is used by SQL Server to store information on operations performed by SQL Server.
This includes information for: the SQL Server Agent, Database Mail, the Service Broker, log shipping,
backup, and maintenance plan job parameters.

4- The tempdb Database

As the name implies, SQL Server uses the tempdb database for storing temporary data and data
objects. The tempdb database is used when an operation requires a temporary table, stored procedure, or
other database object to be performed. Intermediary data for large sort operations is also stored in the
tempdb database as well as temporary data for internal SQL Server operations.

Every time SQL Server is restarted, the tempdb system database is recreated thus clearing any
temporary data stored during the last SQL Server session. In cases where a high volume of users and
operations are performed with SQL Server the tempdb database can grow to use a significantly large
amount of disk space. It is important to plan accordingly in these scenarios since running out of disk
space where the tempdb database is stored will have catastrophic effects on the operation of SQL Server.

IV- Create Database

1- Create Database SQL Server Management Studio

Please follow the steps below to create a database.

a- Open SQL Server Management Studio.

b- Right click “Databases” and click “New Database”.

Lecturer: Heng Bora Page 5

 Ms SQL Server

c- Type the name of the Database called “LibSys” and change the Path of the database (You can use
your own directory). Then click OK.

Now you will see “LibSys” under Databases of Object Explorer.

Lecturer: Heng Bora Page 6

 Ms SQL Server

You have already finished creating the database

Lecturer: Heng Bora Page 7

 Ms SQL Server

2- Create Database with Transaction-SQL

a- Syntax Create Database

CREATE DATABASE database_name

ON [ PRIMARY ]

( NAME = DataFileName,

FILENAME = 'Path\DataFileName.mdf',

SIZE = size,

MAXSIZE = { max_size | UNLIMITED }

FILEGROWTH = growth_increment

………………….

LOG ON

( NAME = LogFileName,

FILENAME = 'Path\ LogFileName.ldf ',

SIZE = size,

MAXSIZE = { max_size | UNLIMITED },

FILEGROWTH = growth_increment

Note

- ON : specifies the disk file used to store the data portion of the database

- LOG ON : specifies the disk files used to store log files

Lecturer: Heng Bora Page 8

 Ms SQL Server

Ex1: Creating a database that specifies the data and transaction log files

The following example creates the database Sales. Because the keyword PRIMARY is not used, the
first file (Sales_dat) becomes the primary file. Because neither MB nor KB is specified in the SIZE
parameter for the Sales_dat file, it uses MB and is allocated in megabytes. The Sales_log file is
allocated in megabytes because the MB suffix is explicitly stated in the SIZE parameter.

USE master;
GO
CREATE DATABASE Sales
ON
( NAME = Sales_dat,
FILENAME = 'C:\Usea\saledat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,
FILENAME = 'C:\Usea\salelog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB ) ;
GO

Ex2: Creating a database that has filegroups

The following example creates the database Sales that has the following filegroups:

• The primary filegroup with the files Spri1_dat and Spri2_dat. The FILEGROWTH increments for
these files are specified as 15%.
• A filegroup named SalesGroup1 with the files SGrp1Fi1 and SGrp1Fi2.
• A filegroup named SalesGroup2 with the files SGrp2Fi1 and SGrp2Fi2.

This example places the data and log files on different disks to improve performance.

Lecturer: Heng Bora Page 9

 Ms SQL Server

USE master;
GO
CREATE DATABASE Sales
ON PRIMARY
( NAME = SPri1_dat,
FILENAME = 'C:\Usea\SPri1dat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
( NAME = SPri2_dat,
FILENAME = 'C:\Usea\SPri2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
FILEGROUP SalesGroup1
( NAME = SGrp1Fi1_dat,
FILENAME = 'C:\Usea\SG1Fi1dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
( NAME = SGrp1Fi2_dat,
FILENAME = 'C:\Usea\SG1Fi2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
FILEGROUP SalesGroup2
( NAME = SGrp2Fi1_dat,
FILENAME = 'C:\Usea\SG2Fi1dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
( NAME = SGrp2Fi2_dat,
FILENAME = 'C:\Usea\SG2Fi2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,
FILENAME = 'C:\Usea\salelog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB ) ;
GO

Lecturer: Heng Bora Page 10

 Ms SQL Server

b- After a database is created, to check details of the database use following

command :

Sp_helpdb [ database_name ]

Ex:

c- To drop a user defined database use following command:

DROP DATABASE database_name

Note: you cannot delete a system-defined database.

Ex: drop database sales

d- To rename a user-defined database use :

sp_renamedb old_database_name , new_database_name

Ex: SP_renameDB Sales, SaleDB

Lecturer: Heng Bora Page 11

 Ms SQL Server

e- Sp_helpFileGroup : use to display FileGroup

Note: How To Change FileGroup

1- Add FileGroup to Database

ALTER DATABASE DB_Name ADD FILEGROUP FileGroupName

2- Add File to filegroup

alter database DB_Name

Add File
(

NAME = DataFileName,

FILENAME = 'Path\DataFileName.ndf',

SIZE = size,

MAXSIZE = { max_size | UNLIMITED }

FILEGROWTH = growth_increment

)to Filegroup FileGroupName

Lecturer: Heng Bora Page 12