Basics for freshers

SQL server basics for beginners



In this article, I am going to discuss about the basics of SQL server which is helpful for newbies.

SQLStructured Query Language (pronounced as SEQUEL) is a Database, computer language designed for managing data in relational database management system (RDBMS).

Most of the people have to know SQL server query has designed for set based (declarative) .

The SQL commands are


DQL (Select)

DML (Insert, Update, Delete)

DDL (Create, Alter, Drop, Truncate)

DCL (Grant, Revoke, Deny)

TCL (Commit, Rollback)



A Database stores all the data information (like objects) and has the default data file and log file. The data file is called mdf, ndf…. (One-mdf,more than one ndf). The log file is called ldf (More than one ldf)


  1. System databases

A system database will create by default when the SQL server installed.

  1. User databases

A user database will create by the user.


SQL Server System Databases

  1. Master

  2. Resource (2005 onwards)

  3. Tempdb

  4. Model

  5. Msdb


1. Master

  • The Master database manages the SQL Server instance. It stores system configuration, logins details etc..
  • In SQL Server 2005 onwards Master database stores only the logical information in the system objects residing in the sys schema.
  • In the previous editions of SQL Server, the Master database physically stores all the system objects, (tables, Sprocs etc.) information.
  • One of the main things is master and resource DB must be located in the same directory. Otherwise the server goes down 🙂


2. Resource

  • Introduced in SQL Server 2005 to help in managing the upgrade and rollback of the system objects (service pack).
  • Prior to SQL Server 2005 the system related data were stored in the master database.
  • It’s a hidden and read-only database that is not accessible via SQL server management studio.

3. TempDB

  • TempDB is a temporary database to store temporary tables like #local, ##global, table variables, cursors, work tables sorted in TempDB, etc…
  • When the SQL Server instance is rebooted, the TempDB database is recreated every time.

4. Model

  • Model is a template database for all user defined databases.
  • So we could use the model as a template for other user databases (such as the recovery model, DB size, SPs etc.…) i.e. Whenever you create a new database the template will taken from the model database.


  • MSDB is the main database to manage the SQL Server Agent configurations.
  • It provides some of the configurations for the SQL Server Agent service (such as jobs alerts etc..) and it stores database backup details.


Important: Keep taking the system database backup regularly except tepmDB.

For More:




Leave a Reply to Vesper Cancel reply

%d bloggers like this: