Working with Databases in Sql Server

Sql Server Tutorial Lesson 2: Working with Databases


A Database in Sql Server consists of mainly database objects like Tables, Stored Procedures, User Defined Functions, Views and so on. Let us first understand how to Create Database, once we create the Database we can add other database objects to it which we learn over the course of this Tutorial.


Below is the Basic Syntax for Creating a Database in Sql Server


Demo 1: Let us Create a DataBase with Name SqlHintsTutorial



A database instance will usually be having multiple System Defined databases and User created databases. We can use the USE statement to select the Database on which we want to perform the database operations. Basically USE statement changes the database context to the Specified Database. Below is the basic syntax of the USE statement.

USE DatabaseName

Demo 2:Let us make the SqlHintsTutorial database as the current contextual database.

USE SqlHintsTutorial


We can drop a database using DROP Statement. Dropping a database will permanently removes all the information stored in it, so be careful before using it. Below is the basic syntax for Dropping the Database.


We can’t drop the database which is the current contextual database. To do it we have to change the current contextual database to some-other database and then issue the DROP statement as shown below.

DEMO 3: Let us try to drop a database which is the current Contextual database

USE SqlHintsTutorial
DROP DATABASE SqlHintsTutorial

Msg 3702, Level 16, State 3, Line 1
Cannot drop database “SqlHintsTutorial” because it is currently in use.

DEMO 4: Below script demonstrate how to delete the current contextual database by changing the Current Context DB to some other database and then drop it

 -- Change Current db Context to System Database TempDB
DROP DATABASE SqlHintsTutorial

System Databases

Below are the list of System Databases which shipped with Sql Server by default. These databases are required for the Sql Server to function smoothly.

System Database Description
MASTER Records all system-level information for an instance of SQL Server.
MSDB Used by SQL Server Agent for scheduling alerts and jobs.
MODEL Used as the template for all databases created on the instance of SQL Server. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward.
TEMPDB It is used for holding temporary objects or intermediate result-sets.

Leave a Reply

Your email address will not be published. Required fields are marked *