Yearly Archives: 2014

Clustered Vs NonClustered indexes (When to choose which one)

In this topic we are going to compare clustered indexes with nonclustered indexes with their usage and performance and will also discuss some points regarding the topic “when to choose which one”.  Clustered Vs NonClustered indexes is an old topic and you can get many article written so far. But in this post I will cover the definitions, structure of indexes, syntax of clustered and nonclustered indexes and the most interesting part is “when to choose which one”. So lets explore clustered and nonclustered indexes with “When to use Clustered vs NonClustered indexes” in deep.

Clustered index

As we know that a Clustered indexes sorts and stores the data rows in the table or view based on their key values. These are the columns included in the index definition. And the most important thing is we can have only one clustered index per table, because the data rows themselves can … More

Query Execution Flow Architecture (SQL Server)

In this article, we are going to discuss the SQL Server’s query execution flow architecture. When a query gets submitted to SQL Server, it goes through multiple steps before final output. We will discuss all these steps of “Query Execution Flow” starting from query submission to getting output.

As we all know that SQL Server has various components to perform various tasks on the given query. Each component performs some operation and pushes its output to the next component in the pipeline.

Lets understand the query execution flow architecture of DML statements. DDL and DCL statements do not follow optimization steps and gets submitted to storage engine directly after parsing step.

SQL Server Engine Architecture:

Below are the major components of SQL Server;

  1. Relation Engine
    1. Parser
    2. Alzebrizer
    3. Query Optimizer
  2. Storage Engine

Just have a look on the below image and then read the below details carefully;

Query Execution Flow

Query Execution Flow

As … More

Read XML Data as a table in SQL Server 1

Sometimes we need bulk data to be sent to SQL Server in procedure parameter and inside procedure we perform some insert or update or delete or even another select with joins etc. And in such type of scenarios, we prefer to send these bulk data in XML format to SQL Server. So in this post we will learn, how we can read xml data in a table. Proceeding with “Read XML data as a table”,  first of all we will create a demo XML variable to perform the select operation on it.

Read XML Data

XML Data

Now we have to read this data in a table to be used further, To get that first have a look on the below select statement;

tbl.col.value('ID[1]', 'smallint'),
Tbl.Col.value('Name[1]', 'varchar(100)'),
Tbl.Col.value('Age[1]', 'smallint'),
Tbl.Col.value('Gender[1]', 'varchar(10)'),
Tbl.Col.value('City[1]', 'varchar(50)'),
Tbl.Col.value('State[1]', 'varchar(50)')

FROM   @xml.nodes('/DataTable/Employee') tbl(col)

This select statement will output as below;

Read XML Data

XML as Table

Now we can put … More

Nested Loop, Merge and Hash Joins in SQL Server

In this article, i will introduce the internal join techniques, which sql server uses to perform various joins internally also known as Nested Loop, Merge and Hash Joins. These types are not directly exposed to us but we can use them as a query hint. These are also known as physical joins in SQL Server. So lets explore this topic  together “Nested Loop, Merge and Hash Joins in SQL Server”.

In our queries, simply we write as below;

From AdventureWorks2012 Database:

USE AdventureWorks2012

SELECT e.[BusinessEntityID]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]

Now its a job of sql server to create an appropriate plan for the query, execute it and return the result set to the caller. SQL Server has multiple components to perform this series of tasks including query parsing, creating query tree, creating binary plan and after … More

Rebuild indexes dynamically for all databases having fragmentation level more than defined level

Rebuild indexes dynamically for all databases

In this article we are going to learn how we can rebuild indexes for all the databases having fragmentation level more than defined level. Fragmentation level to rebuild the indexes may vary databases to database and In our example we are assuming it as 20%. Which can be suitable for most database servers.
We will also log the details of the indexes before rebuilding them and after rebuilding them in a table to get any report in near future.
And we will exclude all those indexes which have page counts less than 8, i.e. the table is too small and can fit in various different locations intentionally by DB engine.

Log Table Script:

CREATE TABLE MyDB.dbo.cmn_RebuildIndexesLog
 in_BatchNo INT,
 in_DBId INT,
 vc_DBName VARCHAR(256),
 vc_SchemaName VARCHAR(256),
 vc_TableName VARCHAR(256),
 vc_IndexName VARCHAR(256),
 num_AvgFragPercent NUMERIC(18,2),
 vc_TypeDesc CHAR(1) --Will store 'B' for before rebuild, 'A' for after rebuild


