Appendix A -- Questions and Answers

3 4

Appendix A

Questions and Answers

Chapter 1 -- Introduction to Microsoft SQL Server 2000

Review

1. What is SQL Server 2000?

SQL Server 2000 is an RDBMS that uses Transact-SQL to send requests between a client computer and a SQL Server 2000 computer. An RDBMS includes databases, the database engine, and the applications necessary to manage the data and the components of the RDBMS. The RDBMS organizes data into related rows and columns within the database.

2. What language is commonly used to work with data in a database?

SQL

3. What is XML?

XML is a standard format for data on the Internet. XML consists of tags within a text document that define the structure of the document. XML documents can be easily processed through HTML. Although most SQL statements return their results in a relational (tabular) result set, the SQL Server 2000 database component supports a FOR XML clause that causes the results to be returned as an XML document. SQL Server 2000 also supports XPath queries from Internet and intranet applications.

4. Which edition of SQL Server 2000 includes the complete SQL Server offering?

SQL Server 2000 Enterprise Edition

5. What is the purpose of the SQL Server 2000 relational database engine?

The SQL Server 2000 relational database engine is a modern, highly scalable engine for storing data. The database engine stores data in tables. Applications submit SQL statements to the database engine, which returns the results to the application in the form of a tabular result set. Internet applications submit either SQL statements or XPath queries to the database engine, which returns the results in the form of an XML document. The relational database engine provides support for common Microsoft data access interfaces, such as ADOs, OLE DB, and ODBC.

6. What SQL Server 2000 technology helps you build data warehouses and data marts in SQL Server by importing and transferring data from multiple heterogeneous sources?

DTS

7. What are at least four administrative tasks that you can use the Enterprise Manager to perform?

Any four of the following tasks:

Defining groups of servers running SQL Server

Registering individual servers in a group

Configuring all SQL Server options for each registered server

Creating and administering all SQL Server databases, objects, logins, users, and permissions in each registered server

Defining and executing all SQL Server administrative tasks on each registered server

Designing and testing SQL statements, batches, and scripts interactively by invoking Query Analyzer

Invoking the various wizards defined for SQL Server

8. Which tool is commonly used to create queries and execute them against SQL Server databases?

Query Analyzer

9. What are at least five objects that can be included in a logical database?

Any five of the following:

Table

Data type

View

Stored procedure

Function

Index

Constraint

Rule

Default

Trigger

10. What are the major components involved in processing a SQL statement received from a SQL Server client?

The client, the tabular data stream, the server Net-Library, and SQL Server (the relational database engine)

11. What two roles does Microsoft Search play in supporting SQL Server?

Indexing support and querying support

12. What phases does a transaction go through?

A transaction goes through several phases:

1. Before the transaction starts, the database is in a consistent state.
2. The application signals the start of a transaction. This process can be initiated explicitly with the BEGIN TRANSACTION statement. Alternatively, the application can set options to run in implicit transaction mode; the first Transact-SQL statement executed after the completion of a prior transaction starts a new transaction automatically. No record is written to the log when the transaction starts; the first record is written to the log when the application generates the first log record for a data modification.
3. The application starts modifying data. These modifications are made one table at a time. As a series of modifications are made, they might leave the database in a temporarily inconsistent intermediate state.
4. When the application reaches a point where all the modifications have completed successfully and the database is once again consistent, the application commits the transaction. This step makes all of the modifi-cations a permanent part of the database.
5. If the application encounters some error that prevents it from completing the transaction, it undoes, or rolls back, all the data modifications. This process returns the database to the point of consistency it was at before the transaction started.

Chapter 2 -- Using Transact-SQL on a SQL Server Database

Review

1. In which window in Query Analyzer can you enter and execute Transact-SQL statements?

The Editor pane of the Query window

2. How do you execute Transact-SQL statements and scripts in Query Analyzer?

You can execute a complete script or an individual Transact-SQL statement by creating or opening the script in the Editor pane and then pressing F5. To perform this task, no other statements can be entered into the Editor pane. If there are other statements, you must highlight the script or statements that you want to execute, then press F5.

3. What type of information is displayed on the Execution Plan tab, the Trace tab, and the Statistics tab?

The Execution Plan tab displays a graphical representation of the execution plan that is used to execute the current query. The Trace tab, like the Execution Plan tab, can assist you with analyzing your queries. The Trace tab displays server trace information about the event class, subclass, integer data, text data, database ID, duration, start time, reads and writes, and CPU usage. The Statistics tab provides detailed information about client-side statistics for execution of the query.

4. Which tool in Query Analyzer enables you to control and monitor the execution of stored procedures?

Transact-SQL debugger

5. What is Transact-SQL?

Transact-SQL is a language that contains the commands used to administer instances of SQL Server; to create and manage all objects in an instance of SQL Server; and to insert, retrieve, modify, and delete data in SQL Server tables. Transact-SQL is an extension of the language defined in the SQL standards published by ISO and ANSI.

6. What are the three types of Transact-SQL statements that SQL Server supports?

DDL, DCL, and DML

7. What type of Transact-SQL statement is the CREATE TABLE statement?

DDL

8. What Transact-SQL element is an object in batches and scripts that can hold a data value?

Variable

9. Which Transact-SQL statements do you use to create, modify, and delete a user-defined function?

CREATE FUNCTION, ALTER FUNCTION, and DROP FUNCTION

10. What are control-of-flow language elements?

Control-of-flow language elements control the flow of execution of Transact-SQL statements, statement blocks, and stored procedures. These words can be used in Transact-SQL statements, batches, and stored procedures. Without control-of-flow language, separate Transact-SQL statements are performed sequentially, as they occur. Control-of-flow language elements permit statements to be connected, related to each other, and made interdependent by using programming-like constructs.

Control-of-flow keywords are useful when you need to direct Transact-SQL to take some kind of action. For example, use a BEGIN...END pair of statements when including more than one Transact-SQL statement in a logical block. Use an IF...ELSE pair of statements when a certain statement or block of statements needs to be executed IF some condition is met, and another statement or block of statements should be executed if that condition is not met (the ELSE condition).

11. What are some of the methods that SQL Server 2000 supports for executing Transact-SQL statements?

You can execute single statements, or you can execute the statements as a batch (a group of one or more Transact-SQL statements). You can also execute Transact-SQL statements through stored procedures and triggers. In addition, you can use scripts to execute Transact-SQL statements.

12. What are the differences among batches, stored procedures, and triggers?

A batch is a group of one or more Transact-SQL statements sent at one time from an application to SQL Server for execution. SQL Server compiles the statements of a batch into a single executable unit, called an execution plan. The statements in the execution plan are then executed one at a time. A stored procedure is a group of Transact-SQL statements that is compiled one time and can then be executed many times. A trigger is a special type of stored procedure that a user does not call directly. When the trigger is created, it is defined to execute when a specific type of data modification is made against a specific table or column.

Chapter 3 -- Designing a SQL Server Database

Lesson 1: Introduction to Database Design

Exercise 1: Exploring the Basic Concepts of Database Design

To view the contents of a table

2. Review the columns and rows within the table.

What are the column names (attributes) in the Categories table, and how many rows of data are displayed?

CategoryID, CategoryName, Description, and Picture (eight rows of data are displayed, not counting the header)

To normalize a database design

2. Keeping in mind the table's design, apply the four basic rules that you should follow when designing a database. The rules are listed here for your convenience:
   • A table should have an identifier.
   • A table should store data for only a single type of entity.
   • A table should avoid nullable columns.
   • A table should not have repeating values or columns.

Which rule is being violated in the Customers table?

The table does not have an identifier.

3. Modify the table's design so that it adheres to the basic rules of normalization. Use your paper and pencil to draw the table and its data.

How should you modify the data?

Add a column that serves as an identifier. For example, you can add a column named CustID. For each row, add a unique value to the column so that each customer has a unique customer ID.

6. Modify the database design so that it adheres to the rules of normalization.

How should you modify the current design?

Ensure that a table exists in the database that lists the various products that the company sells. This table should include an identifier for each product. Create another table that matches customer IDs to product IDs to track which products each customer purchased.

9. Modify the database design so that it adheres to the rules of normalization.

How should you modify the current design?

Create a separate table for the products so that all product and manufacturing information is in a table separate from the Customers table. The products table should include an identifier for each product. Create another table that matches customer IDs to product IDs to track which products each customer purchased.

To view entity relationships in a database diagram

3. View the connector that links the Authors table to the TitleAuthor table and the connector that links the Titles table to the TitleAuthor table.

The connector indicates that a relationship exists between the tables. Notice that there is no connector between the Author table and the Titles table because no direct relationship exists between the tables.

At one end of the connector is a key, which indicates one. The other side of the connector is an infinity sign, which indicates many. These symbols tell you that a one-to-many relationship exists between the Authors table and the TitleAuthor table and that a one-to-many relationship exists between the Titles table and the TitleAuthor table.

What is the implied relationship between the Titles table and the Authors table?

Many-to-many

What type of table is the TitleAuthor table (in terms of the implied relationship between Titles and Authors)?

A junction table

Lesson 3: Identifying System Requirements

Exercise 2: Identifying the System Requirements for Your Database Design

To identify system goals

2. Write down the system goals that you can identify in the scenario.

What are those goals?

Centralize information so that it is easier and more efficient to manage inventory and track orders and sales; maintain a central repository of information about authors, customers, and employees; and provide employees with the ability to generate, track, and modify orders online.

3. Review each goal to determine whether it is measurable.

Which goals are measurable?

The goal of centralizing information so that it is easier and more efficient to manage inventory and track orders and sales is measurable in part. Once the database is implemented, information will be centralized, so this part of the goal is measurable. The goal of making it "easier and more efficient" is not measurable, however. To make it measurable, you must establish an initial measurement. You need to ask, "How much easier and how much more efficient?" Because you are implementing a relatively small database, it would not be practical and cost-effective to conduct an in-depth study of the current system. In this case, it is enough to know that the manager says that the current system is creating a problem—a problem that can be resolved, at least in part, by implementing the database.

The goal of maintaining a centralized repository of information about authors, customers, and employees is measurable because there is currently no centralized repository (and there will be once the database is implemented). The same is true of the goal of providing employees with the ability to generate, track, and modify orders online. Once the database is implemented, they will have this ability.

