I have been playing around some more with SQL Azure in order to get ready for an upcoming presentation, so I thought I would share some of the T-SQL commands in the script below. I have a partial copy of the sample AdventureWorksLT2008R2 database installed on my SQL Azure logical “server” in the Southern US data center before this demo starts. You need at least the November CTP of SQL Server 2008 R2, in order to talk to SQL Azure with SSMS.

I have mainly just trying different commands to see what works and what does not work in SQL Azure, so I hope you find this interesting and useful.

-- Trying out SQL Azure
-- Glenn Berry
-- March 2010
-- http://glennberrysqlperformance.spaces.live.com/
-- Twitter: GlennAlanBerry


-- Get version information
SELECT @@VERSION AS [SQL Version Info];


-- Connect to a user database first
-- Remember, there is no USE databasename allowed
-- This does not work in SQL Azure
USE AdventureWorksLT2008R2;
GO


-- ***  Connect to AdventureWorksLT2008R2 database ***


-- Create a table and populate it

--Drop table if it exists
IF OBJECT_ID('dbo.UserAccount', 'U') IS NOT NULL
  DROP TABLE dbo.UserAccount;
GO

-- Create a table
-- Notice no file group or other options allowed in CREATE TABLE for SQL Azure
CREATE TABLE [dbo].[UserAccount](
    [UserID] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [nvarchar](256) NOT NULL,
    [Password] [nvarchar](50) NOT NULL,
    [CreateDate] [datetime] NOT NULL,
    [ExpireDate] [datetime] NULL,
    [FName] [nvarchar](50) NOT NULL,
    [LName] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_UserAccount] PRIMARY KEY CLUSTERED 
([UserID] ASC));


-- Drop index if it exists
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[UserAccount]') 
            AND name = N'IX_UserAccount_UserName')
DROP INDEX [IX_UserAccount_UserName] ON [dbo].[UserAccount];
GO

-- Create NC Index
-- Notice no options allowed in CREATE INDEX for SQL Azure
CREATE NONCLUSTERED INDEX [IX_UserAccount_UserName] ON [dbo].[UserAccount] 
(
    [UserName] ASC
);

-- Insert a few rows
INSERT INTO dbo.UserAccount(UserName, [Password], CreateDate, [ExpireDate], FName, LName)
VALUES('GlennBerry', 'testpassword', GETDATE(), '12/31/2010', 'Glenn', 'Berry'),
      ('JessicaAlba', 'testpassword', GETDATE(), '12/31/2010', 'Jessica', 'Alba'),
      ('MirandaKerr', 'testpassword', GETDATE(), '12/31/2010', 'Miranda', 'Kerr');


-- See what we have in the table (notice GETDATE() actually returned UTC Date)
SELECT UserID, UserName, [Password], CreateDate, [ExpireDate], FName, LName
FROM dbo.UserAccount;

-- Get the UTC Date
SELECT GETUTCDATE() AS [UTC Date];


-- This works in SQL Azure
EXEC sp_HelpIndex [dbo.UserAccount];
EXEC sp_HelpIndex [SalesLT.Customer];
EXEC sp_HelpIndex [SalesLT.CustomerAddress];

-- This works in SQL Azure
EXEC sp_Help [SalesLT.Customer]


-- Stop the rowcount messsages
SET NOCOUNT ON;

-- Return IO statistics
SET STATISTICS IO ON;

-- Turn on graphical execution plan

-- Run a simple SELECT query
SELECT UserID, UserName, [Password], CreateDate, 
[ExpireDate], FName, LName
FROM dbo.UserAccount;

-- Run a simple SELECT query
SELECT CustomerID, NameStyle, Title, FirstName, MiddleName, 
LastName, Suffix, CompanyName, SalesPerson, EmailAddress, 
Phone, PasswordHash
FROM SALESLT.Customer;


-- Run a simple SELECT query with a WHERE clause
-- Notice SQL Server 2008 syntax works for DECLARE
DECLARE @CustomerID int = 1; 

SELECT CustomerID, NameStyle, Title, FirstName, MiddleName, 
LastName, Suffix, CompanyName, SalesPerson, EmailAddress, 
Phone, PasswordHash
FROM SALESLT.Customer
WHERE CustomerID = @CustomerID;


-- Create a stored procedure
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetUserInfoByID]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetUserInfoByID]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetUserInfoByID]
(
    @UserID int
)
AS
    BEGIN
        
        SET NOCOUNT ON;

        SELECT UserID, UserName, [Password], CreateDate, [ExpireDate], FName, LName
        FROM dbo.UserAccount
        WHERE UserID = @UserID;
        
        RETURN;
        
    END
GO

-- Call the stored procedure
EXEC dbo.GetUserInfoByID 1;
EXEC dbo.GetUserInfoByID 2;





-- Must connect to master database first
-- Remember, there is no USE databasename allowed
-- This does not work in SQL Azure
USE [Master];
GO

-- *** Connect to master database ***


-- Get firewall rules
SELECT id, name, start_ip_address, end_ip_address, 
create_date, modify_date 
FROM sys.firewall_rules;


-- Switch to Business Edition ($99.99/month)
ALTER DATABASE AdventureWorksLT2008R2 
MODIFY (MAXSIZE = 10 GB);

-- Refresh SQL Azure Portal web page to see change

