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
Ayoka l l c 202 e border street ste 334 arlington tx 76010 817 210 4042 ayokasystems

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


Ayoka l l c 202 e border street ste 334 arlington tx 76010 817 210 4042 ayokasystems

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


Ayoka l l c 202 e border street ste 334 arlington tx 76010 817 210 4042 ayokasystems

Global Temporary Table

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

  • SELECT id, name INTO ##people FROM employees

  • DROP TABLE ##people


Ayoka l l c 202 e border street ste 334 arlington tx 76010 817 210 4042 ayokasystems

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


Ayoka l l c 202 e border street ste 334 arlington tx 76010 817 210 4042 ayokasystems

Table Variables Syntax

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


Ayoka l l c 202 e border street ste 334 arlington tx 76010 817 210 4042 ayokasystems

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


Ayoka l l c 202 e border street ste 334 arlington tx 76010 817 210 4042 ayokasystems

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


Ayoka l l c 202 e border street ste 334 arlington tx 76010 817 210 4042 ayokasystems

Code Examples

  • ex_tmp_table.sql

  • ex_no_tmp_table.sql


Ayoka l l c 202 e border street ste 334 arlington tx 76010 817 210 4042 ayokasystems

Microsoft SQL Server

Management Studio Tools

  • Actual Execution Plan

  • Client Statistics

  • Database Engine Tuning Advisor


Ayoka l l c 202 e border street ste 334 arlington tx 76010 817 210 4042 ayokasystems

Actual Execution Plan (1)

  • On SQL Server Management Studio’s tool bar


Ayoka l l c 202 e border street ste 334 arlington tx 76010 817 210 4042 ayokasystems

Actual Execution Plan (2)

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


Ayoka l l c 202 e border street ste 334 arlington tx 76010 817 210 4042 ayokasystems

Client Statistics (1)

  • On SQL Server Management Studio’s tool bar


Ayoka l l c 202 e border street ste 334 arlington tx 76010 817 210 4042 ayokasystems

Client Statistics (2)

  • Review the performance


Ayoka l l c 202 e border street ste 334 arlington tx 76010 817 210 4042 ayokasystems

Database Engine Tuning Advisor (1)

  • On SQL Server Management Studio’s tool bar



Ayoka l l c 202 e border street ste 334 arlington tx 76010 817 210 4042 ayokasystems

Database Engine Tuning Advisor (3)

  • Create recommended indexes