01. SQL Server Management Studio (SSMS)

Level: Beginner

ℹ️ What You'll Learn

• Install and configure SSMS
• Connect to local SQL Server
• Create databases and tables
• Write and execute T-SQL queries
• View and edit data directly
• Create indexes and manage databases
• Set up School Management System database

The Problem

SQL Server is where your data lives. SSMS is the tool to manage it. You need to create tables, write queries, fix data issues, and optimize performance. As a backend developer, you'll spend time in SSMS debugging database issues.

Install SSMS

Download from: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

Install and launch.

Connect to Server

1. Server name: (localdb)\mssqllocaldb (local development)
2. Authentication: Windows Authentication
3. Click Connect

Connected to local SQL Server.

Create SMS Database

Right-click Databases → New Database:

Name: SmsDb
Location: Default
Filegroup: PRIMARY

Click OK.

Database created: SmsDb

Create Tables

Right-click Tables → New Table:

Create Students table:

CREATE TABLE Students (
    Id INT PRIMARY KEY IDENTITY(1,1),
    PublicId UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    SchoolId INT NOT NULL,
    Name NVARCHAR(100) NOT NULL,
    RollNumber NVARCHAR(50) NOT NULL,
    ClassName NVARCHAR(10) NOT NULL,
    Status INT NOT NULL DEFAULT 1,
    CreatedAt DATETIME DEFAULT GETUTCDATE()
);

Run Query

Right-click Database → New Query:

SELECT * FROM Students;

INSERT INTO Students (SchoolId, Name, RollNumber, ClassName)
VALUES (1, 'Ravi Kumar', 'SMS-2024-001', '10-A');

SELECT COUNT(*) as TotalStudents FROM Students;

Click Execute (Ctrl + E).

Results in grid below.

View Data

Right-click Table → Select Top 1000 Rows:

| Id | PublicId | SchoolId | Name | RollNumber | ClassName | Status |
|-----|----------|----------|------|-----------|-----------|---------|
| 1   | abc123.. | 1        | Ravi | SMS-..001 | 10-A      | 1       |

Edit data inline.

Create Indexes

CREATE INDEX IX_Student_RollNumber ON Students(RollNumber);
CREATE INDEX IX_Student_ClassName ON Students(ClassName);

Improves query performance.

Key Takeaways

• SSMS = SQL Server administration tool
• Create databases, tables, indexes
• Execute queries
• View and edit data
• Performance monitoring

💡 SSMS Tip

Use Object Explorer on left. Right-click for context menu options.

🤖Use AI to Learn Faster

Use ChatGPT, Claude, or Copilot to go deeper on SSMS Database Management. Try these prompts:

• "How do I create a new database?"
• "How do I create tables?"
• "How do I execute SQL queries?"
• "Quiz me on SSMS"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.