To identify the amount and type of data

1. Write down the categories of data that you can identify in this scenario.

What categories of data can you identify?

Books, authors, employees, customers, and orders

2. For each category of data that you identified in Step 1, write down the type of information that you should track for each category.

What types of information can you identify?

Category	Types of Information
Books	Title, authors, publisher, publication date, edition, cost, suggested retail price, condition of book, unique ID
Authors	First name, last name, year of birth, year of death, description of author
Employees	First name, last name, address, phone number, date of birth, hire date, position
Customers	First name, last name, phone number, mailing address, books purchased, purchase date
Orders	Books sold, customer, salesperson, amount of the sale, order date, delivery date, form of payment, order status

3. For each category of data that you identified in Step 1, write down the current amount of data for each category.

What is the volume of data for each category?

Books	3000
Authors	2500
Employees	12
Customers	2000
Orders	1000

4. For each category of data that you identified in Step 1, write down the expected growth pattern.

What is the growth pattern for each category?

Books	10 percent annually
Authors	10 percent annually
Employees	One employee annually
Customers	10 percent annually
Orders	10 percent annually

To identify how the data will be used

1. Write down the categories of users that you can identify in this scenario.

What are those categories of users?

Managers, sales staff

2. For each category of user that you identified in Step 1, write down the number of users.

What are the current number of users and the projected number of users in each category?

User Type	Current Amount	Projected Growth Pattern
Managers	2	No expected growth
Sales staff	10	One annually

Only two to four employees (including managers) are in the store at the same time and are potentially accessing and updating the database concurrently.

3. For each category of user that you identified in Step 1, write down the tasks that they will be performing.

What tasks will each type of user be performing?

User Type	Tasks
Managers	Maintaining inventory (book information); maintaining information about authors; maintaining employee information; tracking how many books and which books each employee has sold; maintaining customer information; tracking orders; maintaining a record of sales (based on orders); and generating and modifying orders
Sales staff	Accessing information about authors, books, customers, and orders; creating, tracking, and modifying orders

To identify business rules

1. Write down the business rules that you can identify in this scenario.

What are the business rules?

The database system should adhere to the following business rules:

Book information must include the title, author, cost, suggested retail price, rating, and the unique ID assigned to each book.

The publisher, publication date, and edition are not required.

The book publication date can be a year within the range of 1600 to 2099.

A book must be rated as superb, excellent, good, fair, poor, or damaged.

Each rating should have the capacity to include a two-sentence description, but the description is not required.

Authors can write more than one book.

More than one author can write one book.

Author information must include the author's last name.

Author information can include the author's year of birth and year of death, if applicable.

An author's description is no longer than two sentences.

The author's description is not required.

Managers must be able to access and modify employee information.

Employee information must include the first name, last name, address, telephone number, date of birth, hire date, and position.

An employee's position must be Manager, Assistant Manager, Full-Time Sales Clerk, or Part-Time Sales Clerk.

Each position should have the capacity to include a two-sentence description, but the description is not required.

An employee can hold only one position at a time.

Only managers can access or modify employee information.

Customer information must include a first name or a last name.

Customer information does not require a telephone number or mailing address. Which books customers purchased and on which dates are not required because not all customers have bought books.

Order information must include information about books sold, which customer bought the books, which employee sold the books, the amount of the sale, the date the order is placed, the form of payment, and the status of the order.

The order information must include the date of delivery or pickup once the order is completed.

The form of payment must be cash, check, or credit card.

The order status must be one of the following: (1) to be shipped, (2) customer will pick up, (3) shipped, or (4) picked up.

Orders can contain only one customer, salesperson, order data, delivery date, form of payment, and order status.

Orders can contain one or more books.

When a book is added to an order, it is marked as sold in the inventory.

All employees can access information about authors, books in stock, customers, and orders.

All employees can create, view, and modify orders online.

Only managers can modify information about authors, books, and customers.

Lesson 4: Developing a Logical Data Model

Exercise 3: Developing a Logical Data Model

To identify relationships between entities

4. Identify any many-to-many relationships in the database design.

Which relationship is many-to-many?

The relationship between the Authors table and the Books table is a many-to-many relationship. Many authors can co-author one book; one author can write many books; and many authors can co-author many books.

To identify constraints on data

3. Identify the object, if any, to which this business rule applies.

To which object or objects does this business rule apply?

To the Books and BookAuthors tables

4. Under the Books table name and the BookAuthors table name, write the data constraints that you can derive from the business rule.

What are the data constraints?

The following constraints apply to the Books table:

The TitleID column must contain a value.

The Title column must contain a value.

The Cost column must contain a value.

The SRP column must contain a value.

The ConditionID column must contain a value.

The value in the TitleID column must be unique.

The following constraints apply to the BookAuthors table:

The AuthorID column must contain a value.

The TitleID column must contain a value.

5. For each business rule, define the data constraints. Where applicable, write the constraints beneath the table name. If a constraint does not apply specifically to one table, write it in another space on your paper.

What are the data constraints for your database design?

The following constraints apply to the database design:

Books table

The TitleID column must contain a value.

The value in the TitleID column must be unique.

The Title column must contain a value.

The Title column must be able to contain a value for a long title (about one sentence).

The Publisher column does not require a value.

The PubDate column does not require a value.

The Edition column does not require a value.

The Cost column must contain a value.

The SRP column must contain a value.

The ConditionID column must contain a value.

The Condition ID column must contain a value that is listed in the ConditionID column of the BookCondition table.

The Sold column must indicate whether a book is sold.

The PubDate column does not require a value. If a value is added, it must fall within the range of 1600 to 2099.

BookAuthors table

The AuthorID column must contain a value.

The AuthorID column must contain a value that is listed in the AuthorID column of the Authors table.

The TitleID column must contain a value.

The TitleID column must contain a value that is listed in the TitleID column of the Books table.

The combination of values in the AuthorID column and the TitleID column must be unique.

BookCondition table

The ConditionID column must contain a value.

The value in the ConditionID column must be unique.

The ConditionName column must contain a value.

The value in the ConditionName column must be unique.

The initial values to be used in the ConditionName column are Superb, Excellent, Good, Fair, Poor, and Damaged.

The Description column does not require a value.

The Description column must be able to contain a two-sentence value.

Authors table

The AuthorID column must contain a value.

The value in the AuthorID column must be unique.

The FirstName column does not require a value.

The LastName column must contain a value.

The YearBorn column does not require a value.

The YearDied column does not require a value.

The Description column does not require a value.

The Description column must be able to contain a two-sentence value.

Orders table

The OrderID column must contain a value.

The value in the OrderID column must be unique.

The CustomerID column must contain a value.

The CustomerID column must contain a value that is listed in the CustomerID column of the Customers table.

The EmployeeID column must contain a value.

The EmployeeID column must contain a value that is listed in the EmployeeID column of the Employees table.

The Amount column must contain a value.

The value in the Amount column must be in United States currency.

The OrderDate column must contain a value.

The DeliveryDate column must contain a value when the StatusID column contains a value that is equivalent to a shipped value or to a picked up value in the StatusID column of the Orders table.

The PaymentID column must contain a value.

The PaymentID column must contain a value that is listed in the PaymentID column of the FormOfPayment table.

The StatusID column must contain a value.

The StatusID column must contain a value that is listed in the StatusID column of the OrderStatus table.

The StatusID column must contain a value that is equivalent to a shipped value or to a picked up value in the StatusID column of the Orders table if a value is added to the DeliveryDate column.

BookOrders table

The OrderID column must contain a value.

The OrderID column must contain a value that is listed in the OrderID column of the Orders table.

The TitleID column must contain a value.

The TitleID column must contain a value that is listed in the TitleID column of the Books table.

The combination of values in the OrderID column and the TitleID column must be unique.

OrderStatus table

The StatusID column must contain a value.

The value in the StatusID column must be unique.

The StatusDescrip column must contain a value.

The value in the StatusDescrip column must be unique.

The initial values to be used in the StatusDescrip column are To be shipped, Customer will pick up, Shipped, and Picked up.

FormOfPayment table

The PaymentID column must contain a value.

The value in the PaymentID column must be unique.

The PaymentDescrip column must contain a value.

The value in the PaymentDescrip column must be unique.

The initial values to be used in the PaymentDescrip column are Cash, Check, and Credit card.

Employees table

The EmployeeID column must contain a value.

The value in the EmployeeID column must be unique.

The FirstName column must contain a value.

The LastName column must contain a value.

The Address1 column must contain a value.

The Address2 column does not require a value.

The City column must contain a value.

The State column must contain a value.

The Zip column must contain a value.

The Phone column must contain a value.

The DOB column must contain a value.

The HireDate column must contain a value.

The PositionID column must contain a value.

The PositionID column must contain a value that is listed in the PositionID column of the Positions table.

Customers table

The CustomerID column must contain a value.

The value in the CustomerID column must be unique.

The FirstName column or the LastName column or both columns must contain values.

The Phone column does not require a value.

The Address1 column does not require a value.

The Address2 column does not require a value.

The City column does not require a value.

The State column does not require a value.

The Zip column does not require a value.

Positions table

The PositionID column must contain a value.

The value in the PositionID column must be unique.

The Title column must contain a value.

The value in the Title column must be unique.

The initial values to be used in the Title column are Manager, Assistant Manager, Fulltime Sales Clerk, and Part Time Sales Clerk

The JobDescrip column does not require a value.

The JobDescrip column must be able to contain a two-sentence value.

Multiple tables

When a row is added to the BookOrders table, the value for the Sold column in the Books table is updated to reflect that the book in the order has been sold.

Security

Managers can view and modify data in all tables.

All employees can view and modify data in the BookOrders and Orders tables.

All employees can access data in the Authors table, Book Authors table, Books table, BookCondition table, BookOrders table, Customer table, Orders table, OrderStatus table, and FormOfPayment table.

Review

1. What does a SQL Server database consist of?

A SQL Server database consists of a collection of tables that store specific sets of structured data. A table (entity) contains a collection of rows (tuples) and columns (attributes). Each column in the table is designed to store a certain type of information (for example, dates, names, dollar amounts, or numbers).

