A bit of old news, as I return to the blog in earnest after some time away.. Last summer, in the posts Database Programming: What I’ve Learned About SQL Server 2008 (with a little on SQL Server 2005 thrown in) and Database Programming: Why I Like MERGE , I commented that I’d been able to build a MERGE...

UPDATED 11:11p 7 September 2009: Based on my latest exchange with Adam (see the comments for this post), compound operators should be avoided for string concatenation due to the ORDER BY bug discused in KB 287515 . The XML variant below is the preferred approach for string concatenation, but the compound...

Courtesy of Jens Suessmeyer comes this little gem purloined from an internal discussion; cut-and-paste this into SQL Server Management Studio and see if you can answer the questions in the comments before you execute the script: --   create and populate a table variable DECLARE @Demonstration TABLE...

Hot on the heels of yesterday’s MERGE discussion comes a complementary (and far more detailed) take from Adam Machanic .  It’s a nifty technical discussion, under the banner of an outstanding Dr. Strangelove pun. To see some of the true power of MERGE (and to share Adam’s joy in releasing his MERGE...

In the wake of last month’s post on my new development efforts in SQL Server 2008, several of you have asked me to go into further detail.  This post is my first effort to answer those questions; right now, we’ll focus on these two , which relate to this statement: By using a CTE with a MERGE and...

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...

Now go away, or we shall taunt you a second time. - John Cleese as a French knight (picture at left); Monty Python and the Holy Grail One of the reasons I started blogging over four years ago was my conviction that the coding challenges with which my employer was presenting me, while stimulating and...

One of the most popular posts in the history of this little corner of the Internets is one from August, 2005, which describes a method for accessing stored procedure output in a SELECT statement which I’ve come to refer to as “ the OPENROWSET trick .” On the occasion of this blog’s 750th post(!),...

And I remember what she said to me How she swore that it never would end I remember how she held me, oh so tight Wish I didn't know now what I didn't know then Bob Seger, Against the Wind All you need is love Love is all you need John Lennon and Paul McCartney, All You Need is Love My pal Jimmy May apparently...

As we progress towards the Ides of March, one of my best friends-who-I’ve-never-met, Jimmy May , notes our annual transition to Daylight Savings Time , in a post he generously concludes with a link to one of this very blog’s oldies but goodies . In his blog’s title, Jimmy humbly refers to himself...

Jens Suessmeyer is a Microsoft Consultant in Germany who frequently shares his useful techniques and insights both inside Microsoft and in the community at large.  In his latest post , he answers a colleague’s question with a simple yet thorough repro which proves the titular point. Here’s the money...

Never let it be said that Adam Machanic lacks style.. Adam left a comment on yesterday’s revisiting of the XML String Concatenation Trick, announcing his T-SQL Challenge: Grouped String Concatenation contest. Read Adam’s post to get all of the particulars for the event.  I hope to be able to play...

I had a couple of extra minutes today and found this post on Linchi Shea’s blog ; I wanted to commend to all of you who’ve taken in the programming methodology and set-based thinking discussions in this little corner. While I disagree some with the point in the main post about views (JOINing views...

Fulfilling my promise at TechEd , I’ve finally completed a self-directed demonstration of the database versioning techniques I first presented at last year’s TechEd and which was alluded to during this year’s TechEd SQL Tricks presentations. It’s published here on my resource page at MSDN Code Gallery...

Well, it took me long enough to figure out how to get this done, didn’t it?  The good news is that I have a brand new tool in my arsenal..  the bad news is that I burned a bunch of precious rural bandwidth trying to upload this material to inappropriate platforms. The TechEd EMEA 2008 Developers...

UPDATED 22 November 2008 with Code Gallery URL. A ZIP file of the scripts from the SQL Tricks: Insights from Microsoft IT presentation I’m giving in Barcelona this week is available for download here . The tricks will be familiar to long-time denizens of this space, as they’ve all been discussed here...

Marc left a great question on a Pond's Laws post from July of 2006 : Hi, I have a flashcard system that randomly pulls a word from the database. I also have a testing module that allows a user to test him or her self on words. For each word the user answers incorrectly it is flagged. I want...

Almost three years after the post was originally published in October of 2005, Mark Johansen, author of A Sane Approach to Database Design (an approach I trust it's clear I favor) has left a terrific comment on the Do Not Fear NULLs article which is worthy of your attention. The "money quote" comes...

One of the comments I accidentally deleted earlier this afternoon posed the following question (paraphrased): I understand that TOP with ORDER BY makes no sense, but what about when I use different values for TOP and I ORDER BY a column that has duplicate values? I don't get the same results every...

Kalen Delaney has one of the most consistently informative SQL Server blogs in all of the Internets, and unless I miss my guess, it was also she who left the first of the very supportive comments I've received in the wake of my recent series of personal posts. Thank you, Kalen, and everyone else for...

It's an especially Good Friday when we can close the loop on a technical conversation, and I believe that our modifications to The Technique That Lance Found , also discussed here and here , are complete.  Scott Whigham left a comment on the most recent post offering an XML implementation that will...

When last we checked in on The Technique That Lance Found , Adam had noted that the method entitizes XML special characters, a state of affairs which limits its utility somewhat.  I tried to leverage Tony Rogerson's technique , which Adam passed along in his comment, but it was late and I was tired...

A find shared by one friend leads to correspondence from another.. The redoubtable Adam Machanic left a comment on The Technique That Lance Found which points out that special XML characters in a string will get entitized. As usual, Adam is correct. If we make a subtle change to the contents of...

Courtesy of my good friend and once-and-always colleague, Lance Larsen, who writes: I recently ran into this little trick. Joining two tables having a one-to-many relationship and stuffing a set of column values from the many side into a single column on the one side. Works only for one base row...

Spazecaze discovered a February, 2006 discussion of order of operations and poses the following question in its comment area : So how would you go about designing a database that allows for end user defined fields? For instance, a system is built to allow users to enter data into fields on...