Testing T‑SQL Compatibility in KingbaseES: Key Findings and Tips

Introduction

Hello, I am Wukong, honored to serve as a product experience officer for Kingbase database. This article introduces the basic testing of the KingbaseES SQL Server compatible version's T‑SQL support.

1. Test Overview

KingbaseES has achieved high compatibility with SQL Server in special data types, objects, statements, T‑SQL syntax, and stored procedure calls. The following common T‑SQL constructs are tested:

Batch statement GO

PRINT statement

RAISERROR statement

THROW statement

Statement terminators (semicolon and comma)

2. Download and Install KingbaseES SQL Server Compatible Edition

Download Link

https://www.kingbase.com.cn/download.html

The version adds support for several SQL Server system views and built‑in functions, ICU library optimizations, advanced query features (FOR XML, PIVOT, GROUP BY with different data types, DISTINCT with ORDER BY aliases), DML‑triggered statistics updates, temporary table optimizations, and .Net driver enhancements for date functions.

Download Authorization File

The KingbaseES SQL Server compatible edition authorization file is valid for 90 days from the first database start.

3. Compatibility Tests

3.1 Batch Statements (GO)

Meaning

Batch statements combine multiple T‑SQL statements into a single execution unit. SQL Server separates statements with a semicolon (;) or the GO keyword.

Syntax

Using semicolon

SELECT * FROM Table1;
SELECT * FROM Table2;

Using GO

SELECT * FROM Table1;
GO
SELECT * FROM Table2;
GO

Notes

Semicolon : Required as a statement terminator in many contexts (e.g., TRY…CATCH, BEGIN…END).

GO keyword : Not part of T‑SQL syntax; it is a batch separator used by SSMS and sqlcmd and cannot be used inside stored procedures or functions.

Performance : Batching reduces network round‑trips, improving performance.

Practical Example

Example 1: Using semicolon

SELECT * FROM Table1;
SELECT * FROM Table2;

Example 2: Using GO

SELECT * FROM Table1;
GO
SELECT * FROM Table2;
GO

3.2 PRINT Statement

Meaning

The PRINT statement outputs text to the SQL Server message window, useful for debugging and logging.

Syntax

PRINT 'message';

Notes

Output limit : Maximum 8000 characters.

Performance : PRINT does not affect query execution, but excessive use may impact performance.

Debugging tools : In production, prefer using ERRORLOG or custom log tables.

Practical Example

Example 1: Print constant string

PRINT 'Hello, World!';

Example 2: Print variable value

DECLARE @Message NVARCHAR(100) = 'This is a test message.';
PRINT @Message;

3.3 RAISERROR Statement

Meaning

RAISERROR generates an error message and transfers control to a CATCH block (if present). It is commonly used for custom error handling.

Syntax

RAISERROR ( { msg_id | msg_str } , severity , state [ , argument [ ,...n ] ] );

Notes

Severity : Levels 11‑19 trigger CATCH blocks.

Performance : RAISERROR aborts the current execution flow; use sparingly.

Replacement : For SQL Server 2012+ use THROW instead of RAISERROR.

Practical Example

BEGIN TRY
    RAISERROR ('This is a custom error message.', 16, 1);
END TRY
BEGIN CATCH
    PRINT 'Error caught: ' + ERROR_MESSAGE();
END CATCH;

3.4 THROW Statement

Meaning

THROW throws an error and transfers control to a CATCH block, serving as a simpler alternative to RAISERROR.

Syntax

THROW [error_number , message , state];

Notes

Compatibility : Available only in SQL Server 2012 and later.

Performance : Like RAISERROR, it aborts the current execution flow.

Use case : Prefer THROW for custom errors in newer SQL Server versions.

Practical Example

BEGIN TRY
    THROW 50001, 'This is a custom error message.', 1;
END TRY
BEGIN CATCH
    PRINT 'Error caught: ' + ERROR_MESSAGE();
END CATCH;

3.5 Semicolon Requirement

Meaning

In SQL Server, the semicolon (;) marks the end of a statement.

Test

BEGIN TRY
    SELECT * FROM Table1
    SELECT * FROM Table2
END TRY
BEGIN CATCH
    PRINT 'Error occurred';
END CATCH;

3.6 Trailing Comma in CREATE TABLE

Meaning

SQL Server allows a trailing comma after the last column definition, which can simplify code maintenance.

Syntax

With trailing comma

CREATE TABLE ExampleTable (
    ID INT PRIMARY KEY,
    Data NVARCHAR(100),
    RowVer ROWVERSION,
);

Without trailing comma

CREATE TABLE ExampleTable (
    ID INT PRIMARY KEY,
    Data NVARCHAR(100),
    RowVer ROWVERSION
);

Notes

Trailing commas are accepted by SQL Server but not by some other DBMS such as MySQL.

Consistent style across a team improves maintainability.

Performance is unaffected.

Practical Example with Trailing Comma

CREATE TABLE ExampleTable (
    ID INT PRIMARY KEY,
    Data NVARCHAR(100),
    RowVer ROWVERSION,
);

Practical Example without Trailing Comma

CREATE TABLE ExampleTable (
    ID INT PRIMARY KEY,
    Data NVARCHAR(100),
    RowVer ROWVERSION
);

Summary of Tool Statements

Batch statements : Combine multiple T‑SQL statements to improve execution efficiency. PRINT statement : Useful for debugging and outputting information, but not recommended for production. RAISERROR statement : Custom error handling; for SQL Server 2012+ use THROW instead. THROW statement : Simpler syntax, replaces RAISERROR in newer versions.

These tool statements are valuable for SQL Server development and debugging; proper use enhances code readability and maintainability.

5. Windows Installation Error Summary

1. Initialization Failure

Solution

Disable 360 security software before proceeding.

2. Service Cannot Start

Open services.msc, locate the executable path.

cd D:\Program Files\Kingbase\ES\V9\KESRealPro\V009R004C012\Server\bin
./sys_ctl.exe runservice -N "kingbase9_R1_instance" -D "D:\Program Files\Kingbase\ES\V9\data" -w

sys_ctl: could not start service "kingbase9_R1_instance": error code 1063

./sys_ctl.exe -D "kingbase9_R1_instance" -D "D:\Program Files\Kingbase\ES\V9\data" start

waiting for server to start.....
HKT [18240] LOG:  正在启动 KingbaseES V009R004C012
HKT [18240] LOG:  正在监听IPv6地址 "::", 端口 54321
HKT [18240] LOG:  无法绑定IPv4地址 "0.0.0.0": Only one usage of each socket address (protocol/network address/port) is normally permitted.

HKT [18240] HINT:  端口54321上是否已经运行了另一个kingbase?如果没有，请等待几秒钟后重试。
HKT [18240] FATAL:  无法为 "*" 创建监听套接字
HKT [18240] LOG:  database system is shut down
stopped waiting
sys_ctl: could not start server
Examine the log output.

Solution

Option 1: Check if the port is occupied and kill the process.

netstat -ano | grep "54321"
kill <process id>

Option 2: Change the startup port.

D:\Program Files\Kingbase\ES\V9\data
port=54325

After restarting, the service starts successfully.

Verify the process and adjust the connection port to 54325.

3. Database Connection Error (Garbage Characters)

Solution

Select UTF‑8 encoding during installation.