Exact numeric data. If you need to get back exactly the value you input, choose one of the exact numeric data types. Two exact numeric data types exist: decimal and numeric, which are virtually the same data type. You must specify the precision and scale for these data types, in the format decimal (p,s). The precision is the total number of digits stored on both sides of the decimal point, and the scale is the number to the right of the decimal point. You can store the number 123.4567 in a column using a numeric (7,4) data type. If the number to be stored has more decimal places than will fit, it is rounded to fit. Numeric data types can use anything from 2 to 17 bytes to store the data.
Approximate numeric data. Some numbers cannot be expressed exactly, such as one-third or pi; to express such numbers, use real or float data types. Real stores values to a precision between 1 and 7. Float, sometimes called double precision, can store data with a precision of 8 to 15. You might use real or float in scientific applications where you have to store numbers that do not lend themselves to exact binary representations. The last digit or two may not be exactly preserved when these numbers are converted to binary, so do not use them for exact comparisons in WHERE conditions.
Binary. Suppose you need to store binary data in your database. You have the choice of binary or varbinary data types, which are fixed and variable length, respectively.
Money. The two data types for monetary data are money and smallmoney. The money data type handles values up to plus or minus 922 trillion. Most of us can use the smallmoney data type, which covers the value range -214,748.3648 to +214,748.3647. By convention, money data types are stored to four decimal places and displayed to two. Note that many financial transactions don't use the money data types. For example, stock trades can involve 1/32nd fractions, which need five decimal places.
Dates. SQL Server stores both date and time in one column, so if you ask for the current date with the GETDATE() function, you also get the time. The two date formats are datetime and smalldatetime. The smalldatetime data type covers a range of January 1, 1900, through June 6, 2079, and includes the time to the minute. That range should be good enough for scheduling most projects. The datetime data type is good up to December 31, 9999 (watch for that Y10K problem), and starts at January 1, 1753. Why 1753? It's linked to the change from the Julian to Gregorian calendar. Although the Gregorian calendar was proposed some years before, during a period of approximately 30 years, some countries had adopted the new calendar and others had not. So you need to know the location for the date to be unambiguous. Also, the year started on March 1, not January 1. So a date such as January 15, 1492, can be interpreted as falling in either the first month of 1492 or the 11th month of 1493. SQL Server's creators chose to avoid this confusion by refusing to accept dates before 1753. Also, note that the datetime data type shows times down to the millisecond, but it is accurate only to 1/300th of a second.
Text and image. Use text for character data if you need to store more than 255 characters in SQL Server 6.5, or more than 8000 in SQL Server 7.0. Use image for binary large objects (BLOBs) such as digital images. With text and image data types, the data is not stored in the row, so the limit of the page size does not apply. All that is stored in the row is a pointer to the database pages that contain the data. You must follow special procedures to update this type of data, and they are beyond the scope of this article. (For more information on BLOBs, see Michael Otey, "Who's Afraid of the Big, Bad BLOB?" April 1999.) For now, be aware that changes to text or image data types might not be logged, and pointers in the row might not be updated; only the data in the text or image field changes.
Special Data Types
Some data types do not fit neatly into any specific category. One is the bit data type, an integer with a value of 0 or 1 (you can store eight of them in one byte). You often use the bit data type for true/false flags, such as to note whether an author has a contract or has written a bestseller, or for other similar values. One minor difference is that SQL Server 7.0 allows nulls in columns using the bit data type, whereas SQL Server 6.5 does not—the value has to be 0 or 1.
SQL Server uses the timestamp data type internally; this type provides a unique value that is updated every time the data in a table row is updated. Timestamps are internal values that SQL Server maintains and they do not translate to dates and times.
SQL Server 7.0 introduces two other new data types, and I will leave the details about them for future columns. The uniqueidentifier data type lets you assign a globally unique value to a column. Globally unique means unique not just within this database or this computer, but everywhere. This type is relevant if you plan to merge data from various sources into a data warehouse. The cursor data type is for cursor variables. The use of cursors is an advanced topic, which I will not attempt to cover here.
User-Defined Data Types
SQL Server lets you define your own data types, but they must be variations on the system-supplied data types. Usually they are based on a system-supplied data type with a predetermined size (in the case of character or numeric data) and associated defaults and rules. For example, you can define a zipcode data type as char(10) and a phone number as char(14). The zipcode data type will ensure that all zip code formats are consistent in all zip code columns, for any client, vendor, employee, or personal contact anywhere in the world.
SQL Server is not very helpful when you want to define data types from scratch. For example, you cannot define a new data type with associated properties, such as latitude and longitude data types for a GIS application, along with the spherical trigonometry methods necessary to compute the distance between two points. Perhaps one day SQL Server will offer more flexibility.
For now, you can add data types from the SQL Server Enterprise Manager or from the Query Analyzer (in SQL Server 6.5, the ISQL/w window). Each data type you add is good in only the current database. If you need to define a data type for all databases, put it in the Model database. The new data type will appear in all new databases from then on. Another approach is to write, or use SQL Server's Generate SQL Script option to produce, a script that you can run in each database. In Enterprise Manager, right-click on the database, select All Tasks, Generate SQL Scripts, then pick the option to create a script for the data types.
You can use the graphical interface you see in Screen 1 to add a data type. In Enterprise Manager, expand Databases, expand the database, and select User-Defined Data Types. Right-click and select New User-Defined Datatype Properties. You then define the data type.
As usual in SQL Server, you can also add a data type from the Query Analyzer. You can see the syntax in Screen 2. Notice that the quotes go around the system data type name, but not around the name of the new data type you are defining. Also, a good practice is to state whether the data type allows NULL values. You can override this setting when you use the data type for a specific column, but specifying what is allowed helps set standards, which is why you define data types.
If you define data types, the table creation statement might look like the code in Listing 2. You do not have to give the length of the column where you specified a user-defined data type, because the length is already implied. And you can let the null/not null default to the data type's specification, although it doesn't hurt to include it again.
Choose the Right Type
Picking the right data type is part of table design. In SQL Server 6.5, changing a column after you define it is difficult; in SQL Server 7.0, you can change a column's data type, but do so with caution.
User-defined data types are a good way to ensure consistency across applications and development teams, as long as everyone is using SQL Server. However, if you need to move the SQL code to a different database management system (DBMS), avoid user-defined data types.
End of Article
Prev. page
1
[2]
next page -->
