Saturday, May 10, 2014

[Event Notifications] Hekaton prevents EN?!

Nooooooooooooo...

http://msdn.microsoft.com/en-us/library/dn246937(v=sql.120).aspx

Memory-optimized tables and natively compiled stored procedures cannot be created or dropped if there is a server or database event notification for that DDL operation. Remove the server and database event notifications on CREATE TABLE or DROP TABLE and CREATE PROCEDURE or DROP PROCEDURE.

What happens if you attempt to do it?

 Msg 10794, Level 16, State 124, Line 1
The feature 'EVENT NOTIFICATION' is not supported with memory optimized tables.



Why is this a big deal?  Because, honestly, I'm scared that if they're not supporting things like this, that EN is on its way out.  I use it extensively (see my presentations on it, coming soon to a SQL Saturday near you), and it's IMHO a must-have feature.  It also means that whenever we want to do something with Hekaton, we have to drop the EN, do the Hekaton-based changes, then we're less likely to use Hekaton or EN.  

Please vote on my Connect item:

https://connect.microsoft.com/SQLServer/feedback/details/874185/event-notification-please-make-in-memory-oltp-tables-natively-compiled-sps-work-with-en

2 comments:

Sam Yuan said...

Hi Michael,

My name is Sam. and I'm a production DBA who attended your class this past weekend in Houston. Absolutely loved it! Thank you so much for bringing this topic to houston. I'm excited to try it out at my work place. One thing about the download, some en_Full_Repxxx sql files could not be downloaded. Is it possible to email them to me? (Scripts 06, 11,12 and 17). Thank you so much! oh also, please allow me to add you to my professional network on LinkedIn.

Regards,
Sam Yuan
syphotos@gmail.com

bourgon said...

Howdy, Sam! Thanks for the compliments. Trust me, it was my pleasure to present. I'll post them on this blog in a few.