Differences with MS SQL Server
The most important differences between RSQL and MS SQL Server are listed below.
Not Implemented Features
- Stored procedures, views and triggers are not implemented.
- CHECK constraints are not implemented.
- Server-side cursors are not implemented.
- System views are not implemented.
Generality
- In RSQL, only dbo schema exists in a database. It is not possible to create other schemas.
- It is not possible to change the name of login sa.
Syntax
- RSQL doesnt allow single and double quotes in delimited identifiers. MS SQL Server allows it.
Datatypes
- The datatypes
text
,varchar(max)
,ntext
,nvarchar(max)
,image
andvarbinary(max)
are not implemented by RSQL.- You should use a NoSQL database to store such data.
- literal string
- The prefix
e
allows escape sequence in the string. E.g.e'\u1ebf'
.
- The prefix
- literal numeric
- The suffix
d
forces literal number to typenumeric
.
- The suffix
- literal float
- The suffix
f
forces literal number to typefloat
.
- The suffix
Operators and Functions
- Implict
CAST
fromvarchar
orchar
tonumeric
is not allowed.- However, explicit cast or implicit cast for assignment are allowed, because in these two cases, p and s are known.
- Explicit
CAST
from an integer tovarchar
orchar
raises an error if precision is insufficient.- E.g. RSQL raises an error for the expression
CAST(123456 as varchar(3))
. MS SQL Server allows it and returns the string'*'
.
- E.g. RSQL raises an error for the expression
- The expression
date + time
is allowed, and the result isdatetime
. - The expression
datetime + datetime
is not allowed. - The expression
datetime - datetime
is not allowed. - The function
DATEDIFF
for hour, minute, second returns the true interval.- MS SQL Server returns the number of unit boundary crossings.
- The function
ISNULL
forvarbinary
,varchar
,numeric
doesn’t truncate the result.- E.g. for
ISNULL(cast(NULL as varchar(4)), 'abcdefghijkl')
, MS SQL Server returns'abcd'
, which is not intuitive.
- E.g. for
- In the function
CONVERT
(varchar, float, style)
, the style argument is ignored. - The function
RAND()
is allowed, but notRAND(seed)
. - The new function
TYPEOF
returns the type of an expression. - The new function
@@DATEFORMAT
returns the DATEFORMAT setting. - The new function
CHARLEN
returns the full string length, including trailing blanks. - The new functions
RANDOM_VARCHAR
,RANDOM_INT
,RANDOM_BIGINT
,RANDOM_NUMERIC
,RANDOM_FLOAT
,RANDOM_DATE
can be used to quickly fill your test tables with random data.
Statements
- The new statement
ALTER SERVER PARAMETER
modifies the server parameters. - The new statement
SHRINK TABLE
physically shrinks table files. - The new statement
BULK EXPORT
exports tables or selected rows to text file. - The new statement
SLEEP
is equivalent to MS SQL ServerWAITFOR DELAY
. - The new statement
SHOW
displays information about the parameters, databases, tables, users, etc of the current database. - With RSQL, the statement
SET @variable
= value
doesn’t change the value of@@ROWCOUNT
.- But with MS SQL Server, this statement sets the value of rowcount to 1.
- With RSQL,
SET LANGUAGE
resetsDATEFIRST
andDATEFORMAT
to language default value.- MS SQL Server keeps the current
DATEFIRST
andDATEFORMAT
unchanged, but I think this behaviour is confusing.
- MS SQL Server keeps the current
Batches and Scripts
- In RSQL, current database (see
USE
) crosses batch boundaries like MS SQL Server.- But current
LANGUAGE
,DATEFIRST
andDATEFORMAT
settings don’t span to the subsequent batch.
- But current
Errors
With MS SQL Server, a
NULL
value is returned for division by 0, primary key duplicate, etc. An error message is raised, but the sql script continues, which is often not the expected behaviour.- On MS SQL Server, you must put
SET XACT_ABORT ON
at the beginning of the batch to make it abort on arithmetic or integrity error. - On MS SQL Server, you can also set this option at the server level by using the command
sp_configure 'user options', 16384
.
- On MS SQL Server, you must put
With RSQL, the default behaviour is always to abort the batch when an error is raised, which is the same as MS SQL Server having the
XACT_ABORT
option set to ON.