Expressions (Database Engine)

An expression is a combination of identifiers, values, and operators that SQL Server can evaluate to obtain a result. The data can be used in several different places when you are accessing or changing data. Expressions can be used, for example, as part of the data to retrieve in a query, or as a search condition when looking for data that meets a set of criteria.

An expression can be any of the following:

  • Constant

  • Function

  • Column name

  • Variable

  • Subquery

  • CASE, NULLIF, or COALESCE

An expression can also be built from combinations of these entities joined by operators.

In the following SELECT statement, for each row of the result set, SQL Server can resolve LastName to a single value. Therefore, it is an expression.

USE AdventureWorks2008R2;
GO
SELECT LastName 
FROM Person.Person;

An expression can also be a calculation such as (price * 1.5) or (price + sales_tax).

In an expression, enclose character and datetime values in single quotation marks. In the following SELECT statement, the character literal B% that is used as the pattern for the LIKE clause must be in single quotation marks:

USE AdventureWorks2008R2;
GO
SELECT LastName, FirstName 
FROM Person.Person 
WHERE LastName LIKE 'Bai%';
GO

In the following SELECT statement, the date value is enclosed in quotation marks.

USE AdventureWorks2008R2;
GO
SELECT c.FirstName, c.LastName, e.HireDate 
FROM Person.Person c JOIN HumanResources.Employee e 
ON c.BusinessEntityID = e.BusinessEntityID
WHERE e.HireDate = 'July 1, 2007';
GO

In the following example, more than one expression is used in the query. For example, Name, SUBSTRING, ProductNumber, ListPrice, and 1.5 are all expressions.

USE AdventureWorks2008R2;
GO
SELECT Name, 
   SUBSTRING('This is a long string', 1, 5) AS SampleText, 
   ProductNumber, 
   ListPrice * 1.5 AS NewPrice
FROM Production.Product;