Design and Optimization of datetime Data Types in SQL Server

Source: Internet
Author: User

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:

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

1. SELECT

2. CAST ('2017-05-08 12:35:29. 2007 + 12: 15' AS time (7) AS 'time'

3. CAST ('2017-05-08 12:35:29. 2007 + 12: 15' AS date) AS 'date'

4., CAST ('2017-05-08 12:35:29. 100' AS smalldatetime)

5. 'smalldatetime'

6. CAST ('2017-05-08 12:35:29. 2007 'AS datetime) AS 'datetime'

7., CAST ('2017-05-08 12:35:29. 2007 + 12: 15' AS datetime2 (7) AS 'datetime2'

8., CAST ('2017-05-08 12:35:29. 2007 + 12: 15' AS datetimeoffset (7) AS 'datetime

Link: http://www.365master.com/manage/3/2010/1129/60900.shtml

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.