CREATE DATABASE: CREATE DATABASE is a fundamental SQL command that you need to know in order to work with databases. Here, you will learn how to use it. Read more here.
Welcome to the first article of the fundamental SQL Server series. This series will cover essential topics you should know for starting your career as a SQL Server DBA.
In this part, we will understand a database and how you can create a database in SQL Server using SQL Query and GUI.
What is a database?
The database stores the structured information or data electronically in a computer system. The database is managed by Database Management Systems (DBMS). A database uses different entities to store data. We will cover it later.
What is T-SQL language?
Transact-SQL (T-SQL) is Structure Query Language (SQL) for working with Microsoft SQL Server. It provides all the functionality of SQL compatible with SQL Server. It is popularly known as T-SQL.
Requirements for the Fundamental SQL:
You need the following tools to start following this article.
1. SQL Server Management Studio: Download Link
2. SQL Server Instance: You can use SQL expression edition or development edition for learning purposes. Here, I use SQL Server 2019 developer edition. Download Link
3. AdventureWorks database: Download Link
How to create a SQL Database using SQL Server Management Studio
By default, once you install the SQL Server instance, it establishes the following system databases.
Master
Model
MSDB
TempDB
It is recommended not to use system databases for storing user-defined objects. You can create your database and deploy objects into it.
To create a new database, connect to an instance of the SQL Server Database Engine and then expand that instance. Right-click on the database node in SSMS and choose New Database.
We can use the default database configuration. To create the database with default values, enter the database name and select OK.
In the default configuration, SQL Server creates a database with one primary data file (MDF) and transaction log (LDF).
Primary data file: Initial size 8 MB with 64 MB auto-growth and unlimited (max to disk space) max file size. The primary data file extension is MDF.
Transaction log file: Initial size 8 MB with 64 MB auto-growth and unlimited (max to disk space) max file size. The transaction log (t-log) file extension is LDF.
These database files are stored in the default directory of SQL Server that you specify during instance installation.
To learn the equivalent SQL query, SSMS provides you the option to script out your SSMS actions.
Click on Script as shown in the above image and choose script action to the new query window.
It gives you SQL query Create Database statement with a few default configurations such as file directories and compatibility level.
Do you need to write a complete SQL Query for creating a SQL database with a T-SQL script? No, to create the database without changing the data, log file locations, use the following Script
Use Master
Go
Create Database [FundamentalDB]
GoStay tuned for the next article on the Fundamental SQL series.