In-Memory OLTP in Azure SQL Database

Hey guys, good news for you all who use Azure SQL Database.

Below are some of the cool features Microsoft added in Azure SQL Database:

• Earlier including the index created with the primary key, the memory-optimized table can have up to 8 indexes only. Now with the changes incorporated in June 2017 we can create more than eight indexes on memory-optimized tables
• No more alter table command to change the memory-optimized objects, sp_rename can be used for renaming memory-optimized objects
• You will no longer see the Error 41839 – “Transaction exceeded the maximum number of commit dependencies and the last statement was aborted. Retry the statement.” Microsoft eliminated the limit on the number of commit dependencies
• Now we are free to use the CASE expression, the CROSS APPLY operator, and all JSON functions, isn't it interesting!
• Did you even think of using computed columns in memory-optimized tables, if you think in such direction then please start using computed columns in memory-optimized tables

How to Encrypt Managed Disks using Azure Storage Service Encryption (SSE)

Microsoft keeps adding more and more security features in Azure and the latest addition to that is the Azure "Storage Service Encryption"

Today we will see how to enable the Azure storage encryption.

Within 3 to 4 clicks we will be able to encrypt the storage account.

Lets login to the Azure account first. Once you login click on the "Storage Accounts" Menu.

New blade will be opened with all the available storage account you have created.

Storage Accounts

Now you select the storage account you want to encrypt, you will find the Encryption option, just click on the "Encryption" option.

Here in the right pane you will be able to Enable or Disable the Encryption. At present this feature is available for Azure Blobs and Files.

But nothing to worry more options are going to come soon.

Also you need to keep it in your mind that once you Enable the encryption only new Data will be encrypted, all the existing files will remain as it is.

Thanks for visiting my blog, please leave your comment below.

Can we insert explicit values into IDENTITY column in MS SQL

This is one of my favorite interview question. I hope you also would have faced this question, if not then there is a high possibility that you may face it soon!
Answer to the question Can we insert explicit values into IDENTITY column is MS SQL is YES. You can insert values into the IDENTITY column as you require.

Lets start step by step.

First lets create a table for the demo purpose,

CREATE TABLE DemoIdentity

(

    Id int IDENTITY (1, 1) not null,

    Name varchar(50) not null,

    [Address] varchar (100) not null,

    Phone char (15) not null

)

 

Table is ready, now we will insert some values to the above table.

INSERT INTO [DemoIdentity] ([Name] ,[Address] ,[Phone]) VALUES ('Asheej','Bangalore','9916123456')

INSERT INTO [DemoIdentity] ([Name] ,[Address] ,[Phone]) VALUES ('David','Birmingham','7543789234')

INSERT INTO [DemoIdentity] ([Name] ,[Address] ,[Phone]) VALUES ('Cliff','Columbus','4081234567')

Above is the standard way to insert the values into the table which has IDENTITY column.

Now lets try to insert a value with identity column value,

 

INSERT INTO [DemoIdentity] ([ID], [Name] ,[Address] ,[Phone]) VALUES (4,'Johnson','Perth','61478976782')

If you execute above SQL statement you will get below error,

Msg 544, Level 16, State 1, Line 11
Cannot insert explicit value for identity column in table 'DemoIdentity' when IDENTITY_INSERT is set to OFF.

Yes, now we will see the property which MS SQL provides you to insert values in IDENTITY column.

In SQL we have the property named IDENTITY_INSERT. Whenever you wanted to insert values to an IDENTITY column we just need to enable the property IDENTITY_INSERT.

Lets enable this property and insert a value in above table.

Syntax for enabling the IDENTITY_INSERT is,

SET IDENTITY_INSERT [DemoIdentity] OFF

Now we will insert the value by executing the same above statement by specifying the IDENTITY column value and see the result.
 
One important thing you have to remember is, in order to enable or disable the IDENTITY_INSERT  property you should be part of the sysadmin group.

Make sure you disable the IDENTITY_INSERT ON once you are done with the insert statement as this column is not intended to insert the values explicitly.

Syntax for disabling the IDENTITY_INSERT is,

SET IDENTITY_INSERT [DemoIdentity] ON

 

I hope now you understood the whole concept of the property IDENTITY_INSERT and how you can insert explicit values into an IDENTITY column.

Visual Studio In-built Support for Windows Azure

As you are aware Visual Studio has evolved a lot and every new release Microsoft adds many features. Since Windows azure is the hot cake in the market, of course Microsoft added many inbuilt features in Visual Studio 2013. Today we are going to explore few important features which will help you to debug your application.

 

