The Bit Bucket

SQL Server Reporting Services (SSRS) is a wonderful product, that’s still really important, even though the product group in Microsoft hasn’t been showing it much love lately.

I was at a client site the other week, and while we were using Power BI (PBI) for their dashboards and general visualizations, we were looking to use SSRS for part of the solution.

What fascinated me, is that when they talked to the local Microsoft field staff, they kept being told how SSRS was old technology, and asking why they’d look to use such old technology.

2020-02-11

Sometimes we have a date and we need to extract components of the date. For example, we might have a date and wonder what’s the year or we have a date and wonder what the month is or the day.

While there are several ways to extract the year, the month, and/or the day from a date in SQL Server, there are functions designed to do precisely that.

The YEAR function takes a date and extracts the year. The MONTH function takes a date and extracts a month (as a month number from 1 to 12). And the DAY function takes a date and returns the day (again as a day number like 28, not the name of the day like Tuesday).

2020-02-10

I haven’t been doing as much driving in my car lately, so that’s limited the time I’ve had for listening to audio books. But one that I did complete recently was AI Super Powers by Kai-Fu Lee. The subtitle is China, Silicon Valley, and the New World Order.

This is an area that I’ve been really passionate about for the last few years.  I can see AI changing so much of our current world, and much sooner than I think most people will realise.

2020-02-07

I just wanted to give everyone a heads-up that we recently shipped version 18 of our free SDU Tools for developers and DBAs.

We’ve had so many requests for a version of SDU Tools that will work with Azure SQL Database. So, in version 18.0, we added that support.

So what’s changed?

We’ve added two new scripts included in the download: one for adding the tools to Azure SQL DB and one for removing them.

2020-02-05

Most developers and analysts today are fairly aware of the impacts of technical debt. As technical debt grows, it takes longer and longer to get real customer or end-user work done. Worse, more and more time is spent triaging and squashing bugs. And one interesting aspect of technical debt is old tooling.

Old Tooling

I’ve written before about modern not being a synonym for better, but there comes a point where you need to modernise your tooling, even if it seems to be doing the job.

2020-02-04

Finding out the current time is a really common requirement. But you also have to always consider whose concept of time you’re considering. Is the the time at the server, or the time where you are? (You might not be in the same place, or more importantly, the same timezone).

The function SYSDATETIME() returns the current system date and time right now at the server. The data type that’s returned is a datetime2.  It’s the higher precision datetime data type that was introduced in SQL Server 2008.

2020-02-03

I’ll start this post by mentioning that there’s a general consensus that you should avoid columns in tabular data models that have both dates and times. That’s largely because they don’t compress well. However, sometimes you really do need to have both a date and a time in a single column.

For example, if you want to sort one column by the date and time, we have no option to sort one column by two other columns. And if you’re going to create a composite column to get around that, you’re really back in the same problem as storing a datetime anyway.

2020-01-31

As part of our free SDU Tools for developers and DBAs, we have many scripting functions. SQL Server allows you to create your own database roles, and generally, you should do this instead of using the fixed database roles like db_owner, or db_datareader and db_datawriter, as it lets you allocate just the required permissions. To allow scripting these out, we’ve added a tool called ScriptUserDefinedDatabaseRoles.

It’s a stored procedure (as it has to change databases) and takes a single parameter: @DatabaseName (sysname) as the name of the database whose roles you want to script.

2020-01-29

Once again, I’m seeing lots of customers reporting screen repainting issues in SQL Server Management Studio (SSMS). It mostly seems to affect version 18 but I’ve also seen it in version 17. And it’s most prevalent on Windows 10.

The typical issue is that you click on another open tab, and the contents of the tab doesn’t repaint. You are still seeing the previous tab. If you click into the tab, you start to see bits from both tabs.

2020-01-28

Now, one of the challenges when you go to write dates is that there’s no standard separate format in T-SQL for how to write a date. Instead, we need to write it as a string. So it’s very important to come up with a format that will work properly all the time the example.

In the example I have here, the order date is 20190128. If you use that format (8 digits for a date), it’ll always work OK. SQL Server will take the first four as the year, then 2 for the month, 2 for the day and it does that, no matter what your settings are for your session or for your machine.

2020-01-27