This month’s T-SQL Tuesday is hosted by Brent Ozar, who asks us to Blog about our Favorite Data Type (or Least Favorite).
I find it actually quite easy to decide both on a most-favorite, as well as a least-favorite data type in SQL Server. So, why not blog about both?
This month’s #tsql2sday came to us from Rob Volk (b|t), who asks us to explain databases using an analogy, as if explaining to a 5 year old. I’m actually a big fan of The Feynman Technique (aka ELI5), so I really wanted to participate. But to be honest, I nearly missed out this time simply because I couldn’t think of an idea this whole week.
On the very last day, when the posts already started rolling out by all the bloggers, I’ve read a few, and only then the muse finally hit me. I kid you not, the time is literally 23:59 here in Israel as I hit the publish button!
So anyways, the idea I had was for the ALTER TABLE..SWITCH TO command in SQL Server.
Read More »T-SQL Tuesday #131 – Star Trek Candy and SWITCH TO
You might have seen this blog post by Paul White: New Metadata-Only Column Changes in SQL Server 2016. It talks about how, when a table is compressed, you can change (i.e. increase) a column’s data type without having to create a new table and migrate the data into it, and without having to refactor all of the underlying data. But, not everything is rainbows and unicorns…
Read More »The new “metadata-only column changes” feature in SQL Server 2016 is useless!
Suppose we’re running one or more dynamic SQL commands in SQL Server, and we’re getting an error from somewhere within it.
But, to make things interesting, let’s suppose that we’re running a large number of such dynamic SQL commands within a loop, each constructed a bit differently, and we’re finding it difficult to pinpoint which of the dynamic commands is causing the error, and which SQL statement it is exactly.
Could we, perhaps, print the relevant code section based on the error line reported in the CATCH block?
In this article, we will show you how we implemented a little system to create generic business data based alerts, and show them on Zabbix when needed.
In the first part, Snir will focus on the generic process that will deliver the data you want to Zabbix in case of a problem, and Eitan will explain the steps that need to be taken on the database end of things.
First of all, I must apologize for the long hiatus since my last published content.
I’ve been busy working on a lot of very different stuff, and unfortunately this delayed me from actually completing any one particular thing.
One thing that I did complete, though, was preparing and submitting a webinar for the PASS Global Hebrew Virtual Group.
The webinar is called “Advanced Dynamic Search Queries and How to Protect Them”, and I will be presenting it on Tuesday, April 30th 2019, 13:00 UTC (15:00 Israel Time).
If, for whatever reason, the Windows Computer Name is changed after SQL Server is already installed, then @@SERVERNAME and the information in sys.servers would not automatically reflect the change (sysservers in older SQL versions).
This means that @@SERVERNAME contains the incorrect value for the machine name.
Sometimes, and especially in production environments, the value in that global variable is important and is used as part of business processes.
And if @@SERVERNAME doesn’t reflect the actual server name, it could cause problems.
Alternatively, it’s possible (and maybe even best) to use the SERVERPROPERTY function instead to get the actual server name, or machine name, or instance name. The information available through this function should be up-to-date even after you rename the Windows Computer Name.
Imagine the following: You have a couple or more SQL Servers with some sort of High Availability solution between them (AlwaysOn Availability Groups, Database Mirroring, etc.). You also have a bunch of scheduled jobs which you need to be run on the Primary server.
But wait…. How would you make these jobs run only on the Primary server? After all, if they try to run on the Secondary, they would fail (whether because the database is inaccessible or because it’s read-only). Additionally, you would need to consider the possibility of a failover where the Primary and Secondary servers would switch roles.
This means that you would have to, first, create these jobs on both servers, and implement some sort of mechanism that would detect, for each job, whether the instance it’s being executed at is currently the Primary.
There are a few ways to go about doing this.
[For the sake of this article, let’s ignore the fact that most people don’t even think about it, and leave all of their important jobs on the Primary server only, while ignoring the risks of what would happen when their Primary server crashes and fails over to the Secondary]
Sometimes, when you have a table with an IDENTITY column, there could be scenarios in which weird “gaps” are created between different IDs.
There can be several possible causes for this:
1. The most obvious cause is when rows are deleted from the table. If many rows are deleted from a table with an IDENTITY column, it’s obviously expected that nothing would “fill” up the “gaps” that these rows have left. IDENTITY values only go one way, they don’t automatically re-fill deleted values retroactively.
2. When a ROLLBACK is performed on a transaction after inserting into a table with an IDENTITY column, the increase in the IDENTITY value is NOT rolled back. So even if the row wasn’t actually inserted, the IDENTITY value is still increased. This can happen both with single-row INSERT commands, as well as BULK insertions. So if, for whatever reason, a lot of insertions are rolled-back in your database, you may see a lot of these “gaps”.
3. There’s a special mechanism, specifically in SQL Server 2012, which “pre-allocates” IDENTITY values for a table, and it does this in memory. So when the SQL service is restarted, next time you insert a value into the table, the IDENTITY value would “jump” by 1000 or 10000 (depending on the column data type). This happens in SQL 2012 only, and was reportedly fixed in later versions. More info about it in this blog post by Ahasan Habib.
In my previous post, Advanced Service Broker Sample: Multi-Threading, I showed a rather advanced scenario where we can implement a multi-threading solution inside the SQL Server database engine.
In this post, I hope to show a simpler scenario of using Service Broker in SQL Server.
This time, I’ll start by handing out the API script itself and give brief explanation on how to use it.
Then, if you’re interested, you may continue reading for further explanations.