Documentation

Managing differences in behavior

Babelfish tries to be as compatible with MS SQL as possible. However, some variances still persist. In this section you will learn how things differ from each other and which incompatibilities are known to exist.

Application roles

• MS SQL: Application roles are set with CREATE APPLICATION ROLE or sp_setapprole
• Babelfish: This is currently not supported.

COMPATIBILITY_LEVEL

• MS SQL: Backwards compatibility is set with COMPATIBILITY_LEVEL
• Babelfish: ALTER DATABASE... SET COMPATIBILITY LEVEL will be accepted and ignored

sysdatabases.cmptlevel

• MS SQL: sysdatabases.cmptlevel will always be NULL
• Babelfish: ALTER DATABASE... SET COMPATIBILITY LEVEL will be accepted and ignored

Hints

• MS SQL: Supports table hints, query hints, and join hints.
• Babelfish: Accepted and ignored.

Assembly modules and CLR routines

• MS SQL: Assembly modules and common language runtime (CLR) routines are not supported (CREATE ASSEMBLY).
• Babelfish: Currently not supported

Backup and restore

• MS SQL: Completely different behavior
• Babelfish: Use backup tools as for standard PostgreSQL

bcp, bulk copy, bulk insert

• MS SQL: Microsoft SQL Server offers a command line utility called bcp as a common way of importing and exporting data.
• Babelfish: This is currently not supported.

Blank column names when there is no column alias

• MS SQL: Allows column names to be blank when sending a result set to a client application. This can happen when the column is an expression involving more than a table column reference and no column alias is specified. sqlcmd and psql handle columns with blanks differently. MS SQL sqlcmd returns a blank column name.
• Babelfish: psql returns a generated column name.

Case-sensitivity for identifiers

• MS SQL: By default, SQL Server is case-insensitive for identifiers. So MyTable and MYTABLE reference the same object. However, SQL Server can be configured so that identifiers are case-sensitive. In this setting, MyTable and MYTABLE would identify two different objects.
• Babelfish: For PostgreSQL, only case-insensitive identifiers are supported. For example, the identifiers MyTable and MYTABLE refer to the same object. If your SQL Server database is configured to be case-sensitive for identifiers, Babelfish will identify MyTable and MYTABLE as the same object.

Column default

• MS SQL: Supports creating a column default with an explicitly specified constraint name. For example: ALTER TABLE ADD CONSTRAINT...DEFAULT...FOR.... SQL Server also supports dropping the default using that same name. ALTER TABLE...DROP CONSTRAINT....
• Babelfish: PostgreSQL handles column defaults in a different manner than SQL Server does, and there is no constraint name associated with a column default. Therefore, when creating a column default this way, the constraint name is ignored. To drop a column default use the following: ALTER TABLE...ALTER COLUMN...DROP DEFAULT....

Common language runtime (CLR)

• MS SQL: Supports Microsoft common language runtime (CLR) procedures, functions, and triggers.
• Babelfish: Currently not supported.

Constraints

• MS SQL: SQL Server supports enabling and disabling individual constraints such as the following. ALTER TABLE...[NO]CHECK CONSTRAINT....
• Babelfish: PostgreSQL does not support enabling and disabling individual constraints. The statement is ignored and a warning is raised.

Contained databases

• MS SQL: Contained databases have logins authenticated at the database level rather than at the server level.
• Babelfish: Currently not supported.

Data encryption

• MS SQL: Encrypting data in the database. An example is for an entire database with CREATE DATABASE ENCRYPTION KEY (TDE) or for finer-grained encryption with ENCRYPTBYKEY, CREATE SYMMETRIC KEY, etc., is not supported.
• Babelfish: Currently not supported.

IDENTITY columns support

• MS SQL: IDENTITY columns are supported for data types tinyint, smallint, int, bigint. numeric, and decimal. SQL Server supports precision up to 38 for data types numeric and decimal in IDENTITY columns.
• Babelfish: PostgreSQL supports precision up to 19 for data types numeric and decimal in IDENTITY columns.

Identifiers exceeding 63 characters

• MS SQL: Supports using up to 128 characters for identifiers.
• Babelfish: PostgreSQL supports a maximum of 63 characters for identifiers. Babelfish converts identifiers longer than 63 characters to a name that uses a hash of the original name. Use the original name with T-SQL but the converted name when accessing the database using PostgreSQL.

Language customization

