Transact SQL

1
Transact SQL

• Principles, Techniques
• and Peculiarities

Tim Tatum
2
Transact SQL

• Tim Tatum, MBA MCSD
• Core Consulting
• Richmond, Virginia

3
Transact SQL

• Principles, Techniques and Peculiarities
• Fundamentals of Security
• Preservation of Data Integrity
• Flexible Design Techniques
• Performance Management

4
Fundamentals of Security

• Regular Backups
• Principle of Least Privilege
• Parameterized Stored Procedures

5
Preservation of Data Integrity

• Bad Data Transaction Processing
• Enforce Referential Integrity
• Effective Application Layer Validation
• Bad Data Data Warehouse
• Effective Controls
• Defensive Scripting

6
Preservation of Data Integrity

• Peculiarities
• Nulls
• Variable Assignments
• Useful Techniques
• Reconciliation
• Forcing 11 relationships

7
Preservation of Data Integrity

• Nulls
• Nulls and Joins
• Comparisons
• Null Math/Concatenation

8
Preservation of Data Integrity

• Variable Assignments
• Within Select Statements No Rows
• Within Select Statements Multiple Rows
• Assignments, Data Types and Isnull()

9
Preservation of Data Integrity

• Useful Techniques
• Reconciliation
• Forcing 11 Relationships

10
Flexible Design Techniques

• Dynamic SQL
• sp_executesql
• exec()

11
Managing Performance

• Using Indexes Effectively
• Writing Queries that Perform Well

12
Managing Performance

• Using Indexes Effectively
• Developing an Indexing Strategy
• Maintaining Indexes

13
Managing Performance

• Using Indexes Effectively
• Developing an Indexing Strategy
• Execution Plan
• Seek
• Scan
• Using SQL Profiler
• Revisit Strategies as Data Grows

14
Managing Performance

• Maintaining Indexes
• Manage Fragmentation
• Update Statistics

15
Managing Performance

• Writing Queries that Perform Well
• Use Stored Procedures
• Set nocount on
• Use (nolock) on All Select Queries
• Substitute Joins for Subqueries
• Select Only the Columns You Need
• Handle String Manipulation Carefully
• Avoid Implicit Conversions

16
Managing Performance

• Writing Queries that Perform Well
• Try to Avoid

• IN
• NOT IN
• NOT Exists
• NOT Like

• Like value
• ltgt
• !
• OR

17
Transact SQL

• Principles, Techniques and Peculiarities
• Fundamentals of Security
• Preservation of Data Integrity
• Flexible Design Techniques
• Performance Management