Design and Optimization of datetime Data Types in SQL Server

Last Update:2018-12-05 Source: Internet

Author: User

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

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. The first 4 bytes store the number of days before or after base date (January 1, 1900), and the last 4 bytes store the number of milliseconds after midnight. Value Range: 1753-01-01 to 9999-12-31.

Smalldatetime occupies four bytes, and the first two bytes store 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, please use smalldatetime, the data can be saved in half, although it does not seem to change during query; if you are 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, in addition, the gap is sometimes not 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 it is 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 (1210000 records are inserted 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: