How To Create SQL Data Base (Lesson-3)

For this tutorial I'll be creating a database to track tasks. It can be used as the backend of a task-tracker application. I'll give the database an obvious name like "TaskTracker" so I know what it's used for. This will help distinguish this database from the other databases - especially if one day I end up with say, 10 or 20 databases in my SQL Server Management Studio (which would probably be a small number for some DBAs).

Creating a database can be done by running a SQL script or by "point and click". Here we'll use "point and click". But first, let's look at the system databases.

master

This database stores system level information such as user accounts, configuration settings, and info on all other databases.

model

This database is used as a template for all other databases that are created

msdb

The msdb database is used by the SQL Server Agent for configuring alerts and scheduled jobs etc

tempdb

This one holds all temporary tables, temporary stored procedures, and any other temporary storage requirements generated by SQL Server.

These are system databases and each of them are there for a specific purpose. When we create our own database, the database will be created based on the model database (above). That database provides the template for any new database that you create.

How to Create a New Database

The following steps demonstrate how to create a database in SQL Server 2014 using SQL Server Management Studio.

1.From the Object Explorer, right click on the Databases folder/icon and select New database...:

2. Name your database (I called mine TaskTracker) and click OK:

New Database

Your new database will appear under the Databases section of the Object Explorer (just under the System Databases folder). Here's mine:

As mentioned, the new database is based on the Model database. The Model database is a system database which is used as a template whenever a new database is created. If you use the left pane to navigate to your database and expand the tree, you will notice that your database already contains a number of objects. For example, it already contains system functions, system views, system stored procedures, and (hidden) system tables. These are system objects which provide information about the database.

Other Options

We have just created a database using the default options. When we created the database, a data file and a transaction log were created. They were created in the default location for our server.

If we'd wanted to, we could have specified a different location for these files at the time we created the database. We could also have changed other specifications, such as whether to allow the file to grow automatically (as it stores more and more data), and if so, how that growth should be managed.

Many of these options can be changed via Database Properties, which can be accessed by right-clicking on the database and selecting Properties:

The Properties dialog contains a large number of options for changing the configuration of your database. Clicking on the various items in the top-left pane will result in their respective options being displayed. For now, we'll leave everything at its default setting