Let’s start with the safety convention. The “null” of a null pointer isn’t a magic value, but in real-life implementation is simply zero, which is a perfectly valid virtual address. However, on the premise that trying to access address zero or addresses near it probably indicates a program error, the OS will map that page in such a way that trying to access it causes an access violation. This is not a bug or an accident, but a damn clever feature! Robert Love explains it very nicely over here for Linux, and it applies equally to Windows.
Now recall the convention that trying to retrieve the head or tail of an empty list will – by convention – bring you back a null pointer. When iterating, a related convention may also return a zero when you’ve gone all the way around and come back to the list head. Clearly the onus is on the developer to recognise that null pointer and not dereference it, but attempting to do so sets in motion the safety feature of an access violation, which can then be neatly caught through standard exception handling, for instance yielding a diagnostic stack dump.
Very interesting article, and also a good juxtaposition of supported, “production-safe” code versus undocumented processes.
My first thought was that perhaps there is some process that runs against the production system and the test system that goes to sleep with an open transaction, holding an X or an IX lock against this table. If the index create can’t get its shared lock, then it could be part of a blocking chain.
So I asked first if the index create was the head of the blocking chain, or if it was perhaps blocked by something else. The answer came back that no, the index create was NOT blocked. It was holding the shared lock for a long time.
My new friend even sent a screenshot of the index create running against the test instance in sp_WhoIsActive with blocking_session_id null.
Read on for the full story and keep those systems patched.
In the video, I show an example of a stack dump caused by running DBCC PAGE with format style 3 against a table with a filtered index in SQL Server 2014.
It looks like this bug is fixed in SQL Server 2016, at least by SP1.
Sample code to reproduce this against the AdventureWorks2012 database (which I had restored to SQL Server 2014) is in my gist here.
Click through to watch the video.
There are other issues with the MERGE statement. It has bugs… some of which can cause database corruption.
Here we have a Shiny New Toy (feature), supposed to make life easier, yet it causes problems. Until it can perform better (and the bugs are eliminated), I just don’t use it.
Beware the Shiny New Toys.
Wayne makes a great point. Not all new things are good, even when they’re potentially quite useful. I love shiny new toys a lot, but part of being a database administrator is protecting data, and part of that means being able to trust your tools. Sometimes the tools work really well right out of the gate, and sometimes (like in the case of MERGE) they don’t.
I recently wrote a blog about how to tell why your SQL login isn’t working. There were a lot of good comments and several of them suggested that I create a connect entry to make the error easier to understand. There was also a question of how to create a connect entry, and it wasn’t the first time I’d heard that, this week. So I’m going to give a quick demo on how to create a connect entry. I’m not going to create it using that particular login error because, as another person pointed out, this error is actually by design. We don’t want to make it easier for someone trying to hack in right?
After a lot of different variables in the test-setup, I found out that it’s probably an old bug that wasn’t properly patched when upgrading the SQL Server engine to a newer version. Let me elaborate on that:
– The bug is reproducible on the test server, which is an upgraded engine from SQL 2012 or 2014 to SQL 2016 RTM
– The bug is reproducible on the production server, which is an upgraded engine from SQL 2014 to SQL 2016 RTM
– The bug is not reproducible on a clean install of SQL 2014
– The bug is not reproducible on a clean install of SQL 2016 RTM
– The bug is not reproducible on a clean install of SQL vNext CTP
There’s a lot of good investigative work here, so check it out.
First contender: Inserting to an indexed view can fail
What would happen if I told you that, with regards to a view, sometimes inserting into the table could fail? Well that’s what this Connect item from Dave_Ballantyne found, along with the reason.
Click through for more bugs.
Magic numbers! That sounds cool. Except it’s not. It’s a horrible drain on readability and a pox on the manageability of your code. But we need them, because every database out there has “lookup tables” with specific values that we need to predicate on throughout the code base. SQL Server could solve this problem once and for all by allowing users to promote these values to named enumerators. How cool would that be?!? 220 votes, nine years.
And finally, one more from Mr. Ben-Gan, a clever idea for improving TOP with the OVER clause. I love the OVER clause. I love TOP. Their offspring would be beautiful indeed… 180 voters over the past nine years have shared in this sentiment.
This is an interesting list.
The Connect item that I’m promoting today is Connect #339410 : Please fix the “String or binary data would be truncated” message to give the column name. This Connect item was opened in 2008 and has 1,328 upvotes. If we can create virtual realities, we should be able to add a column name to an error message. Help us Microsoft, you’re our only hope.
This error message is incredibly annoying, particularly because it seems the engine should know which record has an error and on which column that error is. We finally got information on which row caused a primary or unique key constraint violation, and that was helpful; this would be at least as helpful.
There are so many active Connect items, it’s difficult to choose just one, but after spending some time looking at the
generate_seriesfunction in PostgreSQL, the one at the top of my list right now is this suggestion from Erland Sommarskog (blog):
I think it would be cheap and easy for SQL Server to provide a defaut table of numbers, always in memory, just like any other catalog view or DMV. This thing can use compression now, regardless of edition, so even a table with 2 billion rows will only take 13 MB, and should be easy to populate either at start-up or on first use. I could list out all of the potential uses for a numbers table, but they’re fairly well documented in the following sources (and most use cases don’t need anywhere near 2 billion values, so maybe the range could be defined using a
Click through for additional Connect items, and please vote on any Connect items which catch your eye.