- Datatypes Supported by SQL Server You must choose a datatype whenever you create a column in a table or when you declare a local variable. In each case, the choice of a datatype determines the following: . The kind of data that can be stored in a column (numbers, strings, binary strings, bit values, or dates) . In the case of numeric and date datatypes, the range of values permitted in the column . In the case of strings and binary data, the maximum length of data you can store in the column
- Nullability and Datatypes When you define a column, you must also decide whether to allow null values in that column. A column that allows nulls requires more space to store a value and might have other performance or storage implications.
- Character and Binary Datatypes There are three valid datatypes for storing strings: . CHAR, for storing fixed-length strings . VARCHAR, for variable-length strings . TEXT, for strings of virtually unlimitted size (up to two gigabytes of text per row). name varchar (40) state char (2) Binary datatypes store binary strings-strings consisting of binary values instead of characters. The most common uses of binary data are for TIMESTAMPS and IMAGE datatypes: . BINARY, for fixed-length binary strings . VARBINARY, for variable-length strings . IMAGE, for storing large binary strings
- User-defined datatypes User-defined datatypes enable you to subclass an existing system datatype. When you install SQL Server, two user-defined datatypes are already available: SYSNAME and TIMESTAMP.
- Timestamps Timestamp columns enable the user to uniquely identify versions of each row in the table. There are three important steps involving timestamps: . Creation of a table with a timestamp column . Automatic updating of a timestamp column . Optimistic locking, using the timestamp value. create table timestamp_example (id int not null, code char(3) not null, ts timestamp not null)
- Text and Image Data The longest variable-length column is only 255 characters. The maximum row length in SQL Server is limited by the size of the data page, which is 2 KB. Text and Image provides a mechanism for storing binary large objects (BLOBs) as large as two gigabytes per row.
- DATETIME Datatypes minimum value maximum value precision storage . datetime Jan 1, 1753 Dec 31, 9999 3 msec 8 bytes . smalldatetime Jan 1, 1900 Jun 6, 2079 1 minute 4 bytes
- Dateformat Options By default the server treats a date in the format xx/yy/zz as a month/day/year sequence (mdy). SQL Server also supports several other date orderings. the SET DATEFORMAT command, you can choose other date orders. Valid choices are mdy, dmy, ymd, ydm, myd, dym. set dateformat ymd
- Search Behavior with Dates select * from date_table where date = "9/3/96" select * from date_table where date between "9/3/96" and "9/3/96 23:59:59.999"
- Logical Datatype: Bit SQL Server supports a logical datatype of bit for flag columns that store a value of 1 or 0 or true/flase. create table bit_sample (id int not null, description varchar (30) null, active bit not null) bit columns have several unique characteristics: . They don't permit null values . They can't be indexed . Several bit columns can occupy a single byte (SQL Server collects up to eight bit columns into a single byte of physical storage).
- Numeric Datatypes Numeric types fall into basic categories: . Integer, including int, smallint, and tinyint . Floating-point datatypes, including float and real . Exact numeric datatypes, numeric and decimal . Money datatypes, money and smallmoney int smallint tinyint Minimum -2^31 -2^15 0 Max 2^31 2^15 -1 255 Storage 4 bytes 2 bytes 1
- Floating-point Datatypes Float and Real can store very large numbers. . Float (double precision, 8 bytes) . Real (4 bytes)
- Exact Numeric Datatypes Decimal (or dec) and numeric (Identical) numeric (7,2)
- Money Datatypes money smallmoney Range +- 922,337,203,685,477.5808 +-214,748.3647 Storage 8 bytes 4 bytes
- Summary of datatypes See table 5.6 (Page 121)
Last update January 13, 1998.