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 2000Many 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.
The following list and the paragraphs after it highlight some of the critical new features:
XML SupportThe 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 ViewsSQL 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 FunctionsYou 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 ViewsIndexed 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 2000SQL 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 TriggersINSTEAD 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 IntegrityCascading 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. CollationsSQL 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 ServerSQL 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 ViewsSQL 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 TopicsThis 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 ImplementationThe 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.
The following hardware is the minimum needed to install the Enterprise version of SQL Server 2000:
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 ToolsFirst 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). Figure 1.1. SQL Server 2000 Enterprise Manager. 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. Figure 1.2. The Microsoft SQL Server menu group.
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.
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. Figure 1.3. The Windows Taskbar and Start Menu Properties dialog.
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. The Service Manager tool can be used to stop, pause, start, and monitor SQL Server services. This task can also be performed within the operating system's Services dialog or from within the Enterprise Manager. The Services drop-down list displays the services that can be controlled. These include the MSSQLServer Service, the Microsoft Search Service, the MSDTC Service, and the SQL Server Agent Service. You can find some additional functionality in the Service Manager. Click the icon on the upper-left corner of the form and notice two menu items, Options and Connect. The Verify Service Control Action check box is checked by default. This controls whether an Are You Sure? dialog box appears when you stop a service. The Poll Interval determines how frequently Service Manager looks for the status of the services that are monitored. The default interval is 5 seconds. You also can set the default service icon viewed in the taskbar. It is recommended that you set this to the agent so that you can check the status of the service with a simple glance at the bottom of your screen. The Query Analyzer is one of the primary tools used to create, test, and debug T-SQL commands and procedures. This tool is covered in depth in Chapter 7, "Tuning and Optimizing Analysis," and Chapter 8, "Designing for Optimized Data Access," because it has many useful benefits to the database implementer. The Profiler is used to monitor and tune the server and its objects and applications. It is a versatile tool that can diagnose all activity on the server and aid in finding trouble spots in an application or performance bottlenecks in the system. This tool is discussed in depth in 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 ExecutionBecause 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 AnalyzerThe 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. Figure 1.4. The Query Analyzer, your primary coding tool. SQL Query Analyzer is an interactive, graphical tool that enables the developer to write queries, execute multiple queries simultaneously, view results, analyze the query plan, and receive assistance to improve the query performance. The Analyzer screen is divided into three components. The object browser along the left side is handy for finding database object names and command syntax. On the right-hand side of the screen, you find the editing area on the top part. The bottom part, known as the Results pane, contains the output of your query, which is known as a resultset. The Query Analyzer in not used solely to execute T-SQL statements, but also to analyze them. Query Analyzer can report things such as how much time the queries took to run and how much time it took to read data from the hard disk. You can use tools built into the analyzer to diagnose the query, to redesign the query, and to achieve better performance. The Execution Plan options graphically display the data retrieval methods chosen by the query optimizer. If more intense scrutiny is required after designing and deploying your databases, the SQL Profiler would be used instead of the Query Analyzer. After you deploy an application and give access to users, you need to monitor how your server is functioning and make sure that it's working the way it was intended. Using the profiler, you can set up traces that monitor anything that happens to the server. These include knowing whether a login failed or succeeded, whether a query was executed, and other such events. Traces can be customized to monitor different angles of SQL Server at different times. There is considerable exam content dealing with query optimization and the use of SQL Server tools to aid in optimization. We will delve into this content in detail in Chapter 7. Each tool in one way or another will be seen on the exam. Often you will see the tool used as a misleading, incorrect answer. For this reason it is important to know what each tool is used for. To be properly prepared for the exam, you should try to use each tool and note its most appropriate implementation. Other than the tools supplied with the product, SQL Server is made up of numerous objects. Each object has its own function on the server. Knowing how to work with each object is just as important as knowing how each tool is used. |
EAN: 2147483647
Pages: 154