Randomly selected quiz question (want a different question? Click here):
I never know in advance what my next quiz question will be about, but this week the topic came to me easily. I wasted an evening trying to solve a wacky problem while getting some SQL to run on a system (a separate box) that was being prepared for demo purposes.
This turned out to be one of those cases where it's proven true that there's always something left to learn (as I mentioned last month).
The problem first surfaced when using sp_autoformat to format the contents of a table (if you don't know sp_autoformat, you should get acquainted -- try running 'sp_autoformat sysusers' as an example of what it does)
sp_autoformat is supposed to format each column as narrow as possible, given the actual data values it contains, trimming off trailing spaces from [var]char columns, among other things.
My problem was that sp_autoformat was formatting all [var]char columns with a bunch of spaces at the end, which is exactly what should NOT happen. Worse, it really messed up the formatting of some output that was supposed to fit on an 80-character window.
After checking my own SQL code was not at fault, I zoomed in on what sp_autoformat was doing (try adding the @trace=4 parameter). It seemed sp_autoformat was doing everything right, yet every column was still formatted longer than the longest value it contained. Weird.
Eventually I could simplify the problem and reproduce it without sp_autoformat, as follows:
1> select substring('abc', 1, 3)
(1 row affected)
Can you spot the problem? I'm doing a substring() whose result should be 3 characters long, but the resulting column is in fact 6 characters.
This seemed to defy all logic. What on earth is going on here?
Click here for the answer.
More ASE quiz questions: 2013 - 2012 - 2011 - 2010 - 2009 - 2008 - 2007 - 2006 - 2005 - 2004 - 2003 - 2002 - 2001
For a randomly selected quiz question, click here.