Examples from daily work

1
Examples from daily work

• by Bjørn D. Jensen
• Bjorn.D.Jensen_at_gmail.com

2
Solutions for typical problems

• Multi row constraints
• SQL XML
• Coding Style
• Layout
• Instrumentation
• Catch me if you can
• Dont use money
• SQL is not Cobol
• Test
• Deployment
• SAN

3
Multi row constraints

• Unique indexes/constraints
• Selective uniqueness
• No duplicate lists

4
SQL XML

• XML -gt result set (shredding XML)
• Resultset -gt XML (FOR XML)

5
Coding Style

• Readability is key for understanding
• Abbreviations
• Format
• Naming conventions
• Comments
• Fit one monitor page
• Use standard SQL algebra
• Avoid procedural thinking

6
Coding style

• Traceability who did what?
• Header in code
• Generated documentation
• tsqldoc

7
Coding Style

• Code instrumentation
• Debugging
• Visual Studio http//support.microsoft.com/kb/3165
  49
• DIY wrapper to print statements
• Batch log without, error tracing will be hard

8
Coding Style

• Test
• OS/HW platform and version
• Realistic amount of data
• Realistic data content
• Database version
• Concurrency
• Structure
• Functional test
• Unit test

9
Coding Style

• Deployment
• Team Edition for DB professionals
• DIY alternatives

10
SAN performance matters

• IO

11
Resources

• Database In Depth, Chris J. Date, OReilly
• http//awads.net/wp/2005/11/14/do-sql-iso-standard
  s-matter/
• http//en.wikipedia.org/wiki/V-Modell
• http//forums.microsoft.com/MSDN/ShowPost.aspx?Pos
  tID2237640SiteID1
• http//radio.weblogs.com/0100190/stories/2002/07/2
  5/sixRulesOfUnitTesting.html
• http//tsqlunit.sourceforge.net/
• http//www.codeproject.com/useritems/TSQL_coding.a
  sp
• http//www.codeproject.com/useritems/T-SQL_Coding_
  Standard.asp
• http//www.simple-talk.com/content/article.aspx?ar
  ticle367
• http//www.simple-talk.com/sql/t-sql-programming/c
  lose-those-loopholes---testing-stored-procedures--
  /
• http//www.sommarskog.se/
• http//www.sqlservercentral.com/columnists/kpowers
  /thegocommandandthesemicolonterminator.asp
• http//www.sqlservercentral.com/columnists/MWittro
  ck/tsqldoc.asp
• http//www.webservertalk.com/archive137-2007-1-178
  6279.html
• http//www.webservertalk.com/archive137-2007-1-178
  6279.html
• http//www.windowsecurity.com/articles/Hacking_an_
  SQL_Server.html
• ms-help//MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a
  5711f5-4f6d-49e8-b1eb-53645181bc40.htm (BOL on
  Using TryCatch)
• SQL Programming Style, Joe Celko, Morgan Kaufmann
• http//www.sqlmag.com/Articles/ArticleID/26031/260
  31.html?Ad1