Lets go through one by one, lets connect to Windows Azure website form Visual Studio.

Now you can directly connect to your Azure website from Visual Studio. IN order to connect to the windows azure, first you need to sign in to your Azure account. If you are already connected then you can directly open the server explorer form View Menu.

Once you open Server Explorer, you can find an option named “Azure”. You may expand the Azure to find the available options under that. This links nothing but what you see once you login to your Azure account.

 

You can compare the feature list below. Except Visual Studio online everything can be found there. Visual Studio online is a different configuration so that is available under Team explorer in Visual Studio.

 
Now what we are going to explore is the features available under Websites.

 

Lets expand the Websites, you will notice that all the websites which is already hosted in your Azure account will listed there.

We will concentrate on one website now and will see what we can do directly from Visual Studio.

 

Lets right click on one website and select View Settings. You will be amazed to see the options which is opened in your right panel. Lets explore one by one now.

First one you would see is the Stop Website and Restart Website. If the website is in the stopped state you will find an option Start Website in place of Stop Website. As you are aware you stop the application inn windows Azure as well same like the way you do it in IIS.

I believe nothing more to explain this feature as it has got its literal meaning.

Next options which shows under Website Settings are mainly for debugging.

First option is definitely the Framework version. You will have two options either V4.5 or V3.5

You may choose the framework version depends on the one you want to run your application.

Next four settings are related to remote debugging.
1. Web Server Logging
2. Detailed Error Messages
3. Failed Request Tracing
4. Application Logging (File System)
5. Remote Debugging

You may enable all or the require logging options in case you are facing any issues with the application. It is very easy to know what exactly is the issue your application is facing by going through this log files. As soon as you change this logging options it will reflect in Windows Azure as well. Which means instead of logging-in to your windows Azure  account you may manage all these options from Visual Studio itself.

I will be stopping today, there are much more to explain about the log files which you are going to get by enabling this logging mechanism.

I will be writing a separate articles for each logs.
Thanks a lot of reading my article.

Rank VS Dense_Rank in MS SQL

Today we will some interesting SQL feature. You would have seen in SQL RANK and DENSE_Rank function. Did you ever think why we have these two functions and what exactly is the difference between RANK and DENSE_RANK in MS SQL.

My task for the day is to teach you the difference between RANK and DENSE_Rank in MS SQL.

First I will give a small description about the difference and then we will go through the sample.

RANK—> Rank function will help you to find the RANK within your ordered partition. If you have same rank for the two rows then the next rank will be skipped. Which means if you have 3 items with the same rank as 3 then the next rank will be 6. 4th and 5th rank will be skipped.

DENSE_RANK—> Dense_Rank function will help you to rank the the item within your partition same like RANK function but it will not skip any ranking, it will be consecutive. Which means if you have the same scenario we taken above that is 3 items with the same rank as 3 then the next rank will be 4 in case of dense_rank.

Now lets see the demo.

First we will create the table

CREATE TABLE [dbo].[tblStatistics](

    [ID] [numeric](18, 0) NULL,

    [Name] [varchar](50) NULL,

    [Salary] [numeric](18, 0) NULL,

    [City] [nchar](20) NULL

) ON [PRIMARY]

Lets query the table and see the output,

Above one is the simple select query. Now we will query using RANK and DENSE_RANK together to see the difference.

Rank and Rank_Dense query syntax is slightly different from our regular query as you have to mention the partition in query.

 

SELECT [Name]

      ,[Salary]

      ,[City]

      ,RANK() over (partition by City order by Salary) Rank_Demo

      ,dense_rank() over (partition by City order by Salary) Dense_Rank_Demo

      FROM [tblStatistics]

Lets execute above query and see the result.

You would have noticed that the skipping happened in the RANK for Rank column and consecutive rank for the Rank_Dense column in the above screen print.

That’s all..now you learned the difference between RANK and DENSE_RANK in SQL.

Thanks for reading my article!

Compatibility level 90 is deprecated in MS SQL 2014

As we are going to use MS SQL 2014 in near future, compatibility level setting is one of the important thing we should know. Especially when you are migrating SQL from older version we should set this compatibility level properly otherwise your database migration will be a flop.

In MS SQL 2014 Microsoft declared that compatibility level 90 is deprecated.

What it means for the DBA or a developer?

