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

SQL–Structured 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)

Database

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)

Types

1. System databases

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

2. 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.

5. MSDB

• 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: http://msdn.microsoft.com/en-us/library/ms178028.aspx