How to check the current identiy value in a table ?

"DBCC CHECKIDENT" Statement is used to check the current identity value for the specified table and if requires then can modify/change the identity value on a identity column.

Syntax:
DBCC CHECKIDENT ('Table_Name' [ , { NORESEED  | { RESEED [ , new_reseed_value ] } } ] )

Example:

CREATE TABLE Product(ProductID int identity(1,1), ProductName varchar(20), Price decimal(18,6) )

INSERT INTO Product VALUES ('Book',150.10),('Pen',35.50)

-- To check the current maximum identity value in the column, execute the following statement


DBCC CHECKIDENT ('Product', NORESEED)

Database States

MS SQL Server Database will be in any of the states.

• ONLINE: Database is available for access.
• OFFLINE: Database is unavailable.
• RESTORING: Database Restore is not completed, so The database is unavailable.
• RECOVERING: Database is being recovered. the database state will become online if the recovery succeeds.
• RECOVERY PENDING: recovery is failed due to missing resources, Additional action by the user is required to resolve the error and let the recovery process be completed.
• SUSPECT: database may be damaged. Additional is required to resolve the problem.
• EMERGENCY: This state prevent all the changes, It allows only readonly and access is limited to members of the sysadmin fixed server role. EMERGENCY is primarily used for troubleshooting purposes.

To verify the current state of a database, following SQL Queries or system funtions will be useful:

SELECT name,state_desc FROM sys.databases

SELECT DATABASEPROPERTYEX ( 'Test' , 'status' )

System Databases

SQL Server includes the following system databases:

• master - captures all the information about an SQL instance
• msdb - used by SQL Server agent(schedule alerts & jobs)
• model - Database Template for creating new DB
• Resource - read only database, contains system objects which are logically accessed via all the db via sys schema
• tempdb - storage for temporary objects

Microsoft SQL Server Code Names

Microsoft has given codenames to all their products, Here is the list of the code name of which are assigned to each version of SQL Server Products.

In 1988, Microsoft released its first version of SQL Server. It was developed jointly by Microsoft and Sybase for the OS/2 platform.

1993 – SQL Server 4.21 for Windows NT
1995 – SQL Server 6.0, codenamed SQL95
1996 – SQL Server 6.5, codenamed Hydra
1999 – SQL Server 7.0, codenamed Sphinx
1999 – SQL Server 7.0 OLAP, codenamed Plato
2000 – SQL Server 2000 32-bit, codenamed Shiloh (version 8.0)
2003 – SQL Server 2000 64-bit, codenamed Liberty
2005 – SQL Server 2005, codenamed Yukon (version 9.0)
2008 – SQL Server 2008, codenamed Katmai (version 10.0)
2010 – SQL Server 2008 R2, Codenamed Kilimanjaro (aka KJ)
2012– SQL Server 2012, Codenamed Denali (version 11.0)
2012 - SQL Server Data Tools, Codenamed Juneau (Next CTP Of Denali)
2012 - SQL Server Data Tools, Codenamed Crescent(Included in Denali Version)
2014 - SQL Server In-Memory OLTP, Codenames Hekaton