1 / 17

T-SQL: Simple Changes That Go a Long Way

Discover the power of T-SQL with expert Dave Valentine as he shares essential strategies to enhance SQL Server performance. In this session, we will explore key concepts such as Output Table Valued Parameters, Window Functions, Common Table Expressions, and indexing strategies including Clustered and Non-Clustered indexes. Learn how to identify and solve performance issues effectively, ensuring your database runs efficiently. With practical tips and in-depth insights, this presentation aims to help you understand the nuances of performance tuning in SQL Server.

linh
Download Presentation

T-SQL: Simple Changes That Go a Long Way

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. T-SQL: Simple Changes That Go a Long Way DAVE VALENTINE • @ingeniousSQL • ingeniousSQL.com • linkedin.com/in/ingenioussql

  2. Agenda Functionality OUTPUT Table Valued Parameter Window Functions Common Table Expressions Performance Heaps CLUSTERED INDEX NONCLUSTERED INDEX SARGable

  3. Understand the Problem Before You Fix Create the Problem

  4. About Dave… MCP 2 of 3 toward MCSA: SQL Server 2012 Database / BI Developer Adjunct Professor @IngeniousSQL Dave.Valentine@IngeniousSQL.com IngeniousSQL.com

  5. Indexing XML Spatial Full-text Column Store CLUSTERED NONCLUSTERED Covering Filtered

  6. Indexing Clustered Index Physical sorted order One per table PRIMARY KEY Fast key lookup

  7. Indexing Heap Table without a CLUSTERED INDEX Poor performance Table Scans

  8. Indexing Non-Clustered Index Another sorted copy of the data 16 key columns 999 per table dm_db_missing_index_details dm_db_missing_index_groups dm_db_missing_index_group_stats

  9. Indexing • http://www.sqlskills.com/blogs/kimberly/when-did-sql-server-stop-putting-indexes-on-foreign-key-columns/ Non-Clustered Index FOREIGN KEYs Covered Filtered SARGable

  10. Output INSERT, UPDATE, DELETE, MERGE @@IDENTITY SCOPE_IDENTITY IDENT_CURRENT INSERTED.* and DELETED.* http://technet.microsoft.com/en-us/library/ms177564.aspx

  11. Table Valued Parameter Easily store and transfer rows of data User Defined Table Type UNIQUE and PRIMARY KEY Constraints No indexing No statistics Read only as procedure parameters http://technet.microsoft.com/en-us/library/bb510489.aspx

  12. T-SQL Window Functions Ranking RANK, ROW_NUMBER, NTILE, DENSE_RANK http://technet.microsoft.com/en-us/library/ms189798.aspx Aggregate AVG, MIN, SUM, COUNT, STDEV, VAR, MAX http://technet.microsoft.com/en-us/library/ms173454.aspx Analytic LEAD, FIRST_VALUE, LAG, LAST_VALUE http://technet.microsoft.com/en-us/library/hh213234.aspx

  13. Common Table Expressions CTE Simplify query syntax Multiple reference Recursive queries VIEW alternative http://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

  14. Summary INTO OUTPUT Table Valued Parameter Window Functions Common Table Expressions Heaps CLUSTERED INDEX NONCLUSTERED INDEX Covering Indexes Filtered Indexes SARGable

  15. Questions DAVE VALENTINE • @ingeniousSQL • ingeniousSQL.com • linkedin.com/in/ingenioussql

More Related