Datatypes

Datatypes specify the type of data that a variable or column can hold.

They are used:

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);
set @a = 0x1234abcd;
print @a;
char(p) fixed-length string precision p is the length of the string.
precision = 1 to 8000
Padded with blanks.
declare @a char(30);
set @a = 'O''Hara';
print @a;
varchar(p) variable-length string precision p is the max length of the string.
precision = 1 to 8000
'Hello'
'O''Hara'
declare @a varchar(30);
set @a = 'O''Hara';
print @a;
bit value of one bit 0, 1
declare @a bit = 1;
print @a;
tinyint 1-byte unsigned integer 0 to 255
declare @a tinyint = 45;
print @a;
smallint 2-bytes signed integer -32,768 to 32,767
declare @a smallint = 1234;
print @a;
int 4-bytes signed integer -2,147,483,648 to 2,147,483,647
0
5678
-123
declare @a int = 756483;
print @a;
bigint 8-bytes signed integer -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
declare @a bigint = 536477838;
print @a;
money Base 10 decimal number.
Similar to numeric(19,4)
-999999999999999.9999 to 999999999999999.9999
$0
$123
$-680.75
declare @a money = $756483;
print @a;
numeric(p, s) Base 10 decimal number precision p = 1 to 34
scale s = 0 to 34
with scale <= precision
0.0
123.0
-680.75
declare @a numeric(12,2) = 1.23;
print @a;
float Base 2 double-precision floating-point number +-2.2250738585072014e–308 to +-1.7976931348623158e308
0e0
123e0
-680.75e6
declare @a float = 123.45e0;
print @a;
date date 0001-01-01 to 9999-12-31
declare @a date, @b date;
set @a = '19780302';
set @b = '1978-03-02';
print @a, @b;
time time of a day 00:00:00 to 23:59:59.999999999
declare @a time;
set @a = '12:30:05';
print @a;
datetime date and time 0001-01-01T00:00:00 to 9999-12-31T23:59:59.999999999
declare @a datetime;
declare @b datetime;
set @a = '19780302 12:30:05';
set @b = '1978-03-02T12:30:05';
print @a, @b;

Other Datatypes

RSQL doesn’t implement all datatypes of MS SQL Server, but the following datatypes are considered as synonyms:

The datatypes text, varchar(max), ntext, nvarchar(max), image and varbinary(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:

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)
RSQL, a simple alternative to Microsoft SQL Server