Microsoft® SQL Server® 2012 Bible

179

Chapter 8: Data Types, Expressions, and Scalar Functions

8

'abc' + 'defg' AS Concatenation;

Result:

Addition Concatenation

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

579 abcdefg

Data from table columns and string literals may be concatenated to return custom data:

...
USE AdventureWorks
GO
SELECT 'Product: '+ Name as Product
FROM Production.Product

Result:

Proudct

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

Product: Adjustable Race

Product: All-Purpose Bike Stand

Product: AWC Logo Cap

...

One thing to note is that if you are concatenating integers and strings, you must cast or

convert the integer to a string.

Bitwise Operators

The bitwise operators are useful for binary manipulation. These aren’t typically used in

transactional databases, but they can prove useful for certain metadata operations. For

example, one way to determine which columns were updated in a trigger (code that is

executed as the result of a data insert, update, or delete, as covered in Chapter 18) is to

inspect the columns_updated() function, which returns a binary representation of those

columns. The trigger code can test columns_updated() using bitwise operations and

respond to updates on a column-by-column basis. Developers often use this operator when

they try to implement a change detection process but want to know only when certain col-

umns are changed. Of course, there are more elegant solutions, but in some specifi c cases,

this may be a viable option.

Boolean bit operators (and, or, and not) are the basic building blocks of digital electronics

and binary programming. Whereas digital-electronic boolean gates operate on single bits,

these bitwise operators work across every bit of the integer family data type (int,

smallint, tinyint, and bit) values.

Boolean and

A boolean and (represented by the ampersand character, &) returns a value of true only if

both inputs are true (or 1 for mathematical bit operations). If either or both are false (or

0 for mathematical bit operations), then the and will return a value of 1 , as follows:

c08.indd 179c08.indd 179 7/30/2012 4:21:13 PM7/30/2012 4:21:13 PM

http://www.it-ebooks.info