EXAM CRAM™ 2 Designing and Implementing Databases with SQL Server 2000 Enterprise Edition [Electronic resources] نسخه متنی

The Exam

You will find the 70-229 exam to be a thorough inquiry of SQL Server features and coding practices. There will be a wide assortment of material, from the outset of a project when it is sketched out on paper to the concluding deployment. Traditional database design practices, database structuring, and coding trade-offs coupled with the new features of the product make for an intense and challenging exam.

Knowing the New Features Found in SQL Server 2000

Many of the new features found in SQL Server 2000 are likely to be questioned on the 70-229 exam. SQL Server 2000 continues where version 7 left off. Many new features have been added to improve productivity and application diversity. You will find coverage of all the important techniques in the pages throughout this book.

Microsoft loves to have many exam questions about the new features. You can expect to have one or more exam questions for each feature in the New Features list. A complete inventory of these features can be found in Books Online, under What's New. We have broken down the complete list of new features in SQL Server 2000 and have focused on those you can expect on the exam.

The following list and the paragraphs after it highlight some of the critical new features:

Indexed views

New data types

INSTEAD OF and AFTER triggers

User-defined functions

Collation enhancements

Multiple instances of SQL Server

Federated database servers

Cascading referential integrity constraints

Updatable distributed partitioned views

XML support

XML Support

The relational database engine can return data as Extensible Markup Language (XML) documents. Additionally, XML can be used to insert, update, and delete values in the database. XML is covered fully in Chapter 5, "Retrieving and Modifying Data."

Distributed Partitioned Views

SQL Server 2000 supports distributed partitioned views that enable you to divide tables horizontally and place the data on multiple servers. A group of federated database servers can support the data storage requirements of the largest websites and enterprise systems. Partitioned views are covered fully in Chapter 4, "Advanced Physical Database Implementation."

Creating Your Own T-SQL Functions

You can extend the programmability of SQL Server by creating your own Transact-SQL (T-SQL) functions. T-SQL is an extension of the language defined by SQL standards and the basis for programming in SQL Server. A user-defined function can return either a scalar value or a table. User-defined functions (UDFs) are covered fully in Chapter 6, "Programming Business Logic."

Indexed Views

Indexed views can significantly improve the performance of an application when queries frequently perform certain joins or aggregations. An indexed view provides for a technique in which fast access to data is enabled (indexing) and associated with data display definitions (views), and where the resultset of the view is materialized and stored and indexed in the database. Indexed views are covered fully in Chapter 4.

New Data Types in SQL Server 2000

SQL Server 2000 introduces three new data typesbigint, sql_variant, and tablethat are supported for variables and are the return types for user-defined functions. Data types are covered in full in Chapter 3, "Implementing the Physical Database."

INSTEAD OF and AFTER Triggers

INSTEAD OF TRiggers are executed in place of the triggering action (for example, INSERT, UPDATE, DELETE). They can also be defined on views, in which case they greatly extend the types of updates a view can support. AFTER TRiggers fire after the triggering action. SQL Server 2000 introduces the capability to specify which AFTER triggers fire first and last. Triggers are covered fully in Chapter 6.

Cascading Referential Integrity

Cascading actions enable you to control the actions SQL Server 2000 takes when deleting or changing data. If you attempt to update or delete a key to which existing foreign keys point, cascading actions will dictate the effects on the associated records. This is controlled by the new ON DELETE and ON UPDATE clauses in the REFERENCES clause of the CREATE TABLE and ALTER TABLE statements. Cascading referential integrity is covered fully in Chapter 3.

Collations

SQL Server 2000 includes support for most collations supported in earlier versions of SQL Server, and it introduces a new set of collations based on Windows collations. You can now specify collations at the database level or at the column level. Collations are covered fully later in this chapter.

Connecting to Multiple Instances of SQL Server

SQL Server 2000 supports running multiple instances of the relational database engine on the same computer. Each computer can run one instance of the relational database engine from SQL Server version 6.5 or 7.0, along with one or more instances of the database engine from SQL Server 2000.

Partitioned Views

SQL Server 2000 can partition tables horizontally across several servers, and define a distributed partitioned view on each member server so that it appears as if a full copy of the original table is stored on each server. Groups of servers running SQL Server that cooperate in this type of partitioning are called federations of servers. Partitioned views are covered fully in Chapter 4.

