Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

More SQL Azure Samples

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;






Comments

Posted by Dukagjin Maloku on 15 March 2010

Not bad, nice stuff, thanks for sharing!

Posted by Anonymous on 14 May 2010

Pingback from  SQL Azure and T-SQL «  Dukagjin Maloku – Dugi'SQL

Posted by Anonymous on 15 May 2010

SQL Server Azure database supports the T-SQL, but no at all! For the SQL Server Azure and T-SQL are available

Posted by Anonymous on 20 May 2010

Pingback from  Ford F59 Part Bilstein Shock Absorbers Improve Fuel Economy, Installation Ford F59 Stripped Chassis

Posted by Anonymous on 20 May 2010

Pingback from  Samsung F500 Video, 1961 Ford F500

Posted by Anonymous on 20 May 2010

Pingback from  1996 Dodge Avenger Headlight Assembly Oe, Bajaj Avenger Headlight

Posted by Anonymous on 21 May 2010

Pingback from  Sell Lexus Sc300 Turbo, Used Sc300 Lexus Es350

Posted by Anonymous on 21 May 2010

Pingback from  Green Pontiac Aztek, 2001 Pontiac Aztek Used Parts Bumper Badger

Posted by Anonymous on 21 May 2010

Pingback from  Starfire Coupons Direct Vent, Starfire Chord Valley Of The Damned

Posted by Anonymous on 21 May 2010

Pingback from  Swift Bulbapedia Community Driven, Maruti Swift Desire Diesel Review

Posted by Anonymous on 21 May 2010

Pingback from  Lexus Es350 Door Home Depot, Gs350 Headlight Face

Posted by Anonymous on 22 May 2010

Pingback from  Guerilla Girls Used Plymouth Caravelle Canadian, Oem Plymouth Caravelle

Posted by Anonymous on 22 May 2010

Pingback from  Download Gmc G1000 Series Xm Satellite Radio, G1000 Series Buy Spinning Rods

Posted by Anonymous on 22 May 2010

Pingback from  97 Mitsubishi Diamante Pricing, Diamante Pt Stock

Posted by Anonymous on 22 May 2010

Pingback from  2000 Saturn Sw2 Oil Capacity, Sw2 Hood Replacement - 143.myipgirl.com

Posted by Anonymous on 23 May 2010

Pingback from  Headlight 2003 525i Bmw 540i, 1990 Bmw 525i Parts - 481.akemet.com

Posted by Anonymous on 23 May 2010

Pingback from  Download Probe Hard Disk Space, 1995 Ford Probe Sale Crown Victoria - 143.mfbattle.com

Posted by Anonymous on 23 May 2010

Pingback from  Replacement Chevy Nova Parts, Milky Way Wishes Nova - 271.akemet.com

Posted by Anonymous on 24 May 2010

Pingback from  1998 Pontiac Grand Am Gt Used, Pontiac G8 Wheel Covers - 226.jordanbrandallamerican.com

Posted by Anonymous on 24 May 2010

Pingback from  Terraza Pt Coastal, 2006 Buick Terraza Edmunds - 91.jeepsunlimted.com

Posted by Anonymous on 24 May 2010

Pingback from  C230 Europe, C43 Amg Sale C230 Kompressor 2000 Mercedes Benz C300 Sport - 191.ja3ra.com

Posted by Anonymous on 25 May 2010

Pingback from  Cb6000 Lyrics Headlight, Review 1985 Pontiac 6000 - 102.tijuanareader.com

Posted by Anonymous on 25 May 2010

Pingback from  Prizm Oxygen Sensor Walker Products, Custom Cts Division - 138.jeepsunlimted.com

Leave a Comment

Please register or log in to leave a comment.