SDU Tools: Determining Leap Years in T-SQL with IsLeapYear

Is 2018 be a leap year?

Was 2000 a leap year?

Will 2100 be a leap year?

When I was a young student at school, we learned that leap years were every four years. Then as I got older, I learned that there was more to it than that.

It's important to understand that any calendar is just an approximation, and there is a lot more to calendars than most people realize.

For those that would really like to get some background, my favorite calendar-related site is Claus Tøndering's Calendar FAQ. It's a wonderful collection of details of calendars and their history. In addition, for those that are into coding, he has sample program code to determine things like when is Easter next year?

Currently the rule for determining leap years is:

  • Every fourth year
  • Except if the year is divisible by 100 but not by 400

So, to answer the questions above, 2018 is not a leap year, 2000 was a leap year, but 2100 is not a leap year. (It's the last one that usually surprises people).

Apparently the scientists are still discussing where or not there should be a 4000 year rule. Can't say that I care as I won't be here.

We added a simple tool to do these calculations:

You can see it in action here:

For more information on our free SDU Tools, please visit:

http://sdutools.sqldownunder.com

 

 

 

 

Shortcut: Using Colors to Avoid Running Scripts Against the Wrong Server

Everyone who's worked with SQL Server for any length of time, has had the experience of executing a T-SQL script, and then noticing, with horror, that they've just executed the script against the wrong server.

You know the feeling. It even happens at Christmas time, just when you were hoping to get away from work for a few days, or when you are the unlucky one who's doing on call work.

Many of these surprises would be avoided if there was something that gave you a visual clue that you were connected to the wrong server.

SQL Server Management Studio (SSMS) has had an option to color code connections to servers for quite a while. The solution isn't perfect and isn't as good as Mladen Prajdić's SSMS Tools Pack:

http://www.ssmstoolspack.com/

(If you're keen to pay for an add-in, I do recommend that one)

For many people though, the colorizing provided by SSMS is just enough. And it's easy to use.

When you open a new database connection, you can click Options:

This opens a larger dialog that allows you to specify a color for the connection:

I've chosen Red to warn me that this is a production server.

Then, you'll see the color bar at the bottom of each script window in SSMS:

 

 

 

 

SDU Tools: Show SQL Server Backup Completion Estimates

When you first start working with SQL Server, you're often working with smaller databases and every backup seems to happen quickly. SQL Server is fast at creating backups.

When you get to larger databases, backups start to take a while. Fortunately, backup is one of the commands that sets a percentage complete value that can be seen in the sys.dm_exec_requests system view.

And when the databases get even larger, the question quickly becomes:

When will my backup finish?

The good news is that we've built a tool to make that easy to work out.

One of the SDU Tools is ShowBackupCompletionEstimates. It shows you any currently executing backups, how complete they are, when they started, how long they've taken so far (in seconds), and when we estimate that it will complete. Because it can cope with more than one backup running at a time, we also added the last SQL statement that was executed in the session, to make it easier to work out which is which.

Here's an example of its output (wrapped for clarity):

You can see it action here:

For more information on our free SDU Tools, please visit this site:

http://sdutools.sqldownunder.com

 

Opinion: And One Column to Rule Them All

I work with a lot of SQL Server databases that are poorly normalized. One of my pet dislikes is the column to rule them all.

Here are simple tests:

If I ask you what's stored in a column and you can't tell me a single answer , then you've got a problem.

If you need to refer to another column to work out what's in the first column, then you've got a problem.

Here are some examples:

  • If you have a column (let's call it ObjectID) that sometimes holds a TeamMemberID, sometimes it's a CoachID, sometimes it's a TeamID, etc. then you have a design problem.
  • If you must refer to another column (let's call is ObjectType), to work out what's in the ObjectID column, then you have a design problem.

Instead of a combination of ObjectType and ObjectID, I'd rather see you have a TeamMemberID column that's nullable, a CoachID column that's nullable, a TeamID column that's nullable, etc. And at least there's a chance that you could one day even have foreign keys in the database, and some chance of integrity. (But that's a topic for another day).

One of the strangest reasons that I've heard for this was to "try to minimize the number of columns in the table". Please don't say that. No sensible person is going to ever exceed the limits.

Prior to SQL Server 2008, the limit for the number of columns per table was 1024.

It's hard to imagine what you'd use more than that for, but the SharePoint team asked to have that increased. Apparently, 10,000 columns wasn't enough, so we ended up with 30,000 columns per table now. I struggle to think about what type of design leads to that many columns but it's also why we got SPARSE columns and filtered indexes in that version. (Mind you, filtered indexes were a great addition to the product on their own, unrelated to SPARSE columns). Let's just leave that reason as "oh, SharePoint". Can't say I love their database design, at all.

But for the rest of us, limiting the number of columns in a table isn't a valid reason for messing up normalization, particularly when those columns are keys from other tables.

SDU Tools: LeftPad and RightPad in T-SQL (Right-align, Left-align)

Over the years, I've had a surprising number of questions on how to right-align a set of numbers using T-SQL.

The first concept that people seem to miss is that numbers aren't strings. So there's no concept of actually aligning a number, it's only a string representation of a number that can be right-aligned or left-aligned. Or how it's displayed in a client application.

But if you really want to create a string that has right-aligned numbers, then left padding of the number is what you want.

One of our free SDU Tools is LeftPad. (We also have RightPad).

Here's an example:

I've set NOCOUNT on first (just to clean up the output by removing rowcounts).

I've then called LeftPad using the word Hello (the value to be padded), then 10 as the total width, and an o as the padding character.

In the second example, I've padded the value 18 with zeroes.

And in the 3rd and 4th examples, I've right-aligned some decimal values.

As I mentioned earlier, there's also a RightPad function, which can be useful for creating fixed width output strings.

You can see them both in action here:

For more information on our free SDU Tools, visit here:

http://sdutools.sqldownunder.com

 

Opinion: Designing Databases to Minimize Damage During Application Intrusions

Intrusions into computer systems are happening all the time now. We need to address this issue as an industry, but it’s important to understand that the way we design databases plays a big role in the impacts that occur during intrusions.

If you don’t accept that you could have an intrusion, you are living in La La Land. (See https://en.wikipedia.org/wiki/Fantasy_prone_personality)

A bug in any one of the frameworks that you use, the code that you write, the protocols that you use, the operating system or hosting services that you use can potentially expose you to an intrusion.

So do we just give up?

No, what you need to ensure is that when an intrusion occurs, the damage or impact is minimized. We do this in all other industries. For example, people working in high locations don’t expect to fall but they (generally) make sure that if they do, while something nasty might happen, it won’t be disastrous.

I routinely see web applications and middleware that can access any part of a database that it wants. The developers love this as it’s easy to do. But it exposes you to major risks. If the application is trampled on, you’ve opened up everything.

I always want to put mitigation in place and to limit the damage.

If your plan is to have your application connect to the database as one user, and you make that user a database owner (db0), or a combination of db_datareader and db_datawriter, or worse, a system administrator; then you don't have a plan.

A better plan is this:

  • Create a schema for the application – let’s call it WebApp
  • In the WebApp schema, create only the views and procedures that define what you want the application to be able to do (ie: it’s basically a contract between the database and the application)
  • Create a new user (from a SQL login or, better-still, a domain service account) for the application to connect through.
  • Grant that user EXECUTE and SELECT permission on the WebApp schema (and nothing else)

Then if the application is trampled on, the most that it can do is the list of things that you’ve defined in that schema and nothing else.

We need to start building systems more defensively, and this is reason #82938429 for why I just don’t like most ORMs as they tend to encourage entirely the wrong behavior in this area. (Some let you do it better begrudgingly).

SQL: Concatenating Column Values as Strings in T-SQL (Using CONCAT and CONCAT_WS)

There were a number of new T-SQL functions introduced in SQL Server 2012. As I've mentioned before, I get excited when there are new T-SQL functions.

Some, I'm not so excited about. EOMONTH was in that category, not because of the functionality, but because of the name (wish it was ENDOFMONTH), and lack of symmetry (lack of a STARTOFMONTH or BEGINNINGOFMONTH).

One that I thought was curious was CONCAT. I thought "why on earth do we need a function to concatenate strings. I can already do that. But when I got into using it, I realized how wonderful it was.

The problem with concatenating values is that you first need to convert them to strings. CONCAT does that automatically with all parameters that you pass to it. (It takes a list of values as parameters and you must have at least two parameters). You can see here how I can mix data types in the parameter list:

All the values are implicitly cast to a string. OK, so that's a little bit useful, but still no big deal?

The really powerful aspect is that it ignores NULL parameters in the list. (The documentation says that it implicitly casts them to an empty string but based on discussions I've had with the product group lately, my guess is that it simply ignores any parameter that's NULL).

Now that's something that's much messier with normal T-SQL. The problem is that when you concatenate anything that's NULL with the + operator in T-SQL, the answer is NULL, no matter what the other values are:

But this handles it nicely:

But notice that we're still not quite there. It's a pain to need to specify the separator each time (I've used N' ' as a single unicode space). More of a pain though, is notice that I still have two separators between Tim and Taylor in the example above.

CONCAT_WS in SQL Server 2017 comes to the rescue for that. It lets you specify the separator once, and ignores NULLs in the parameter list, and importantly, doesn't add the separator when the value is NULL. That's a pain if you want to use the function to create a string like a CSV as output (and I wish it had been a parameter to this function) but for this use case, it's perfect.

 

 

 

 

 

 

Shortcut: Using Database Snapshots to Provide Large Unit Testing Data with Quick Restores

SQL Server databases have a reputation for being hard to test, or at least hard to test appropriately.

For good testing, and particularly for unit tests, you really want the following:

  • Database in a known state before each test
  • Database containing large amounts of (preferably masked) data (production-sized)
  • Quick restore after each test before the next test

For most databases, this is hard to achieve. The restore after each test means that a normal database restore can't be used. What I often see instead, is people using transactions to try to achieve this i.e. the process becomes:

  • Start a transaction
  • Run the test
  • Check the results
  • Roll back the transaction

In some situations, that works well but the minute that you start trying to test transactional code, things fall apart quickly. SQL Server doesn't support truly nested transactions. When you execute a ROLLBACK, it doesn't matter how deep this occurs, the outer transaction is being rolled back too.

One option that I think many people should consider is database snapshots.

Your testing mechanism becomes:

  • Create a snapshot of the database to be tested
  • Test against the original database
  • Check the results
  • Revert the database to the snapshot

Both creating a snapshot database, and restoring from the snapshot are very quick operations. The creation is always quick and the revert time depends upon how many pages were changed during the test. That's often not many.

Creating a snapshot is described here: https://msdn.microsoft.com/en-us/library/ms175876(v=sql.105).aspx

Reverting a database from a snapshot is described here: https://msdn.microsoft.com/en-us/library/ms189281(v=sql.105).aspx

 

 

 

SDU Tools: Find String Within a SQL Server Database (Did you mask your database properly?)

I've mentioned that I work on a lot of financial systems. Masking and trimming the production databases so they can be used in development is important, yet I see many issues with masking.

A few months back, I was working on a SQL Server database from a large financial client, and the database was supposed to have been masked. However, what they missed was that the XML metadata associated with images stored in the database still had the actual client's details. Any developer in the organization could have retrieved private details of all the members of the fund.

This just isn't acceptable.

I decided to add a tool to our free SDU Tools collection that would help with this. It's useful way beyond just checking masking though. It lets you look everywhere in a database for a given string. It checks a wide variety of data types that might contain the value and it automatically checks every column in every table.

So even if you are just wondering where a value is stored in a database, this might help. Here's an example:

The procedure is called FindStringWithinADatabase. It takes 3 parameters. The first is the name of the database (in this case WideWorldImporters), the second is the string to search for (in this case Kayla), and the third is a flag to indicate whether or not the full contents of the actual rows should be displayed as well (in this case no).

For checking masking, you can enter a name that you know is real and find everywhere that it exists within the database. Or if you are just running an application, and see a value and wonder where it's stored, you can use this too.

Here's what happens if you set that last parameter to 1 (to indicate that you want to see the rows):

After the same initial output, all relevant rows from each table in the first list are displayed.

You can see the tool in action here:

You can find more information on our SDU Tools here:

https://sqldownunder.com/sdu-tools

We hope you find it useful.

SDU Tools: New T-SQL Statement: STRING_SPLIT plus Split Delimited String Tool

I've mentioned that I love it when SQL Server gets new T-SQL functionality.

A useful function that was added in SQL Server 2016 was STRING_SPLIT. You can see it in action in the main image for this post.

It's another function that is great as far as it goes, but when I was a kid, this would be described as "nice try but no cigar".

It works, and it's fast, so what's missing. I think two things:

  • You need to be able to know the position of each value retrieved from the string.
  • You often need to trim the values as well.

So, what do we do if we need that, or if we aren't yet on SQL Server 2016 anyway?

We have a tool for that. The SDU Tool SplitDelimitedString does these things. It's implemented in boring T-SQL but it just works. I've seen attempts to do this via XML queries, and they are fast, but they also screw up with certain characters in the strings (like <, >, %, etc.). So I went for slow and reliable. Here's what it does:

The 3rd parameter indicates whether values should be trimmed or not. In this case, I asked for trimming.

You can see it in action here:

You can find out more about our free SDU Tools here:

http://sdutools.sqldownunder.com