COUNT(star) vs. COUNT(ColumnReference)

The difference is COUNT(*) includes NULL values while COUNT(column_reference) doesn’t. As an example consider the two below queries for Northwind database that find the number of orders placed by each Northwind customer:
SELECT C.CompanyName, COUNT(*) AS NoOfOrders
FROM dbo.Customers AS C LEFT OUTER JOIN dbo.Orders AS O
ON C.CustomerID = O.CustomerID
GROUP BY C.CompanyName
SELECT C.CompanyName, COUNT(O.OrderId) AS NoOfOrders
FROM dbo.Customers AS C LEFT OUTER JOIN dbo.Orders AS O
ON C.CustomerID = O.CustomerID
GROUP BY C.CompanyName

SQL Server 2012 Multidimensional vs tabular

Which model should you use, Multidimensional vs Tabular?

Well, there is no clear-cut answer, but there are some factors that can make you choose one over the other:

  • If you want to use DAX, you have to use Tabular
  • If you want to use Power View, you have to use Tabular
  • If your dataset is extremely large, go with Multidimensional
  • If you need writeback support, you have to use Multidimensional
  • If you need access to many different external data sources, choose Tabular
  • If you need complex calculations, scoping, and named sets, choose Multidimensional
  • If you need extreme speed and consistently fast query time, choose Tabular
  • If you need Many-to-Many relationships, choose Multidimensional (can be done in Tabular but difficult)
  • If your solution requires complex modeling, choose Multidimensional
  • If you need any of the following features, you must use Multidimensional: Actions, Custom Assemblies, Custom Rollups, Custom Drillthrough Actions (but BIDS Helper

URL Rewriting With ASP.NET and C hash

In this article we will learn how to use URL rewriting to map URLs for better search results and user friendly URLs. This comes in handy when we are working with query strings, but don’t actually want to display the query string values to the user. For instance, we could turn the URL ‘~/Default.aspx?page=Books’ into ‘~/Books’.

First, we will need to create a new ASP.NET Empty Web Site and add a new page to it named ‘Default.aspx’. For this example we will be rewriting two URL’s, a ‘Books’ page and an ‘Magazine’ page.

Let’s build our page with links to those pages. To begin, open Default.aspx and:

  • Add a hyperlink control ans set the text  property to Books.
  • Set the NavigateUrl property to ‘~/Books
  • Add a second hyperlink control and set the text property to Magazine.
  • Set the NavigateUrl property to ‘~/Magazine’.

Now we have two different links that we … More

Common Table Expressions

What is CTE (Common Table Expressions)

A common table expression (CTE) is a temporary result set accessible within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.

CTE does not hold any physical space in database like a derived table. But Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

We can also create Nested CTE to staging our data throughout multiple CTE’s

Below are the scenarios where a CTE can best fit:

  1. Recursive Queries Using Common Table Expressions.
  2. Instead of creating a view for only one query we can choose CTE. As like view it will not take create it’s definition inside metadata.
  3. Making queries readable (For complex and long queries)

Advantages : –

Use of CTE offers improved readability and ease in maintenance of complex queries. The query can be divided into blocks … More

Setting document mode of IE based upon browser mode at run time (C hash) 1

Recently I was working on a project that required me to force the user into IE9 document mode, but ONLY if they were using IE9 compatibility view (CV)  or  IE 10 mode.

The main elements that we need to look at are the Version token and the Trident token. Below are the tokens that you will find in each browser:

                                                          VERSION                   TRIDENT

IE9 StandardMSIE 9.0Trident/5.0
IE9 CVMSIE 7.0Trident/5.0
IE8 StandardMSIE 8.0Trident/4.0
IE8 CVMSIE 7.0Trident/4.0
IE7MSIE 7.0No Trident token
IE6MSIE 6.0No Trident token

As you can see, each browser has a unique combination of these two tokens. We can use this knowledge to now create a function that will tell us what browser mode is in use. My function is shown below:

private string GetIEBrowserMode()


string mode = "";

string userAgent = Request.UserAgent; //entire UA string

string browser