To know the answer for the above question first you should know what is compatibility level. I will give you a short table below to understand how it is related to each version MS SQL.

Compatibility Level	MS SQL Server Version	Applies to
80	SQL Server 2000	SQL Server 2008 through SQL Server 2008 R2
90	SQL Server 2005	SQL Server 2008 through SQL Server 2012
100	SQL Server 2008 and SQL Server 2008 R2	SQL Server 2008 through SQL Server 2014
110	SQL Server 2012	SQL Server 2012 through SQL Server 2014
120	SQL Server 2014	SQL Server 2014 through SQL Server 2014

 

Now you might be clear what is this compatibility level all about and why Level 90 is deprecated in SQL 2014.

When Microsoft says compatibility level 90 is deprecated which means when you are migrating from SQL server 2005 or older version to SQL server 2014 you have to change the compatibility level from 90 to 100, that is the minimum compatibility level SQL 2014 supports.

Once you install SQL server 2014 then the default compatibility level will be set it as 120. Where as when you migrate your database from older version database retains its existing compatibility level if it is at least 100, which means it retains its compatibility level if the database you are migrating is SQL 2008 or above.

So here comes the catch. What will happen if you are migrating SQL server 2005 or older version. In such cases we have to change the compatibility level to 100 manually.
Microsoft recommends below steps to change the compatibility level,

 

1. Login to the database and set the database to single-user access mode by using ALTER DATABASE SET SINGLE_USER.
   
2. Change the compatibility level of the database.
   

   ALTER DATABASE AdventureWorks2012
   SET COMPATIBILITY_LEVEL = 100;
   GO

3. Put the database in multiuser access mode by using ALTER DATABASE SET MULTI_USER.

Other option is login to the database. Select the DB you wanted to change the compatibility level and then right click to open the properties.

Click on properties and then select Options. Scroll down to change the Database to Single User mode and then click OK.

Now click on the Compatibility level dropdown and select the preferred compatibility level. Once you change the compatibility level then scroll down again and change the database to multi user mode.
You are done now!!!

I would like to offer you further reading in this regard too, here is the MSDN link related to the same topic.

I hope you enjoyed reading this and learnt something interesting.

Role based access in Windows Azure

This is one of the demand every subscriber was asking to Microsoft and finally they came up with the proper solution. So now you don’t need to have multiple subscription for development, test and production. Everything can be managed by single subscription.

Today I will be taking you through the simple steps to add the users with different rights in Windows azure.

Windows Azure has got 3 roles now,
 

To add to this roles, Microsoft suggest you to configure Active directory with these roles and add the users to this active directory group.

For example, If you would like to add the user “Martin” then you need to him to the active directory to the respective role, i.e. read, write or contributor role. Once you add him to the AD then his name can be selected from the Azure.

Lets see how can you select the existing AD user or how you can add a guest user.

In order to add the users first click on Browse—>Everything and then click on the item “Subscriptions…”. This will show all the subscriptions you have in your account in case you have multiple subscriptions.

Select the subscription you wanted to add the user. Once you select the subscription it will show the roles in the right pane. Now select the role you wanted to add the user.

If you wanted to add a read only user then click on the reader option. This will again open a right pane to add the user.

Click on the ADD button to add the user from the Active directory. As soon as you type in if the user is part of the Active Directory which is synced with the Azure it will automatically pops up.

Here you will find an option to invite the user as well. If the user already have a Microsoft account you may use that to provide Azure access.

I hope you are very clear about the steps you need to follow to provide role based access to Windows Azure.

I have a further reading link in case you require more information regarding this. 

Error 8672 in MS SQL 2008 while using Merge statement

This is one of the weird error you get in MS SQL 2008 if you have Merge statement in your query.

Below is the complete exception message which you may receive. 

The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows. [SQLSTATE 42000] (Error 8672).

Interesting thing about the above exception is you will get this exception even if you don’t have duplicate rows are there during the join operation. When I got this message I was checking for the duplicate rows first as the error message was highlighting that.

Any way Microsoft informed that this is a bug in SQL Server 2008. In order to resolve this issue you have to install Cumulative Update 6 for SQL Server 2008 Service Pack 1. You can download the same from Microsoft Download centre. Here is the link for “Cumulative Update 6 for SQL Server 2008 Service Pack 1”.

Microsoft created a KB article to address this issue. If you are interested to know more about this issue please refer Microsoft KB article.

I hope after installing the above updates your error is disappeared and the same query is running without any issues.

Filtered Index in MS SQL

