Sounds more like a bug than a feature. Is this actually useful for anything beyond bar bets? Why would a single insert that breaks the rules be advantageous? Especially given the (rather unrealistic) constraints involved.
Yes. I posted those uses in the article along with the bar bet use.
As for the unrealistic constraints, there's really only 3 in most cases that will cause the others to automatically come into play.
- The table must be empty either as a new or truncated table. Common for a lot of ETL.
- The table must only have a clustered index. Common for a lot of ETL.
- You must use WITH(TABLOCK) during the insert. Not common for a lot of people because they don't know what it does for them but very easy to implement.
To be sure, the kind of ETL that I'm referring to is the same as I spoke of in the article. You want to import data into a staging table and because you know you'll have some expAnsive updates as you glean'n'clean the data, you want something less than a 100% Fill Factor. It avoids the need to import the data and then rebuild the index to help avoid the performance drag that page splits cause especially if the table has a lot of rows per page.
In that same vein, it's also handy for making copies of tables (or building large test tables) with a given fill factor in a limited space for testing or whatever, especially on large tables, because you avoid the "freespace overhead" of the original heap or clustered table being held in place until the new clustered table is committed.
Heh... and I do agree. It does sound a lot like a bug but one man's bug is another man's feature and, for a lot of the stuff I have to do, is a real convenience, bar bets included 😀 . I'll also say again that if you use it for something more important, you DO need to have a "Plan B". It IS definitely a totally undocumented "feature" that could go away at the drop of a hat and I hope it never does because it's useful for me.
Speaking of that, I've not tested this when SET IDENTITY_INSERT is ON... I don't know if the sort in TempDB that it causes (which has been listed as a bug on the MS Feedback site and has cost me dearly thanks to the damned non-optional TF 1117 effect in TempDB) will interfere with this "feature".