Sql Server Tutorial Lesson 2: Working with Databases

DATABASE

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.

CREATE DATABASE

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

CREATE DATABASE DataBaseName

Demo 1: Let us Create a DataBase with Name SqlHintsTutorial

CREATE DATABASE SqlHintsTutorial

USE DATABASE

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

DROP DATABASE

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.

DROP DATABASE DatabaseName

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
GO
DROP DATABASE SqlHintsTutorial

RESULT:
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
USE TempDB
GO
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.