SQL Server 2012 | SQL Server Portal

Posts Tagged ‘SQL Server 2012’

SQL SERVER 2012 – How to create a customized snippet

Posted in Solutions, SQL SERVER, tagged raresql, Snippet Manager, SQL, SQL Server, SQL Server 2012, SQL SERVER 2012 - How to create a customized sinippet on May 13, 2013| 1 Comment »

In my earlier article, I wrote about the new feature of SQL Server 2012 namely Insert Snippet. This is quite a handy feature which can save a lot of your time. This feature is shipped with a lot of standard objects snippet with the customization options.

But there were two big questions in front of me. 1) Why do we need to customize it? 2) How can this save our time ?
Answer :The reason for customization is that, each and every company follows some standards to create standard object (Table, view etc.) of SQL Server. For example the name of the table should start with tbl_ or there might be some additional fixed column with some default values in each table etc etc. Therefore, once you customize the snippet as per your requirements, then you can use it every time.

Prerequisite: How to insert the Snippet in SQL Server 2012.

Solution :
Let me explain, how to customize the standard object table snippet Step by Step :

Step 1 :

First of all, I will take you to the path where snippet of the standard objects resides in the SQL Server 2012.

To find the path you need to go to Tools and select Code Snippets Manager as shown in the picture below.

Once you open the Code Snippets Manager, select the appropriate object and you can see the path of that particular object as shown in the picture below.

Step 2 :
As you can see the path of table snippet in the above picture, the next step is to browse the table path and find the standard table snippet namely Create Table.snippet.

Step 3 :

Now, you are in the standard table snippet folder, so you need to make a copy of the standard table object namely “Create Table.snippet” and rename it to “Create Sample Table.snippet” as shown in the picture below.

Step 4 :

Open the newly created file Create Sample Table.snippet, first and foremost, change the title as highlighted in the given below picture.

Step 5 :

Once you change the title copy the selected area as shown in the picture below and paste twice after <Literal> because we need to add two additional columns of the table in the snippet.

Step 6 :

