Task Prepare procedures for testing SQL server performance.

Prepare tables with about 1,000,000 rows of data.

Prepare procedures that generate random data.

Check search performance with and without index.

Data fields: key field

1 date type

1 varchar(255)

1 varchar(40)

Use the timekeeping function.

code>>

USE [?????] GO /****** Object: Table [dbo].[test_time] Script Date: 30.11.2022 14:02:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[test_time]( [id] [int] IDENTITY(1,1) NOT NULL, [date] [date] NULL, [string1] [varchar](255) NULL, [string2] [varchar](40) NULL, PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: StoredProcedure [dbo].[create_string] Script Date: 30.11.2022 14:02:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[create_string] @max_l int, @min_l int,@string varchar(255) output AS BEGIN declare @l int, @i int =1, @z int exec rnd_v @max_l,@min_l, @l output set @string ='' while @i<=@l begin exec rnd_v 90,65, @z output set @string=@string+char(@z) set @i=@i+1 end END GO /****** Object: StoredProcedure [dbo].[random_date] Script Date: 30.11.2022 14:02:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[random_date] @date date output as begin declare @y int declare @m int declare @d int declare @s_date varchar(10) exec rnd_v 2022,1900, @y output exec rnd_v 12,1, @m output if @m=2 begin exec rnd_v 28,1, @d output end else begin if @m=4 or @m=6 or @m=9 begin exec rnd_v 30,1, @d output end else begin exec rnd_v 30,1, @d output end end set @s_date=cast(@y as varchar(4))+'-'+cast(@m as varchar(2))+'-'+cast(@d as varchar(2)) set @date =cast(@s_date as date) end GO /****** Object: StoredProcedure [dbo].[rnd_v] Script Date: 30.11.2022 14:02:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create PROCEDURE [dbo].[rnd_v] @x int, @y int, @z int output AS BEGIN set @z=cast(rand()*(@x-@y+1)+@y as int) END GO /****** Object: StoredProcedure [dbo].[test] Script Date: 30.11.2022 14:02:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[test] @n_r int as begin declare @w varchar(255),@w2 varchar(255) declare @r_date date, @i int=1 while @i<=@n_r begin exec create_string 39,3, @w output exec create_string 255,3, @w2 output exec random_date @r_date output insert into test_time values (@r_date, @w2,@w) set @i=@i+1 end end GO