Download - Ayoka, L.L.C. 202 E. Border Street, Ste 334 Arlington, TX 76010 817.210.4042 ayokasystems
![Page 1: Ayoka, L.L.C. 202 E. Border Street, Ste 334 Arlington, TX 76010 817.210.4042 ayokasystems](https://reader035.vdocuments.us/reader035/viewer/2022070420/56815e7d550346895dcd0597/html5/thumbnails/1.jpg)
Made in USA Software Development Services
Ayoka, L.L.C.202 E. Border Street, Ste 334Arlington, TX 76010817.210.4042www.ayokasystems.com
By Steve Chang
SQL Server Best Practices
![Page 2: Ayoka, L.L.C. 202 E. Border Street, Ste 334 Arlington, TX 76010 817.210.4042 ayokasystems](https://reader035.vdocuments.us/reader035/viewer/2022070420/56815e7d550346895dcd0597/html5/thumbnails/2.jpg)
Made in USA Software Development Services
• 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
Temporary Tables
![Page 3: Ayoka, L.L.C. 202 E. Border Street, Ste 334 Arlington, TX 76010 817.210.4042 ayokasystems](https://reader035.vdocuments.us/reader035/viewer/2022070420/56815e7d550346895dcd0597/html5/thumbnails/3.jpg)
Made in USA Software Development Services
•CREATE TABLE #people ( id INT, name VARCHAR(32) )
•SELECT id, name INTO #people FROM employees
•DROP TABLE #people
Local Temporary Table
![Page 4: Ayoka, L.L.C. 202 E. Border Street, Ste 334 Arlington, TX 76010 817.210.4042 ayokasystems](https://reader035.vdocuments.us/reader035/viewer/2022070420/56815e7d550346895dcd0597/html5/thumbnails/4.jpg)
Made in USA Software Development Services
•CREATE TABLE ##people ( id INT, name VARCHAR(32) )
•SELECT id, name INTO ##people FROM employees
•DROP TABLE ##people
Global Temporary Table
![Page 5: Ayoka, L.L.C. 202 E. Border Street, Ste 334 Arlington, TX 76010 817.210.4042 ayokasystems](https://reader035.vdocuments.us/reader035/viewer/2022070420/56815e7d550346895dcd0597/html5/thumbnails/5.jpg)
Made in USA Software Development Services
•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
![Page 6: Ayoka, L.L.C. 202 E. Border Street, Ste 334 Arlington, TX 76010 817.210.4042 ayokasystems](https://reader035.vdocuments.us/reader035/viewer/2022070420/56815e7d550346895dcd0597/html5/thumbnails/6.jpg)
Made in USA Software Development Services
•DECLARE @people TABLE ( id INT, name VARCHAR(32) )
Table Variables Syntax
![Page 7: Ayoka, L.L.C. 202 E. Border Street, Ste 334 Arlington, TX 76010 817.210.4042 ayokasystems](https://reader035.vdocuments.us/reader035/viewer/2022070420/56815e7d550346895dcd0597/html5/thumbnails/7.jpg)
Made in USA Software Development Services
•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•…
Limitations of Table Variables
![Page 8: Ayoka, L.L.C. 202 E. Border Street, Ste 334 Arlington, TX 76010 817.210.4042 ayokasystems](https://reader035.vdocuments.us/reader035/viewer/2022070420/56815e7d550346895dcd0597/html5/thumbnails/8.jpg)
Made in USA Software Development Services
•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
Conclusion
![Page 9: Ayoka, L.L.C. 202 E. Border Street, Ste 334 Arlington, TX 76010 817.210.4042 ayokasystems](https://reader035.vdocuments.us/reader035/viewer/2022070420/56815e7d550346895dcd0597/html5/thumbnails/9.jpg)
Made in USA Software Development Services
•ex_tmp_table.sql•ex_no_tmp_table.sql
Code Examples
Location Work Area Month Piece Sum
Month Hours Sum
YTD Piece Sum
YTD Hours Sum
Location 1 WA01 200 50 1500 250
Location 1 WA02 300 60 1400 300
Location 2 WA01 250 55 1600 300
Location 2 WA02 260 80 3000 320
…
![Page 10: Ayoka, L.L.C. 202 E. Border Street, Ste 334 Arlington, TX 76010 817.210.4042 ayokasystems](https://reader035.vdocuments.us/reader035/viewer/2022070420/56815e7d550346895dcd0597/html5/thumbnails/10.jpg)
Made in USA Software Development Services
Microsoft SQL ServerManagement Studio Tools
•Actual Execution Plan•Client Statistics•Database Engine Tuning Advisor
![Page 11: Ayoka, L.L.C. 202 E. Border Street, Ste 334 Arlington, TX 76010 817.210.4042 ayokasystems](https://reader035.vdocuments.us/reader035/viewer/2022070420/56815e7d550346895dcd0597/html5/thumbnails/11.jpg)
Made in USA Software Development Services
Actual Execution Plan (1)
•On SQL Server Management Studio’s tool bar
![Page 12: Ayoka, L.L.C. 202 E. Border Street, Ste 334 Arlington, TX 76010 817.210.4042 ayokasystems](https://reader035.vdocuments.us/reader035/viewer/2022070420/56815e7d550346895dcd0597/html5/thumbnails/12.jpg)
Made in USA Software Development Services
Actual Execution Plan (2)
•According to the result, determine which sql statements are the bottlenecks
![Page 13: Ayoka, L.L.C. 202 E. Border Street, Ste 334 Arlington, TX 76010 817.210.4042 ayokasystems](https://reader035.vdocuments.us/reader035/viewer/2022070420/56815e7d550346895dcd0597/html5/thumbnails/13.jpg)
Made in USA Software Development Services
Client Statistics (1)
•On SQL Server Management Studio’s tool bar
![Page 14: Ayoka, L.L.C. 202 E. Border Street, Ste 334 Arlington, TX 76010 817.210.4042 ayokasystems](https://reader035.vdocuments.us/reader035/viewer/2022070420/56815e7d550346895dcd0597/html5/thumbnails/14.jpg)
Made in USA Software Development Services
Client Statistics (2)
•Review the performance
![Page 15: Ayoka, L.L.C. 202 E. Border Street, Ste 334 Arlington, TX 76010 817.210.4042 ayokasystems](https://reader035.vdocuments.us/reader035/viewer/2022070420/56815e7d550346895dcd0597/html5/thumbnails/15.jpg)
Made in USA Software Development ServicesDatabase Engine Tuning
Advisor (1)•On SQL Server Management Studio’s tool bar
![Page 16: Ayoka, L.L.C. 202 E. Border Street, Ste 334 Arlington, TX 76010 817.210.4042 ayokasystems](https://reader035.vdocuments.us/reader035/viewer/2022070420/56815e7d550346895dcd0597/html5/thumbnails/16.jpg)
Made in USA Software Development ServicesDatabase Engine Tuning
Advisor (2)•Start Analysis
![Page 17: Ayoka, L.L.C. 202 E. Border Street, Ste 334 Arlington, TX 76010 817.210.4042 ayokasystems](https://reader035.vdocuments.us/reader035/viewer/2022070420/56815e7d550346895dcd0597/html5/thumbnails/17.jpg)
Made in USA Software Development ServicesDatabase Engine Tuning
Advisor (3)•Create recommended indexes
![Page 18: Ayoka, L.L.C. 202 E. Border Street, Ste 334 Arlington, TX 76010 817.210.4042 ayokasystems](https://reader035.vdocuments.us/reader035/viewer/2022070420/56815e7d550346895dcd0597/html5/thumbnails/18.jpg)
Made in USA Software Development ServicesDatabase Engine Tuning
Advisor (4)•ex_advisor.sql