Starting with SQL Server

We will be using Microsoft SQL Server 2008 running on iisqlsrv.cs.washington.edu. Accounts on it have been set up for each of you, and you have been given your user names and passwords. For the first project, you need to set up an interactive query session with the server. For this, you will need access to SQL Server Management Studio, which is installed on the machines in the undergrad labs. You will need it for both administrative tasks and interactive querying.

Before you can run queries on IISQLSRV, you need to register the server in Management Studio, and change your password:

  1. Go to Start -> All Programs -> Microsoft SQL Server 2008 -> SQL Server Management Studio.
  2. A box labeled "Connect to Server" will show up.
    1. In the box labeled SQL Server, enter iisqlsrv.cs.washington.edu.
    2. Select the Use SQL Server Authentication option.
    3. Enter your login and password.
    4. Click OK.
  3. This will connect you to the server, on the left side of the screen is an expanding tree.
  4. Expand the Security node of the tree, then the Logins node.
  5. Find your user name, right click and go to properties.
  6. Change your password then click OK (remember to check the 'Specify old password' checkbox and enter your old password.)

For the first project, you will use Management Studio to connect to the server, and run SQL queries. This will involve the following steps:

  1. Log into the server using Management Studio. (You should know how to do this if you changed your password.)
  2. To start a new query click New Query in the upper left.
  3. Type in USE imdb;, and press F5 to execute the statement. This will set your current database to be the imdb database, used in the first project.
  4. You can now enter and execute any SQL statements. Pressing F5 sequentially executes all the statements currently in the query pane, displaying the results in the results pane. If you highlight a piece of SQL code and press F5, only that code is executed.

That should be enough to get you started with Management Studio and SQL Server, but both tools have many more capabilities. The variant of SQL supported by SQL Server is called Transact-SQL (T-SQL); in addition to executing all Transact-SQL statements, Management Studio can also be used to view query execution plans, statistics, and more. To learn more about these features, explore the Query, Tools, and View menus of Management Studio, or read the Management Studio manual.

Note that in addition to read-only privileges on the imdb database, you also have your own database on IISQLSRV. This database's name is the same as your SQL Server username, so it can be accessed using the SQL statement USE your_sql_login;. You have full privileges to this database; feel free to use it for any project or homework assignment.

Warning: It is not possible to directly connect to SQL Server on IISQLSRV from outside the CSE network. If you plan to use Management Studio or connect to SQL Server from home, consider working on a CSE terminal server, such as aria.cs.washington.edu, or read the notes on SSH tunneling for SQL Server.