how to create a new database using Management Studio

In previous chapters, you learned to how to connect to SQL Server 2005 using management studio. In this chapter, you will learn how to create and delete a database.

Create new database

To create a new database, right click on the "Databases" node in the left panel in the Management studio. You will get couple of options in the context menu. Select the option 'New Database'.




You will get another dialog box where you have to specify few details. The most important one is the database name. In the below screenshot, I have specified the new database name as "MySampleDatabase".



You may notice two other circled fields called "logical name" and "path". When any database is created, there is at least 2 files created in the hard disk. One file is called "Data file" and it has an extension of .MDF. The other file is called "log file" and it has an extension of .LDF by default. The logical names are the internal names used by the SQL Server to represent the data file and logical file. The path represents the physical location of the above files in your hard disk. The default location will be within the Program Files folder. You may change it to your projects folder or any other folder you want. However, the only mandatory entry required fill to create a new database is the database name.

Manage the Databases

If you expand the node "Databases" on the management studio, you will see the following sub nodes:

System Databases
Database Snapshots

You can expand the System Databases node further to see all the default, system databases. You may not want to mess with them !

Below the System Databases and Database Snapshots, you will see the list of the databases you have created. You can expand any of your database name to see further options below the node. See the screenshot:





Above screen shot shows two custom databases with the names "dotnetspider" and 'aspspider".

Under the database name, you may expand the "Tables" node to see the list of all tables within the selected database.

How to use Management Studio ?

In the previous chapter, you learned how to connect to SQL Server Database Server. In this chapter, you can find more about how to use Management Studio to perform various actions on SQL Server.

After you connect to SQL Server, you will see a screen as shown below:




The left side panel shows a tree structure. The first node says "(local)\SQL2005". Here, I have connected to an instance SQL Server called "SQL2005" on the local computer. Since I have SQL Server 2000 and SQL Server 2005 installed on my computer, I used the name "SQL2005" when I installed SQL 2005.

When you expand the server name, you will see few nodes depending on the number of features you have installed:

1. Databases
2. Security
3. Server Objects
4. Replication
5. Management
6. Notification Services
etc.

The first node "Databases" is the one that you will use most of the time. This node includes all the databases created in this instance of the database server. The security node allows you to manage user accounts and permissions.

Connecting to SQL Server Database using Management Studio

As you learned in one of the previous chapters, SQL Management Studio can be used to manage multiple instances of SQL Server. However, when you open the Management Studio, it will prompt you to specify the login information to any one of the instances.

The below screenshot shows the login screen of SQL Management Studio 2005.





Server Type

The first dropdown in the above screenshot represents the "Server Type". Depending on the number of features you have installed, there will be various options here. When you are trying to connect to the database server, you have to choose "Database Engine".

Server Name

Second dropdown is to select the database server name. If you are connecting to the SQL Server installed on local computer, you can use one of the following (assuming DevServer1 as the computer name and 192.168.2.100 as IP Address):

(local)
. (dot)
localhost
127.0.0.1
DevServer1
192.168.2.100

The first 4 options are used to represent the local computer. If you are connecting to an SQL Server installed on a different computer, you can use only IP Address or Computer name.

If you are connecting to a named instance of SQL Server instead of the default instance, then you must append a backslash and instance name to the above mentioned names. Example: 192.168.2.100\SQLInstance2

Authentication

This dropdown gives two options:

1. Windows Authentication
2. SQL Server Authentication

If you select Windows Authentication, the login and password you used while logging in to your computer will be used to connect to the database server. If you are an administrator on the local computer or if you have configured the database server to use your windows account, you can use this option.

SQL Server Authentication can be used if you have created a user name and password in SQL Server. In that case, you must select this option and specify the user name and password below the drop down.

After specifying appropriate options above, you can press the "Connect" button to connect to the specified database server.

SQL Management Studio Editions

SQL Management Studio comes in two flavours:

1. SQL Management Studio

This edition comes with all versions of SQL Server 2005 except SQL Server 2005 Express Edition. This edition has various advanced features like import data from other database systems, export data to other databases etc.

2. SQL Management Studio Express