Lets rename the column name and data type of the columns ([Creation_Datetime] & [Created_By] having data type datetime and nvarchar(50) respectively as shown in the picture below.

Step 7 :

Once you create the additional column, do not close the file, just scroll down a little bit in the same file and copy the selected area ($Column2$ $datatpe2$) and paste it twice and then rename it to $column3$ & 4$ and $datatype 3$ & 4$ respectively as shown in the picture below.

Step 8 :

Once all the changes are done in Create Sample Table.snippet save and close the file and open SQL Server Management Studio.

Step 9 :

Lets insert the customized table snippet in Query window as shown below.

customizesnippet1.8

customizesnippet1.9

Read Full Post »

SQL SERVER – How to convert Gregorian dates to Hijri date with Formatting

Posted in Solutions, SQL SERVER, tagged Format() Function in SQL Server 2012, raresql, SQL, SQL Server, SQL SERVER - How to convert Gregorian dates to Hijri date with Formatting, SQL Server 2012 on May 8, 2013| 4 Comments »

How to convert Gregorian date format to simple Hijri date format? Doesn’t it sound a very simple question? Yes, because in the earlier versions of SQL Server you could convert Gregorian dates to simple Hijri date format (dd mon yyyy hh:mi:ss:mmmAM). But if you need to convert Gregorian dates to any specific Hijri date format, then comes the problem. Basically, the earlier version of SQL Server supports very limited type of Hijri date formats but in SQL Server 2012, you can convert Gregorian dates to ANY Hijri date format using FORMAT function.

Let me convert it using different methods :

Method 1 : Using Convert Function

--This script is compatible with SQL Server 2005 and above.
--This Method can convert into very limited format of Hijri dates.
DECLARE @DateTime AS DATETIME
SET @DateTime=GETDATE()
SELECT @DateTime AS [Gregorian Date]
, CONVERT(VARCHAR(11),@DateTime,131) AS [Gregorian date to Hijri date]
GO
--OUTPUT

Method 2 : Using Format Function

--This script is compatible with SQL Server 2012 and above.
DECLARE @DateTime AS DATETIME
SET @DateTime=GETDATE()

SELECT @DateTime AS [Gregorian Date]
,FORMAT(@DateTime,'dd-MM-yyyy','ar')
AS [Gregorian date to Hijri date]

UNION ALL

SELECT @DateTime AS [Gregorian Date]
,FORMAT(@DateTime,'dd/MM/yyyy','ar')
AS [Gregorian date to Hijri date]

UNION ALL

SELECT @DateTime AS [Gregorian Date]
,FORMAT(@DateTime,'yyyy-MM-dd','ar')
AS [Gregorian date to Hijri date]

UNION ALL

SELECT @DateTime AS [Gregorian Date]
,FORMAT(@DateTime,'dddd/MMMM/yyyy','ar')
AS [Gregorian date to Hijri date]

UNION ALL

SELECT @DateTime AS [Gregorian Date]
,FORMAT(@DateTime,'dd-MM-yyyy','ar')
AS [Gregorian date to Hijri date]
GO
--OUTPUT

Let me know if you know any better way.

Read Full Post »

SQL SERVER 2012 – Semantic Search – Implementation– Part-2

Posted in Features, SQL SERVER, tagged raresql, SEMANTICKEYPHRASETABLE, SQL, SQL Server, SQL Server 2012, SQL SERVER 2012 – Semantic Search – Implementation– Part-2 on May 6, 2013| 5 Comments »

In Part 1 of Semantic Search Implementation that I published yesterday, I wrote about how to install and configure semantic search in SQL Server 2012. Today in Part 2, we will learn how to implement semantic search and will demonstrate how beneficial it is.

Prerequisite :

Given below are the two articles that you should read to have clear understanding about Semantic Search Implementation.

Lets Implement it step by step.

Step 1: Create database

USE master
GO
CREATE DATABASE [SampleDB]
ON PRIMARY
( NAME = N'SampleDB',
FILENAME = N'C:\DATA\SampleDB.mdf'
),
FILEGROUP SampleFileGroup CONTAINS FILESTREAM
(NAME = SampleFileTableFS,
FILENAME='C:\Data\SampleFileTable'
)
LOG ON
( NAME = N'SampleDB_log',
FILENAME = N'C:\DATA\SampleDB_log.ldf'
)
WITH FILESTREAM
( NON_TRANSACTED_ACCESS = FULL,
DIRECTORY_NAME = N'SampleFileTableDB'
)
GO

Step 2: Create FileTable

USE SampleDB
GO
CREATE TABLE CVBank AS FileTable
WITH
(
FileTable_Directory = 'CVFileTable_Dir'
);
GO

Step 3: Populate data into FileTable
In this step, we will insert few Cvs in the file table.

USE SampleDB
GO
INSERT INTO [dbo].[CVBank] ([name],[file_stream])
SELECT'Muhammad Imran -CV.doc',
* FROM OPENROWSET
(
BULK N'C:\Cv\Muhammad Imran -CV.doc', SINGLE_BLOB
) AS FileData
GO

INSERT INTO [dbo].[CVBank] ([name],[file_stream])
SELECT 'Salman Tahir -CV.doc',
* FROM OPENROWSET
(
BULK N'C:\Cv\Salman Tahir -CV.doc', SINGLE_BLOB
) AS FileData
GO

INSERT INTO [dbo].[CVBank] ([name],[file_stream])
SELECT 'Syed Kamran Ali -CV.doc',
* FROM OPENROWSET
(
BULK N'C:\Cv\Syed Kamran Ali -CV.doc', SINGLE_BLOB
) AS FileData
GO

Step 4: Create full text search on FileTable

Right click on the file table point to full-Text index then select Define Full-Text Index… Further details to create Full Text Index is available here

Now, we are prepared to use semantic search. Lets create few examples.

Example 1: Search Sharepoint Developer CVs
As I mentioned above that, I populated some CVs in the file table, now it is time to search Cvs with certain criteria using semantic search.
In this example, I will search candidates having knowledge of sharepoint.

USE SampleDB
GO
SELECT
D.name
,KEYP_TBL.score
,KEYP_TBL.Keyphrase
FROM dbo.CVBank D
INNER JOIN SEMANTICKEYPHRASETABLE
(
dbo.CVBank,(name,file_stream)
) AS KEYP_TBL
ON D.path_locator = KEYP_TBL.document_key
WHERE KEYP_TBL.keyphrase like '%sharepoint%'
ORDER BY KEYP_TBL.score DESC
--OUTPUT

Example 2: Search SQL Developer CVs
In this example, I will search candidates having knowledge of SQL.

USE SampleDB
GO
SELECT
D.name
,KEYP_TBL.score
,KEYP_TBL.Keyphrase
FROM dbo.CVBank D
INNER JOIN SEMANTICKEYPHRASETABLE
(
dbo.CVBank,(name,file_stream)
) AS KEYP_TBL
ON D.path_locator = KEYP_TBL.document_key
WHERE KEYP_TBL.keyphrase like '%sql%'
ORDER BY KEYP_TBL.score DESC
--OUTPUT

Conclusion :
Semantic Search is very handy if you need to develop some solution on the basis of unstructured document. Shall post another nice feature of semantic search in my upcoming post.

Read Full Post »

SQL SERVER 2012 – Semantic Search – Install and configure – Part-1

Posted in Features, SQL SERVER, tagged raresql, Semantic Search, sp_fulltext_semantic_register_language_statistics_db, SQL, SQL Server, SQL Server 2012, SQL SERVER 2012 - Semantic Search - Install and configure - Part-1, sys.fulltext_semantic_language_statistics_database on May 5, 2013| 1 Comment »

In the earlier versions of SQL Server, it was very difficult to view the unstructured documents placed in the SQL Server. But in SQL Server 2012, with the solution shipped namely Semantic Search not only can you easily look inside in the unstructured documents but also you can create phrase table to find key phrases, similar documents, related documents as well. This feature gave boost to another newly introduced feature namely File Table in SQL Server 2012. You must install and configure Semantic Search before you use it.
Let’s install / configure it Step by Step.

Step 1:
In order to install Semantic Search, first of all you need to check whether FULL TEXT SEARCH feature is installed in your SQL Server or not. Because Semantic search feature is based on Full text search feature.
Given below is the script to check this.

USE master
GO
SELECT SERVERPROPERTY('IsFullTextInstalled') AS [Result]
GO
--OUTPUT

Result
1

(1 row(s) affected)

Step 2:
If result of step 1 is 1 then you should not do anything in Step 2. But if the result is 0, you must run the SQL Server setup again, go to Feature Selection option, select Full-Text and Semantic Extractions for search and install this feature (as shown in the picture below).

Step 3:
Once you install the Full-Text and Semantic Extractions for search feature, you need to check whether Semantic Language Statistics Database (One of the core dependency of Semantic searches) is installed or not. Given below is the script to check.

USE master
GO
SELECT * FROM
sys.fulltext_semantic_language_statistics_database
GO
--OUTPUT

Ooooppss……… as you can see, the output says blank; it means semantic language statistics database is not yet installed. So you need to install it.

Step 4:

To install semantic language statistics database, you need to go to SQL Server installation media and browse the given below folder and run SemanticLanguageDatabase.msi

• For x86 : …\x86\Setup\SemanticLanguageDatabase.msi
• For x64 : …\x64\Setup\SemanticLanguageDatabase.msi

Given below is the screen image.

Step 5:

Once you execute the above SemanticLanguageDatabase.msi and install it properly, it will give you two database files (semanticsDB, semanticsdb_log) in a location (C:\Program Files\Microsoft Semantic Language Database), in case you install it in the default location. Now you have database files, just attach these files in your SQL Server.

Step 6:
Once you attach these database files in your SQL Server, then you have to register semantic language database. Given below is the script.

EXEC sp_fulltext_semantic_register_language_statistics_db
@dbname = N'semanticsdb'
GO

Step 7:
Again, you need to check whether the semantic language statistics database is installed or not. Given below is the script.

SELECT * FROM
sys.fulltext_semantic_language_statistics_database
GO
--OUTPUT

Now, you can see that the semantic language statistics database is installed.

In my next article, I will discuss how to implement semantic search on file tables and make your life easier.

Reference : MSDN

Read Full Post »

SQL SERVER 2012 – Fix – Error :11513 – The metadata could not be determined because statement contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.

Posted in Errors, SQL SERVER, tagged Msg 11513 Level 16 State 1, raresql, SQL, SQL Server, SQL Server 2012, SQL SERVER 2012 - Fix - Error :11513 - The metadata could not be determined because statement contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set. on May 4, 2013| 1 Comment »

Dynamic SQL queries are one of my favorite queries when it comes to handling multiple conditions from a single query. But it should be handled very carefully. The error we will discuss in this article is related to dynamic SQL and sp_describe_first_result_set, a new system stored procedure shipped with SQL Server 2012. This error message is available in SQL Server 2012 and above.

Let’s discuss this in detail:
Message Number: 11513

Severity : 16

Error Message: The metadata could not be determined because statement ‘%.*ls’ contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.

Error Generation:
Let me create a sample to demonstrate this error. In this sample, I need to dynamically select few columns from [HumanResources].[vEmployee] (View).

USE AdventureWorks2012
GO
DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @SelectClause AS NVARCHAR(MAX)
SET @SelectClause='TOP 10 [FirstName],[EmailAddress]'
SET @SQL='SELECT '+ @SelectClause +
' FROM [HumanResources].[vEmployee]'
EXEC (@SQL)
GO
--OUTPUT

You can see that the above script executed successfully. Now I would like to view the metadata of the above dynamic SQL query. Given below is the script to view the metadata using sp_describe_first_result_set

sp_describe_first_result_set @tsql =N'
DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @SelectClause AS NVARCHAR(MAX)
SET @SelectClause=N''TOP 10 [FirstName],[EmailAddress]''
SET @SQL=N''SELECT '''''' + @SelectClause
+ '''''' FROM [HumanResources].[vEmployee] ''
EXEC(@SQL)'
GO
--OUTPUT

Msg 11513, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement ‘EXEC(@SQL)’ contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.

Ooopps…… I am unable to execute it.

Resolution:
The reason behind this error is, this query is developed using dynamic SQL and to view the metadata of dynamic SQL query, we must use the result set clause with dynamic SQL query.

Given below is the correct script.

sp_describe_first_result_set @tsql =N'
DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @SelectClause AS NVARCHAR(MAX)
SET @SelectClause=N''TOP 10 [FirstName],[EmailAddress]''
SET @SQL=N''SELECT '''''' + @SelectClause
+ '''''' FROM [HumanResources].[vEmployee] ''

EXEC(@SQL) WITH RESULT SETS
(([FirstName] varchar(50)
,[EmailAddress] nvarchar(50)));'
GO
--OUTPUT

Conclusion :
Remember, whenever you use dynamic SQL query and would like to view the metadata of that query using sp_describe_first_result_set, always use result set clause.

Read Full Post »

SQL SERVER 2012 – Insert Snippet …

Posted in SQL SERVER, Tips, tagged raresql, snippet picker, SQL, SQL Server, SQL Server 2012, SQL SERVER 2012 - Insert Snippet ... on May 3, 2013| 4 Comments »

SQL Server 2012 came up with many new features and enhancements. Insert Snippet is one of the new features introduced in it. This feature reduces the development time and increases productivity. How ? It basically generates the code snippet for the most frequently used objects in SQL Server instantly (with few clicks or key strokes). But it is not limited to the frequently used objects, custom code snippet template can also be added in it and can generate it at any point of time, to make life easier. How to create custom code snippet will be demonstrated in my up coming article.

Given below are the object’s code snippet available in SQL Server as a standard template :

Function
Index
Login
Role
Schema
Stored Procedure
Synonym
Table
Trigger
User
User Defined Data Type
User Defined Table Type
User Defined Type
View

Let me insert a Create Table code snippet step by step using Insert snippet .

Step 1:

Open a new query window in the Database Engine and place the cursor where the code snippet has to be inserted.

Step 2:

In this step, launch snippet picker to insert Snippet. It can be done using three different methods. Given below are the details of these methods.

Method 1:

Press CTRL+K, CTRL+X to launch snippet picker.

Method 2:

Go to the Edit Menu then point to IntelliSense and then select Insert Snippet to launch snippet picker as shown in the picture below .

Method 3:

Right click and select Insert Snippet to launch snippet picker as shown in the picture below.

Step 3:

Once the snippet picker is launched, select table from the list as shown in the picture below.

Step 4:

After selecting the Table press TAB or Enter key to proceed as shown in the picture below.

Step 5:

After selecting the Create Table script as show in the above picture then again press TAB or Enter key to generate the code snippet as shown below.

Step 6:

Notice that the create table code snippet in the above image has been generated. Now just change the highlighted area to create desired customized table. Additional columns as well can be added.

Conclusion :

Insert Snippet is a very nice feature introduced in SQL Server 2012. Time saving tool. The example discussed above took less than 10 seconds to create it. Also it is not limited to create table code snippet alone, any object code snippet can be created in less than 10 seconds.

Will post how to create the customized code snippet in my upcoming post.

Read Full Post »

SQL SERVER – How to implement LIMIT with performance

Posted in Solutions, SQL SERVER, tagged Fetch Next, Offset Rows, SQL, SQL Server, SQL SERVER - How to implement LIMIT with performance, SQL Server 2012 on April 30, 2013| Leave a Comment »

“How to implement LIMIT in SQL Server?” (How to pick up certain rows after certain rows in SQL SERVER), I came across this question many times in multiple forums and the common solution is, to use common table expression and ROW_NUMBER. However, this is an old approach. A new approach has been introduced in SQL Server 2012 using OFFSET ROWS FETCH NEXT ROWS.

Let me create a sample script to explain both approaches.

USE AdventureWorks2012;
GO
Select
FirstName
,Jobtitle
From [HumanResources].[vEmployee]
Order By FirstName,Jobtitle
--OUTPUT

Lets discuss both approaches with examples :

Old Approach:
In this approach, we will pick 10 rows after row number 10 (meaning row number 11 till 20) from [HumanResources].[vEmployee]. Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012;
GO
;With CTE_Employee_List As
(
Select ROW_NUMBER() Over
(Order By[FirstName],[JobTitle]) As [Row Number],
FirstName
,Jobtitle
From [HumanResources].[vEmployee]
)
Select FirstName,Jobtitle
From CTE_Employee_List
Where [Row Number] Between 11 And 20;
GO
--OUTPUT

New Approach:
In the new approach too, we will select 10 rows after row number 10 (meaning row number 11 till 20) from [HumanResources].[vEmployee] and the script goes as follows :

--This script is compatible with SQL Server 2012 and above.
USE AdventureWorks2012;
GO
Select
FirstName
,Jobtitle
From [HumanResources].[vEmployee]
Order By FirstName,Jobtitle
Offset 10 Rows
Fetch Next 50 Rows Only;
--OUTPUT

As we can see, the output is exactly same but a lot of other parameters are different that can impact the performance.
Given below is the summary of both approaches.

S.No	Type	Old Approach	New Approach
1	Common Table Expression	Yes (required)	N/A
2	ROW_NUMBER	Yes (required)	N/A
3	CPU time	15 ms	15 ms
4	Elapsed time	1387 ms	375 ms
5	Query Cost (relative to the batch)	53%	47%

Conclusion :
If you are using SQL Server 2012, I would recommend to use the new approach. It not only boosts the performance but reduce the line of code as well.

Read Full Post »

SQL SERVER 2012 – Fix – Error :35327- ALTER INDEX REBUILD statement failed because specifying FILLFACTOR is not allowed when rebuilding a columnstore index. Rebuild the columnstore index without specifying FILLFACTOR.

Posted in Errors, SQL SERVER, tagged Msg 35327 Level 16 State 1, raresql, SQL, SQL Server, SQL Server 2012, SQL SERVER 2012 - Fix - Error :35327- ALTER INDEX REBUILD statement failed because specifying FILLFACTOR is not allowed when rebuilding a columnstore index. Rebuild the columnstore index without speci on April 30, 2013| Leave a Comment »

sp_MsForEachTable is one of my favorite undocumented stored procedures. Even though it is undocumented (support not available) it is very handy stored procedure. I remember giving one script using sp_MsForEachTable to my team to built all indexes via a single line of code. Given below is the script.

--Given below script is not compatible with Columnstore Index
USE AdventureWorks2012;
GO
EXEC sp_MSforeachtable @command1="print '?'"
, @command2="SET QUOTED_IDENTIFIER ON;ALTER INDEX ALL ON ?
REBUILD WITH (FILLFACTOR=80)"

But this script broke down today while running on SQL Server 2012 with an error.

Let’s discuss this error in detail:
Message Number: 35327

Severity : 16

Error Message: ALTER INDEX REBUILD statement failed because specifying FILLFACTOR is not allowed when rebuilding a columnstore index. Rebuild the columnstore index without specifying FILLFACTOR.

Error Generation:
Let me create a columnstore index on a table to demonstrate this error.

USE AdventureWorks2012;
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX
[IX_SalesOrderDetail_ColumnStore]
ON [Sales].[SalesOrderDetail]
([ProductID],
[OrderQty],
[UnitPrice]
)WITH (DROP_EXISTING = OFF) ON [PRIMARY]
GO

Once columnstore index is created, lets execute the given below script.

USE AdventureWorks2012;
GO
EXEC sp_MSforeachtable @command1="print '?'"
, @command2="SET QUOTED_IDENTIFIER ON;ALTER INDEX ALL ON ?
REBUILD WITH (FILLFACTOR=80)"
--OUTPUT

Msg 35327, Level 16, State 1, Line 1
ALTER INDEX REBUILD statement failed because specifying FILLFACTOR is
not allowed when rebuilding a columnstore index. Rebuild the columnstore index without specifying FILLFACTOR.

Ooopps…… I am unable to execute it properly.

Resolution:

The reason behind this error is that once the above script tried to build columnstore index using fill factor, it generated error because it cannot build columnstore index using fill factor.

Two different methods to fix this error are :

Using Cursor
Using sp_MSforeachtable

Using Cursor

This method is recommended because there is no undocumented command involved in this solution and it uses cursor to build each index. Here is the solution.

Using sp_MSforeachtable

This method uses shortest possible solution. I modified above script and now it is compatible with columnstore index. It can build columnstore index without fill factor but the rest of the indexes will be filled with fill factor. Given below is the script.

USE AdventureWorks2012;
GO
EXECUTE sp_msForEachTable ' Print ''?'';SET QUOTED_IDENTIFIER ON
;IF EXISTS (Select * from sys.indexes
Where object_id = object_id(''?'') And type=6)
ALTER INDEX ALL ON ? REBUILD ELSE
ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR=80)'
--OUTPUT

