Using SQL Server

In this section, you'll explore some of the tools you use to manage SQL Server. Specifically, you'll learn how to start and stop SQL Server using the Service Manager and use the Enterprise Manager to administer SQL Server.

Starting and Stopping SQL Server

To start and stop SQL Server, you use the Service Manager tool. To open the Service Manager, you select Start ➣ Programs ➣ Microsoft SQL Server ➣ Service Manager. The Service Manager is shown in Figure 2.1.

Figure 2.1: The Service Manager

You select the name of the server computer on which SQL Server is running in the Server drop-down list box. To start SQL Server, you click the Start/Continue button. To stop SQL Server, you click the Stop button. You can also use the Service Manager to pause SQL Server, and select whether you want to automatically start SQL Server when the operating system (OS) starts.

Once you've started SQL Server, other programs can access the databases managed by that SQL Server installation.

Using Enterprise Manager

To administer a database, you use the Enterprise Manager tool. You can create databases, create and edit tables, create and edit users, and so on, using Enterprise Manager. To open the Enterprise Manager, you select Start ➣ Programs ➣ Microsoft SQL Server ➣ Enterprise Manager. The Enterprise Manager is shown in Figure 2.2.

Figure 2.2: The Enterprise Manager

On the left pane of Enterprise Manager, you'll see a tree that shows the accessible SQL Server installations. The contents of the right pane of Enterprise Manager display different information based on what you select in the left pane. For example, I selected the Databases folder and the North-wind database in the left pane when preparing Figure 2.2. As you can see, the right pane displays icons that allow you to edit the items stored in that database.

Each SQL Server installation contains the following seven folders shown in the left pane:

• Databases Contains tools that allow you to access the databases managed by SQL Server.

• Data Transformation Services Provides access to tools that allow you to move data from one database to another. You can also programmatically modify the data as it is moved. For example, you might want to move data from SQL Server database to an Oracle database, or vice versa.

• Management Contains tools that allow you to back up your databases, monitor current database activity, and other tasks.

• Replication Provides access tools that allow you to copy information from one database to another in near real time using a process known as replication. For example, you might want to move data from a database running at a branch office of a company to a database at headquarters.

• Security Contains tools that allow you to manage logins and built-in roles that contain permissions. You can also manage linked servers and remote servers. Linked servers are databases that you can access over a network. These databases don't have to be SQL Server databases; they could also be Oracle databases, for example. The only limitation is that there must be an OLE DB (Object Linking and Embedding for Databases) provider for that database. Remote servers are SQL Server databases that you can access over a network and run stored procedures on.

• Support Services Provides access to tools that allow you to manage the Distributed Transaction Coordinator, Full-Text Search, and SQL Mail services. The Distributed Transaction Coordinator service allows you to manage transactions that use more than one database. The Full Text Search service allows you to perform searches for phrases through large amounts of text. The SQL Mail service allows you to send electronic mail from SQL Server.

• Meta Data Services Contains tools that allow you to manage the information stored in the local repository. This information contains details about databases, users, tables, columns, views, stored procedures, and so on. This information is primarily used by data-warehousing applications.

Note 

Since this is a book on database programming, I won't cover too many details on database administration; I'll just focus on the Databases folder. Typically, your organization will have a database administrator, or DBA, who takes care of administering your databases and will use the other folders to perform their tasks. If you need more details on administering SQL Server, I recommend the book Mastering SQL Server 2000 by Mike Gunderloy and Joseph L. Jorden (Sybex, 2000).

Let's take a closer look at the Databases folder, which contains the databases that are managed by a particular SQL Server installation. For example, my SQL Server installation manages six databases named master, model, msdb, Northwind, pubs, and tempdb. When you expand the Databases folder for a database, you'll see the following nodes:

• Diagrams You use a diagram to store a visual representation of the tables in a database. For example, the Northwind database contains many tables, four of which are named Customers, Orders, Order Details, and Products. Figure 2.3 illustrates how these tables are related. The columns for each table are shown within each box in the diagram. For example, the Customers table contains 11 columns: CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, and Fax. As you'll learn in the "Table Relationships and Foreign Keys" section, the lines that connect the tables show the relationships between the various tables.

  
  Figure 2.3: The Customers, Orders, Order Details, and Products tables

• Tables You use a table to store rows that are divided into columns. Figure 2.4 shows a list of the tables stored in the Northwind database.

  
  Figure 2.4: The tables of the Northwind database

  You can create new tables, view the properties of a table, and query the rows stored in a table. You'll learn how to create a new table later in the "Creating a Table" section. To view the properties of a table, you select the table from the list in the right pane, click the right mouse button, and select Properties from the context-sensitive pop-up menu. You can also double-click the table to display the properties, and Figure 2.5 shows the properties of the Customers table. You'll learn the meaning of these properties as this chapter progresses.

  
  Figure 2.5: The Customers table properties

