Home |  Writing |  Consulting |  Schedule |  Scripts |  Links |  About

Paul's Blog...
Top Posts
Why use Stored Procs?
10 lessons from 35K tps
SQL Server:Fresh Vision
SQL Myths
Data Architecture
CodeGen AutoAudit Trail

CS TechCast
TechEd ArCast
PASSCamp 2008 Panel
SQL DownUnder

Locations of visitors to this page

SQL Server
Smart Database Design

Design and development principles for SQL Server 2000/2005 Database Professionals:
database architects, data modelers, database developers, web developers, and .NET developers
who want to develop high-performance SQL Server database.

Smart Database Design
Database performance, scalability, and extensibility don't happen by accident.
The truth is that the way the database is designed is the single greatest factor determining the performance of the database.

Most performance training is a random collection of tips and best practices, but SQL Server Smart Database Design makes sense of the techniques smorgasbord by explaining how the database design affects queries, indexing, concurrency, and advanced scalability. Each layer either sets up the next area for success, or creates frustration and bottlenecks.
The SQL Server Smart Database Design seminar unpacks each layer:
  • explaining the key design decisions
  • testing the performance of the viable options
  • proving the best practice

  • so you know for certain how to design and develop your SQL Server database for maximum performance.


    Work through the concepts of Smart Database Design with Paul in an interactive format. Lots of discussion, practical illustrations and code proofs.
    Toronto - SQL Teach Pre-Con May 12, 2008
    Denver Sept 9-11, 2008
    Vancouver Sept 16-18, 2008
    London Sept 30- Oct 1, 2008
    Copenhagen Oct 7-9, 2008
    Montreal - SQLTeach Pre-con Date: TBD
    Charlotte TBD
    Los Angeles TBD
    Colorado Springs TBD


    Smart Database Design $29.95 New
    Producer / Director: digital media MVP Chuck Boyce

    2-hour DVD summarizing Smart Database Design.
    Currently in post-production, as soon as it's complete I'll post ordering info here.
    Paul, thanks for coming out to IBS. Iíve already needed to use some of the ideas you showed us. Iím even looking forward to wading through our indexes and cleaning things out.
    - Ryan Opfer

    This class was great. Paul was willing to take the time needed when I had a question, and always had a good answer. I came away with an ability to explain things Iíve always heard before but could never quite articulate. I really appreciated the practical approach that made the concepts we covered applicable to my job right now. It was especially helpful to get an expert's advice on using the new features of SQL Server 2005.
    - Josh Smith

    I greatly appreciated the down to earth approach and the way that you presented the different topics. It was helpful to talk about indexes in depth and seeing and understanding the new features of SQL 2005 was helpful too. You not only explained the details, but you explained the design reasons why certain techniques work and when to apply them. Excellent class - so much better than a standard class, thanks!
    - Marlin Brubaker
    You Will Learn How To:
  • Design an elegant efficient database physical schema that's fast, easy to query, and flexible so it works today and can handle changes in the future.
  • Design performance into the schema - why 3rd Normal Form and denoramlization alone arn't enough
  • Use the right set-based solution to solve query problems.
  • Tighten transactions for scalability with thousands of users.
  • Refactor complex cursors into high-performance set-based queries
  • Develop a comprehensive indexing strategy for overall best performance
  • Dramatically improve the performance of legacy databases
  • Who Should Attend
  • Data Architects who want to take advantage of the SQL Server 2005 in their designs
  • Data Modelers who want to design high-performance database physical schemas
  • Database Developers tasked with improving the performance of a legacy database
  • Database Professionals designing and developing a new database
  • .NET or web developers who design their own SQL Server databases
  • Curriculum
    1) Optimization Theory
  • The Framework for Performance Designs
  • Optimization Theory and Refactoring
  • 2) Physical Schema Performance
  • Relational Schema Design for Performance
  • Schema Design Layers
  • Normalization & the Rules of One
  • Generalization
  • Managing Optional Data
  • Data Patterns
  • Primary Key / Foreign Key Design
  • Attribute Design
  • Constraints
  • Responsible Denormalization
  • Generic Designs
  • 3) Hierarchical Data Designs
  • Adjacency List
  • Materialized Path
  • 4) Designing a Data Abstraction Layer
  • Stored Procedures
  • Complex Triggers
  • T-SQL Best Practices
  • 5) Set-based Queries
  • Advanced Query Strategies
  • When to use or avoid Temp Tables
  • Understanding Query Plans
  • The Causes of Iterative Code
  • Refactoring Cursors into Set-based solutions
  • Query Plan Reuse
  • 6) Query Plans and Index Tuning
  • SQL Server Page Structures
  • Clustered Indexes and Heaps
  • Non-Clustered Indexes
  • Covering Indexes
  • Scans, Seeks, Joins and SARGs
  • Base Indexes
  • CRUD Matrix Index Strategy
  • 7) Managing Transactions, Locking & Blocking
  • Transactional Integrity & ACID
  • Isolation Levels and Locks
  • Snapshot Isolation and Row Versioning
  • Diagnosing Concurrency Problems
  • Deadlocks
  • Handling Concurrency Problems
  • 8) Advanced Scalability
  • Disk Subsystem Planning
  • Partition Tables
  • Indexed Views
  • Service Broker
  • Course F.A.Q.
  • How does this course compare with the Microsoft 2784 Query Optimization Course?
    Paul designed the MOC 2784 for Microsoft so both courses share his point of view, If you desire a 3-day workshop experience with 80% hands-on labs then take the MOC 2784. The SQL Server Smart Database Design explains database design and development options and when to apply which solution.

  • We're not designing a new database but trying to optimize our current database. Will these optimization techniques still apply?
    Absolutely. Design principles are easiest to implement on a clean slate, but they become even more critical when reafactoring a production database.

  • I'm not a SQL Expert, will I be able to keep up with an advanced course?
    Yes. A basic familiarity of SQL Server, data modeling, and T-SQL programming is assumed, but you don't need to be a SQL expert to benefit from this course.

  • We're planning on upgrading but still running SQL Server 2000. How much of the course is specific to SQL Server 2005?
    The design principles, and almost all of the implementation details apply to both SQL Server 2000 and SQL Server 2005. The proofs and examples use SQL Server 2005.

  • Theory's great, but how much of the seminar is practical, in depth knowledge about how to design and develop the database?
    Theory without practical implementation is useless. The SQL Server Smart Database Design includes all my favorite practical best-design techniques.

  • What are the seminar hours?
    9 am until 5pm with a morning break, a 1 hour break for lunch, and 2 afternoon breaks.

  • May I bring my notebook computer?
    Yes. Sample databases and scripts can be downloaded by attendees so you can test and verify the principles.

  • What material will I receive at the workshop?
    Attendees receive a binder with all slides printed for note taking, and various white papers. Sample code is available for download from the web-site.

  • Is the seminar competitively priced?
    Very. The average cost per day for advanced SQL Server training nationally is about $580.

  • Are questions answered after the class?
    Yes. Paul answers all email from students.

  • Can we offer an in-house workshop within our organization?
    Yes. Paul will teach a in-house course customized to your specific needs.

  • What is the dress code?
    Dress is business casual. Wear comfortable clothing and bring a sweater or jacket in case the room is cool.

  • Can I just "show up" for a class?
    I don't recommend it, there may not be any available seats. It's best to register, even if it's a last minute registration.