Introduction System Databases in SQL Server

SQL Server 2008 uses five system databases to store system information, track operations, and provide a temporary work area.

Master:  Master database holds information about the running server’s databases and server’s Configuration. It contains all of the system level information for SQL Server – all logins, linked servers, endpoints, and other system-wide configuration settings.

SQL Server cannot start if the master database is unavailable because master database records the existence of all other databases and the location of those database files and records the initialization information for SQL Server.

We can use master database objects in other databases e.g. store procedure in master database may be called from user database.

If master database is corrupted and sql server is start with damaged Master database then master database will be restored from backup.Sometimes Master database is corrupted and we cannot start SQL Server.  Then master database cannot be restored.  In this situation rebuild the master database using command prompt and restored from latest backup once again.

Msdb: Msdb database Maintains lists of activities, such as backups and jobs, Maintenacne  plan, DTS packages ,database mail, Service Broker ,and tracks which database backup goes with which user database.  By default msdb use simple recovery model. It  is database for sql agent.

Model: Model database is template database for new databases. Any object placed in the

 model database will be copied into any new database.

Tempdb: The tempdb is a temporary workspace  for storing temporary tables, worktables that hold intermediate results during the sorting or query processing ,batches, stored procedures (including Microsoft stored procedures), and the     SQL Server engine itself. If SQL Server needs to create temporary heaps or lists during query execution, it creates them in tempdb. tempdb is dropped and recreated when SQL Server is restarted.

The tempdb is created from model database and reset to its last configured size.
we cannot backup and restore tempdb.

Resource: This hidden and read-only database, added in SQL Server 2005. It contains

contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.

Difference between @@IDENTITY, SCOPE_IDENTITY () and IDENT_CURRENT (TABLE).

When we are working with identity columns the problem arise how to determining the value of the identity (column with identity property) that was just created. The new identity value is created with SQL Server at the time of the insert and we want to display this inserted identity value to user or we want to display the row on a user-interface grid within an application.

1     @@IDENTITY: This is global variable .It returns the last identity value generated by SQL Server for any table, connection, or scope. If another insert takes place between the time of your insert and the time when you check @@IDENTITY, @@IDENTITY will return not your insert, but the last insert. For this reason, don’t use @@IDENTITY.

Example: Suppose you have table category and a insert trigger on category table. This trigger is used to insert identity value in Product table. Then @@IDENTITY returns the last inserted value from Product table.

---------------Session 1---------------

If OBJECT_ID('Category','U') is not null

Drop table Category

go

Create table Category(CatID int not null identity(1,1),CatName varchar(20))

go

If OBJECT_ID('Product','U') is not null

Drop table Product

go

Create table Product(ProdID int not null identity(1000,1),ProdName varchar(20))

go

------------Insert Trigger on Category Table----------

Create Trigger CategoryTrigger ON Category FOR INSERT

AS

BEGIN

            INSERT into Product(ProdName) values('Product1');

END;

---------insert in Category table----

insert into Category(CatName) values('PromDresses');

-------------Check value of both table-------

select * from Category

select * from Product

------------Check value of @@IDENTITY-------

select @@IDENTITY

            ---This will return 1000

2    SCOPE_IDENTITY (): This is function. It returns the last generated identity value within the scope of the calling batch or procedure rather than identity created by any trigger or any udf (user defined function). It is the safest way to determine the identity value   you last generated.

----Check scope identity----

select scope_identity()

------This will return 1 last inserted value of category table in current session

3    IDENT_CURRENT (TABLE): This function returns the last identity value of a specific table in any session and any scope.

-------Checking Ident_current Value-------

Select Ident_current('Category')

-----This will return 1-------

Select Ident_current('Product')

---------This will return 100--------------

Check all the values in new session(Open new query window)

--------------Session 2----------------

Select @@IDENTITY -------It will return null

Select Scope_Identity()--------return null

Select Ident_current('Category')

-----This will return 1-----------------------

Select Ident_current('Product')

---------This will return 100----------------