Conclusion :
Remember, whenever you build columnstore index either all indexes together or discretely, never build columnstore index with fill factor.

Read Full Post »

SQL SERVER – Get day of week in SQL 2005/2008/2012

Posted in Solutions, SQL SERVER, tagged Datename Function, Day of the Week, Format Function, raresql, SQL, SQL Server, SQL Server 2012, week day name on April 18, 2013| Leave a Comment »

How to get the day of the week ? I have been asked this question several times and we do have its solution via DATENAME function. However today we will achieve the same solution using FORMAT function, introduced in SQL Server 2012 as well. Given below are the two methods that help in identifying the day of a week in SQL Server 2005 & SQL Server 2012 respectively.
Method 1 :
Script supported in SQL Server 2005 and above :

--This script will work in SQL Server 2005 and above
Declare @Datetime as datetime
Set @Datetime =getdate()
Select DATENAME(dw , @Datetime) as [Weekday Name]
--OR
Select DATENAME(weekday, @Datetime) as [Weekday Name]
--OUTPUT

Method 2 :
Script supported in SQL Server 2012 and above :

--This script will work in SQL Server 2012 and above
Declare @Datetime as datetime
Set @Datetime =getdate()
Select FORMAT(@Datetime,'dddd') as [Weekday Name]
--OUTPUT

