--Createproductstable.
CREATETABLEproducts(idintIDENTITYPRIMARYKEY,productvarchar(40))
GO
--Insertingvaluesintoproductstable.
INSERTINTOproducts(product)VALUES(screwdriver)
INSERTINTOproducts(product)VALUES(hammer)
INSERTINTOproducts(product)VALUES(saw)
INSERTINTOproducts(product)VALUES(shovel)
GO
--Createagapintheidentityvalues.
DELETEproducts
WHEREproduct=saw
GO
SELECT*
FROMproducts
GO
--AttempttoinsertanexplicitIDvalueof3;
--shouldreturnawarning.
INSERTINTOproducts(id,product)VALUES(3,gardenshovel)
GO
--SETIDENTITY_INSERTtoON.
SETIDENTITY_INSERTproductsON
GO
--AttempttoinsertanexplicitIDvalueof3
INSERTINTOproducts(id,product)VALUES(3,gardenshovel)
GO
PRINTUseexplicittransaction
BEGINTRAN
INSERTINTOt1VALUES(2)
SELECTTrancountintransaction=@@TRANCOUNT
COMMITTRAN
SELECTTrancountoutsidetransaction=@@TRANCOUNT
GO
PRINTSettingIMPLICIT_TRANSACTIONSON
GO
SETIMPLICIT_TRANSACTIONSON
GO
PRINTUseimplicittransactions
GO
--NoBEGINTRANneededhere.
INSERTINTOt1VALUES(4)
SELECTTrancountintransaction=@@TRANCOUNT
COMMITTRAN
SELECTTrancountoutsidetransaction=@@TRANCOUNT
GO
PRINTUseexplicittransactionswithIMPLICIT_TRANSACTIONSON
GO
BEGINTRAN
INSERTINTOt1VALUES(5)
SELECTTrancountintransaction=@@TRANCOUNT
COMMITTRAN
SELECTTrancountoutsidetransaction=@@TRANCOUNT
GO
SELECT*FROMt1
GO
--Needtocommitthistrantoo!
DROPTABLEt1
COMMITTRAN
GO
USEpubs
GO
--Displaythecountmessage.
SELECTau_lname
FROMauthors
GO
USEpubs
GO
--SETNOCOUNTtoONandnolongerdisplaythecountmessage.
SETNOCOUNTON
GO
SELECTau_lname
FROMauthors
GO
--ResetSETNOCOUNTtoOFF.
SETNOCOUNTOFF
GO
CREATETABLEt1(aintPRIMARYKEY)
CREATETABLEt2(aintREFERENCESt1(a))
GO
INSERTINTOt1VALUES(1)
INSERTINTOt1VALUES(3)
INSERTINTOt1VALUES(4)
INSERTINTOt1VALUES(6)
GO
SETXACT_ABORTOFF
GO
BEGINTRAN
INSERTINTOt2VALUES(1)
INSERTINTOt2VALUES(2)/*Foreignkeyerror*/
INSERTINTOt2VALUES(3)
COMMITTRAN
GO
SETXACT_ABORTON
GO
BEGINTRAN
INSERTINTOt2VALUES(4)
INSERTINTOt2VALUES(5)/*Foreignkeyerror*/
INSERTINTOt2VALUES(6)
COMMITTRAN
GO