The Bit Bucket

I mentioned in my last two SDU_Tools posts that I get really good feedback all the time from users of our free SDU Tools for developers and DBAs, but none more than when we have more and more date and time functions. Yet another pair of functions that we added in v19 that was recently shipped was StartOfYear and EndOfYear.

We had feedback that even though we had StartOfFinancialYear and EndOfFinancialYear, we didn’t have just StartOfYear and EndOfYear. So we’ve fixed that. These are simple scalar functions that take a single date parameter, and return the date for the start of the calendar year and the end of the calendar year that contain the date provided.

2020-04-29

I’ve had a number of people asking me lately about what’s happening with SQL Server certifications. There are a number of clients that I deal with, where they require their staff to regularly update their certifications. It’s been part of their ongoing commitment to training.

In particular, I’m asked When will the new SQL Server certifications be available?

And the (perhaps surprising for some) answer is: They won’t be.

The way that Microsoft certifies people has gone through a seismic shift. The entire focus is now on certifying people’s ability to perform roles, rather than their ability to use a particular product.

2020-04-28

One of the challenges when you go to write dates is that there’s no standard format in SQL Server for how to write a date. Some other languages have a specific format for writing dates. In T-SQL, we have to write it as a string.

Because there’s no special way to write dates, it’s very important to come up with a format that will work properly all the time. The example I’ve got here for OrderDate is 20190128. That is a safe format that always works. SQL Server will interpret it as YYYYMMDD every time, no matter what regional or language settings you have configured.

2020-04-27

In the past, I was an unashamed fan of the Dilbert cartoon strip. In recent years though,  the author (Scott Adams) has become a pretty divisive character in and around US politics and life in general. So I was intrigued by his book Win Bigly.

Nowadays, I keep hearing many people refer to him as being very pro-Trump and in the opinion stakes, that immediately cuts him off from half the US population, and the majority of the world.

2020-04-24

I remember when SQL Server 7 was released, it was such a big deal that you could easily grow and shrink database files, and indeed, you could have databases auto-grow, and, auto-shrink. That seemed pretty appealing if you wanted a low maintenance solution.

If only life was so simple.

One of the real challenges of auto-grow and auto-shrink is that you could have a database constantly changing size up and down, and that’s quite nasty, because each time you grow, you’re often using a different part of your file system.

2020-04-23

I mentioned in my last SDU_Tools post that I get really good feedback all the time from users of our free SDU Tools for developers and DBAs, but none more than when we have more and more date and time functions. Another pair of functions that we added in v19 that was recently shipped was StartOfWorkingWeek and EndOfWorkingWeek.

These are simple scalar functions that take a single date parameter, and return the date for the Monday and the Friday of the week that contains the date provided.

2020-04-22

I was working with another client recently, and they were changing the working IT environments for their staff. What struck me as odd was that they called the new environments the Modern ones.

Modern was actually the name of the environment. I’m sure they currently see the environments as the modern ones, but soon enough, it won’t be modern or new, and then the name looks really, really odd. In a few years’ time, they’ll have more recent ones, and it then gets tricky. Are they then the Even More Modern environments?

2020-04-21

While there are some other data types that I’d like to see added (for example a month data type), SQL Server has quite a rich set of date and time data types. Some of these were older data types and some new ones were introduced in SQL Server 2008.

An important one that was added in 2008 is the date data type. It’s just a date. It has no time associated with it and it covers year from 1 to 9999. It says that it’s based on the Gregorian calendar (i.e. our current calendar) but that’s a bit ambitious given it didn’t exist back in the year 1. But for the range of dates that we would work with, this will always work fine.

2020-04-20

I posted the other day that I don’t like negative ranges used for IDENTITY columns. I feel the same about the range of values for sequences, but when I mentioned that I prefer using sequences, a few friends asked why I preferred them.

Let me start by saying that I was surprised with the previous post, that the strong responses were about using natural vs surrogate keys. That had nothing to do with what I was discussing, and is an endless topic for another day. The issue was about when someone has already decided to use an IDENTITY or a sequence. And so, the same point applies to this discussion.

2020-04-17

This week, one of my colleagues was complaining that he had to do work on a database where every table had identity columns, and they all started at large negative values.

I remember some people promoting this years ago, to double the available numeric range for the data types, rather than just using positive values.

I didn’t like it when I saw it then, and I still don’t like it.

2020-04-16