Database
A database is a structured collection of records that is stored in a computer system. It contains a set of related database objects
-
Tables - Storages of structured data.
-
Views - Queries to present data from tables.
-
Indexes - Sorting indexes to speed up searches.
-
Stored Procedures - Predefined SQL programming units.
-
Users - Identifications used for data access control.
-
Other objects.
System Databases
System Databases will be created during the time of installation. They will help SQL Server to all the other databases and client execution sessions. There are four default system databases.
-
master - It is the main database of the SQL server – Server configuration details and runtime information will be stored.
-
model – It is a empty database. It is used to create the new databases
-
msdb - It used to background job processes
-
tempdb – For temporary operations
Create New Database
We create a Database using “Create Database” statement
Syntax
CREATE DATABASE
Example
Create Database EmpDB
GO
The New database “EmpDB” created in the Sql Server. Now this database is empty because no table is there. We have to create a table in order to use the database.
Set the Current Database
When you entered into SQL Server, you have to select a database to work with. The “Use” statement will help you to set the current database.
Syntax
USE
Example
Use EmpDB
Delete a Database
Some time you might create the database incorrectly, or you don’t need that database. In that time you can use the “Delete Database” statement to remove the database from the SQL Server
Syntax
Drop Database
Example
Drop Database EmpDB
When you execute the command "Drop Database EmpDB", then the database will be deleted. Suppose you get an error message like "Cannot Drop Database EmpDB because it is currently in Use". This means the database "EmpDB" is in use. There are two ways to avoid this error message. The first way is that you can completely exit from Sql Server then again you can login to the server and can execute the drop database command. Another one is, you can switch to another database and can delete the EmpDB Database
Example
Use Master
Drop Database EmpDB
List Database Names
You can get a list of database names available in your SQL Server. “Sys.Databases” can be used to achieve this.
Example
select * from sys.databases
Primary Data File and Transaction Log
Once the database is created then two files will be created on the hard disk. They are used for storing data and database configuration information. We have created the EmpDB database, so the following files should be created
-
EmpDB.mdf – Primary Data File
-
EmpDB_log.ldf - Transaction Log File
We can check that with the following example
Use EmpDB
SELECT type_desc, physical_name, size FROM sys.database_files
sys.database_files view will help you to display the file names.
Create Database Using Primary Data File and Transaction Log File
We know that when you create a database file then the Primary Data File and Transaction Log File will be created. You can create a database by specifying .mdf and .ldf file name and location.
Let us assume that we are going to create the Database Expenses, and will specify the two files Expenses.mdf and Expenses_log.ldf
-
Expenses.mdf – Primary Data File
-
Expenses.ldf - Transaction Log File
Syntax
CREATE DATABASE database_name
ON (NAME = logical_data_name,
FILENAME = physical_data_name,
SIZE = n, MAXSIZE = n, FILEGROWTH = n)
LOG ON (NAME = logical_log_name,
FILENAME = physical_log_name,
SIZE = n, MAXSIZE = n, FILEGROWTH = n)
Example
CREATE DATABASE Expenses
ON (NAME = ExpensesData,
FILENAME = 'C:\MyDb\Expenses.mdf',
SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
LOG ON (NAME = ExpensesLog,
FILENAME = 'c:\MyDb\Expenses_log.LDF',
SIZE = 1MB, MAXSIZE = 5MB, FILEGROWTH = 1MB)
Above example, will create the database files located in the C:\MyDb directory:
Rename a Database
Sometimes you may like to change the database name. “Alter Database” statement will help you to change the database name.
Syntax
ALTER DATABASE
Modify Name =
Example
use Expenses
ALTER DATABASE Expenses MODIFY NAME = ExpensesDb
Set Database state as Offline
We set Database status to offline that means it will not available for some specific reasons (admin operations). If the change is done we can make it available.
Syntax
Alter Database Set Offline
Example
Alter Database Expenses Set Offline
If Expenses is currently in use then the following error will be displayed.
"Failed to restart the current database. The current database is switched to master."
So you can change the current database to msdb and then execute the command
Example
use msdb
Alter Database Expenses Set offline
In the same manner you can set the database name to online
Example
Alter Database Expenses Set online
Now the database will be available for transactions.
List the Status of all the Databases
sys.databases view will list all of the states of the databases in your system.
Example
SELECT name, state_desc from sys.databases
Move the Physical File
As per our previous examples we are having the Expenses.mdf and Expenses_log.LDF' in the c:\MyDb location. Suppose you moved them to the d:\MyDb location you can use alter database command. First you can set the database status to offline then you can change the physical location then can set to online
Example
Alter Database Expenses Set offline
Go
ALTER DATABASE Expenses
MODIFY FILE (NAME = ExpensesData,
FILENAME = 'd:\MyDb\Expenses.mdf')
GO
The file "ExpensesData" has been modified in the system catalog. The new path will be used the next time the database is started.
ALTER DATABASE Expenses
MODIFY FILE (NAME = ExpensesLog,
FILENAME = 'd:\MyDb\Expenses_Log.ldf')
The file "ExpensesLog" has been modified in the system catalog. The new path will be used the next time the database is started.
Now you can use the sys.database_files view to view the physical location of the database
Alter Database Expenses Set online
Go
SELECT type_desc, name, physical_name, size
FROM sys.database_files
Set Database to Readonly
There are two options in database. They are READ_WRITE and READ_ONLY. READ_WRITE is the default option and you can do write and read operations. In READ_ONLY state you are allowed to write query buy you cannot do any modification operations.
Alter Database is used to set the Update options. First we are going to set the database state to readonly
Syntax
ALTER DATABASE Expenses SET READ_ONLY
Example
use Expenses
go
ALTER DATABASE Expenses SET READ_ONLY
go
insert into tblExp values('Travel',250)
Result
Msg 3906, Level 16, State 1, Line 1
Failed to update database "Expenses" because the database is read-only.
Here after setting the Expenses database to read only, then tried to insert a row in the tblExp. Hence we get an error.
So you can set the database status to READ_WRITE and then try to insert the rows
use Expenses
go
ALTER DATABASE Expenses SET READ_WRITE
go
insert into tblExp values('Travel',250)
Result
(1 row(s) affected)
Now one row will be inserted.
User Access Options
There are three options for users.
-
MULTI_USER – All users can access the database. It is the default option.
-
SINGLE_USER – Only one user at a time allowed to use the database.
-
RESTRICTED_USER –Only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles are allowed to connect to the database, but it does not limit their number.
You can use the Alter Database statement to set the User access option
use Expenses
GO
ALTER DATABASE Expenses SET SINGLE_USER
GO
Now connect to server with another client session and try:
USE Expenses
GO
Result
Msg 924, Level 14, State 1, Line 1
Database 'Expenses' is already open and can only have one user at a time.
Go back to the first session and re-set the database to MULTI_USER:
ALTER DATABASE Expenses SET MULTI_USER
GO
Now you can access the database in multiple client sessions.