• MS SQL: Supports using a non-default language for error messages and for day and month names.
• Babelfish: For PostgreSQL, only English error messages and date names are currently supported.

NEWSEQUENTIALID() function

• MS SQL: SQL Server supports the NEWSEQUENTIALID() function to generate a GUID value, which is guaranteed to have a higher value than a set of previously generated GUID values.
• Babelfish: When calling NEWSEQUENTIALID(), PostgreSQL cannot guarantee a higher GUID value so it will just generate a new GUID value, just like NEWID() does.

Procedure or function parameter limit

• MS SQL: SQL Server supports up to 2100 parameters for a SQL stored procedure or SQL function.
• Babelfish: PostgreSQL supports a maximum of 100 parameters for a procedure or function.

Remote object access

• MS SQL: SQL Server can access objects on a remote server including tables, views, and procedures.
• Babelfish: Currently not supported.

ROWGUIDCOL

• Babelfish: Currently ignored. Queries referencing $GUIDGOL will cause a syntax error.

Row-level security

• Babelfish: Row-level security with CREATE SECURITY POLICY and inline table-valued functions is currently not supported.

SEQUENCE objects support

• MS SQL: SEQUENCE objects are supported for data types tiny int, smallint, int, bigint . numeric, and decimal. SQL Server supports precision up to 38 for data types numeric and decimal in SEQUENCE objects.
• Babelfish: PostgreSQL supports precision up to 19 for data types numeric and decimal in SEQUENCE objects.

SQL keywords CLUSTERED and UNCLUSTERED constraints

• Babelfish: Currently not supported. PostgreSQL ignores CLUSTERED and UNCLUSTERED keywords for indexes or constraints. Babelfish does not support this storage structure (whereby the index key defines the physical storage order of the rows). A clustered index can impact query plans and performance aspects, though the precise effects are non-trivial.

SQL keyword FILLFACTOR

• MS SQL: Related to SQL Server-specific aspects of data storage.
• Babelfish: Currently ignored.

SQL keyword NONCLUSTERED for indexes and constraints

• MS SQL: SQL Server uses NONCLUSTERED keywords for indexes and constraints.
• Babelfish: Currently ignored. PostgreSQL supports heap structures.

SQL keyword clause NOT FOR REPLICATION

• MS SQL: Related to SQL Server-specific aspects of data storage.<
• Babelfish: Currently ignored

SQL keyword clause ON filegroup

• MS SQL: For SQL Server, the commands CREATE TABLE and CREATE INDEX support the object placement clause ON filegroup. This is related to SQL Server-specific aspects of data storage.
• Babelfish: Currently ignored because it is irrelevant for PostgreSQL.

SQL keyword SPARSE

• MS SQL: Related to SQL Server-specific aspects of data storage.
• Babelfish: Currently ignored.

SQL keyword TEXTIMAGE_ON filegroup

• MS SQL: or SQL Server, the commands CREATE TABLE and CREATE INDEX support the object placement clause TEXTIMAGE_ON filegroup
• Babelfish: PostgreSQL ignores the TEXTIMAGE_ON filegroup clause because it is irrelevant for PostgreSQL.

SQL statement USE database name

• Babelfish: Currently not supported

Sort order with DESC

• MS SQL: Allows the column sorting order in UNIQUE and PRIMARY KEY constraints to be specified as DESC. This helps to optimize queries that can benefit from this ordering.
• Babelfish: PostgreSQL does not support DESC for constraints. PostgreSQL constraints are created with the default ASC ordering. However, PostgreSQL does support DESC ordering for columns in explicitly created indexes.

Time precision

• MS SQL: SQL Server supports fractions of a second for precision of the datetime2 data type to 7 digits with 3 fractional digits. SQL Server rounds this to 3-millisecond precision. This means that when you store a particular time value, the milliseconds might be rounded and end up being stored differently than what you specified. For example, the command SELECT CAST('2016-12-26 23:59:59.999' as datetime) returns the value 2016-12-27 00:00:00.000.
• Babelfish: PostgreSQL supports 6-digit precision for fractional seconds. PostgreSQL does not perform SQL Server 3-millisecond rounding. No adverse effects are anticipated with this behavior.

Transaction isolation levels

