phila
PRESENTED BY
23 SLIDES
761 VUES
380 LIKES

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.

1 / 23

Télécharger la présentation

SQL Server Data Types Choosing and Using

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related