T-SQL differences in Babelfish

Following, you can find a table of T-SQL functionality as supported in the current release of Babelfish with some notes about differences in the behavior from that of SQL Server.

For more information about support in various versions, see Supported functionalities in Babelfish by version. For information about features that currently aren't supported, see Unsupported functionalities in Babelfish.

Babelfish is available with Aurora PostgreSQL-Compatible Edition. For more information about Babelfish releases, see the Release Notes for Aurora PostgreSQL.

Functionality or syntax	Description of behavior or difference
\ (line continuation character)	The line continuation character (a backslash prior to a newline) for character and hexadecimal strings isn't currently supported. For character strings, the backslash-newline is interpreted as characters in the string. For hexadecimal strings, backslash-newline results in a syntax error.
@@version	The format of the value returned by @@version is slightly different from the value returned by SQL Server. Your code might not work correctly if it depends on the formatting of @@version.
Aggregate functions	Aggregate functions are partially supported (AVG, COUNT, COUNT_BIG, GROUPING, MAX, MIN, STRING_AGG, and SUM are supported). For a list of unsupported aggregate functions, see Functions that aren't supported.
ALTER TABLE	Supports adding or dropping a single column or constraint only.
ALTER TABLE..ALTER COLUMN	NULL and NOT NULL can't currently be specified. To change the nullability of a column, use the postgreSQL statement ALTER TABLE..{SET|DROP} NOT NULL.
Blank column names with no column alias	The sqlcmd and psql utilities handle columns with blank names differently: SQL Server sqlcmd returns a blank column name. PostgreSQL psql returns a generated column name.
CHECKSUM function	Babelfish and SQL Server use different hashing algorithms for the CHECKSUM function. As a result, the hash values generated by CHECKSUM function in Babelfish might be different from those generated by CHECKSUM function in SQL Server.
Column default	When creating a column default, the constraint name is ignored. To drop a column default, use the following syntax: ALTER TABLE...ALTER COLUMN..DROP DEFAULT...
Constraint_name	In SQL Server, constraint names must be unique within the schema to which the table belongs. However, in Babelfish, this applies only to PRIMARY KEY and UNIQUE constraints. Other types of constraints are not subject to this restriction.
Constraints	PostgreSQL doesn't support turning on and turning off individual constraints. The statement is ignored and a warning is raised.
Constraints with IGNORE_DUP_KEY	Constraints are created without this property.
CREATE, ALTER, DROP SERVER ROLE	ALTER SERVER ROLE is supported only for sysadmin. All other syntax is unsupported. The T-SQL user in Babelfish has an experience that is similar to SQL Server for the concepts of a login (server principal), a database, and a database user (database principal).
CREATE, ALTER LOGIN clauses are supported with limited syntax	The CREATE LOGIN... PASSWORD clause, ...DEFAULT_DATABASE clause, and ...DEFAULT_LANGUAGE clause are supported. The ALTER LOGIN... PASSWORD clause is supported, but ALTER LOGIN... OLD_PASSWORD clause isn't supported. Only a login that is a sysadmin member can modify a password.
CREATE DATABASE case-sensitive collation	Case-sensitive collations aren't supported with the CREATE DATABASE statement.
CREATE DATABASE keywords and clauses	Options except COLLATE and CONTAINMENT=NONE aren't supported. The COLLATE clause is accepted and is always set to the value of babelfishpg_tsql.server_collation_name.
CREATE SCHEMA... supporting clauses	You can use the CREATE SCHEMA command to create an empty schema. Use additional commands to create schema objects.
Database ID values are different on Babelfish	The master and tempdb databases won't be database IDs 1 and 2.
FORMAT date type function is supported with the following limitations	Single character meridian isn't supported. "yyy" format in SQL server returns 4 digits for year above 1000, but only 3 digits for others. "g" and "R" formats aren't supported "vi-VN" locale translation is slightly different.
Identifiers exceeding 63 characters	PostgreSQL supports a maximum of 63 characters for identifiers. Babelfish converts identifiers longer than 63 characters to a name that includes a hash of the original name. For example, a table created as "AB(ABC1234567890123456789012345678901234567890123456789012345678901234567890" might be converted to "ABC123456789012345678901234567890123456789012345678901234567890".
IDENTITY columns support	IDENTITY columns are supported for data types tinyint, smallint, int, bigint. numeric, and decimal. SQL Server supports precision to 38 places for data types numeric and decimal in IDENTITY columns. PostgreSQL supports precision to 19 places for data types numeric and decimal in IDENTITY columns.
Indexes with IGNORE_DUP_KEY	Syntax that creates an index that includes IGNORE_DUP_KEY creates an index as if this property is omitted.
Indexes with more than 32 columns	An index can't include more than 32 columns. Included index columns count toward the maximum in PostgreSQL but not in SQL Server.
Indexes (clustered)	Clustered indexes are created as if NONCLUSTERED was specified.
Index clauses	The following clauses are ignored: FILLFACTOR, ALLOW_PAGE_LOCKS, ALLOW_ROW_LOCKS, PAD_INDEX, STATISTICS_NORECOMPUTE, OPTIMIZE_FOR_SEQUENTIAL_KEY, SORT_IN_TEMPDB, DROP_EXISTING, ONLINE, COMPRESSION_DELAY, MAXDOP, and DATA_COMPRESSION
JSON support	Order of the name-value pairs isn't guaranteed. But the array type remains unaffected.
LOGIN objects	All options for LOGIN objects are not supported except for PASSWORD, DEFAULT_DATABASE, DEFAULT_LANGUAGE, ENABLE, DISABLE.
NEWSEQUENTIALID function	Implemented as NEWID; sequential behavior isn't guaranteed. When calling NEWSEQUENTIALID, PostgreSQL generates a new GUID value.
OUTPUT clause is supported with the following limitations	OUTPUT and OUTPUT INTO aren't supported in the same DML query. References to non-target table of UPDATE or DELETE operations in an OUTPUT clause aren't supported. OUTPUT... DELETED *, INSERTED * aren't supported in the same query.
Procedure or function parameter limit	Babelfish supports a maximum of 100 parameters for a procedure or function.
ROWGUIDCOL	This clause is currently ignored. Queries referencing $GUIDGOL cause a syntax error.
SEQUENCE object support	SEQUENCE objects are supported for the data types tinyint, smallint, int, bigint, numeric, and decimal. Aurora PostgreSQL supports precision to 19 places for data types numeric and decimal in a SEQUENCE.
Server-level roles	The sysadmin server-level role is supported. Other server-level roles (other than sysadmin) aren't supported.
Database-level roles other than db_owner	The db_owner database-level roles and user-defined database-level roles are supported. Other database-level roles (other than db_owner) aren't supported.
SQL keyword SPARSE	The keyword SPARSE is accepted and ignored.
SQL keyword clause ON filegroup	This clause is currently ignored.
SQL keywords CLUSTERED and NONCLUSTERED for indexes and constraints	Babelfish accepts and ignores the CLUSTERED and NONCLUSTERED keywords.
sysdatabases.cmptlevel	sysdatabases.cmptlevel is always set to 120.
tempdb isn't reinitialized at restart	Permanent objects (like tables and procedures) created in tempdb aren't removed when the database is restarted.
TEXTIMAGE_ON filegroup	Babelfish ignores the TEXTIMAGE_ON filegroup clause.
Time precision	Babelfish supports 6-digit precision for fractional seconds. No adverse effects are anticipated with this behavior.
Transaction isolation levels	READUNCOMMITTED is treated the same as READCOMMITTED.
Virtual computed columns (non-persistent)	Virtual computed columns are created as persistent.
Without SCHEMABINDING clause	This clause isn't supported in functions, procedures, triggers, or views. The object is created, but as if WITH SCHEMABINDING was specified.