Common SQL operations and compatibility for Babelfish - ApsaraDB RDS

This topic covers common T-SQL operations and known compatibility limitations for ApsaraDB RDS for PostgreSQL instances with Babelfish enabled. Connect to the instance over the Tabular Data Stream (TDS) port before running the examples.

Prerequisites

Before you begin, make sure that you have:

An ApsaraDB RDS for PostgreSQL instance with Babelfish enabled
A client that connects over the TDS port (for example, sqlcmd or SQL Server Management Studio)

Common operations

System queries

Operation	SQL
Query the database version	`SELECT @@version;`
List all databases	`SELECT * FROM sys.databases;`

Database operations

Create a database

CREATE DATABASE testdb;

Note In Single-DB migration mode, only one database is allowed. If a database already exists, the CREATE DATABASE statement fails.

Query a database

SELECT * FROM sys.databases WHERE name = 'testdb';

Switch to a database

USE testdb
GO
SELECT db_name();

Drop a database

DROP DATABASE testdb;

Schema operations

Create a schema

CREATE SCHEMA sch_demo;

View a schema

SELECT * FROM sys.schemas AS sch WHERE sch.name = 'sch_demo';

Create a table in a schema

CREATE TABLE sch_demo.tb_demo(id int);

SELECT
    sch.name AS schema_name,
    tb.name AS table_name
FROM sys.tables AS tb
    INNER JOIN sys.schemas AS sch
    ON tb.schema_id = sch.schema_id
WHERE tb.name = 'tb_demo';

Drop a schema

Note Drop all tables in the schema before dropping the schema itself.

DROP TABLE sch_demo.tb_demo;
GO

DROP SCHEMA sch_demo;
GO

Table operations

Create a table

USE testdb
GO

CREATE TABLE dbo.tb_test(
    id int not null IDENTITY(1,1) PRIMARY KEY,
    name varchar(50))
GO

Query a table

SELECT sche.name AS schema_name, tb.name AS table_name
FROM sys.tables AS tb
    INNER JOIN sys.schemas AS sche
    ON tb.schema_id = sche.schema_id
WHERE tb.name = 'tb_test';
GO

Add a column

ALTER TABLE dbo.tb_test ADD col_added bigint null;
GO

Modify a column

ALTER TABLE dbo.tb_test ALTER column col_added varchar(50);
GO

Drop a column

ALTER TABLE dbo.tb_test DROP column col_added;
GO

Create an index

CREATE INDEX ix_tb_test_name ON tb_test(name);
GO

Drop an index

DROP INDEX ix_tb_test_name ON tb_test;
GO

Data operations

INSERT

INSERT INTO dbo.tb_test
SELECT 'A' UNION ALL
SELECT 'B';
GO

SELECT

SELECT * FROM dbo.tb_test;

UPDATE

UPDATE TOP(1) dbo.tb_test
SET name = 'A_updated';
GO

DELETE

DELETE TOP(1) FROM dbo.tb_test;
GO

SELECT * FROM dbo.tb_test;

Stored procedure operations

Create a stored procedure

USE testdb
GO

CREATE PROC dbo.UP_getDemoData(
    @id int
)
AS
BEGIN
    SET NOCOUNT ON
    SELECT *
    FROM dbo.tb_test
    WHERE id = @id
END;
GO

View a stored procedure

SELECT *
FROM sys.procedures
WHERE name = 'up_getdemodata';

Execute a stored procedure

EXEC dbo.UP_getDemoData @id = 7;
GO

Drop a stored procedure

USE testdb
GO
DROP PROC dbo.UP_getDemoData
GO

Compatibility limitations

Note This section covers common incompatibility scenarios. For a complete list, see the Babelfish for PostgreSQL documentation.

The following T-SQL statements are not supported when Babelfish is enabled.

Unsupported operation	Impact	Workaround
`EXEC sp_help 'dbo.tb_test'` — view table schema via sp_help	Error	None available via TDS port
`ALTER TABLE ... ALTER COLUMN ... NULL` — set nullable when modifying a column	Error	Omit the `NULL` keyword; see details below
`ALTER INDEX ... REBUILD` — rebuild an index	Error	Drop and recreate the index; see details below
`ALTER PROC` — modify a stored procedure	Error	Drop and recreate the stored procedure; see details below
`SET showplan_xml ON` — query an execution plan	Error	None available via TDS port

View table schema via sp_help

EXEC sp_help is not supported:

-- Not supported
EXEC sp_help 'dbo.tb_test'

Set a column to nullable when modifying it

Setting NULL explicitly when altering a column is not supported. Omit the NULL keyword instead:

-- Not supported
ALTER TABLE dbo.tb_test ALTER column col_added varchar(50) null;
GO

-- Workaround: omit the NULL keyword
ALTER TABLE dbo.tb_test ALTER column col_added varchar(50);
GO

Rebuild an index

ALTER INDEX ... REBUILD is not supported. Drop and recreate the index instead:

-- Not supported
ALTER INDEX ix_tb_test_name ON tb_test REBUILD;
GO

-- Workaround: drop then recreate
DROP INDEX ix_tb_test_name ON tb_test;
GO
CREATE INDEX ix_tb_test_name ON tb_test(name);
GO

Modify a stored procedure

ALTER PROC is not supported. Drop and recreate the stored procedure instead:

-- Not supported
USE testdb
GO

ALTER PROC dbo.UP_getDemoData(
    @id int
)
AS
BEGIN
    SET NOCOUNT ON
    SELECT *
    FROM dbo.tb_test
    WHERE id >= @id
END;
GO

-- Workaround: drop then recreate
USE testdb
GO
DROP PROC dbo.UP_getDemoData
GO

CREATE PROC dbo.UP_getDemoData(
    @id int
)
AS
BEGIN
    SET NOCOUNT ON
    SELECT *
    FROM dbo.tb_test
    WHERE id >= @id
END;
GO

Query an execution plan

SET showplan_xml ON is not supported:

-- Not supported
SET showplan_xml ON
SELECT * from tb_test;