Turns out SQL 2008R2 (where the original script worked) returns different fields than 2012 and 2014 (where it didn’t).
I figured I didn’t want to find out which version of the script to use every time I needed to run it on a server, so I told the script to figure that out by itself, and then run the appropriate hunk of code (example below)
This is a good explanation of how to back out of a complex situation.
You can change the compatibility level of an Azure SQL Database.
It’s true! I know!
OK, so I’m a little excited about this one. See, I’ve been giving this talk on cardinality for the past couple of years now, so this is a hidden gem to me. When I found out this was possible I took out my demo scripts to see if changing the compatibility level would have any effect.
This is interesting, especially given that Management Studio doesn’t give you that option. Know your T-SQL, folks.
So here’s the official word: The attached source code is hereby released to the world, copyright and royalty free. You may use it, if you like, for whatever you want. Enjoy! If you use it for a public project, I would appreciate a mention in the acknowledgements section, but even that is not required. This source code is yours, warts and all. I was tempted to do some cleanup work, but at this point it’s just not something I’m ever going to touch again. I upgraded the project from Visual Studio 2005 to Visual Studio 2013, confirmed that it builds and seems to work, and that’s that.
Adam may never have used in a production scenario, but I certainly have, and SQLQueryStress is still the best free load simulator. There’s also a GitHub repo thanks to Erik Ejlskov Jensen, so go forth and hack at some C# code.
Anchor Modelling moves you beyond third normal form and into sixth normal form. What does this mean? Essentially it means that an attribute is stored independently against the key, not in a big table with other attributes. This means you can easily store metadata about that attribute and do full change tracking with ease. The historical problem with this methodology is that it makes writing queries a real pain. Anchor Modelling overcomes this by providing views that combine all the attribute data together.
However, it seems that there might be two kinks in the line:
The first kink occurs somewhere between the 800m distance and the mile. It seems that the sprinting distances (and the 800m is sometimes called a long sprint) has different dynamics from the events up to the marathon.
The analysis is done in R, and the code is available in the post. Check it out.
Recently I needed to apply compression data on a particularly large table. One of the main reasons for applying compression was because the database was extremely low on space, in both the data and the log files. To make matters worse, the data and log files were nowhere near big enough to accommodate compressing the entire table in one go. If the able was partitioned then I could have done one partition at a time and all my problems would go away. No such luck.
Best way to eat an elephant, etc. etc. Read the whole thing; you might be in a similar situation someday.
Specifically, how is it evaluated when your where clause says “WHERE This AND That OR Something AND that”, without any clarifying parenthesis?
Let’s play around with this. The simplest test scenario is a SELECT 1. If I get a 1 back, that means my WHERE clause evaluated to true, right? Right.
Parentheses should clarify statements. If I see an “AND” and an “OR” in a WHERE clause, I want to see parentheses, even if you’ve gotten it right. It’s too easy to misinterpret precedence.
Every tsql command in your SQL script(s) has the potential to fail. It’s important to catch and handle tsql errors so that they don’t cause the entire installation to fail. This will require a lot of defensive, resilient, fault-tolerant coding on your part. Here’s an example for creating the database. Note the emphasis on permissions, which I touched on in another post.
This is important advice if you send installation scripts to customers (even if you’re using a packager to generate an install EXE).
To view the output from extended events you can open the .xel file in Management Studio or query the data using the sys.fn_xe_file_target_read_file function. I typically prefer the UI, but there’s currently no great way to copy the blocking report text and view it in the format you’re used to. But if you use the function to read and parse the XML from the file, you can…
If you can’t buy a tool which monitors long-term blocking, you can still build it yourself pretty easily.