1 of 10

SQL Datatypes

Day 8

Shrikant Khode

​

2 of 10

Introduction to SQL Datatypes

  • A data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, date and time data, binary strings, and so on. 

3 of 10

Categories of SQL Datatypes

  • Numeric
  • String (Character)
  • Date and Time
  • Binary
  • Miscellaneous

4 of 10

Exact Numeric Datatypes

  • 1. INT
    • Description: Stores integer data.
    • Storage Size: 4 bytes.
    • Range: -2,147,483,648 to 2,147,483,647.
  • 2. SMALLINT
    • Description: Stores small integer data.
    • Storage Size: 2 bytes.
    • Range: -32,768 to 32,767.
  • 3. TINYINT
    • Description: Stores very small integer data.
    • Storage Size: 1 byte.
    • Range: 0 to 255.
  • 4. BIGINT
    • Description: Stores large integer data.
    • Storage Size: 8 bytes.
    • Range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
  • 5. DECIMAL / NUMERIC
    • Description: Stores fixed precision and scale numbers.
    • Storage Size: Varies based on precision.
    • Syntax: DECIMAL(p, s) or NUMERIC(p, s)
      • p (precision): Total number of digits.
      • s (scale): Number of digits to the right of the decimal point.
    • Example: DECIMAL(10, 2) stores numbers up to 10 digits, with 2 digits after the decimal.
  • 6. MONEY
    • Description: Stores monetary values.
    • Storage Size: 8 bytes.
    • Range: -922,337,203,685,477.5808 to 922,337,203,685,477.5807.
  • 7. SMALLMONEY
    • Description: Stores small monetary values.
    • Storage Size: 4 bytes.
    • Range: -214,748.3648 to 214,748.3647.

​

5 of 10

Approximate Numeric Data Types�

  • 1. FLOAT
      • Description: Stores approximate numeric data with floating decimal points.
      • Storage Size: Varies (4 bytes for FLOAT(24), 8 bytes for FLOAT(53)).
      • Syntax: FLOAT(n)
        • n: Number of bits used to store the mantissa.
      • Range: -1.79E+308 to 1.79E+308.
  • 2. REAL
      • Description: Stores approximate numeric data with floating decimal points.
      • Storage Size: 4 bytes.
      • Range: -3.40E+38 to 3.40E+38.

​

6 of 10

String (Character) Datatypes

  • CHAR
    • Description: Fixed-length, non-Unicode character data.
    • Storage Size: 1 byte per character.
    • Range: 1 to 8,000 characters.
  • VARCHAR
    • Description: Variable-length, non-Unicode character data.
    • Storage Size: 1 byte per character + 2 bytes.
    • Range: 1 to 8,000 characters.
  • TEXT
    • Description: Variable-length, non-Unicode character data with a maximum length of 2^31-1 (2,147,483,647) characters.
    • Note: Deprecated in favor of VARCHAR(MAX).
    • NCHAR
    • Description: Fixed-length, Unicode character data.
    • Storage Size: 2 bytes per character.
  • Range: 1 to 4,000 characters.
  • NVARCHAR
    • Description: Variable-length, Unicode character data.
    • Storage Size: 2 bytes per character + 2 bytes.
    • Range: 1 to 4,000 characters (up to 2^31-1 characters for NVARCHAR(MAX)).
  • NTEXT
    • Description: Variable-length, Unicode character data with a maximum length of 2^30-1 (1,073,741,823) characters.
    • Note: Deprecated in favor of NVARCHAR(MAX).

​

7 of 10

Date and Time Datatypes

  • DATE
    • Description: Stores date values only.
    • Storage Size: 3 bytes.
    • Range: January 1, 0001, to December 31, 9999.
    • Format: YYYY-MM-DD
  • TIME
    • Description: Stores time values only.
    • Storage Size: 3 to 5 bytes, depending on precision.
    • Range: 00:00:00.0000000 to 23:59:59.9999999.
    • Format: hh:mm.nnnnnnn
  • DATETIME
    • Description: Stores date and time values.
    • Storage Size: 8 bytes.
    • Range: January 1, 1753, to December 31, 9999.
    • Format: YYYY-MM-DD hh:mm.nnn
  • DATETIME2
    • Description: Stores date and time values with higher precision.
    • Storage Size: 6 to 8 bytes, depending on precision.
    • Range: January 1, 0001, to December 31, 9999.
    • Format: YYYY-MM-DD hh:mm.nnnnnnn
  • SMALLDATETIME
    • Description: Stores date and time values.
    • Storage Size: 4 bytes.
    • Range: January 1, 1900, to June 6, 2079.
    • Format: YYYY-MM-DD hh:mm
  • DATETIMEOFFSET
    • Description: Stores date and time values with time zone awareness.
    • Storage Size: 10 bytes.
    • Range: January 1, 0001, to December 31, 9999.
    • Format: YYYY-MM-DD hh:mm.nnnnnnn
    • [{+|-}hh]
  • TIMESTAMP / ROWVERSION
    • Description: Stores a unique binary number within a database.
    • Storage Size: 8 bytes.
    • Range: Unique across the database.
    • Format: Automatically generated.

​

8 of 10

Binary Datatypes

  • BINARY
      • Description: Fixed-length binary data.
      • Storage Size: 1 to 8,000 bytes.
      • Range: Defined length in bytes.
  • VARBINARY
      • Description: Variable-length binary data.
      • Storage Size: 1 to 8,000 bytes.
      • Range: Variable length up to 8,000 bytes (or MAX for large data).
  • IMAGE
      • Description: Variable-length binary data with a maximum length of 2^31-1 (2,147,483,647) bytes.
      • Note: Deprecated in favor of VARBINARY(MAX).

9 of 10

Choosing the Right Datatype

  • Best Practices
  • - Choose the most appropriate datatype for the data
  • - Consider the size and performance

​

  • Common Pitfalls
  • - Using a larger datatype than necessary
  • - Not accounting for data precision

10 of 10

Thank You.