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.
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.
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
I have a client who’s publishing their tabular data models to Azure Analysis Services (AAS). They want to publish a table that’s visible, but only has a single column that’s hidden.
You might wonder why he wanted to do that.
He’s trying to have a table with no existing columns that’s an anchor point for report designers to attach their report-specific measures. There are measures and computed columns in the tabular data model in AAS. But he wants to have a known location for measures that are only related to the specific report.
2020-01-24
In a recent forum post, the OP was asking about an issue where he had two tables “users” and “userroles” and was getting the following error when he tried to insert rows into the “userroles” table:
Msg 4406, Level 16, State 1, Line 1 Update or insert of view or function ‘UserRoles’ failed because it contains a derived or constant field.
Now the first thing that was odd about this was that the error message was clearly showing that UserRoles wasn’t a table. It’s either a view or a table-valued function (TVF). An insert into a TVF isn’t going to work, so I presume this is a view.
2020-01-23
As part of our free SDU Tools for developers and DBAs, we have a function that generates dimension columns for individual time periods. To make that easy to use across a range of times in day, we’ve added a function called TimePeriodsBetween.
It’s a table-valued function that takes three parameters:
@StartTime time is the first time to return @EndTime is the last time to be returned @MinutesPerPeriod is the number of minutes in each time period for the day (e.g. it’s 15 for every quarter of an hour)
2020-01-22
