T-SQL & SQL Server DB
Practices Part 1
SQL Server Management Studio
Exercise 1 Exploring the Environment .......................................................................................................................... 2 Exercise 2 Creating a New Database ............................................................................................................................. 4 Exercise 3 Attaching a Database ................................................................................................................................... 5 Exercise 4 Exploring Database ...................................................................................................................................... 7 Exercise 5 Create Database Objects—Tables, Views .................................................................................................... 8
Exercise 1
Exploring the Environment
Scenario
In this exercise, you will become familiar with the new SQL Server Management Studio Tool.
SQL Server Management Studio is a new tool built for SQL Server 2005. It combines the functionality of the Enterprise Manager snap-in and the Query Analyzer. Although this is the main tool for administering one or more SQL Servers, you can also use the SQL Server Management Studio for executing queries and scripts, and for managing SQL Server projects.
The SQL Server Management Studio tool is based on the Microsoft Development Environment used in Visual Studio 2005 to create applications. If you are not already familiar with Visual Studio 2005, using the SQL Server Management Studio tool will help you learn to use the new Microsoft Development Environment for Visual Studio 2005. Tasks 1. Open SQL Server Management Studio and connect to your server. Detailed Steps 1. From the Windows task bar, select Start | All Programs | Microsoft SQL Server 2005 | SQL Server Management Studio. 2. When the Connect to Server dialog box appears, verify that Server type is set to Database Engine, Server name is set to localhost, and that Windows Authentication is selected as the authentication method. 3. Click the Options button to display additional connection options. 4. Click the Connection Properties tab. Note that the following options are available: ? You can configure the network protocol to use for this connection, which might be different than the protocol you use for other connections. ? You can configure a connection timeout, which controls how long to wait for the connection to be made. ? You can configure an execution timeout to specify how long to wait for response from a query. Note that you can export your server registration information, or import registration information from another server. This facility can be valuable for large organizations with many SQL Server administrators who all want to have the same servers registered. 5. Click Options again to hide the additional options tabs. 6. Click Connect. Note the various areas of the SQL Server Management Studio: The lower left pane is the Object Explorer, which appears as a tree view on the left side of SQL Server Management Studio. Above that is the Registered Servers pane, containing a list of servers to which Management Studio can connect. If the Registered Servers pane is not visible, click the View | Registered Servers menu. The right side of the SQL Server Management Studio contains the tools for managing projects. On the top right is the Solution Explorer. Below that is the Properties Window. If the Solution Explorer is not visible, you can choose to display it by selecting View | Solution Explorer. If the Properties window is not visible, you can also enable that window from the View menu, or by right-clicking on any object in the Solution Explorer window and choosing Properties Window. At the moment, the Solution Explorer is blank because no solution is currently loaded.
Tasks 2. Open new Query to execute a simple SQL clause. Detailed Steps If you close any of the windows, you can restore them from the View menu. 1. Open a new query window with File | New | Database Engine Query. When prompted, click Connect in the Connect to Database Engine dialog box. 2. The query window appears in the center portion of the SQL Server Management Studio, and is tabbed with the Summary Page. All additional query windows will be tabbed as well. 3. In the query window, enter the following code: EXEC sp_who 4. Press F5 or click the Execute button on the toolbar to execute the query. You will see the current active current users and processes in your SQL Server Database Engine. 3. Create a Management Studio Project and use a script file from a project. 1. Click File | New | Project, and choose SQL Server Scripts in the New Project dialog, type the project name “mySSMSProject”, and then click OK button, then a new Management Studio project is created. 2. You should see the mySSMSProject solution in the Solution Explorer window on the right side of the Management Studio. There is no files or connection now, it only has three folders, on the Queries folder, right click and select New Query, the Connect to Database Engine dialog will prompt, confirm your server name and authentication mode and then click Connect button to connect server. 3. Type the following SQL clauses: USE master SELECT SERVERPROPERTY('ServerName') SELECT SERVERPROPERTY('Edition') SELECT SERVERPROPERTY('ProductVersion') SELECT SERVERPROPERTY('ProductLevel') 4. To execute the batch, press F5 or click the Execute button on the toolbar. It will show the edition and version number of the SQL Server you are connected to. 5. Click File | Save All to Save your first SQL Server Management Studio project.
Exercise 2
Creating a New Database
Scenario
In this exercise, you will create a new database using Graphic interface or a SQL Server Management Studio template. You will then investigate the default properties of a new database. Tasks 1. Create a new database via Object Explorer 2. Create a new database using a TSQL Template. Detailed Steps 1. Right click the Databases folder in the Object Explorer window and choose New Database… item and then the New Database dialog pop up. 2. Type the new Database Name “NewDB”, and then Click OK button, a new database will be created. 1. Choose View | Template Explorer. 2. In the Template Explorer, expand the Database list. 3. Double click on create database. If prompted, connect to the database. A new query window will open, and you should see the following template: -- ============================================= -- Create database template -- ============================================= USE master GO -- Drop the database if it already exists IF EXISTS ( SELECT name FROM sys.databases WHERE name = N'
Tasks Detailed Steps 4. Explore the properties dialog box to see what properties exist for a newly created database. Because the Properties window is not modal, you can open a separate Properties window for each database, making it easier to compare the two. 5. Close the Properties window(s) when you’re done. Exercise 3
Attaching a Database
Scenario
In this exercise, you will attach the Northwind database using the “Attach Database” option in the SQL Server Management Studio Object Explorer.
Attaching a database means making all the database files available to your SQL Server, just as if you had created the database on the current server. Detaching a database allows you to move the physical files and reattach those files from a new physical location, or to make a copy of the files to use on another server (perhaps a test or development server).
Because the primary file contains the locations of all the other files in a database, if all the files are in their original location (as stored in the primary file) you only need to specify the primary file when attaching the database. SQL Server will read the information in the primary file and attach all the associated files for the database.
However, if the files are not all in the original location, the information stored in the primary file may not be
sufficient for SQL Server to find and attach all the database files. You will then need to specify the exact physical location of each database file that is not in the location it was when the database was detached.
When a database is detached, SQL Server will do a checkpoint in the database, so all the committed transactions are written to the disk files. Tasks 1. If the database is already attached, detach it. Detailed Steps 1. In the Object Explorer, expand localhost or the name of your server (if it's not already expanded), then expand the Databases folders. 2. If the Northwind database is in the list, right-click on it. Otherwise, skip to the Attach the Northwind database task below. 3. Point to Tasks, and then click Detach. 4. In the Detach Database dialog box, click OK. If you did not close the script Window from Exercise 1, you will not be able to Detach the database and will get an error. Close the Window and retry the Detach process. 1. Copy SQL2000SampleDb.msi from our training share folder T-SQL Part 2. Install it and default it would create folder in C:\\SQL Server 2000 Sample Databases path. 3. In the Object Explorer, right-click on the Databases folder, and click Attach. 4. In the Attach Databases dialog box, click Add. 5. Locate and select the following master file for the Northwind database ( C:\\SQL Server 2000 Sample Databases \\Northwind.mdf), and click OK. 6. Verify that there are two files listed in the Northwind database details section in the bottom half of the Attach Databases dialog box. 7. Click OK to attach the database. 1. Expand the Databases folder in the Object Explorer. 2. If the Northwind database doesn’t appear, right-click the Databases folder and select Refresh. 2. Attach the Northwind database. 3. Use the Object Explorer to Verify the Northwind Database.