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


Hi dears i think all (DBA / DB Analyst / BI developer / SQL Developer) knowing about new Improvement in the world of databases new Enhancement in the world of databases  How the Query will be faster by  SQL Server 2014…ETC , but i think not all try this New version from SQL SERVER not knowing about the restriction on SQL Server 2014 , How we can use ..? What we used before in SQL Server 2012 and we can’t use it now in SQL Server 2014 ..?

So before going to upgrade we should now

  1.  the Improvement of SQL Server 2014 and the new Feature on SQL Server 2014. you can follow this Series of blogs  thanks for SHEHAP EL-NAGAR for his wonderful posts:

Why need to upgrade quickly to SQL Server 2014…? (1/10)

Why need to upgrade quickly to SQL Server 2014…? (2/10)

Why need to upgrade quickly to SQL Server 2014…? (3/10)

Why need to upgrade quickly to SQL Server 2014…? (4/10)

2- You Should now the Restriction/Constructs in SQL Server 2014 (Follow me  in this Part)

(What you do in SQL Server 2012 you can do on SQL Sever 2014) / but / (What you do on SQL Server 2012 you can’t do it on all new feature on SQL Server 2014 )

SQL Server 2014 coming with new Improvement and enhancement  called (In-Memory OLTP)

Memory-optimized tables and natively compiled stored procedures do not support the full Transact-SQL surface area that is supported by disk-based tables and interpreted Transact-SQL stored procedures. When attempting to use one of the unsupported features, the server returns an error. in my post i have 2 parts(theoretical part,Practical Part)

theoretical part

Memory optimized table :

we have many many things not supported in Memory Optimized table 😦this below points not all point i will Complete it in the Next Posts )

  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.

Note : before going to explain this point i will explain it by the Code to support what i need to say

What is the Meaning of this last points ..?

  • we can’t alter Memory Optimized table.

 In SQL Serve 2014 we can Create new Memory Optimized table but this new Feature doesn’t support the Alter so if you need to add new Column in this table you Should move your data to another table then recreate the table with new Structure.

  • We can’t create index after we create the table

In SQL Server 2014 if you need to Create Non Clustered  index on in memory table you should create it Through the table Creation Otherwise you don’t have any option.

  • Memory optimized tables cannot be created in system databases

this new Feature doesn’t support system database (master,tempdb,Model) but you can do it on MSDB Database

  • The index option ‘fillfactor’ , data_compression is not supported with indexes on memory optimized tables

index on memory optimized tables doesn’t support index option (Fillfactor,Data_compression )

  • Indexes on character columns that do not use a *_BIN2 collation are not supported with indexes on memory optimized tables

Index Created on String Columns Created on Certain Collation not supported all Collation

  • 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.

we can not Create Column with Char , Varchar , Nvarchar data type on  memory optimized tables on any collation supported only Certain Collation  .

Practical Part

{I will do my workshop on SQL Server 2014 you can download it from here }

  • Create Database

in SQL Server 2014 if you need to go for Memory optimized table you should Configure your database at the first to support the new feature so your Database should have File Group for MEMORY_OPTIMIZED_DATA so you can do it by this Script

CREATE DATABASE SQL2014_WorkShop
  • Alter Database SQL2014_WorkShop to add MEMORY_OPTIMIZED_DATA
ALTER DATABASE SQL2014_WorkShop
ADD FILEGROUP SQL2014_WorkShop_mod CONTAINS MEMORY_OPTIMIZED_DATA

ALTER DATABASE SQL2014_WorkShop
ADD FILE( NAME = 'SQL2014_WorkShop_mod' ,
FILENAME = 'c:\SQL2014_WorkShop_mod')
TO FILEGROUP SQL2014_WorkShop_mod;
  • Lsit by Databases working on MEMORY_OPTIMIZED_DATA_FILEGROUP

to know more inforamtion about this FileGroup you can run this Script (Run it on Database With MEMORY_OPTIMIZED_DATA_FILEGROUP)

Select * from Sys.data_spaces where type = 'FX'

List all your Databases that’s Support  MEMORY_OPTIMIZED_DATA_FILEGROUP

EXEC sp_MSforeachdb '
BEGIN
Use ?
 Select ''?'' AS Database_name,* from Sys.data_spaces where type = ''FX''
END'

now our database SQL2014_WorkShop is working on Collation Arabic_CI_AS you can test is by this Script

Select collation_name from Sys.Databases where Name = 'SQL2014_WorkShop'

