06. Create database and table

This article explains about creating databases and tables in SQL Server.

There are multiple objects within a SQL Server instance. When a SQL Server instance is created, several system databases and settings are configured by default. These components are essential for managing the instance.

What is database

A database in SQL Server is a structured collection of data that is organized for easy access, management, and updating. It consists of tables, views, stored procedures, and other database objects. Databases help store and retrieve large amounts of structured data efficiently.

SQL Server Instance  
   └─ Databases  
       ├─ System Databases  
       │  ├─ master      ── (Stores system-level metadata and configuration settings)  
       │  ├─ tempdb      ── (Provides temporary storage for query processing and internal operations)  
       │  ├─ model       ── (Template database used for creating new databases)  
       │  ├─ msdb        ── (Stores SQL Server Agent jobs, alerts, and backup history)  
       │  └─ ResourceDB  ── (Read-only database that contains system objects)  
       │
       └─ User Databases  
           ├─ ExampleDB   ── (User-defined database for application)  
           └─ AnotherDB   ── (Another user-created database)  

— System Databases
master: Maintains system-wide configuration data, instance settings, and login information.
tempdb: Used for temporary storage, sorting, and internal operations. It is recreated every time SQL Server starts.
model: Acts as a template for newly created databases, inheriting its structure and settings.
msdb: Contains job scheduling data, backup history, and SQL Agent metadata.
ResourceDB: A hidden, read-only system database containing system objects like stored procedures and functions.

— User Databases
ExampleDB: Represents a user-created database, which can store tables, views, stored procedures.
AnotherDB: Another example of a user-defined database. Users can create multiple databases as needed.

What is table

A table is the fundamental unit of storage in SQL Server. It consists of rows and columns where each column represents a specific field (e.g., Name, Age, Salary), and each row represents a single record.

SQL Server Instance  
   ├─ Databases  
   │  └─ System Databases  
   │  　  ├─ master      
   │  　  ├─ tempdb      
   │  　  ├─ model       
   │  　  ├─ msdb         
   │  　  └─ ResourceDB  
   │
   └─ User Databases  
       ├─ ExampleDB   
       │  ├─ System Tables  
       │  │  ├─ sys.objects      ── (Stores metadata about all objects in the database)  
       │  │  ├─ sys.indexes      ── (Stores information about indexes in the database)  
       │  │  └─ sys.tables       ── (Stores metadata about user-defined tables)  
       │  │
       │  └─ User Tables  
       │  　  ├─ Customers        ── (Stores customer information)  
       │  　  ├─ Orders           ── (Stores order details)  
       │  　  ├─ Products         ── (Stores product catalog data)  
       │  　  └─ Employees        ── (Stores employee records)  
       │
       └─ AnotherDB   

— System Tables in ExampleDB
sys.objects: Stores metadata about all objects (tables, views, stored procedures, etc.).
sys.indexes: Stores information about indexes, including primary and secondary indexes.
sys.tables: Contains metadata about user-defined tables.

— User Tables in ExampleDB
Customers: Stores customer details such as name, contact information, and purchase history.
Orders: Contains order data, including customer ID, product ID, and purchase date.
Products: Stores product catalog data like product name, price, and stock quantity.
Employees: Contains employee records, including names, positions, and salaries.

How to create databas

Using SSMS

1. Open SQL Server Management Studio (SSMS) and connect to your SQL Server instance.
2. In Object Explorer, right-click on Databases and select New Database.
3. In the New Database window:
   • Enter the Database Name (e.g., MyDatabase).
   • Configure file settings if needed.
   • Click OK to create the database.
4. Your new database will appear in the Object Explorer under Databases.

---

Using T-SQL Query

Alternatively, you can use the following query to create a database:

CREATE DATABASE MyDatabase;
GO

How to create table

Using SSMS

1. Expand MyDatabase in Object Explorer.
2. Right-click on Tables and select New Table.
3. Define columns by entering:
   • Column Name (e.g., EmployeeID, FirstName, LastName, etc.)
   • Data Type (e.g., INT, NVARCHAR(50), DATE, etc.)
   • Allow Nulls (check if null values are allowed)
4. Set the Primary Key:
   • Right-click the EmployeeID column and select Set Primary Key.
5. Click File > Save Table_1 and provide a table name (e.g., Employees).

---

Using T-SQL Query

Use the following query to create a table programmatically:

USE MyDatabase;
GO

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    BirthDate DATE,
    Salary DECIMAL(10,2)
);
GO