What we Should know before going to upgrade to SQL Server 2014(2/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 :

  1. Memory Optimized table doesn’t support Alter.
  2. We can’t create index after we create the table but before we can do (Non-Clustered Hash / Non-Clustered )
  3. Memory optimized tables cannot be created in system databases.
  4. The index option ‘fillfactor’ , data_compression is not supported with indexes on memory optimized tables.
  5. Indexes on character columns that do not use a *_BIN2 collation are not supported with indexes on memory optimized tables
  6. The data types char(n) and varchar(n) using a collation that has a code page other than 1252 are not supported with memory optimized tables.

Today we are going to Show another Important point must be the DB Developer and DB Analyst / Architecture / DBA take care about it  let’s go for our DEMO : I will work on the Same Database i sued it on the previous Post

What we will Cover today in this Post is :

  1. We can Create Index on NULL Column on Memory-optimized-table ..?
  2. We can Create Memory-optimized-table Without Primary Key Column ..?
  3. Clustered index Supported or not Supported in Memory-optimized-table ..?
  4. What about Computed Column / PERSISTED Supported or No in Memory-optimized-table ..?
  5. How we can Update the Primary key Column in Memory-optimized-table ..?

 

  • Nullable columns in the index key are not supported with indexes on memory optimized tables

We Can’t Create index on Nullable Column on Memory optimized-table it Should be Not Null Column

CREATE TABLE [dbo].[orders_mem](
 [O_OrderKey] [int] NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=3000000),
 [O_CustKey] [int] NULL,
 [O_OrderStatus] [Nvarchar](64) NULL,
 [O_TotalPrice] [decimal](13, 2) NULL,
 [O_OrderDate] [datetime] NULL, --,INDEX IX_OrderDate NONCLUSTERED
 [O_OrderPriority] [Nvarchar](15) NULL,
 [O_Clerk] [Nvarchar](64) NULL,
 [O_ShipPriority] [int] NULL,
 [O_Comment] [Nvarchar](80) NULL,
 [skip] [Nvarchar](64) NULL

INDEX IX_OrderDate (O_OrderDate ASC)
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA)
GO

NullableColumn

 

 

 

 

 

 

 

 

 

 

  • No Heap table in Memory Optimized table

The memory optimized table with DURABILITY=SCHEMA_AND_DATA must have a primary key

CREATE TABLE Employee
(
ID int,
NAME Nvarchar(50))
WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA)
  • Clustered indexes not Supported in Memory Optimized table

As we see in the Pervious point No Memory optimized table without Primary key so i added the primary key and we now the default index for the primary key is Clustered index if we didn’t have any Clustered index on the table but the fact here in memory optimized table No Clsutered index so we Shoukd write behind the Primary key NONCLUSTERED HASH WITH (BUCKET_COUNT=3000000) this the newNONCLUSTERED Type index on SQL Server 2014 NONCLUSTERED HASH

CREATE TABLE Employee
(
ID int NOT NULL PRIMARY KEY ,
NAME Nvarchar(50))
WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA)

ERROR:

Msg 12317, Level 16, State 72, Line 1
Clustered indexes, which are the default for primary keys, are not supported with memory optimized tables.

True Way to create Memory Optimized table with primary key

CREATE TABLE Employee
(
ID int NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=3000000),
NAME Nvarchar(50))
WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA)
  • Computed columns are not supported with memory optimized tables
USE SQL2014_WorkShop
GO
CREATE TABLE Employee_Salary
(

ID int NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=3000000),
BASIC_Salary bigint NOT NULL,
Monthly_bonuses Bigint NULL,
ToTal_Salary as (BASIC_Salary+Monthly_bonuses)PERSISTED
)WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA)
  • Update Action not Supported on Primary Key Column on Memory-Optimized-table

WoooW in Memory-Optimized-table We can’t Update the primary Key Column If you need to do this you must delete it and insert the row again with new Value for the Primary key.

USE SQL2014_WorkShop
go
CREATE TABLE PRIMARY_table
(
ID INT not null primary key nonclustered hash with (bucket_count = 1024),
NAME Nvarchar(50)
)
WITH (MEMORY_OPTIMIZED = ON)

Go

INSERT INTO PRIMARY_table
(ID,Name) VALUES
(1,'Mostafa')

NOW LET’S TY TO UPDATE THE PRIMARY KEY COLUMN

UPDATE PRIMARY_table SET ID = 2 WHERE ID = 1

RROR :

Msg 12302, Level 16, State 9, Line 1
Updating columns that are part of the PRIMARY KEY constraint is not supported with memory optimized tables.

What we Should do in this Case ..? We Should do workaround like this

DELETE FROM PRIMARY_table WHERE ID = 1

GO
INSERT INTO PRIMARY_table
(ID,Name) VALUES
(2,'Mostafa')

this only the way to update the primary key Column on Memory-Optimized-table

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 :) and you can View all my tips

See you in the next blog Post

Reference by {SQLServer performance Tuning} and {Database Administration Community}

 

 

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 and tagged , , , , , , , . 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