Microsoft SQL Server - Getting Started

What is Microsoft SQL Server?

Microsoft SQL Server (MSSQL) is a relational database management system (RDBMS) developed by Microsoft. It's widely used in enterprise environments for storing and managing data.

Key Features

T-SQL: Transact-SQL, Microsoft's extension to SQL
Performance: Optimized query execution and indexing
Security: Row-level security, encryption, authentication
Integration: Works seamlessly with .NET and Microsoft tools
Scalability: Handles databases from small to petabyte scale

Installation

SQL Server Express (Free)

# Download from Microsoft website
# Express Edition limitations:
# - 10GB max database size
# - 1GB max memory
# - 4 cores max CPU

SQL Server Management Studio (SSMS)

SSMS is the primary GUI tool for working with SQL Server.

Download: https://aka.ms/ssmsfullsetup
Features:
- Query editor with IntelliSense
- Visual database designer
- Execution plan analyzer
- Built-in debugging

Basic Connection

Using SSMS

Server name: localhost\SQLEXPRESS
Authentication: Windows Authentication (or SQL Server Auth)

Connection String (C#)

string connectionString =
    "Server=localhost\SQLEXPRESS;" +
    "Database=MyDatabase;" +
    "Trusted_Connection=True;";

Connection String (Python)

import pyodbc

conn_str = (
    "Driver={ODBC Driver 17 for SQL Server};"
    "Server=localhost\SQLEXPRESS;"
    "Database=MyDatabase;"
    "Trusted_Connection=yes;"
)

conn = pyodbc.connect(conn_str)

Creating Your First Database

-- Create database
CREATE DATABASE MyFirstDB;
GO

-- Use the database
USE MyFirstDB;
GO

-- Create a table
CREATE TABLE Users (
    UserID INT PRIMARY KEY IDENTITY(1,1),
    Username NVARCHAR(50) NOT NULL,
    Email NVARCHAR(100) NOT NULL,
    CreatedDate DATETIME DEFAULT GETDATE(),
    IsActive BIT DEFAULT 1
);
GO

Key T-SQL Syntax

GO: Batch separator (not standard SQL)
IDENTITY(1,1): Auto-increment starting at 1, increment by 1
NVARCHAR: Unicode string (N prefix)
BIT: Boolean (0 or 1)
GETDATE(): Current datetime function

Basic CRUD Operations

Create (INSERT)

-- Insert single row
INSERT INTO Users (Username, Email)
VALUES ('john_doe', 'john@example.com');

-- Insert multiple rows
INSERT INTO Users (Username, Email)
VALUES
    ('jane_smith', 'jane@example.com'),
    ('bob_jones', 'bob@example.com'),
    ('alice_wilson', 'alice@example.com');

-- Insert with OUTPUT clause (T-SQL specific)
INSERT INTO Users (Username, Email)
OUTPUT INSERTED.UserID, INSERTED.Username
VALUES ('new_user', 'new@example.com');

Read (SELECT)

-- Select all
SELECT * FROM Users;

-- Select specific columns
SELECT UserID, Username, Email
FROM Users;

-- With WHERE clause
SELECT *
FROM Users
WHERE IsActive = 1;

-- With ORDER BY
SELECT *
FROM Users
ORDER BY CreatedDate DESC;

-- With TOP (limit results)
SELECT TOP 10 *
FROM Users
ORDER BY CreatedDate DESC;

Update (UPDATE)

-- Update single record
UPDATE Users
SET Email = 'newemail@example.com'
WHERE UserID = 1;

-- Update multiple columns
UPDATE Users
SET
    Email = 'updated@example.com',
    IsActive = 0
WHERE Username = 'john_doe';

-- Update with OUTPUT
UPDATE Users
SET IsActive = 0
OUTPUT DELETED.Username, INSERTED.IsActive
WHERE UserID = 1;

Delete (DELETE)

-- Delete specific record
DELETE FROM Users
WHERE UserID = 1;

-- Delete with condition
DELETE FROM Users
WHERE IsActive = 0
  AND CreatedDate < DATEADD(YEAR, -1, GETDATE());

-- Delete all (use with caution!)
DELETE FROM Users;

-- Truncate (faster, resets identity)
TRUNCATE TABLE Users;

Data Types

Common Types

-- Numeric
INT                 -- -2,147,483,648 to 2,147,483,647
BIGINT              -- Larger integers
DECIMAL(10,2)       -- Fixed precision (10 digits, 2 decimal)
FLOAT               -- Floating point

-- String
VARCHAR(50)         -- Variable-length ASCII string
NVARCHAR(50)        -- Variable-length Unicode string
CHAR(10)            -- Fixed-length ASCII string
TEXT                -- Large text (deprecated, use VARCHAR(MAX))

-- Date/Time
DATE                -- Date only
TIME                -- Time only
DATETIME            -- Date and time
DATETIME2           -- More precise datetime

-- Other
BIT                 -- Boolean (0/1)
UNIQUEIDENTIFIER    -- GUID
BINARY              -- Binary data

Constraints

CREATE TABLE Products (
    ProductID INT PRIMARY KEY IDENTITY(1,1),
    ProductName NVARCHAR(100) NOT NULL,
    SKU NVARCHAR(50) UNIQUE NOT NULL,
    Price DECIMAL(10,2) CHECK (Price >= 0),
    CategoryID INT FOREIGN KEY REFERENCES Categories(CategoryID),
    Stock INT DEFAULT 0,
    CreatedDate DATETIME DEFAULT GETDATE()
);

Constraint Types

PRIMARY KEY: Unique identifier
FOREIGN KEY: References another table
UNIQUE: No duplicate values
NOT NULL: Must have a value
CHECK: Value must meet condition
DEFAULT: Default value if not specified

Indexes

-- Create index
CREATE INDEX IX_Users_Username
ON Users(Username);

-- Create unique index
CREATE UNIQUE INDEX IX_Users_Email
ON Users(Email);

-- Composite index
CREATE INDEX IX_Users_Name_Date
ON Users(Username, CreatedDate);

-- Drop index
DROP INDEX IX_Users_Username ON Users;

Views

-- Create view
CREATE VIEW ActiveUsers AS
SELECT UserID, Username, Email, CreatedDate
FROM Users
WHERE IsActive = 1;
GO

-- Use view
SELECT * FROM ActiveUsers;

-- Update view
ALTER VIEW ActiveUsers AS
SELECT UserID, Username, Email
FROM Users
WHERE IsActive = 1
  AND CreatedDate >= DATEADD(MONTH, -6, GETDATE());
GO

Stored Procedures

-- Create procedure
CREATE PROCEDURE GetUserByID
    @UserID INT
AS
BEGIN
    SELECT *
    FROM Users
    WHERE UserID = @UserID;
END;
GO

-- Execute procedure
EXEC GetUserByID @UserID = 1;

-- Procedure with OUTPUT parameter
CREATE PROCEDURE CreateUser
    @Username NVARCHAR(50),
    @Email NVARCHAR(100),
    @NewUserID INT OUTPUT
AS
BEGIN
    INSERT INTO Users (Username, Email)
    VALUES (@Username, @Email);

    SET @NewUserID = SCOPE_IDENTITY();
END;
GO

-- Execute with OUTPUT
DECLARE @ID INT;
EXEC CreateUser
    @Username = 'test_user',
    @Email = 'test@example.com',
    @NewUserID = @ID OUTPUT;
SELECT @ID AS NewUserID;

Functions

-- Scalar function
CREATE FUNCTION dbo.GetUserFullName
(
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50)
)
RETURNS NVARCHAR(101)
AS
BEGIN
    RETURN @FirstName + ' ' + @LastName;
