Database Management

This section covers essential tasks for managing SQL Server databases, including creation, modification, and maintenance.

Creating a New Database

You can create a new database using SQL Server Management Studio (SSMS) or Transact-SQL (T-SQL).

Using SSMS:

1. Connect to your SQL Server instance in SSMS.
2. In Object Explorer, right-click on the "Databases" folder.
3. Select "New Database...".
4. Enter the database name and configure file locations and sizes as needed.
5. Click "OK".

Using T-SQL:

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 );
        

Altering Database Properties

You can modify various properties of an existing database, such as its name, file sizes, and growth settings.

Changing Database Name:

ALTER DATABASE MyOldDatabase
NAME = MyNewDatabaseName;
        

Modifying File Growth:

ALTER DATABASE MyDatabase
MODIFY FILE
( NAME = MyDatabase_Data,
    FILEGROWTH = 100MB );

ALTER DATABASE MyDatabase
MODIFY FILE
( NAME = MyDatabase_Log,
    MAXSIZE = 500MB );
        

Database Maintenance

Regular database maintenance is crucial for performance and data integrity.

Updating Statistics:

Statistics help the query optimizer choose the best execution plan. It's recommended to update statistics regularly, especially after significant data changes.

-- Update statistics for all tables in the current database
UPDATE STATISTICS WITH ALL_TABLES;

-- Update statistics for a specific table
UPDATE STATISTICS MyTable WITH FULLSCAN;
        

Rebuilding Indexes:

Index fragmentation can degrade performance. Rebuilding or reorganizing indexes can resolve this.

-- Rebuild all indexes on a table
ALTER INDEX ALL ON MyTable REBUILD;

-- Reorganize indexes with low fragmentation
ALTER INDEX ALL ON MyTable REORGANIZE;
        

Checking Database Integrity:

DBCC CHECKDB is a powerful command to check for logical and physical integrity errors in your database.

DBCC CHECKDB (MyDatabase) WITH NO_INFOMSGS, ALL_ERRORMSGS;
        

Detaching and Attaching Databases

Detaching a database removes it from the SQL Server instance, allowing you to move or copy its data and log files. Attaching brings a detached database back online.

Detaching a Database:

USE master;
GO
EXEC sp_detach_db 'MyDatabase';
GO
        

Attaching a Database:

USE master;
GO
CREATE DATABASE MyDatabase
ON (FILENAME = 'C:\Path\To\MyDatabase_Data.mdf'),
   (FILENAME = 'C:\Path\To\MyDatabase_Log.ldf')
FOR ATTACH;
GO