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 default data file and log file. Data file is called mdf, ndf…. (One-mdf,more than one ndf). Log file is called ldf(More than one ldf)
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.
- 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 information.
- One of the main thing is master and resource DB must be located in 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.
- Prior to SQL Server 2005 the system related data was stored in the master database.
- Its a read-only database that is not accessible via SSMS.
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 template database for all user defined databases.
- So we could use the model as template to other user databases (such as the recovery model, DB size,SPs etc…)
5. MSDB
- Msdb is the main database to manage the SQL Server Agent configurations.
- It is provides some of the configurations for the SQL Server Agent service (such as jobs alerts ect..)
Important: Keep takes the system database backup regularly except tepmDB.
For More:
