September | 2013 | SQL Server Portal

Archive for September, 2013

SQL SERVER HEKATON – Fix – Error – 10770 – The operation ‘ALTER TABLE’ is not supported with memory optimized tables.

Posted in Errors, SQL SERVER, tagged Msg 10770 Level 16 State 102, raresql, SQL, SQL Server, SQL SERVER HEKATON - Fix - Error - 10770 - The %S_MSG '%ls' is not supported with memory optimized tables., The operation 'ALTER TABLE' is not supported with memory optimized tables. on September 3, 2013| 2 Comments »

The operation ‘ALTER TABLE’ is not supported with memory optimized tables is one of the new error messages in SQL Server Hekaton. This error message is related to Memory optimized tables feature, a new type of table shipped with SQL Server Hekaton.

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

Severity : 16

Error Message: The %S_MSG ‘%ls’ is not supported with memory optimized tables.

Error Generation:
Let me create a sample memory optimized table to demonstrate this error.

USE Sample_DB
GO
CREATE TABLE tbl_sample
(
[ID] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH
WITH (BUCKET_COUNT = 100000),
[Name] VARCHAR(50) NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

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

ALTER TABLE tbl_sample ADD [CreationDateTime] datetime NULL
--OUTPUT

Msg 10770, Level 16, State 102, Line 17
The operation ‘ALTER TABLE’ is not supported with memory optimized tables.

Ooopps…… I am unable to execute it.

Resolution:
Memory optimized tables do not support alter table statement. So, instead of alter, you need to drop and create the memory optimized tables.

Lets rewrite the above statement using drop and create. Given below is the script.

USE Sample_DB
GO
DROP TABLE tbl_sample
GO
CREATE TABLE tbl_sample
(
[ID] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH
WITH (BUCKET_COUNT = 100000),
[Name] VARCHAR(50) NOT NULL,
[CreationDateTime] datetime
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

Read Full Post »

SQL SERVER 2012 – Create custom snippet for Sequence object

Posted in Solutions, SQL SERVER, tagged Create custom snippet for Sequence object, raresql, SQL, SQL Server 2012 on September 2, 2013| Leave a Comment »

SEQUENCE object is one of my favorite objects introduced in SQL Server 2012. It is very handy when you need an enhanced functionality in an identity column. Due to its performance & functionality, I use it very frequently, but the issue is, I either need to write the complete script of sequence by myself or I need to use SSMS and then go to database >> Programmability >> Sequence and create a new sequence object as shown in the picture below.

Alternatively, we can create the Sequence object snippet, as shown in the script below. Just copy the given below script and save as “Create Sequence.snippet” and register it. Once registered, you can use it like any other custom snippet.

<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
<_locDefinition xmlns="urn:locstudio">
<_locDefault _loc="locNone" />
<_locTag _loc="locData">Title</_locTag>
<_locTag _loc="locData">Description</_locTag>
<_locTag _loc="locData">Author</_locTag>
<_locTag _loc="locData">ToolTip</_locTag>
</_locDefinition>
<CodeSnippet Format="1.0.0">
<Header>
<Title>Create Sequence</Title>
<Shortcut></Shortcut>
<Description>Creates a sequence.</Description>
<Author>Muhammad Imran</Author>
<SnippetTypes>
<SnippetType>Expansion</SnippetType>
</SnippetTypes>
</Header>
<Snippet>
<Declarations>
<Literal>
<ID>SchemaName</ID>
<ToolTip>Name of the schema</ToolTip>
<Default>dbo</Default>
</Literal>
<Literal>
<ID>Sequencename</ID>
<ToolTip>Name of the Sequence</ToolTip>
<Default>Sample_Sequence</Default>
</Literal>
<Literal>
<ID>datatype</ID>
<ToolTip>Data type of the column</ToolTip>
<Default>int</Default>
</Literal>
<Literal>
<ID>start</ID>
<ToolTip>Start Value</ToolTip>
<Default>1</Default>
</Literal>
<Literal>
<ID>increment</ID>
<ToolTip>Increment Value</ToolTip>
<Default>1</Default>
</Literal>
<Literal>
<ID>minvalue</ID>
<ToolTip>Min Value</ToolTip>
<Default>1</Default>
</Literal>
<Literal>
<ID>maxvalue</ID>
<ToolTip>Max Value</ToolTip>
<Default>100</Default>
</Literal>
<Literal>
<ID>Cache</ID>
<ToolTip>Cache Value</ToolTip>
<Default>2</Default>
</Literal>
</Declarations>
<Code Language="SQL">
<![CDATA[CREATE SEQUENCE [$SchemaName$].[$Sequencename$]
AS [$datatype$]
START WITH $start$
INCREMENT BY $increment$
MINVALUE $minvalue$
MAXVALUE $maxvalue$
CACHE $Cache$
GO
]]>
</Code>
</Snippet>
</CodeSnippet>
</CodeSnippets>

Read Full Post »