• MS SQL: Choosing a transaction isolation level doesn’t affect the locks that are acquired to protect data modifications. A transaction always gets an exclusive lock on any data it modifies and holds that lock until the transaction completes, regardless of the isolation level set for that transaction. For read operations, transaction isolation levels primarily define the level of protection from the effects of modifications made by other transactions.
• Babelfish: We use standard PostgreSQL MVCC behavior. This implies that READ UNCOMMITED is automatically mapped to READ COMMITTED as PostgreSQL does not have a lower isolation level. REPEATABLE READ and SERIALIZABLE are currently not supported. MS SQL snapshot> is treated as the equivalent of .

Triggers for LOGON and DDL

• Babelfish: Currently this is not supported.

@@version

• Babelfish: The contents of @@version in Babelfish differs from the native content for SQL Server. Your code might not work correctly if it depends on the native formatting of @@version, such as to extract specific minor version numbers.

Unsupported stored procedures

MS SQL comes with a huge set of predefined stored procedures which make database handling easier, or in some cases make it possible at all. However, some of these procedures are not supported by Babelfish.

Missing catalog-related procedures

The following catalog procedures are currently not supported:

• sp_column_privileges
• sp_databases
• sp_fkeys
• sp_pkeys
• sp_server_info
• sp_special_columns
• sp_sproc_columns
• sp_statistics
• sp_stored_procedures
• sp_table_privileges

Missing cursor-related procedures

The following list contains an overview of missing cursor-related procedures:

• sp_describe_cursor_columns
• sp_describe_cursor_tables

Missing database engine-related procedures

This is a list of database engine-related functionality which is not yet available:

• sp_add_data_file_recover_suspect_db
• sp_add_log_file_recover_suspect_db
• sp_addextendedproc
• sp_addextendedproperty
• sp_addmessage
• sp_addtype
• sp_addumpdevice
• sp_altermessage
• sp_attach_db
• sp_attach_single_file_db
• sp_autostats
• sp_bindefault
• sp_bindrule
• sp_bindsession
• sp_certify_removable
• sp_clean_db_file_free_space
• sp_clean_db_free_space
• sp_configure
• sp_control_plan_guide
• sp_create_plan_guide
• sp_create_plan_guide_from_handle
• sp_create_removable
• sp_createstats
• sp_cycle_errorlog
• sp_db_increased_partitions
• sp_dbcmptlevel
• sp_dbmmonitoraddmonitoring
• sp_dbmmonitorchangealert
• sp_dbmmonitorchangemonitoring
• sp_dbmmonitordropalert
• sp_dbmmonitordropmonitoring
• sp_dbmmonitorhelpalert
• sp_dbmmonitorhelpmonitoring
• sp_dbmmonitorresults
• sp_delete_backuphistory
• sp_depends
• sp_detach_db
• sp_dropdevice
• sp_dropextendedproc
• sp_dropextendedproperty
• sp_dropmessage
• sp_droptype
• sp_getbindtoken
• sp_help
• sp_helpconstraint
• sp_helpdevice
• sp_helpextendedproc
• sp_helpfile
• sp_helpfilegroup
• sp_helpindex
• sp_helplanguage"
• sp_helpserver
• sp_helpsort
• sp_helpstats
• sp_helptext
• sp_helptrigger
• sp_indexoption
• sp_invalidate_textptr
• sp_lock
• sp_monitor
• sp_prepexecrpc
• sp_procoption
• sp_recompile
• sp_refreshview
• sp_rename
• sp_renamedb
• sp_resetstatus
• sp_sequence_get_range
• sp_serveroption
• sp_set_session_context
• sp_setnetname
• sp_settriggerorder
• sp_spaceused
• sp_tableoption
• sp_unbindefault
• sp_unbindrule
• sp_updateextendedproperty
• sp_updatestats
• sp_validname
• sp_who

Missing security-related procedures

