Lecture 3: MS SQL Data Types

1. Introduction to Data Types

A data type is a vital attribute assigned to a column that dictates exactly what kind of data can be stored within it. Choosing the optimal data type is crucial because it directly impacts storage efficiency, query performance, and strict data integrity.

2. Numeric Data Types

• Exact Numeric Types: Guarantee precise storage without rounding errors.
  • BIT: Stores either 0 or 1. Takes exactly 1 bit of storage. Ideal for true/false or yes/no flags.
  • TINYINT: Stores integers from 0 to 255. Takes 1 byte.
  • SMALLINT: Stores integers from -32,768 to 32,767. Takes 2 bytes.
  • INT: Stores integers up to approximately ±2 billion. Takes 4 bytes. This is the industry standard for Primary Keys and standard IDs.
  • BIGINT: Takes 8 bytes. Used for exceptionally large datasets.
  • DECIMAL(p,s) / NUMERIC(p,s): Variable storage. Crucial for financial data where exact precision (p) and scale (s) are required (e.g., 99.99).
  • MONEY (8 bytes) / SMALLMONEY (4 bytes): Specifically formatted for currency values.
• Approximate Numeric Types:
  • FLOAT / REAL: Used for scientific calculations requiring floating-point numbers. They should never be used for exact monetary calculations due to inherent binary rounding inaccuracies.

3. Character (String) Data Types

String types are divided by their ability to support multiple languages (Unicode).

• Non-Unicode: Stores 1 byte per character. Best for standard English text.
  • CHAR(n): Fixed-length. If you specify CHAR(50) and store the 3-letter word "Ali", the database still consumes the full 50 bytes by padding the rest with spaces.
  • VARCHAR(n): Variable-length. Storing "Ali" in VARCHAR(50) only consumes 5 bytes (3 for the letters + 2 for background overhead).
  • VARCHAR(MAX): Can store up to 2GB of text data.
• Unicode: Stores 2 bytes per character. Mandatory for storing multi-language text, such as Arabic, Chinese, or Russian.
  • NCHAR(n): Fixed-length Unicode.
  • NVARCHAR(n): Variable-length Unicode. Storing "Ali" consumes 8 bytes. This is the gold standard for global applications.
  • NVARCHAR(MAX): Used for massive, multilingual text blocks.

4. Date/Time, Binary, and Special Types

• Date and Time: DATE (stores only the date), TIME (stores only the time), DATETIME (the older standard for combined date/time), DATETIME2 (the modern, highly precise standard), and DATETIMEOFFSET (includes specific time zone awareness).
• Binary: BINARY(n) (fixed), VARBINARY(n) (variable), VARBINARY(MAX) (used for storing physical files, images, or PDFs directly inside the database).
• Special: UNIQUEIDENTIFIER (stores universally unique GUIDs), SQL_VARIANT (can hold multiple varying data types), and XML.

5. Foreign Key Rules and Actions

When creating a Foreign Key (FK) to link a "child" table to a "parent" table, developers must define what happens when the parent record is deleted or updated:

• CASCADE: If the parent row is deleted or updated, all related child rows are automatically deleted or updated to match.
• SET NULL: If the parent row is deleted, the FK column in the child rows is set to NULL (requires the column to allow NULLs).
• SET DEFAULT: If the parent row is deleted, the child row reverts to a pre-defined default value.
• NO ACTION: The default database behavior. It aggressively prevents the deletion or update of the parent row as long as related child rows exist.