SQL Management Studio Express is part of SQL Server 2005 Express Advanced edition. You can download Management Studio Express as a separate tool also. Some of the key features like Import data, export data etc are missing in the Express edition of Management Studio.

For learning purposes and development purposes, the express edition serves the purpose. After all, it is FREE !

The non express edition of the management studio is also free when you purchase non express editions of SQL Server 2005, but you have to pay several thousand dollars for the SQL Server license.

What is SQL Management Studio?

As you already learned, the core part of SQL Server system is the 'server' which runs as a service. The service is a hidden application that runs in the background and it is hard to deal with this service directly.

SQL Server 2005 comes with a tool called 'Management Studio' which will help you manage your SQL Server. SQL Management Studio allows you to perform various actions on SQL Server including:

1. Create/Delete databases
2. Backup/restore databases
3. Attach/detach databases
4. Design tables and edit data
5. Execute queries against any database

SQL Management studio has a lot more features than explained above. Some of the main functionalists mentioned above are explained in coming chapters.

SQL Management Studio allows you to manage multiple instances of SQL Server installed on same computer or different computers. To manage a specific instance of SQL Server, you have to first connect to the instance using appropriate login and password. You need to install management studio only once even if you install multiple instances of SQL Server.

Selecting authentication mode while installing

SQL Server (and SQL Server Express) supports two authentication modes:

1. Windows Authentication mode
2. Mixed Mode (Windows Authentication and SQL Authentication)

If you select "Windows Mode", you have to use your windows user id and password to connect to SQL Server. Microsoft recommends "Windows Mode" for security reasons.

If you select "Mixed Mode", you have both the options - either use your windows account or create users in SQL Server.

For learning purpose, let us select "Mixed Mode" when you install SQL Server. When you select this mode, a default administrative account called "sa" will be created on SQL Server. You have to select a password for this account.




Enter a secure password in the above dialog and proceed. Don't forget this password. This is your administrative password and you should use this account to create new users in SQL Server.

Your Administrative Account in SQL Server: sa
Administrative password:

If you installed SQL Server Express Advanced Edition, then it will also install SQL Server Management Studio automatically. SQL Management Studio Express is a tool that is used to view, edit, create and manage databases.

If you installed regular version of SQL Server Express, then you must install Management Studio Express separately. Proceed to next chapter for installation instructions on Management Studio Express

Installing SQL Server Express

This chapter talks about installing SQL Server Express. If you have a licensed version of full SQL Server 2005, you can follow the same steps. The installation process is almost the same.

After downloading SQL Server Express, double click on the file SQLEXPR.EXE to start installation. The setup wizard will guide you through various steps.

The first few steps are very obvious. You can accept the terms and conditions and proceed.

In the feature selection dialog, by default, only the database services is selected. Click on the second item (Client Components) and select the option "Will be installed on local hard drive". This will install all client components also in addition to the database services.




Click NEXT to proceed.

In the next screen, you have to select the INSTANCE NAME. You have 2 options:

1. Select default instance
2. Select an instance name

The default name shown will be "SQLExpress". You may leave it as it is, or change it to "default instance". The point is, you have to remember what option you selected.

If you choose the "default instance", then you will be refering to your SQL Server instance with the name (local) orlocalhost or 127.0.0.1.

If you choose "SQLExpress", then then you will be refering to your SQL Server instance with the name (local)\SQLExpress or localhost\SQLExpress or 127.0.0.1\SQLExpress.

Proceed to the next chapter for additional installation instructions.

Limitations of SQL Server Express

SQL Server Express is a free, easy to use, redistributable version of SQL Server 2005 designed for building simple data-driven applications. SQL Server Express has most of the features offered by SQL Server full version, but with certain limitations. This makes SQL Server Express a good choice for small and medium scale applications.

Most of the limitations of SQL Server Express edition will not affect the small/medium scale applications.

Limitations of SQL Server Express Edition
1. Number of CPUs supported

SQL Server Express uses only one CPU at a time. It can be installed on a server with multiple CPUs, but it will use only one CPU at a time.

2. Maximum memory used

