| Previous | Table of Contents | Next |
Week 1
Day 4
All About Columns
Today you work with table columns. It’s going to be a big day, and I cover a lot of material. Much of what I am about to discuss is stuff that will help you later—in the next couple of weeks—and throughout your use of SQL. Don’t try to memorize all the functions or tables. Just get comfortable playing with them and let today’s lesson give you an idea of what is possible. Refer back to the function examples in the middle of the chapter as you find the need to use them or for help with the exercises at the end of the day. Today I discuss the following topics:
- • Data types
- • Column manipulation
- • The CONVERT function
- • Arithmetic and string operators
- • String functions, searching for strings
- • Working with numeric columns
- • Working with dates
- • System functions and tables
- • Column manipulation
Data Types
Each column in a table has a data type. All of the data in the column must be of the same type. The following table describes each of the 17 data types available in MS SQL Server and gives an example of what they might be used for. Variable-length columns are best when the data contained in them will be of different lengths, such as names, addresses, and titles. Fixed- length columns are best used when the data is of the same length, such as phone numbers, social security numbers, status codes, or ZIP codes.
A data type determines how the data for a particular column is accessed, indexed, and physically stored on the server.
Variable-length columns incur a little overhead to track where the columns end. Although they incur some overhead, variable-length fields will usually save you space over their fixed-length counterparts when your data varies dramatically in length. If you’re curious, see the following technical note for details on physical row storage.
Table 4.1 describes each of the data types in SQL Server and how they are commonly used.
| Long Name | Use | Example | Description |
|---|---|---|---|
| Binary | binary(4) | 0x000A34FF | Binary data types are used to store bit patterns. A binary column may hold no more than 255, base-16 byte pairs. In the example here, four byte-pairs are stored (00, 0A, 34, and FF). See BLOBs for a data type that can be used to hold large amounts of text or binary data. |
| Variable binary | varbinary(8) | 0xA34FF | (Same as above.) |
| Character | char(6) | “John” | Fixed-length character. Useful for character strings of equal or very nearly equal length, such as phone numbers. Spaces are used to pad strings that are not as long as the field. |
| Variable character | varchar(6) | “John” | Variable-length character fields are best for most strings. |
| Datetime | datetime | Jan 1, 1994 12:15:00.000 am | Datetime fields are used for precise storage of dates and times. The millisecond field does not store increments of a single millisecond, but rather one three-hundredth of a second (3.33 milliseconds). Datetimes can range from January 1, 1753 to December 31, 9999. Values outside the range must be stored as character. |
| Small datetime | smalldatetime | Jan 1, 1994 12:15am | Small datetimes are half the size of datetimes. They use increments of one minute and represent dates from January 1, 1900 through June 6, 2079. |
| Precise decimal | decimal(4, 2) or numeric(4,2) | 13.22 | Decimal/numeric data types store fractional numerics precisely. The first parameter (precision) specifies how many digits are allowed in the field. (The decimal does not count.) The second parameter specifies how many digits may come after the decimal. In this example, I could represent numbers from –99.99 to 99.99. |
| Big floating point | float(15) | 64023.0134 | Floating-point numbers are not guaranteed to be stored precisely. SQL Server rounds up numbers that binary math can’t handle (base-10 decimals ending in 2, 6, or 7). Floats take a parameter specifying the total number of digits. |
| Little float | real(7) | 17.5545 | Half the size of a float; the same rules apply. |
| Integer | int | 734534 | Integers are four bytes wide and store numbers between plus or minus two billion. |
| Small integer | smallint | 12331 | Small ints are half the size of integers, ranging from –32,768 through 32,767. |
| Tiny integer | tinyint | 5 | Tiny ints are half again the size of tiny integers—a single byte—and may not be negative. Values run from 0 to 255. Perfect for an age column (until medicine greatly improves). |
| Bit | bit | 1 | Bits are the smallest data type available. They are one bit in size—one-eighth of a byte. Bits may not be null and can have a value of either 0 or 1. Bits may not be indexed. If you have more than one bit column in a row, the server will “bit pack” the values into a single byte. Neat! |
| Money | money | $654.2225 | Money types range from plus or minus 922 trillion. Money types store four digits to the right of the decimal and are stored as fixed-point integers. |
| Small money | smallmoney | $32.15 | Small money can handle about plus or minus $214,000, with four digits to the right of the decimal. Half the size of money. |
| Text | text | “Four score and seven…” | Text fields can be up to 2GB in size. Text fields are Binary Large Objects (BLOBs) and are subject to a great many limitations. They cannot be ORDER BYed, indexed, or grouped, and handling them inside an application program takes some extra work. You can search a text field using LIKE. |
| Image | image | 0x00223FE2… | Image data can be used to store any type of binary data, including images (GIF, JPG, TIFF, and so on) executables, or anything else you can store on your disk drive. Images are also BLOBs. They are subject to the same limitations as text fields, but you cannot search an image field. |
| Previous | Table of Contents | Next |