Have you come across any other method ? Do share.

Read Full Post »

SQL SERVER 2012 – Fix – Error : 35343 – CREATE INDEX statement failed. Column has a data type that cannot participate in a columnstore index. Omit column.

Posted in Errors, SQL SERVER, tagged CREATE INDEX statement failed. Column has a data type that cannot participate in a columnstore index. Omit column, Msg 35343 Level 16 State 1, raresql, SQL, SQL Server, SQL Server 2012 on April 11, 2013| Leave a Comment »

Today when I was working on Columnstore index and I came across this error, CREATE INDEX statement failed. Column ‘%.*ls’has a data type that cannot participate in a columnstore index. Omit column ‘%.*ls’. This is one of the new error messages come in SQL Server 2012. This error message is related to Columnstore index a new database feature shipped with SQL Server 2012.

Let me explain this with a simple example.
Message Number: 35343

Severity : 15

Error Message: CREATE INDEX statement failed. Column ‘%.*ls’has a data type that cannot participate in a columnstore index. Omit column ‘%.*ls’.

Error Generation:

Given below is the script that I tried to execute but it resulted with the following error:

Use AdventureWorks2012
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_SalesOrderDetail_ColumnStore]
ON [Sales].[SalesOrderDetail]
(
[ProductID],
[OrderQty],
[UnitPrice],
[Rowguid]
)WITH (DROP_EXISTING = OFF) ON [PRIMARY]
GO

