Database Programming: What I’ve Learned About SQL Server 2008 (with a little on SQL Server 2005 thrown in)

With SQLRAP 2.5 out the door a couple of weeks ago, I’ve been free to turn to another project, SIPA, an internal effort to automate storage and retrieval of our group’s diverse intellectual property catalog. I’m acting as both the database architect and one of the database implementers on the project.

This is my first significant from-scratch development effort under SQL Server 2008, and I’m incredibly impressed:

• the MERGE statement, which I among many other have been asking for since shortly after I got to Microsoft in 1999, is one of the coolest things since sliced bread. My pre-MERGE approach to data maintenance was relatively bullet-proof and entailed roughly 100 lines of code in half a dozen stored procedures. By using a CTE with a MERGE and a TRY-CATCH block, in a single statement I can:
  • shred the XML document containing user input;
  • update existing records;
  • modify existing records;
  • delete records (either logically or physically);
  • create a driver table for child processes containing PKs of inserted records (isolating the PKs of just-inserted records was a huge challenge until the OUTPUT clause was introduced in SQL Server 2005).
• I built my first indexed views on this project, to support SIPA’s search functionality. Indexed views were first introduced in SQL Server 2005, but I’d never had occasion to use one before SIPA. While the performance of my search component improved when I used indexed views, I got better results when I built and indexed warehouse-like 1NF tables to support the search.
• I worked with full-text search for the first time. While I was very impressed with its capabilities and performance, its “fuzzy” aspects were inappropriate for our application.

Every time I turn around, it seems as though I find another arrow that the SQL Server dev team has added to my quiver. When one combines the newer tools in synergistic ways, one gets code which is compact and powerful, yet easy to understand.

Coding in SQL Server 2008 is a lot of fun. As I get deeper into SIPA, I’ll share more of what i learn.

-wp

---

this copyrighted material was originally posted at https://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work. however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites.

Comments

• Anonymous
  July 27, 2009
  Hi Ward, Interesting post. Can you share some information on how big the data sets you used MERGE for were? In my attempts to use it to date I've found its performance to be less than ideal; between twice as slow and 100x as slow as my pre-MERGE methodology (using a temp table). BTW, indexed views were introduced in SQL Server 2000 :-)

• Anonymous
  July 27, 2009
  Also, regarding the idea of creating a "driver table", based on just-inserted keys, did you consider Change Tracking? It might be interesting to work something up using the CHANGE_TRACKING_CONTEXT feature, where the parent process figures out the most recent change version, then generates a unique context and does its work. Then the child process can take the version and the context and figure out what work the parent process did based on the change data.

• Anonymous
  July 28, 2009
  Can you please elaborate (perhaps in another blog) on your "CTE with a MERGE and a TRY-CATCH block" code? I have done something similar in SQL 2005 before, and am curious how you approached it.

• Anonymous
  July 29, 2009
  Hi Ward.  I believe indexed views were introduced in SQL 2000.  

• Anonymous
  July 31, 2009
  Great post, Ward!  My primary server will be upgraded to 2008 in the next couple of months, and MERGE is one of the features I'm most looking forward to (after filtered indexes, of course!).   Also, I've just recently started using indexed views myself and was wondering if you could expound on the improved performance you received in the 1NF table vs the indexed view.  

• Anonymous
  August 23, 2009
  Thanks for sharing. I just starting online. Its really need. What do you think about www.site2you.com? Is that really possible to make a website pretty quick and is that good quality CMS does they using?