Category : SQL Server


Locking, Blocking and Deadlocking in sql server 1

Lock vs Block vs Deadlock

Have you ever asked to differentiate lock, block and deadlock in sql server during your interview session? In this article we are going to differentiate these frequently confused terms – Lock, Block and Deadlock. So lets have a look on “Lock vs Block vs Deadlock”

These terms sound the same but are different in their meanings. Have a look on below definition to understand it.

Lock

When any session needs access to a piece of data from Database, a lock of some type is placed on that data to maintain the isolation property of database. Locking is different from blocking.

Block

When a session needs to wait for a resource being locked by another session, this process is called as blocking. e.g. If session “A” is waiting for a resource “R” being used by session “B”, you can say that session “B” is blocking session … More


Apply vs Join in sql server 3

What is Apply in SQL Server?

In this topic “Apply vs Join”, we will discuss about the APPLY operator and its usage scenarios. Lot of people got confused “where they can use Apply and what is it”. They afraid about the performance and usage scenario also. In this article we are going to remove all these scary images of Apply from your mind.

So let’s start with ABCD of Apply and try to cover some advanced topics.

The primary use of Apply operator in sql server is to invoke a table valued function (TVF) but it can also be used in some other important scenarios. We will discuss the importance of Apply operator in brief in this article and would also compare this with Join operator. Invoking table valued function (TVF) is for what Microsoft introduced Cross Apply and Outer Apply primarily, as per MSDN.

Apply Operator executes for each … More


Indexed Views in SQL Server

Before having a discussion on “Indexed views in SQL Server”, let’s talk about simple view first.

A view is a virtual table which contains a SELECT statement to be executed when this view is called. It does not store any physical data and processes the data at run time. This can be used as a table and security principals can be applied on it. So instead of a table, a view can be exposed to the user. Using views, we can also take advantage of exposing only some columns of the given table instead of all columns, which increases the security. But in case of a view, each time it’s called, query inside the view gets executed.

Now come to the point of discussion of this post, an indexed view is a type of view which can materialize it’s data and instead of getting data by querying the underlying tables … More


SQL Server Management Studio shortcuts

Column and block text selection :

Using SHIFT to Select Text –

In this post we will have a look on “SQL Server Shortcut keys”. with some other details. It is well known that using the SHIFT key you can perform normal text selection in SSMS.  If you put your cursor to the left of “Person.Address” and hold the SHIFT key and then put your cursor at the end of “Person.CountryRegion” it will select the first three lines of code as shown below.

SQL Server Management Studio shortcuts

Using SHIFT+ALT to Select Columns

If you would like to select columns or blocks then Microsoft SQL Server offers a solution for you. You can use the key shortcut SHIFT+ALT as described in the following steps. Please note that this feature works using SSMS for SQL Server 2008 and up.

Place your cursor to the left of “[Person].[Address]”, press SHIFT+ALT then click at the end of “Person” … 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