Other Relevant Exam Topics

This exam will of course cover traditional relational database concepts and features that have been historically present in the product. With this in mind, you need a solid knowledge of and hands-on experience with all facets of SQL Server 2000 database implementations before attempting the exam.

Expect to see many questions dealing with the designing of a database. Choosing the right number and makeup of the tables and other objects within a database will be probed from various perspectives on the exam. Like new features, these more traditional topics will represent another significant portion of the exam topic material.

SQL Server Product Implementation

The exam is geared for the Enterprise version of Microsoft SQL Server 2000 edition when it is used as a production database server. The Enterprise Edition supports all features available in SQL Server 2000 and scales to the performance levels required to support the largest websites and enterprise online transaction processing (OLTP) and data warehousing systems.

You can obtain 120-day evaluation versions of the product as a download from Microsoft's product download page, http://www.microsoft.com/sql/evaluation/trial/2000.

The following hardware is the minimum needed to install the Enterprise version of SQL Server 2000:

Computer:

Intel or compatible

Pentium 166MHz or higher

Memory:

64MB minimum, 128MB or more recommended

Hard Disk Space:

SQL Server database components: 95270MB, 250MB typical

Analysis Services: 50MB minimum, 130MB typical

English Query: 80MB

Desktop Engine only: 44MB

Books Online: 15MB

Monitor:

VGA or higher resolution

800x600 or higher resolution required for the SQL Server graphical tools

Other Peripherals:

CD-ROM drive

Product installation is pretty straightforward, though installing it correctly for use in a production environment will necessitate changing many of the defaults. To obtain the best installation in a production environment, you should review the SQL Server Administration topic within Books Online. Because this is a book on database implementation, administration concepts are out of our scope.

There are, however, a few more things you should know before you install SQL Server 2000 in a production environment. In most multiSQL Server operations, the machine needs to participate in network security. An applications server, such as SQL Server, usually requires a large amount of resources exclusively available for its use. Also, appropriate permission sets need to be configured to ensure that sensitive data is not available where it shouldn't be. You seldom want SQL Server to share a machine with other applications, and certainly a SQL Server should never share a machine with a security server such as a Windows 2000, Windows 2003, or Windows NT Domain controller. Participating as a network user, with access to resources through a login ID, is an important aspect of SQL Server. The service accounts that make up the database and agent engines are assigned a domain user account that will have administrative rights over the machine where it is installed. Without this aspect the server is unable to send out email notifications. Once installed, the Enterprise Manager is the center of control for SQL Server. From this tool you can perform most activities needed to configure and maintain the server. Along with the Enterprise Manager, many other tools are installed. These tools have specific uses and provide intuitive means of accessing the SQL Server environment. A brief explanation of each tool is provided in the paragraphs that follow. Throughout the book, these tools will be used to aid in applying the implementation concepts. The short descriptions that follow will provide you with at least a point of initial discovery into their use.

SQL Server Tools

First off, SQL Server comes equipped with two additional broad-scale products that operate on top of the SQL Server database engine. Analysis Services and English Query provide support for data warehousing and plain language query, respectively. Analysis Services, formerly known as OLAP Services, provides for data warehouse cube storage and data mining capabilities used to find information in OLAP cubes and relational databases. English Query allows for the development of applications to allow users to ask questions from the data store using common English instead of traditional SQL statements. As mentioned previously, the Enterprise Manager is the primary administration tool for interacting with Microsoft SQL Server 2000. Using this tool, you can administer any server in the organization. The Enterprise Manager is an MMC snap-in tool that is installed along with the SQL Server client utilities. This is a good place to start exploring the contents of the server. Shown in Figure 1.1 is the Enterprise Manager with a set of SQL Servers registered. The right view pane has been set to Taskpad (from the View menu option, select Taskpad).

The installation of client tools provides many other tools, most of which are accessible from within the Enterprise Manager. Other tools can be accessed from the Windows, Programs menu in the Microsoft SQL Server menu group, as shown in Figure 1.2.