2. What is normalization?

The process of using formal methods to separate the data into multiple related tables

3. What are the four basic rules that you should follow when normalizing a database design?

A table should have an identifier, store data for only a single type of entity, avoid nullable columns, and not have repeating values or columns.

4. What are the three basic types of relationships that can exist between tables in the SQL Server database, and what are the basic differences between these types?

The three types of relationships are one-to-one, one-to-many, and many-to-many. In a one-to-one relationship, a row in table A can have no more than one matching row in table B (and vice versa). In a one-to-many relationship, table A can have many matching rows in table B, but a row in table B can have only one matching row in table A. In a many-to-many relationship, a row in table A can have many matching rows in table B (and vice versa).

5. What are the three types of operating system files that SQL Server uses?

Primary, secondary, and transaction log

6. What two stages of security does a user pass through when working in SQL Server, and how do these stages differ?

A user passes through authentication and authorization (permissions validation). The authentication stage identifies the user who is using a login account and verifies only the capability to connect to an instance of SQL Server. If authentication is successful, the user connects to an instance of SQL Server. The user then needs permissions to access databases on the server. This process is done by granting access to an account in each database (mapped to the user login). The permissions validation stage controls the activities that the user is allowed to perform in the SQL Server database.

7. What are the four primary tasks that you should perform when identifying the system requirements for a database design?

Identifying system goals, identifying the amount and types of data, identifying how the data will be used, and identifying business rules

8. When determining the volume of data that the system will manage, you should identify what two types of information?

The actual amount of data and its growth pattern

9. When determining how data will be used in a new system, you should identify what three types of information?

Who will be using the data, the number of users who will be accessing the data, and the tasks that they will be performing when they access that data

10. When you are gathering system requirements for a database design, one of the steps that you should take is to define the specific categories of data. What type of object within a database maps to these categories of information?

A table object

11. What task do you perform after determining that a relationship exists between two tables, and how do you perform that task?

Determine the types of relationships that exist between tables. To determine the types of relationships, you should look at the types of data that each table contains and the types of interchanges between them.

12. What information within the system requirements should you base data constraints upon?

Business rules

Chapter 4 -- Implementing SQL Server Databases and Tables

Lesson 2: Identifying Data Types

Exercise 2: Identifying Column Data Types

To identify the data types for the Authors table

2. Refer to the data constraints that you identified for the Authors table when you developed your database design.

Which data constraints apply to the AuthorID column of the Authors table?

The column must contain a value, and the value must be unique.

4. Review the database design and the data constraints for the FirstName and LastName columns.

What type of data will you store in this column?

Character data

5. Review the database design and the data constraints for the YearBorn and YearDied columns.

You can assume that each column will contain only four characters. Because date and time data types do not include a year-only data type, you decide to use a character data type.

Which data type should you use for the YearBorn and YearDied columns?

char(4), although which data type to use is not always a clear-cut decision

6. Review the database design and the data constraints for the Description column.

What type of data will you store in this column?

Character data

To identify the column data types for tables in the BookShopDB database

3. Identify the data type for each column.

What is the data type for each column in the BookShopDB tables?

NOTE

---

It is sometimes difficult to predict exactly what length you should use for data types such as char and varchar. You can get a feel for lengths, however, by reviewing column properties in existing databases, such as the Pubs database or the Northwind database.

The following table lists the suggested data types for each column in the tables:

Table	Column	Data Type
Books	TitleID	char(8)
	Title	varchar(70)
	Publisher	varchar(50)
	PubDate	char(4)
	Edition	char(4)
	Cost	money
	SRP	money
	ConditionID	tinyint
	Sold	bit
BookCondition	ConditionID	tinyint
	ConditionName	char(10)
	Description	varchar(50)
Authors	AuthorID	smallint
	FirstName	varchar(30)
	LastName	varchar(30)
	YearBorn	char(4)
	YearDied	char(4)
	Description	varchar(200)
BookAuthors	AuthorID	smallint
	TitleID	char(8)
Employees	EmployeeID	smallint
	FirstName	varchar(30)
	LastName	varchar(30)
	Address1	varchar(60)
	Address2	varchar(60)
	City	varchar(15)
	State	char(2)
	Zip	varchar(12)
	Phone	varchar(24)
	DOB	datetime
	HireDate	datetime
	PositionID	tinyint
Positions	PositionID	tinyint
	Title	varchar(30)
	JobDescrip	varchar(80)
Customers	CustomerID	smallint
	FirstName	varchar(30)
	LastName	varchar(30)
	Phone	varchar(24)
	Address1	varchar(60)
	Address2	varchar(60)
	City	varchar(15)
	State	varchar(7)
	Zip	varchar(12)
Orders	OrderID	smallint
	CustomerID	smallint
	EmployeeID	smallint
	Amount	money
	OrderDate	datetime
	DeliveryDate	datetime
	PaymentID	tinyint
	StatusID	tinyint
OrderStatus	StatusID	tinyint
	StatusDescrip	varchar(25)
FormOfPayment	PaymentID	tinyint
	PaymentDescrip	varchar(12)
BookOrders	OrderID	smallint
	BookID	char(8)

NOTE

---

Notice that the State column in the Customers table uses the varchar(7) data type rather than char(2), as in the Employees table. Because a value is not required for this column in the Customers table, a default value of "unknown" will be defined (rather than permitting a null value). Nullability and default values are discussed in more detail in Lesson 3.

Lesson 3: Creating and Managing Tables

Exercise 3: Creating and Managing Tables in a SQL Server Database

To create the remaining tables in the BookShopDB database

1. In the Editor pane of the Query window, enter and execute the Transact-SQL statements necessary to create the remaining tables in the BookShopDB database.

When creating the Transact-SQL statements, refer to the Transact-SQL reference in SQL Server Books Online as necessary. Also refer to the database design for the BookShopDB database that you created in Chapter 3 and the data constraints that you identified in that chapter.

What are the Transact-SQL statements that you created?

You should use the following Transact-SQL statements to create the remaining tables in the BookShopDB database:

 USE bookshopdb CREATE TABLE Books       (       TitleID CHAR(8) NOT NULL,       Title VARCHAR(70) NOT NULL,       Publisher VARCHAR(50) NOT NULL DEFAULT 'N/A',       PubDate CHAR(4) NOT NULL DEFAULT 'N/A',       Edition CHAR(4) NOT NULL DEFAULT 'N/A',       Cost MONEY NOT NULL,       SRP MONEY NOT NULL,       ConditionID TINYINT NOT NULL,       Sold BIT NOT NULL DEFAULT '0',       ) CREATE TABLE BookOrders       (       OrderID SMALLINT NOT NULL,       TitleID CHAR(8) NOT NULL        ) CREATE TABLE Positions       (       PositionID TINYINT NOT NULL,       Title VARCHAR(30) NOT NULL,       JobDescrip VARCHAR(80) NOT NULL DEFAULT 'N/A'       ) CREATE TABLE Employees       (       EmployeeID SMALLINT IDENTITY NOT NULL,       FirstName VARCHAR(30) NOT NULL,       LastName VARCHAR(30) NOT NULL,       Addrees1 VARCHAR(60) NOT NULL,       Address2 VARCHAR(60) NOT NULL DEFAULT 'N/A',       City VARCHAR(15) NOT NULL,       State CHAR(2) NOT NULL,       Zip VARCHAR(12) NOT NULL,       Phone VARCHAR(24) NOT NULL,       DOB DATETIME NOT NULL,       HireDate DATETIME NOT NULL,       PositionID TINYINT NOT NULL       ) CREATE TABLE Customers       (       CustomerID SMALLINT IDENTITY(10,1) NOT NULL,       FirstName VARCHAR(30) NOT NULL DEFAULT 'unknown',       LastName VARCHAR(30) NOT NULL DEFAULT 'unknown',       Phone VARCHAR(24) NOT NULL DEFAULT 'unknown',       Address1 VARCHAR(60) NOT NULL DEFAULT 'unknown',       Address2 VARCHAR(60) NOT NULL DEFAULT 'unknown',       City VARCHAR(15) NOT NULL DEFAULT 'unknown',       State VARCHAR(7) NOT NULL DEFAULT 'unknown',       Zip VARCHAR(12) NOT NULL DEFAULT 'unknown'       ) CREATE TABLE Orders       (       OrderID SMALLINT IDENTITY NOT NULL,       CustomerID SMALLINT NOT NULL,       EmployeeID SMALLINT NOT NULL,       Amount MONEY NOT NULL DEFAULT 0,       OrderDate DATETIME NOT NULL,       DeliveryDate DATETIME NULL,        PaymentID TINYINT NOT NULL,       StatusID TINYINT NOT NULL       ) CREATE TABLE OrderStatus       (       StatusID TINYINT NOT NULL,       StatusDescrip VARCHAR(25) NOT NULL        ) CREATE TABLE FormOfPayment       (       PaymentID TINYINT NOT NULL,       PaymentDescrip VARCHAR(12) NOT NULL       ) 

Review

1. What is the first step toward implementing the physical database?

Creating a database object

2. What factors should you take into consideration before creating a database?

You should take into consideration the following factors:

Permission to create a database defaults to members of the sysadmin and dbcreator fixed server roles, although permissions can be granted to other users.

The user who creates the database becomes the owner of the database.

A maximum of 32,767 databases can be created on a server.

The name of the database must follow the rules for identifiers.

3. What are the two steps that SQL Server uses when implementing a new database?

SQL Server implements a new database through the following two steps:

SQL Server uses a copy of the Model database to initialize the new database and its metadata.

SQL Server then fills the rest of the database with empty pages, except for pages that have internal data recording how the space is used in the database.

4. What methods can you use to create a SQL Server database object?

SQL Server provides several methods that you can use to create a database: the Transact-SQL CREATE DATABASE statement, the console tree in Enterprise Manager, and the Create Database wizard (which you can access through Enterprise Manager).

5. What is a data type?

A data type is an attribute that specifies the type of data (integer, character, monetary, and so on) that the object can hold. Each column in a SQL Server table has a related data type. Certain objects other than columns also have an associated data type.

6. What four attributes does a data type define for an object?

A data type defines the following four attributes:

