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 http://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.