SQL Azure Beginner’s TSQL Task: Creating a basic stored procedure

Being New at something is scary! In Tech field, One of the ways you could NOT be NEW at something is to kick the tire’s of that tech for a while. To that end, Here are the few tasks that you could perform if you are new to SQL Azure – Kick SQL Azure’s tires for a while – write few TSQL on SQL Azure – And NOT be NEW at it anymore. Go Learn!

In this Part 1 of N, I am going to create a stored procedure that accepts a parameter with SQL Azure.

So I have a table that lists firstname, lastname of the customer along with location details. Here’s what it looks like:

Resource: A Tutorial on Installing a sample database on SQL Azure.

And just note that I running the queries on SQL Azure management portal and not on SSMS. You can do so in SSMS too. But for this blog-post – I used the SQL Azure management portal.

So now we want to write a Stored Procedure which gives this information when it is called; Not only that, we want that stored procedure to return information about customers from the country that’s specified by the person calling the procedure. And such a stored procedure would empower the person who is going to call the stored procedure to filter the customer list based on the country of their choice. So let’s go ahead and write the code:

[sourcecode language=”sql”]

create procedure saleslt.spcustomerlocation @countryname nvarchar(50)
as
select firstname,lastname,city,stateprovince,countryregion from customertable
where countryregion = @countryname

[/sourcecode]

Now once we have created the stored procedure, It can be called by running the following code:

[sourcecode language=”sql”]

exec saleslt.spcustomerlocation ‘United States’

[/sourcecode]

And it would give us list of customers in United States like shown below:

So that’s about it for this post.

And Let’s connect! I Look forward to Interacting with you on any of these people networks:

Want to practice public speaking? Join Toastmaster’s!

Note the title of the blog-post, I have used the word “practice” and not words like learning or Understanding because I believe that one of the best ways to become a good public speaker is to practice – practice – practice. Just do it! You know what, You can listen to 10 great speeches, learn from it – I am not asking you to stop that. But you must find a play ground that would allow you to test out the ideas you have learned. You need a playground that allows you to fail. You need a playground that can help you get feedback.

And Toastmaster is that playground for you.

And what is Toastmaster? From their website:

Toastmasters meeting is a learn-by-doing workshop in which participants hone their speaking and leadership skills in a no-pressure atmosphere.

And from my 8 month’s of experience as a Toastmaster member, Here’s why Toastmaster is a perfect playground for practicing public speaking:

Toastmaster’s has modular format and each module has specific goal. So for instance goal of one of the module is to get your body movements right. The goal of other module is to get the organization right.
Every speech is evaluated. Every. Every Speaker gets an oral and written feedback “evaluation”
Toastmaster’s club meetings are structured. So everyone knows what their role is. There’s no confusion. So for instance, couple of them know that they are speaking. Couple of them are evaluating. There is grammarian to point of grammatical errors. And there are other meaningful roles too.
Toastmaster’s also has a meeting period dedicated to give members chance to practice “impromptu” speaking. So that means you practice how to think on your feet.
Toastmaster’s gives you ample of opportunities to practice public speaking with NO time constraint at all. You can pace your learning the way you want.

Stepping back for a moment, Public speaking is probably one of the most important skill for your career and so I just wanted to point out that Toastmaster’s is THE best resource that I know of that would help you do so. There are clubs all across the globe and if you find a club that’s near you – what are you waiting for?

How I solved sharepoint’s service unavailable error, HTTP 503?

I have Sharepoint installed on one of my VM and I needed to open Sharepoint central administration for some reason. BTW, if you are into Sharepoint BI – there’s a little chance that you may run into this error and the error says “Service unavailable. HTTP error 503”. And after quick searches on Internet, figured out how to solve the problem. It turns out that I had changed the password of the Identity. It was my demo machine and I was running sharepoint using the domain’s administrative account. Don’t hit me! I am learning..Kidding. Any-who, Here are the steps:

Go to IIS Manager > Application pools > Locate sharepoint central admin that stopped > right click > Advanced settings > reset password.

Here’s a quick video. I am sorry for poor audio, Next time I’ll use a better tool:

Ability to create Hans-Rosling styled BI dashboard with PowerView is great!

