sql server 2008 - non-integer constant in ORDER BY

问题:

Upgrade advisor says

"Non-integer constants are not allowed in the ORDER BY clause in 90 or later compatibility mode".

But, when I try the below statement in SQL Server 2008, it works just fine. So, my questions is - What exactly is a non-integer constant?

select 
    POS_NO
    ,EMP_NO
    ,ORG_NAME
    ,EMP_LAST_NAME + ', ' + EMP_FIRST_NAME AS "Name" 
FROM 
    dbo.MyEmpTable 
ORDER BY
    "Name"

回答1:

Here is what the MSDN have to say about character constant that are in a sense the non-integer constant

Character string constants are enclosed in single quotation marks and include alphanumeric characters (a-z, A-Z, and 0-9) and special characters, such as exclamation point (!), at sign (@), and number sign (#). Character string constants are assigned the default collation of the current database, unless the COLLATE clause is used to specify a collation. Character strings typed by users are evaluated through the code page of the computer and are translated to the database default code page if it is required.

1) Non-integer constants are ... constants that are not integer number.

Example: 'string1' represents a string constant

0x01 represents a varbinary constant

{ts '2015-02-26 06:00:00'} represents a datetime constant

1.23 represents a numeric constants

2) So single quotes are used to define a string constants / character string constants but SQL Server allows also to use single quotation marks use also as column identifier delimiter:

SELECT ... expression AS 'Column1'
FROM ...

In this context is clear that 'Column1' is a column identifier but when used in ORDER BY : ORDER BY 'Column1' it generates confusion because SQL Server doesn't knows if it represents a string literal (character string constant) or it represents a column identifier / column name.

3) SQL Server allows to use use integer constants in ORDER BY thus SELECT ColA, ColB, ColC FROM ... ORDER BY 2. In this case 2 is the index's of column ColB. Also, if you want to sort by ColB and ColC you could use ORDER BY 2, 3. Usage of column's index is considered to be bad practice.

4) In this case I would use

ORDER BY EMP_LAST_NAME + ', ' + EMP_FIRST_NAME