-- Switch to Web Edition ($9.99/month)
ALTER DATABASE AdventureWorksLT2008R2 
MODIFY (MAXSIZE = 1 GB);


-- Create an "Admin" login and user in the SQL Azure instance

-- List all logins on "instance" (must be connected to master)
SELECT *
FROM sys.sql_logins;

-- Cleanup if necessary
DROP LOGIN TestLogin;
GO
DROP USER TestLoginUser;
GO

-- Create a logon with a "strong" password that
-- can create databases and other logins
CREATE LOGIN TestLogin WITH password='1994Acura#';
GO
-- Create a database user that is linked to login
CREATE USER TestLoginUser FROM LOGIN TestLogin;
GO
EXEC sp_addrolemember 'loginmanager', 'TestLoginUser';
EXEC sp_addrolemember 'dbmanager', 'TestLoginUser';  -- like dbcreator


-- List all logins on "instance" 
SELECT *
FROM sys.sql_logins;

-- List all databases (must be connected to master)
SELECT name, database_id, create_date, [compatibility_level]
FROM sys.databases;

-- Drop database if necessary
DROP DATABASE TestDatabase;

-- Create new database on SQL Azure "instance"
CREATE DATABASE TestDatabase;


-- List all databases (must be connected to master)
SELECT name, database_id, create_date, [compatibility_level]
FROM sys.databases;

-- Connect to a user database first
-- Remember, there is no USE databasename allowed
-- This does not work in SQL Azure
USE TestDatabase;
GO

-- ***  Connect to TestDatabase ***

-- Create a table
CREATE TABLE [dbo].[TestTable](
    [TestID] [int] IDENTITY(1,1) NOT NULL,
    [TestName] [nvarchar](50) NOT NULL,    
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
([TestID] ASC));

-- Insert a few rows into table
INSERT INTO dbo.TestTable(TestName)
VALUES('Test1'),
      ('Test2'),
      ('Test3');
      
-- Get row counts
SELECT OBJECT_NAME(object_id) AS [ObjectName], object_id, index_id, row_count
FROM sys.dm_db_partition_stats;


-- This does not work in SQL Azure
DBCC FREEPROCCACHE;

-- This does not work in SQL Azure
DBCC FREEPROCINDB(5);

-- This does not work in SQL Azure
DBCC DROPCLEANBUFFERS;


-- ***  Connect to AdventureWorksLT2008R2 database ***

-- Run a stored procedure
EXEC dbo.GetCustomerInfoByFirstName N'James';
EXEC dbo.GetUserInfoByID 2;


-- Run some DMV queries against the database

-- Get row counts
SELECT OBJECT_NAME(object_id) AS [ObjectName], object_id, index_id, row_count
FROM sys.dm_db_partition_stats;


-- Monitor connections
SELECT s.session_id, s.login_name, e.connection_id,
      s.last_request_end_time, s.cpu_time
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_connections AS e
ON s.session_id = e.session_id;

-- Find top Avg CPU time queries
SELECT TOP (5) MIN(query_stats.statement_text) AS [Statement Text], 
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS [Avg CPU Time],
query_stats.query_hash AS [Query Hash]

FROM (SELECT QS.*, SUBSTRING(ST.[text], (QS.statement_start_offset/2) + 1,
    ((CASE statement_end_offset 
        WHEN -1 THEN DATALENGTH(st.[text])
        ELSE QS.statement_end_offset END 
            - QS.statement_start_offset)/2) + 1) AS statement_text
     FROM sys.dm_exec_query_stats AS QS
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) AS ST) AS query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;

-- Top Cached Plans By total worker time (CPU)
SELECT q.[text], hcpu.total_worker_time, 
       hcpu.execution_count, hcpu.plan_handle
FROM 
    (SELECT TOP (50) qs.* 
     FROM sys.dm_exec_query_stats AS qs 
     ORDER BY qs.total_worker_time DESC) AS hcpu 
     CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q 
ORDER BY hcpu.total_worker_time DESC;


-- Top Cached Plans By total logical reads (Memory)
SELECT q.[text], hcpu.total_logical_reads, 
       hcpu.execution_count, hcpu.plan_handle
FROM 
    (SELECT TOP (50) qs.* 
     FROM sys.dm_exec_query_stats AS qs 
     ORDER BY qs.total_worker_time DESC) AS hcpu 
     CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q 
ORDER BY hcpu.total_logical_reads DESC;


-- Top Cached Plans By total elapsed time
SELECT q.[text], hcpu.total_elapsed_time, 
       hcpu.execution_count, hcpu.plan_handle
FROM 
    (SELECT TOP (50) qs.* 
     FROM sys.dm_exec_query_stats AS qs 
     ORDER BY qs.total_worker_time DESC) AS hcpu 
     CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q 
ORDER BY hcpu.total_elapsed_time DESC;



-- DMVs that are available in SQL Azure Service Update 1
SELECT * FROM sys.dm_exec_connections; 

SELECT * FROM sys.dm_exec_requests; 

SELECT * FROM sys.dm_exec_sessions; 

SELECT * FROM sys.dm_tran_database_transactions;  

SELECT * FROM sys.dm_tran_active_transactions; 


-- Drop the Stored procedure
DROP PROCEDURE dbo.GetUserInfoByID;

-- Drop the table
DROP TABLE dbo.UserAccount;

-- Drop the database
DROP DATABASE TestDatabase;

-- Drop the login
DROP LOGIN TestLogin;