Monthly Archives: October 2014


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


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