Master SQL Server From Scratch

Clear, interactive, and structured SQL Server lessons designed for beginners and developers.

COURSE SYLLABUS
1. Introduction to SQL Server
Introduction to SQL Server SQL Server Installation SQL Server Fundamentals
2. Data Types
Data Types Numeric Data Types Character & String Data Types Date & Time Data Types Other SQL Server Data Types
3. Database & Tables
Database & Tables Introduction Creating a Database Creating Tables Table Structure Altering Tables Dropping Tables
4. Constraints
Constraints Introduction PRIMARY KEY FOREIGN KEY UNIQUE Constraint NOT NULL & DEFAULT CHECK Constraint
5. SQL Basics
SQL Basics Introduction SQL Commands SQL Command Categories SELECT Statement INSERT, UPDATE & DELETE
6. CRUD Operations
CRUD Operations Introduction INSERT Operation SELECT Operation UPDATE Operation DELETE Operation
7. SQL Queries
Queries Introduction WHERE Clause ORDER BY Clause DISTINCT LIKE Operator IN, BETWEEN & IS NULL
8. Joins
Joins Introduction INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN CROSS JOIN
9. SQL Functions
SQL Functions Introduction String Functions Numeric Functions Date & Time Functions Conversion Functions NULL Functions
10. Grouping & Aggregate Functions
GROUP BY Aggregate Functions COUNT() SUM() AVG() MIN() & MAX()
11. Subqueries
Subqueries Introduction Single-Row Subquery Multiple-Row Subquery Correlated Subquery Subquery with DML
12. Views
SQL Views Introduction Creating a View Updating a View Altering a View Dropping a View
13. Stored Procedures
Stored Procedures Introduction Creating a Stored Procedure Input Parameters Output Parameters Executing & Modifying Stored Procedures
14. Triggers
Triggers Introduction AFTER INSERT Trigger AFTER UPDATE Trigger AFTER DELETE Trigger INSTEAD OF Trigger
15. Transactions
Transactions Introduction COMMIT ROLLBACK SAVEPOINT Transaction with TRY...CATCH
16. Indexes
Indexes Introduction Clustered Index Non-Clustered Index Creating & Dropping Indexes Index Performance
17. Common Table Expressions
CTE Introduction Simple CTE CTE with JOIN CTE with Aggregate Functions Recursive CTE
18. Window Functions
Window Functions Introduction ROW_NUMBER() RANK() and DENSE_RANK() LEAD() and LAG() PARTITION BY
19. Advanced SQL
Advanced SQL Introduction Temporary Tables Table Variables Dynamic SQL Error Handling Performance Optimization

Character & String Data Types

SQL Server provides character and string data types to store text, names, addresses, descriptions, codes and other character-based information.

What are Character & String Data Types?

Character and string data types are used to store text values in SQL Server. These values can contain letters, numbers, spaces and special characters.

SQL Server provides different string data types depending on the maximum length of the text and whether Unicode characters are required.

Common Character & String Data Types

Data Type Description Example
CHAR(n) Stores fixed-length non-Unicode character data. CHAR(10)
VARCHAR(n) Stores variable-length non-Unicode character data. VARCHAR(100)
VARCHAR(MAX) Stores variable-length character data with very large size. VARCHAR(MAX)
NCHAR(n) Stores fixed-length Unicode character data. NCHAR(10)
NVARCHAR(n) Stores variable-length Unicode character data. NVARCHAR(100)
NVARCHAR(MAX) Stores large variable-length Unicode text. NVARCHAR(MAX)

CHAR

The CHAR data type is used to store fixed-length character data.

If the defined length is 10 characters, SQL Server allocates space for 10 characters even when the stored value is shorter.

SQL
CREATE TABLE Employees
(
    EmployeeCode CHAR(10),
    EmployeeName VARCHAR(100)
);

VARCHAR

The VARCHAR data type stores variable-length non-Unicode character data.

It is commonly used for names, email addresses, cities, addresses and other normal text values.

SQL
CREATE TABLE Students
(
    StudentId INT,
    StudentName VARCHAR(100),
    City VARCHAR(50),
    Email VARCHAR(150)
);

NCHAR and NVARCHAR

NCHAR and NVARCHAR are used to store Unicode character data.

They are useful when applications need to store text from multiple languages and character sets.

SQL
CREATE TABLE Customers
(
    CustomerId INT,
    CustomerName NVARCHAR(100),
    Address NVARCHAR(250)
);

CHAR vs VARCHAR

CHAR VARCHAR
Fixed-length data Variable-length data
Suitable when values have a fixed size Suitable when values have different lengths
Example: Country Code Example: Student Name

Example: Using String Data Types

The following example creates a Students table using different character and string data types.

SQL
CREATE TABLE Students
(
    StudentId INT,
    StudentName VARCHAR(100),
    Gender CHAR(1),
    City VARCHAR(50),
    Address NVARCHAR(250)
);

Here, different string data types are selected according to the type of text and storage requirement.

Insert String Values

SQL
INSERT INTO Students
(
    StudentId,
    StudentName,
    Gender,
    City,
    Address
)
VALUES
(
    1,
    'Rahul',
    'M',
    'Pune',
    N'Pune, Maharashtra'
);

Output

SQL Output
StudentId    StudentName    Gender    City    Address
-------------------------------------------------------
1            Rahul          M         Pune    Pune, Maharashtra

The output shows how SQL Server stores character and string values in the respective columns.

Choosing the Correct String Data Type

Select the string data type according to the length, structure and character requirements of the data.

  • Use CHAR for fixed-length values.
  • Use VARCHAR for variable-length non-Unicode text.
  • Use NCHAR for fixed-length Unicode text.
  • Use NVARCHAR for variable-length Unicode text.
  • Use VARCHAR(MAX) or NVARCHAR(MAX) when very large text needs to be stored.
Summary

In this section, we learned about character and string data types in SQL Server, including CHAR, VARCHAR, VARCHAR(MAX), NCHAR, NVARCHAR and NVARCHAR(MAX). We also learned the difference between fixed-length, variable-length and Unicode string data types.