Chapter 9. Data Types, Expressions, and Scalar Functions


  • Working with expressions and scalar functions

  • Using logic within a query

  • Working with nulls, strings, and dates

When my son, David, was younger he built incredible monster trucks and gizmos out of K'NEX construction pieces. If you aren't familiar with K'NEX, do a Google image search and see the wild things that kids can build with it.

What makes K'NEX cool is that nearly any piece can plug into any other piece. This interconnectivity makes K'NEX flexible. In the same way, the interconnectivity of SQL expressions and functions makes SQL so flexible and powerful.

Expressions can retrieve data from a subquery, handle complex logic, convert data types, and manipulate data. If the secret to being a competent SQL database developer is mastering SQL queries, then wielding expressions and scalar functions is definitely in the arsenal.

An expression is any combination of constants, functions, or formulas that returns a single value. Expressions may be as simple as a hard-coded number, or as complex as a case expression that includes several formulas and functions.

Expressions may be employed in several places within the SQL syntax. Nearly anywhere a value may be used, an expression may be used instead. This includes column values, JOIN ON clauses, WHERE and HAVING clauses, and ORDER BY columns. Expressions can't be substituted for object names such as table names or column names.

Building Expressions

You can construct SQL expressions ...

Get Microsoft® SQL Server® 2008 Bible now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.