sql server data types choosing and using
DESCRIPTION
SQL Server Data Types Choosing and Using. Vern Rabe. Independent consultant, trainer MCT, MCDBA, MCSE, MCITP SQL Server Pro magazine advisory board Oregon PASS Steering Committee Working with/focusing on SQL Server since 1993, starting with SQL Server 4.21a. Data Types What We’ll Cover. - PowerPoint PPT PresentationTRANSCRIPT
![Page 2: SQL Server Data Types Choosing and Using](https://reader033.vdocuments.us/reader033/viewer/2022051518/56816021550346895dcf23cd/html5/thumbnails/2.jpg)
G. Vern Rabe - [email protected] 2
Vern Rabe
• Independent consultant, trainer• MCT, MCDBA, MCSE, MCITP• SQL Server Pro magazine advisory board• Oregon PASS Steering Committee• Working with/focusing on SQL Server since
1993, starting with SQL Server 4.21a
2012-09-12
![Page 3: SQL Server Data Types Choosing and Using](https://reader033.vdocuments.us/reader033/viewer/2022051518/56816021550346895dcf23cd/html5/thumbnails/3.jpg)
G. Vern Rabe - [email protected] 3
Data TypesWhat We’ll Cover
• Why do we care?– Performance, accuracy
• Data type by Data type– When to use which
• Functions and Nuggets– What to use, what not to use, some workarounds
2012-09-12
![Page 5: SQL Server Data Types Choosing and Using](https://reader033.vdocuments.us/reader033/viewer/2022051518/56816021550346895dcf23cd/html5/thumbnails/5.jpg)
G. Vern Rabe - [email protected] 5
Implicit ConversionData Type Precedence
16. int17. smallint18. tinyint19. bit20. ntext21. text22. image23. rownumber24. uniqueidentifier25. nvarchar26. nchar27. varchar28. char29. varbinary30. binary (lowest)
2012-09-12
1. user-defined data types (highest)2. sql_variant3. xml4. datetimeoffset5. datetime26. datetime7. smalldatetime8. date9. time10. float11. real12. decimal13. money14. smallmoney15. bigint
![Page 7: SQL Server Data Types Choosing and Using](https://reader033.vdocuments.us/reader033/viewer/2022051518/56816021550346895dcf23cd/html5/thumbnails/7.jpg)
G. Vern Rabe - [email protected] 7
Data Type Categories
• Number• Character• Temporal• Binary• Other
2012-09-12
![Page 8: SQL Server Data Types Choosing and Using](https://reader033.vdocuments.us/reader033/viewer/2022051518/56816021550346895dcf23cd/html5/thumbnails/8.jpg)
G. Vern Rabe - [email protected] 8
Number Category• bit : 0 or 1• tinyint : 0 to 255• smallint : -32,768 to 32,767• int : -2,147,483,648 to 2,147,483,647• bigint : -9,223,372,036,854,775,808 to 9,223,372,036,854,775,808• decimal, numeric : maximum precision of 38• smallmoney : -214,748.3648 to 214,748.3647• money : -922,337,203,685,477.5808 to 922,337,203,685,477.5807• float : approx ± 2 * 10 ±308
• real : approx ± 2 * 10 ±38
2012-09-12
![Page 9: SQL Server Data Types Choosing and Using](https://reader033.vdocuments.us/reader033/viewer/2022051518/56816021550346895dcf23cd/html5/thumbnails/9.jpg)
G. Vern Rabe - [email protected] 9
Numeric Data Type Issues
• Float is imprecise/approximate
2012-09-12
![Page 10: SQL Server Data Types Choosing and Using](https://reader033.vdocuments.us/reader033/viewer/2022051518/56816021550346895dcf23cd/html5/thumbnails/10.jpg)
G. Vern Rabe - [email protected] 10
Numeric Data Type Issues
• Float is imprecise/approximate• Money has rounding surprises• Know your decimal limits (operator results)
2012-09-12
![Page 11: SQL Server Data Types Choosing and Using](https://reader033.vdocuments.us/reader033/viewer/2022051518/56816021550346895dcf23cd/html5/thumbnails/11.jpg)
G. Vern Rabe - [email protected] 11
Precision and Scale ofDecimal Operation Results
2012-09-12
Operation Result precision Result scale *
e1 + e2 max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2)
e1 - e2 max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2)
e1 * e2 p1 + p2 + 1 s1 + s2
e1 / e2 p1 - s1 + s2 + max(6, s1 + p2 + 1) max(6, s1 + p2 + 1)
e1 { UNION | EXCEPT | INTERSECT } e2
max(s1, s2) + max(p1-s1, p2-s2) max(s1, s2)
e1 % e2 min(p1-s1, p2 -s2) + max( s1,s2 ) max(s1, s2)
* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.
![Page 12: SQL Server Data Types Choosing and Using](https://reader033.vdocuments.us/reader033/viewer/2022051518/56816021550346895dcf23cd/html5/thumbnails/12.jpg)
G. Vern Rabe - [email protected] 12
Character Category
• char(N) : 8000 characters• nchar(N) : 4000 characters• varchar(N) : 8000 characters• varchar(MAX) ≈ 2,000,000,000 characters• nvarchar(N) : 4000 characters• nvarchar(MAX) ≈1,000,000,000 characters)• text : ≈ 2,000,000,000 characters• ntext : ≈ 1,000,000,000 characters2012-09-12
![Page 13: SQL Server Data Types Choosing and Using](https://reader033.vdocuments.us/reader033/viewer/2022051518/56816021550346895dcf23cd/html5/thumbnails/13.jpg)
G. Vern Rabe - [email protected] 13
Character Category
• char(N) : 8000 characters• nchar(N) : 4000 characters• varchar(N) : 8000 characters• varchar(MAX) ≈ 2,000,000,000 characters• nvarchar(N) : 4000 characters• nvarchar(MAX) ≈1,000,000,000 characters)• text : ≈ 2,000,000,000 characters• ntext : ≈ 1,000,000,000 characters2012-09-12
![Page 14: SQL Server Data Types Choosing and Using](https://reader033.vdocuments.us/reader033/viewer/2022051518/56816021550346895dcf23cd/html5/thumbnails/14.jpg)
G. Vern Rabe - [email protected] 14
Size Matters• (var)char(small) vs. (var)char(big) – difference? • (var)char vs. (var)char(n)
– (var)char(n) - When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30
• varchar(n) vs. char(n)• varchar(n) vs. varchar(MAX)
– Performance• Rob Garrison blog – http
://www.simple-talk.com/sql/database-administration/whats-the-point-of-using-varchar(n)-anymore/
– Domain integrity2012-09-12
![Page 15: SQL Server Data Types Choosing and Using](https://reader033.vdocuments.us/reader033/viewer/2022051518/56816021550346895dcf23cd/html5/thumbnails/15.jpg)
G. Vern Rabe - [email protected] 15
Temporal Category
• date : 0001-01-01 to 9999-12-31• time : 00:00:00.0000000 to 23:59:59.9999999• datetime2 : 0001-01-01 to 9999-12-31, 100ns precision• datetimeoffset : 0001-01-01 to 9999-12-31, 100ns
precision• smalldatetime : 1900-01-01 to 2079-06-06, precision
of 1 minute• datetime : 1753-01-01 to 9999-12-31, precision of 3⅓
millisecond (.000, .003, .007, .010 sec)
2012-09-12
![Page 16: SQL Server Data Types Choosing and Using](https://reader033.vdocuments.us/reader033/viewer/2022051518/56816021550346895dcf23cd/html5/thumbnails/16.jpg)
G. Vern Rabe - [email protected] 16
Temporal Category
• date : 0001-01-01 to 9999-12-31• time : 00:00:00.0000000 to 23:59:59.9999999• datetime2 : 0001-01-01 to 9999-12-31, 100ns precision• datetimeoffset : 0001-01-01 to 9999-12-31, 100ns
precision• smalldatetime : 1900-01-01 to 2079-06-06, precision
of 1 minute• datetime : 1753-01-01 to 9999-12-31, precision of 3⅓
millisecond (.000, .003, .007, .010 sec)
2012-09-12
![Page 17: SQL Server Data Types Choosing and Using](https://reader033.vdocuments.us/reader033/viewer/2022051518/56816021550346895dcf23cd/html5/thumbnails/17.jpg)
G. Vern Rabe - [email protected] 17
Temporal Examples
• Date literals– ‘1/2/11’?– ‘01-02-2011’?– ‘2011-01-02’?
• Using BETWEEN to identify day• Adding days
2012-09-12
![Page 18: SQL Server Data Types Choosing and Using](https://reader033.vdocuments.us/reader033/viewer/2022051518/56816021550346895dcf23cd/html5/thumbnails/18.jpg)
G. Vern Rabe - [email protected] 18
Binary Category
• binary(N) : 8000 bytes• varbinary(N) : 8000 bytes• varbinary(MAX) ≈ 2,000,000,000 bytes• image : ≈ 2,000,000,000 bytes
2012-09-12
![Page 19: SQL Server Data Types Choosing and Using](https://reader033.vdocuments.us/reader033/viewer/2022051518/56816021550346895dcf23cd/html5/thumbnails/19.jpg)
G. Vern Rabe - [email protected] 19
Binary Category
• binary(N) : 8000 bytes• varbinary(N) : 8000 bytes• varbinary(MAX) ≈ 2,000,000,000 bytes• image : ≈ 2,000,000,000 bytes
2012-09-12
![Page 20: SQL Server Data Types Choosing and Using](https://reader033.vdocuments.us/reader033/viewer/2022051518/56816021550346895dcf23cd/html5/thumbnails/20.jpg)
G. Vern Rabe - [email protected] 20
Other Category
• cursor• timestamp/rowversion• hierarchyid• uniqueidentifier• sql_variant• xml• table
2012-09-12
![Page 21: SQL Server Data Types Choosing and Using](https://reader033.vdocuments.us/reader033/viewer/2022051518/56816021550346895dcf23cd/html5/thumbnails/21.jpg)
G. Vern Rabe - [email protected] 21
Other Category
• cursor• timestamp/rowversion• hierarchyid• uniqueidentifier• sql_variant• xml• table
2012-09-12
![Page 22: SQL Server Data Types Choosing and Using](https://reader033.vdocuments.us/reader033/viewer/2022051518/56816021550346895dcf23cd/html5/thumbnails/22.jpg)
G. Vern Rabe - [email protected] 22
Functions
• ISNUMERIC• TRY_CONVERT• ISDATE
– Doesn’t cover entire date range– Doesn’t support datetime2
• CURRENT_TIMESTAMP vs. GETDATE() vs. SYSDATETIME() vs. GETUTCDATE() vs. SYSUTCDATETIME()
• ISNULL vs. COALESCE– ISNULL returns data type of first argument– COALESCE returns data type of highest precedence
2012-09-12
![Page 23: SQL Server Data Types Choosing and Using](https://reader033.vdocuments.us/reader033/viewer/2022051518/56816021550346895dcf23cd/html5/thumbnails/23.jpg)
G. Vern Rabe - [email protected] 23
Thanks
• Questions?
• Contact information– [email protected]– LinkedIn• www.linkedin.com/pub/vern-rabe/a/ba3/980
2012-09-12