Archive for August, 2012

Columnstore Indexes in SQL Server 2012

August 11, 2012 1 comment

This time the new version of SQL Server 2012 a.k.a Denali has introduced a new kind of index i.e. ColumnStore Index, which is very different from the traditional indexes. This new index differs in the way it is created, stores its table contents in specific format and provides fast retrieval of data from the new storage.

–> Before talking about ColumnStore Index, let’s first check and understand what is a ColumnStore?

ColumnStore is a data storage method that uses xVelocity technology based upon Vertipaq engine, which uses a new Columnar storage technique to store data that is highly Compressed and is capable of In-memory Caching and highly parallel data scanning with Aggregation algorithms.

Traditionally, on the other side a RowStore is the traditional and by-default way to store data for each row and then joins all the rows and store them in Data Pages, and is still the same storage mechanism for Heap and Clustered Indexes.

The ColumnStore or Columnar data format does not store data in traditional RowStore fashion, instead the data is grouped and stored as one column at a time in Column Segments.

–> Here is what happens when you try to create a ColumnStore Index on a table:

1. Existing table rows are divided into multiple RowGroups, a Row-Group can contain upto 1 million rows.

2. Each column of a RowGroup is stored in its own Segment and is compressed.

3. The individual compressed Column Segments are added to the ColumnStore.


4. When new rows are inserted or existing ones are updated (in small batches, except BulkLoad) they are added to a separate Delta Store, upto a threshold of 1 million rows.

5. When a Delta-Store reaches its threshold of 1 million rows a separate process Tuple-mover invokes and closes the delta-store, compresses it & stores it into the ColumnStore index.

–> Thus, Columnstore indexes can produce faster results by doing less I/O operations by following:

1. Reading only the required columns, thus less data is read from disk to memory.

2. Heavy Column compression, which reduces the number of bytes that must be read and moved.

3. Advanced query execution technology by processing chunks of columns called batches (1000 rows) in a streamlined manner, further reducing CPU usage.

4. Stored as ColumnStore Object Pool in RAM to cache ColumnStore Index, instead of SQL Buffer Pool (for Pages)

–> Please Note: In SQL Server 2012 ColumnStore indexes has some limitations:

1. A Table (Heap or BTree) can have only one NonClustered ColumnStore Index.

2. Cannot be a Clustered Index.

3. A Table with NonClustered ColumnStore Index becomes readonly and cannot be updated.

4. Check MSDN BoL for more limitations with SQL Server 2012 version, link.


New CHOOSE() and IIF() functions introduced in SQL Server 2012 (Denali)

August 6, 2012 1 comment


1. The CHOOSE() function provides you array like feature where 1st parameter specifies the index and rest parameters are the array elements, this returns the element at the specified index from a list of elements.

SELECT CHOOSE ( 3, 'Apple', 'Mango', 'Banana', 'Kiwi' ) AS Result; -- Banana
SELECT CHOOSE ( 2, 'Manoj', 'Saurabh', 'Andy', 'Dave' ) AS Result; -- Saurabh


2. The IIF() function is a good replacement of CASE statement, it returns either of the two values passed to 2nd and 3rd parameter on evaluation of boolean expression to the 1st parameter.

-- IIF()
DECLARE @x int = 10;
DECLARE @y int = 20;
SELECT IIF ( @x > @y, 'TRUE', 'FALSE' ) AS Result; -- FALSE

-- CASE equivalent
	WHEN @x > @y THEN 'TRUE' 
END AS Result; -- FALSE

Note: IIF() function is internally converted to CASE expression by SQL engine and can be nested upto 10 levels like CASE.

–> Video:

Search a String in Stored Procedure, View, Function or other database object definitions

August 1, 2012 1 comment

Looks like a simple topic and some might wonder why another post as this information is already available in other forums and blogs. But here I’ll go ahead with the topic and talk about an issue that not many people are aware of.

There are times when you want to search a particular string or keyword in various database objects like Stored Procedures, Views, Functions, etc. What you do in that case?
#1. Go to object explorer, open up each object and search the string by scripting it out.
– OR –
#2. Query the system catalog tables or metadata views.

Option #1 is time consuming, slow and very cumbersome.
Option #2 is quick and easy in retrieving the information.

So, we will see in what all ways we can get the required information from our database & which is the best way.

