SQL Server In Cloud

Technorati Tags: Access cloud SQL Server from client SSMS

I am excited i got windows azure subscription, i couldn’t stop myself to create a SQL Server Instance in cloud. Here is my small experience with cloud. Lets create SQL Server instance and access from our client SSMS(SQL Server Management Studio).

Step 1: Go to your portal:

Step 2: Click on +New (Quick Launch for Services) Or SQL Database Symbol on Left pane:

If you click on new, this will give you quick launch of cloud services. You can choose your services like compute (create website, Virtual Machine, Virtual Network, Mobile Services), Data Services (Create Database, Storage, Big-data etc), App Services etc.

Step 3: Choose Data Services/SQL Database/Quick Create:

Step 4: Server is ready:

Then you can see a progress bar there in the portal and at bottom as well. Once that is created we can customize as well. Keep in mind this user name and password will be useful connecting server from client side.

Step 5: Give access to your client machine to access through SSMS:

Click on server name as marked in below image.

You get to another page where you can allow access to clients ip address. Here you can see three textboxes, enter computer name (for your reference only) then starting range of IP and ending range of IP.

You can get your client IP of your system by typing “my IP” in Google; this I thought would be simplest.

Then save the changes. Now if you have given permission for your computer IP then you will be able to use SSMS to connect to the server from the permitted client machine.

Once Server is ready then we can access this database even from our client SSMS tool. For that you need Server Name. For that follow next step.

Step 6:Let’s find server name:

Click on Manage as shown below.

Copy this completely like “xxxxxxxxxxx.database.windows.net”. This is Server name.

Let’s try connecting to the server via SSMS in permitted client machine. As you can see I have given server name as copied as explained in above step.

There you are connected with the server, now you can do all operation in your machine and the changes will be saved in your server created in cloud.

Now you have created a SQL Server Instances in cloud. Enjoy SQL coding…..

Compare two tables with same columns sizes in SQL Server

Sometimes you are suppose to change logic of your procedure/function which results a table and then you have to compare the tables, whether the logical modification caused any change in your table. If you are facing any scenario like this, then here i am sharing my experience. So lets begin with a table “Address_user”,which contains user address shown below in screenshot.

SELECT au.AddressID,au.AddressLine1,au.City,au.PostalCode FROM Address_User au

  Assume that we have got some changes in logic of procedure/function, so first of all we will take backup of the table like shown in below query.

SELECT * INTO Address_User_backup FROM Address_User au

Now both will be same in the result, lets compare. There are many ways to compare two tables, here i will show you couple of ways like

• Using EXCEPT keyword
• Using Checksum
• Using NOT IN
• Using union all

Using Except Keyword:

Its very simple just you need to use Except keyword between select query of two tables like shown below. this returns any distinct values from the left query that are not found on the right query.

SELECT au.AddressID,au.AddressLine1,au.City,au.PostalCode FROM Address_User au
EXCEPT
SELECT aub.AddressID,aub.AddressLine1,aub.City,aub.PostalCode FROM Address_User_backup aub

This wont result you anything, as both tables are exactly same.

Lets modify the table now , i am updating city ‘Portland’ with ‘Kochi’.

UPDATE Address_User
SET City='Kochi'
WHERE City='Portland'

Now lets compare again using same query. You can see the result below, which displays city Kochi, which is there in left side query but not in right side of Except.

Lets swap the select queries and then compare the result. you can see it results Portland instead of Kochi. it is because backup table is having city Portland which is not there in Right-side query of EXCEPT Keyword.

SELECT aub.AddressID,aub.AddressLine1,aub.City,aub.PostalCode FROM Address_User_backup aub
EXCEPT
SELECT au.AddressID,au.AddressLine1,au.City,au.PostalCode FROM Address_User au

Using Checksum:

If you are looking for faster way and exact match then you can use this method.

IF (
    SELECT   CHECKSUM_AGG(BINARY_CHECKSUM(*))
        FROM   Address_User)=(
    SELECT   CHECKSUM_AGG(BINARY_CHECKSUM(*))
        FROM   Address_User_backup)
