| Before attending this course, students must have:
|
|
|
| Connect clients running Windows 2000 to networks and the Internet.
|
| Create and manage user accounts.
|
| Configure and manage disks and partitions, including disk striping and mirroring.
|
| Implement Windows 2000 security.
|
| An understanding of basic relational database concepts, including:
|
| Data integrity concepts.
|
| How data is stored in tables (rows and columns).
|
| Familiarity with the role of the database administrator.
|
| Experience using the Microsoft Windows® 2000 operating system to:
|
| Configure the Windows 2000 environment.
|
| Manage access to resources by using groups.
|
| Manage data by using the NTFS file system.
|
| Optimize performance in Windows 2000.
|
| Logical and physical database design.
|
| Relationships between tables and columns (primary key and foreign key, one-to-one, one-to-many, many-to-many).
|
| Knowledge of basic Transact-SQL syntax (SELECT, UPDATE, and INSERT statements). |
| At the end of the course, students will be able to describe the elements of SQL Server 2000; design a SQL Server enterprise application architecture; describe the conceptual basis of programming in Transact-SQL; create and manage databases and their related components; implement data integrity by using the IDENTITY column property, constraints, defaults, rules, and unique identifiers; plan for the use of indexes; create and maintain indexes; create, use, and maintain data views; implement user-defined functions; design, create, and use stored procedures; create and implement triggers; program across multiple servers by using distributed queries, distributed transactions, and partitioned views; optimize query performance; analyze queries; and manage transactions and locks to ensure data concurrency and recoverability. At the end of this module, you will be able to:
|
|
|
| Execute extended stored procedures.
|
| Describe SQL Server 2000 and its supported operating system platforms.
|
| Describe SQL Server integration with Microsoft Windows 2000 and other server applications.
|
| Describe SQL Server databases.
|
| Describe SQL Server security.
|
| Describe SQL Server administration and implementation activities, as well as SQL Server application design options.
|
| Describe the concepts of enterprise-level application architecture.
|
| Describe the primary SQL Server programming tools.
|
| Explain the difference between the two primary programming tools in SQL Server.
|
| Describe the basic elements of Transact-SQL.
|
| Describe the use of local variables, operators, functions, control of flow statements, and comments.
|
| Describe the various ways to execute Transact-SQL statements.
|
| Create a database.
|
| Create a filegroup.
|
| Manage a database.
|
| Describe data structures.
|
| Create and drop user-defined data types
|
| Create and drop user tables.
|
| Generate column values.
|
| Generate a script
|
| Describe the types of data integrity.
|
| Describe the methods to enforce data integrity.
|
| Determine which constraint to use and create constraints.
|
| Define and use DEFAULT, CHECK, PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints.
|
| Disable constraints.
|
| Describe and use defaults and rules.
|
| Determine which data integrity enforcement methods to use.
|
| Describe why and when to use an index.
|
| Describe how SQL Server uses clustered and nonclustered indexes.
|
| Describe how SQL Server index architecture facilitates the retrieval of data.
|
| Describe how SQL Server maintains indexes and heaps.
|
| Describe the importance of selectivity, density, and distribution of data when deciding which columns to index.
|
| Create indexes and indexed views with unique or composite characteristics.
|
| Use the CREATE INDEX options.
|
| Describe how to maintain indexes over time.
|
| Describe how the query optimizer creates, stores, maintains, and uses statistics to optimize queries.
|
| Query the sysindexes table.
|
| Describe how the Index Tuning Wizard works and when to use it.
|
| Describe performance considerations that affect creating and maintaining indexes.
|
| Describe the concept of a view.
|
| List the advantages of views.
|
| Define a view with the CREATE VIEW statement.
|
| Modify data through views.
|
| Optimize performance by using views.
|
| Describe how a stored procedure is processed.
|
| Create, execute, modify, and drop a stored procedure.
|
| Create stored procedures that accept parameters.
|
| Create custom error messages.
|
| Describe the three types of user-defined functions.
|
| Create and alter user-defined functions.
|
| Create each of the three types of user-defined functions.
|
| Create a trigger.
|
| Drop a trigger.
|
| Alter a trigger.
|
| Describe how various triggers work.
|
| Evaluate the performance considerations that affect using triggers.
|
| Describe distributed queries.
|
| Write ad hoc queries that access data that is stored in a remote SQL Server 2000 or in an OLE DB data source.
|
| Set up a linked server environment to access data that is stored in a remote SQL Server 2000 or in an OLE DB data source.
|
| Write queries that access data from a linked server.
|
| Execute stored procedures on a remote server or linked server.
|
| Explain the role of the query optimizer and how it works to ensure that queries are optimized.
|
| Use various methods for obtaining execution plan information so that they can determine how the query optimizer processed a query and validate that the most efficient query plan was generated.
|
| Create indexes that cover queries
|
| Identify indexing strategies that reduce page reads.
|
| Evaluate when to override the query optimizer.
|
| Analyze the performance gain of writing efficient queries and creating useful indexes for queries that contain the AND logical operator.
|
| Analyze the performance gain of writing efficient queries and creating useful indexes for queries that contain the OR logical operator
|
| Evaluate how the query optimizer uses different join strategies for query optimization.
|
| Describe transaction processing.
|
| Execute, cancel, or roll back a transaction.
|
| Identify locking concurrency issues.
|
| Identify resource items that can be locked and the types of locks.
|
| Describe lock compatibility
|
| Describe how SQL Server 2000 uses dynamic locking.
|
| Set locking options and display locking information. |
Top