SQL Server discards SPACE during GROUP BY

Question

It looks like SQL Server (tested for 2008 R2) makes RTRIM columns in columns in GROUP BY . Has anyone noticed this? Did I miss something?

Two choices return the same result set in the query below, which should not be the way I think.

 declare @t table(Name varchar(100), Age int) insert into @t values ('A', 20) insert into @t values ('B', 30) insert into @t values ('C', 40) insert into @t values ('D', 25) insert into @t values (' A', 21) insert into @t values ('A ', 32) insert into @t values (' A ', 28) select Name, count(*) Count from @t group by Name select rtrim(Name) RtrimmedName, count(*) Count from @t group by rtrim(Name) 

Please let me know your thoughts ...

Answer 1

In fact, this is done the other way around, but the observed effects are the same.

When comparing two lines of unequal length, one of the SQL rules (standard, not just SQL Server) is that the shorter line is filled with spaces until it is the same length, and then the comparison is performed.

If you want to not be surprised, you need to add a space character at the end of each line.

---

In fact, checking the standard text , it seems that there are two options:

4.6 Type conversion and data type transfer

...

When values ​​of unequal length are compared, if the comparison sequence for comparison has the NO PAD attribute, and a shorter value is prefixed with a longer value, then the shorter value is considered smaller than the longer value. If the comparison sequence for comparison has the PAD SPACE attribute, for comparison purposes, the shorter value effectively extends the length longer by concatenating <space> s on the right.

But all the SQL Server mappings that I know of are PAD SPACE .

Answer 2

This is easier to see:

 declare @t table (Name varchar(100), Age int) insert @t values('A', 20),('B', 30),('C', 40),('D ', 25) ,(' A', 21),('A ', 32),(' A ', 28),('D ',10); select Name, Replace(Name,' ','-'), count(*) Count from @t group by Name -- NAME COLUMN_1 COUNT A -A 2 A A- 2 BB 1 CC 1 D D-- 2 

Note the space between A and the dot. He chose a 1-space version over 0-space.
Note also that the group D chooses the one that has 2 trailing spaces over 4.

So no, it does not perform RTRIM. However, this is a somewhat bland mistake, because it arbitrarily selected one of the two columns (the one that it came up with first) as a result of GROUP BY, which could throw you out if it made sense.

Answer 3

I know this is an old topic, but it might help someone else. I used the following for a group to correctly identify differences in trailing spaces:

 declare @t table(Name varchar(100), Age int) insert into @t values ('A', 20) insert into @t values ('B', 30) insert into @t values ('C', 40) insert into @t values ('D', 25) insert into @t values (' A', 21) insert into @t values ('A ', 32) insert into @t values (' A ', 28) select Name, count(*) Count from @t group by Name, DATALENGTH(Name) 

By adding an additional DATALENGTH for the group, he will also determine the difference in the length of the items.