Five Things About sp_.. (okay.. Four Things and a Closing Argument)

Celebrating the first day of Summer with the first technical post on the blog since mid-Winter (apologies for my absence).. Jens has a great post regarding the internal behavior of various forms of sp_-named programmability objects.  Some SQLRAP-related research led me to take a different tack, and I wanted to share the results of that…


MERGE, XML Access, and a CTE: Not So Fast

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…


Database Programming: A New String Concatenation Contender for SQL Server 2008

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 operator remains a viable alternative in other scenarios….


Database Programming: Did You Know IN Can Do This?

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 (     ID INT IDENTITY(1,1),     Col1 INT,    …


Adam Machanic Likes MERGE, Too

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-related stress), click on over! …


Database Programming: Why I Like 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…


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…


Database Programming: Using the .modify() Method Against an XML Variable in SQL Server 2008

Make a note..  here’s my first useful programming tip for SQL Server 2008 (and yes, there will be more to come; I’m about a week and a half into my first SQL Server 2008 development project).. Way back in February of aught-seven, I shared Jerry’s solution for running the .modify() method against an XML memory…


Changing the Default Collation When No User Databases are Present

UPDATED 13 April 2009: The SAPWD parameter sets the sa password; use BLANKSAPWD if you don’t wish to set one.  I’ve updated the seventh paragraph below accordingly. One of our responsibilities in the ITOE IP Architects group (the artists formerly known as the Exchange, SQL Server, and Windows Centers of Excellence) is to review the…