Creating Databases in SQL Server

This section provides comprehensive guidance on creating and configuring databases within the SQL Server Database Engine. Understanding the process of database creation is fundamental for any SQL Server developer or administrator.

Methods for Creating Databases

You can create databases using several methods, primarily through SQL Server Management Studio (SSMS) or Transact-SQL (T-SQL) commands.

1. Using SQL Server Management Studio (SSMS)

SSMS offers a graphical interface for creating databases, making it accessible for beginners.

1. Connect to an instance of the SQL Server Database Engine in Object Explorer.
2. Right-click the Databases folder, then select New Database....
3. In the New Database dialog box, enter the database name.
4. Optionally, specify the owner, collation settings, and initial file locations and sizes for the data and log files.
5. Click OK to create the database.

2. Using Transact-SQL (T-SQL)

For automation or scripting, T-SQL provides a powerful way to create databases.

The basic syntax for creating a database is:

CREATE DATABASE database_name
[ ON
    [ PRIMARY
        ( NAME = logical_file_name,
          FILENAME = 'os_file_name',
          SIZE = size,
          MAXSIZE = { max_size } | UNLIMITED,
          FILEGROWTH = { growth_increment } )
    ]
    [, FILEGROUP filegroup_name
        [ PRIMARY ]
        ( NAME = logical_file_name,
          FILENAME = 'os_file_name',
          SIZE = size,
          MAXSIZE = { max_size } | UNLIMITED,
          FILEGROWTH = { growth_increment } )
        [, ... ]
    ]
    [, ... ]
]
[ LOG ON
    { FILES | FILEGROUP filegroup_name }
    ( NAME = logical_file_name,
      FILENAME = 'os_file_name',
      SIZE = size,
      MAXSIZE = { max_size } | UNLIMITED,
      FILEGROWTH = { growth_increment } )
    [, ... ]
]
[ COLLATE collation_name ]
[ WITH  [,...n] ] ;

A simple example to create a database named 'MyNewDatabase':

CREATE DATABASE MyNewDatabase;

Creating a database with specified file locations and growth settings:

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

Database File Types

• Primary Data Files (.mdf): Contain the database's startup information and a pointer to the rest of the files in the database. Each database has one primary data file.
• Secondary Data Files (.ndf): Optional files that contain data and objects. A database can have zero or more secondary data files.
• Transaction Log Files (.ldf): Contain the information required to recover the database. Each database must have at least one transaction log file.

Key Considerations

• Database Name: Choose a descriptive and unique name.
• File Locations: Store data and log files on separate physical drives for better performance and recovery.
• Initial Size: Set an appropriate initial size to avoid frequent autogrowth operations.
• Autogrowth: Configure autogrowth settings carefully. Small, frequent growths can impact performance. Fixed increments are often preferred over percentage-based growths.
• Collation: Specify the collation if you need specific rules for sorting and case sensitivity, especially if your application handles data in different languages.

Advanced Options

The CREATE DATABASE statement supports various options to control database behavior, such as:

• RECOVERY MODEL (FULL, BULK_LOGGED, SIMPLE)
• CONTAINMENT (NONE, PARTIAL, ALL)
• FILESTREAM options
• DEFAULT_FULLTEXT_LANGUAGE

Refer to the SQL Server T-SQL Reference for a complete list of options.