transact sql n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Transact SQL PowerPoint Presentation
Download Presentation
Transact SQL

Loading in 2 Seconds...

play fullscreen
1 / 17

Transact SQL - PowerPoint PPT Presentation


  • 161 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Transact SQL' - elpida


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
transact sql

Transact SQL

Principles, Techniques

and Peculiarities

Tim Tatum

transact sql1

Transact SQL

Tim Tatum, MBA MCSD

Core Consulting

Richmond, Virginia

transact sql2
Transact SQL
  • Principles, Techniques and Peculiarities
    • Fundamentals of Security
    • Preservation of Data Integrity
    • Flexible Design Techniques
    • Performance Management
fundamentals of security
Fundamentals of Security
  • Regular Backups
  • Principle of Least Privilege
  • Parameterized Stored Procedures
preservation of data integrity
Preservation of Data Integrity
  • Bad Data – Transaction Processing
    • Enforce Referential Integrity
    • Effective Application Layer Validation
  • Bad Data – Data Warehouse
    • Effective Controls
    • “Defensive” Scripting
preservation of data integrity1
Preservation of Data Integrity
  • Peculiarities
    • Nulls
    • Variable Assignments
  • Useful Techniques
    • Reconciliation
    • Forcing 1:1 relationships
preservation of data integrity2
Preservation of Data Integrity
  • Nulls
    • Nulls and Joins
    • Comparisons
    • Null Math/Concatenation
preservation of data integrity3
Preservation of Data Integrity
  • Variable Assignments
    • Within Select Statements – No Rows
    • Within Select Statements – Multiple Rows
    • Assignments, Data Types and Isnull()
preservation of data integrity4
Preservation of Data Integrity
  • Useful Techniques
    • Reconciliation
    • Forcing 1:1 Relationships
flexible design techniques
Flexible Design Techniques
  • Dynamic SQL
    • sp_executesql
    • exec()
managing performance
Managing Performance
  • Using Indexes Effectively
  • Writing Queries that Perform Well
managing performance1
Managing Performance
  • Using Indexes Effectively
    • Developing an Indexing Strategy
    • Maintaining Indexes
managing performance2
Managing Performance
  • Using Indexes Effectively
    • Developing an Indexing Strategy
      • Execution Plan
        • Seek
        • Scan
      • Using SQL Profiler
      • Revisit Strategies as Data Grows
managing performance3
Managing Performance
  • Maintaining Indexes
    • Manage Fragmentation
    • Update Statistics
managing performance4
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
managing performance5
Managing Performance
  • Writing Queries that Perform Well
    • Try to Avoid
  • IN
  • NOT IN
  • NOT Exists
  • NOT Like
  • Like ‘%value’
  • <>
  • !=
  • OR
transact sql3
Transact SQL
  • Principles, Techniques and Peculiarities
    • Fundamentals of Security
    • Preservation of Data Integrity
    • Flexible Design Techniques
    • Performance Management