slide1
Download
Skip this Video
Download Presentation
Ayoka, L.L.C. 202 E. Border Street, Ste 334 Arlington, TX 76010 817.210.4042 ayokasystems

Loading in 2 Seconds...

play fullscreen
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
slide1

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

slide2

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
slide4

Global Temporary Table

  • CREATE TABLE ##people (     id INT,     name VARCHAR(32) )
  • SELECT id, name INTO ##people FROM employees
  • DROP TABLE ##people
slide5

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
slide6

Table Variables Syntax

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

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
slide8

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
slide9

Code Examples

  • ex_tmp_table.sql
  • ex_no_tmp_table.sql
slide10

Microsoft SQL Server

Management Studio Tools

  • Actual Execution Plan
  • Client Statistics
  • Database Engine Tuning Advisor
slide11

Actual Execution Plan (1)

  • On SQL Server Management Studio’s tool bar
slide12

Actual Execution Plan (2)

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

Client Statistics (1)

  • On SQL Server Management Studio’s tool bar
slide14

Client Statistics (2)

  • Review the performance
slide15

Database Engine Tuning Advisor (1)

  • On SQL Server Management Studio’s tool bar
slide17

Database Engine Tuning Advisor (3)

  • Create recommended indexes
ad