The kind of data contained by the object  For example, the data might be character, integer, or binary.

The length of the stored value, or its size  The lengths of image, binary, and varbinary data types are defined in bytes. The length of any of the numeric data types is the number of bytes required to hold the number of digits allowed for that data type. The length of the character string and Unicode data types is defined in characters.

The precision of the number (numeric data types only)  The precision is the number of digits that the number can contain. For example, a smallint object can hold a maximum of five digits; therefore, it has a precision of five.

The scale of the number (numeric data types only)  The scale is the number of digits that can be stored to the right of the decimal point. For example, an int object cannot accept a decimal point and has a scale of zero. A money object can have a maximum of four digits to the right of the decimal point and has a scale of four.

7. What are the nine categories of data types that SQL Server supports?

Binary, character, date and time, decimal, floating point, integer, monetary, special, and Unicode

8. What are user-defined data types?

User-defined data types are data types that are based on the system data types in SQL Server 2000. User-defined data types can be used if several tables must store the same type of data in a column and if you must ensure that these columns have exactly the same data type, length, and nullability. For example, a user-defined data type named postal_code could be created based on the char data type.

9. What type of information, at a minimum, should a table definition include?

The table name, column names, data types (and lengths, if required), and whether a column accepts NULL values

10. What are you defining when you define column nullability in a table definition?

The nullability of a column determines whether the rows in the table can contain a null value for that column. A null value is not the same as zero, blank, or a zero-length character string such as " ". Null means that no entry has been made. The presence of null usually implies that the value is either unknown or undefined.

11. How do you define a default value for a column?

By defining a DEFAULT definition with the column definition of a CREATE TABLE statement; you can also add a default value to a column by modifying the column definition

12. Which property can you use in the column definition of a CREATE TABLE statement in order to automatically generate an identity number for each new row added to a table?

The IDENTITY property, which enables you to define the number for the first row inserted into the table (the seed value) and to define an increment to be added to the seed to determine successive identity numbers

Chapter 5 -- Implementing Data Integrity

Lesson 1: Introduction to Data Integrity

Exercise 1: Identifying the Properties Used to Ensure Data Integrity

To identify properties in the Employees table

4. Scroll through the result on the Grids tab. Identify the data types, nullability, DEFAULT definitions, IDENTITY property, indexes, and constraints.

What types of constraints have been defined for the Employees table?

CHECK, DEFAULT, FOREIGN KEY, and PRIMARY KEY

Which columns in the Employees table allow null values?

The minit column and the job_lvl column

To identify properties in the Publishers table

2. Scroll through the result on the Grids tab. Identify the data types, nullability, DEFAULT definitions, IDENTITY property, indexes, and constraints.

What types of constraints have been defined for the Publishers table?

CHECK, DEFAULT, and PRIMARY KEY

Which column in the Publishers table is the identity column?

No identity column has been defined for this table.

Which columns in the Publishers table have been defined with the char data type?

The pub_id column and the state column

To identify properties in the Titles table

2. Scroll through the result on the Grids tab. Identify the data types, nullability, DEFAULT definitions, IDENTITY property, indexes, and constraints.

Which column in the Publishers table has been defined with a user-defined data type?

The title_id column has been defined with the tid user-defined data type.

How many columns in the Titles table allow null values?

Six

Lesson 2: Implementing Integrity Constraints

Exercise 2: Adding Constraints to Existing Tables

To add a PRIMARY KEY constraint to the Authors table

1. Refer to the business rules and database design and identify which column (or columns) in the Authors table should be defined with a PRIMARY KEY constraint.

At this point in the database development process, it should be fairly obvious which column should be configured as the primary key. Remember that a PRIMARY KEY constraint is defined for a column (or columns) whose values uniquely identify each row in the table.

Which column (or columns) in the Authors table should be defined with a PRIMARY KEY constraint?

The AuthorID column

To add a PRIMARY KEY constraint to the BookAuthors table

1. Refer to the business rules and database design and identify which column (or columns) in the BookAuthors table should be defined with a PRIMARY KEY constraint.

Which column (or columns) in the BookAuthors table should be defined with a PRIMARY KEY constraint?

The AuthorID and TitleID columns

To add a PRIMARY KEY constraint to the remaining tables in the BookShopDB database

2. Use Query Analyzer to add a primary key to each remaining table in the BookShopDB database.

For the BookOrders table, the primary key should be created for the two columns in that table. For the other tables, use one identifier column for the primary key.

What Transact-SQL statements should you use to add the PRIMARY KEY constraints to the remaining tables?

Use the following Transact-SQL statements to add the PRIMARY KEY constraints:

 ALTER TABLE Books ADD CONSTRAINT books_pk PRIMARY KEY (TitleID) ALTER TABLE BookCondition ADD CONSTRAINT bookcondition_pk PRIMARY KEY (ConditionID) ALTER TABLE BookOrders ADD CONSTRAINT bookorders_pk PRIMARY KEY (OrderID, TitleID) ALTER TABLE Customers ADD CONSTRAINT customers_pk PRIMARY KEY (CustomerID) ALTER TABLE Orders ADD CONSTRAINT orders_pk PRIMARY KEY (OrderID) ALTER TABLE Employees ADD CONSTRAINT employees_pk PRIMARY KEY (EmployeeID) ALTER TABLE Positions ADD CONSTRAINT positions_pk PRIMARY KEY (PositionID) ALTER TABLE OrderStatus ADD CONSTRAINT orderstatus_pk PRIMARY KEY (StatusID) ALTER TABLE FormOfPayment ADD CONSTRAINT formofpayment_pk PRIMARY KEY (PaymentID) 

Each table in the BookShopDB database should now be defined with a PRIMARY KEY constraint.

To add FOREIGN KEY constraints to the BookAuthors table

1. Refer to the business rules and database design and identify which column (or columns) in the BookAuthors table should be defined with FOREIGN KEY constraints.

Remember that a FOREIGN KEY constraint establishes and enforces a link between two tables. By looking at the business rules and the database design, you can determine what these links should be.

Which column (or columns) in the BookAuthors table should be defined with a PRIMARY KEY constraint?

The AuthorID column should be defined with a FOREIGN KEY constraint that references the AuthorID column in the Authors table, and the TitleID column should be defined with a FOREIGN KEY constraint that references the TitleID column in the Books table.

To add FOREIGN KEY constraints to the Books, BookOrders, Orders, and Employees tables

2. Use Query Analyzer to add foreign keys to the Books, BookOrders, Orders, and Employees tables.

For the BookOrders table, you should add a FOREIGN KEY constraint to each column. For the Orders table, you should add a FOREIGN KEY constraint to each of the four columns that reference other tables. For the Books table and the Employees table, you should add only one FOREIGN KEY constraint per table.

What Transact-SQL statements should you use to add the FOREIGN KEY constraints to the Books, BookOrders, Orders, and Employees tables?

Use the following Transact-SQL statements to add the FOREIGN KEY constraints:

 ALTER TABLE Books ADD CONSTRAINT conditionid_fk FOREIGN KEY (ConditionID) REFERENCES BookCondition (ConditionID) ALTER TABLE BookOrders ADD CONSTRAINT orderid_fk FOREIGN KEY (OrderID) REFERENCES Orders (OrderID) ALTER TABLE BookOrders ADD CONSTRAINT titleid2_fk FOREIGN KEY (TitleID) REFERENCES Books (TitleID) ALTER TABLE Orders ADD CONSTRAINT customerid_fk FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID) ALTER TABLE Orders ADD CONSTRAINT employeeid_fk FOREIGN KEY (EmployeeID) REFERENCES Employees (EmployeeID) ALTER TABLE Orders ADD CONSTRAINT paymentid_fk FOREIGN KEY (PaymentID) REFERENCES FormOfPayment (PaymentID) ALTER TABLE Orders ADD CONSTRAINT statusid_fk FOREIGN KEY (StatusID) REFERENCES OrderStatus (StatusID) ALTER TABLE Employees ADD CONSTRAINT positionid_fk FOREIGN KEY (PositionID) REFERENCES Positions (PositionID) 

The appropriate tables in the BookShopDB database should now be defined with FOREIGN KEY constraints.

To add a CHECK constraint to the Customers table

1. Refer to the business rules and database design and identify which column (or columns) in the Customers table should be defined with a CHECK constraint.

Remember that a CHECK constraint enforces domain integrity by limiting the values that a column will accept.

Which column (or columns) in the Customers table should be defined with a CHECK constraint?

A CHECK constraint should be defined for the FirstName and LastName columns.

To add CHECK constraints to the Authors table and the Books table

1. Refer to the business rules and database design and identify which columns in the Authors table and the Books table should be defined with CHECK constraints.

Which columns in the Authors table and the Books table should be defined with CHECK constraints?

CHECK constraints should be defined for the YearBorn and YearDied columns in the Authors table and for the PubDate column in the Books table.

Review

1. What properties within a SQL Server database are used to enforce data integrity?

Data types, NOT NULL definitions, DEFAULT definitions, IDENTITY properties, constraints, rules, triggers, and indexes

2. What is the difference between a DEFAULT definition and a NOT NULL definition?

A DEFAULT definition specifies what values are used in a column if you do not specify a value for the column when inserting a row. A NOT NULL definition specifies that null values cannot be used in a column.

3. What are the advantages of using CHECK constraints rather than rules?

Rules are a backward-compatibility feature that performs some of the same functions as CHECK constraints. CHECK constraints are the preferred, standard way to restrict the values in a column. CHECK constraints are also more concise than rules; there can only be one rule applied to a column, but multiple CHECK constraints can be applied. CHECK constraints are specified as part of the CREATE TABLE statement, while rules are created as separate objects and are then bound to the column.

4. What four types of data integrity does SQL Server support?

Entity integrity, domain integrity, referential integrity, and user-defined integrity

5. What are the differences between entity integrity and domain integrity?

Entity integrity defines a row as a unique instance of an entity for a particular table. Entity integrity enforces the integrity of the identifier column or the primary key of a table (through indexes, UNIQUE constraints, PRIMARY KEY constraints, or IDENTITY properties). Domain integrity is the validity of entries for a given column. You can enforce domain integrity by restricting the type (through data types), the format (through CHECK constraints and rules), or the range of possible values (through FOREIGN KEY constraints, CHECK constraints, DEFAULT definitions, NOT NULL definitions, and rules).

