Datatypes
Datatypes specify the type of data that a variable or column can hold.
They are used:
- in
CREATE TABLE
, for column definition. - in
DECLARE
, for variable declaration. - in
CAST
orCONVERT
functions.
List of datatypes, by precedence order:
Datatype | Description | Range | Literal constant | Examples |
---|---|---|---|---|
varbinary(p) | variable-length binary string | precision p is the max length of the binary string. precision = 1 to 8000 |
0x1234ff |
declare @a varbinary(200); |
char(p) | fixed-length string | precision p is the length of the string. precision = 1 to 8000 Padded with blanks. |
declare @a char(30); |
|
varchar(p) | variable-length string | precision p is the max length of the string. precision = 1 to 8000 |
'Hello' |
declare @a varchar(30); |
bit | value of one bit | 0, 1 | declare @a bit = 1; |
|
tinyint | 1-byte unsigned integer | 0 to 255 | declare @a tinyint = 45; |
|
smallint | 2-bytes signed integer | -32,768 to 32,767 | declare @a smallint = 1234; |
|
int | 4-bytes signed integer | -2,147,483,648 to 2,147,483,647 | 0 |
declare @a int = 756483; |
bigint | 8-bytes signed integer | -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 | declare @a bigint = 536477838; |
|
money | Base 10 decimal number. Similar to numeric(19,4) |
-999999999999999.9999 to 999999999999999.9999 | $0 |
declare @a money = $756483; |
numeric(p, s) | Base 10 decimal number | precision p = 1 to 34 scale s = 0 to 34 with scale <= precision |
0.0 |
declare @a numeric(12,2) = 1.23; |
float | Base 2 double-precision floating-point number | +-2.2250738585072014e–308 to +-1.7976931348623158e308 | 0e0 |
declare @a float = 123.45e0; |
date | date | 0001-01-01 to 9999-12-31 | declare @a date, @b date; |
|
time | time of a day | 00:00:00 to 23:59:59.999999999 | declare @a time; |
|
datetime | date and time | 0001-01-01T00:00:00 to 9999-12-31T23:59:59.999999999 | declare @a datetime; |
Other Datatypes
RSQL doesn’t implement all datatypes of MS SQL Server, but the following datatypes are considered as synonyms:
nchar
is synonym ofchar
.nvarchar
is synonym ofvarchar
.integer
is synonym ofint
.smallmoney
is synonym ofmoney
.decimal
anddec
are synonyms ofnumeric
.real
anddouble precision
are synonyms offloat
.smalldatetime
anddatetime2
are synonyms ofdatetime
.
The datatypes
text
,varchar(max)
,ntext
,nvarchar(max)
,image
andvarbinary(max)
are not implemented by RSQL.You should store these data in a NoSQL database server, like MongoDB.
The SHA-1 hash of a document can be used as the key, as it is unique. You store the document in the NoSQL database along with the key, and store only the key in the SQL database.
Images, pdfs or other blobs are usually accessed from many applications, don’t need transactions, don’t need strong coherency, and have different backup requirements than those for tabular data. NoSQL databases are the best storage for these particular needs.
Datatype Precedence
Datatype conversions to datatype of higher precedence often occur in calculation.
When operands have different datatypes, most operators will promote the operand with lowest datatype precedence to match the datatype of the other operand.
In particular, literals in an expression are often the cause of datatype promotions:
- When a literal integer is encountered, the result will be
int
. - And when a literal numeric is encountered, the result will be
numeric
. - And when a literal float is encountered, the result will be
float
.
For example:
DECLARE @a SMALLINT = 123;
PRINT ( @a + 10 ) * 12.5 + 4e-1;
-- 'smallint' + 'int' --> 'int'
-- 'int' * 'numeric' --> 'numeric'
-- 'numeric + 'float' --> 'float'
The result is:
1662.9 <--- result is 'float'
To debug your expressions, you can use the RSQL TYPEOF
function to get the datatype of an expression.
PRINT 123.45 + 5000
PRINT TYPEOF(123.45);
PRINT TYPEOF(5000);
PRINT TYPEOF(123.45 + 5000);
The result is:
5123.45
NUMERIC(5, 2)
INT
NUMERIC(13, 2)