Column header underline length consistency

Question

I have noticed the LEFT TSQL statement within SSMS (Results to Text) returns a different length column header underline if you use a declared integer variable rather than a hard coded integer as can be seen in the following example:

SELECT Hi = LEFT('Stuart', 3)

Hi
----
Stu

DECLARE @x INT = 3
SELECT Hi = LEFT('Stuart', @x)

Hi
------
Stu

Does anyone know why this is? Also why does the first example have four underline characters rather than the expected three?

Answer 1

0

The number of underlines seems to be determined by the largest of:

The column label
The maximum length of the data
4

4 seems to be the minimum : try SELECT Hi = LEFT('Stuart', 1) - still 4

The reason for the second one returning 6, is that at parse time, there is no idea of what @x is, but the max it could be is 6 (length of 'stuart'), so that is used.

Nothing specific to the LEFT function at all.

answered Jan 11 '12 at 03:54 AM

Kev Riley ♦♦
46.1k ● 38 ● 43 ● 69

Gah. You must have submitted your answer just as I was starting on mine. Oh well...

Jan 11 '12 at 04:00 AM ThomasRushton ♦

(comments are locked)

Answer 2

If you try

DECLARE @x INT = 3 SELECT Hi = LEFT('Ethel The Aardvark', @x)

you get a line of 18 -s as a column header separator. Which implies that, for LEFT function calls where the length parameter is dynamic (ie a variable), the column header is created as the original length of the string.

If you try

SELECT Hi = LEFT('Stuart', 1)

you still get 4 -s in the header.

Try fiddling with other select statements, and you quickly find that integer columns are built to the length of the maximum number that can be held, and other similar limits elsewhere.

Unless... the column name is longer than the output - in which case the column name length takes precedence.

Why this is? So that the data can be formatted on the fly before all results are back.

Column header underline length consistency

Follow this question

Related Questions