SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 SQL Server 2014 memory optimized tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ferrethouse
Constraint Violating Yak Guru

324 Posts

Posted - 07/11/2013 :  11:43:44  Show Profile  Reply with Quote
Two questions:

1. How do you alter an existing table to be memory optimized? I've only had luck creating new ones.

2. Documentation recommends using memory optimized tables for "critical" tables. What does that mean? Heavy reads? Heavy writes?

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 07/11/2013 :  13:00:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You have to create the table using certain settings. The suntax is slightly different.



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

ferrethouse
Constraint Violating Yak Guru

324 Posts

Posted - 07/11/2013 :  14:59:35  Show Profile  Reply with Quote
quote:
Originally posted by SwePeso

You have to create the table using certain settings. The suntax is slightly different.



N 56°04'39.26"
E 12°55'05.63"




I know how to create the table. I want to make an existing table memory optimized. Is it even possible?
Go to Top of Page

ferrethouse
Constraint Violating Yak Guru

324 Posts

Posted - 07/11/2013 :  15:26:08  Show Profile  Reply with Quote
Further research indicates that you can't ALTER a memory optimized table. You can't even add or change indexes. It seems that this table type is pretty limited at this point. Have to wait for a later release of 2014.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 07/11/2013 :  21:13:17  Show Profile  Visit robvolk's Homepage  Reply with Quote
In-memory tables are not a be-all end-all replacement for regular on-disk tables. There's a lot of guidance on what they're good for here:

http://blogs.msdn.com/b/arvindsh/archive/2013/07/03/sql-2014-in-memory-oltp-hekaton-training-videos-and-white-papers.aspx
Go to Top of Page

ferrethouse
Constraint Violating Yak Guru

324 Posts

Posted - 07/12/2013 :  15:56:47  Show Profile  Reply with Quote
quote:
Originally posted by robvolk

In-memory tables are not a be-all end-all replacement for regular on-disk tables. There's a lot of guidance on what they're good for here:

http://blogs.msdn.com/b/arvindsh/archive/2013/07/03/sql-2014-in-memory-oltp-hekaton-training-videos-and-white-papers.aspx



Thanks! Some great info on there.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 07/12/2013 :  19:49:47  Show Profile  Visit robvolk's Homepage  Reply with Quote
I just learned of a few other restrictions on compiled stored procedures:

- Can't use certain functions (DateAdd, DateDiff, NewID, SYSDATETIME) or statements (PRINT)
- Can't ORDER BY or use TOP on more than 8000 rows

While these kinda suck I can understand some of them based on the fact they're compiled to C code and then to DLLs. Just some more things to keep in mind if you're considering using them.
Go to Top of Page

ferrethouse
Constraint Violating Yak Guru

324 Posts

Posted - 07/15/2013 :  15:41:19  Show Profile  Reply with Quote
quote:
Originally posted by robvolk

I just learned of a few other restrictions on compiled stored procedures:

- Can't use certain functions (DateAdd, DateDiff, NewID, SYSDATETIME) or statements (PRINT)
- Can't ORDER BY or use TOP on more than 8000 rows

While these kinda suck I can understand some of them based on the fact they're compiled to C code and then to DLLs. Just some more things to keep in mind if you're considering using them.



It sounds like most of these restrictions are simply there because they haven't had time to do them. Wouldn't be surprised if the RTM isn't nearly as limited.

Edited by - ferrethouse on 07/15/2013 15:42:02
Go to Top of Page

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 07/15/2013 :  19:25:41  Show Profile  Visit robvolk's Homepage  Reply with Quote
In digging a bit further I found the C code and read some more of the whitepapers on Hekaton. They explain why some of the restrictions are there (mostly performance) and when I read those I saw why some others are not included either.

Basically, if you were writing an in-memory DB from scratch, you probably wouldn't include most of those things either.
Go to Top of Page

fchenaj
Starting Member

China
3 Posts

Posted - 10/17/2013 :  05:56:15  Show Profile  Reply with Quote
unspammed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000