6. Which type of integrity preserves the defined relationships between tables when records are entered or deleted?

Referential integrity

7. What types of constraints does SQL Server support?

SQL Server supports four main classes of constraints: PRIMARY KEY constraints, UNIQUE constraints, FOREIGN KEY constraints, and CHECK constraints. DEFAULT definitions and NOT NULL definitions are sometimes considered types of constraints. Transact-SQL treats DEFAULT definitions as a type of constraint.

8. How many PRIMARY KEY constraints can be included in a table definition?

One

9. When should you use a UNIQUE constraint rather than a PRIMARY KEY constraint?

When a column (or combination of columns) is not the primary key or when a column allows null values

10. What does SQL Server check for in the existing data when a PRIMARY KEY constraint is added to an existing column (or columns)?

Whether any null values or duplicate values exist in the data

11. What is a FOREIGN KEY constraint, and how is it created?

A FOREIGN KEY constraint is a column or a combination of columns used to establish and enforce a link between the data in two tables. A link is created between two tables by adding a column or columns to one of the tables and defining those columns with a FOREIGN KEY constraint. The columns will hold the primary key values from the second table. A table can contain multiple FOREIGN KEY constraints.

12. How does a CHECK constraint determine what values are valid?

CHECK constraints determine the valid values from a logical expression. You can create a CHECK constraint with any logical (Boolean) expression that returns TRUE or FALSE based on the logical operators.

Chapter 6 -- Accessing and Modifying Data

Lesson 3: Modifying Data in a SQL Server Database

Exercise 3: Modifying Data in a SQL Server Database

To use an INSERT...VALUES statement to add data to the Test1 table

3. Write a SELECT statement that enables you to view all of the data in the Test1 table.

What statement should you use?

You should use the following SELECT statement:

 SELECT * FROM Test1 

Review

1. What are the four primary properties that most SELECT statements describe in a result set?

Most SELECT statements describe the following four primary properties of a result set:

• The columns to be included in the result set
• The tables from which the result set data is retrieved
• The conditions that the rows in the source table must meet to qualify for the result set
• The ordering sequence of the rows in the result set

2. What are the main clauses of a SELECT statement?

The main clauses of a SELECT statement can be summarized as follows:

SELECT select_list

[INTO new_table_name]

FROM table_list

[WHERE search_conditions]

[GROUP BY group_by_list]

[HAVING search_conditions]

[ORDER BY order_list [ASC | DESC] ]

3. What are several keywords that you can use in a select list?

DISTINCT, TOP n, and AS

4. What type of objects can you specify in the FROM clause of a SELECT statement?

Tables, views, joins, and derived tables

5. What purpose does a join provide when used in a SELECT statement?

By using joins, you can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how SQL Server should use data from one table to select the rows in another table.

6. What are the differences between inner joins and outer joins?

Inner joins return rows only when there is at least one row from both tables that matches the join condition, eliminating the rows that do not match with a row from the other table. Outer joins, however, return all rows from at least one of the tables or views mentioned in the FROM clause (as long as these rows meet any WHERE or HAVING search conditions).

7. What is a subquery?

A subquery is a SELECT statement that returns a single value and is nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery. A subquery can be used anywhere an expression is allowed. A subquery is also called an inner query or inner select, while the statement containing a subquery is called an outer query or outer select.

8. What are the differences between a CUBE operator and a ROLLUP operator?

The ROLLUP operator generates a result set that is similar to the result sets generated by the CUBE operator. The differences between CUBE and ROLLUP are as follows:

• CUBE generates a result set showing aggregates for all combinations of values in the selected columns.
• ROLLUP generates a result set showing aggregates for a hierarchy of values in the selected columns.

9. For what types of columns can you not specify values in an INSERT statement?

Columns with an IDENTITY property, columns with a DEFAULT definition that uses the NEWID() function, and computed columns

10. What methods can you use to modify data in a SQL Server database?

The UPDATE statement, database APIs and cursors, and the UPDATETEXT statement

11. What are the major clauses contained in an UPDATE statement?

SET, WHERE, and FROM

12. Which statement should you use to delete all rows in a table without having the action logged?

The TRUNCATE TABLE statement

Chapter 7 -- Managing and Manipulating Data

Lesson 1: Importing and Exporting Data

Exercise 1: Importing and Exporting Data

To use BULK INSERT statements to import data into the OrderStatus andFormOfPayment tables

1. Use BULK INSERT statements to insert data from the FormOfPayment.txt file to the FormOfPayment table and from the OrderStatus.txt file to the OrderStatus table.

What statements should you use?

Use the following BULK INSERT statements:

 USE BookShopDB BULK INSERT FormOFPayment FROM 'c:\formofpayment.txt' WITH (DATAFILETYPE = 'CHAR') BULK INSERT OrderStatus FROM 'c:\orderstatus.txt' WITH (DATAFILETYPE = 'CHAR') 

To use BULK INSERT statements to import data into the Authors table, Books table, Customers table, and Employees table

1. Use BULK INSERT statements to insert data from the Authors.txt file into the Authors table, from the Books.txt file into the Books table, from the Customers.txt file into the Customers table, and from the Employees.txt file into the Employees table.

What statements should you use?

Use the following BULK INSERT statements:

 USE BookShopDB BULK INSERT Authors FROM 'c:\authors.txt' WITH (DATAFILETYPE = 'CHAR') BULK INSERT Books FROM 'c:\books.txt' WITH (DATAFILETYPE = 'CHAR') BULK INSERT Customers FROM 'c:\customers.txt' WITH (DATAFILETYPE = 'CHAR') BULK INSERT Employees FROM 'c:\employees.txt' WITH (DATAFILETYPE = 'CHAR') 

To use the bcp command prompt utility to import data into the BookAuthors table

1. Use the bcp utility to copy data from the BookAuthors.txt file into the BookAuthors table.

What bcp command should you use?

bcp bookshopdb..bookauthors in bookauthors.txt -c –T

Review

1. What are the differences between importing data and exporting data?

Importing data is the process of retrieving data from sources external to SQL Server (for example, an ASCII text file) and inserting it into SQL Server tables. Exporting data is the process of extracting data from an instance of SQL Server into some user-specified format (for example, copying the contents of a SQL Server table to a Microsoft Access database).

2. What tools are available to import data into or export data out of a SQL Server database?

The bcp command prompt utility, the BULK INSERT statement, and Data Transformation Services (DTS)

3. What tasks can you perform by using DTS?

You can extract, transform, and consolidate data from disparate sources into single or multiple destinations.

4. What data access technology is used by SQL Server to support distributed queries?

OLE DB, the Microsoft specification of an API for universal data access

5. What two methods can you use in distributed queries to reference heterogeneous OLE DB data sources?

Using linked server names or ad hoc computer names

6. What is a linked server?

A linked server is a virtual server that has been defined in SQL Server. The linked server definition includes all of the information needed to access an OLE DB data source. You can set up a linked server by using Enterprise Manager or by using the sp_addlinkedserver system stored procedure. The linked server definition contains all of the information needed to locate the OLE DB data source.

7. What functionality do cursors support in order to extend result processing?

Cursors extend result processing by supporting the following function-ality:

• Allowing positioning at specific rows of the result set
• Retrieving one row or block of rows from the current position in the result set
• Supporting data modifications to the rows at the current position in the result set
• Supporting different levels of visibility to changes made by other users to the data presented in the result set
• Providing Transact-SQL statements in scripts, stored procedures, and triggers access to the data in a result set

8. Which three types of cursor implementations does SQL Server support?

Transact-SQL server cursors, API server cursors, and client cursors

9. How do Transact-SQL cursors differ from API server cursors?

Transact-SQL Server cursors are based on the DECLARE CURSOR statement and are used mainly in Transact-SQL scripts, stored procedures, and triggers. Transact-SQL cursors are implemented on the server and are managed by Transact-SQL statements sent from the client to the server. They are also contained in batches, stored procedures, or triggers. API server cursors support the API cursor functions in OLE DB, ODBC, and DB-Library. API server cursors are implemented on the server. Each time a client application calls an API cursor function, the SQL Server OLE DB provider, ODBC driver, or DB-Library DLL transmits the request to the server for action against the API server cursor.

10. What features are included in SQL Server to support XML functionalities?

The following features support XML functionalities:

• The capability to access SQL Server through HTTP
• Support for XDR schemas and the capability to specify XPath queries against these schemas

• The capacity to retrieve and write XML data:
  • Retrieving XML data by using the SELECT statement and the FOR XML clause
  • Writing XML data by using the OPENXML rowset provider
  • Retrieving XML data by using the XPath query language

• Enhancements to the SQLOLEDB that enable XML documents to be set as command text and to return result sets as a stream

11. What does the FOR XML clause in a SELECT statement enable you to do?

The FOR XML clause enables you to execute SQL queries and to return the result in XML format.

12. What does the OPENXML function enable you to do?

The OPENXML function is a Transact-SQL keyword that provides a rowset over in-memory XML documents. OPENXML is a rowset provider similar to a table or a view. OPENXML enables access to XML data as if it were a relational rowset by providing a rowset view of the internal representation of an XML document. The records in the rowset can be stored in database tables (similar to the rowsets provided by tables and views).

Chapter 8 -- Implementing Stored Procedures

Lesson 1: Introduction to Stored Procedures

Exercise 1: Exploring Stored Procedures

To view system stored procedures in the Master database

6. How can you tell the difference between a system stored procedure and an extended stored procedure from the list of procedures that appears below the Stored Procedures node?

System stored procedures usually contain an sp_ prefix, while extended stored procedures usually contain the xp_ prefix. There are exceptions to this rule, however. You can verify the type of stored procedure by using the OBJECTPROPERTY function.

Lesson 2: Creating, Executing, Modifying, and Deleting Stored Procedures

Exercise 2: Working With Stored Procedures

To execute the stored procedure

2. In the previous example, EXEC was specified to run the stored procedure. Was it necessary to use this keyword?

No, it isn't necessary to specify EXEC or EXECUTE in this case, because the stored procedure to run is the first line in the Transact-SQL statement.

