Database Management

This section covers the fundamental aspects of managing SQL databases, including creation, configuration, optimization, and maintenance.

Creating and Configuring Databases

Learn how to create new databases, define their properties, and configure storage options. This includes setting initial sizes, growth increments, and file locations for data and transaction logs.

Creating a New Database

You can create a database using T-SQL commands or the SQL Server Management Studio (SSMS) GUI.

CREATE DATABASE MyNewDatabase
ON PRIMARY
( NAME = MyNewDatabase_Data,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MyNewDatabase_Data.mdf',
    SIZE = 10MB,
    MAXSIZE = 50MB,
    FILEGROWTH = 5MB )
LOG ON
( NAME = MyNewDatabase_Log,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MyNewDatabase_Log.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB );
            

Database Properties

Key properties to manage include recovery model, compatibility level, and collation. The recovery model dictates how transactions are logged, impacting backup and recovery strategies.

Database Maintenance

Regular maintenance is essential to ensure optimal performance and data integrity. This includes tasks such as updating statistics, rebuilding indexes, and performing integrity checks.

Updating Statistics

Outdated statistics can lead to inefficient query execution plans. Regularly update statistics for tables and indexes.

UPDATE STATISTICS MyTable WITH FULLSCAN;
            

Rebuilding and Reorganizing Indexes

Indexes can become fragmented over time, reducing query performance. Rebuild or reorganize indexes to improve their structure.

ALTER INDEX MyIndex ON MyTable REBUILD;
            

Database Integrity Checks

Use DBCC CHECKDB to verify the logical and physical integrity of your entire database.

DBCC CHECKDB (MyDatabase) WITH NO_INFOMSGS;
            

Managing Database Files and Filegroups

For large databases, organizing data into filegroups can improve manageability and performance, especially when distributing data across different physical storage devices.

Filegroups

Filegroups allow you to group data files. Primary filegroups contain all the primary data and is the default.

ALTER DATABASE MyDatabase
ADD FILEGROUP MyNewFilegroup;

ALTER DATABASE MyDatabase
ADD FILE
(
    NAME = MyDatabase_Data2,
    FILENAME = 'D:\SQLData\MyDatabase_Data2.mdf',
    SIZE = 100MB,
    MAXSIZE = 1GB,
    FILEGROWTH = 100MB
)
TO FILEGROUP MyNewFilegroup;
            

Data vs. Log Files

Data files (.mdf, .ndf) store the actual data, while log files (.ldf) store transaction log records. Separating these onto different physical disks can improve I/O performance.

Database Shrinking and Growth

Managing the size of your database files is important for disk space utilization and performance. Auto-growth settings should be configured carefully.

Backup and Recovery Strategies

A robust backup and recovery strategy is paramount. This section outlines different backup types (full, differential, transaction log) and recovery models.

See the Backup & Recovery section for detailed information.