Microsoft SQL Server

Learn Microsoft SQL Server - enterprise-grade database solution

Introduction to Microsoft SQL Server

Microsoft SQL Server is a relational database management system developed by Microsoft. It is designed for enterprise environments and offers advanced features for data management, business intelligence, and analytics.

Key Features of SQL Server:

Enterprise-grade security and compliance
Advanced analytics and machine learning integration
High availability and disaster recovery options
Business intelligence and reporting tools
Columnstore indexes for data warehousing

SQL Server Installation and Setup

Installation Notes

-- SQL Server Installation
-- 1. Download SQL Server from Microsoft website
-- 2. Run the installation wizard
-- 3. Choose edition (Express, Standard, Enterprise)
-- 4. Configure security settings
-- 5. Complete installation

-- Connect using SQL Server Management Studio (SSMS)
-- Or connect using command line tools like sqlcmd

Connecting to SQL Server

-- Using sqlcmd
sqlcmd -S server_name -U username -P password

-- Connection string example
Server=server_name;Database=database_name;User Id=username;Password=password;

SQL Server Specific Commands

Database Operations

-- Create Database
CREATE DATABASE database_name;

-- Use Database
USE database_name;

-- Show Databases
SELECT name FROM sys.databases;

-- Drop Database
DROP DATABASE database_name;

Table Operations

-- Create Table
CREATE TABLE table_name (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(255) NOT NULL,
    email NVARCHAR(255) UNIQUE,
    created_at DATETIME2 DEFAULT GETDATE()
);

-- Show Tables
SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE';

-- Describe Table
EXEC sp_columns table_name;

-- Drop Table
DROP TABLE table_name;

SQL Server Data Types

-- Numeric Types
INT, TINYINT, SMALLINT, BIGINT
DECIMAL(p,s), NUMERIC(p,s), FLOAT, REAL, MONEY

-- Date and Time Types
DATE, TIME, DATETIME2, DATETIME, SMALLDATETIME, DATETIMEOFFSET

-- String Types
CHAR(n), VARCHAR(n), TEXT
NCHAR(n), NVARCHAR(n), NTEXT (Unicode support)

-- Binary Types
BINARY(n), VARBINARY(n), IMAGE

SQL Server Functions and Features

String Functions

-- Common String Functions
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
SELECT UPPER(name) FROM products;
SELECT LOWER(name) FROM products;
SELECT LEN(name) FROM products; -- Note: LEN instead of LENGTH
SELECT SUBSTRING(name, 1, 5) FROM products;
SELECT REPLACE(name, 'old', 'new') FROM products;
SELECT REVERSE(name) FROM products;

Date and Time Functions

-- Common Date Functions
SELECT GETDATE(); -- Current date and time
SELECT GETUTCDATE(); -- Current UTC date and time
SELECT SYSDATETIME(); -- Current date and time with more precision
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss'); -- Formatted date
SELECT DATEDIFF(day, '2023-01-01', '2023-12-31'); -- Difference in days
SELECT DATEADD(month, 1, GETDATE()); -- Add 1 month

SQL Server Specific Functions

-- SQL Server Specific Functions
SELECT NEWID(); -- Generate unique identifier (GUID)
SELECT CASE WHEN condition THEN result1 ELSE result2 END;
SELECT ISNULL(value, replacement); -- Check for NULL values
SELECT COALESCE(value1, value2, value3); -- Returns first non-null value
SELECT IIF(condition, value_if_true, value_if_false); -- SQL Server 2012+
SELECT FORMAT(value, 'format_string'); -- Format values

SQL Server Indexes and Performance

Creating Indexes

-- Create Index
CREATE INDEX IX_lastname ON users(last_name);

-- Create Unique Index
CREATE UNIQUE INDEX IX_email ON users(email);

-- Create Clustered Index (only one per table)
CREATE CLUSTERED INDEX IX_user_id ON users(user_id);

-- Create Non-Clustered Index
CREATE NONCLUSTERED INDEX IX_name_age ON users(first_name, last_name, age);

-- Drop Index
DROP INDEX users.IX_lastname;

SQL Server Performance Optimization

-- Analyze Query Performance
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT * FROM users WHERE email = 'john@example.com';

-- Show Execution Plan
SET SHOWPLAN_ALL ON;
SELECT * FROM users WHERE email = 'john@example.com';

-- View Indexes
SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('users');

-- Update Statistics
UPDATE STATISTICS table_name;

Performance Tips:

Use columnstore indexes for data warehousing scenarios
Implement proper partitioning for large tables
Use query hints judiciously for performance tuning
Monitor query execution plans regularly
Implement proper indexing strategy

SQL Server Stored Procedures and Functions

Creating Stored Procedures

-- Create a stored procedure
CREATE PROCEDURE GetUsersByCity
    @city_name NVARCHAR(255)
AS
BEGIN
    SELECT * FROM users WHERE city = @city_name;
END;

-- Execute the procedure
EXEC GetUsersByCity 'New York';
-- Or
EXECUTE GetUsersByCity @city_name = 'New York';

Creating Functions

-- Create a scalar function
CREATE FUNCTION CalculateAge(@birth_date DATE)
RETURNS INT
AS
BEGIN
    DECLARE @age INT;
    SET @age = DATEDIFF(YEAR, @birth_date, GETDATE());
    RETURN @age;
END;

-- Use the function
SELECT name, dbo.CalculateAge(birth_date) AS age FROM users;

Common Table Expressions (CTEs)

-- Simple CTE
WITH EmployeeCTE AS (
    SELECT EmployeeID, FirstName, LastName, DepartmentID
    FROM Employees
    WHERE DepartmentID = 1
)
SELECT * FROM EmployeeCTE;

-- Recursive CTE
WITH Numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1
    FROM Numbers
    WHERE n < 10
)
SELECT * FROM Numbers;