Review

1. You create a local temporary stored procedure and ask a colleague to execute the stored procedure. Your colleague claims that she cannot execute the stored procedure. Why can't she execute the stored procedure?

Local temporary stored procedures may only be executed by the creator of the storedprocedure from the connection used to create the procedure. When the connection terminates, the local temporary stored procedure is removed.

2. Why do complex stored procedures typically execute faster the second time they are run than the first time?

The first time the stored procedure is run, an execution plan is created and the stored procedure is compiled. Subsequent processing of the compiled stored procedure is faster because SQL Server does not recheck command syntax, re-create an execution plan, or recompile the procedure. SQL Server stores the execution plan for the procedure in its procedure cache. The cache is checked first before a new execution plan is created for the procedure.

3. What security features are available for stored procedures?

Database users can be given permission to execute a stored procedure without being granted permissions to access the database objects on which the stored procedure operates. A stored procedure can be encrypted when it is created or altered so that users cannot read the Transact-SQL commands in the stored procedure.

4. What function can you use to check the properties of a stored procedure and other objects in SQL Server?

The OBJECTPROPERTY function enables you to view various attributes of database objects (including stored procedures). The following example shows how to use OBJECTPROPERTY to check whether a procedure is extended:

 SELECT OBJECTPROPERTY(object_id('storedprocedurename'), 'IsExtendedProc') 

5. Why is it more efficient to modify a stored procedure by using the ALTER PROCEDURE keywords than it is to drop and re-create a stored procedure?

Modifying a procedure (rather than dropping and recreating it) is a time saver, because most of the stored procedure properties (such as permissions) are retained when a procedure is modified.

6. You set the current database to Northwind, and then you create a stored procedure named #Procedure01. You then check the Northwind database for the stored procedure, but it doesn't appear. You can run the stored procedure using Northwind as the current database. Why are you able to execute this stored procedure but it doesn't appear in the Northwind database?

Appending a stored procedure name with a # sign upon creation creates a local temporary stored procedure. This action instructs SQL Server to create the procedure in TempDB. Therefore, the stored procedure does not appear in the Northwind database. The procedure is available from any database without fully qualifying its name, provided that it executes from the connection that created it.

7. In what three ways is the RETURN statement used in a stored procedure?

First, it is commonly used to provide a return code for error handling. Second, if no integer is specified with the RETURN statement, it simply exits unconditionally from a stored procedure. Third, it can be used for purposes other than error handling. For example, in Exercise 3 you used the RETURN statement to provide the integer value provided by the @@IDENTITY function.

Chapter 9 -- Implementing Triggers

Review

1. You have applied constraints, an INSTEAD OF trigger, and three AFTER triggers to a table. A colleague tells you that there is no way to control trigger order for the table. Is he correct? Why or why not?

He is incorrect. INSTEAD OF triggers always fire before constraints are processed. Following constraint processing, the AFTER triggers fire. Because there are three AFTER triggers, you can be sure about their execution order by using sp_settriggerorder to define the first and last trigger to execute.

2. You need to make sure that when a primary key is updated in one table, all foreign key references to it are also updated. How should you accomplish this task?

Configure cascading referential integrity to the foreign key constraints so that updates to the primary key are propagated to the other tables.

3. Name four instances when triggers are appropriate.

Triggers are appropriate in the following instances:

• If using declarative data integrity methods does not meet the functional needs of the application
• If changes must cascade through related tables in the database
• If the database is denormalized and requires an automated way to update redundant data contained in multiple tables
• If a value in one table must be validated against a non-identical value in another table
• If customized messages and complex error handling are required

4. When a trigger fires, how does it track the changes that have been made to the modified table?

An INSERT or UPDATE trigger creates the Inserted (pseudo) table in memory. The Inserted table contains any inserted or updated data. The UPDATE trigger also creates the Deleted (pseudo) table, which contains the original data. A DELETE trigger also creates a Deleted (pseudo) table in memory. The Deleted table contains any deleted data. The transaction isn't committed until the trigger completes. Thus, the trigger can roll back the transaction.

5. Name a table deletion event that does not fire a DELETE trigger.

TRUNCATE TABLE does not fire a DELETE trigger because the transaction isn't logged. Logging the transaction is critical for trigger functions, because without it, there is no way for the trigger to track changes and roll back the transaction if necessary.

6. Name a system stored procedure and a function used to view the properties of a trigger.

