SQL Data Services (SDS) Part V

We discussed some T-SQL statements in my previous post SQL Data Services (SDS) Part IV. In this post, I will show some more T-SQL statements with SQL Azure and discuss the compulsion of using clustered index, and how try … catch and Transaction related statements differ with the use of SET XACT_ABORT clause

Note that SQL Azure does not support heap tables. You need to create clustered index. If a table is created without a clustered index, you must create one before inserting data. If you have no clustered index for a table and you try entering data in that table, you get following error in SQL Azure

Following is the example with Try Catch and SET XACT_ABORT_OFF or ON

IF OBJECT_ID(N't2', N'U') IS NOT NULL
    DROP TABLE t2;
GO
IF OBJECT_ID(N't1', N'U') IS NOT NULL
    DROP TABLE t1;
GO
CREATE TABLE t1
    (a INT NOT NULL PRIMARY KEY);
CREATE TABLE t2
    (id int primary key identity,a INT NOT NULL REFERENCES t1(a));
GO
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (3);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (6);
GO
SET XACT_ABORT OFF;
GO
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2); -- Foreign key error.
INSERT INTO t2 VALUES (3);
COMMIT TRANSACTION;
GO
SELECT * FROM t2

Even if we use Transaction, we see that the statement which gives error is not executed. The remaining 2 inserts for which there was no error, are successful

as we see with the select statement

When we use the following statement, none of the records are inserted as required:

SET XACT_ABORT ON;
GO
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (4);
INSERT INTO t2 VALUES (5); -- Foreign key error.
INSERT INTO t2 VALUES (6);
COMMIT TRANSACTION;
GO
SELECT * FROM t1
SELECT *
    FROM t2;
GO

And SELECT statement for table t2 shows same result as what was before the insert statements

In this post, we discussed the compulsion of using clustered index, how try … catch and Transaction related statements differ with the use of SET XACT_ABORT clause.

In next article we will discuss how to use the XML data type with SQL Azure