By far, one of the most useful tools to be found for anyone trying to master Microsoft SQL Server 2000 is the complete reference guide available in Books Online. This thorough help system provides full coverage of the product with plenty of examples for coding assistance. It is generally regarded as one of the best help facilities of any of the Microsoft technical products, and it's mentioned here because of how much is actually inside.

You will want to enable the use of personalized menus to gain the full use of the help facility. One facet of the help facility is the capability to mark favorites and maintain a list of topics you want to revisit, and this feature is not available if the option is not set within the taskbar properties.

Within Books Online, use the index pane to look up topics by key term or definition. The index pane is easy to use and links you to the most probable locations to get assistance. If you know an exact word or statement in SQL Server, you can look it up. You can find most any topic from this tab by typing a few letters of your topic. Of course, if that doesn't help, you can use the Search tab, but the main drawback of this tab is the number of matches that will be found for any given search. Use the Favorites tab as a means of saving topic links. If you find that your links are not being saved from session to session, it is likely that the personalized menu options have not been selected. To activate this option, right-click on the Windows taskbar and select Properties. There you will find the option to select, as shown in Figure 1.3.

The Client and Server Network utilities can be used to specify communication protocols and network libraries used in communications with the server. You may also use these tools to fine-tune some of the facets of communications such as configuring server aliasing or changing the default communications port. These two tools are used solely from the Windows menu system and cannot be accessed through the Enterprise Manager.Chapter 7, "Tuning and Optimizing Analysis," and Chapter 8, "Designing for Optimized Data Access," because it has many useful benefits to the database implementer.Chapter 7.

The Bulk Copy Program (BCP) and the ODBC Structured Query Language (OSQL) tools are command-line tools that can be used to execute commands against the server. Both tools, though seldom used, offer some advantages in particular circumstances. BCP can be used to populate databases with data. The main feature of the Bulk Copy Program is the capability to import or export data from text files to or from SQL Server tables. As the name suggests, the "Bulk" Copy Program is primarily used to shift large amounts of data from one place to another, so it is used only in this case. The rate at which the Bulk Copy Program transfers data from one place to another is about 2000 rows per second. The BCP.exe is stored in the C:\Program Files\Microsoft SQL Server\MSSQL\Binn folder and is run through the command prompt.

The OSQL tool provides the capability to execute T-SQL statements or batches from a server or workstation and view the results returned. The OSQL utility is similar to the Query Analyzer in that it executes batches of T-SQL code. The utility is run on the command line. Other than this, Query Analyzer and OSQL perform more or less the same function. OSQL's primary purpose is to allow the scheduling of operations via the operating system scheduler, SQL Server scheduler, or any other schedule application provided by a third party. Analysis Services and English Query are two additional programs that ship with SQL Server to provide some added functionality. Analysis Services is used to configure and utilize data warehouse cubes, and English Query provides simple language support. These tools are beyond the scope of this book and the exam. For this exam and consequently this book, we will focus on the tools that allow you to develop applications against SQL Server databases.

Program Execution

Because this exam is all about SQL coding against well-designed database systems, it is imperative that you understand how to code and execute SQL code. Executing SQL code can be achieved using various methods. The dialect of SQL code used by SQL Server is an extension on ANSI-SQL (the industry standard) known as T-SQL, or Transact SQL. T-SQL includes the entire implementation of the industry standard version as well as other commands that represent Microsoft's extension of the code. SQL Server 2000 supports the ANSI SQL-92 entry-level standard, but not the ANSI-92 intermediate-level standard. This is also true of SQL Server's primary competitors.

You can develop front-end applications with Visual Basic, Visual Basic .NET, Visual C, C#, Access, or the Internet, or in combination with other data and programming interfaces. Commands can be executed through ODBC or OLE-DB standard libraries connecting to the server from virtually any computer. Statements can be executed directly on the server using the Query Analyzer or OSQL. Commands are also executed within the context of Triggers, Stored Procedures, and user-defined functions.

With all of these different coding mechanisms, you will want to become intimately familiar with the Query Analyzer. This tool is the primary development environment for writing, testing, debugging, and executing T-SQL code.

The Query Analyzer

The Query Analyzer is shown in Figure 1.4. This tool is your primary tool for executing T-SQL code. As you write code for various implementations, you can use the Query Analyzer to initially enter the code; however, it is also a full development environment that has many qualities needed for code analysis and improvement.