SQL Server Best Practices
Download
1 / 18

Ayoka, L.L.C. 202 E. Border Street, Ste 334 Arlington, TX 76010 817.210.4042 ayokasystems - PowerPoint PPT Presentation


  • 77 Views
  • Uploaded on

SQL Server Best Practices. By Steve Chang. Ayoka, L.L.C. 202 E. Border Street, Ste 334 Arlington, TX 76010 817.210.4042 www.ayokasystems.com. Temporary Tables. Local temporary table On disk, in tempdb Visible only to the current scope (like a stored procedure) Global temporary table

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 ' Ayoka, L.L.C. 202 E. Border Street, Ste 334 Arlington, TX 76010 817.210.4042 ayokasystems' - havyn


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

SQL Server Best Practices

By Steve Chang

Ayoka, L.L.C.

202 E. Border Street, Ste 334

Arlington, TX 76010

817.210.4042

www.ayokasystems.com


Temporary Tables

  • Local temporary table

    • On disk, in tempdb

    • Visible only to the current scope (like a stored procedure)

  • Global temporary table

    • On disk, in tempdb

    • Visible to all sessions


Local temporary table
Local Temporary Table

  • CREATE TABLE #people (     id INT,     name VARCHAR(32) )

  • SELECT id, name INTO #people FROM employees

  • DROP TABLE #people


Global Temporary Table

  • CREATE TABLE ##people (     id INT,     name VARCHAR(32) )

  • SELECT id, name INTO ##people FROM employees

  • DROP TABLE ##people


Table Variables

  • In memory

  • Performs slightly better than local temporary table

  • Automatically cleared when the procedure or function goes out of scope

  • In user-defined function, only allow table variables


Table Variables Syntax

  • DECLARE @people TABLE (     id INT,     name VARCHAR(32) )


Limitations of Table Variables

  • Cannot truncate a table variable

  • Table variables cannot be altered

  • Cannot explicitly add an index to a table variable

  • Cannot drop a table variable when it is no longer necessary


Conclusion

  • Rarely use ##table

  • Choose between #table and @table

    • Depend on performance and reasonable load testing

    • Small data set result, use @table

    • Need index, use #table

    • In most situations, #table makes more sense

  • Avoid cursor

  • Use #table before large tables’ join.

  • Do not use the SELECT INTO statement to create #table


Code Examples

  • ex_tmp_table.sql

  • ex_no_tmp_table.sql


Microsoft SQL Server

Management Studio Tools

  • Actual Execution Plan

  • Client Statistics

  • Database Engine Tuning Advisor


Actual Execution Plan (1)

  • On SQL Server Management Studio’s tool bar


Actual Execution Plan (2)

  • According to the result, determine which sql statements are the bottlenecks


Client Statistics (1)

  • On SQL Server Management Studio’s tool bar


Client Statistics (2)

  • Review the performance


Database Engine Tuning Advisor (1)

  • On SQL Server Management Studio’s tool bar



Database Engine Tuning Advisor (3)

  • Create recommended indexes



ad