Additional T-SQL operations in SQL Server 2008

Scalar Operators

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

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.)

Variable	Explanation
@@CONNECTIONS	Returns the number of login attempts since starting the system.
@@CPU_BUSY	Returns the total CPU time (in units of milliseconds) used since starting the system.
@@ERROR	Returns the information about the return value of the last executed Transact-SQL statement.
@@IDENTITY	Returns the last inserted value for the column with the IDENTITY property (see Chapter 6).
@@LANGID	Returns the identifier of the language that is currently used by the database system.
@@LANGUAGE	Returns the name of the language that is currently used by the database system.
@@MAX_CONNECTIONS	Returns the maximum number of actual connections to the system.
@@PROCID	Returns the identifier for the stored procedure currently being executed.
@@ROWCOUNT	Returns the number of rows that have been affected by the last Transact-SQL statement executed by the system.
@@SERVERNAME	Retrieves the information concerning the local database server. This information contains, among other things, the name of the server and the name of the instance.
@@SPID	Returns the identifier of the server process.
@@VERSION	Returns the current version of the database system software.

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.

AND	T	U	F		OR	T	U	F		NOT
T	T	U	F		T	T	T	T		T	F
U	U	U	F		U	T	U	U		U	U
F	F	F	F		F	T	U	F		F	T

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

Conclusion

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.

Exercises

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?

SQL Server 2008 function types in T-SQL

Transact-SQL Functions

Transact-SQL functions can be either aggregate functions or scalar functions. The following sections describe these function types.

Aggregate functions

Aggregate functions are applied to a group of data values from a column. Aggregate functions always return a single value. Transact-SQL supports several groups of aggregate functions:

You are reading part 3 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.
• Convenient aggregate functions
• Statistical aggregate functions
• User-defined aggregate functions
• Analytic aggregate functions

Statistical and analytic aggregates are discussed in Chapter 24. User-defined aggregates are beyond the scope of this book. That leaves the convenient aggregate functions, described next:

• AVG - Calculates the arithmetic mean (average) of the data values contained within a column. The column must contain numeric values.
• MAX and MIN - Calculate the maximum and minimum data value of the column, respectively. The column can contain numeric, string, and date/time values.
• SUM - Calculates the total of all data values in a column. The column must contain numeric values.
• COUNT - Calculates the number of (non-null) data values in a column. The only aggregate function not being applied to columns is COUNT(*). This function returns the number of rows (whether or not particular columns have NULL values).
• COUNT_BIG - Analogous to COUNT, the only difference being that COUNT_BIG returns a value of the BIGINT data type.

The use of convenient aggregate functions with the SELECT statement are described in detail in Chapter 6.

Scalar functions

In addition to aggregate functions, Transact-SQL provides several scalar functions that are used in the construction of scalar expressions. (A scalar function operates on a single value or list of values, as opposed to aggregate functions, which operate on the data from multiple rows.) Scalar functions can be categorized as follows:

• Numeric functions
• Date functions
• String functions
• System functions
• Metadata functions

The following sections describe these function types.

Numeric functions

Numeric functions within Transact-SQL are mathematical functions for modifying numeric values. The following numeric functions are available:

Function	Explanation
ABS(n)	Returns the absolute value (i.e., negative values are returned as positive) of the numeric expression n. Example: SELECT ABS(–5.767) = 5.767, SELECT ABS(6.384) = 6.384
ACOS(n)	Calculates arc cosine of n. n and the resulting value belong to the FLOAT data type.
ASIN(n)	Calculates the arc sine of n. n and the resulting value belong to the FLOAT data type.
ATAN(n)	Calculates the arc tangent of n. n and the resulting value belong to the FLOAT data type.
ATN2(n,m)	Calculates the arc tangent of n/m. n, m, and the resulting value belong to the FLOAT data type.
CEILING(n)	Returns the smallest integer value greater or equal to the specified parameter. Examples: SELECT CEILING(4.88) = 5 SELECT CEILING(–4.88) = –4
COS(n)	Calculates the cosine of n. n and the resulting value belong to the FLOAT data type.
COT(n)	Calculates the cotangent of n. n and the resulting value belong to the FLOAT data type.
DEGREES(n)	Converts radians to degrees. Examples: SELECT DEGREES(PI()/2) = 90.0 SELECT DEGREES(0.75) = 42.97
EXP(n)	Calculates the value e^n. Example: SELECT EXP(1) = 2.7183
FLOOR(n)	Calculates the largest integer value less than or equal to the specified value n. Example: SELECT FLOOR(4.88) = 4
LOG(n)	Calculates the natural (i.e., base e) logarithm of n. Examples: SELECT LOG(4.67) = 1.54 SELECT LOG(0.12) = –2.12
LOG10(n)	Calculates the logarithm (base 10) for n. Examples: SELECT LOG10(4.67) = 0.67 SELECT LOG10(0.12) = –0.92
PI()	Returns the value of the number pi (3.14).
POWER(x,y)	Calculates the value x^y. Examples: SELECT POWER(3.12,5) = 295.65 SELECT POWER(81,0.5) = 9
RADIANS(n)	Converts degrees to radians. Examples: SELECT RADIANS(90.0) = 1.57 SELECT RADIANS(42.97) = 0.75
RAND	Returns a random number between 0 and 1 with a FLOAT data type.
ROUND(n, p,[t])	Rounds the value of the number n by using the precision p. Use positive values of p to round on the right side of the decimal point and use negative values to round on the left side. An optional parameter t causes n to be truncated. Examples: SELECT ROUND(5.4567,3) = 5.4570 SELECT ROUND(345.4567,–1) = 350.0000 SELECT ROUND(345.4567,–1,1) = 340.0000
ROWCOUNT_BIG	Returns the number of rows that have been affected by the last Transact-SQL statement executed by the system. The return value of this function has the BIGINT data type.
SIGN(n)	Returns the sign of the value n as a number (+1 for positive, –1 for negative, and 0 for zero). Example: SELECT SIGN(0.88) = 1
SIN(n)	Calculates the sine of n. n and the resulting value belong to the FLOAT data type.
SQRT(n)	Calculates the square root of n. Example: SELECT SQRT(9) = 3
SQUARE(n)	Returns the square of the given expression. Example: SELECT SQUARE(9) = 81
TAN(n)	Calculates the tangent of n. n and the resulting value belong to the FLOAT data type.

Date Functions

Date functions calculate the respective date or time portion of an expression or return the value from a time interval. Transact-SQL supports the following date functions:

Function	Explanation
GETDATE()	Returns the current system date and time. Example: SELECT GETDATE() = 2008-01-01 13:03:31.390
DATEPART(item,date)	Returns the specified part item of a date date as an integer. Examples: SELECT DATEPART(month, '01.01.2005') = 1 (1 = January) SELECT DATEPART(weekday, '01.01.2005') = 7 (7 = Sunday)
DATENAME(item,date)	Returns the specified part item of the date date as a character string. Example: SELECT DATENAME(weekday, '01.01.2005') = Saturday
DATEDIFF(item,dat1,dat2)	Calculates the difference between the two date parts dat1 and dat2 and returns the result as an integer in units specified by the value item. Example: SELECT DATEDIFF(year, BirthDate, GETDATE()) AS age FROM employee; -> returns the age of each employee.
DATEADD(i,n,d)	Adds the number n of units specified by the value i to the given date d. Example: SELECT DATEADD(DAY,3,HireDate) AS age FROM employee; -> adds three days to the starting date of employment of every employee (see the sample database).

String Functions

String functions are used to manipulate data values in a column, usually of a character data type. Transact-SQL supports the following string functions:

Function	Explanation
ASCII(character)	Converts the specified character to the equivalent integer (ASCII) code. Returns an integer. Example: SELECT ASCII('A') = 65
CHAR(integer)	Converts the ASCII code to the equivalent character. Example: SELECT CHAR(65) = 'A'.
CHARINDEX(z1,z2)	Returns the starting position where the partial string z1 first occurs in the string z2. Returns 0 if z1 does not occur in z2. Example: SELECT CHARINDEX('bl', 'table') = 3.
DIFFERENCE(z1,z2)	Returns an integer, 0 through 4, that is the difference of SOUNDEX values of two strings z1 and z2. (SOUNDEX returns a number that specifies the sound of a string. With this method, strings with similar sounds can be determined.) Example: SELECT DIFFERENCE('spelling', 'telling') = 2 (sounds a little bit similar, 0 = doesn't sound similar)
LEFT(z, length)	Returns the first length characters from the string z.
LEN(z)	Returns the number of characters, instead of the number of bytes, of the specified string expression, excluding trailing blanks.
LOWER(z1)	Converts all uppercase letters of the string z1 to lowercase letters. Lowercase letters and numbers, and other characters, do not change. Example: SELECT LOWER('BiG') = 'big'
LTRIM(z)	Removes leading blanks in the string z. Example: SELECT LTRIM(' String') = 'String'
NCHAR(i)	Returns the Unicode character with the specified integer code, as defined by the Unicode standard.
QUOTENAME(char_string)	Returns a Unicode string with the delimiters added to make the input string a valid delimited identifier.
PATINDEX(%p%,expr)	Returns the starting position of the first occurrence of a pattern p in a specified expression expr, or zeros if the pattern is not found. Examples: 1) SELECT PATINDEX('%gs%', 'longstring') = 4; 2) SELECT RIGHT(ContactName, LEN(ContactName)-PATINDEX('% %',ContactName)) AS First_name FROM Customers; (The second query returns all first names from the customers column.)
REPLACE(str1,str2,str3)	Replaces all occurrences of the str2 in the str1 with the str3. Example: SELECT REPLACE('shave' , 's' , 'be') = behave
REPLICATE(z,i)	Repeats string z i times. Example: SELECT REPLICATE('a',10) = 'aaaaaaaaaa'
REVERSE(z)	Displays the string z in the reverse order. Example: SELECT REVERSE('calculate') = 'etaluclac'
RIGHT(z,length)	Returns the last length characters from the string z. Example: SELECT RIGHT('Notebook',4) = 'book'
RTRIM(z)	Removes trailing blanks of the string z. Example: SELECT RTRIM('Notebook ') = 'Notebook'
SOUNDEX(a)	Returns a four-character SOUNDEX code to determine the similarity between two strings. Example: SELECT SOUNDEX('spelling') = S145
SPACE(length)	Returns a string with spaces of length specified by length. Example: SELECT SPACE = ' '
STR(f,[len [,d]])	Converts the specified float expression f into a string. len is the length of the string including decimal point, sign, digits, and spaces (10 by default), and d is the number of digits to the right of the decimal point to be returned. Example: SELECT STR(3.45678,4,2) = '3.46'
STUFF(z1,a,length,z2)	Replaces the partial string z1 with the partial string z2 starting at position a, replacing length characters of z1. Examples: SELECT STUFF('Notebook',5,0, ' in a ') = 'Note in a book' SELECT STUFF('Notebook',1,4, 'Hand') = 'Handbook'
SUBSTRING(z,a,length)	Creates a partial string from string z starting at the position a with a length of length. Example: SELECT SUBSTRING('wardrobe',1,4) = 'ward'
UNICODE	Returns the integer value, as defined by the Unicode standard, for the first character of the input expression.
UPPER(z)	Converts all lowercase letters of string z to uppercase letters. Uppercase letters and numbers do not change. Example: SELECT UPPER('loWer') = 'LOWER'

System Functions

System functions of Transact-SQL provide extensive information about database objects. Most system functions use an internal numeric identifier (ID), which is assigned to each database object by the system at its creation. Using this identifier, the system can uniquely identify each database object. System functions provide information about the database system. The following table describes several system functions. (For the complete list of all system functions, please see Books Online.)

Function	Explanation
CAST(a AS type [(length)]	Converts an expression a into the specified data type type (if possible). a could be any valid expression. Example: SELECT CAST(3000000000 AS BIGINT) = 3000000000
COALESCE(a1,a2,…)	Returns for a given list of expressions a1, a2,... the value of the first expression that is not NULL.
COL_LENGTH(obj,col)	Returns the length of the column col belonging to the database object (table or view) obj. Example: SELECT COL_LENGTH('customers', 'cust_ID') = 10
CONVERT(type[(length)],a)	Equivalent to CAST, but the arguments are specified differently. CONVERT can be used with any data type.
CURRENT_TIMESTAMP	Returns the current date and time. Example: SELECT CURRENT_TIMESTAMP = '2008-01-01 17:22:55.670'
CURRENT_USER	Returns the name of the current user.
DATALENGTH(z)	Calculates the length (in bytes) of the result of the expression z. Example: SELECT DATALENGTH(ProductName) FROM products. (This query returns the length of each field.)
GETANSINULL('dbname')	Returns 1 if the use of NULL values in the database dbname complies with the ANSI SQL standard. (See also the explanation of NULL values at the end of this chapter.) Example: SELECT GETANSINULL('AdventureWorks') = 1
ISNULL(expr, value)	Returns the value of expr if that value is not null; otherwise, it returns value (see Example 5.22).
ISNUMERIC(expression)	Determines whether an expression is a valid numeric type.
NEWID()	Creates a unique ID number that consists of a 16-byte binary string intended to store values of the UNIQUEIDENTIFIER data type.
NEWSEQUENTIALID()	Creates a GUID that is greater than any GUID previously generated by this function on a specified computer. (This function can only be used as a default value for a column.)
NULLIF(expr1,expr2)	Returns the NULL value if the expressions expr1 and expr2 are equal. Example: SELECT NULLIF(project_no, 'p1') FROM projects. (The query returns NULL for the project with the project_no = 'p1').
SERVERPROPERTY(propertyname)	Returns the property information about the database server.
SYSTEM_USER	Returns the login ID of the current user. Example: SELECT SYSTEM_USER = LTB13942dusan
USER_ID([user_name])	Returns the identifier of the user user_name. If no name is specified, the identifier of the current user is retrieved. Example: SELECT USER_ID('guest') = 2
USER_NAME([id])	Returns the name of the user with the identifier id. If no name is specified, the name of the current user is retrieved. Example: SELECT USER_NAME = 'guest'

All string functions can be nested in any order; for example, REVERSE(CURRENT_USER).

Metadata Functions

Generally, metadata functions return information concerning the specified database and database objects. The following table describes several metadata functions. (For the complete list of all metadata functions, please see Books Online.)

Function	Explanation
COL_NAME(tab_id, col_id)	Returns the name of a column belonging to the table with the ID tab_id and column ID col_id. Example: SELECT COL_NAME(OBJECT_ID('employee') , 3) = 'emp_lname'
COLUMNPROPERTY(id, col, property)	Returns the information about the specified column. Example: SELECT COLUMNPROPERTY(object_id('project'), 'project_no', 'PRECISION') = 4
DATABASEPROPERTY(database, property)	Returns the named database property value for the specified database and property. Example: SELECT DATABASEPROPERTY('sample', 'IsNullConcat') = 0. (The IsNullConcat property corresponds to the option CONCAT_NULL_YIELDS_NULL, which is described at the end of this chapter.)
DB_ID([db_name])	Returns the identifier of the database db_name. If no name is specified, the identifier of the current database is returned. Example: SELECT DB_ID('AdventureWorks') = 6
DB_NAME([db_id])	Returns the name of the database with the identifier db_id. If no identifier is specified, the name of the current database is displayed. Example: SELECT DB_NAME(6) = 'AdventureWorks'
INDEX_COL(table, i, no)	Returns the name of the indexed column in the table table, defined by the index identifier i and the position no of the column in the index.
INDEXPROPERTY(obj_id, index_name, property)	Returns the named index or statistics property value of a specified table identification number, index or statistics name, and property name.
OBJECT_NAME(obj_id)	Returns the name of the database object with the identifier obj_id. Example: SELECT OBJECT_NAME(453576654) = 'products'
OBJECT_ID(obj_name)	Returns the identifier of the database object obj_name. Example: SELECT OBJECT_ID('products') = 453576654
OBJECTPROPERTY(obj_id,property)	Returns the information about the objects from the current database.

Using T-SQL data types in SQL Server 2008

Data Types

All the data values of a column must be of the same data type. (The only exception specifies the values of the SQL_VARIANT data type.) Transact-SQL uses different data types, which can be categorized as follows:

• Numeric data types
• Character data types
• Temporal (date and/or time) data types
• Miscellaneous data types
• DECIMAL with VARDECIMAL storage type

The following sections describe all these categories.

Numeric Data Types

Numeric data types are used to represent numbers. The following table shows the list of all numeric data types:

Data Type	Explanation
INTEGER	Represents integer values that can be stored in 4 bytes. The range of values is –2,147,483,648 to 2,147,483,647. INT is the short form for INTEGER.
SMALLINT	Represents integer values that can be stored in 2 bytes. The range of values is –32768 to 32767.
TINYINT	Represents nonnegative integer values that can be stored in 1 byte. The range of values is 0 to 255.
BIGINT	Represents integer values that can be stored in 8 bytes. The range of values is –2^63 to 2^63 – 1.
DECIMAL(p,[s])	Describes fixed-point values. The argument p (precision) specifies the total number of digits with assumed decimal point s (scale) digits from the right. DECIMAL values are stored, depending on the value of p, in 5 to 17 bytes. DEC is the short form for DECIMAL.
NUMERIC(p,[s])	Synonym for DECIMAL.
REAL	Used for floating-point values. The range of positive values is approximately 2.23E – 308 to 1.79E +308, and the range of negative values is approximately –1.18E – 38 to –1.18E + 38 (the value zero can also be stored).
FLOAT[(p)]	Represents floating-point values, like REAL. p defines the precision with p < 25 as single precision (4 byte) and p >= 25 as double precision (8 byte).
MONEY	Used for representing monetary values. MONEY values correspond to 8-byte DECIMAL values and are rounded to four digits after the decimal point.
SMALLMONEY	Corresponds to the data type MONEY but is stored in 4 bytes.

Character Data Types

There are two general forms of character data types. They can be strings of single-byte characters or strings of Unicode characters. (Unicode uses several bytes to specify one character.) Further, strings can have fixed or variable length. The following character data types are used:

Data Type	Explanation
CHAR[(n)]	Represents a fixed-length string of single-byte characters, where n is the number of characters inside the string. The maximum value of n is 8000. CHARACTER(n) is an additional equivalent form for CHAR(n). If n is omitted, the length of the string is assumed to be 1.
VARCHAR[(n)]	Describes a variable-length string of single-byte characters (0 < n ≤ 8000). In contrast to the CHAR data type, the values for the VARCHAR data type are stored in their actual length. This data type has two synonyms: CHAR VARYING and CHARACTER VARYING.
NCHAR[(n)]	Stores fixed-length strings of Unicode characters. The main difference between the CHAR and NCHAR data types is that each character of the NCHAR data type is stored in 2 bytes, while each character of the CHAR data type uses 1 byte of storage space. Therefore, the maximum number of characters in a column of the NCHAR data type is 4000.
NVARCHAR[(n)]	Stores variable-length strings of Unicode characters. The main difference between the VARCHAR and the NVARCHAR data types is that each NVARCHAR character is stored in 2 bytes, while each VARCHAR character uses 1 byte of storage space. The maximum number of characters in a column of the NVARCHAR data type is 4000.

 

Note: The VARCHAR data type is identical to the CHAR data type except for one difference: if the content of a CHAR(n) string is shorter than n characters, the rest of the string is padded with blanks. (A value of the VARCHAR data type is always stored in its actual length.)

Temporal Data Types

Transact-SQL supports the following temporal data types:

• DATETIME
• SMALLDATETIME
• DATE
• TIME
• DATETIME2
• DATETIMEOFFSET

The DATETIME and SMALLDATETIME data types specify a date and time, with each value being stored as an integer value in 4 bytes or 2 bytes, respectively. Values of DATETIME and SMALLDATETIME are stored internally as two separate numeric values. The date value of DATETIME is stored in the range 01/01/1753 to 12/31/9999. The analog value of SMALLDATETIME is stored in the range 01/01/1900 to 06/06/2079. The time component is stored in the second 4-byte (or 2-byte for SMALLDATETIME) field as the number of three-hundredths of a second (DATETIME) or minutes (SMALLDATETIME) that have passed since midnight.

The use of DATETIME and SMALLDATETIME is rather inconvenient if you want to store only the date part or time part. For this reason, SQL Server 2008 introduces the new data types DATE and TIME, which store just the DATE or TIME component of a DATETIME, respectively. The DATE data type is stored in 3 bytes and has the range 01/01/0001 to 12/31/9999. The TIME data type is stored in 3–5 bytes and has an accuracy of 100 nanoseconds (ns).

The DATETIME2 data type is also a new data type that stores high-precision date and time data. The data type can be defined for variable lengths depending on the requirement. (The storage size is 6–8 bytes). The accuracy of the time part is 100 ns. This data type isn't aware of Daylight Saving Time.

All the temporal data types described thus far don't have support for the time zone. The new data type called DATETIMEOFFSET has the time zone offset portion. For this reason, it is stored in 6–8 bytes. (All other properties of this data type are analogous to the corresponding properties of DATETIME2.)

The date value in Transact-SQL is by default specified as a string in a format like 'mmm dd yyyy' (e.g., 'Jan 10 1993') inside two single quotation marks or double quotation marks. (Note that the relative order of month, day, and year can be controlled by the SET DATEFORMAT statement. Additionally, the system recognizes numeric month values with delimiters of / or –.) Similarly, the time value is specified in the format 'hh:mm' and Database Engine uses 24-hour time (23:24, for instance).

 

Note: Transact-SQL supports a variety of input formats for DATETIME values. As you already know, both objects are identified separately; thus, date and time values can be specified in any order or alone. If one of the values is omitted, the system uses the default values. (The default value for time is 12:00 AM.)

Examples 4.4 and 4.5 show the different ways, how date or time values can be written using the different formats.

Example 4.4
The following date descriptions can be used:

'28/5/1959' (with SET DATEFORMAT dmy)
'May 28, 1959'
'1959 MAY 28'

Example 4.5
The following time descriptions can be used:

'8:45 AM'
'4 pm'

Miscellaneous Data Types

Transact-SQL supports several data types that do not belong to any of the data type groups described previously:

• Binary data types
• BIT
• Large object data types
• CURSOR (discussed in Chapter 8)
• UNIQUEIDENTIFIER
• SQL_VARIANT
• TABLE (discussed in Chapter 8)
• XML (discussed in Chapter 28)
• Spatial (e.g., GEOGRAPHY and GEOMETRY) data types (discussed in Chapter 29 )
• HIERARCHYID
• TIMESTAMP data type
• User-defined data types (discussed in Chapter 5)

The following sections describe each of these data types (other than those designated as being discussed in another chapter).

Binary and BIT Data Types

BINARY and VARBINARY are the two binary data types. They describe data objects being represented in the internal format of the system. They are used to store bit strings. For this reason, the values are entered using hexadecimal numbers.

The values of the BIT data type are stored in a single bit. Therefore, up to 8 bit columns are stored in 1 byte. The following table summarizes the properties of these data types:

Data Type	Explanation
BINARY[(n)]	Specifies a bit string of fixed length with exactly n bytes (0 < n ≤ 8000).
VARBINARY[(n)]	Specifies a bit string of variable length with up to n bytes (0 < n ≤ 8000).
BIT	Used for specifying the Boolean data type with three possible values: FALSE, TRUE, and NULL.

Large Object Data Types

Large objects (LOBs) are data objects with the maximum length of 2GB. These objects are generally used to store large text data and to load modules and audio/video files. Transact-SQL supports two different ways to specify and access LOBs:

• Use the data types VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX)
• Use the so-called text/image data type

The following subsections describe the two forms of LOBs.

The MAX Specifier Starting with SQL Server 2005, you can use the same programming model to access values of standard data types and LOBs. In other words, you can use convenient system functions and string operators to work with LOBs.

Database Engine uses the MAX specifier with the data types VARCHAR, NVARCHAR, and VARBINARY to define variable-length columns. When you use MAX by default (instead of an explicit value), the system analyzes the length of the particular string and decides whether to store the string as a convenient value or as a LOB. The MAX specifier indicates that the size of column values can reach the maximum LOB size of the current system. (In a future version of SQL Server, it is possible that MAX will have a higher maximum value for strings.)

Although the database system decides how a LOB will be stored, you can override this default specification using the sp_tableoption system procedure with the LARGE_ VALUE_TYPES_OUT_OF_ROW option. If the option's value is set to 1, the data in columns declared using the MAX specifier will be stored separately from all other data. If this option is set to 0, Database Engine stores all values for the row size < 8060 bytes as regular row data.

In SQL Server 2008, you can apply the new FILESTREAM attribute to a VARBINARY(MAX) column to store large binary data directly in an NTFS file system. The main advantage of this attribute is that the size of the corresponding LOB is limited only by the file system volume size.

TEXT, NTEXT, and IMAGE Data Types The data types TEXT, NTEXT, and IMAGE constitute the so-called text/image data types. Data objects of the type IMAGE can contain any kind of data (load modules, audio/video), while data objects of the data types TEXT and NTEXT can contain any text data (that is, printable data).

The text/image data types are stored by default separately from all other values of a database using a B-tree structure that points to the fragments of that data. (A B-tree structure is a treelike data structure in which all of the bottom nodes are the same number of levels away from the root of the tree.) For columns of a text /image data type, Database Engine stores a 16-byte pointer in the data row that specifies where the data can be found.

If the amount of text/image data is less than 32KB, the pointer points to the root node of the B-tree structure, which is 84 bytes long. The root node points to the physical blocks of the data. If the amount of the data is greater than 32KB, Database Engine builds intermediate nodes between the data blocks and the root node.

For each table that contains more than one column with such data, all values of the columns are stored together. However, one physical page can hold only text/image data from a single table.

Although text/image data is stored separately from all other data, you can modify this using the sp_tableoption system procedure with the TEXT_IN_ROW option. Using this option, you can specify the maximum number of bytes, which are stored together with the regular data.

The text/image data types discussed this far have several limitations. You can't use them as local variables (in stored procedures or in groups of Transact-SQL statements). Also, they can't be a part of an index or can't be used in the following clauses of the SELECT statement: WHERE, ORDER BY, and GROUP BY. The biggest problem concerning all text/image data types is that you have to use special operators (READTEXT, WRITETEXT, and UPDATETEXT) to work with such data.

 

Note: The text/image data types are marked as a deprecated feature and will be removed in a future version of Database Engine. Use VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) instead.

UNIQUEIDENTIFIER Data Type

As its name implies, a value of the UNIQUEIDENTIFIER data type is a unique identification number stored as a 16-byte binary string. This data type is closely related to the globally unique identifier (GUID), which guarantees uniqueness worldwide. Hence, using this data type, you can uniquely identify data and objects in distributed systems.

The initialization of a column or a variable of the UNIQUEIDENTIFIER type can be provided using the functions NEWID and NEWSEQUENTIALID, as well as with a string constant written in a special form using hexadecimal digits and hyphens. (The functions NEWID and NEWSEQUENTIALID are described in the section "System Functions" later in this chapter.)

A column of the UNIQUEIDENTIFIER data type can be referenced using the keyword ROWGUIDCOL in a query to specify that the column contains ID values. (This keyword does not generate any values.) A table can have several columns of the UNIQUEIDENTIFIER type, but only one of them can have the ROWGUIDCOL keyword.

SQL_VARIANT Data Type

The SQL_VARIANT data type can be used to store values of various data types at the same time, such as numeric values, strings, and date values. (The only types of values that cannot be stored are TIMESTAMP values.) Each value of an SQL_VARIANT column has two parts: the data value and the information that describes the value. (This information contains all properties of the actual data type of the value, such as length, scale, and precision.)

Transact-SQL supports the SQL_VARIANT_PROPERTY function, which displays the attached information for each value of an SQL_VARIANT column. For the use of the SQL_VARIANT data type, see Example 5.5 in Chapter 5.

 

Note: Declare a column of a table using the SQL_VARIANT data type only if it is really necessary. A column should have this data type if its values may be of different types or if it is not possible to determine the type of a column during the database design process.

HIERARCHYID Data Type

The HIERARCHYID data type is used to store an entire hierarchy. It is implemented as a Common Language Runtime (CLR) user-defined type that comprises several system functions for creating and operating on hierarchy nodes. The following functions, among others, belong to the methods of this data type: GetAncestor(), GetDescendant(), Read(), and Write(). (The detailed description of this data type is outside the scope of this book.)

TIMESTAMP Data Type

The TIMESTAMP data type specifies a column being defined as VARBINARY(8) or BINARY(8), depending on nullability of the column. The system maintains a current value (not a date or time) for each database, which it increments whenever any row with a TIMESTAMP column is inserted or updated and sets the TIMESTAMP column to that value. Thus, TIMESTAMP columns can be used to determine the relative time when rows were last changed. (ROWVERSION is a synonym for TIMESTAMP.)

 

Note: The value stored in a TIMESTAMP column isn't important by itself. This column is usually used to detect whether a specific row has been changed since the last time it was accessed.

DECIMAL with VARDECIMAL Storage Format

The DECIMAL data type is typically stored on the disk as fixed-length data. Since SQL Server 2005 SP2, this data type can be stored as a variable-length column by using the new storage format called VARDECIMAL. Using VARDECIMAL, you can significantly reduce the storage space for a DECIMAL column in which values have significant difference in their lengths.

 

Note: VARDECIMAL is a storage format and not a data type.

The VARDECIMAL storage format is helpful when you have to specify the largest possible value for a field in which the stored values usually are much smaller. Table 4-1 shows this.

 

Note: The VARDECIMAL storage format works the same way for the DECIMAL data type as the VARCHAR data type works for alphanumerical data.

Precision	No. of Bytes: VARDECIMAL	No. of Bytes: Fixed Length
0 or NULL	2	5
1	4	5
20	12	13
30	16	17
38	20	17

Table 4–1 Number of Bytes for Storing VARDECIMAL and Fixed Length

To enable the VARDECIMAL storage format, you first have to enable it for the database and then enable it for the particular table of that database. The sp_db_ vardecimal_storage_format system procedure is used for the first step, as Example 4.6 shows.

Example 4.6
EXEC sp_db_vardecimal_storage_format 'sample', 'ON';

The VARDECIMAL STORAGE FORMAT option of the sp_table option system procedure is used to turn on this storage for the table. Example 4.7 turns on the VARDECIMAL storage format for the project table.

Example 4.7
EXEC sp_tableoption 'project', 'vardecimal storage format', 1

As you already know, the main reason to use the VARDECIMAL storage format is to reduce the storage size of the data. If you want to test how much storage space could be gained by using this storage format, use the dynamic management view called sys.sp_estimated_rowsize_reduction_for_vardecimal. This dynamic management view gives you a detailed estimate for the particular table.

Basic objects of T-SQL in SQL Server 2008

Learn the elementary objects and basic operators supported by the Transact-SQL language in SQL Server 2008 in this excerpt from "Microsoft SQL Server 2008: A Beginner's Guide." You'll find out about the basic objects and operators of T-SQL, including constants, identifiers and delimiters, and you'll also discover the corresponding data types that relate to each object and operator. Author Dusan Petkovic also describes how NULL values, scalar objects and global variables operate in SQL Server 2008.

SQL's Basic Objects

The language of Database Engine, Transact-SQL, has the same basic features as other common programming languages:

• Literal values (also called constants)
• Delimiters
• Comments
• Identifiers
• Reserved keywords

The following sections describe these features.
Literal Values

A literal value is an alphanumerical, hexadecimal, or numeric constant. A string constant contains one or more characters of the character set enclosed in two single straight quotation marks (' ') or double straight quotation marks (" ") (single quotation marks are preferred due to the multiple uses of double quotation marks, as discussed in a moment). If you want to include a single quotation mark within a string delimited by single quotation marks, use two consecutive single quotation marks within the string. Hexadecimal constants are used to represent nonprintable characters and other binary data. Each hexadecimal constant begins with the characters '0x' followed by an even number of characters or numbers. Examples 4.1 and 4.2 illustrate some valid and invalid string constants and hexadecimal constants.

Example 4.1

Some valid string constants and hexadecimal constants follow:

'Philadelphia'

"Berkeley, CA 94710"

'9876'

'Apostrophe is displayed like this: can''t' (note the two consecutive single quotation marks)

0x53514C0D

Example 4.2

The following are not string constants:

'AB'C' (odd number of single quotation marks)

'New York" (same type of quotation mark—single or double—must be used at each end of the string)

The numeric constants include all integer, fixed-point, and floating-point values with and without signs (see Example 4.3).

Example 4.3

The following are numeric constants:
130

–130.00

–0.357E5 (scientific notation—nEm means n multiplied by 10^m)

22.3E-3


A constant always has a data type and a length, and both depend on the format of the constant. Additionally, every numeric constant has a precision and a scale factor. (The data types of the different kinds of literal values are explained later in this chapter.)

Delimiters

In Transact-SQL, double quotation marks have two meanings. In addition to enclosing strings, double quotation marks can also be used as delimiters for so-called delimited identifiers. Delimited identifiers are a special kind of identifier usually used to allow the use of reserved keywords as identifiers and also to allow spaces in the names of database objects.
Note: Differentiation between single and double quotation marks was first introduced in the SQL92 standard. In the case of identifiers, this standard differentiates between regular and delimited identifiers. Two key differences are that delimited identifiers are enclosed in double quotation marks and are case sensitive. (Transact-SQL also supports the use of square brackets instead of double quotation marks.) Double quotation marks are only used for delimiting strings. Generally, delimited identifiers were introduced to allow the specification of identifiers, which are otherwise identical to reserved keywords. Specifically, delimited identifiers protect you from using names (identifiers, variable names) that could be introduced as reserved keywords in one of the future SQL standards. Also, delimited identifiers may contain characters that are normally illegal within identifier names, such as blanks.

In Transact-SQL, the use of double quotation marks is defined using the QUOTED_IDENTIFIER option of the SET statement. If this option is set to ON, which is the default value, an identifier in double quotation marks will be defined as a delimited identifier. In this case, double quotation marks cannot be used for delimiting strings.

Comments

There are two different ways to specify a comment in a Transact-SQL statement. Using the pair of characters /* and */ marks the enclosed text as a comment. In this case, the comment may extend over several lines. Furthermore, the characters -- (two hyphens) indicate that the remainder of the current line is a comment. (The two hyphens -- comply with the ANSI SQL standard, while /* and */ are the extensions of Transact-SQL.)

Identifiers

In Transact-SQL, identifiers are used to identify database objects such as databases, tables, and indices. They are represented by character strings that may include up to 128 characters and can contain letters, numerals, or the following characters: _, @, #, and $. Each name must begin with a letter or one of the following characters: _, @, or #. The character # at the beginning of a table or stored procedure name denotes a temporary object, while @ at the beginning of a name denotes a variable. As indicated earlier, these rules don't apply to delimited identifiers (also known as quoted identifiers), which can contain, or begin with, any character (other than the delimiters themselves).

Reserved Keywords

Each programming language has a set of names with reserved meanings, which must be written and used in the defined format. Names of this kind are called reserved keywords. Transact-SQL uses a variety of such names, which, as in many other programming languages, cannot be used as object names, unless the objects are specified as delimited or quoted identifiers.
Note: In Transact-SQL, the names of all data types and system functions, such as CHARACTER and INTEGER, are not reserved keywords. They can therefore be used for denoting objects. (Do not use data types and system functions as object names! Such a use makes Transact-SQL statements difficult to read and understand.)

What's new in SQL Server 2008

SQL Server 2008 will be released in 2^nd quarter of 2008. SQL Server 2008 will also be part of a joint launch with Windows Server 2008 and Visual Studio 2008 on February 27, 2008 in Los Angeles, CA.Here is a list of some of the new features available

Transparent Data Encryption

SQL Server 2008 enables encryption of entire databases, data files, and log files, without the need for application changes. Encryption enables organizations to meet the demands of regulatory compliance and overall concern for data privacy. Some of the benefits of transparent data encryption include searching encrypted data using either range or fuzzy searches, more secure data from unauthorized users, and data encryption. These can be enabled without changing existing applications.

External Key Management

SQL Server 2008 provides a comprehensive solution for encryption and key management. To meet the growing need for greater security of information within data centers, organizations have invested in vendors to manage security keys within the enterprise. SQL Server 2008 provides excellent support for this need by supporting third-party key management and hardware security module (HSM) products.

Enhanced Auditing

SQL Server 2008 improves compliance and security by allowing you to audit activity on your data. Auditing can include information about when data has been read, in addition to any data modifications. SQL Server 2008 has features such as enhanced configuration and management of audits in the server, which enable organizations to meet varied compliance needs. SQL Server 2008 can also define audit specifications in each database, so audit configuration can be ported with databases. Filtering of audits to specific objects allows better performance in audit generation and flexibility in configuration.

Enhanced Database Mirroring

• Automatic page repair. SQL Server 2008 enables the principal and mirror computers to transparently recover from 823 and 824 errors on data pages by requesting a fresh copy of the corrupted page from the mirroring partner.
• Improved performance. SQL Server 2008 compresses the outgoing log stream in order to minimize the network bandwidth required by database mirroring.
• Enhanced supportability
• SQL Server 2008 includes additional performance counters to enable more granular accounting of the time spent across the different stages of Database Management System (DBMS) log processing.
• SQL Server 2008 includes new Dynamic Management Views and extensions of existing views to expose additional information about mirroring sessions.

Hot Add CPU

Extending existing support in SQL Server for adding memory resources online, Hot Add CPU allows a database to be scaled on demand. In fact, CPU resources can be added to SQL Server 2008 on supported hardware platforms without requiring application downtime.

Performance data collection

Performance tuning and troubleshooting are time-consuming tasks for the administrator. To provide actionable performance insights to administrators, SQL Server 2008 delivers more extensive performance data collection, a new centralized data repository for storing performance data, and new reporting and monitoring tools.

Extended Events

SQL Server Extended Events is a general event-handling system for server systems. The Extended Events infrastructure is a lightweight mechanism that supports capturing, filtering, and acting upon events generated by the server process. This ability to act upon events allows users to quickly diagnose run time problems by adding contextual data, such as Transact SQL call stacks or query plan handles, to any event.  Events can be captured into several different output types, including Event Tracing for Windows (ETW).  When Extended Events are output to ETW, correlation with operating system and database applications is possible, allowing for more holistic system tracing.

Backup compression

Keeping disk-based backups online is expensive and time consuming. With SQL Server 2008 backup compression, less disk I/O is required, less storage is required to keep backups online, and backups run significantly faster.

Data compression

Improved data compression enables data to be stored more effectively and reduces the storage requirements for your data. Data compression also provides significant performance improvements for large input/output-bound workloads such as data warehousing.

Resource Governor

SQL Server 2008 enables organizations to provide a consistent and predictable response to end users with the introduction of Resource Governor. Resource Governor enables database administrators to define resource limits and priorities for different workloads, which enables concurrent workloads to provide consistent performance to end users.

Plan Freezing

SQL Server 2008 enables greater query performance stability and predictability by providing new functionality to lock down query plans, enabling organizations to promote stable query plans across hardware server replacements, server upgrades, and production deployments.

These are just some of the new features. I plan to provide you with some tutorials of what SQL Server 2008 has to offer in the near future.

10+ interview questions that will help reveal a candidate's true qualities

Author: TechRepublic

Today's IT job hunters are often so adept at fielding interview questions, it can be difficult to get a read on whether they'd be a good fit for your organization. Asking some of these questions may give you a glimpse of the person behind that candidate facade.

---

Determining a candidate's technical skills is a major goal in an interview, but finding out how the person will fit into your organization's culture is also essential. These days, however, candidates tend to be interview-savvy and well rehearsed in responding to typical questions. So how do you break through a candidate's facade and find out what you really want to know? Here are some questions you may want to consider asking to help you gain some legitimate insight into the person you're interviewing.

Note: These suggestions are based on the article "Questions I'd like to ask the next time I'm hiring," along with some member suggestions. It's also available as a PDF download.

1: How's your stamina?

We're not talking primarily about physical stamina here, although that's part of it. In a lot of shops, the workload can grind people down if they aren't strong enough to handle it. It's important to let candidates know that a position will be demanding-as well as to see how they rate themselves in the fortitude department.

2: How hard have you been working lately?

Even the most industrious employees can lose the habit of working hard if they've been in a situation that doesn't require it. And a candidate who's fallen into "coasting" mode may have trouble ramping up for the effort you require. Conversely, a candidate who speaks enthusiastically about being engaged in challenging projects may well be a self-starter who could energize your team with his or her commitment and work ethic.

3: How do you react to being told "No"?

A big part of the typical manager's job is telling people why they can't do something-either because they don't have the money or resources or because an idea or proposal is no good. And let's face it: Some folks don't handle being told No that well. A candid response to this question may not tell you for sure how well candidates handle the issue, but it could give you a picture of whether they're aware of their own tendencies.

4: Can you handle telling other people "No"?

If don't want to be the DDrN (Designated Dr. No) for the organization, you need people on your team who are willing and able to share the load. Of course, you don't want someone who's chomping at the bit to slap people down, either. But it can be revealing to see how many candidates respond along these lines: "I don't really feel comfortable telling other people they can't do things. I just worry about my own responsibilities."

5: How good are you — REALLY — at handling change?

Everybody asks this question, so of course every candidate has a prepared answer. It goes like this: "I think it's important to be flexible and adapt to new circumstances. One time, [insert anecdote illustrating ability to manage change here]…." This is a critical problem for managers, because the pace of change continues to accelerate, but a lot of job candidates are extremely uncomfortable with it. Trying to identify those folks during the interview process may require you to ask about it point-blank–and then hope that the candidate will abandon the script at some point so you can have an earnest discussion.

6: Are you a good scrounger?

A common interview question centers around a candidate's problem-solving capability. But this question focuses on a candidate's ability to come up with the resources out of what he or she has on the shelf. (Think of the James Garner character "The Scrounger" from the movie The Great Escape, who comes up with camera, pipe, or whatever else the POWs need when planning their breakout.)

7: When conflict arises on your team, how do you handle it?

This is one of those questions that can easily be fielded with a stock answer and a polished anecdote, so it's up to you to try to elicit something more illuminating. Often this will just be a matter of asking follow-up questions (and these don't have to be formulaic; just have a conversation around what the candidate has told you). You can also pose a scenario and ask candidates what they might do in a particular situation. Is this approach contrived? You bet it is. But it will challenge candidates to think on their feet and may provide useful clues about their personality and conflict management skills.

8: What have the last few years taught you?

Anyone who's been in IT for awhile knows that the industry has had some serious ups and downs. This questions is designed to get at what the job candidate has learned through the periods of explosive growth as well as through the tough downturns, tight budgets, and shifts in the job market.

9: What type of people do you like to work with?

Even if you get a canned response here, you may be able to get a glimpse of the candidate's personality. Previous experiences and genuine preferences will often filter through to their answers. For example: "I like to work with people who really know what they're talking about, not people who just want to show everyone how smart they are"; "I like to work with people who I can bounce ideas off of"; "I like to work with people who respect what I do."

10: How do you stay current?

Since this one comes right out of Interviewing 101, most candidates will be ready for it. But it's still a critical question that must be addressed. The technology changes so quickly that all of our past experience decreases in value daily. You can't hire an IT professional without assessing their plans to keep abreast of new products and technologies.

11: What's the toughest thing you've had to do professionally?

This question also comes out of the interviewing playbook, but it's still a good one. It's interesting to see whether the candidate mentions some technical achievement or project or discusses something more personal instead — for example, having to fire an employee.

12: How would you describe your perfect job?

You can learn a lot from the responses to this question, and it may spark a lively conversation as well. You might discover that the candidate is quite assertive in describing what he or she wants a position to provide; in fact, you may learn a thing or two that will help you craft a better job description for the position. You might also find out that a candidate has some unrealistic expectations about the respective roles of employer and employee-which could lead to disappointment and poor performance if left unaddressed.

13: If you could take back one career decision, what would it be?

This is a pretty good shot-in-the-dark question. There is certainly no "right" answer, but it can be useful to see how candidates respond. Can they point to something instantly or do they have to consider? Maybe they'll be confident enough to admit, "I can't think of anything substantial. So far, I'm pretty pleased with how my career is going." Sometimes, ambivalence or dissatisfaction come to light, suggesting that they're headed down the wrong path altogether. Regardless of their answer, this question can lead to an interesting discussion.

Three tips for writing an effective cover letter

Author: Toni Bowers

Some experts think that the introductory note you include with your resume is even more important than the resume. Here are three tips for making your cover letter more dynamic.
——————————————————————————————————————-
Since so many of us, particularly those in IT, don’t actually send paper resumes anymore, “cover letter” is a bit of a misnomer. But even if you e-mail a resume, there has to be some kind of introductory note to accompany your resume. Some career experts will tell you this introductory piece is even more important than the resume itself. It’s where you can offer a picture of the real you and not feel constricted by bulleted lists.

The goal with a cover letter is the same as with the resume: to stand out from the crowd. Here are a few tips to keep in mind when you’re composing your cover letter.

1. Grab the reader from the get-go
I recently unearthed some copies of my old resumes and cover letters. The good news is that I customized each cover letter for the position; the bad news is that every one began with some variation of “I am writing in response to your ad for….” Every. Single. One. It’s a wonder anyone looking at the cover letter could stay awake past the fourth word. So what’s the alternative?

Instead of saying,

“I am writing in response to your ad for an IT project manager,” try

“My experience in managing the workplan, schedule, and budget for several large migrations makes me a good fit for the IT project manager position you advertised.”

2. Avoid passive voice
A lot of people don’t even know what passive voice is, much less when they’re using it. Passive voice is when you make the object of an action into the subject of a sentence. It’s not always grammatically incorrect, but it isn’t very dynamic when used in a resume. Here’s an example of passive voice:

“I received many honors for my work.”

This sentence would be much more dynamic if you say:

“I earned many honors in the course of my work.” The implication with the latter is that your actions got you the honors, and the honors weren’t just being handed out.

3. Don’t be touchy-feely
I see a lot of cover letters with the phrases “I feel…” or “I believe….” I understand the natural inclination to use these words because they sound less boastful; but if you had to choose between “I feel that I can lead a large project from start to finish” and “My track record shows my success with leading projects,” which would you choose? Another alternative is to just use stronger qualifiers, like “I am convinced that I can lead a large project….”

In all of the instances above, be certain to qualify and quantify your statements with results, e.g., specific time/money savings for the company, a marked decrease in network downtime, etc.

10 boring phrases you should cut from your resume

Author: Toni Bowers

When creating a resume, it’s easy to fall into the habit of using old-hat expressions. Here are 10 that have become meaningless and should be avoided at all costs.
——————————————————————————————————————-
I ran across a piece by the esteemed Liz Ryan, a 25-year HR veteran and former Fortune 500 VP, that just made me cringe with recognition. She wrote about those trite phrases that inevitably make their way into our resumes. It’s one of those things many of us do by rote without considering that they are red flags for, as Ms. Ryan puts it, the “vocabulary challenged.”

Before I list the phrases, let me explain a little about why they should be run out of town on a rail. Have you ever sat through a speech by a company executive who seems to communicate solely in business cliches? He’ll speak of synergy and paradigm shifts and value-added propositions until you want to impale yourself on the nearest sharp object. And the only value you take away from a speech like that is that you are, in fact, capable of a boredom-induced vegetative state.

Cliched forms of speech are crutches for the uncreative. And the frequency of their usage make them absolutely meaningless.

So do you want your resume to say, above everything else, that you are incapable of forming a new thought? No, you don’t. You also don’t want the people reviewing your resume to gloss over these trite phrases and not give you a chance. That’s why you should strike every occurrence of the following from your resume:

• Results-oriented professional
• Cross-functional teams
• More than [x] years of progressively responsible experience
• Superior (or excellent) communication skills
• Strong work ethic
• Met or exceeded expectations
• Proven track record of success
• Works well with all levels of staff
• Team player
• Bottom-line orientation

Find better ways of saying these things. After all, just because you think of yourself as a results-oriented professional does not necessarily make it fact. Why not just list your actual results? You have a proven track record? Proven by whose standards? And if there is a “record” of it, why don’t you just give me its contents?

Met or exceeded expectations? Whose expectations? The guy down the street at the coffee shop? Tell me instead about the time you had to lead a project with a seemingly impossible timeline and budget.

---

Toni Bowers is the Head Blogs Editor of TechRepublic. She has been in the publishing industry for 20 years, with concentration in IT-related topics. She has edited newsletters, books, and web sites pertaining to software, IT career, and IT management issues.

10 reasons IT certification will be important in 2009

By Erik Eckel
Many technology professionals believe IT certifications reached their peak during the height of the dot-com boom. But such a mindset may well prove shortsighted. The subsequent dot-com bomb led to an exodus of certified technicians from the industry. Then, as the dust settled, IT certifications were reworked. Accreditations were better mapped to real-world needs and expertise. Program flaws were eliminated. Training programs improved.
Now, in turbulent economic times, IT certifications will provide more relevance than ever before. With unprecedented bailouts, widespread cost and workforce reductions, and a slew of new platforms being released, IT accreditations will assume renewed importance in 2009. Here are 10 reasons why IT certifications will prove important in 2009.

