1 / 17

Transact SQL

Transact SQL. Principles, Techniques and Peculiarities. Tim Tatum. Transact SQL. Tim Tatum, MBA MCSD Core Consulting Richmond, Virginia. Transact SQL. Principles, Techniques and Peculiarities Fundamentals of Security Preservation of Data Integrity Flexible Design Techniques

elpida
Download Presentation

Transact SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 1:1 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 1:1 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’ • <> • != • OR

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

More Related