The Bit Bucket

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

Are you trying to connect to a SQL Server instance and ending up with the error:

The certificate chain was issued by an authority that is not trusted

You aren’t alone.

SQL Server 2005 introduced authentication encryption (by default) in the SQL Native Access Client (SNAC). SQL Server will self-generate a certificate that’s then used unless you replace it with your own certificate.

If you do use your own SSL (Secure Sockets Layer) certificate for SQL Server, unless it’s a publicly trusted certificate, your client system will need to trust that certificate. Generally that means that you’ll need to list your own certificate authority (CA) as a trusted publisher on each of your client systems. Then that would work well.

2020-01-16

As part of our free SDU Tools for developers and DBAs, we have many scripting functions. Moving or recreating object-level permissions between SQL Server databases can be time consuming.  To allow scripting these out, we’ve added a tool called ScriptDatabaseObjectPermissions.

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 object permissions you want to script.

2020-01-15

I was in another discussion recently where someone was worried about running out of bigint values. I come across this regularly. A few months back, another customer was in the middle of changing all their bigint values to GUIDs as they were worried about running out of bigints.

People don’t seem to understand just how large a bigint really is. It’s certainly hard to imagine.

How big is a bigint?

I remember reading an article back in 1992. They said that if you got an 8 bit computer, put zero in a register, and made it loop on an instruction to increment the register value, you’d have 256 operations before it overflowed. That would happen in the blink of an eye.

2020-01-14

I previously mentioned that there were three functions for replacing values in strings. We saw REPLACE and STUFF. The other one is relatively new (SQL Server 2017) and it’s called TRANSLATE.

Here’s an example of how I can use it to change the format of a phone number:

The first parameter is the string to work on, the second parameter is a list of individual characters to replace, and the third parameter (which must be a string the same length as the second parameter), is the set of replacement characters.

2020-01-13

I’ve been working to convert our SQL Down Under sample databases like PopkornKraze across to Snowflake. One of the areas that I ran into real challenges with was sequences.

The good news

The syntax for creating and accessing them is basic enough.

CREATE SEQUENCE "SequenceName";

is enough.  (Note the double-quotes are mine as I don’t want the name auto-wrapped to SEQUENCENAME).

And there are some of the same options that SQL Server has:

2020-01-10