| 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" |
 |
|
|
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? |
|
|
|
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. |
|
|
|
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 |
|
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
|
|
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 rowsWhile 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. |
|
|
|
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 rowsWhile 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. |
|
|
|
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. |
|
|
|
fchenaj
Starting Member
3 Posts |
Posted - 2013-10-17 : 05:56:15
|
| unspammed |
|
|
|
|
SQL Server 2014 memory optimized tables