As I said at the top of this post if you search online you will see lot of solutions, but all those solution have some minor hidden issues which we will see here.

We will check below how we can search the “[HumanResources].[Employee]” table getting used in what all objects. (Here we are not checking the dependency on objects, but just checking any text which can be an object, SQL reserved keyword or user specified string to build a SQL query or a T-SQL script.)

–> 1. The first way comes in people mind is to search with INFORMATION_SCHEMA.ROUTINES View:

USE [AdventureWorks2012]

where ROUTINE_DEFINITION like N'%[[]HumanResources].[[]Employee]%'
6 Records returned with following Stored Procedure names:


–> 2. The second way is to search in sys.syscomments View:

		WHEN OBJECTPROPERTY(id, 'IsProcedure') = 1      THEN 'Stored Procedure'
		WHEN OBJECTPROPERTY(id, 'IsView') = 1			THEN 'View'
		WHEN OBJECTPROPERTY(id, 'IsScalarFunction') = 1 THEN 'Scalar Function'
		WHEN OBJECTPROPERTY(id, 'IsTableFunction') = 1  THEN 'Table Valued Function'
		WHEN OBJECTPROPERTY(id, 'IsInlineFunction') = 1 THEN 'Inline Function'
		WHEN OBJECTPROPERTY(id, 'IsTrigger') = 1        THEN 'Trigger'
	END AS ObjType, 
from sys.syscomments 
where text like N'%[[]HumanResources].[[]Employee]%'

This query returns 12 reocrds, with 1 Table Valued Function, 5 SPs, 1 Trigger & 5 Views.

–> 3. The third way is to search in sys.sql_modules View:

		WHEN OBJECTPROPERTY(object_id, 'IsProcedure') = 1      THEN 'Stored Procedure'
		WHEN OBJECTPROPERTY(object_id, 'IsView') = 1			THEN 'View'
		WHEN OBJECTPROPERTY(object_id, 'IsScalarFunction') = 1 THEN 'Scalar Function'
		WHEN OBJECTPROPERTY(object_id, 'IsTableFunction') = 1  THEN 'Table Valued Function'
		WHEN OBJECTPROPERTY(object_id, 'IsInlineFunction') = 1 THEN 'Inline Function'
		WHEN OBJECTPROPERTY(object_id, 'IsTrigger') = 1        THEN 'Trigger'
	END AS ObjType, 
from sys.sql_modules 
where definition like N'%[[]HumanResources].[[]Employee]%'

Same as above, returns 12 reocrds.

No issue till now, great!!!

Now, let’s check this scenario: I want to get all Database Objects containing the “[BikeFrame]” column name.

–> 1. When I choose to search with INFORMATION_SCHEMA.ROUTINES view it does not give any result:

where ROUTINE_DEFINITION like N'%[[]BikeFrame]%'

Because this View stores only first 4000 characters in ROUTINE_DEFINITION column of NVARCHAR datatype. So the query gets truncated before the column name occurs.

–> 2. When I go with sys.syscomments view it also gives me nothing:

select * 
from sys.syscomments 
where text like N'%[[]BikeFrame]%'

Here this View stores the query-text in “text” column of NVARCHAR type with size of 4000 characters. The query also gets truncated here but it stores the query in chunks thus the rest part goes to the next rows.

So it should return the row with matching chunk from the matching row, but it does not, why? Because in this typical case the column name is getting splitted in different rows. Check by running this query:

select id, colid, ctext, text
from sys.syscomments 
where id = 2059154381
id		colid	ctext			text
2059154381	1	0x0D000A00430052004...	CREATE VIEW [Production].[vProductModelCatalog...
2059154381	2	0x65004600720061006...	eFrame] ,[CatalogDescription].value(N'declare nam...

Here, you can see the second half text “eFrame]” of the column name is placed at beginning of the 2nd row.
And if you expand the “text” column you will see the first half text “[Bik” of the column name at the end of 1st row.

–> 3. When I go with sys.sql_modules view it gives me expected result:

select * 
from sys.sql_modules 
where definition like N'%[[]BikeFrame]%'

Because this View stores the query text in “definition” column of NVARHCAR(MAX) type with length of 2GB.

So, its better to use sys.sql_modules view to do all metadata based searches instead of INFORMATION_SCHEMA.ROUTINES & sys.syscomments views.

Categories: SQL Tips Tags: