SQL Data Services (SDS) Part IV

In the previous SDS posts, we discussed how to create database, create table in the cloud by writing a query and also programmatically. When working with SQL Azure T-SQL provided is a subset of T-SQL for SQL Server.

Using T-SQL with SQL Azure

1. When referring to an object in SQL Azure following are the conventions. Note that server name is not allowed in the reference

schema name.object name

i. Create a new query in SQL Server Management Studio (SSMS)
Enter query

CREATE SCHEMA MySchema

ii. Create table as follows

CREATE TABLE MySchema.MyTable
(Id int identity primary key,
UserName nvarchar(15))

iii. Insert rows as follows

INSERT INTO MySchema.MyTable
VALUES (Name1),('Name2')

Note you need to specify the schema name with the object. Currently using database name along with schema and object name is not supported

2. All normal data types are supported in SQL Azure. SQL Azure does not support User Defined Data type. It supports XML data type.

3. SQL Azure database does not support any of the SQL system table

a. There is no provision and requirement of backup and restore

b. There is no log shipping or replication requirement

4. A lot of T-SQL statements like ALTER SCHEMA, ALTER ROLE, DROP LOGIN, DROP USER, CAST, CONVERT, and SET @variable are supported. We will discuss some of them

a.    --create a stored proc
CREATE PROC DispNames
AS
SELECT * FROM Names

-- execute the stored proc
EXEC DispNames

--alter the existing stored proc
ALTER PROC DispNames
AS
SELECT UserId, [User Name] FROM Names

--execute changed stored proc
EXEC DispNames
--delete the proc
DROP PROC DispNames

Notice the first variable data is truncated due to wrong length

c. Merge statement works with SQL Azure in the same manner as SQL Server as follows

CREATE TABLE t1
(Id int NOT NULL primary key, FullName varchar(100))

CREATE TABLE t2
(Id int NOT NULL primary key, FullName varchar(100))

INSERT INTO t1 VALUES
(1,'Smita Sane'),
(5,'Sarita Bhave'),
(6,'John')

INSERT INTO t2 VALUES
(1,'Smita Sohoni'),
(5,'Sarita sonu Bhave'),
(7,'Danny')

SELECT * FROM T1

SELECT * FROM T2

The result for this is as follows

MERGE t1
USING
(SELECT * from t2) target
ON t1.Id=target.Id
WHEN MATCHED
THEN UPDATE SET t1.FullName=target.FullName
WHEN  NOT MATCHED by target
THEN INSERT VALUES (target.Id,target.FullName);

SELECT * FROM t1
SELECT * FROM t2

After merge the result is as follows

So we see that in table T1 records are updated as well as inserted depending upon the condition

DROP TABLE T1
DROP TABLE T2

In next article we will discuss some more T-SQL statements with SQL Azure

SQL Data Services (SDS) Part IV

No comments: