Below Script can be\u00a0used to create a database PerformanceTraining\u00a0with a\u00a0table Employee. This script populates 5\u00a0Lakh records in the employee table. Note: Copy paste of this script is not working properly, please correct the quotation marks before running it.<\/p>\n
CREATE DATABASE PerformanceTraining
\nGO
\nUSE PerformanceTraining
\nGO
\nCREATE TABLE [Employee](
\n[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
\n[FirstName] Varchar(50),
\n[LastName]\u00a0 Varchar(50),
\n[City]\u00a0\u00a0\u00a0\u00a0 Varchar(50),
\n[State]\u00a0 Varchar(40),
\n[Country]\u00a0\u00a0 VarChar(40),
\n[BirthDate] datetime,
\n[MaritalStatus] nchar(1),
\n[Gender] nchar(1),
\n[HireDate] datetime,
\n[CreationDate] datetime,
\n[ChangeDate] datetime
\n)
\nGO
\nSET NOCOUNT ON<\/p>\n
Declare @i\u00a0int,@State varchar(40),@MaritalStatus\u00a0nchar(1),@Gender nchar(1)
\nSET @i=0
\nwhile(@i<500000)
\nBegin
\nSELECT @i = @i\u00a0+1,@MaritalStatus=’S’,@Gender=’M’,@State=’Karnataka’<\/p>\n
if(@i % 2 =0)
\nselect @MaritalStatus=’M’,@Gender=’F’,@State=’Maharashtra’<\/p>\n
INSERT INTO [PerformanceTraining].[dbo].[Employee]
\n([FirstName],[LastName],[City],
\n[State],[Country],[BirthDate]
\n,[MaritalStatus],[Gender],[HireDate]
\n,[CreationDate],[ChangeDate])
\nVALUES (‘FirstName’ + CAST(@i\u00a0AS VARCHAR), ‘LastName’ + CAST(@i\u00a0AS VARCHAR),’City’ + CAST(@i AS VARCHAR)
\n,@State,’INDIA’,DATEADD(DAY,@i\/1000,’01\/01\/1978′)
\n,@MaritalStatus,@Gender,DATEADD(DAY,@i\/1000,’01\/01\/2000 ‘ + CONVERT(VARCHAR(8),GETDATE(),108))
\n,DATEADD(DAY,@i\/1000,’01\/01\/2000′),DATEADD(DAY,@i\/1000,’01\/01\/2000’))<\/p>\n
IF @i = 500000
\nUPDATE dbo.Employee WITH(ROWLOCK)
\nSET HireDate = ‘2001-05-16 ‘ + CONVERT(VARCHAR(8),GETDATE(),108)
\nWHERE EmployeeID = @i<\/p>\n
end<\/p>\n
GO<\/p>\n","protected":false},"excerpt":{"rendered":"
Below Script can be\u00a0used to create a database PerformanceTraining\u00a0with a\u00a0table Employee. This script populates 5\u00a0Lakh 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), … Continue reading Index misuse Script<\/span>