Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

Browse by Tags

Related Posts
  • Blog Post: Finally, An Answer (and a mea culpa) For Adam Machanic

    I've once again been frightfully neglectful of this blog, as the holidaze, shifting work assignments, and the occasional personal event have combined to render me a poor custodian. The good news is that I've got a backlog of tidbits to share. Before we get started with those, though, I'd like to respond...
  • Blog Post: A Note From Binh's Customer

    Binh Cao's customer left a comment on yesterday's post on global temp tables. Here's the money quote: My research confirms that global temp table does allow the plan to be shared among processes. However now we are forced to deal with the problem of ownership of data within the global temp table...
  • Blog Post: Database Programming: A More Performant Alternative To COLUMNS_UPDATED()

    (updated 18 February 2006 to clarify version information) A recent discussion with several colleagues reminded me of a hard-won insight I've been meaning to share here. This involves the code that we originally discussed here . I know the following to be true for SQL Server 2000. I've not tested the...
  • Blog Post: Database Programming: Prime Number Methodology

    Rob Farley 's back with two comments in regard to my prime number methodology: Ward - how do you justify having the 'initial fill of sieve' in your tweak of Hugo's? If you pull that stuff out (as per my first post ), it takes much longer. Rob ( new SQL MVP , by the way!) Like... just populate...
  • Blog Post: Loading An XML Document Into SQL Server: Another OPENROWSET Trick

    Have you ever had an XML document in a file that you needed to get into a database column quick-and-dirty? Well, another OPENROWSET trick is ready to come to our rescue. Up until I was shown this trick, every time I needed to import XML into a data column, I'd load up a copy of the XML in SQL Server...
  • Blog Post: Database Programming: A Prime Number Contender From Down Under

    Denis' Prime Number Challenge just won't die. I think this topic has spurred more dialog than any other in this blog's 15-month, 180-odd post history. Just imagine if I could've harnessed this global outpouring of SQL skills for something with commercial potential.. J Rob Farley has two posts on his...
  • Blog Post: Cross-Database Connectivity: An Answer For Kyle

    Kyle Schoonover, a former colleague in MSIT, posed the following questions in a comment on October’s cross-version database attachments post : Ward, I'm currently working in MySQL and writing conversion scripts to convert a MySQL database to a SQL 2005 database.  I have also been using OpenQuery...
  • Blog Post: Database Programming: Why I, Like, May Never Write Another LIKE, Like, Ever Again

    [ UPDATE 30 October 2007 : There are significant ambiguities in this post which are addressed in the comments and in this follow-up , which I recommend reading after you've read this post and its comments. -wp] I've been doing some performance tuning work over the last couple of days, and I've found...
  • Blog Post: Optional Parameters: Adam Hopes I'm Joking, But The Joke's On Me

    This is absolutely my last gasp on optional parameters. Adam Machanic left some syntax in a comment here which shows just how far off base my "last gasp" in this post was. My syntax produced 594 logical reads to resolve a query while the equivalent dynamic SQL handled the same issue in six logical...
  • Blog Post: Database Programming: The String Concatenation XML Trick, Revisited

    UPDATED 27 February 2009 for spelling I’ve got to pay more punctual attention to my comment pool.. RBarryYoung’s movingsql.com will be on my blog roll shortly after I get this posted (second attempt; first thwarted by a laptop hang. I have suspended unit testing of SQLRAP 2.5 changes until I’m...
  • Blog Post: Database Design: Do Not Fear NULLs

    Last week, I found myself simultaneously confounded and fascinated by a conversation on the internal Microsoft SQL Server discussion list. If you know me at all, you know that I had to toss in my two cents, but I’m getting ahead of myself. The thread started with a question from an application...
  • Blog Post: Two Programming Tricks: Counting Characters In A String and RETURN CASE

    A couple of tricks here, from some recent work: a user-defined function to count the number of characters in a string, and a new (for me) syntax discovery: RETURN CASE. Here's what I came up with for the user-defined function: CREATE FUNCTION dbo . fnCountCharactersInString ( @StringToTest nvarchar...
  • Blog Post: Database Programming: Yet More On Optional Parameters

    Ever since I mentioned that Calvin Hsia clued me in to the size of this blog's audience, the comment traffic here has seen a notable spike. I love it.. In case you've missed it in the comment stream from the original post in this thread, Adam Machanic left a comment commending an article on this topic...
  • Blog Post: Database Programming: Feedback Requested -- Which Syntax Is More Maintainable?

    This might be something of an atypical post for this blog in that it's a solicitation of feedback rather than a "sermonette," but so be it. Perhaps this is the start of a positive trend.. :-) I was looking over this code from a previous post : UPDATE s SET SupplementTitle = CASE NULLIF ( i . SupplementTitle...
  • Blog Post: See You Even More In Barcelona: Two Additional Chalk Talk Sessions Added

    I'm pleased to report that the organizers of TechEd have scheduled repeat offerings of two of my sessions (so much for my earlier belief that the schedule was finalized *grin*): Set-Based Thinking for the T-SQL Developer: Insights from Microsoft IT will also be presented in Room 132, 3:45pm-5pm...
  • Blog Post: Database Programming: Optional Parameters Revisited

    Frequent visitors might recall a thread earlier in the spring regarding optional parameters to a stored procedure (the earlier posts are here , here , and here ). The question had basically boiled down to whether there was single-statement syntax available which would preclude the necessity to use dynamic...
  • Blog Post: Database Versioning Demonstration Uploaded

    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...
  • Blog Post: Database Programming: A Brief Note On The Second OPENROWSET Trick

    Back in March, I presented the " second OPENROWSET trick ", a technique for loading a flat file into a single column of a single row of a table. In researching a question posed by Rob Jarratt of Microsoft Consulting Services in the UK, I discovered that the OPENROWSET extensions that make that trick...
  • Blog Post: 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...
  • Blog Post: Second Shot with a Side of Career Assist: Just What the Doctor Ordered

    In light of the current state of the economy (not to mention recent news in my own back yard (I can still call it that since we continue, against our deeply held wishes, to own a house in Redmond)), I make it a point to count my blessings every day.  A loving and supportive family is primary among...
  • Blog Post: A Solution For Stripping Invalid XML Characters From varchar/text Data Structures

    One of the more subtle aspects of converting (n)varchar or (n)text data to XML is the fact that XML is choosy about which characters are permitted and (n)/varchar/(n)text is not. Any T-SQL programmer who runs conversions of this type is likely to run into this issue. Here's a code block that resolves...
  • Blog Post: Database Programming: User-Defined Functions And Linked Server Connections

    This post addresses an edge-case programming issue, but if you've ever run into into it, this may be a useful trick to have in your back pocket. This discussion applies to both SQL Server 2000 and SQL Server 2005; in SQL Server 2005, all databases involved must have SET TRUSTWORTHY ON run against them...
  • Blog Post: 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...
  • Blog Post: Database Programming: Nearing Capitulation On Optional Parameters

    Adam and Tony both left comments indicating that either flavor of my proposed optional parameter syntax will result in a table scan, and that performance will (to appropriate Tony's characterization) suck. That's quite correct. There's one more syntax construction I need to take a look at, and I'll...
  • Blog Post: Database Programming: The OPENROWSET Trick, Revisited

    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(!),...