Index misuse Script

Below Script can be used to create a database PerformanceTraining with a table Employee. This script populates 5 Lakh records in the employee table. Note: Copy paste of this script is not working properly, please correct the quotation marks before running it.

CREATE DATABASE PerformanceTraining
GO
USE PerformanceTraining
GO
CREATE TABLE [Employee](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] Varchar(50),
[LastName]  Varchar(50),
[City]     Varchar(50),
[State]  Varchar(40),
[Country]   VarChar(40),
[BirthDate] datetime,
[MaritalStatus] nchar(1),
[Gender] nchar(1),
[HireDate] datetime,
[CreationDate] datetime,
[ChangeDate] datetime
)
GO
SET NOCOUNT ON

Declare @i int,@State varchar(40),@MaritalStatus nchar(1),@Gender nchar(1)
SET @i=0
while(@i<500000)
Begin
SELECT @i = @i +1,@MaritalStatus=’S’,@Gender=’M’,@State=’Karnataka’

if(@i % 2 =0)
select @MaritalStatus=’M’,@Gender=’F’,@State=’Maharashtra’

INSERT INTO [PerformanceTraining].[dbo].[Employee]
([FirstName],[LastName],[City],
[State],[Country],[BirthDate]
,[MaritalStatus],[Gender],[HireDate]
,[CreationDate],[ChangeDate])
VALUES (‘FirstName’ + CAST(@i AS VARCHAR), ‘LastName’ + CAST(@i AS VARCHAR),’City’ + CAST(@i AS VARCHAR)
,@State,’INDIA’,DATEADD(DAY,@i/1000,’01/01/1978′)
,@MaritalStatus,@Gender,DATEADD(DAY,@i/1000,’01/01/2000 ‘ + CONVERT(VARCHAR(8),GETDATE(),108))
,DATEADD(DAY,@i/1000,’01/01/2000′),DATEADD(DAY,@i/1000,’01/01/2000’))

IF @i = 500000
UPDATE dbo.Employee WITH(ROWLOCK)
SET HireDate = ‘2001-05-16 ‘ + CONVERT(VARCHAR(8),GETDATE(),108)
WHERE EmployeeID = @i

end

GO

3 thoughts on “Index misuse Script

Leave a Reply

Your email address will not be published.