SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Natively Compiled Stored Procedures: What they are all about!


Natively Compiled Stored Procedures: What they are all about!

Author
Message
Ed Pollack
Ed Pollack
SSC Eights!
SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)

Group: General Forum Members
Points: 808 Visits: 513
Comments posted to this topic are about the item Natively Compiled Stored Procedures: What they are all about!
peter-757102
peter-757102
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1069 Visits: 2559
Thank you for the article and especially the attention to the restrictions.

Which sadly brings me to stating my opinion that Microsoft spend a lot of time adding a near unusable and sometimes just inaccessible features. It seems they lost track of their product development and have no clue what is important and what is not. There are so many things that can make SQL Server a lot more usable to a lot more people. And most definitely, pleasing the anti-SQL/anti-RDBMS crowd isn't one of them.
icocks
icocks
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 628
No reflection on the article, which is thorough & well written, but I went from thinking that natively compiled sprocs sounded great to wondering in what circumstances I'd ever be able to use them. The list of restrictions is just incredible. You can work round most of them, but mostly in ways that sound a retrograde step (my particular favourite is using a While loop in place of an outer join Crazy ). Hope the feature becomes more usable in later releases.
patrickmcginnis59 10839
patrickmcginnis59 10839
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2395 Visits: 5668
icocks (8/4/2014)
No reflection on the article, which is thorough & well written, but I went from thinking that natively compiled sprocs sounded great to wondering in what circumstances I'd ever be able to use them. The list of restrictions is just incredible. You can work round most of them, but mostly in ways that sound a retrograde step (my particular favourite is using a While loop in place of an outer join Crazy ). Hope the feature becomes more usable in later releases.

Makes me wonder about the reasons behind the restrictions. Surely a first iteration of a new product feature can dictate a list of gotchas, so it will be interesting to see how this new functionality progresses, which restrictions are eliminated, and which stick around for what reasons (if they're ever discussed that is).

to properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
patrickmcginnis59 10839
patrickmcginnis59 10839
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2395 Visits: 5668
peter-757102 (8/4/2014)
Thank you for the article and especially the attention to the restrictions.

Which sadly brings me to stating my opinion that Microsoft spend a lot of time adding a near unusable and sometimes just inaccessible features. It seems they lost track of their product development and have no clue what is important and what is not. There are so many things that can make SQL Server a lot more usable to a lot more people. And most definitely, pleasing the anti-SQL/anti-RDBMS crowd isn't one of them.

Actually, pleasing the NoSQL crowd could actually be driving some development efforts on Microsoft's part, and not just with this feature set we're discussing, look at maybe Hadoop on Azure for instance. Whether this is strictly "anti-SQL/anti-RDBMS", well thats probably a conversation that will bring out the "troll label" again, so no thanks on that one!

to properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
andrewj29
andrewj29
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 5
Perhaps I missed it, but can a Natively Compiled Stored Procedure (NCSP) be called from within a Non-NCSP?

I am sure some kind of 'wrapper' will be developed in the future, to make the implementation of NCSPs a bit easier.
skeleton567
skeleton567
Mr or Mrs. 500
Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)

Group: General Forum Members
Points: 525 Visits: 401
As a retired DBA and having done extensive development of SQL code, the prohibition of foreign keys is a show stopper. Responsible designers and developers need to keep data integrity and reliability as perhaps the uppermost priority, even at the expense of performance and other consideration. invalid information is worthless at any speed, and dangerous to companies making decisions based on it. Sometimes progress can be in the wrong direction. Things that are fun and interesting to technical folks are not always the most beneficial to enterprises. We all know that code is almost never perfect and must constantly be reproved by any means possible.
Ed Pollack
Ed Pollack
SSC Eights!
SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)

Group: General Forum Members
Points: 808 Visits: 513
andrewj29 (8/4/2014)
Perhaps I missed it, but can a Natively Compiled Stored Procedure (NCSP) be called from within a Non-NCSP?

I am sure some kind of 'wrapper' will be developed in the future, to make the implementation of NCSPs a bit easier.


You definitely can call a NCSP from a non-NCSP if you want. The NCSP is an atomic set of TSQL and will occur on its own, in-memory, without interference from any other TSQL in the calling proc.
Ed Pollack
Ed Pollack
SSC Eights!
SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)

Group: General Forum Members
Points: 808 Visits: 513
The gotcha with in-memory OLTP is that it needs to be completely contained in-memory. A natively compiled stored proc can't go out to disk for other data, or even for TempDB usage (temp tables, hash joins, etc...). Therefore, any in-memory schema will be somewhat isolated from the rest of your database.

In general, I think the key to in-memory OLTP is to only use it in areas where it will provide the greatest gain. Places where contention and IO are very heavy can benefit greatly from its use---if all of the restrictions aren't show-stoppers.

I've taken the time to convert some real world production schema into in-memory OLTP w/ a few natively compiled procs, and while it took a while, the results were impressive.

Can everyone live without left joins or foreign keys? Maybe not...and Microsoft's workarounds provided for each of these cases are a bit hacky at times, but there's potential here that I expect will become more useful in future versions of SQL Server. Columnstore indexes got quite a bit of extra love in 2014, and I am sure in-memory OLTP will see similar actions taken, given the common theme of responses by DBAs to the new feature.
Alan.B
Alan.B
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7069 Visits: 7836
Great, great article Ed!

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search