SELECT 'they are probably the same'
ELSE
SELECT 'they are different'

Using NOT IN:

If you are not having duplication in data then you can choose this method.

SELECT * FROM Address_User WHERE
AddressID NOT IN (SELECT AddressID FROM  Address_User_backup)

For complete explanation please refer this link.

http://www.codeproject.com/Articles/619094/EXCEPT-vs-NOT-IN-in-SQL-Server

Using union all:

Its preferable to use if you have primary key in your table.

SELECT COUNT(xyz.AddressID) FROM
(SELECT au.AddressID,au.AddressLine1,au.City,au.PostalCode FROM Address_User au
UNION ALL
SELECT aub.AddressID,aub.AddressLine1,aub.City,aub.PostalCode FROM Address_User_backup aub) xyz
GROUP BY xyz.AddressLine1,xyz.City,xyz.PostalCode
HAVING COUNT(xyz.AddressID)<>2

Conclusion:

Like i explained comparing two tables can be done in many ways depending upon your requirement, but among these explained methods i preferred to use Except or checksum, because they are simpler and faster as well. other than these explained method you can use Joins as well to comparison.

References:

http://www.codeproject.com/Articles/619094/EXCEPT-vs-NOT-IN-in-SQL-Server

https://www.simple-talk.com/sql/t-sql-programming/on-comparing-tables-in-sql-server/

http://msdn.microsoft.com/en-IN/library/ms188055.aspx

Nested Transaction in SQL Server 2012

Nested transaction is nothing but, you can make another transaction without even committing/Rollback previous transaction. With Every begin the value of @@Trancount increases by one and this is counter balanced by commit statement. As shown in image below. But in case of rollback it directly falls back to zero.

 

Lets experiment this;

Assume i have a table “Columns_for_Transaction_Example” having some values in it

Begin Nested Transaction:

here the value of Trancount will become 1. And new value would be like this as shown below

Let’s begin another transaction:

 

now Trancount will change to 2

 

lets check the value in table after the update, as  we can see offset value will become 0.

so now lets commit one by one , As shown in transaction tree figure trancount will decrease one by one ,can’t reduce to zero directly.

similarly if we do rollback,Trancount decreases directly to zero As shown below.

How SQL Transactions Affect if not committed or rolled back

We have discussed little about SQL transactions in my previous post.Today lets discuss behavior of transactions in different scenarios.Imagine a scenario where transaction has begun but neither committed yet ,nor rolled back.

let’s consider a table for example which is taken from system table:

/*Create a table Columns_for_Transaction_Example */

Select * into COLUMNS_FOR_TRANSACTION_EXAMPLE from sys.syscolumns

 

/*Fetch table #Columns_for_Transaction_Example for particular Id */

select object_name(id) Names,id,xtype,length,offset

from Columns_for_Transaction_Example where id=2089058478

This would be the result:

Let’s check the TRANCOUNT now:

That shows that there is no pending transaction, or all the transactions are committed.

Let’s come back to the scenario we were discussing about.How this scenario is going to affect Transaction in this database.Lets begin the transaction.

 

In this transaction named “MyTransaction” which deletes the values from a table having id=2089058478. On execution I found like 4 rows are affected.

Let’s check the Trancount:

This shows there is one uncommitted transaction in this database.

 

Let’s see what happens if I query something from this table from another session(Another query window).

You can see my snapshot which says it executing for past 9 minutes, Hence you cannot make any transaction on this table until the transaction is committed or rolled back.

What if you query that table with query hint [nowait] ? You get message like table is locked. This means the pages are protected from reading uncommitted data.

 

So now, we can either rollback the changes or we can commit if we think its fine to do so. As soon as we rollback or commit the Trancount reduces to “0”.If we rollback, then changes made will be reverted back. If we commit then the changes will be committed to the page.

Rollback Transaction Mytransaction;

COMMIT TRANSACTION Mytransaction;

