Additional T-SQL operations in SQL Server 2008

Scalar operators are used for operations with scalar values. Transact-SQL supports numeric and Boolean operators as well as concatenation.

There are unary and binary arithmetic operators. Unary operators are + and – (as signs). Binary arithmetic operators are +, –, *, /, and %. (The first four binary operators have their respective mathematical meanings, whereas % is the modulo operator.)

You are reading part 4 from "The basic objects of T-SQL in SQL Server 2008," excerpted from Microsoft SQL Server 2008: A Beginner's Guide, by Dusan Petkovic, copyright 2008, printed with permission from McGraw-Hill Osborne Media.

Boolean operators have two different notations depending on whether they are applied to bit strings or to other data types. The operators NOT, AND, and OR are applied to all data types (except BIT). They are described in detail in Chapter 6.

The bitwise operators for manipulating bit strings are listed here, and Example 4.8 shows how they are used:

• ~ Complement (i.e., NOT)
• & Conjunction of bit strings (i.e., AND)
• | Disjunction of bit strings (i.e., OR)
• ^ Exclusive disjunction (i.e., XOR or Exclusive OR)

Example 4.8
~(1001001) = (0110110)
(11001001) | (10101101) = (11101101)
(11001001) & (10101101) = (10001001)
(11001001) ^ (10101101) = (01100100)

The concatenation operator + can be used to concatenate two character strings or bit strings.

Global variables are special system variables that can be used as if they were scalar constants. Transact-SQL supports many global variables, which have to be preceded by the prefix @@. The following table describes several global variables. (For the complete list of all global variables, see Books Online.)

NULL Values

A NULL value is a special value that may be assigned to a column. This value is normally used when information in a column is unknown or not applicable. For example, in the case of an unknown home telephone number for a company's employee, it is recommended that the NULL value be assigned to the home_telephone column.

Any arithmetic expression results in a NULL if any operand of that expression is itself a NULL value. Therefore, in unary arithmetic expressions (if A is an expression with a NULL value), both +A and –A return NULL. In binary expressions, if one (or both) of the operands A or B has the NULL value, A + B, A – B, A * B, A / B, and A % B also result in a NULL. (The operands A and B have to be numerical expressions.)

If an expression contains a relational operation and one (or both) of the operands has (have) the NULL value, the result of this operation will be NULL. Hence, each of the expressions A = B, A <> B, A < B, and A > B also returns NULL.

In the Boolean AND, OR, and NOT, the behavior of the NULL values is specified by the following truth tables, where T stands for true, U for unknown (NULL), and F for false. In these tables, follow the row and column represented by the values of the Boolean expressions that the operator works on, and the value where they intersect represents the resulting value.

Any NULL value in the argument of aggregate functions AVG, SUM, MAX, MIN, and COUNT is eliminated before the respective function is calculated (except for the function COUNT(*)). If a column contains only NULL values, the function returns NULL. The aggregate function COUNT(*) handles all NULL values the same as non-NULL values. If the column contains only NULL values, the result of the function COUNT(DISTINCT column_name) is 0.

A NULL value has to be different from all other values. For numeric data types, there is a distinction between the value zero and NULL. The same is true for the empty string and NULL for character data types.

A column of a table allows NULL values if its definition explicitly contains NULL. On the other hand, NULL values are not permitted if the definition of a column explicitly contains NOT NULL. If the user does not specify NULL or NOT NULL for a column with a data type (except TIMESTAMP), the following values are assigned:

• NULL - If the ANSI_NULL_DFLT_ON option of the SET statement is set to ON
• NOT NULL - If the ANSI_NULL_DFLT_OFF option of the SET statement is set to ON

If the SET statement isn't activated, a column will contain the value NOT NULL by default. (The columns of TIMESTAMP data type can only be declared as NOT NULL columns.)

There is also another option of the SET statement: CONCAT_NULL_YIELDS_ NULL. This option influences the concatenation operation with a NULL value so that anything you concatenate to a NULL value will yield NULL again. For instance:

'San Francisco' + NULL = NULL

The basic features of Transact-SQL consist of data types, predicates, and functions. Data types comply with data types of the ANSI SQL92 standard. Transact-SQL supports a variety of useful system functions.

The next chapter introduces you to Transact-SQL statements in relation to SQL's data definition language. This part of Transact-SQL comprises all the statements needed for creating, altering, and removing database objects.

E.4.1
What is the difference between the numeric data types INT, SMALLINT, and TINYINT?

E.4.2
What is the difference between the data types CHAR and VARCHAR? When should you use the latter (instead of the former) and vice versa?

E.4.3
How can you set the format of a column with the DATE data type so that its values can be entered in the form 'yyyy/mm/dd'?

In the following two exercises, use the SELECT statement in the Query Editor component window of SQL Server Management Studio to display the result of all system functions and global variables. (For instance, SELECT host_id() displays the ID number of the current host.)

E.4.4
Using system functions, find the ID number of the test database (Exercise 2.1).

E.4.5
Using the system variables, display the current version of the database system software and the language that is used by this software.

E.4.6
Using the bitwise operators &, |, and ^, calculate the following operations with the bit strings:
(11100101) & (01010111)
(10011011) | (11001001)
(10110111) ^ (10110001)

E.4.7
What is the result of the following expressions? (A is a numerical and B a logical expression.)
A + NULL
NULL = NULL
B OR NULL
B AND NULL

E.4.8
When can you use both single and double quotation marks to define string and temporal constants?

E.4.9
What is a delimited identifier and when do you need it?

TABLE OF CONTENTS    Part 1: The basic objects of T-SQL in SQL Server 2008    Part 2: Using T-SQL data types in SQL Server 2008    Part 3: SQL Server 2008 function types in T-SQL    Part 4: Additional T-SQL operations in SQL Server 2008

This chapter excerpt from Microsoft SQL Server 2008 : A Beginner's Guide by Dusan Petkovic, is printed with permission from McGraw-Hill Osborne Media, Copyright 2008. Click here for the chapter download or purchase the book here.

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL/Transact SQL (T-SQL) The sqlcmd utility in SQL Server How to create a SQL inner join and outer join: Basics to get started Avoid cursors in SQL Server with these methods to loop over records Implementing security audit in SQL Server 2008 New datetime data types in SQL Server 2008 offer flexibility Basic objects of T-SQL in SQL Server 2008 Using T-SQL data types in SQL Server 2008 SQL Server 2008 function types in T-SQL Using DATEADD and DATEDIFF to calculate SQL Server datetime values Manipulate column names in a SQL Server table SQL/Transact SQL (T-SQL) Research SQL Server 2008 (Katmai) SQL Server Management Studio 2008: New features, part 1 Implementing security audit in SQL Server 2008 What's new in SQL Server 2008 Reporting Services? New security features in SQL Server 2008 leave some work for you New datetime data types in SQL Server 2008 offer flexibility Basic objects of T-SQL in SQL Server 2008 Using T-SQL data types in SQL Server 2008 SQL Server 2008 function types in T-SQL SQL Server 2008 Integration Services delivers new features Change data capture in SQL Server 2008 improves BI reporting accuracy SQL Server performance and tuning Top 10 SQL Server Tips of 2008 Tutorial: Performance tuning SQL Server via queries, indexes and more SQL Server Management Studio 2008: New features, part 1 Tutorial: SQL Server indexing tips to improve performance SQL Server virtualization pros and cons: Weigh the performance impact How to configure storage in SQL Server DB with more writes than reads Avoid cursors in SQL Server with these methods to loop over records Solve SQL Server errors and more from the DBA trenches -- part 2 Tutorial: Learn SQL Server basics from A-Z FAQ: Troubleshooting SQL Server Reporting Services

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary ACID  (SearchSQLServer.com) commit  (SearchSQLServer.com) DAO  (SearchSQLServer.com) fetch  (SearchSQLServer.com) OLE DB  (SearchSQLServer.com) query  (SearchSQLServer.com) SQL  (SearchSQLServer.com) T-SQL  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary