|
CHAPTER 2
Single-Table Queries
61
This workaround turned out to be quite convoluted. In this particular case, you can use a math-
ematical workaround that avoids division altogether.
SELECT col1, col2
FROM dbo.T1
WHERE (col1 > 0 AND col2 > 2*col1) OR (col1 < 0 AND col2 < 2*col1);
I included this example to explain the unique and important concept of all-at-once operations and
to elaborate on the fact that SQL Server guarantees the processing order of the WHEN clauses in a
CASE expression.
Working with Character Data
In this section, I cover query manipulation of character data, including data types, collation, operators
and functions, and pattern matching.
data Types
SQL Server supports two kinds of character data types—regular and Unicode. Regular data types
include CHAR and VARCHAR, and Unicode data types include NCHAR and NVARCHAR. Regular
characters use one byte of storage for each character, whereas Unicode data requires two bytes per
character, and in cases in which a surrogate pair is needed, four bytes are required. If you choose a
regular character type for a column, you are restricted to only one language in addition to English.
The language support for the column is determined by the column’s effective collation, which I’ll de-
scribe shortly. With Unicode data types, multiple languages are supported. So if you store character
data in multiple languages, make sure that you use Unicode character types and not regular ones.
The two kinds of character data types also differ in the way in which literals are expressed. When
expressing a regular character literal, you simply use single quotes: ‘This is a regular character string
literal’. When expressing a Unicode character literal, you need to specify the character N (for National)
as a prefix: N’This is a Unicode character string literal’.
Any data type without the VAR element (CHAR, NCHAR) in its name has a fixed length, which
means that SQL Server preserves space in the row based on the column’s defined size and not on
the actual number of characters in the character string. For example, when a column is defined as
CHAR(25), SQL Server preserves space for 25 characters in the row regardless of the length of the
stored character string. Because no expansion of the row is required when the strings are expanded,
fixed-length data types are more suited for write-focused systems. But because storage consumption
is not optimal with fixed-length strings, you pay more when reading data.
A data type with the VAR element (VARCHAR, NVARCHAR) in its name has a variable length, which
means that SQL Server uses as much storage space in the row as required to store the characters that
appear in the character string, plus two extra bytes for offset data. For example, when a column is de-
fined as VARCHAR(25), the maximum number of characters supported is 25, but in practice, the actual
number of characters in the string determines the amount of storage. Because storage consumption
www.it-ebooks.info
62
Microsoft SQL Server 2012 T-SQL Fundamentals
for these data types is less than that for fixed-length types, read operations are faster. However, up-
dates might result in row expansion, which might result in data movement outside the current page.
Therefore, updates of data having variable-length data types are less efficient than updates of data
having fixed-length data types.
note
If compression is used, the storage requirements change. For details about compres-
sion, see “Data Compression” in SQL Server Books Online at http://msdn.microsoft.com
/en-us/library/cc280449.aspx.
You can also define the variable-length data types with the MAX specifier instead of a maximum
number of characters. When the column is defined with the MAX specifier, any value with a size up to
a certain threshold (8,000 bytes by default) can be stored inline in the row (as long as it can fit in the
row). Any value with a size above the threshold is stored external to the row as a large object (LOB).
Later in this chapter, in the “Querying Metadata” section, I explain how you can obtain metadata
information about objects in the database, including the data types of columns.
Collation
Collation is a property of character data that encapsulates several aspects, including language sup-
port, sort order, case sensitivity, accent sensitivity, and more. To get the set of supported collations
and their descriptions, you can query the table function fn_helpcollations as follows.
SELECT name, description
FROM sys.fn_helpcollations();
For example, the following list explains the collation Latin1_General_CI_AS:
■
■
Latin1_General Code page 1252 is used. (This supports English and German characters, as
well as characters used by most Western European countries.)
■
■
Do'stlaringiz bilan baham: | 
