Constants in Query Result Sets

Constants are not usually specified as a separate column in a result set. It is usually more efficient for an application itself to build the constant value into the results when they are displayed, rather than requiring the server to incorporate the constant value in every result set row returned across the network.

Exceptions to this general rule include:

  • Stored procedures may be called by many different applications or scripts. These procedures do not have access to the constant value that should be incorporated in the results. The SELECT statement in the procedure itself should then specify the constant as part of the select list.

  • When a site wants to enforce a formatting or display standard, the format can be built into a view or stored procedure.

  • A SELECT statement may be executed from a script or a tool that does not support merging constants with a result set after the result set has been returned from the server.

Character string constants are included for proper formatting or readability when character columns are concatenated. This example combines the LastName and FirstName columns into a single column. The character string ', ' separates the two parts of the name in the new combined column:

SELECT LastName + ', ' + FirstName AS ContactName
FROM AdventureWorks2008R2.Person.Person
ORDER BY LastName, FirstName ASC;

Community Additions