Developing in SQL Server 2000 Course Outline
Duration: 5 Days
Objectives
This course will enable delegates to program a SQL Server 2000 database. This includes developing in Transact-SQL. The course will cover the structure of a SQL Server 2000 database. Delegates will be able to create stored procedures, triggers, functions and views. The course will cover improving database performance by indexing tables.
Audience
This course is designed for those who are responsible for implementing database objects and programming SQL Server databases by using Transact-SQL.
Prerequisites
An understanding of basic relational database concepts, including:
Logical and physical database design
Relationships between tables and columns (primary key and foreign key, one-to-one, one-to-many, many-to-many)
How data is stored in tables (rows and columns)
Knowledge of basic Transact-SQL syntax (SELECT, UPDATE, and INSERT statements)
Module 1: Overview of Programming SQL Server
1. Overview of Transact-SQL.
2. Describe the concepts of enterprise-level application architecture.
3. Describe the primary SQL Server programming tools.
4. Explain the difference between the two primary programming tools in SQL Server.
5. Describe the basic elements of Transact-SQL.
6. Describe the use of local variables, operators, functions, control of flow statements, and comments.
7. Describe the various ways to execute Transact-SQL statements.
Module 2: Creating and Managing Databases
1. Identify system requirements
2. Develop a logical data model
3. Create a database.
4. Create a filegroup.
5. Create a transaction log.
6. Manage a database.
7. Describe data structures.
Module 3: Creating Data Types and Tables
1. Create and drop user-defined data types.
2. Create and drop user tables.
3. Generate column values.
4. Generate a script.
Module 4: Implementing Data Integrity
1. Describe the types of data integrity.
2. Describe the methods to enforce data integrity.
3. Determine which constraint to use and create constraints.
4. Define and use DEFAULT, CHECK, PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints.
5. Disable constraints.
6. Describe and use defaults and rules.
7. Determine which data integrity enforcement methods to use.
Module 5: Access and Modify Data
1. Define and use the SELECT statement.
2. Use FROM, INTO, WHERE, GROUP BY, HAVING and ORDER BY clauses.
3. Use sub-queries within SELECT statements.
4. Summarise data with aggregate functions.
5. Modify data with INSERT, UPDATE and DELETE statements.
6. Define join types.
7. Create advanced queries with inner, outer and cross joins.
Module 6: Managing and Manipulating Data
1. Import and Export data with bcp utility, BULK INSERT and DTS.
2. Write queries that access data from a linked server.
3. Write ad hoc queries that access data that is stored in a remote SQL Server 2000.
4. Use cursors to retrieve data.
5. Define cursor locking.
6. Use the FOR XML clause to retrieve XML data.
7. Use the OPEN XML clause to access XML data.
Module 7: Planning Indexes
1. Describe why and when to use an index.
2. Describe how SQL Server uses clustered and non-clustered indexes.
3. Describe how SQL Server index architecture facilitates the retrieval of data.
4. Describe how SQL Server maintains indexes and heaps.
Module 8: Creating and Maintaining Indexes
1. Create indexes and indexed views with unique or composite characteristics.
2. Use the CREATE INDEX options.
3. Describe how to maintain indexes over time.
4. Query the sysindexes table.
5. Describe performance considerations that affect creating and maintaining indexes.
Module 9: Implementing Views
1. Describe the concept of a view.
2. List the advantages of views.
3. Define a view with the CREATE VIEW statement.
4. Modify data through views.
5. Optimise performance by using views.
Module 10: Implementing Stored Procedures
1. Describe how a stored procedure is processed.
2. Create, execute, modify, and drop a stored procedure.
3. Create stored procedures that accept parameters.
4. Execute extended stored procedures.
5. Create custom error messages.
Module 11: Implementing User-defined Functions
1. Describe the three types of user-defined functions.
2. Create and alter user-defined functions.
3. Create each of the three types of user-defined functions.
Module 12: Implementing Triggers
1. Understand different types of triggers
2. Create a trigger.
3. Drop a trigger.
4. Alter a trigger.
5. Describe how various triggers work.
6. Evaluate the performance considerations that affect using triggers.
Module 13: Managing Transactions and Locks
1. Define transaction and locking architecture.
2. Describe types of transaction.
3. Define optimistic and pessimistic concurrency.
4. Define transaction isolation levels.
Module 14: Design and Administer Security Levels
1. Design security plan.
2. Administer authentication.
3. Administer authorisation.
4. Administer permissions.
5. Administer users, groups and roles.
Module 15: Monitoring and Tuning
1. Use SQL Profiler to monitor a database
2. Describe how the Index Tuning Wizard works and when to use it.
3. Define database partitioning
Download the Microsoft Powerpoint Presentation for this Course >> |
 |