SQL Server Express uses a maximum of 1 GB memory for it's data buffer. So, if your server has severaql GB memeory, SQL Server Express cannot take advantage of it.

4. Database size limit

Maximum database size is limited to 4 GB (log file size is not counted)

5. Profiler in SQL Server Express

Profiler tool is not included with SQL Server Express editions.

6. Job Scheduler

Job Scheduling service is not available with SQL Server Express.

7. Import/Export

Data import and export feature is not available with SQL Server Express.

A complete feature comparison of various editions of SQL Server can be found at the Microsoft web site - http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

SQL Server 2005 Express

SQL Server Express is a free, easy to use, redistributable version of SQL Server 2005 designed for building simple data-driven applications. Developers can design schemas, add data, and query local databases, and perform most of the features supported by SQL Server 2005. If developers need more advanced database features, then SQL Server Express can be seamlessly upgraded to more sophisticated versions of SQL Server.

Microsoft SQL Server 2005 Express Edition (SQL Server Express) is a database platform that is based on Microsoft SQL Server 2005. It is also a replacement for Microsoft Desktop Engine (MSDE).

SQL Server Express comes with a graphical user interface to manage the database system. It is called 'SQL Server Management Studio Express".

SQL Server Express and Management Studio Express are free to download and distribute. If you like to upgrade SQL Server Express databases to full version of SQL Server, it is very straight forward. Just install SQL Server 2005 and your databases and code will work without any changes.

You can download SQL Server Express and Management Studio Express from Microsoft website.

If you are looking for a free, powerful database system for your application, SQL Server Express if the way to go!

Manage SQL Server services using SQL Service Manager

In one of the previous chapters, you learnt how to use Windows Service Control Manager to start/stop SQL Server services.

Microsoft provides a special tool called 'SQL Server Service Manager'. This tool does almost the same thing - allow you to Start, Pause or Stop the SQL Server services.

The SQL Service Control Manager can manage SQL Servers installed onany computer in the same network. (Windows Service Control displays all services installed on the same computer).

The below image shows the screen shot of SQL Service Control Manager.



In the Server drop down, you can type or select the IP Address of any SQL Server in your network. When you select an IP Address, it will display all SQL Servers installed on that machine. Then you can choose the options like Start or Stop.

When SQL Service Control Manager is running, you can see an icon in the system tray as shown below:



The icon will have a green arrow when the currently selected SQL Server is running. If the currently selected SQL Server is stopped, it will have a red color arrow.

If the SQL Service COntrol Manager is not running, you can start the it from the programs menu > Microsoft SQL Server -> Service Manager.

How to start and stop SQL Server service ?

A "service" is an application that can start automatically when the computer starts.

There are two start up modes:

1. Automatic - the service starts automatically when the computer starts.
2. Manual - the service is installed, but will not start until somebody manually start it.

By default, SQL Server is configured as "Automatic". So, SQL Server service starts when you start your computer. However, some developers will change it to "Manual" mode for various reasons. One example is, if you have a very slow computer, you may want to stop all unwanted applications running. So, you can go and stop the SQL Server service. Also, you can change it to "manual" mode so that it won't start automatically everytime you reboot your computer. However, when you want to run an application that need to access SQL Server, you have to go to the service control manager and start the service.

Service Control manager

"Service control manager" is a windows tool that allow you manage all services (including SQL Server service) installed on the computer.

To open the service control manager, go to Control Panel -> Administrative Tools -> Services.



You can see the list of all installed services and their current status. Right click on any service name and see the context menu. You can see various options like Start, stop, restart, pause etc.

The above image shows several service names starting with the word "SQL Server...". They are various services related to SQL Server. You can right click on the service name and select any task like Start or Stop.

SQL Server Instances

SQL Server (service) can be installed multiple times on the same computer. Each installation is called an 'Instance'. When you install SQL Server each time, a new windows service is installed.

Each instance of the SQL Server can have it's own databases, security, users etc. Each instance one can be stopped and started without affecting other instances.

SQL Server 2005 allows to install up to 50 instances on the same computer.

Instances are classified to two categories:

1. Default instance (no name)
2. Named instance