---

1) Job retention
Organizations are laying off employees at alarming levels. When wildly successful franchises such as the National Football League downsize 10 percent, you know the economy’s in trouble. When faced with difficult personnel decisions, organizations generally try to retain the most skillful and knowledgeable employees. Certified IT pros have a credible advantage over their colleagues. While holding a current IT accreditation is no guarantee against being laid off, the more education, expertise, and skills you can demonstrate, the better.
2) Salary maintenance
Many organizations—and city and state governments in particular—are asking employees to accept salary reductions. Whereas staff may have grumbled over a scant four or five percent salary increase a few years ago, today many are being asked to cut their compensation by those amounts.
Holding current IT certification does not guarantee you won’t face salary reductions. But possessing specific certifications—including A+, Security+, Microsoft credentials, and other accreditations—often qualifies employees for higher pay grades. Thus, when forced to accept a salary reduction, you're more likely to be earning more than your non-certified colleagues.
3) Hiring and promotion eligibility
Despite the economic downturn, some companies are still hiring. Others are actively promoting from within. Recent headlines prove medical facilities, health insurance companies, and manufacturers, among others, continue expansion efforts.
Significant competition exists for these open positions. With unemployment exceeding six percent, a number expected to grow in 2009, jockeying for good jobs will only increase. If your resume is bolstered by new and timely certifications, you'll gain an advantage over others applying for the same role. For better or worse, in cases where two otherwise equal candidates are competing for the same lucrative job offer, one applicant’s certifications could prove the deciding factor. Certification may even be required to apply for the position.
4) Career improvement
Many technology professionals feel they’ve done all they can do as a support technician or network administrator. They may be working in positions where they’ll receive no additional responsibilities, pay, or challenges unless they move up the corporate ladder.
IT certifications can certainly open the door to such promotions. By completing project management training and proving command of the fundamentals by earning a Project Management Institute (PMI) Project Management Professional or CompTIA Project+ certification, an administrator can demonstrate initiative and expertise in an effort to win a project management promotion. Likewise, a support tech might leverage a Microsoft Certified IT Professional (MCITP) accreditation to gain a new position as a server administrator.
5) New-generation certs increase relevance
Certifications are receiving a boost from considerable reworking. Many organizations, including CompTIA and Cisco, are revamping and redesigning exams and instructional initiatives. And Microsoft really stands out due to the variety and impact of changes made to its training and certification program.
Microsoft’s new generation of certifications -- including the new MCITP, Microsoft Certified Technology Specialist (MCTS), and Microsoft Certified Professional Developer (MCPD) accreditations -- map directly to real-world needs. The MCTS, for example, measures a candidate’s skill, knowledge, and expertise deploying, maintaining, and administering specific Microsoft platforms.
Microsoft’s new MCITP credential, meanwhile, is aimed at helping organizations meet specific staffing needs. The certification is designed to demonstrate expertise within job roles, such as server administrator or desktop support technician, thereby better enabling hiring managers to spot qualified, well-targeted candidates.
To keep these new-generation certifications relevant, Microsoft is expiring new credentials when mainstream support for the corresponding technology platforms is retired. Those changes, combined with the introduction of classroom and lab training requirements for new higher-level certifications, are helping put the shine back on IT certifications in 2009.
6) Organizations will become more discriminating
Consultants can benefit from IT certifications in 2009, too. As clients more closely guard expenses and become more discerning, organizations needing to outsource computer services and support will want to ensure the firms and technicians they hire are competent. IT certifications are a great method for consultants to demonstrate their skill, knowledge, and expertise to potential clients.
7) New products will gain momentum
A slew of new products is sure to gain momentum in 2009. Microsoft’s 2008-branded server products lead the charge of new technologies that will gain market share throughout the year.
As organizations begin replacing older or failed equipment with these new products, and as myriad other factors require that the new platforms be deployed, these organization will seek qualified IT technicians, managers, and consultants to plan, deploy, and administer the upgrades. If you can demonstrate your skills and expertise with these platforms, you'll be better positioned to provide those services. By becoming certified on new technologies that gain traction in 2009, you'll not only strengthen your resume, but you'll also position yourself well by aligning your expertise with these new products.
8) Organizations must minimize downtime
Server, desktop, and network downtime, as well as mean times to repair, must improve. This is true for most every organization, but especially for those that have reduced staff, as fewer employees are available to pick up the slack when errors or failures occur.
When running lean, as many companies have been forced to do, remaining employees’ workloads are often increased. Thus, it’s imperative that organizations fully utilize remaining staff.
IT certification programs are one method of ensuring that staff members have the training and instruction required to fulfill specific responsibilities. Employees who are better trained and educated as the result of certification efforts will be less likely to commit errors that lead to failure. And when outages do occur, the corresponding education and training will prove helpful in speeding recovery.
9) Organizations need to reduce costs
When sales or funding levels dive, reducing operating costs becomes critical. During periods of recession, organizations are obligated to maximize efficiency. As a result, productivity requirements become greater for each worker.
From a cold and calculating perspective, IT certification is one proven method for leveraging an organization’s salary expenses. By ensuring that technicians have specific skills via training and certification programs, whether those skills target desktop support or network design and optimization, organizations know that IT certification efforts help maximize ROI.
A Kotler Marketing Group study published by CompTIA revealed certifications enabled organizations to reduce expenses, identify knowledge gaps, and improve productivity. In addition, certifications proved helpful in improving uptime and reducing turnover.
10) Confidence proves handy during turbulent times
If nothing else, during periods of stress and upheaval, it helps to have confidence. While you can’t insulate yourself from major economic trends, you can leverage certifications to know you've taken prudent steps to keep skills current and make yourself an attractive employee, both to your current employer and to prospective hiring managers, should a pink slip arrive.
Some 75 percent of IT professionals responding to the Kotler study said their CompTIA certifications make them more attractive to employers, while 84 percent believe they now have the skills necessary to fulfill a job’s requirements. Further, some 93% agreed or strongly agreed that customers felt they are in good hands when working with them, due in part to their certifications.
With numerous other factors seemingly out of your control, IT certifications present at least one element you can command. In an age of unprecedented business and economic turmoil, the resulting confidence boost can only help.

Three questions you should ask an interviewer

Job interviews are not just a means by which prospective employers judge your suitability. It’s also a mechanism by which you can see if a company would be a place you’d want to work. Here are some questions you can ask an interviewer that will give you information about what the company culture is like and what the manager’s expectations will be.

——————————————————————————————————————-

Many people approach job interviews with a deer-in-the-headlights “please don’t let me say anything stupid” mentality. And that’s understandable since no matter how many times career experts say otherwise — that you should also use an interview to size up the company — the job interview is a means by which you’re being judged.

Sometimes when people attempt to size up a company and its job offering, it goes terribly wrong. Some candidates misinterpret that mission and end up asking “What about me?” questions such as, what’s the vacation policy and how long do employees get for lunch? Interviewers like questions from candidates, they really do, but you have to learn to ask the right kind. If you ask the right questions, the information you get back could help you tailor your own presentation. Here are some examples:

1. “What can you tell me about the people I’ll be working with?” You can tell a great deal about an interviewer from how she answers this question. Does she speak in glowing terms about the team? Or does she go into too much detail about their quirks? Maybe the question tips off a tirade from her about how worthless and unproductive her staff members are. (That last response should send you running for the hills.)


2. “How do you approach problem solving?” If the interviewer responds that he expects problems to be solved in nanoseconds and you know yourself to be the type of person who likes to weigh all aspects of an issue, then you can pretty much discern that a working relationship between you two will be like oil and water. This could work out nicely if you think you’re being a yin to his yang could be ultimately productive, but it’s something to think about.


3. “What do you see as the ultimate goal of your department or team?” A good manager will respond in terms of company value and employee satisfaction. If he responds that his ultimate goal is to not screw up and to stay under the radar, you should be able to infer that that culture will not be the most supportive to growth. Also beware if this question causes the interviewer to veer off on a long tangent about his personal career goals. (This actually happened to me once. About 20 minutes into his self-expressive monologue, I wanted to wave my hand and ask, “Hey, remember me?”)
   

