|
Module
1 - SQL Server 2000 Development
Foundation
SQL Server Tour
- Enterprise Manager, Analysis
Services, Query Analyzer, English Query
- Profiler, XML Support
- Management Tools
- SQL Server Security
- Meta Data Services
- Import and Export
- Data Transformation Services
- Replication
Installation and Configuration
- Versions
- Pre-Requirements
- Multiple Instances
- Install SQL Server
- Setting Up the System Accounts
- Configure the Server
- Memory, Processor, Connections
- Server Settings
- Database Settings
Designing and Implementing a
Database
- Database Design, Normalization
- Beyond Normalization
- Setting Up a Database
- Creating Databases
- Creating, Dropping, and Altering
Tables
- Modifying Tables
- Creating Indexes
- Triggers, Stored Procedures
- Database Diagrams
- Tools for Enforcing Data Integrity
Basic Querying
Understanding SQL
The SELECT Statement
The WHERE Clause
The ORDER BY Clause
The GROUP BY Clause
TOP
Search Conditions
Aggregate Functions
Joining
- Principals of Joining
- Inner Joins
- Outer Joins
- Self Joins
- Subqueries
- Unions
Action Queries
Inserting Data
Updating Data
Deleting Data
The Use of Temporary Tables
Bulk Inserts
|
|
Module
2 - Developing Database and
System Features
What's new in the Query Analyzer
Object Browser
Transact-SQL Debugger
Query Analyzer Features
Query Plans and Index Tuning
Manage Indexes and Statistics
Options and Customization
Programming with T-SQL
- Overview of Transact-SQL
- Transact-SQL Extensions
- Batches, Variables
- Delimiters and Operators
- Data Types
- Built-in Functions
- System Functions
@@IDENTITY
@@ERROR, etc.
- Error Handling
- Flow of Control
Transaction Processing
- Implicit Transactions
- Explicit Transactions
- Working with Locks
- Monitoring Processes and Locks
- Isolation Levels
- Blocking and Deadlocks
- Setting Deadlock Priority
Building Views
- Complex Joins
- Pre-Aggregate Data
- Indexed Views
- Partitioned Views
Maintaining and Backing up SQL
Server
Backup and Restore
Creating a Backup Strategy
Implementing Backups
Database Maintenance Plan Wizard
Restoring from a Backup
Building Stored Procedures and
Triggers
- Building Stored Procedures
Parameters
Temp tables
Cursors
- Error Handling in Stored
Procedues
- Debugging Stored Procedures
- Building Triggers
Enforce Business Rules
Send Alerts
AFTER Triggers
INSTEAD OF Triggers
Building User-Defined Functions
Scalar functions
Inline table-valued functions
Multi-statement table-valued
functions
The TABLE Data Type
Putting it All Together
When to use a view, function,
stored procedure
Advanced Queries/Linked Servers
- Advanced Queries
Handling Text & Image
Datatypes
- EXECUTE, EXECUTE() and
sp_executeSQL
- Dynamic SQL and Permissions
- When and How to Use Cursors
- Handling Large Data Sets
- Partitioning Tables
- Distributed Queries-Linked
Servers
OPENROWSET
OPENQUERY
Security
- NT 4.0 and Windows 2000
- SQL Server Authentication
- Configuring SQL Server Security
Settings
- Understanding Roles, Database,
Server and User-Defined Roles
- Assigning and Granting Permissions
- Application Roles
- Securing Data
- Windows 2000 / Kerberos
Monitoring, Tuning and
Configuration
- SQL Server Monitoring Tools
- SQL Server Profiler
- Tuning Queries (Joins and
Subqueries)
- Clustered and Non-Clustered Index
Recommendations
- Graphical Showplan
- Indexing tools and recommendations
- Using System Monitor
- Factors for a Well-Tuned
Application
- Understanding Caching, Data Cache,
Procedure Cache
|