Let’s go to our Demo to Cover the last monition point

  • we can’t alter Memory Optimized table

i will Create Table then i will try to Alter it

Use SQL2014_WorkShop
Go
CREATE TABLE Employees (
 EmployeeID int NOT NULL ,
 LastName nvarchar(20) NOT NULL ,
 FirstName nvarchar(10) NOT NULL ,
 CONSTRAINT PK_Employees PRIMARY KEY NONCLUSTERED HASH(EmployeeID) WITH (BUCKET_COUNT=1024)
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_AND_DATA)
GO

now try to alter this table by this Command

Alter table Employees
Add Mobile int Not Null

ERROR

Msg 10794, Level 16, State 15, Line 42
The operation ‘ALTER TABLE’ is not supported with memory optimized tables.

  • we can’t Create index on Memory optimized table after the table Created
Create NonClustered index [IX_lastname] on [Employees]
(lastname ASC) with (Fillfactor=80,data_Compression=page)

ERROR

Msg 10794, Level 16, State 13, Line 4 The operation ‘CREATE INDEX’ is not supported with memory optimized tables. IF you write Click on the indexes under the Table you will not Found Create new index and you will found one index Created with new name Non-Clustered Hash Index New index type for Memory Optimized Table in SQL Server 2014 i Created it when i Create the Table Check the Script for Create Memory Optimized Table.

  •  Memory optimized tables cannot be created in system databases.(Master/Model/tempdb)
CREATE TABLE Employees (
 EmployeeID int NOT NULL ,
 LastName nvarchar(20) NOT NULL ,
 FirstName nvarchar(10) NOT NULL ,
 CONSTRAINT PK_Employees PRIMARY KEY NONCLUSTERED HASH(EmployeeID) WITH (BUCKET_COUNT=1024)
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_AND_DATA)

ERROR

Msg 41326, Level 16, State 1, Line 61 Memory optimized tables cannot be created in system databases.

  • The index option ‘fillfactor’ , data_compression is not supported with indexes on memory optimized tables
  • Indexes on character columns that do not use a *_BIN2 collation are not supported with indexes on memory optimized tables

When i Create the Employee table i Created Non-Clustered Hash Index with it but what i can do if i need to Create Non-Clustered index on Specific Column  we have workaround for this Request but this request have some limitation  the same Pervious table Creation only i will alter the line for Column name (lastname) by this LastName nvarchar(20)  NOT NULL INDEX IX_LastName NONCLUSTERED,

use SQL2014_WorkShop
go
CREATE TABLE Employees_1 (
 EmployeeID int NOT NULL ,
 LastName nvarchar(20) NOT NULL INDEX IX_LastName NONCLUSTERED,
 FirstName nvarchar(10) NOT NULL ,
 CONSTRAINT PK_Employees_1 PRIMARY KEY NONCLUSTERED HASH(EmployeeID) WITH (BUCKET_COUNT=1024)
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_AND_DATA)
GO

Check this ERROR

Msg 12328, Level 16, State 106, Line 3 Indexes on character columns that do not use a *_BIN2 collation are not supported with indexes on memory optimized tables. Msg 1750, Level 16, State 0, Line 3 Could not create constraint or index. See previous errors.

this Error meaning : we cannot Create Non-Clustered index on Nvarchar() Column on any Collation we have Limitation on the Collation not all Collation for Column with data type  Nvarchar() can Support Non-Clustered

SQLServer2014_2

 

 

 

 

 

 

 

 

All supported collations for indexes on memory-optimized tables

select * from sys.fn_helpcollations() where name like '%BIN2'

so let’s Create index on memory-Optimized-Tables

use SQL2014_WorkShop
go
CREATE TABLE Employees_1 (
 EmployeeID int NOT NULL , 
 LastName Nvarchar(20) Collate Arabic_100_BIN2 NOT NULL INDEX IX_LastName NONCLUSTERED, 
 FirstName Nvarchar(10) NOT NULL ,
 CONSTRAINT PK_Employees_1 PRIMARY KEY NONCLUSTERED HASH(EmployeeID) WITH (BUCKET_COUNT=1024)
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_AND_DATA)
GO

perfect 10 table Crated with index Successfully So Indexes on (n)(var)char columns can only be specified with BIN2 collations  index Option not Supported on memory-Optimized-Tables

