What we Should know before going to upgrade to SQL Server 2014(3/4)


Hello my Followers in the last previous post i covered some points not supported in SQL Server 2014 in the new Technology and Feature Memory-Optimized-Table :
We can Create Index on NULL Column on Memory-optimized-table ..?
We can Create Memory-optimized-table Without Primary Key Column ..?
Clustered index Supported or not Supported in Memory-optimized-table ..?
What about Computed Column / PERSISTED Supported or No in Memory-optimized-table ..?
How we can Update the Primary key Column in Memory-optimized-table ..?
and today we will Cover new point and limitation in SQL Server 2014 Memory Optimized Table :
SQL Server 2014

Memory Optimized Table Dosent Support Table Without Non Clustered index or Primary Key
CREATE TABLE Employee (
StudentID int NOT NULL,
FirstName varchar(256) COLLATE Latin1_General_100_BIN2 NOT NULL,
LastName varchar(256) COLLATE Latin1_General_100_BIN2 NOT NULL,
Age tinyint NOT NULL,
Major varchar(256) COLLATE Latin1_General_100_BIN2 NOT NULL,
EnrollmentDate datetime
)
WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_ONLY)
Error:
Msg 41327, Level 16, State 7, Line 1
The memory optimized table ‘Employee’ must have at least one index or a primary key.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint or index. See previous errors.
IDENTITY Feature not Supported in Memory-Optimized-Table
here in this point i need to Clear something IDENTITY feature is Supported and not Supported in Memory-Optimized-Table
IDENTITY(1, 1) is supported on a memory-optimized table. However, identity columns with definition of IDENTITY(x, y) where x != 1 or y != 1 are not supported on memory-optimized tables. The workaround for IDENTITY values uses the SEQUENCE object (Sequence Numbers).
IDENTITY(1, 1) is supported on a memory-optimized table
Create Table Orders
(Order_ID int Identity (1,1),
Order_Name Nvarchar(50)

PRIMARY KEY NONCLUSTERED HASH
(
[Order_ID]
)WITH ( BUCKET_COUNT = 128)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )
IDENTITY(1, 2) not supported on a memory-optimized table
Create Table Orders
(Order_ID int Identity (1,2),
Order_Name Nvarchar(50)

PRIMARY KEY NONCLUSTERED HASH
(
[Order_ID]
)WITH ( BUCKET_COUNT = 128)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )
ERROR:
Msg 10794, Level 16, State 8, Line 1
The feature ‘IDENTITY’ is not supported with memory optimized tables.
So as Microsoft recommendation in this case The workaround for IDENTITY values uses the SEQUENCE object (Sequence Numbers).
ANSI_PADDING OFF Not Supported in Memory-Optimized-Table
When you are going to Create New Memory-Optimized-Table try to enable Feature ANSI_PADDING it Should be ON
SET ANSI_PADDING OFF

Create Table Orders
(Order_ID int NOT NULL ,
Order_Name Nvarchar(50)

PRIMARY KEY NONCLUSTERED HASH
(
[Order_ID]
)WITH ( BUCKET_COUNT = 128)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )
Memory-optimized tables Not Suuported as target of MERGE
Memory-optimized tables cannot be the target of a MERGE operation. Use INSERT, UPDATE, or DELETE statements instead.
Create Table Target_Table
(Order_ID int NOT NULL ,
Order_Name Nvarchar(50)

PRIMARY KEY NONCLUSTERED HASH
(
[Order_ID]
)WITH ( BUCKET_COUNT = 128)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )

GO

Create Table Source_Table
(Order_ID int NOT NULL ,
Order_Name Nvarchar(50)

PRIMARY KEY NONCLUSTERED HASH
(
[Order_ID]
)WITH ( BUCKET_COUNT = 128)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )

GO

Insert into Source_Table
Values (2,’SSIS’)
Merg
Merge Command on Memory -Optimized-table
Merge Target_Table As Target
USING Source_Table AS Source
on Target.Order_ID = Source.Order_ID
When Not Matched then Insert
(Order_ID , Order_name)
Values (Source.Order_ID , Source.Order_name);
ERROR :
Msg 10794, Level 16, State 104, Line 27
The operation ‘MERGE’ is not supported with memory optimized tables.

this not the End if you need to know more Secrets, more Surprise , More Workaround in SQL Server 2014 don’t close your email Still follow me Mostafa Elmasry to be the first one know this new Secrets in SQL Serve 2014 🙂
See you in the next blog Post

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: