Data Types and Conversion

• Categories of Data Types

  • Exact numerics
  • Approximate numerics
  • Date and Time
  • Character Strings
  • Unicode Character Strings
  • Binary Strings
  • Other data types

• Exact Numerics

  • Whole Numbers
    • smallint
    • tinyint
    • int
    • bigint
  • Decimal Numbers
    • numeric
    • decimal
    • money
    • smallmoney

• Exact numerics - integers

  Numbers without a decimal point

  Data Type	Storage
  bigint	8 Bytes
  int	4 Bytes
  smallint	2 Bytes
  tinyint	1 Byte

• Exact numerics - decimals

  

• Approximate numerics

  • Float
  • Real
  • Store approximate numeric values

• Date and Time Data Types

  Data Type	Format	Accuracy
  time	hh:mm:ss[.nnnnnnn]	100 nanoseconds
  date	YYYY-MM-DD	1 day
  smalldatetime	YYYY-MM-DD hh:mm:ss	1 minute
  datetime	YYYY-MM-DD hh:mm:ss[.nnn]	0.00333 second
  datetime2	YYYY-MM-DD hh:mm:ss[.nnnnnnn]	100 nanoseconds

• Character and Unicode Character Data Types

  • Character data types store character strings (ASCII)
    • char
    • varchar
    • text
  • Unicode data types are used for storing Unicode data (non-ASCII)
    • nchar
    • nvarchar
    • ntext

• Other data types

  • binary
  • image
  • cursor
  • rowversino
  • uniqueidentifier
  • xml
  • Spatial Geometry / Geography Types

---

Implicit Conversion

• Data Comparison

  Keep in mind : for comparing two values, they need to be of the same type.
  Otherwise:

  • SQL Server converts from one type to another (IMPLICIT)
  • The developer explicitly converts the data (EXPLICIT)

• Data Type Precedence

  1. user-defined data types (highest)
  2. datetime
  3. date
  4. float
  5. decimal
  6. int
  7. bit
  8. nvarchar (including nvarchar(max))
  9. varchar (including varchar(max))
  10. binary (lowest)

• Implicit conversion between data types

  

• Performance impact of implicit conversion

  • Implicit conversion is done for each row of the query
  • Implicit conversion can be prevended with a good database schema design.

---

Explicit Conversion

• Implicit and Explicit Conversion

  • IMPLICIT : performed automatically, behind the scenes
  • EXPLICIT : performed with functions CAST() and CONVERT()
    • CAST() and CONVERT() are used to convert from one data type to another

• CAST()

  Syntax: CAST(expression AS data_type [(length)])

        SELECT
            CAST(3.14 AS INT) AS DECIMAL_TO_INT,
            CAST('3.14' AS DECIMAL(3,2)) AS STRING_TO_DECIMAL,
            CAST(GETDATE() AS NVARCHAR(20)) AS DATE_TO_STRING,
            CAST(GETDATE() AS FLOAT) AS DATE_TO_FLOAT;
  

• CONVERT()

  Syntax: CONVERT(data_type [(length)], expression, [,style])

        SELECT
            CONVERT(INT, 3.14) AS DECIMAL_TO_INT,
            CONVERT(DECIMAL(3,2), '3.14') AS STRING_TO_DECIMAL,
            CONVERT(NVARCHAR(20), GETDATE(), 104) AS DATE_TO_STRING,
            CONVERT(FLOAT, GETDATE()) AS DATE_TO_FLOAT;
  

• CAST() vs CONVERT()

  • CAST() comes from the SQL standard and CONVERT() is SQL Server specific
  • CAST() is available in most database products
  • CONVERT() performs slightly better in SQL Server