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
CREATE DATABASE DataBaseName
Demo 1: Let us Create a DataBase with Name SqlHintsTutorial
CREATE DATABASE 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.
Demo 2:Let us make the SqlHintsTutorial database as the current contextual 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
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
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.
|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.|