Monthly Archives: September 2014


Read and compare XML nodes dynamically with unknown elements 1

In this article, I will share a practical example from my experience. In one of my project, I had a requirement of dynamic data comparison between two entities and the number of attributes were dynamic and can be added any time by end user and removed too. Let me explain it with an example that how we can “Read XML with unknown elements”;

Problem explanation with example:-

We have employees as an entity with many attributes like “salary”, “employee group”, “employment type”, “plan”, “Is owns a Car”, “Gender”, “city” and so on….. and end user wants to create policies with some combination of attributes defined to get list of employees satisfying those criterias defined in the policy.

Policy name – Test_Policy

Attributes for policy – Regular employee, plan A, Having Car, Belongs to New Delhi, India

Above is just an example. It may be in next policy end user can … More


Reverse the string with a delimiter (Recursive CTE) 1

In this article, we are going to learn how we can “Reverse string in sql server” with some specified delimiter e.g, based on space or comma or tilde or semi-colon etc;

If you will input “A B C”, the output should be as “C B A”

Solution:

Lets assume space as a delimiter in this example and now to achieve our goal, we will use a recursive CTE(Common table expression). Just have a look on below code and then we will discuss in detail;


DECLARE @VAR VARCHAR(100)

SET @VAR = 'GOPAL KRISHNA RANJAN'

DECLARE @StringToReturn VARCHAR(1000)

SET @StringToReturn= ''

;

WITH CTE AS

(

SELECT CAST(LEFT(@VAR,CHARINDEX(' ', @VAR + ' ')-1) AS VARCHAR(100)) VARS, STUFF(@VAR,1,CHARINDEX(' ', @VAR + ' '),'') VAR1

,ROW_NUMBER() OVER(ORDER BY @VAR) SEQ

UNION ALL

SELECT CAST(LEFT(VAR1,CHARINDEX(' ',VAR1 + ' ')-1) AS VARCHAR(100)), STUFF(VAR1,1,CHARINDEX(' ',VAR1 + ' '),'') VAR2,SEQ + 1 FROM CTE

WHERE VAR1 > 
More

Row mapping and generating equivalent records for given condition in sql server

In this article, i will demonstrate a solution for dynamic row mapping problem in sql server. We will see “row mapping for given condition” with examples and in various way. One of my colleague asked me a question as below;

“I have a table with Male and Female genders and want an output by mapping each Male with a Female and vice versa and in case any Gender has less number of records than another, “NA” should be displayed with opposite Gender name.”

i.e. If i have a table named #Players with columns Name and Gender. Now if table has 3 Males and 2 Females named Ram, Shyam, Amit as males and Sita, Radha as Females, output should be as below;

Output:

NameGender
RamMale
SitaFemale
ShyamMale
RadhaFemale
AmitMale
Not AvailableFemale

Solution :- 1

In this problem we are not going to use any … More


Generate Defined number of rows dynamically (SET based approach)

In this article , we will generate as much number of rows as needed for each row of given table dynamically. We have a table named #Employee with two columns “EmpName” and “RowsToGenerate”. Lets explore “Generate rows dynamically” topic in with examples and demos.

Generate Rows dynamically as per given count:

Below is the structure of the table #Employee


CREATE TABLE #Employee

(

EmpName VARCHAR(100),

RowsToGenerate INT

)

Now put some demo values in this table as below;


INSERT INTO #Employee(EmpName, RowsToGenerate)

VALUES('Emp1', 5)

,('Emp2', 10)

,('Emp3', 4)

,('Emp4', 1)

,('Emp5', 2)

,('Emp6', 8)

,('Emp7', 1)

,('Emp8', 0)

,('Emp9', 0)

,('Emp10', 0)

Trick

I will create a CTE (Common table expression) with serial numbers to perform a join on #Employee table and this created CTE to generate the rows as much as we want as per column value “RowsToGenerate”. In case of 0, it will not generate any row.

Have … More


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;


SELECT
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
GO

SELECT e.[BusinessEntityID]
,p.[Title]
,p.[FirstName]
,p.[MiddleName]
,p.[LastName]
,p.[Suffix]
,e.[JobTitle]
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
)

More

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:
WRONG:
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
ORDER BY NoOfOrders
RIGHT:
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
ORDER BY NoOfOrders
More