Sql Server Data Types In Sql Server Complete Guide

SQL Server Data Types in SQL Server

SQL Server supports various data types to handle different kinds of data such as integers, floating-point numbers, character strings, dates, and binary data. Here, we will explore these data types, starting from the basics, and learn how to use them in SQL Server.

1. Numeric Data Types

Numeric data types are used to store numeric values.

Example:

Let's create a table NumericTypesDemo that contains numeric fields.

CREATE TABLE NumericTypesDemo (
    TinyIntCol TINYINT,
    SmallIntCol SMALLINT,
    IntCol INT,
    BigIntCol BIGINT,
    DecimalCol DECIMAL(10, 2),
    NumericCol NUMERIC(10, 2),
    FloatCol FLOAT,
    RealCol REAL,
    MoneyCol MONEY,
    SmallMoneyCol SMALLMONEY
);

Explanation:

• TINYINT: An 8-bit unsigned integer from 0 to 255.
• SMALLINT: A 16-bit signed integer from -32,768 to 32,767.
• INT: A 32-bit signed integer from -2,147,483,648 to 2,147,483,647.
• BIGINT: A 64-bit signed integer from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
• DECIMAL and NUMERIC: Fixed precision and scale numbers. Here, DECIMAL(10, 2) allows up to 10 digits, with 2 digits after the decimal point.
• FLOAT: Floating precision number.
• REAL: Double precision floating point number.
• MONEY: Currency values.
• SMALLMONEY: Small money.

2. Date and Time Data Types

Example:

Create a table DateTimeTypesDemo to store date and time.

CREATE TABLE DateTimeTypesDemo (
    DateTimeCol DATETIME,
    SmDateTimeCol SMALLDATETIME,
    DateCol DATE,
    TimeCol TIME,
    DateTime2Col DATETIME2,
    DateTimeOffsetCol DATETIMEOFFSET
);

Explanation:

• DATETIME: Date and time from January 1, 1753 to December 31, 9999 with no fractional seconds.
• SMALLDATETIME: Date and time from January 1, 1900 to June 6, 2079 with no fractional seconds.
• DATE: Date only from January 1, 0001 to December 31, 9999.
• TIME: Time only with up to 7 digits of precision.
• DATETIME2: Date and time from January 1, 0001 to December 31, 9999 with up to 7 digits of precision.
• DATETIMEOFFSET: Date and time with time zone information.

3. Character String Data Types

These data types are used to store variable and fixed-length character strings.

Example:

Create a table StringTypesDemo that stores character strings.

CREATE TABLE StringTypesDemo (
    CharCol CHAR(20),
    VarCharCol VARCHAR(100),
    TextCol TEXT,
    NCharCol NCHAR(20),
    NVarCharCol NVARCHAR(100),
    NTextCol NTEXT
);

Explanation:

• CHAR(n): Fixed-length non-Unicode string data of length n.
• VARCHAR(n): Variable-length non-Unicode string data with a maximum length of n.
• TEXT: Variable-length non-Unicode data with a maximum length of 2^31-1 bytes (2 GB). Deprecated in favor of VARCHAR(MAX).
• NCHAR(n): Fixed-length Unicode string data of length n.
• NVARCHAR(n): Variable-length Unicode string data with a maximum length of n.
• NTEXT: Variable-length Unicode data with a maximum length of 2^31-1 bytes (2 GB). Deprecated in favor of NVARCHAR(MAX).

4. Binary Data Types

Binary data types are used for storing binary data.

Example:

Create a table BinaryTypesDemo for binary data.

CREATE TABLE BinaryTypesDemo (
    BinaryCol BINARY(50),
    VarBinaryCol VARBINARY(100),
    ImageCol IMAGE
);

Explanation:

• BINARY(n): Fixed-length binary data with a length of n bytes.
• VARBINARY(n): Variable-length binary data with a maximum length of n.
• IMAGE: Variable-length binary data with a maximum length of 2^31-1 bytes (2 GB). Deprecated in favor of VARBINARY(MAX).

5. XML Data Type

The XML data type allows you to store XML data in the database.

Example:

Create a table XmlTypesDemo for XML data.

CREATE TABLE XmlTypesDemo (
    XmlCol XML
);

Explanation:

• XML: Stores XML data in either character or binary form.

6. Spatial Data Types

Spatial data types store geometric and geographic spatial data.

Example:

Create a table SpatialTypesDemo for spatial data.

CREATE TABLE SpatialTypesDemo (
    GeographyCol GEOGRAPHY,
    GeometryCol GEOMETRY
);

Explanation:

• GEOGRAPHY: Stores full-featured spatial data referencing the Earth.
• GEOMETRY: Stores planar (flat Earth) spatial data.

Using Data Types in Tables

Let's create a practical example using these data types.

Example:

Create a table EmployeeDetails to store detailed information about employees.

CREATE TABLE EmployeeDetails (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    BirthDate DATE,
    Gender CHAR(1),
    Salary MONEY,
    HireDate DATETIME,
    Address NVARCHAR(255),
    IsFullTime BIT,
    Photo VARBINARY(MAX),
    Resume XML
);

Explanation:

• EmployeeID: Unique identifier for each employee, auto-incremented.
• FirstName: Employee's first name using VARCHAR.
• LastName: Employee's last name using VARCHAR.
• BirthDate: Employee's date of birth using DATE.
• Gender: CHAR(1) to store 'M', 'F', or another single character.
• Salary: Employee's salary using MONEY.
• HireDate: Date and time when the employee was hired using DATETIME.
• Address: Employee's address using NVARCHAR.
• IsFullTime: Boolean to determine if the employee is full-time using BIT.
• Photo: Employee's photo stored as binary data using VARBINARY(MAX).
• Resume: Employee's resume in XML format using XML.

Querying Tables

Let's insert data into the EmployeeDetails table and query it.

Example:

Insert data into the EmployeeDetails table.

INSERT INTO EmployeeDetails (FirstName, LastName, BirthDate, Gender, Salary, HireDate, Address, IsFullTime, Photo, Resume)
VALUES 
    ('John', 'Doe', '1985-05-15', 'M', 75000.00, '2010-07-25 08:30:00', '123 Elm Street', 1, NULL,
        CAST('<?xml version="1.0"?><Resume><JobTitle>Software Engineer</JobTitle><Experience>5 years</Experience></Resume>' AS XML)),
    ('Jane', 'Smith', '1990-08-10', 'F', 85000.00, '2013-06-15 08:00:00', '456 Oak Avenue', 1, NULL,
        CAST('<?xml version="1.0"?><Resume><JobTitle>Project Manager</JobTitle><Experience>6 years</Experience></Resume>' AS XML));

Explanation:

• Inserting data with various data types, including XML and NULL for Photo.

Example:

Query the EmployeeDetails table.

SELECT 
    EmployeeID,
    FirstName,
    LastName,
    BirthDate,
    Gender,
    Salary,
    HireDate,
    Address,
    IsFullTime,
    Resume.value('(/Resume/JobTitle/text())[1]', 'VARCHAR(50)') AS JobTitle
FROM 
    EmployeeDetails;

Explanation:

• Querying all columns and extracting the JobTitle from the Resume XML column.

Summary