• Views You use a view to retrieve a set of columns from one or more tables. You can think of a view as a more flexible way of examining the rows stored in the tables. For example, one of the views of the Northwind database retrieves an alphabetical list of products, and retrieves the product name and the category name, among other columns. This information comes from both the Products and Categories tables. You can create new views, examine the properties of a view, and query the rows through a view. To examine the properties of a view, you select the view, click the right mouse button, and select Properties. You can also double-click the view to examine the Properties. Figure 2.6 shows the properties for the alphabetical list of products view. The text of the view is written in SQL, which you'll learn more about in Chapter 3, along with how to use the view in that chapter.

  
  Figure 2.6: The alphabetical list of products view properties

• Stored Procedures You use a stored procedure to run a sequence of statements in the database. In SQL Server, stored procedures are written in Transact-SQL, which you'll learn about in Chapter 4. Stored procedures are saved in the database, and are typically used when you need to perform a task that intensively uses the database, or you want to centralize a function in the database that any user can call rather than have each user write their own program to perform the same task. For example, one of the stored procedures in the Northwind database is named CustOrdHist, which returns the product name and the sum of the quantity of products ordered by a particular customer, who is passed as a parameter to the procedure. Figure 2.7 shows the properties for the CustOrdHist stored procedure.

  
  Figure 2.7: The CustOrdHist stored procedure properties

• Users Every time you access the database, you connect to a particular user account in the database. Every SQL Server database comes with two default users named dbo and guest. The dbo user owns the database and has the permissions to do anything in the database, such as create new tables, edit tables, and so on. The guest user has more limited permissions that allow access to the contents of the tables, but not the ability to create or edit tables, and so on. Figure 2.8 shows the properties of the dbo user. You'll notice that the dbo user has been granted two roles, public and db_owner. You'll learn about roles next. You can view all the permissions assigned to the dbo user by clicking the Permissions button.

  
  Figure 2.8: The dbo user properties

• Roles A role is a named set of permissions that you can assign to a user. It is useful when you need to assign the same set of permissions to more than one user. That way, if you need to change the set of permissions, you need to change only the permissions assigned to the role, rather than the permissions assigned to each user. For example, you saw in the previous figure that the dbo user has been granted the public and db_owner roles. Figure 2.9 shows the properties of the public role. You'll notice that the public role has also been granted to the guest user. If no public role was used, then the set of permissions would have to be added by hand to both the dbo and guest users.

  
  Figure 2.9: The public role properties

  You can view the permissions assigned to a role by clicking the Permissions button. Figure 2.10 shows the properties assigned to the public role, and Table 2.2 lists the meaning of the available permissions.

  Table 2.2: MEANING OF THE AVAILABLE PERMISSIONS

  PERMISSION	MEANING
  SELECT	Allows retrieval of rows from a table or view.
  INSERT	Allows addition of rows into a table or view.
  UPDATE	Allows modification of rows in a table or view.
  DELETE	Allows removal of rows from a table or view.
  EXEC	Allows execution of a stored procedure.
  DRI	Allows the addition or removal of declarative referential integrity (DRI) constraints to a table. The constraints ensure that proper actions are taken when adding, modifying, or removing foreign key values. Foreign keys specify that a column in one table is related to a column in another table. You'll learn more about foreign keys later in the "Table Relationships and Foreign Keys" section.

  
  Figure 2.10: The public role permissions

• Rules A rule is an expression that evaluates to either true or false and determines whether you are able to assign a particular value to a column. For example, you might define a rule that specifies a range of values, and if a supplied value is beyond that range, then you cannot assign that value to the column. Rules are provided for compatibility with older versions of SQL Server and are now replaced by constraints. You'll learn more about constrains in the "Creating a Constraint" section later.

• Defaults A default value is an initial value that is set when you add a new row to a table. Defaults are provided for compatibility with older versions of SQL Server and are now replaced by the default value of a column. You'll learn more about default values in the "Creating a Table" section later.

• User-Defined Data Types User-defined data types allow you to create your own types based on the existing SQL Server types. For example, say you wanted to store a United States ZIP code in several tables of your database; you could create a type that stores a string of five characters. If you then wanted to increase the length from five to eight to store an extended ZIP code, then all you need to do is modify your type and the change will be reflected in all the tables where you used that type.

• User-Defined Functions User-defined functions allow you to create your own functions. For example, say you wanted to compute the distance traveled by an object, then you could create a function to do that.

• Full-Text Catalogs Full-text catalogs allow you to create a full-text index, which enables you to perform searches for phrases through large amounts of text.

In the next chapter, you'll see that Visual Studio .NET's Server Explorer also allows you to use many of the same features contained in the Databases folder of Enterprise Manager. Specifically, Server Explorer allows you to view, create, and edit the following items: database diagrams, tables, views, stored procedures, and user-defined functions.

In the following section, you'll learn what is meant by the term relational in the context of a relational database, and you'll explore some of the tables in the Northwind database.