Microsoft® SQL Server® 2012 Bible

178

Part II: Building Databases and Working with Data

The syntax of SQL keywords is not case-sensitive. The convention is to use keywords in all uppercase, all lowercase,

camel case, etc. Regardless of the method you choose, that is the method that should be adopted throughout the

entire database. This convention is not required, but it does improve the readability of the query.

Depending on the collation setting of the server or database, database, table, and column names, and even the data

itself, might be case-sensitive.

One thing that should be noted is the use of @, which is part of the variable declaration. Whenever, you

declare a variable that will be used in an expression, stored procedure, or function you must prefi x it

with an @ symbol.

Chapter 9, “Merging Data with Joins, Subqueries, and CTEs,” covers subqueries. Chapter 18, “Building User-Defi ned

Functions,” covers user-defi ned functions.

Operators

Although the meaning of many of these expression constants, operators, and expressions is

obvious and common to other programming languages, a few deserve special mention.

The division mathematical operator (/) is a common source of errors when integers are

divided because there is an implicit truncation of values. For instance, 17/9 gives a result

of 1; although, it is almost 2 (which 18/9 would yield). If you are interested in obtaining a

true decimal value, simply modify the query to this, 17/9, and your result will be 1.888888.

Simply adding a decimal to one of the values in the expression tells SQL server to return a

more precise value.

The modulo mathematical operator (%) returns only the remainder of the division. The

floor()and ceiling() mathematical functions, which return the integer rounded down

or up, respectively, are related to it. The floor() function is the SQL Server equivalent of

the BASIC int() function:

SELECT 15%4 AS Modulo,

FLOOR(1.25) AS [Floor], CEILING(1.25) AS [Ceiling];

Result:

Modulo Floor Ceiling

----------- ----- -------

3 1 2

The + operator is used for both mathematical expressions and string concatenation. This oper-

ator is different from the Visual Basic symbol for string concatenation, the ampersand (&):

SELECT 123 + 456 AS Addition,

c08.indd 178c08.indd 178 7/30/2012 4:21:12 PM7/30/2012 4:21:12 PM

http://www.it-ebooks.info