Msg 35343, Level 16, State 1, Line 1
CREATE INDEX statement failed. Column ‘rowguid’has a data type that cannot participate in a columnstore index. Omit column ‘rowguid’.

Ooopps…… I am unable to create columnstore index.

Resolution:
To resolve this error, I need to find the datatypes which are not supported by Columnstore index . As per MSDN, given below are the datatypes .

binary and varbinary
ntext , text, and image
varchar(max) and nvarchar(max)
uniqueidentifier
rowversion (and timestamp)
sql_variant
decimal (and numeric) with precision greater than 18 digits
datetimeoffset with scale greater than 2
CLR types (hierarchyid and spatial types)
xml

Now, just change your script (to create the columnstore index) and remove all non supported datatype. In this example, I will remove [Rowguid] column because its datatype is uniqueidentifier.

CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_SalesOrderDetail_ColumnStore]
ON [Sales].[SalesOrderDetail]
(
[ProductID],
[OrderQty],
[UnitPrice]
)WITH (DROP_EXISTING = OFF) ON [PRIMARY]
GO
--OUTPUT

Command(s) completed successfully.

Conclusion :

Remember, whenever you create the columnstore index, you must not include any column which has datatype that is not supported by columnstore index.

Read Full Post »

« Newer Posts - Older Posts »

SQL Server Portal

Real world SQL scenarios & its unique & optimized solutions

Posts Tagged ‘SQL Server 2012’

SQL SERVER 2012 – How to create a customized snippet

SQL SERVER – How to convert Gregorian dates to Hijri date with Formatting

SQL SERVER 2012 – Semantic Search – Implementation– Part-2

SQL SERVER 2012 – Semantic Search – Install and configure – Part-1

SQL SERVER 2012 – Fix – Error :11513 – The metadata could not be determined because statement contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.

SQL SERVER 2012 – Insert Snippet …

SQL SERVER – How to implement LIMIT with performance

SQL SERVER 2012 – Fix – Error :35327- ALTER INDEX REBUILD statement failed because specifying FILLFACTOR is not allowed when rebuilding a columnstore index. Rebuild the columnstore index without specifying FILLFACTOR.

SQL SERVER – Get day of week in SQL 2005/2008/2012

SQL SERVER 2012 – Fix – Error : 35343 – CREATE INDEX statement failed. Column has a data type that cannot participate in a columnstore index. Omit column.

Recent Posts

Archives

Categories

Blog Stats

Follow Blog via Email

Find us on Facebook

Disclaimer