You can have only one default instance and all other instances should have a unique name. You have the option of selecting default instance or named instance while installing SQL Server.

In most cases, you will need to install only one instance (default instance). Multiple instances are useful if you have multiple departments or multiple clients sharing the same computer and do need to manage their SQL Server instance without affecting other departments/clients.

You can access the default instance of SQL Server using one of the following names. Assume the IP Address of the computer is 192.168.2.100 and computer name is DevServer1.

(local)
. (dot)
localhost
127.0.0.1
DevServer1
192.168.2.100

Any of the above names can be used if the SQL Server is installed on the same computer. if it is remote computer, then you can access it using only the IP Address or computer name (last 2 options). The IP Address 127.0.0.1 is a spacial IP Address to represent the local computer. Even though this is not a real IP Address, you can represent the local computer using this Ip Address.

The named instances can be accessed by appending the name of the instance to one of the above with a backslash, as shown below:

(local)\INSTANCENAME
.\INSTANCENAME
localhost\INSTANCENAME
127.0.0.1\INSTANCENAME
DevServer1\INSTANCENAME
192.168.2.100\INSTANCENAME

SQL Server service

What is the Server ?

All advanced database management systems include some kind of service applications as part of the database system. For example, to use the SQL Server database, you must have an application called "SQL Server Engine" running on the computer. This application is a windows service (a hidden application that will start automatically when your computer starts and keep running always).

Your program (software) which need to access the database will not access the database file directly. Instead, your program will communicate with the database service application. It is the responsibility of this service to communicate with the database file and execute your commands.

When you install SQL Server on your computer, the SQL Server service will be automatically installed on your computer. When you write code to access database, you just write the ADO.NET code using C#, VB.NET etc. The .NET Framework will execute your ADO.NET code to communicate with the database server and database server will communicate with the database file to execute. All these happens without your knowledge. All you do is, write ADO.NET code to execute queries and you will get the results back.

To make your ADO.NET code work, the database service must be running. If somebody stop the service, your code will fail.

In case of file based database systems like MS Access or FoxPro, there is no database service involved. Your code will directly communicate with the database file.

SQL Server Editions

SQL Server 2005 Enterprise Edition

Enterprise Edition scales to very high performance levels required to support the largest enterprise online transaction processing (OLTP), highly complex data analysis, data warehousing systems, and Web sites.

SQL Server 2005 Standard Edition

SQL Server 2005 Standard Edition is best suited for small and medium sized organizations. It scales to meet reasonable volume and load of data.

SQL Server 2005 Workgroup Edition

SQL Server 2005 Workgroup Edition is the data management solution for small organizations that need a database with no limits on size or number of users.

SQL Server 2005 Developer Edition

SQL Server 2005 Developer Edition has all features of Enterprise edition, but it is licensed to use only for development and testing purposes and not for deployment on production systems.

SQL Server 2005 Express Edition

The SQL Server Express edition is a replacement for the MSDE (free database system). This edition is based on SQL Server 2005 and offers a lot more features than MSDE. Even though Microsoft claims that is edition can be used only for small applications, a smart design and appropriate caching will make this a smart choice for several types of applications. Several websites have switched from MS Access or MySql to SQL Server 2005 Express since it is free and offers lot of features of SQL Server 2005.

SQL Server Express is free and can be redistributed (subject to some agreement), and functions as the client database, as well as a basic server database. SQL Server Express is an ideal choice for deploying with small client-server applications as well as small web sites.

SQL Server 2005 Compact Edition

SQL Server Compact Edition is the compact database server for smart devices. SQL Server Compact Edition is capable of replicating data with SQL Server 2005 and SQL Server 2000.

SQL Server 2005 Runtime Edition

SQL Server 2005 Runtime Edition is a special edition designed for Microsoft ISV partners. Under the end user license agreement for SQL Server 2005 Runtime Edition, an independent software vendor (ISV) may embed SQL Server code into their software but end users are not allowed to use this instance of SQL Server to access from any other applications.

Among various versions mentioned above, SQL Server 2005 Expression is the only free version. Enterprise and standard editions can cost several thousand dollars depending on the number of users and number of CPUs in the system.

What is MSDE ?

