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. 

Export ASP.NET web page into MS Word / How to export DIV tag contents into MS Word

I have been forced write this article “How to export ASP.NET Web Page content into MS Word” because of the huge number of similar questions I have been answering in Microsoft forums.

Sometimes you may have to export your webpage content into word or PDF or Excel especially if the user wanted to print the whole content of your webpage.

Today we will see this simple code.

First we will create a demo page for this purpose with few labels and textboxes.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ExportToWord.aspx.cs" Inherits="DotnetGalaxy.ExportToWord" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title>Export To Word - Div Tag Content</title>

</head>

<body>

    <form id="form1" runat="server">

   <div id="ExportToWordDiv" runat="server">

        <table class="auto-style1" border="1">

            <tr>

                <td>

                    <asp:Label ID="Label1" runat="server" Text="Name"></asp:Label>

                </td>

                <td>

                    <asp:TextBox ID="TextBox1" runat="server" Width="200px"></asp:TextBox>

                </td>

            </tr>

            <tr>

                <td>

                    <asp:Label ID="Label2" runat="server" Text="Address"></asp:Label>

                </td>

                <td>

                    <asp:TextBox ID="TextBox2" runat="server" Height="21px" Width="300px"></asp:TextBox>

                </td>

            </tr>

            <tr>

                <td>

                    <asp:Label ID="Label3" runat="server" Text="Phone"></asp:Label>

                </td>

                <td>

                    <asp:TextBox ID="TextBox3" runat="server" Width="200px"></asp:TextBox>

                </td>

            </tr>

            <tr>

                <td></td>

                <td>

                    <asp:Button ID="btnExportToWord" runat="server" Text="Export To Word" OnClick="btnExportToWord_Click" />

                </td>

            </tr>

        </table>

    </div>

    </form>

</body>

</html>

As you can see in the above code, only difference from the normal code to the above one is I have given a name to the div tag, because we are going to export all the contents inside the DIV tag into MS Word.

Now we will run this project and see the result.

Its the time to write the code to export to Word now. Lets add the the required namespace first and then write the code under Button click event.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Net;

using System.IO;

namespace DotnetGalaxy

{

    public partial class ExportToWord : System.Web.UI.Page

    {

        protected void Page_Load(object sender, EventArgs e)

        {

        }

        protected void btnExportToWord_Click(object sender, EventArgs e)

        {

            try

            {

                Response.Clear();

                Response.AddHeader("content-disposition", "attachment;filename=DemoExport.doc");

                Response.Charset = "";

                Response.Cache.SetCacheability(HttpCacheability.NoCache);

                Response.ContentType = "application/doc";

                System.IO.StringWriter stringWrite = new System.IO.StringWriter();

                System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

                ExportToWordDiv.RenderControl(htmlWrite);

                Response.Write(stringWrite.ToString());

                Response.End();

            }

            catch (Exception ex)

            {

                string strError = "Error while exporting to Word" + ex.Message;

            }

        }

    }

}

Only one additional Namespace I have added and that is “using System.IO;” Don’t forget to add this namespace.

There is nothing else specific in the code to explain.

Lets see how this will be exported in MS Word.

As you can see the same content whatever it was there in the web page has been exported to MS Word.

I hope whatever I have explained above is very clear and no about about anything.

You are always welcome to ask questions or drop a comment about my article.

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.