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 and varbinary(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'.
• literal numeric
  • The suffix d forces literal number to type numeric.
• literal float
  • The suffix f forces literal number to type float.

Operators and Functions

• Implict CAST from varchar or char to numeric 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 to varchar or char 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 '*'.
• The expression date + time is allowed, and the result is datetime.
• 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 for varbinary, 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.
• In the function CONVERT(varchar, float, style), the style argument is ignored.
• The function RAND() is allowed, but not RAND(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 Server WAITFOR 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 resets DATEFIRST and DATEFORMAT to language default value.
  • MS SQL Server keeps the current DATEFIRST and DATEFORMAT unchanged, but I think this behaviour is confusing.

Batches and Scripts

• In RSQL, current database (see USE) crosses batch boundaries like MS SQL Server.
  • But current LANGUAGE, DATEFIRST and DATEFORMAT settings don’t span to the subsequent batch.

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.

• 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.