MSDE stands for Microsoft Desktop Engine. MSDE is replaced with Microsoft SQL Server 2005 Express.

MSDE is a free, light weight version of SQL Server. It lacks several advanced features of SQL Server, but still lot of people use MSDE since it is a free version.

MSDE Facts

# Microsoft does not provide a graphical user itnerface (like SQL Enterprise Manager) to manage the databases. However, there are several third party management tools available. Or, you can use a console based interface provided by Microsoft.
# Performance throttling prevents too many users accessing database at the same time (Microsoft does not want you to use MSDE for high traffic applications ! Instead, they want you to buy SQL Server)
# MSDE is based on SQL Server engine, but is a scaled down version of SQL Server.
# Migrating from MSDE to SQL Server is very easy. All you have to do is, uninstall MSDE and install SQL Serer. Your databases and code will work without any change.
# MSDE is not for sale as a separate product. It is available for royalty-free redistribution by vendors under certain MSDE licensing conditions.
# MSDE allows a maximum database size of 2 GB
# MSDE can use a maximum of 2 GB RAM
# Supports only 2 CPUs on the server.
# Features like Full-text search, profiler, import/export wizards, index tuning wizard etc not available.

MSDE is an outdated database system. If you are looking for free relational database system, you may want to try Microsoft SQL Server 2005 Express edition.

Compare SQL Server and MS Access

Advantages of MS Access

MS Access is a light weight database managements system. Also, it provides it's own designer so that you can generate reports and simple windows applications without using another programming environment.

It is easy to deploy an MS Access database. Just copy the .MDB file to appropriate location and you are done. There is no database attach/detach process.

Which one to choose - MS Access or SQL Server ?

MS Access is ideal only for applications that require very small databases. Even though the maximum database size limit for MS Access is 1 GB, the performance becomes poor if the size grows more than few hundred MBs.

If you expect your application database to grow more than 100 MB in future, you should consider using SQL Server. It may not be an easy task to migrate to SQL Server later. Eeven though SQL Server allow you to import your MS Access database to SQL Server, you will have to do lot of code changes to take advantage of the SQL Server features.

There will be major performance difference between MS Access and SQL Server. If you are writing a critical application where performance is important, then you must consider SQL Server instead of MS Access.

The new SQL Server 2005 Express is a free version and allow copy/deploy feature. You can copy the database file and attach on the fly. It is a good idea to use SQL Server 2005 Express if you cannot afford to purchase SQL Server license. It has much better peformance and features compared to MS Access.

The bottom line is, choose MS Access only for small and non critical applications.

What is SQL Server?

Microsoft SQL Server
SQL Server is one of the most popular and advanced database systems currently available. SQL Server is provided by Microsoft.

Microsoft SQL Server is sometimes called as "Sequel Server". It can be managed using Structured Query Language.

While MS Access is meant for small applications, SQL Server supports large applications with millions of users or huge databases. SQL Server is much more powerful than Access and provides several other advanced features and much better security. SQL Server is compatible with MS Access. You can easily import/export data between these two.

SQL Server is a Relational database where data is stored and retrieved very efficiently.

Sql Server tutorials for beginners

Tutorials

Chapter 1 : What is SQL Server?
Chapter 2 : Compare SQL Server and MS Access
Chapter 3 : What is MSDE ?
Chapter 4 : SQL Server Editions
Chapter 5 : SQL Server service
Chapter 6 : SQL Server Instances
Chapter 7 : How to start and stop SQL Server service ?
Chapter 8 : Manage SQL Server services using SQL Service Manager
Chapter 9 : SQL Server 2005 Express
Chapter 10 : Limitations of SQL Server Express
Chapter 11 : Download SQL Server Express 2005
Chapter 12 : Installing SQL Server Express
Chapter 13 : Selecting authentication mode while installing
Chapter 14 : What is SQL Management Studio?
Chapter 15 : SQL Management Studio Editions
Chapter 16 : Connecting to SQL Server Database using Management Studio
Chapter 17 : How to use Management Studio ?
Chapter 18 : Manage the Databases
Chapter 19 : how to create a new database using Management Studio