END;
GO

-- Use function
SELECT dbo.GetUserFullName('John', 'Doe');

-- Table-valued function
CREATE FUNCTION dbo.GetActiveUsers()
RETURNS TABLE
AS
RETURN
(
    SELECT UserID, Username, Email
    FROM Users
    WHERE IsActive = 1
);
GO

-- Use table function
SELECT * FROM dbo.GetActiveUsers();

Transactions

-- Basic transaction
BEGIN TRANSACTION;

    INSERT INTO Users (Username, Email)
    VALUES ('user1', 'user1@example.com');

    UPDATE Users
    SET IsActive = 1
    WHERE Username = 'user1';

COMMIT TRANSACTION;

-- Transaction with error handling
BEGIN TRY
    BEGIN TRANSACTION;

        -- Operations here
        INSERT INTO Users (Username, Email)
        VALUES ('user2', 'user2@example.com');

        -- Force error for testing
        -- INSERT INTO Users (UserID) VALUES (NULL);

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;

    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Best Practices

1. Always Use Schema Names

-- Good
SELECT * FROM dbo.Users;

-- Bad
SELECT * FROM Users;

2. Use Parameterized Queries

-- Prevents SQL injection
DECLARE @Username NVARCHAR(50) = 'john_doe';
SELECT * FROM Users WHERE Username = @Username;

-- Never do this with user input:
-- SELECT * FROM Users WHERE Username = '" + userInput + "'

3. Use Appropriate Data Types

-- Good
Email NVARCHAR(100)

-- Wasteful
Email NVARCHAR(MAX)

-- Too small
Email NVARCHAR(20)

4. Create Indexes on Foreign Keys

CREATE INDEX IX_Orders_CustomerID
ON Orders(CustomerID);

5. Use Transactions for Multiple Operations

BEGIN TRANSACTION;
    -- Insert order
    -- Insert order items
    -- Update inventory
COMMIT TRANSACTION;

Next Steps

Learn about JOINs for combining data from multiple tables
Master query optimization with execution plans
Explore reporting features with aggregations and grouping
Understand backup and recovery strategies
Study security and user permissions

This is the foundation - the next articles will dive deeper into advanced querying, joins, and reporting!

Microsoft SQL Server - Getting Started

Microsoft SQL Server - Getting Started

What is Microsoft SQL Server?

Key Features

Installation

SQL Server Express (Free)

SQL Server Management Studio (SSMS)

Basic Connection

Using SSMS

Connection String (C#)

Connection String (Python)

Creating Your First Database

Key T-SQL Syntax

Basic CRUD Operations

Create (INSERT)

Read (SELECT)

Update (UPDATE)

Delete (DELETE)

Data Types

Common Types

Constraints

Constraint Types

Indexes

Views

Stored Procedures

Functions

Transactions

Best Practices

1. Always Use Schema Names

2. Use Parameterized Queries

3. Use Appropriate Data Types

4. Create Indexes on Foreign Keys

5. Use Transactions for Multiple Operations

Next Steps

Quick Nav

Related Articles