Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 SQL Server 2014 memory optimized tables

Author  Topic 

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2013-07-11 : 11:43:44
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

30421 Posts

Posted - 2013-07-11 : 13:00:34
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

352 Posts

Posted - 2013-07-11 : 14:59:35
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

352 Posts

Posted - 2013-07-11 : 15:26:08
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

15732 Posts

Posted - 2013-07-11 : 21:13:17
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

352 Posts

Posted - 2013-07-12 : 15:56:47
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

15732 Posts

Posted - 2013-07-12 : 19:49:47
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

352 Posts

Posted - 2013-07-15 : 15:41:19
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.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-07-15 : 19:25:41
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

3 Posts

Posted - 2013-10-17 : 05:56:15
unspammed
Go to Top of Page
   

- Advertisement -