The sp_helptrigger system stored procedure shows the properties of one or all triggers applied to a table or view. The OBJECTPROPERTY function is used to determine the properties of database objects (such as triggers). For example, the following code returns 1 if a trigger named Trigger01 is an INSTEAD OF trigger:

 SELECT OBJECTPROPERTY (OBJECT_ID(`trigger01'), `ExecIsInsteadOfTrigger') 

7. Using Transact-SQL language, what are two methods to stop a trigger from running?

You can use the ALTER TABLE statement to disable a trigger. For example, to disable a trigger named Trigger01 that is applied to a table named Table01, type the following:

 ALTER TABLE table01 DISABLE TRIGGER trigger01. 

A second option is to delete the trigger from the table by using the DROP TRIGGER statement.

8. Write a (COLUMNS_UPDATED()) clause that detects whether columns 10 and 11 are updated.

 IF ((SUBSTRING(COLUMNS_UPDATED(),2,1)=6))  PRINT 'Both columns 10 and 11 were updated.' 

9. Name three common database tasks accomplished with triggers.

Maintaining running totals and other computed values; creating audit records; invoking external actions; and implementing complex data integrity

10. What command can you use to prevent a trigger from displaying row count information to a calling application?

In the trigger, type the following:

 SET NOCOUNT ON 

There is no need to include SET NOCOUNT OFF before exiting the trigger, because system settings configured in a trigger are only in effect while the trigger is running.

11. What type of event creates both an Inserted and Deleted logical table?

An UPDATE event is the only type of event that creates both pseudo tables. The Inserted table contains the new value specified in the update, and the Deleted table contains the original value before the UPDATE runs.

12. Is it possible to instruct a trigger to display result sets and print messages?

Yes, it is possible to display result sets by using the SELECT statement and print messages to the screen by using the PRINT command. You shouldn't use SELECT and PRINT to return a result, however, unless you know that all applications that will modify tables in the database can handle the returned data.

Chapter 10 -- Implementing Views

Review

1. What is a view?

A view is a virtual table whose contents are defined by a query. Like a real table, a view consists of a set of named columns and rows of data. A view does not exist as a stored set of data values in a database, however. The rows and columns of data come from tables referenced in the query defining the view and are produced dynamically when the view is referenced. Views are generally used to focus, simplify, and customize each user's perception of the database. A view acts as a filter on the underlying tables referenced in the view. The query that defines the view can be from one or more tables or from other views in the current or other databases. Distributed queries can also be used to define views that use data from multiple heterogeneous sources.

2. What functions can a view be used to perform?

A view can be used to perform any or all of the following functions:

• Restricting a user to specific rows in a table
• Restricting a user to specific columns
• Joining columns from multiple tables so that they look like a single table
• Aggregating information instead of supplying details

3. For what scenarios can views be used?

Views can be used in a variety of ways to return data:

• To focus on specific data
• To simplify data manipulation
• To customize data
• To export and import data
• To combine partitioned data

4. What are at least five restrictions that you must adhere to when creating views?

You must adhere to the following restrictions:

• You can create views only in the current database. The tables and views referenced by the new view, however, can exist in other databases or even in other servers if the view is defined with distributed queries.
• View names must follow the rules for identifiers and must be unique for each user. Additionally, a view name must not be the same as any table owned by that user.
• You can build views on other views and on procedures that reference views. SQL Server 2000 enables views to be nested up to 32 levels.
• You cannot associate rules or DEFAULT definitions with views.
• You cannot associate AFTER triggers with views (only INSTEAD OF triggers).
• The query that defines the view cannot include the ORDER BY, COMPUTE, or COMPUTE BY clauses or the INTO keyword.
• You cannot define full-text index definitions for views.
• You cannot create temporary views, and you cannot create views on temporary tables.
• Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless the view is dropped or changed so that it no longer has schema binding. In addition, ALTER TABLE statements on tables that participate in views having schema binding will fail if these statements affect the view definition.
• You cannot issue full-text queries against a view, although a view definition can include a full-text query if the query references a table that has been configured for full-text indexing.

5. What two tools does SQL Server provide to create a view?

Enterprise Manager and the CREATE VIEW statement in Transact-SQL

6. Where are the result sets stored for standard views and indexed views?

For standard views, the result set is dynamically built when the view is called. For indexed views, the result set is stored in the database.

7. If a view is going to be indexed, what type of index must be the first index created on that view?

A unique clustered index

8. Which Transact-SQL statement (or statements) should you use to change the definition of a view or to delete that view from the database?

Use the ALTER VIEW statement to modify the view definition, and use the DROP VIEW statement to delete the view from the database.

9. What Transact-SQL statement should you use if you want to view all of the data in the AuthorNames view of the Northwind database?

Use the following Transact-SQL statement:

 USE Northwind SELECT * FROM AuthorNames 

10. Which Transact-SQL statement (or statements) should you use to insert, modify, and delete data through a view?

Use the INSERT statement to add data, the UPDATE statement to modify data, and the DELETE statement to delete data.

11. What conditions must a view meet before you can modify data through that view?

A view must meet the following conditions:

• The view contains at least one table in the FROM clause of the view definition; the view cannot be based solely on an expression.
• No aggregate functions (AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, or VARP) or GROUP BY, UNION, DISTINCT, or TOP clauses are used in the select list. Aggregate functions can be used within a subquery defined in the FROM clause, however, provided that the derived values generated by the aggregate functions are not modified.
• No derived columns are used in the select list. Derived columns are result set columns that are formed by anything other than a simple column reference.

12. When is a view considered to be an updateable partitioned view?

A view is considered an updateable partitioned view if the view is defined with a set of SELECT statements whose individual result sets are combined into one by using the UNION ALL statement. Each individual SELECT statement references one SQL Server base table. The table can be either a local table or a linked table referenced using a four-part name, the OPENROWSET function, or the OPENDATASOURCE function (you cannot use an OPENDATASOURCE or OPENROWSET function that specifies a pass-through query).

Chapter 11 -- Implementing Indexes

Lesson 1: Index Architecture

Exercise 1: Viewing Index Properties and Using an Index

To view index properties in the Northwind database

3. Which index represents the sort order of the Customers table?

The PK_Customers index is a clustered index. If a table contains a clustered index, the table's sort order is the sort order of the clustered index.

4. Does the Customers table contain a composite index?

No, the Customers table does not contain any composite indexes, because each index key on the Customers table is composed of a single column.

To run queries and examine the execution plan

6. Why did the Query Optimizer choose the City index rather than the PK_Customers index in this case?

The City nonclustered index is a covering index for this query. The index key of the City index is the City column. The leaf level of the B-tree for the nonclustered index is the index key of the clustered index, CustomerID.

Lesson 2: Index Creation and Administration

Exercise 2: Creating a Clustered Index

To create and test a nonclustered index

7. Why didn't the Query Optimizer use the clustered index Books_pk?

The TitleSoldStatus index is a covering index for the query. The index key contains the Title and Sold columns. The bookmark in the nonclustered index is TitleID because the Books_pk clustered index uses TitleID as its index key.

Review

1. Can a clustered index also be a unique index?

Yes, a unique index can be defined for both a clustered or nonclustered index. If you create a primary key constraint on a table that does not contain a clustered index, SQL Server automatically creates a clustered, unique index for the primary key constraint.

2. If you query a Customers table containing a clustered index on the primary key column, CustomerID, and a nonclustered index on the LastName column, is the nonclustered index a covering index for the following Transact-SQL statement?

 SELECT LastName, CustomerID FROM Customers WHERE LastName LIKE 'nej' 

Yes, the nonclustered index contains the LastName column as its index key, and the index pages at the leaf level of the B-tree use the clustered index key CustomerID. Therefore, the nonclustered index covers everything contained in the query.

3. Is a composite index key always part of a covering index?

No, a covering index could contain a single column. An index is considered covering when the result set of a query is provided entirely by the index. Therefore, a query of a single column is covered by an index that uses that column as its index key.

4. How does the global fill factor affect existing indexes and new indexes where a fill factor is not specified?

A global fill factor affects an index when it is created without the FILLFACTOR clause. If you change the global default fill factor from zero to another value, existing indexes are unaffected—but any new index that is created without the FILLFACTOR clause inherits the global fill factor setting.

5. How do you maintain a fill factor in existing indexes?

A fill factor is set when the index is created and the index fills from that point. For example, if you create an index with a fill factor of 70 percent, 30 percent of the index is empty to accommodate new index entries. As new records are inserted, the index pages grow to accommodate the new entries, and the index pages become less than 30 percent empty. Eventually, the index pages will split to accommodate additional entries. To avoid or reduce the frequency of page splits, re-create the index with the original fill factor by using CREATE INDEX and specifying the DROP_EXISTING and FILLFACTOR clauses. You can also use DBCC DBREINDEX to rebuild the index.

6. What type of index can assist the following query?

 SELECT productname FROM products WHERE productname LIKE '%tool'  

The Query Optimizer might choose an index containing the ProductName index key, but parsing the index will not significantly affect the speed at which records are returned for this type of query. A full-text index applied to the ProductName column will assist with this query. The Products table containing the ProductName key should have at least one unique index. The simplest way to ensure the presence of a unique index is by applying a primary key constraint to a column (or columns) in the table.

7. You create the following composite index:

 CREATE UNIQUE CLUSTERED INDEX index01 ON employees(socialsecuritynumber, lastname) 

Is Index01 ideal for the following query? Explain your answer.

 SELECT socialsecuritynumber, lastname FROM employees WHERE lastname = 'kaviani' 

No, it isn't. The index key starts with the Social Security number and then the last name, but the query searches on the lastname column to find a match. The query is covered by the index, but a covering index optimized for this query contains the LastName column as the first part of the index key.

8. Why is it unwise to create wide, clustered index keys?

A clustered index requires additional disk capacity because the index contains the table and the index key. Therefore, the wider the index key, the greater the disk capacity required to contain the index. In addition, nonclustered indexes on the same table or view use the clustered index key as their bookmarks. Therefore, wide clustered index keys create large nonclustered indexes.

9. Which index type, clustered or nonclustered, must be most carefully designed? Explain your answer.

A clustered index must be most carefully designed because the table is physically sorted by the clustered index key, and you can create only a single clustered index on a table or view. The clustered index is typically designed to accommodate the most common queries and is most effective for range queries. The sort order of the clustered index should represent the most common sort of characteristics specified by users. When the ORDER BY clause specified in a SELECT statement matches the sort order of the clustered index, the Query Optimizer does not need to perform a sort operation.

10. If a table contains a single, nonclustered index, what is its bookmark?

The bookmark of a nonclustered index is a RID when a clustered index does not exist on the table. The RID is a pointer to a table row in a heap, and it consists of a page number, file number, and slot number. A table without a clustered index is called a heap.

11. What is the default sort order for an index key?

An index key is sorted in ascending order unless you specify descending order (the DESC keyword).

12. You wish to create a nonclustered index on a view in SQL Server 2000 Enterprise Edition, but an error message is returned indicating that you cannot create a nonclustered index on the view. What is the most likely reason for this error message?

A unique, clustered index must exist on a view before you can create a nonclustered index on the view. The clustered index on a view is the result set returned by the view. Without the result set provided by the clustered index, there is nothing on which to create a nonclustered index.

Chapter 12 -- Managing SQL Server Transactions and Locks

Review

1. What three operations do transaction logs support?

Transaction logs support the following three operations:

• Recovery of individual transactions
• Recovery of all incomplete transactions when SQL Server is started
• Rolling a restored database forward to the point of failure

2. What events are recorded in a transaction log?

Which of the following events are recorded in a transaction log?

• The start and end of each transaction
• Every data modification (insert, update, or delete)
• Every extent allocation or deallocation
• The creation or dropping of a table or index

3. When are checkpoints created in a transaction log?

Checkpoints are created when the following events occur:

• When a CHECKPOINT statement is executed
• When an ALTER DATABASE statement is used to change a database option
• When an instance of SQL Server is stopped by executing a SHUTDOWN statement or by using the SQL Server Service Control Manager to stop the service running an instance of the database engine
• When an instance of SQL Server periodically generates automatic checkpoints in each database to reduce the amount of time the instance would take to recover the database

4. When does log truncation occur?

Log truncation occurs at the completion of any BACKUP LOG statement and occurs every time a checkpoint is processed (provided the database is using the simple recovery model).

5. What is a transaction?

A transaction is a sequence of operations performed as a single logical unit of work. To qualify as a transaction, a logical unit of work must exhibit four properties called the ACID properties (atomicity, consistency, isolation, and durability).

6. What three types of transactions does SQL Server support, and how do they differ?

The three types of transactions are explicit, autocommit, and implicit. An explicit transaction is one in which you explicitly define both the start and the end of the transaction. An autocommit transaction is the default transaction management mode of SQL Server. Every Transact-SQL statement is committed or rolled back when it is completed. When a connection is operating in implicit transaction mode, SQL Server automatically starts a new transaction after the current transaction is committed or rolled back. You do nothing to delineate the start of a transaction; instead, you only commit or roll back each transaction. Implicit transaction mode generates a continuous chain of transactions.

7. What Transact-SQL statement is used to mark the starting point of an explicit transaction?

BEGIN TRANSACTION

8. What two phases are used to manage the commit process in the distributed transaction?

The prepare phase and the commit phase

9. What are the differences between lost updates and uncommitted dependencies?

Lost updates occur when two or more transactions select the same row and then update the row based on the value originally selected. Each transaction is unaware of other transactions. The last update overwrites updates made by the other transactions, which results in lost data. Uncommitted dependency occurs when a second transaction selects a row that is being updated by another transaction. The second transaction is reading data that has not been committed yet and might be changed by the transaction updating the row.

10. What are the differences between optimistic concurrency and pessimistic concurrency?

Optimistic concurrency control works on the assumption that resource conflicts between multiple users are unlikely (but not impossible) and enables transactions to execute without locking any resources. Only when attempting to change data are resources checked to determine whether any conflicts have occurred. If a conflict occurs, the application must read the data and attempt the change again. Pessimistic concurrency control locks resources as they are required for the duration of a transaction. Unless deadlocks occur, a transaction is assured of successful completion.

11. What isolation levels does SQL Server support?

The following isolation levels are supported:

• Read uncommitted (the lowest level, at which transactions are isolated only enough to ensure that physically corrupt data is not read)
• Read committed (SQL Server default level)
• Repeatable read
• Serializable (the highest level, at which transactions are completely isolated from one another)

12. What guidelines should you follow to help minimize deadlocks?

You should adhere to the following guidelines:

• Access objects in the same order.
• Avoid user interaction in transactions.
• Keep transactions short and in one batch.
• Use a low isolation level.
• Use bound connections.

Chapter 13 -- Designing and Administering SQL Server 2000 Security

Lesson 2: Designing a Database Security Plan

Exercise 1: Designing Security for BookShopDB

To identify security requirements

2. Identify and write down the security requirements contained in the system requirements.
   • Managers must be able to access and modify employee information.
   • Only managers can access or modify employee information.
   • All employees can access information about authors, books in stock, customers, and orders.
   • All employees can create, track, and modify orders online.
   • Only managers can modify information about authors, books, and customers.

3. Identify and write down two security requirements that do not appear in the list extracted from the system requirements.

There are many right answers. The following list shows three possible items to include:

• Two employees must be able to run a backup of the database.
• Managers must be able to administer SQL Server security for all employees.
• A trusted developer who is not part of the company and one manager must have full access to the database.

To determine users, groups, and roles for security and create a user-to-activity map

1. Assuming that SQL Server is running in a Windows domain named BOOKSHOP, create a list of unique users, roles, and groups to accommodate the security requirements. List only the users, roles, or groups that you believe are required to accommodate the security requirements.
   • BOOKSHOP\Managers (All managers are members.)
   • BOOKSHOP\Users (All employees, including managers, are members.)
   • Public role (All authenticated users are members.)
   • BOOKSHOP\Staff01 and BOOKSHOP\Staff02 (The two users to be granted backup privileges for SQL Server use these Windows accounts.)
   • db_BackupOperator fixed database role (BOOKSHOP\Staff01 and BOOKSHOP\Staff02 are members.)
   • SecurityAdmin fixed server role (BOOKSHOP\Managers are members.)
   • DevUser (The outside developer uses this SQL Server login ID.)
   • BOOKSHOP\Manager01 (The manager to be granted special privileges for SQL Server uses this Windows account.)
   • SysAdmin fixed server role (BOOKSHOP\Manager01 and DevUser are members.)

2. Create a User-to-Activity Map that ties the security requirements to the unique users, roles, and groups that you defined in the previous step.

User Account, Group, or Role	Activity
Public (role)	Read-only access to all tables except for the Employees and Positions tables
Public (role)	Add, delete, and modify data in Orders, BookOrders, and OrderStatus tables
BOOKSHOP\Managers	Fully access all user tables
SecurityAdmin	Administer SQL Server security
db_BackupOperator	Run the database backup
SysAdmin (fixed role)	Administer SQL Server

Review

1. Which two layers of the security architecture outlined in Lesson 1 contain SQL Server-specific security features?

The network protocol and SQL Server security layers. The network protocol layer includes the SQL Server 2000 Client Network Utility. With this utility, you configure encryption for Net-Library protocols used between the client and the SQL Server. The SQL Server layer includes the following SQL Server-specific security features:

• Pass-through authentication (Windows authentication)
• SQL Server Authentication
• Object and statement permissions
• Special security accounts, such as sa and roles (like fixed roles, the public role, and application roles)
• Database object encryption
• Internal auditing

2. You specifically revoke the UPDATE permission from User01 for Table01 so that the user cannot update the table. After revoking the permission, User01 still can update Table01. Name three possible reasons for this result. Four possible reasons are included in the answers.
   • Revoking a permission does not necessarily deny a user's access to the permission. If User01 is a member of a group or a role that is granted the UPDATE permission, the user's effective right is the UPDATE permission.
   • You have removed the permission from Table01 in the wrong database. The majority of permission setting occurs within the context of a database and only applies to objects within the current database.
   • The Public role is assigned permissions to update Table01.
   • User01 has been removed from the database and the Guest account is permitted to update Table01.

3. You create a view and grant the Public role SELECT permission to the view. A user attempts to run the view but receives a permission conflict error message. What is the most likely reason for this result?

The ownership chain is broken. You own the view; another user owns the underlying table; and the user who is attempting to run the view does not have permissions to the underlying table. When an ownership chain is broken, SQL Server evaluates permissions on every object in the chain.

4. You use sp_revokelogin, but a Windows user is still able to authenticate to SQL Server. What is the most likely reason why the user can authenticate to SQL Server?

The user is a member of a group that has been granted the right to connect to SQL Server. The sp_revokelogin system stored procedure does not deny a user access to log in to SQL Server; rather, it simply removes the account specified in the @loginame input parameter.

5. A SQL Server computer is part of a workgroup. User01 on another Windows computer that is part of the same workgroup wants to log in to SQL Server. Name two ways that the user can connect to SQL Server.

There are a number of ways to connect:

• The user can log in with a SQL Server login ID.
• Create a Windows user account named User01 on the SQL Server computer. Make the password for User01 on the SQL Server the same as the User01 account on the client computer.
• Add the local User01 account to a Windows group that has been permitted to establish a connection with SQL Server.

6. You use sp_droplogin to delete a SQL Server login ID from SQL Server, but you receive an error message stating that the security account is mapped or aliased to a database. You then use sp_revokelogin to delete a Windows account from SQL Server. You know that the Windows user account is mapped to several databases, but the procedure succeeds. Explain why you can delete the Windows account but you cannot delete the SQL Server login ID.

You must run sp_revokedbaccess against all databases where a SQL Server login ID has been granted access. Then, the security account can be deleted from SQL Server by using sp_droplogin. When you delete a Windows account from SQL Server by using sp_revokelogin, SQL Server automatically removes the account from any databases where privileges have been assigned. In Enterprise Manager, you can delete any security account without first revoking database privileges.

7. Why is there no place in Enterprise Manager to make a Windows user account or SQL Server login ID a member of a Windows group?

Windows Group membership is assigned outside SQL Server by using Windows operating system tools such as User Manager, and only Windows accounts (users and groups) can be members of a Windows group. Therefore, you cannot assign a SQL Server login ID as a member of a Windows group.

8. You map the special Guest account to a database named DB01, but you don't grant the Guest account any privileges in the database. You then run SQL Profiler and notice that a user who is not mapped to DB01 deleted a table in the database. What is the most likely reason why the user could delete a table?

A user who is not mapped to a database can connect to the database as the Guest account if Guest is mapped to the database. The CREATE TABLE statement might have been assigned to the Public role, and because Guest is mapped to the database, Guest is a member of the Public role.

9. You use the DENY statement to explicitly deny User01 and DOMAIN01\IT Users the CREATE VIEW permission. What Transact-SQL statement do you use to clear the explicit denial?

Use the REVOKE statement to clear an explicit denial. The following REVOKE statement clears the denial described in the question:

 USE pubs REVOKE CREATE VIEW TO user01, [DOMAIN01\it users] 

10. Using Enterprise Manager, you attempt to modify the permissions of a standard database role, but the Permissions button is inactive. You are connected to the database as a member of the SysAdmin role. Why is the Permissions button unavailable?

You are attempting to modify permissions on a fixed database role. You cannot modify permissions assigned to either predefined role type (fixed server or fixed database).

11. What are the next important steps after implementing a security design?

Verifying the security design by connecting to the database with various security accounts and testing permissions and audit activity with SQL Profiler

12. Explain the purpose of an application role and how you activate it.

Application roles provide a method of controlling access to a database from within an application. When an application role is active, the user's privileges in the database are irrelevant until the session is ended. Use the sp_setapprole system stored procedure to activate an application role.

Chapter 14 -- SQL Server Monitoring and Tuning

Lesson 1: Monitoring Databases with SQL Profiler

Exercise 1: Capturing Events Using SQL Profiler

To analyze the trace data

3. Which statement in the batch required the most CPU time to execute?

 SELECT * FROM table01 WHERE uniqueid BETWEEN 5000 AND 10000 

This statement requires the most CPU time to execute. The amount of CPU time will vary depending on the speed of the processor(s) used for this exercise.

4. Which event required the most CPU time? Explain your answer.

The SQL:BatchCompleted event appears to take the most time to execute. However, if you sum the three Transact-SQL statements, you will see that the CPU time is equivalent to the SQL:BatchCompleted CPU time. Therefore, the event that required the most CPU time is the following statement:

 SELECT * FROM table01 WHERE uniqueid BETWEEN 5000 AND 10000 

If you regularly execute batches of SQL Statements, the SQL:BatchCompleted event is useful in determining the most expensive batch.

10. How does the trace output differ from the trace you created in the previous practice?

The SQL:BatchCompleted event appears after each Transact-SQL statement. If you sum the CPU time for the three Transact-SQL statements, you will see that placing a GO statement between each SQL statement is slightly less processor intensive. However, the processor isn't the only resource to consider. Placing a GO statement between each statement slightly increases network traffic.

Review

1. Name a monitoring feature that sends database information to a Network Management System (NMS).

SQL Server 2000 contains MIB files that can be loaded into an NMS so that the NMS can monitor various aspects of SQL Server activity.

2. Name a SQL Server tool you can use to monitor current SQL Server activity.

The Current Activity node of Enterprise Manager and SQL Profiler are two SQL Server tools that monitor current activity.

3. Several users inform you that database performance levels seem to change right around the time that staff rotations occur. How can you use SQL Profiler to determine if the staff rotation has anything to do with changing performance levels?

Run a trace several times a day as staff is rotated in and out. Create or use an existing template that groups activity by users. Analyze the activity to determine if there is a user running inappropriate or intensive queries.

4. You are concerned about database security. How can you use SQL Profiler to alleviate your concerns?

Create a trace that includes some or all of the event classes in the Security Audit collection. Run the trace continuously or intermittently depending on your security needs.

5. How can you reduce the amount of data collected by a trace?

Be selective about which event classes and data columns should be part of a trace. Further restrict the amount of data captured by applying data filters in the Filters tab of the Event Properties dialog box.

6. Where can trace data be stored?

When you create a trace you can instruct SQL Profiler to create a trace file or a trace table. If you don't select a trace table or trace file location, the trace will be lost when SQL Profiler is closed.

7. As you move through the Index Tuning wizard screens, you see that choosing a script from the Query Analyzer is not an option. What is the most likely reason for this result?

You did not open the Index Tuning wizard from Query Analyzer. The script option is available in the Index Tuning wizard only when the wizard is started from Query Analyzer.

8. How can you start a trace in Query Analyzer?

Use SQL Profiler to define the properties of a trace. Then, create a script from the Script Trace option below the File menu. You can create a script for either SQL Server 7.0 or SQL Server 2000. The script trace contains SQL Profiler stored procedures and input parameters necessary to create a trace. Load the script into the Query Analyzer, make some minor modifications as explained in the script file, and run the trace by executing the script.

9. What application requirement must be met for the application to benefit from a federation of servers?

The application must send requests for data or updates to the member server with the most data required to complete the statement.

10. How must member tables be configured to support distributed partitioned views?

Each member table has the same number of columns as the original table, and each column has the same attributes (such as data type, size, and collation) as the corresponding column in the original table.

11. What are two ways that CPU consumption can be reduced when performing an analysis with the Index Tuning wizard?

Any two of the following are acceptable for reducing CPU consumption: lower the level of analysis by selecting the Fast or Medium tuning modes; analyze a smaller workload and fewer tables; run the analysis against a test version of the production server; and run the wizard on a client computer instead of the SQL Server.

12. The Index Tuning wizard can create indexed views on what SQL Server editions?

SQL Server Enterprise Edition and SQL Server Developer's Edition support indexed views. Therefore, the Index Tuning wizard can create indexed views on these platforms. If you run the Index Tuning wizard on another edition of SQL Server, such as SQL Server Personal Edition, the Add Indexed Views checkbox is not available.