Understand the data type definitions
- Last UpdatedNov 12, 2024
- 2 minute read
All data types (except timestamp on SQL Server) are user-defined, so they can be specified without reference to a specific database, and so their underlying database representation can be more easily changed. A limited number of types are employed, which result in some fields being bigger than they would need to be.
The following table describes the basic types and their anticipated mappings:
|
Name |
SQL Server |
|---|---|
|
bigint64 |
bigint |
|
blob |
image |
|
tfloat |
float |
|
int32 |
int |
|
logical |
bit |
|
notes |
nvarchar(max) |
|
string20 |
nvarchar(20) |
|
string40 |
nvarchar(40) |
|
string80 |
nvarchar(80) |
|
string254 |
nvarchar(254) |
|
string1000 |
nvarchar(1000) |
|
string1700 |
nvarchar(1700) |
|
string2000 |
nvarchar(2000) |
|
string4000 |
nvarchar(4000) |
|
tdatetime |
datetime |
|
tdatetime2 |
datetime2 |
|
tdatetime20 |
datetime2(0) |
|
tdatetimems |
datetime |
|
time0 |
time(0) |
|
tsmalldatetime |
smalldatetime |
|
uint32 |
numeric(10) |
-
When defining database tables, each basic user-defined data type except logical may be modified by appending "_NULL" for those fields that are nullable. If there’s no "_NULL" suffix, the type is assumed to be non-null.
-
The logical data type is constrained to have only values of 0 (No/False) or 1 (Yes/True).
-
The uint32 data type is a 32‑bit unsigned integer that can have a maximum value of 4,294,967,296. Therefore it needs a 10‑digit field to hold it.
-
Whole numbers which are assigned sequentially by the system are shown as an ID data type. These are called identity columns in SQL Server. All identity columns are defined to start with ID(1). The number following "ID" is the starting value. All IDs are assumed to increment by one and never wrap around. They are handled as 32‑bit integer except in heavily inserted tables, for which they are defined as bigints.
-
In SQL Server, the timestamp type is an 8‑byte binary number. Many tables have a mod_id field as one of their columns, which is this type, and is used by the business rules layer to allow optimistic concurrency (no SELECT FOR UPDATE). Other tables have a non-null last_edit_at field that contains the date/time the record was last modified, which will serve the same purpose in lieu of a mod_id field. Records for which there is no chance of concurrent updates will not have a mod_id field.
-
Fields with "pct" in their name, which are described as "%", are really decimal fractions with values from 0 to 1 inclusive, where 1 = 100%.
-
Milliseconds are stripped from all datetime columns except labor usage.
-
The smalldatetime data type is used to remove seconds from those columns using it (e.g., the effectivity dates for shift patterns).
-
The datetime2(0) data type is used to store datetimes with no decimal values (i.e., YYYY-MM-DD hh:mm:ss).
-
For definitions of the SQL Server data types listed here, see the Microsoft SQL Server online documentation.