SQL Server is a relational database management system (RDBMS) developed by Microsoft. Its main components include:

• Database Engine: Core service for storing, processing, and securing data
• SQL Server Agent: Handles job scheduling and automation
• SQL Server Reporting Services (SSRS): For creating and managing reports
• SQL Server Integration Services (SSIS): For data integration and transformation
• SQL Server Analysis Services (SSAS): For data mining and analytics
• SQL Server Management Studio (SSMS): Primary management and development tool

SQL Server data types are categorized into:

Numeric Types: INT, BIGINT, SMALLINT, TINYINT, DECIMAL, NUMERIC, FLOAT, REAL, MONEY
String Types: VARCHAR, NVARCHAR, CHAR, NCHAR, TEXT, NTEXT
Date/Time Types: DATE, TIME, DATETIME, DATETIME2, SMALLDATETIME, DATETIMEOFFSET
Binary Types: BINARY, VARBINARY, IMAGE
Other Types: BIT, UNIQUEIDENTIFIER, XML, JSON (SQL Server 2016+)

Example:

CREATE TABLE Employee (
    ID INT,
    Name NVARCHAR(50),
    Salary DECIMAL(10,2),
    HireDate DATETIME
);

• VARCHAR: Stores non-Unicode character data, uses 1 byte per character
• NVARCHAR: Stores Unicode character data, uses 2 bytes per character

VARCHAR is suitable for English text, while NVARCHAR supports international characters and multiple languages. NVARCHAR requires more storage but provides better internationalization support.

• INNER JOIN: Returns records matching in both tables
• LEFT JOIN: Returns all records from left table and matching from right
• RIGHT JOIN: Returns all records from right table and matching from left
• FULL OUTER JOIN: Returns all records from both tables
• CROSS JOIN: Returns Cartesian product of both tables

Example:

-- INNER JOIN
SELECT e.Name, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DeptID = d.ID;

PRIMARY KEY: Uniquely identifies each record in a table. Cannot be NULL and must be unique.
FOREIGN KEY: Links two tables together, referencing the primary key of another table.

Example:

CREATE TABLE Departments (
    ID INT PRIMARY KEY,
    Name NVARCHAR(50)
);

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name NVARCHAR(50),
    DeptID INT FOREIGN KEY REFERENCES Departments(ID)
);