SET Operators in T-SQL

Set Operators :

It is used to combine multiple results sets into one single result set.

There are 3 set operators in T-SQL
1) UNION/UNION ALL
2) EXCEPT
3) INTERSECT

UNION/UNION ALL :
----------------------------
It is used to combine two or more result sets into one single result set by with or without duplicates
(ie, UNION doesn't allow null values, but UNION ALL allows null values)

Example:
<<ResultSet>>
UNION or UNION ALL
<<ResultSet>>


 EXCEPT:
------------
It takes the data from one result set where there is no matching in another result set.

Example:
<<ResultSet>>
EXCEPT
<<ResultSet>>

 INTERSECT:
----------------
It takes the data from the result sets which are in common

Example:
<<ResultSet>>
INTERSECT
<<ResultSet>>

How to change the Identity Column value ?

Before Change the Identity value(ProductID column) from the Product Table

 

 

-- To set the new identity value on the Product Table

DBCC CHECKIDENT ('Product', RESEED, 10)

Execution Results as follows

Checking identity information: current identity value '10', current column value '10'.
DBCC execution completed.

To insert a new record in the product table, to test the next identity value

insert into Product values ('Mat',200)

After Change the Identity value

 

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' )