Analysis on Design and Optimization of datetime Data Types in SQL Server

I. Scenarios

In SQL Server 2005, there is a table TestDatetime, where the data type of the Dates field is datetime. If you see a table record, what do you first think?

Figure 1: Data List)

When you see this data, do you think this design is a waste of storage space and makes the index of this column increase, making the query slower, you also want to use some other data types to replace this datetime?

In fact, everyone thinks this way. This direction is 100% correct, but before I write this article, I have entered two misunderstandings: If you have entered the following two misunderstandings, so please read this article .)

Misunderstanding 1: Change the datetime Data Type of the Dates field to smalldatetime, so that the data is changed from: '2017-04-09 00:00:00. 000 'is changed to '2017-04-09 00:00:00', which does not seem to reduce much storage space.

Misunderstanding 2: Change the datetime Data Type of the Dates field to char (10), so that the data is changed from: '2017-04-09 00:00:00. 000 'is changed to '2017-04-09', which seems to reduce a lot of storage space.

Ii. Analysis

In SQL Server 2005, only datetime and smalldatetime data types are supported, but some date data types are added in SQL Server 2008: time, date, smalldatetime, datetime, datetime2, and datetimeoffset. The date type can meet our needs. If you are lucky enough to use SQL Server 2008, congratulations, use the date data type.

However, I am a little sad. On the premise of using SQL Server 2005, I entered misunderstanding 1 and misunderstanding 2. In fact, this is because I ignore the basic things. If I know the size of buckets of different data types, it may be easy to avoid such low-level errors.

In fact, when you view the Dates field in the TestDatetime table, you can see that "-" and ":" In the query results are only used for display, and they are not actually saved in this format.

Datetime occupies 8 bytes, and the first 4 bytes are stored in base date, that is, January 1, 1900) before or after the day, and the last 4 bytes are stored in milliseconds after midnight. Value Range: 1753-01-01 to 9999-12-31.

Smalldatetime occupies four bytes, and the first two bytes are stored in the number of days after base date, January 1, January 1, 1900. The last two bytes are stored in minutes after midnight. Value range: To 2079-06-06.

Date occupies three bytes, which is 1 byte more than the first two bytes of smalldatetime, so the value range is wider. Value Range: 0001-01-01 to 9999-12-31.

Therefore, if you use char (10) To save the truncated date, your storage space will be larger.

Conclusion: If it is SQL Server 2005, use smalldatetime to save half of the data, although it does not seem to change during query. If it is SQL Server 2008, so please use date,

Although there is not much difference between the three bytes and the four bytes, the design and Logic definition are greatly improved, and the gap is sometimes not a matter of 1 byte, for example, when the table data volume reaches several hundred million, there is still a difference, or a record may just give a 8060-byte split because the difference of one byte, which cannot be ignored.

Iii. Test

Next we will test the data storage size, index storage size, and index usage speed. Here we only test the Data Type:, and the data content is the same)

(1) test prelude:

1. Create tables with three data types: char (10), datetime, and smalldatetime. The table structure is shown in the following SQL statement)

 
 

  
  
CREATE TABLE [dbo].[TestDatetime](  
  
  
    [Id] [int] IDENTITY(1,1) NOT NULL,  
  
  
    [Dates] [datetime] NULL,  
  
  
 CONSTRAINT [PK_TestDatetime] PRIMARY KEY CLUSTERED  
  
  
(  
  
  
    [Id] ASC 
  
  
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]  
  
  
) ON [PRIMARY] 
 
 

2. Insert the same record to three tables; insert 1210000 records here)

3. Create an index for the [Dates] field. You can set the fill factor to 100% when creating the index)

4. view the index fragmentation information in the index attribute, view the table data and the space occupied by the index, and test the query efficiency of the [Dates] field index;

(2) test results:

1. Data storage size:

 

Figure 2: Data Space Comparison)

2. Index storage information:

 

Figure 3: char (10 ))

Figure 4: datetime)

Figure 5: smalldatetime)

3. Index query:

For multiple executions, the SQL Server execution time is: [char (10)] most of them are 43 ~ 59 hovering, occasionally appearing less than 10; [datetime] average in 1 ~ 2 ms; [smalldatetime] is within 1 ms. You will find that the other 9 logical reads in [smalldatetime] have changed to 8.

-- [TestChar10]

SQL Server Analysis and Compilation Time:

CPU time = 0 ms, occupied time = 1 ms.

(Row 3 is affected)

Table 'testchar10 '. 1 scan count, 9 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads.

SQL Server execution time:

CPU time = 0 ms, occupied time = 59 Ms.

SQL Server execution time:

CPU time = 0 ms, occupied time = 1 ms.

-- [TestDatetime]

SQL Server Analysis and Compilation Time:

CPU time = 0 ms, occupied time = 1 ms.

(Row 3 is affected)

Table 'testdatetime '. 1 scan count, 9 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads.

SQL Server execution time:

CPU time = 0 ms, occupied time = 2 ms.

SQL Server execution time:

CPU time = 0 ms, occupied time = 1 ms.

-- [TestSmalldatetime]

SQL Server Analysis and Compilation Time:

CPU time = 0 ms, occupied time = 1 ms.

(Row 3 is affected)

Table 'testsmalldatetime '. 1 scan count, 8 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads.

SQL Server execution time:

CPU time = 0 ms, occupied time = 1 ms.

SQL Server execution time:

CPU time = 0 ms, occupied time = 1 ms.

-- New data type in SQL Server 2008

 
 

  
  
SELECT 
  
  
CAST('2007-05-08 12:35:29. 1234567 +12:15' AS time(7)) AS 'time' 
  
  
,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS date) AS 'date' 
  
  
,CAST('2007-05-08 12:35:29.123' AS smalldatetime) AS 
  
  
'smalldatetime' 
  
  
,CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime' 
  
  
,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(7)) AS 'datetime2' 
  
  
,CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS 'datetimeoffset'; 
 
 

Iv. References

Date and Time data types and functions (Transact-SQL)

LEN (Transact-SQL)

DATALENGTH (Transact-SQL)

Smalldatetime and datetime Storage