What is filtered index? What is the benefit of using filtered index? Does filtered index really improves the performance? All these queries will be answered in this article.
Lets start with what is filtered index? A kind of interview question.
Filtered index is an optimized non clustered index. Filtered index is more suitable for the queries which select the data from a well defined subset. Important thing you have to remember about the filtered index is it uses the filter predicate to index only a portion of rows in a table. This is what I have mentioned above that “filtered index is suitable for the queries which select the data from a well defined subset”.
Does the filtered index improve the performance?

How to use OUTPUT Clause in MS SQL / What is the use of OUTPUT Clause?

Today we will learn some SQL. Have you ever used OUTPUT clause in MS SQL? What exactly us the use of OUTPUT clause in MS SQL? What is the advantage of using OUTPUT clause in SQL? All these questions are answered in this article.

What is OUTPUT clause in SQL?

OUTPUT clause gives you the information about each row impacted by running INSERT or UPDATE or DELETE or MERGE SQL statement. Normally in applications we use this information to return to the user in a meaningful manner to show the result of submitting the data.

What you can do is, you may save this information in a temporary table or in a permanent table itself for future use. You can use this information for auditing purpose also because it has got complete information about the sol statement which you executed row by row.

The main disadvantage of this OUTPUT clause is it returns the same information even if your SQL execution throws error or it is rolled back. So you need to make sure that such information should not be used for any auditing purpose or to pass the information to client.

Now lets see what kind of information we gets when we use OUTPUT clause.

First we will create a sample table.

CREATE TABLE [dbo].[tblBill](

    [BillDate] [datetime] NULL,

    [Expense] [numeric](18, 0) NULL,

    [travel] [numeric](18, 0) NULL

)

GO

Lets query the table and see the values we have inserted in this table.

Now we will delete one row with the OUTPUT clause and see the result and also the syntax to use output clause.

  delete from [DemoAzure].[dbo].[tblBill] 

  OUTPUT DELETED.*

  where travel = 150

As you can see above OUTPUT clause should be before where clause.

Once you execute this statement it will return the complete row which got deleted like a select statement. This is the reason I mentioned earlier that we can directly insert this values into a table for future use or for auditing purpose.

Lets see the result set now.

Lets see a sample code to insert the value in a table variable.

DECLARE @tblBillVar table( 

    [BillDate] [datetime] NULL,

    [Expense] [numeric](18, 0) NULL,

    [travel] [numeric](18, 0) NULL);

INSERT INTO [dbo].[tblBill] ([BillDate],[Expense],[travel]) 

OUTPUT INSERTED.[BillDate], INSERTED.[Expense], INSERTED.[travel]

        INTO @tblBillVar

VALUES ('2015-01-06 00:00:00.000',875,475)

select * from @tblBillVar

Once you execute above query you will find that the inserted values will be displayed in the query analyzer.

Similar to table variable you can have your own table as well.

I hope you are now clear about the OUTPUT clause in MS SQL.

GROUPING SETS in MS SQL

I have been doing some reporting work today. In that report I have to show some trending. In fact I have to show average salary based on the city and department. I was thinking what will be the easiest option to display in that manner, only thing is I don’t want to write huge SQL query to show this data.  Nothing to worry now, you have an option in MS SQL that is nothing by grouping set.
Today we will see how we can use GROUPING SET in SQL query and when exactly we can use this query.
First we will create a table with the name tblStatistics.

Synchronize local MS SQL database to SQL Azure

In this article I will be taking you through the steps to synchronize your local MS SQL Database to SQL Azure. This is one of the regular requirement you may find in your application development when you are using Windows Azure. Synchronizing the local database will allow you to export all the data from your local database to SQL Azure in few seconds or in few minutes.
SQL Azure allows you to configure this synchronization automatically and will allow you to set the frequency of data synchronization as well. 
Ok, as usual we will go through the complete procedure step by step.
First we will create a database in our local database system and insert few data in the table.

Deploy ASP.NET web application in cloud with SQL Azure Connectivity

Today I am taking you to the complete deployment of an ASP.NET web application with database connectivity in Microsoft Azure. Since database is in cloud we have to do some configuration changes while doing the deployment. In this article we will do the sample development in local system and we will do only the deployment in Microsoft Azure. As you are aware once deploy the application in Microsoft Azure by using SQL Azure as database everything will be online and people will be able to access the application over the net.
Lets go through the complete procedure you have to follow to do the web application deployment with SQL Azure.