Databases Reference
In-Depth Information
Data type selections can also impact performance and flexibility. DMBSs can
store, retrieve, and manipulate some data types more quickly than others. What
you can do with data, including the types of calculations in which the data can
be used, also varies by data type.
Data types fall into general categories, with most DBMSs supporting the same
basic categories. Typically, these will include:
▲
Numeric:
For storage of integer, floating point, and scientific format
numeric data.
▲
Binary:
Fixed or variable-length flexible storage that can store a raw
binary image, even files such as photos or executable program.
▲
String:
Fixed or variable-length character storage.
▲
Date/Time:
Stores date and time values, usually with various levels of
accuracy supported.
▲
Large object (LOB):
Used to store large blocks of text or binary data,
with “large” as a relative term defined by the DBMS.
▲
Other:
Specialized storage for data such as globally unique identifiers
(GUIDs), XML data, or generic storage that can accept nearly any kind of
data without having to predefine the data.
In addition to defining the data columns, you can also define table con-
straints. Depending on the DBMS, these can include primary key, foreign key,
check, unique, and default constraints. You can specify them when you create
the table or, in most cases, add them later.
The basic CREATE TABLE syntax requires you to specify the table name and
column list, along with the data type for each column. For example, to create
the General Hardware Company CUSTOMER table, you could use the following
command:
CREATE TABLE CUSTOMER
(CustomerNumber as int,
CustomerName as nvarchar(40),
SalespersonNumber as int,
HQCity as nvarchar(40))
This is similar to the statement example you saw earlier in this chapter. This
statement creates the table CUSTOMER with the following four columns:
▲
CustomerNumber that stores integer data.
▲
CustomerName that stores up to 40 Unicode characters.
SalespersonNumber that stores integer data.
▲
HQCity that stores up to 40 Unicode characters.
▲