I got a chance to play with power-view which seems like great feature of SQL Server 2012! And I liked it that we can create Hans Rosling styled BI dashboard’s with Powerview. For those who do not know about Hans Rosling presentations, check these collection: All Hans Roslings Ted Videos.

And by studying modules in SQL server Training kit 2012, I was able to create a similar Dashboard, It was fun!

Now, Listing resources for you, If you are Interested to learn about Powerview:

Check out this SolidQ Journal article: http://www.solidq.com/sqj/Pages/Business_Intelligence/An-introduction-to-PowerView-in-SQL-Server-2012.aspx

Also, SQL server Training kit 2012 is a great resource.

And if you do not like setup’s and want to quickly get hands-on – Check this out: http://www.microsoft.com/sqlserver/en/us/learning-center/virtual-labs.aspx

And also Microsoft Virtual Academy for video tutorials about SQL Server 2012 BI:
1. breakthrough-insights-using-microsoft-sql-server-2012-analysis-services
2. breakthrough-insights-using-microsoft-sql-server-2012-reporting-services

That’s about it for this post. Your feedback is welcome!

And Let’s connect! I Look forward to Interacting with you on any of these people networks:

What’s the widely accepted definition of cloud computing?

Cloud computing is no more a “wild west”. Their seems to be a consensus on what cloud is – And more importantly what cloud is NOT. So what’s the widely accepted definition of cloud computing? Well, It’s the NIST definition of cloud computing.

It has three pieces to it, Here they are:

Characteristics of a CLOUD
Delivery (/Consumption/Service) Model of CLOUD
Deployment Model for cloud

Please search for “NIST Definition of Cloud Computing” and you’ll get to a (latest version/draft) short PDF that is worth reading – takes just 10 odd minutes but super helpful!

For your convenience (and Mine) – Here’s a visualization of the “data” that’s in the NIST’s definition document:

NIST's Widely accepted definition of CLOUD COMPUTING

If you want fun way to remember the definition, please go to: Cloud Computing is AWESOME (OSSM) :: Defining Cloud Computing the Urban Dictionary style! (It’s goal is to help you remember the characteristics of cloud, and i make fun of NIST’s definition – not because it’s not great but it seems academic which may not be the best way to explain cloud to non-technical or business person. So a fun way to remember cloud helps you have a sophisticated discussion with someone who is not completely familiar with cloud. Hope that blog posts is of help too)

And That’s about it for this post. Your feedback is welcome!

And Let’s connect! I Look forward to Interacting with you on any of these people networks:

Gartner’s Hype cycle for CLOUD COMPUTING: 2008 – 2009 – 2010 – 2011

In second half of 2008, I had taken a class on Distributed systems. As a part of that class, We were asked to write a paper on the subject of our choice. I had chosen the subject that focused on answering: How is cloud different from traditional approaches? My aim was to figure out why was CLOUD getting all the “attention”. And i chose this topic because, at that time [2008], I used to follow few blogs (including Gartner) at that time and almost all of them seemed to be “heavily promoting” cloud. So I asked myself “why is world going crazy about cloud?” “What makes cloud different from traditional approaches?” And I decided to find answers to this question and convert it into a term paper for the class. And during the term paper assignment, I got a traditional Hosting account and deployed one of my project there. And compared it with what literature on cloud computing had to say at that time. This approach was super helpful because I had a benchmark on what traditional hosting felt like and then what cloud computing enthusiast had to say about why is cloud better than traditional approaches.And after getting my head around that concept, it’s been fun following how “cloud” has evolved over time. One of the best ways to recap it is to analyze Garnter Hype cycle. To this end, Here’s a recap of where Gartner Places Cloud Computing (and its different flavors) on hype cycle from 2008, 2009, 2010 and 2011. Here’s a recap:

2008:

2009:

2010: (Note the presence of different flavors of cloud on the hype cycle)

2011:

Image Courtesy: http://www.gartner.com

And That’s about it for this post. Your feedback is welcome!

And Let’s connect! I Look forward to Interacting with you on any of these people networks:

Great to see neck to neck price competition between Azure and AWS

[I am referring to price reduction announcements by Azure and AWS in Early March 2012]

