Feeds:
Posts
Comments

Archive for the ‘Database Features’ Category

HASH index (NONCLUSTERED) is a new type of index introduced in SQL Server 2014. It is only supported by memory optimized table. Recently, I was working on a memory optimized table and I came across in a situation where I had to find all the hash indexes available in the database. As usual I got it from a system view (sys.indexes). It is nothing new but the story did not end here because I found a shorter way to get the list of non-clustered hash indexes.
Given below is script that can give you the list of all hash indexes available in any particular database.

USE hkNorthwind
GO
SELECT
  object_schema_name([object_id]) As [Schema Name]
, object_name([object_id]) As [Table Name]
, [name] As [Index Name]
, [type_desc] As [Index Description]
FROM
sys.hash_indexes
--OUTPUT

list of hash indexes.1.1

Read Full Post »

Columnstore index is one of the nice features introduced in SQL Server 2012. It improves the performance more times than the normal query but on the other hand it has some limitations as well, and one of the limitation is that you cannot create a CLUSTERED Columnstore index.

Let me demonstrate it in SQL Server 2012.

USE tempdb
GO
CREATE TABLE tbl_Product_Master
(
 [Product ID] INT,
 [Product Name] NVARCHAR(100),
 [Model] INT
)
GO
CREATE CLUSTERED COLUMNSTORE INDEX
[IX_tbl_Product_Master_ColumnStore]
ON [tbl_Product_Master] ([Product ID])
GO
--OUTPUT

Msg 35338, Level 15, State 1, Line 3
Clustered columnstore index is not supported.

Ooopps…… I am unable to create it in SQL Server 2012.

The above limitation has been eliminated in SQL Server 2014 and you can add the CLUSTERED COLUMNSTORE INDEX in the table.

Let me demonstrate the same example in SQL Server 2014.


USE tempdb
GO
CREATE TABLE tbl_Product_Master
(
 [Product ID] INT,
 [Product Name] NVARCHAR(100),
 [Model] INT
)
GO

CREATE CLUSTERED COLUMNSTORE INDEX
[IX_tbl_Product_Master_ColumnStore]
ON [tbl_Product_Master]
GO
--OUTPUT

Command(s) completed successfully.

Lets check whether the index has been created successfully or not.

columnstoreindexcusteredindex1.1

The result is evident above, in SQL 2014.

Let me know if you explore the other nice enhancements of SQL Server 2014.

Read Full Post »