Recently, I encountered a performance problem of SQL Server running SQL, which is also a relational database. Therefore, at the principle level, some contents of different databases can be used for reference, but some details and operation levels of SQL Server are slightly different, which need to be familiar with and accumulated.

To simulate, you need to insert some test data, especially a string type field that stores the date (required format: yyyymmdd). I use the following operation methods, which may be a little around. If you have a better plan, you can put it forward and learn together.

1. Create a test table

create table t1(id int identity(1,1) primary key, c1 varchar(10));

2. Insert 10000 test data

insert into t1 default values
go 10000

Two things to note,

(1) go syntax prompts errors in dbaver and can be executed in SQL Server Management Studio.

(2) 'go 10000' cannot have ';'.

At this time, the id field in t1 will get a value from 1 to 10000 due to the setting of Idntity, but the c1 field is empty. Due to the test requirements, the c1 field needs to store the date value of "yyyymmdd".

3. Generate data intermediate table of random date

SQL Server can generate random numbers by using the function rand(), for example,

select cast(rand()*1000 as int);

If you find a way to generate random dates,

declare @bdate date, @edate date
set @bdate = '20210101'
set @edate = '20210601'
select id, random_date = convert(varchar(10), dateadd(day, abs(checksum(newid())))%datediff(day, @bdate, @edate), @bdate), 112)
into t2 from t1;

Several knowledge points,

(1) @bdate and @ edate are the upper and lower limits for generating random dates.

(2) The dateidff function is used to obtain the date / time difference. The input parameters are three, (datepart, startdate, enddate). Datepart indicates the number of date or time boundaries crossed between two specified dates (startdate and enddate). The day used here is calculated by day, but can also be changed to second, calculated by second, etc.

(3) The checksum () function returns the checksum value calculated according to a row or a set of expressions in the table. It can change the text into a string of numbers.

(4) newid() returns a new GUID number that never repeats and is irregular.

(5) As the name suggests, the DateAdd function adds a time interval to the specified datepart of the specified date and returns a new datetime value. The input parameters are three, (datepart, number, date).

(6) The convert function, which is interesting, returns date strings in various formats, as shown below. What we want is "yyyymmdd", so the value is 112,

Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 1): 05/16/06
Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16
Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06
Select CONVERT(varchar(100), GETDATE(), 4): 16.05.06
Select CONVERT(varchar(100), GETDATE(), 5): 16-05-06
Select CONVERT(varchar(100), GETDATE(), 6): 16 05 06
Select CONVERT(varchar(100), GETDATE(), 7): 05 16, 06
Select CONVERT(varchar(100), GETDATE(),: 10:57:46
Select CONVERT(varchar(100), GETDATE(), 9): 05 16 2006 10:57:46:827AM
Select CONVERT(varchar(100), GETDATE(), 10): 05-16-06
Select CONVERT(varchar(100), GETDATE(), 11): 06/05/16
Select CONVERT(varchar(100), GETDATE(), 12): 060516
Select CONVERT(varchar(100), GETDATE(), 13): 16 05 2006 10:57:46:937
Select CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967
Select CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47
Select CONVERT(varchar(100), GETDATE(), 21): 2006-05-16 10:57:47.157
Select CONVERT(varchar(100), GETDATE(), 22): 05/16/06 10:57:47 AM
Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16
Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47
Select CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250
Select CONVERT(varchar(100), GETDATE(), 100): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 101): 05/16/2006
Select CONVERT(varchar(100), GETDATE(), 102): 2006.05.16
Select CONVERT(varchar(100), GETDATE(), 103): 16/05/2006
Select CONVERT(varchar(100), GETDATE(), 104): 16.05.2006
Select CONVERT(varchar(100), GETDATE(), 105): 16-05-2006
Select CONVERT(varchar(100), GETDATE(), 106): 16 05 2006
Select CONVERT(varchar(100), GETDATE(), 107): 05 16, 2006
Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49
Select CONVERT(varchar(100), GETDATE(), 109): 05 16 2006 10:57:49:437AM
Select CONVERT(varchar(100), GETDATE(), 110): 05-16-2006
Select CONVERT(varchar(100), GETDATE(), 111): 2006/05/16
Select CONVERT(varchar(100), GETDATE(), 112): 20060516
Select CONVERT(varchar(100), GETDATE(), 113): 16 05 2006 10:57:49:513
Select CONVERT(varchar(100), GETDATE(), 114): 10:57:49:547
Select CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49
Select CONVERT(varchar(100), GETDATE(), 121): 2006-05-16 10:57:49.700
Select CONVERT(varchar(100), GETDATE(), 126): 2006-05-16T10:57:49.827:
Select CONVERT(varchar(100), GETDATE(), 131): 18/04/1427 10:57:49:920AM

Through the above functions, a random date string can be generated. "Select... Into t2 from t1" takes all records (10000 pieces) from the t1 table, including the self incremented primary key field id and the randomly generated date string c1 for each row, and inserts them into t2. The field names are id and random_date.

4. Association update t1

In fact, in the third step, the record of t2 table already contains the numeric field id and the random date string field c1, which meets the test requirements. If t1 is necessary, you can update it by associating t2 and updating t1,

update t1 set t1 = t2.random_date
  from t1, t2
 where t1.id = t2.id;

In fact, many functions have been seen in various development languages and databases in terms of names, so many technical knowledge are interlinked. Only by drawing inferences from one instance and integrating them can we slowly improve our level.