This week, Microsoft announced price reduction for Windows Azure compute and storage. Read more here – And even AWS announced price reduction too. Read more here. So it’s great to see neck to neck competition between Azure and AWS. And this is what is great about an economy that is not “monopolized”. To sustain, both Azure and AWS need to compete PLUS they compete with other cloud vendors that are out there too. Hopefully, other cloud vendors would also come up with an aggressive pricing strategy. Also, such competition among vendors would spur innovation and we would see “more features” coming out their factories more often than before (Yay!). And guess what, Who’s the winner? WE the CUSTOMERS!

Also, I noticed a pattern in both announcements that said “We are glad to pass along the savings to customer”. This seems to pointing to the fact that since cloud computing adoption has increased and cloud vendors benefit from Economies at scale – They pass the savings to the customers. And this acts as a catalyst for more adoption!

This seems like an awesome circle:

I understand that CLOUD ADOPTION is just not triggered by “price reduction”. That’s not what i mean here. But price reduction can sure act as catalyst. Hopefully, in not so distant future, the cloud prices would be so lucrative that setting up private data-centers would be a “thing of past” (unless you are governed by laws to have your own data-center or some other policy that restricts you from cloud adoption).

And I am also not comparing the price reduction “directly” because it would be like comparing Apples and Oranges. But what I hoped to point out was that we as customers would see more price reduction, more features, better experience because of the neck to neck competition among cloud vendors.

And That’s about it for this post. Your feedback is welcome!

And Let’s connect! I Look forward to Interacting with you on any of these people networks:

Do tables in a SQL Azure Database need to have a primary key?

Answer: No.

Though SQL Azure does need that a table has a clustered index. So to this end, Let’s write some TSQL code and cement this concept in our brain. So as a part of the test, we would create a table with no primary key – However we would certainly create a clustered index on one of the column. So let’s get started:

OK, so here’s the TSQL code to create a table named ‘InternationalStudentList’:

[code language=”sql”]create table InternationalStudentList (StudentName varchar(30),HomeCountry varchar(30), DegreeProgram varchar(30))
go
[/code]

Now let’s insert some data into this table. Here’s the query:

[code language=”sql”]
insert into internationalstudentlist values(‘Paras Doshi’,’India’,’Masters in IT and Management’)
go
[/code]

If you run the above query – you would get the error:

Msg 40054, Level 16, State 1, Line 1
Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.

So we know that a table in SQL Azure requires a clustered index, right? So, Let’s create one!
Let’s assume that the InternationalStudentList would be queried a LOT to answer the question: “List all StudentName whose HomeCountry is X” – So based on this information let’s create a clustered index on HomeCountry Column. Here’s the TSQL code:

[code language=”sql”]
create clustered index cix_internationalstudentlist_homecountry ON
InternationalStudentList(HomeCountry)
go
[/code]

Once a clustered index is created, Try inserting data again. And you would see that a row would get successfully Inserted!
Run a Select Command to verify that:

And as you can see, we were able to insert a row in a table. Remember that this table did not have a primary key But we did create a clustered index. The Goal of the Post is achieved here, But just want to show you the Query Plan for a Query that looks like:

[code language=”sql”]
select StudentName,HomeCountry,DegreeProgram from InternationalStudentList
where HomeCountry = ‘India’
go
[/code]

Note that I have run this queries on Management portal for SQL Azure, You can run it in SSMS too. But the goal is to show you that we have clustered index seek and that’s one of the way to tune queries. Not going into details in this post – And That’s about it for this post. Your feedback is welcome.

And Let’s connect! Here are few people networks that I am active on:

I Look forward to Interacting with you!

Insight Extractor – Blog

Paras Doshi's Blog on Analytics, Data Science & Business Intelligence.

Month: March 2012

SQL Azure Beginner’s TSQL Task: Creating a basic stored procedure

Like this:

Want to practice public speaking? Join Toastmaster’s!

Like this:

How I solved sharepoint’s service unavailable error, HTTP 503?

Like this:

Ability to create Hans-Rosling styled BI dashboard with PowerView is great!

Like this:

Do tables in a SQL Azure Database need to have a primary key?

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: