Yearly Archives: 2014


Speed up MS SQL Server Reporting Services SSRS on First Run

On first run of SSRS, report rendering is very slow and in this post we will explore “how to speed up SSRS on first run”. The reason behind is that we have to wait for the application pool to spin up. During each start up, the SSRS web service reads and decrypts the rsreportserver.config file, it has to physically open up a socket connection between the two servers since the connection pool is empty, log into the database instance, etc. Also the web service has to make RPC calls into the Windows Service to get the encryption keys. There is an idle timeout value which forces the application to shut down after 20 minutes by default. We can tweak this timeout setting to have the application always up and running.

The solution to resolve this issue is :

You can modify the “RecycleTime” parameter in RSReportServer configuration file for SQL … More


Allow only alphanumeric characters in a column 2

One of my colleague asked me a question that how to “allow only alphanumeric characters” in a column. Below is the exact question:

How can i stop accepting any non-alphanumeric characters during DML operations in a column of a table?

To achieve this, we can follow below steps. There may be more different ways other than these;

  1. Create a table
  2. Create a scalar function which returns true or false by checking non-alphanumeric characters (Using scalar function as an example so that in case of complex logic it can be implemented inside this function.)
  3. Create a check constraint on table and call this created function to check that column has only alphanumeric values

Some other way, we can also create an “Instead Of Trigger” and write a logic to check alphanumeric values for that column which I will not cover in this post.

Now let’s brief me each step mentioned above:… More


Compare tables data on different databases with same structure 2

Last night, i was asked to create a script to compare a bunch of tables data from QA to Prod server. Be sure here that i am talking about the comparison of data and nor schema. Some of us also uses some tools to achieve the same and i have no question about their working ability and method. All these tools paid or even free can do much better job than what i am about to share here. But question is still here that in some cases either we don’t want to use these tools or not aware of them.

Again i am saying that in this article i am not raising any question about any tool which does the comparison of table data.

Now just have a look on the below script and at the bottom i will share the description of this script. You can also find the … More


Index Join in sql server 1

Lets explore the topic “Index Join in sql server” starting with “what is index join?”. Index join is a technique which uses an index intersection with two or more indexes to fulfill a query completely. If a table has multiple small index instead of a large covering index, the optimizer can choose index intersection on these small indexes to satisfy a query. In index intersection, it has to perform logical reads on all the indexes being used to serve the query.

If the querying table is large and  it has multiple narrow indexes instead of one wide index, index intersection can be used by sql server to fetch the entire data to be returned.

Example;

Create a table as below;


CREATE TABLE DemoTable
(
EmpID INT,
EmpName VARCHAR(100),
EmpAddress VARCHAR(500),
EmpContact VARCHAR(15),
EmpPinCode VARCHAR(15)
)

Now we have created a table and as per the next step, we have to … More


Bookmark Lookup in sql server 3

What is Bookmark Lookup?

If all the columns required by the query are not available in the nonclustered index itself, a lookup is required to base table to pick those columns which are not part of the nonclustered index being used to retrieve the data. Lookup fetches the corresponding data row from the table by following the row locator value from the index row. It requires extra logical read on data page excluding the logical read on the index page.

Types of lookup

This lookup may be a RID lookup against a clustered index or a key lookup against a  heap.

RID Lookup

If base table does not have any clustered index created on it, a Row ID of heap, will be used as a row locator and if any query which will require a lookup using this heap row id row locator will perform a lookup, the lookup will … More


What is Computed Column in sql server 4

Computed column is a virtual column in sql server, which is not stored physically in the table. It’s data is not materialized in the database and gets calculated during query execution on that column.

It takes it’s value from an expression and that expression can contain other columns to calculate a new value for the computed column.

Can it persist physically in the belonging table?

Yes, and to make it persist physically in table, just mark it as PERSISTED. By default it does not persist physically. In this case the data will be materialized in the table and will not be computed during query execution.

Without physical persistence:


CREATE TABLE TestComputedColumn
(
ID INT IDENTITY(1,1),
NAME VARCHAR(100),
SALARY NUMERIC(18,2),
TAX NUMERIC(18,2),
NETSALARY AS SALARY - TAX
)

With physical persistence:

CREATE TABLE TestComputedColumn
(
ID INT IDENTITY(1,1),
NAME VARCHAR(100),
SALARY NUMERIC(18,2),
TAX NUMERIC(18,2),
NETSALARY AS SALARY - TAX PERSISTED
)
More

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