Appendix A: Questions and Answers

Part 1 of this book.

Chapter 1: Introduction to Microsoft SQL Server 2000 Review

page 44

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.

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

SQL

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.

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

SQL Server 2000 Enterprise Edition

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.

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

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

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

Query Analyzer

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

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)

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

Indexing support and querying support

What phases does a transaction go through?

A transaction goes through several phases:

12.1. Before the transaction starts, the database is in a consistent state.

12.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.

12.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.

12.4. When the application reaches a point where all the modifications have completed successfully and the database is once again consistent, application commits the transaction. This step makes all of the modifications a permanent part of the database.

12.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.