SQL Server Data Types Choosing and Using

1. SQL Server Data TypesChoosing and Using G. Vern Rabe - vern@rabe.net

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 G. Vern Rabe - vern@rabe.net

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 G. Vern Rabe - vern@rabe.net

4. Why Important? • Performance G. Vern Rabe - vern@rabe.net

5. Implicit ConversionData Type Precedence • int • smallint • tinyint • bit • ntext • text • image • rownumber • uniqueidentifier • nvarchar • nchar • varchar • char • varbinary • binary (lowest) • user-defined data types (highest) • sql_variant • xml • datetimeoffset • datetime2 • datetime • smalldatetime • date • time • float • real • decimal • money • smallmoney • bigint G. Vern Rabe - vern@rabe.net

6. Why Important? • Performance • Accuracy G. Vern Rabe - vern@rabe.net

7. Data Type Categories • Number • Character • Temporal • Binary • Other G. Vern Rabe - vern@rabe.net

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 G. Vern Rabe - vern@rabe.net

9. Numeric Data Type Issues • Float is imprecise/approximate G. Vern Rabe - vern@rabe.net

10. Numeric Data Type Issues • Float is imprecise/approximate • Money has rounding surprises • Know your decimal limits (operator results) G. Vern Rabe - vern@rabe.net

11. Precision and Scale ofDecimal Operation Results * 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. G. Vern Rabe - vern@rabe.net

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 characters G. Vern Rabe - vern@rabe.net

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 characters G. Vern Rabe - vern@rabe.net

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 integrity G. Vern Rabe - vern@rabe.net

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) G. Vern Rabe - vern@rabe.net

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) G. Vern Rabe - vern@rabe.net

17. Temporal Examples • Date literals • ‘1/2/11’? • ‘01-02-2011’? • ‘2011-01-02’? • Using BETWEEN to identify day • Adding days G. Vern Rabe - vern@rabe.net

18. Binary Category • binary(N) : 8000 bytes • varbinary(N) : 8000 bytes • varbinary(MAX) ≈ 2,000,000,000 bytes • image : ≈ 2,000,000,000 bytes G. Vern Rabe - vern@rabe.net

19. Binary Category • binary(N) : 8000 bytes • varbinary(N) : 8000 bytes • varbinary(MAX) ≈ 2,000,000,000 bytes • image : ≈ 2,000,000,000 bytes G. Vern Rabe - vern@rabe.net

20. Other Category • cursor • timestamp/rowversion • hierarchyid • uniqueidentifier • sql_variant • xml • table G. Vern Rabe - vern@rabe.net

21. Other Category • cursor • timestamp/rowversion • hierarchyid • uniqueidentifier • sql_variant • xml • table G. Vern Rabe - vern@rabe.net

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 G. Vern Rabe - vern@rabe.net

23. Thanks • Questions? • Contact information • vern@rabe.net • LinkedIn • www.linkedin.com/pub/vern-rabe/a/ba3/980 G. Vern Rabe - vern@rabe.net