Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Blog Forum

My name is Muthukkumaran Kaliyamoorthy and I am living in India (Chennai). 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.

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

 

Comments

Leave a comment on the original post [www.sqlserverblogforum.com, opens in a new window]

Loading comments...