Transactions in SQL Server 2012

 

Well i am learning transaction these days, So thought of sharing my earnings with all. well this is very useful when we need to have good control on transaction. Or in case of failure/error we can handle the transaction like roll back the changes . During learning process i referred couple of blogs and MSDN as well.

Transactions:

Basically as simple it is, transaction is any task performed in SQL Server by our command given. To be precise smallest unit of task called transaction. For example, reading data from table, inserting data to the table we say its transaction.

When we use select statement, we read data. It’s transaction. We update table, again we enter data to the specified table its transaction. Similarly we give any command from SSMS, read data or write information to memory/Table.

Transactions are having standard property called ACID:

• Atomicity: Guaranty the task, for its success or failure.
• Consistent: Ensures that the database properly changes states upon a successfully committed transaction.
• Isolation: Ensures that the database properly changes states upon a successfully committed transaction.
• Durability: Ensures that the result or effect of a committed transaction persists in case of a system failure.

Sometimes we need to control over the transactions we made, thus SQL provides some commands to us, to control the transaction for DML (Select, Insert and Update). These commands are:

• Begin Transaction
• Commit Transaction
• Rollback Transaction
• Save Transaction

 

This becomes Important to talk about Global Variable @@TRANCOUNT when we are discussing about Transactions. This is the variable which keeps the value ,or number of times Transaction happened which is not yet committed. If there is no Transaction then value in this variable would be zero.

We can discuss about this in Next blog.

Cumulative Addition in SQL Server 2012

Today i found new feature added in SQL Server 2012. I was working for a query which was resulting cumulative addition, which can help you in avoiding CTE’s or complex sub queries. This is Possible by using OVER clause.

 

Let’s Consider an example here:

--Create Table

CREATE TABLE RunTotalTestData (
   id    int not null identity(1,1) primary key,
   Name  nvarchar(25),
   Department nvarchar(30),
   salary int not null
  );

 

-- Inserting Values

INSERT INTO RunTotalTestData (Name,Department,salary) VALUES ('Shekhar','EC',10000);
INSERT INTO RunTotalTestData (Name,Department,salary) VALUES ('Amit','EC',200000);
INSERT INTO RunTotalTestData (Name,Department,salary) VALUES ('Sumit','EC',4000000);
INSERT INTO RunTotalTestData (Name,Department,salary) VALUES ('Sunit','EC',7000000);
INSERT INTO RunTotalTestData (Name,Department,salary) VALUES ('Sunita','BC',9000000);
INSERT INTO RunTotalTestData (Name,Department,salary) VALUES ('Abhishekh','BC',12000);
INSERT INTO RunTotalTestData (Name,Department,salary) VALUES ('Shiva','BC',1300000);
INSERT INTO RunTotalTestData (Name,Department,salary) VALUES ('Niju','CC',1600000);
INSERT INTO RunTotalTestData (Name,Department,salary) VALUES ('Linju','CC',2200000);
INSERT INTO RunTotalTestData (Name,Department,salary) VALUES ('Sreeju','CC',4200000);
INSERT INTO RunTotalTestData (Name,Department,salary) VALUES ('Praveen','CC',5700000);
INSERT INTO RunTotalTestData (Name,Department,salary) VALUES ('Anju','CC',5800000);
INSERT INTO RunTotalTestData (Name,Department,salary) VALUES ('Veena','FC',5900000);
INSERT INTO RunTotalTestData (Name,Department,salary) VALUES ('Parvaty','FC',6000000);

 

Here is the query to result the cumulative some for each department, In case if we want for all then we can simple use over clause with order by.

 

SELECT    a.id
        ,a.Name
        ,a.Department       
        , SUM(a.salary/100000) OVER (Partition by a.Department ORDER BY a.id) as cumulative_sum
FROM   RunTotalTestData a
group by a.id, a.Department,a.Name,a.salary
ORDER BY a.id;

Technorati Tags: Cumulative addition in SQL 2012,sum() over() clause,sum with over clause