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

About Mustafa EL-Masry

I love fixing problems and learning new technologies that can help me fix those problems faster and better than I did the time before. In my spare time I like to read, speak, write blogs and learn new things. I currently work as a Principal Database Administrator where I solve fairly interesting problems on fairly large databases. And troubleshooting High sensitive Performance Cases ► I am Microsoft Certified in SQL Server Administration and Development 2008 , 2012 MCTS, MCTIP , MCSA , MCSE ► I have deep practical knowledge about T-SQL performance , HW Performance issues, Data Warehousing and data mart solutions , SQL Server Replication, Clustering solutions (Active Active and active passive)and Database Designs for different kinds of systems , HAG, I worked on All SQL Server Versions (2005,2008,2008R2,2012,2014) diving deeply more check the below information about me : ► Founder of Community :SQL Server Consultation :http://MostafaElmasry.com/ ► Co-Founder : http://www.SQLGULF.net ► Audience Marketing Manager and Executive Board member: SQLSERVER PERFORMANCE TUNING http://sqlserver-performance-tuning.net/ ► LinkedIn: https://www.linkedin.com/in/mostafaelmasry ► My Community Annual report : http://mostafaelmasry.com/2014/annual-report/ ► I am Technical Writer and Reviewer : http://www.slideshare.net/MostafaElmasry3/ ► Fluent Participator at Microsoft Forums of SQL Server at http://Social.technet.microsoft.com ► +200 blogs in SQL Server technology English and Arabic: http://mostafaelmasry.com/community-founder/all-tips/ ► Microsoft Profile: https://www.mcpvirtualbusinesscard.com/VBCServer/EngMostafaElamsry/profile For any inquiries you can contact me on my phone or by mail: Mobile: +966 543990968, +2 01114668821 Mail: dbconsultant@mostafaelmasry.com & SQLGULF@mostafaelmasry.com
Gallery | This entry was posted in Home, SQL Server 2014. Bookmark the permalink.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s