Feb 282018
 

Months are funny. Unlike other parts of a date, they vary in length:

  • The last second of a minute is always 59.
  • The last minute of a hour is always 59.
  • The last hour of a day is always 23.

But the last day of a month? Well that depends on what month it is. And the year matters too because a leap year means February gets an extra day.

This used to be somewhat tricky to calculate, but then for SQL Server 2012 Microsoft added the EOMONTH() function (End Of MONTH) to T-SQL to help with this. EOMONTH() takes a DATE or DATETIME, and it will return the DATE (with no time component) of the last day of that month. Here’s a basic example:

 

It also accounts for leap years. Let’s try a date in February 2016:

 

What about other months?

In the case that you need the last day of a different month than the current one, you can make use of EOMONTH()‘s second parameter. This allows you to add (or subtract) months from the date you specified:

 

 

What if I need the first day of the month?

There’s no similar function to calculate the first day of the month, but think about it: the first day of the month is very predictable. It’s always day 1. You can also use EOMONTH() to find the first day of a month by adding one day to the last day of the previous month:

 

 

What if I don’t have SQL Server 2012?

If you’re still running SQL Server 2008R2 or older, my first piece of advice is to please look into upgrading to something more current soon. As of this writing, SQL Server 2008R2 Service Pack 3 is only supported until 9 July 2019. If you’re not running Service Pack 3, you are already unsupported.

Without the EOMONTH() function, calculating the end of the month gets a bit more complex. There are several ways to do it in T-SQL, but this is my preferred method:

 

This works by utilizing some creative math with dates. I’ve highlighted the different sections of the statement to explain what’s happening:

What if I don’t have a computer?

I’ve got a solution for this one too! Ever heard of the knuckle trick?

Make two fists and put them next to each other so your knuckles are lined up. Each high and low point along your knuckles represents a month, and the high points represent months with 31 days. Low points are months with less than 31 days (28/29 for February, 30 for the rest).

Source: Wikipedia

Feb 132018
 

SQL Server Mirroring has had a rough time the past few years. It was deprecated in SQL Server 2016, and now it’s more or less being replaced by Basic Availability Groups, (which I love to refer to as “BAGS”). Database Mirroring hasn’t gone the way of the Dodo yet though. Perhaps it won’t be quite so popular in new deployments anymore, but there’s plenty of existing ones out there. For this reason I don’t expect Microsoft to remove the feature for at least a few more years.

A few weeks ago I had a reminder of one of the finer points of the requirements for mirroring: mirrored servers need to be running not only the same version of SQL Server, but the same edition as well.

I hadn’t thought of this in a while, but it makes sense. Asynchronous Database Mirroring (also known as “High-Performance Mode”) is only available in Enterprise Edition, while Standard Edition only supports “High Safety Mode”, which is synchronous. If the primary server in a mirroring topology was Enterprise Edition, but the mirror server was Standard Edition, how would that work? What if the Enterprise Edition server had features enabled that don’t exist in Standard Edition? One way might be to disable features where conflicts occur, but that would probably lead to more issues as well as confusion. The simple solution is “just don’t let that happen” and that is what Microsoft opted for here. If you try to configure mirroring between instances running different editions of SQL Server, you won’t get very far before you see this:

Microsoft makes no secret of this in their documentation. The second listed prerequisite is “The two partners, that is the principal server and mirror server, must be running the same edition of SQL Server. The witness, if any, can run on any edition of SQL Server that supports database mirroring.”

So to sum things up, you may never have to touch a mirrored database again, but if you do, remember that editions matter!

UPDATE: While editions must match, it should be noted that versions of SQL Server need not match, and this exception can be particularly useful for using mirroring to perform upgrades, as mentioned in this blog post by Glenn Berry. However once you failover a mirroring session to a newer version of SQL server, failback to the older version is no longer possible. This is perhaps why Microsoft states in their documentation that versions must match – they will need to for any kind of long-term arrangement.

Jun 222016
 

 

SQL Server 2016 is upon us, with all the much-anticipated hoopla and sexy new features people have been lusting over in the CTPs for months. It’s always great to see the hype around a new release. But in the circle of (an application’s) life, the arrival of new things often means others are going away for good. Let’s pause for a moment and reflect upon the two features that, as of SQL Server 2016, are no longer with us:

32-bit SQL Server. SQL Server 2016 is 64-bit only. If for whatever reason you’re running on a 32-bit architecture, sadly you’re now out of luck – 2014 is the end of the road. On the bright side, there’s probably some new hardware in your future!

Compatibility Level 90. If you’re using compatibility level for backwards compatibility, the oldest available version in SQL Server 2016 is 100, which corresponds to SQL Server 2008. Compatibility level 90, SQL Server 2005, is no longer an option.

Hopefully these changes didn’t catch anyone off guard. To help better prepare for the removal of features in future versions, Microsoft maintains a list of deprecated features in the next version of SQL Server. Here’s a few highlights of what’s most likely going away in SQL Server vNext:

Backup/Restore WITH PASSWORD. This one has been bad news for a long time. Stop using it years ago! If you didn’t, stop using it TODAY :)

Encryption with the RC4 or RC4_128 Algorithms. Better options have been available for a while, hopefully you’re using them for any new development at this point.

Remote Servers. Architect them out. If you really can’t, use linked servers instead.

SET ROWCOUNT. I doubt this one will ever really go away, but it’s nice to dream. The TOP keyword has been available for quite some time now.

HOLDLOCK table hint (without parenthesis). This one’s easy, put HOLDLOCK in parenthesis. Like this: (HOLDLOCK). See? It’s easy! Start doing it!

Database Safety Bear says: “Don’t delay, start planning for deprecated features TODAY!”

Dec 032015
 

I’m already at peace with the fact that I’ll never know all of SQL Server’s secrets, but that doesn’t stop me from being surprised every time I figure out something new. In this case, it’s another “secret” hiding in plain sight (Books Online).

It turns out that the DROP DATABASE statement doesn’t just have to drop one database. BOL shows that multiple databases can be specified when separated with commas. Let’s see it in action.

First, create 4 databases:

Now drop them:

Yep, all gone.

But what if there’s an error? Re-run the CREATE statements above, but now let’s drop 5 databases instead of 4. DB4 doesn’t exist (much like Terminal 4 at Chicago’s O’Hare Airport).

The above statement will throw an error that it is unable to drop DB4 because that database doesn’t exist, but the other 4 databases are dropped without incident. The same will happen if a user is connected to one of the databases: that one will remain, but all others will be dropped.

So there you have it: you can drop multiple databases with a single statement in SQL Server. (According to Books Online this does not work in Windows Azure SQL Database.) It’s amazing the things you can learn from reading documentation!