SQL Server - Create a Database

One of the first things we should look at with SQL Server/Management Studio is how to create a database. After all, most of the tasks you perform with SQL Server will evolve around one or more databases.

System Databases

If you've only just installed SQL Server, you might notice that some databases have already been created. These are system databases.

Database	Type	Description
master	System database	Stores system level information such as user accounts, configuration settings, and info on all other databases.
model	System database	This database is used as a template for all other databases that are created.
msdb	System database	Used by the SQL Server Agent for configuring alerts and scheduled jobs etc
tempdb	System database	Holds all temporary tables, temporary stored procedures, and any other temporary storage requirements generated by SQL Server.

We will now create another database for our own use.

Creating a New Database

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

1. Right click on the "Databases" icon and select "New Database...":

    

           2.Name your database and click "OK":

Your New Database

You will now notice your new database appears under the "Databases" section of SQL Server Management Studio.
Your 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. We also could have changed specifications on whether to allow the file to grow automatically (as it stores more and more data), and if so, how that growth should be managed. We could have done that at step 2. But all is not lost. We can still do it now that we've created the database. We can do it via the Properties dialog box.
To view or change the database properties, simply right click on the database and select "Properties":

The Properties dialog contains a large number of options for changing the configuration of your database. For now, we can leave everything at its default setting.

SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) is the main administration console for SQL Server.
SSMS enables you to create database objects (such as databases, tables, views etc), view the data within your database, you can configure user accounts, transfer data between databases, and more.
Here's what SQL Server Management Studio looks like when you first open it up:

The left pane contains the Object Explorer. The Object Explorer provides navigation to databases, server objects (such as triggers), log files, and more.
The right pane allows you to write queries against the database and view the results. In this screenshot I have opened a blank query by clicking the "New Query" button. You can also bring up other windows, such as the Properties window.
Note that I have minimized the size of the window for this screenshot. Once maximized, you have much more room to play with.
You can use SQL Server Management Studio to create as many databases as you like. You can also connect to as many databases on as many servers as you like.
Most of the tasks performed with SQL Server Management Studio are initiated either from the top menu, or by right-clicking on an icon/object.
Throughout most of this tutorial, we'll be looking at the various things you can do via SQL Server Management Studio.

SQL Server 2008 Editions

SQL Server 2008 Editions
If you are serious about installing (or upgrading) SQL Server, this page provides a quick overview of your options.
SQL Server 2008 comes in many different editions. The edition you choose will depend on your requirements. If you are looking for a free database management system, you will need to choose one of the Express editions or the Compact edition. You could also try the Evaluation edition, which allows you to trial SQL Server 2008 for 180 days.
The Editions
Here are the different editions available for SQL Server 2008.
• Enterprise Edition
Data management and business intelligence platform providing enterprise class scalability, high availability, and security for running business-critical applications
• Standard Edition
Data management and business intelligence platform providing ease of use and manageability for running departmental applications
• Workgroup Edition
Data management and reporting platform providing secure, remote synchronization, and management capabilities for running branch applications
• Developer Edition
May be installed and used by one user to design, develop, test, and demonstrate your programs on as many systems as needed
• Web Edition
A low-TCO, scalable, and manageable database option for web hosters and end customers looking to deploy publicly facing web applications and services
• Express Edition
A free edition of SQL Server ideal for learning and building desktop and small server applications and for redistribution by ISVs
• Compact Edition
A free, SQL Server embedded database ideal for building stand-alone and occasionally connected applications for mobile devices, desktops, and web clients
• Evaluation Edition
This edition may be installed for demonstration and evaluation purposes until an expiration period of 180 days.
SQL Server Edition in this Tutorial
The examples in this tutorial were made using the (free) Express edition of SQL Server 2008. To be more precise, it is SQL Server 2008 Express with Advanced Services.
SQL Server 2008 Express with Advanced Services includes the following features:
• SQL Server database engine - create, store, update and retrieve your data
• SQL Server Management Studio Basic - visual database management tool for creating, editing and managing databases
• Full-text Search - powerful, high-speed engine for searching text-intensive data
• Reporting Services - integrated report creation and design environment to create reports
The Express edition also comes as SQL Server 2008 Express with Tools, which only includes the SQL Server database engine and the SQL Server Management Studio Basic, and SQL Server 2008 Express (Runtime Only) which doesn't include the SQL Server Management Studio.
While the free version of SQL Server does have its limitations, it is a good start for those starting out with SQL Server.
You can download SQL Server 2008 Express from Microsoft's website.

SQL server 2008 Tutorial

About SQL Server

Microsoft SQL Server is a Relational Database Management System (RDBMS) designed to run on platforms ranging from laptops to large multiprocessor servers. SQL Server is commonly used as the backend system for websites and corporate CRMs and can support thousands of concurrent users.
SQL Server comes with a number of tools to help you with your database administration and programming tasks.
SQL Server is much more robust and scalable than a desktop database management system such as Microsoft Access. Anyone who has ever tried using Access as a backend to a website will probably be familiar with the errors that were generated when too many users tried to access the database!
Although SQL Server can also be run as a desktop database system, it is most commonly used as a server database system.

Server Database Systems

Server based database systems are designed to run on a central server, so that multiple users can access the same data simultaneously. The users normally access the database through an application.
For example, a website could store all its content in a database. Whenever a visitor views an article, they are retrieving data from the database. As you know, websites aren't normally limited to just one user. So, at any given moment, a website could be serving up hundreds, or even thousands of articles to its website visitors. At the same time, other users could be updating their personal profile in the members' area, or subscribing to a newsletter, or anything else that website users do.
Generally, it's the application that provides the functionality to these visitors. It is the database that stores the data and makes it available. Having said that, SQL Server does include some useful features that can assist the application in providing its functionality.

SQL Server Editions

SQL Server comes in many editions. Some are paid versions others are free. The examples in this tutorial were done using the free Express version of SQL Server.