Database Reference
In-Depth Information
can be stored in a database for retrieval by an application. This kind of data
is generally called
binary large object
(BLOB) data.
This information is usually stored in SQL Server in one of the follow-
ing data types: binary, varbinary, and image. As with the character data
types, the existence of the
var
prefix denotes that the given attribute has
variable-length values in the field. Therefore,
binary
defines a fixed-width
attribute containing binary data, and
varbinary
specifies the
maximum
width of an attribute containing the binary data. The
image
data type sim-
ply specifies that the attribute contains variable-length binary data, similar
to varbinary but with much greater storage potential.
Character data can also come in forms much longer than the standard
alphanumeric data types described earlier. What if you need to store free-
form text in a single field, such as raw resume information? Two
charac-
ter large object
(CLOB) data types handle this information:
text
and
ntext.
These two data types are designed to handle large amounts of char-
acter data in a single field. Again, as with the other character data types,
the
n
prefix indicates whether or not the data is being stored in the
Unicode format. Choose these data types when you will have very large
amounts of alphanumeric text stored as a single attribute in an entity.
Dates and Times
Nearly every data model in existence requires that some entities have at-
tributes that are related to dates and times. Date and time data can be used
to track the time a change was made to an order, the hire date for employ-
ees, or even the delivery time for products. Every RDBMS has its own im-
plementations of date and time data types that store this data. For SQL
Server 2008, there are now six data types for this purpose. This is an im-
provement over previous versions of SQL Server, which only had two data
types: datetime and smalldatetime. Each data type stores date-oriented in-
formation; the difference is in the precision of the data and in the range of
valid values.
First, let's look at the old standards
.
Datetime
stores date and time
data with 1 millisecond accuracy. For example, suppose you are inserting a
record into a table that has a datetime column and the value inserted is
12/01/2006 6:00PM
Search WWH ::
Custom Search