The most commonly used fields are the integer types and the character types.

---

Technical Note:  This note is for those of you who are interested in the details of column storage. It is not necessary to understand this to have a complete understanding of T-SQL.

If a row in a table uses only fixed-length columns, the storage requirements for the row are four bytes plus the length of the data in the row. For a variable-length column, it is four bytes plus the length of all the data in the row (a varchar(50) with ten characters in it only counts as ten!), plus one byte for each variable-length column in the row, plus two bytes to record total row length, plus one byte for a column offset table, plus (total length/256) bytes rounded up to nearest byte. (Phew!)

Note that a column that accepts nulls is stored as a variable-length field. From a space standpoint, it makes no sense to create fixed-length, nullable character fields, although it may make sense from a database design perspective.

---

Column Manipulation

When you are working with character (or string) data, such as names, addresses, or titles of books, T-SQL provides methods for manipulating the way output is displayed. So far, I have only selected a column from the server and accepted the way it was returned. This section discusses the different transformations you can perform on columns.

Selecting and Using Constant Strings

You may remember from earlier that you can select constant strings in a SELECT:

select     “Hello World!”

----------------
Hello     World!

You can also select constant strings for appearance in a SELECT list next to data from a table:

select     “Hello, my name is”, fname
from       employee

                  fname
-----------
Hello, my name is  Paolo
Hello, my name is  Pedro
Hello, my name is  Victoria
Hello, my name is  Helen
[…]
Hello, my name is  Howard
Hello, my name is  Martin
Hello, my name is  Gary
Hello, my name is  Daniel

(43   row(s) affected)

There are two columns of output in this result set: the first column has the words, “Hello, my name is” for every row in the employee table. The second column contains the first name of each employee.

Now, what if you wanted to get similar output, where the entire introduction was in a single column? To do that, you need to use the addition (+) operator.

Using the Addition Operator on Strings

If we want to add two numbers together, we can use the addition sign. If we want to add two strings together, we can also use the addition sign. When used between two strings, the addition operator allows you to concatenate the strings (that is, to add the second string on to the end of the first).

In this next example, I’ll ask for the entire introduction in a column called “Introduction.” In order to make the output pretty, I have to add an extra space in the constant string. Otherwise, the first names of all the employees will be pushed up against the word is.

select     “Hello, my name is” + fname Introduction
from       employee

Introduction
------------------------------------
Hello, my name is Paolo
Hello, my name is Pedro
Hello, my name is Victoria
Hello, my name is Helen
[…]
Hello, my name is Howard
Hello, my name is Martin
Hello, my name is Gary
Hello, my name is Daniel

(43 row(s) affected)

Although this looks a lot like the previous example, in this query I retrieved the static string plus the data in a single column instead of two columns. Use the + sign to concatenate strings.

Another Column Manipulation Example

You can add two columns in the same row together with or without static strings. A common example of this application is printing user names. In the author table, the first name is stored in the au_fname column and the last name in au_lname. Suppose that I wanted a list of author names, listed with last name first, first name last, and separated by a comma. I would like to call this “Author Names.” Here is the query:

select    au_lname + “, “ + au_fname “Author Names”
from      authors
order by “Author Names”

User Names
--------------------------------------------------------------
Bennet, Abraham
Blotchet-Halls, Reginald
Carson, Cheryl
DeFrance, Michel
Dull, Ann
Green, Marjorie
Greene, Morningstar
Gringlesby, Burt
Hunter, Sheryl
Karsen, Livia
Locksley, Charlene
MacFeather, Stearns
McBadden, Heather
O’Leary, Michael
Panteley, Sylvia
Ringer, Albert
Ringer, Anne
Smith, Meander
Straight, Dean
Stringer, Dirk
White, Johnson
Yokomoto, Akiko
del Castillo, Innes

(23 row(s) affected)

Using the + operator with strings enables you to put multiple columns and static strings together in a single column of the result set. I used an alias to name the column; then I used the alias in the ORDER BY clause.

---

Technical Note:  I could have also ordered this last result set “by au_lname, au_fname” to achieve the same result, or I could have asked for ORDER BY 1. When you ask for an ordering to occur on a contrived result, by which I mean something that exists only in the result set and not in the table itself, the server first builds the result set and then sorts it. To support this sort, the server may build a dynamic index. It may be slower than you want, but it is nice to know that you can order by any column in your result set.

---