• sp_add_trusted_assembly
• sp_addapprole
• sp_addlinkedserver
• sp_addlinkedsrvlogin
• sp_addlogin
• sp_addremotelogin
• sp_addrole
• sp_addrolemember
• sp_addserver
• sp_addsrvrolemember
• sp_adduser
• sp_approlepassword
• sp_audit_write
• sp_change_users_login
• sp_changedbowner
• sp_changeobjectowner
• sp_control_dbmasterkey_password
• sp_dbfixedrolepermission
• sp_defaultdb
• sp_defaultlanguage
• sp_denylogin
• sp_describe_parameter_encryption
• sp_dropalias
• sp_drop_trusted_assembly
• sp_dropapprole
• sp_droplinkedsrvlogin
• sp_droplogin
• sp_dropremotelogin
• sp_droprole
• sp_droprolemember
• sp_dropserver
• sp_dropsrvrolemember
• sp_dropuser
• sp_generate_database_ledger_digest
• sp_grantdbaccess
• sp_grantlogin
• sp_helpdbfixedrole
• sp_helplinkedsrvlogin
• sp_helplogins
• sp_helpntgroup
• sp_helpremotelogin
• sp_helprole
• sp_helprolemember
• sp_helprotect
• sp_helpsrvrole
• sp_helpsrvrolemember
• sp_helpuser
• sp_migrate_user_to_contained
• sp_MShasdbaccess
• sp_password
• sp_refresh_parameter_encryption
• sp_remoteoption
• sp_revokedbaccess
• sp_revokelogin
• sp_setapprole
• sp_srvrolepermission
• sp_testlinkedserver
• sp_unsetapprole
• sp_validatelogins
• sp_verify_database_ledger
• sp_verify_database_ledger_from_digest_storage
• sp_xp_cmdshell_proxy_account

Dealing with limitations

In this section, we’ve written a lot about limitations, unsupported features and such. However, how do you deal with these things in real life? If Babelfish runs a SQL statement that doesn’t return a result set that is identical to the result set when run against SQL Server, the SQL statement will fail. To better deal with statements that might fail, Babelfish allows you to define escape hatches. An escape hatch is a flag that specifies Babelfish behavior when it encounters an unsupported feature or syntax. Babelfish can either return an error or ignore the condition if the PostgreSQL result set is not a perfect match for the result set returned by SQL Server.

You can use the sp_babelfish_configure stored procedure to control the settings of each escape hatch. Use the script to specify if each escape hatch should be set to ignored or strict.

If set to strict, Babelfish will return an error that you must correct before continuing. Include the clusterkeyword to apply the changes to the current session as well as on a cluster level.

The following types of hatches exist. The list should give you an impression of what is possible:

• storage_options: Controls treatment of …
  • Column options (sparse files, file streams, ROWGUIDCOL)
  • Index options (PAD_INDEX, FILLFACTOR, SORT_IN_TEMPDB, IGNORE_DUP_KEY, STATISTICS_NORECOMPUTE, STATISTICS_INCREMENTAL, ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, OPTIMIZE_FOR_SEQUENTIAL_KEY, MAXDOP, DATA_COMPRESSION)
• storage_on_partition: Handle invalid partition streams
• database_misc_options: Handle database chaining, database property TRUSTWORTHY, databases with persistent log buffers
• language_non_english: Create databases with default languages other than english
• compatibility_level: Currently no options.

• CHARINDEX</index>: not supported on case-insensitive or accent-insensitive collations

• fulltext: Databases with default fulltext languages. CREATE FULLTEXT INDEX, ALTER FULL TEXT INDEX, DROP FULL TEXT INDEX
• schemabinding_function: Handles errors when no SCHEMABINDING option is given
• schemabinding_trigger: Throw an error when it is not given
• schemabinding_procedure: Throw an error when it is not given
• schemabinding_view: Throw an error when it is not given
• index_clustering: CLUSTERED columns are not supported. Control this behavior.
• index_columnstore: Column stores are not supported. Ignore or error out.
• for_replication: Handle unsupported ALTER PROCEDURE ... ENCRYPTION, ALTER PROCEDURE ... NATIVE_COMPILATION, ALTER PROCEDURE ... RECOMPILE.
• rowguidcol_column: Handle unsupported ROWGUIDCOL columns
• nocheck_add_constraint: Handle unsupported ALTER TABLE WITH [NO]CHECK ADD
• nocheck_existing_constraint: Handle ALTER TABLE [NO]CHECK
• constraint_name_for_default: Handle CONSTRAINT DEFAULT
• table_hints: PostgreSQL does not support table hints
• query_hints: PostgreSQL does not support query hints
• join_hints: PostgreSQL does not support join hints
• session_settings: Handle SET SHOWPLAN_TEXT ON, FORCEPLAN, OFFSETS, PARSEONLY, REMOTE_PROC_TRANSACTIONS, SHOWPLAN_ALL, SHOWPLAN_TEXT, SHOWPLAN_XML, STATISTICS, DATEFORMAT, DEADLOCK_PRIORITY, LOCK_TIMEOUT, CONTEXT_INFO, QUERY_GOVERNOR_COST_LIMIT, STATISTICS, XML modify method.