SELECT*

I we have a primary key and Foreign key relations are there in the tables we can not delete the data from primary key value. Since that value used in the child tables. But this Possible if we create table with ON DELETE CASCADE option. Example is given below. CREATE TABLE Items (Sno INT PRIMARY KEY, [Name] VARCHAR(50)) INSERT INTO Items VALUES(1,'Pen') INSERT INTO Items VALUES(2,'Paper') INSERT INTO Items VALUES(3,'Pencil') INSERT INTO Items VALUES(4,'Sketches') SELECT * FROM Items GO CREATE TABLE Orders ( OrderID INT PRIMARY KEY , Sno INT CONSTRAINT FK_02345 FOREIGN KEY REFERENCES Items(Sno) , Qty INT , FOREIGN KEY (Sno) REFERENCES Items ON DELETE CASCADE ) INSERT INTO Orders VALUES(2,3,15) DELETE FROM Items--ParentTable WHERE Sno=3 SELECT * FROM Orders

CREATE TABLE EmpSalWithDeptWise (EmpID INT,EName CHAR(5),Salary MONEY,DeptID INT) GO empid ename sal deptid 101 A 23000 10 102 B 12000 10 103 C 8000 20 104 D 15000 30 105 E 32000 10 106 F 22000 20 107 G 5000 30 108 H 14000 30 109 I 16000 20 110 J 19000 10 111 K 7000 10 112 L 45000 20 113 M 22000 10 114 N 16000 10 115 O 11000 20 GO SELECT Q.empid,Q.ename, Q.Salary,Q.deptid ,Q.Salary FROM ( SELECT T.empid, T.ename, deptid ,Salary, Row_Order =( SELECT COUNT(T1.empid) + 1 FROM dbo.EmpSalWithDeptWise T1 WHERE T1.Salary < T.Salary AND t1.deptid = t.deptid ) FROM dbo.EmpSalWithDeptWise T)Q WHERE Q.Row_Order <= 2 ORDER BY Q.deptid

We wish to know what locks are being held by transaction. SELECT L.request_session_id AS SPID, DB_NAME(L.resource_database_id) AS DatabaseName, O.Name AS LockedObjectName, P.object_id AS LockedObjectId, L.resource_type AS LockedResource, L.request_mode AS LockType, ST.text AS SqlStatementText, ES.login_name AS LoginName, ES.host_name AS HostName, TST.is_user_transaction as IsUserTransaction, AT.name as TransactionName, CN.auth_scheme as AuthenticationMethod FROM sys.dm_tran_locks L JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id JOIN sys.objects O ON O.object_id = P.object_id JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST WHERE