Hope these help in your next job interview.

10 tips on conducting effective interviews

Consideration, planning, and communication savvy all play into the successful interview. Regardless of the issue at hand, you’ll get better results if you follow a few best practices.

---

Note: This information is also available as a PDF download.

Whether you’re having a discussion with an employee about performance issues, trying to gather information about why a project failed, looking for feedback from a staff member about team dynamics, or evaluating someone for possible promotion, conducting a successful interview requires skill and planning on your part. Here are some pointers that will make it easier to get the information you need.

#1: Determine your objective

Before you schedule the interview, determine why you want to have it. What information can you gain from the interviewee? How will this information help you achieve your other goals? How will you be better off after having conducted this interview?

#2: Outline your areas

You certainly could write out the questions you plan to ask the interviewee and then read them aloud at the interview. However, this approach may make you look stilted and artificial, and it could hinder the flow of information. Furthermore, by reading your questions, you might miss nonverbal cues, such as body language, that could indicate an area for further questioning.

A better approach is to outline the general areas you want to cover. I do so at the top-right corner of the first sheet of the tablet I’m using. After I discuss an area, I cross it out.

#3: Pick the location

Meeting at the interviewee’s office may make that person more at ease. Be aware of the possibility, though, that the interviewee might instead think you are invading his or her space. In that case, a neutral location, such as a conference room, cafeteria, or even Starbucks might be more appropriate. Present these alternatives and work things out with the interviewee.

#4: Observe standards of etiquette

If you’re meeting in the interviewee’s office, knock before entering. Don’t sit down until invited to do so. During the interview, keep things to yourself. If you start invading the interviewee’s personal space (for example, by gradually taking over the person’s desk), the latter will become less willing to talk. See below for other reasons you should keep your notebook and other items near you.

#5: Open with standard rapport/small talk

Before starting the interview, take a few moments to get to know the interviewee. Ask the standard questions and make standard comments about weather or make positive comments about the meeting room or office or mementos on the interviewee’s desk. However, be careful about speculating on photographs. That child whom you think is a grandson or granddaughter might actually be a son or daughter instead. A college or high school photo of the interviewee could cause problems if you say, “You looked great back then.” Instead, keep your comments general, as in, “What a great photograph.”

Once you’ve spent a few minutes with the getting-acquainted talk, you can start transitioning to the interview. To signal this transition, shift position in your seat , begin to take out a notebook or tablet, or say something like, “I appreciate your time. As you know, I’ve come to discuss….”

#6: Distinguish open and closed questions

Open questions begin with words such as “Who,” “What,” “Where,” or “When.” That is, they give the other person a chance to give a narrative response, without being confined by the question. Such questions are good when one is seeking general or background information. Their disadvantage is that they can cause an interviewee to ramble on endlessly.

Closed questions, on the other hand, call for a specific answer, usually a “Yes” or a “No.” A person who asks a closed question is usually seeking a particular answer to a particular question. The disadvantage of closed questions is that in using them, you may be jumping too quickly to conclusions.

Both types of questions have their place during the interview. In general, begin with open questions. At this point, you want to get the big picture and to avoid jumping to conclusions or making wrong assumptions. When you ask open questions, you allow the other person to bring up matters you then can focus on more specifically.

As the interview progresses, use closed questions either to confirm your understanding or to explore in more depth the matter being discussed. You can also use closed questions to help control the rambling interviewee. If you think you know the point he or she is making, cut to the chase by asking a question such as, “So if I understand, your point is that…”? If you’re right, the person will agree, and you will have saved time. If you’re wrong, the person will let you know, and (you hope) will summarize the point quickly.

#7: Use notations to record impressions

When I take notes, I draw a vertical line one-third from the left of the page. On the right side, I will record the interviewee’s comments. On the left side, I will record impressions and reactions, and reminders of things I should follow up on. In particular, if I hear something that clashes with what I already know or have been told, I will note the comment accordingly.

Be careful about your notations, though. People can read upside down. If you write “ridiculous” on the left side, the other person may take offense. A better alternative is to develop your own set of codes — one for “ridiculous,” another for “follow up,” etc.

#8: Hide your notepad and pen

Try to hide your notepad and pen while you’re writing. Rather than write on a table or desk, keep the pad and pen in your lap so that the interviewee can’t see any notations you’ve made. Furthermore, the person won’t be able to see whether you’re writing anything at all. If the interviewee sees you writing in reaction to some comments and not others, it might affect what he or she is saying or planning to say.

#9: Use tact when exploring sensitive issues

You may be conducting an interview to determine the cause of some high profile problem or failure. Perhaps you’re interviewing someone who either caused the problem or is connected to it. In this case, tact and diplomacy are important, not only from a courtesy standpoint, but more pragmatically, to maximize the chances you get the information you are seeking.

Be careful about the word “you,” because its overuse can make people feel defensive. Instead of saying, for example, “What factors led you to make that decision?” consider a passive construction, such as, “What factors led to the making of that decision?” Similarly, a hypothetical construction can soften a question, such as, “What might have caused the accident?”

I’m not saying you always have to use such techniques. Rather, I’m recommending that you consider such alternatives before asking your questions.

#10: Contradict with caution

Be careful when confronting an interviewee who gives you contradictory information. That person might be innocently mistaken or might have recalled things incorrectly. Calling him or her a liar will hardly endear you, and it certainly won’t advance the interview. Instead, consider a statement such as, “That’s interesting, because I’ve heard different things from other people….” Making this kind of statement signals that you may not disagree, but that the other interviewees might. You could go one step further and adopt the Columbo approach — namely, pleading ignorance and lack of familiarity, then asking the interviewee to explain differences with you have heard elsewhere.

---

Calvin Sun works with organizations in the areas of customer service, communications, and leadership. His Web site is http://www.calvinsun.com and his e-mail address is csun@calvinsun.com.

Don't Talk Too Much in Interview

The gift of gab can be something of a curse during an interview. You could end up talking your way right out of the job.

It's important to remember that interviewers are only human, and their attention tends to wane as you speak. Fully understanding this is critical to effectively communicating during any interview. Your response should be less than a minute and a half when an interviewer asks you to tell me about yourself." Why? You'll have that interviewer's attention for just about 90 seconds.

The average interviewer's attention span looks something like this:
As you begin speaking, the interviewer is listening with nearly full attention.
After about 10 seconds, he begins listening with less intensity.
After 60 seconds, his mind begins to wander and he's devoting less than half his attention to you. The interviewer starts asking questions about your response or begins formulating his next question.
After you've been speaking for 90 seconds without interruption, the interviewer is barely listening at all.

An interviewer's attention level can be nearly impossible to detect, because most people are skilled at nodding their heads and saying "hmmm" while looking at you, all in an effort to disguise their wandering minds. The longer you speak without interruption, the less attention the listener is giving you. Hence, when you provide a long answer that builds to an important conclusion, often the interviewer is no longer listening. This is particularly important when you respond to an interviewer's request to tell him about yourself, because there is just so much you can say on the subject, and you can't be sure what part of your background the interviewer is most interested in learning about.


Your Questions Are Key

Near the end of your response, it's important to keep the interviewer engaged by asking questions.

Skilled interviewers will pose behavioral-event questions, asking you to describe specific examples of your experience. In these situations, your response can easily last much longer than 90 seconds. In such situations, interrupt yourself by asking the interviewer a question like, "Is this the level of detail you are looking for?" or "Is this the type of example you're interested in?" This strategy helps to reengage your listener and promotes two-way communication.

According to Kent Kirch, the global director of recruiting at Deloitte, interviewers are more impressed with your questions than any selling points you try to make. "What's really disappointing to an interviewer is at the end of an interview and I ask the candidate, ‘Do you have any questions I can answer for you?' and he says, ‘Nope, I think you answered them all,' and that's the end of it; it's just really frustrating," he says. "It all goes back to preparation, and [your questions] tells the interviewer you thought about this interview before you walked in the door."

Asking questions can also give you a strategic edge. "People love to talk about themselves," says Austin Cooke, the global recruitment director at Sapient. "So if you as a candidate can kind of get interviewers talking about themselves, you're one step up."

Your interview goals are to ensure you are understood and to make the best presentation of your talents. Engaging interviewers in two-way communication by asking questions helps you ensure they are listening while you deliver your response.