•  
  • Archives for SQL basics for freshers (1)

SQL server basics for beginners

 

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

 

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

page 1 of 1

Welcome , today is Saturday, August 2, 2014