The Bit Bucket

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

I regularly enter passwords into websites, and am told after I’ve entered a new password, that I can’t use any special characters.

Why exactly?

If I see a site that won’t deal with special characters properly, it immediately makes me think there’s some pretty poor coding going on under the covers. Very likely, the developers haven’t thought through how the parsing of requests, etc. should be handled.

It’s not just special characters either. Requiring short passwords is another red flag.

2020-01-21

SQL Server has a rich set of data types. Some of them were older data types and a number of new ones were introduced in SQL Server 2008.

One of the important ones was the date data type. It’s just a date. It has no time associated with it and it’s from the year 1 to the year 9999 based upon the Gregorian calendar. Well, they say the Gregorian calendar, even though that’s funny because they was no Gregorian calendar in the year 1. Regardless, for the range of dates that we would work with, this will always work fine.

2020-01-20

Overall the SQL language for Snowflake is somewhat of a subset of what’s available in SQL Server with T-SQL. But there are some commands that really are useful. UNDROP is one of them.

I’ve talked in earlier posts about how Snowflake stores data in immutable micropartitions, and in other posts mentioned the concept of timetravel. Well, an added advantage of having older data still accessible is that you can quickly recover from “incidents”.

2020-01-17