use SQL2014_WorkShop
go
CREATE TABLE Employees_2 (
 EmployeeID int NOT NULL , 
 LastName Nvarchar(20) Collate Arabic_100_BIN2 NOT NULL 
INDEX IX_LastName NONCLUSTERED with(FillFactor=80,Data_Compression=page), 
 FirstName Nvarchar(10) NOT NULL ,
 CONSTRAINT PK_Employees_1 PRIMARY KEY NONCLUSTERED HASH(EmployeeID) WITH (BUCKET_COUNT=1024)
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_AND_DATA)
GO

ERROR:

Msg 10794, Level 16, State 89, Line 3 The index option ‘fillfactor’ is not supported with indexes on memory optimized tables.

  • 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.
now if you need to Create memory optimized tables With Column varchar 
or Char not Supported all Collation should be this Collation has 
a code page other than 1252 If you go now to Create table With 
the same last Collation we used it on the Index it will not 
Supported if the Column Data Type is Varchar(n)/Char(n). 
The same Previous Code what i changed here only the data 
type for the Column (lastName) Changed it from nvarchar(20) 
to (varchar(20) and Removed the index
use SQL2014_WorkShop
go
CREATE TABLE Employees_2 (
 EmployeeID int NOT NULL , 
 LastName varchar(20) Collate Arabic_100_BIN2 NOT NULL  , 
 FirstName Nvarchar(10) NOT NULL ,
 CONSTRAINT PK_Employees_2 PRIMARY KEY NONCLUSTERED HASH(EmployeeID) 
WITH (BUCKET_COUNT=1024)
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_AND_DATA)
GO

ERROR: Msg 12329, Level 16, State 107, Line 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. Workaround here : all supported collations for (var)char columns in memory-optimized tables:

select * from sys.fn_helpcollations()
where collationproperty(name, 'codepage') = 1252

So let’s take one Collation from the result list as Example {SQL_Latin1_General_CP1_CI_AS}

use SQL2014_WorkShop
go
CREATE TABLE Employees_2 (
 EmployeeID int NOT NULL , 
 LastName varchar(20) Collate SQL_Latin1_General_CP1_CI_AS NOT NULL , 
 FirstName Nvarchar(10) NOT NULL ,
 CONSTRAINT PK_Employees_2 PRIMARY KEY NONCLUSTERED HASH(EmployeeID) 
WITH (BUCKET_COUNT=1024)
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_AND_DATA)
GO

Perfect 10 table Created Successfully  but we have one issue  we can not Create index on this Column like what we do before because this Collation {SQL_Latin1_General_CP1_CI_AS} not from the list of {BIN2 Collation}. Let’s Try

use SQL2014_WorkShop
go
CREATE TABLE Employees_3 (
 EmployeeID int NOT NULL , 
 LastName varchar(20) Collate SQL_Latin1_General_CP1_CI_AS NOT NULL 
INDEX IX_LastName NONCLUSTERED , 
 FirstName Nvarchar(10) NOT NULL ,
 CONSTRAINT PK_Employees_3 PRIMARY KEY NONCLUSTERED HASH(EmployeeID) 
WITH (BUCKET_COUNT=1024)
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_AND_DATA)
GO

ERROR:  Msg 12328, Level 16, State 106, Line 6 Indexes on character columns that do not use a *_BIN2 collation are not supported with indexes on memory optimized tables. Msg 1750, Level 16, State 0, Line 6 Could not create constraint or index. See previous errors. Very bad so what we Should do in this Case if we need to Create Memory-Optimized-table with Column Data Type (Varchar or Char) and with Non-Clustered index on this Column. we should now the slimier Collation between

  • Indexes on (n)(var)char columns can only be specified with BIN2 collations 
  • (var)char columns in memory-optimized tables must use code page 1252 collation
select * from sys.fn_helpcollations()
where collationproperty(name, 'codepage') = 1252 and name like '%BIN2'

by this result we can Create Memory-Optimized table with Column Vrachar with NonClsutered index on this Column

use SQL2014_WorkShop
go
CREATE TABLE Employees_3(
 EmployeeID int NOT NULL ,
 LastName varchar(20) Collate Latin1_General_100_BIN2 NOT NULL
INDEX IX_LastName NONCLUSTERED ,
 FirstName Nvarchar(10) NOT NULL ,
 CONSTRAINT PK_Employees_3 PRIMARY KEY NONCLUSTERED HASH(EmployeeID)
WITH (BUCKET_COUNT=1024)
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_AND_DATA)
GO

Perfect 10 Table Created Successfully this is not the End if you need to know

Image

don’t close your email Still follow me Mostafa Elmasry  To know what is the new in SQL Server 2012 or 2014 to 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