Once you have SQL Server and all the necessary updates installed, the next step is to learn how to create databases and work with them. The easiest interface available to the beginner is Microsoft SQL Server Management Studio (SSMS), a graphical environment where you can view and manipulate all of the databases and other objects on your SQL Server instance and perform other advanced functions.
If you installed the Express edition with Management Tools or Advanced Tools as detailed in the last chapter, you should have SSMS installed and showing in the Program Menu under whichever version of SQL Server you have installed. Just click on the program to open it.
Connecting to a SQL Server
Management Studio is able to connect to both local instances of SQL Server that are installed on your system and remote instances installed on other servers. I use it to connect over the Internet to the SQL Server instance that provides data to websites as well as my local SQL Server instance.
When you first open SSMS, it will show you the login screen which enables you to select the server you want to connect to. As you add servers, the connection information will be saved and you will be able to select that information for
As you can see by the screenshot, I’m using the 2008 version of SQL Server for this demonstration. As of this writing, the latest version is SQL Server 2012 but the 2008 version is still available for download and is the last version to support Windows XP. Unless otherwise specified, everything in this article applies to both the 2008 and 2012 versions.
In the screenshot above, the server name is the computer name followed by the SQL Server instance name. In SQL Server Express, this is true for both default and named instances of SQL Server except that the default instance will be named SQLEXPRESS as shown here. I have this installation setup for Windows Authentication mode so I’m automatically logged in with my Windows user name and password. All I have to do is hit Connect.
(Click image for full-size view.)
Once SSMS connects to the server, you should see a screen like the one shown in the above screenshot. The Object Explorer on the left of the screen shows all the database objects on the server as well as other management tools for the SQL Server instance itself. In this shot, I have a few of the items expanded, including a custom database that I created called Contacts and a single table called MainContacts I’d recommend taking some time to explore the various objects within the Object Explorer by right-clicking on them and seeing the various options presented to you.
(Article continues after ad)
Creating Your First Database
For all the capabilities of SQL Server, creating a database is actually simple although there are quite a few options and settings that you can use to administer it properly. To create a new database, simply right-click on the Databases item in the Object Explorer and select New Database. SSMS will open the dialog window in the below screenshot with all the options for naming and creating a new database. The only thing you really have to provide is a name. The rest of the options can be safely left at the defaults but again, it’s a good idea to explore them.
(Click image for full-size view.)
The database files section under the General page enables you to specify the initial size of the database and how SQL Server will manage its growth. You can also specify where the database will be located although it’s best to leave it in the default location unless you have a specific reason for putting it elsewhere.
The second Options page contains more lower level settings, many of which probably won’t mean much to you until you’re very familiar with SQL Server but it wouldn’t hurt to learn more about them now. SQL Server uses MSDN as a help resource so, as long as you have an Internet connection, you can just hit F1 on any screen to find out more.
When creating a database in this way, there is a Management Studio issue that you might run into. If you’re using the new LocalDB version of SQL Server 2012, SSMS attempts to create the database under a hidden subdirectory of your Windows User directory and is denied access. A message box is displayed with SQL Error 5105 and the message “CREATE DATABASE failed”. Even if it could create the database file, you would have a hard time finding it. There are a couple of ways around this problem. The first is to use the options panel in the screenshot above to manually set the database directory. Under the Database Files list, use the scroll bar to scroll over to the Path setting and manually set it to another location on your hard drive.
The other option is to create the database using a query statement. To do this, select File –> New –> Query with Current Connection. This will open up a new query window where you can type SQL and T-SQL commands to communicate with the database. Type the following and then press the F5 button to run it.
CREATE DATABASE TestDB
This is the T-SQL statement to create a new database and SSMS will place the new database files under the user directory where it has access but will not bury them in the lower levels. After SSMS completes the task, it should show a message window underneath the script to confirm that it was successful. I will write more about T-SQL scripts in an upcoming chapter.
Once you’ve created the database, you’ll probably want to create a table or two in which to store your data. Again, it’s as simple as expanding the object view under your database, right-clicking on the Tables item and selecting New Table.
(Click image for full-size view.)
If you view the full-size version of the screenshot above, you’ll see that the table design environment is divided into three sections that take up the rest of the Management Studio interface. The section in the top center of the screenshot is the list of fields in the table. I’ve created an extremely simple table here with first and last names of contacts in a basic contact database. There are three columns for each field; the field’s name (column name), the data type and a checkbox indicating if the field can accept NULL values. SQL Server has a good selection of data types that you can choose from to accomodate different entries.
The Column Properties section under the field list enables you to change settings for a specific field including the data type and size and whether the field can be indexed. The properties that are available depend on the data type of the field On the right are the properties that apply to the entire table. You might not need to adjust many of these properties but, again, it’s a good idea to get a basic familiarity with them.
Once you’re finished designing a table, all you have to do is select the Save option from the File menu or click the Save icon on the toolbar. The program will ask you for a table name and will then create the table.
In future chapters, I will talk about ways of writing to and from the tables. The next chapter will look at the use of scripts to create a database and its objects.
(This article is updated from an article series previously published by Comeau Software Solutions.)