First create a table:
CREATE TABLE [dbo].[emp](
[empid] [int] NULL,
[empname] [varchar](50) NULL,
[gender] [varchar](1) NULL,
[address] [varchar](50) NULL
) ON [PRIMARY]
Then insert a record:
INSERT INTO [DDB].[dbo].[emp]
([empid],[empname],[gender],[address])
VALUES (1,'John','M','Berlin')
Then execute following query:
select * from emp;
Now look at the SQL Server profiler as shown in figure below:
Now drop the existing table and recreated new one with following query:
BEGIN TRANSACTION
GO
CREATE TABLE [dbo].[emp]
(
empid int NOT NULL,
[empname] [varchar](50) NULL,
[gender] [varchar](1) NULL,
[address] [varchar](50) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[emp] ADD CONSTRAINT
PK_Table_1 PRIMARY KEY CLUSTERED
(
empid
) ON [PRIMARY]
GO
COMMIT
USE [DDB]
GO
Again inset the data:
INSERT INTO [DDB].[dbo].[emp]
([empid],[empname],[gender],[address])
VALUES (1,'John','M','Berlin')
INSERT INTO [DDB].[dbo].[emp]
([empid],[empname],[gender],[address])
VALUES (2,'Robert','M','Athens')
INSERT INTO [DDB].[dbo].[emp]
([empid],[empname],[gender],[address])
VALUES (3,'Sami','M','Karachi')
Then execute following query:
select * from emp;
Again check the profiler:
Now create another table with the name dept using the query below:
CREATE TABLE [dbo].[dept](
[deptid] [int] NOT NULL,
[deptname] [varchar](50) NULL,
CONSTRAINT [PK_dept] PRIMARY KEY CLUSTERED
(
[deptid] ASC
) ON [PRIMARY]
) ON [PRIMARY]
Insert the data into the table:
INSERT INTO [DDB].[dbo].[dept]
([deptid],[deptname])
VALUES (1,'Accounts')
GO
INSERT INTO [DDB].[dbo].[dept]
([deptid],[deptname])
VALUES (2,'Budget')
GO
INSERT INTO [DDB].[dbo].[dept]
([deptid],[deptname])
VALUES (3,'Stores')
GO
Update the employee table after adding the new column for deptid:
UPDATE [DDB].[dbo].[emp]
SET [deptid] = 1
WHERE empid = 1;
GO
UPDATE [DDB].[dbo].[emp]
SET [deptid] = 2
WHERE empid = 2;
GO
UPDATE [DDB].[dbo].[emp]
SET [deptid] = 3
WHERE empid = 3;
GO
Now again execute the following query:
Again check the profiler:
Now drop the primary keys constraints from both tables:
BEGIN TRANSACTION
GO
ALTER TABLE dbo.dept
DROP CONSTRAINT PK_dept
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.emp
DROP CONSTRAINT PK_Table_1
GO
COMMIT
Now again execute the following query:
select * from emp join dept on emp.deptid = dept.deptid
where emp.empname = 'john' or dept.deptid = 2;
and check the profiler output.
For understanding of transaction processing, please execute the following statements:
BEGIN TRANSACTION
GO
INSERT INTO [DDB].[dbo].[emp]
([empid],[empname],[gender],[address])
VALUES (7,'Seven','M','Karachi')
Now in a separate window execute the following statements:
BEGIN TRANSACTION
GO
select * from emp;
update [DDB].[dbo].[emp] set empname = 'Test' where empid = 7;
select * from emp;
GO
COMMIT
Observe what happens!
Now execute the following statements in first windows of first transaction that we have created:
delete from [DDB].[dbo].[emp] where empid = 7;
GO
COMMIT
Now observe what happened!
Now check the profiler. Migrate the profile data to sql serve table and execute the following query with correct ClientProcessID.
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 [RowNumber]
,[EventClass]
,[TextData]
,[ApplicationName]
,[LoginName]
,[ClientProcessID]
,[SPID]
,[StartTime]
,[BinaryData]
,[DatabaseID]
,[DatabaseName]
,[EventSequence]
,[HostName]
,[IsSystem]
,[LoginSid]
,[NTDomainName]
,[RequestID]
,[SessionLoginName]
,[TransactionID]
,[IntegerData]
,[ServerName]
,[XactSequence]
,[ObjectName]
,[ObjectID]
,[LineNumber]
,[NestLevel]
,[ObjectType]
,[Duration]
,[EventSubClass]
,[IntegerData2]
,[Offset]
,[SqlHandle]
,[BigintData1]
FROM [DDB].[dbo].[Trace]
where
ClientProcessID =
--lock acquried [EventClass] = 24
--lock released [EventClass] = 23
