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.

Now it's saying that it contains a derived or constant field. I have no idea what the table was actually called, so let's create one to try this:

Then an insert should be fine:

Running it returns the two rows inserted as expected.

How let's add some derived things. First, I'll add a derived column to the view and try the insert again:

Adding a derived column in the view works just as expected. What about adding it to the table instead?

I've set the view so that it doesn't use that derived column, and all is still good. But what if the view references the derived column?

And that's all good too. So what does that leave us?

Well a common view definition that I often come across is where the view returns data from a table, along with a constant row. Perhaps it's a "superuser" or something in this case. So let's try that:

And there's the error:

Msg 4406, Level 16, State 1, Line 78
Update or insert of view or function 'dbo.UserRoles' failed because it contains a derived or constant field.

You can't insert through a view when you have only this type of setup.

 

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.

And that's often the problem that causes the above issue.

Trusting the Server

But what if you just want to trust the certificate that was self-signed by the server? Well there's an option for that (Trust server certificate), in the Options section of the connection dialog:

Chances are that if you just check that box, you'll then be fine.

Note that there's also an option to turn off encryption (by unchecking Encrypt connection). While it would also "fix" the issue, that's not the best option to choose here.

And I'm posting this so that one day in the future when I forget what this was about, I'll find this post.

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.

You can use our tools as a set or as a great example of how to write functions like these.

Find out more

You can see it in action in the main image above, and in the video here:

 

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

 

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.

For a 16 bit computer, and it's 65,536 operations, again it's the blink of an eye.

For a 32 bit computer, and it's 4,294,967,296 operations, on the machines of the time, it was around 2 to 3 minutes.

But for a 64 bit value, on the fastest machine on the planet at that time, it was 350 years.

And for a 128 bit value (like a GUID), the size of those numbers is outside our ability to comprehend in any meaningful way. (That's why they picked that size).

Current Day

Now computers have become quite a bit faster but based on the fastest MIPS value on Wikipedia's page on it today, that's still around 2 years. Yep, that's two years of doing nothing but just adding one, and on a staggeringly fast machine.

Many years back, I remember someone from the product group telling me they'd had the first customer who'd exceeded bigint for an identity column. But no surprise, crazy allocation of values was involved.

Business users are never going to run out of bigints for sane processes, unless they do something dumb like big seed or increment values.

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.

Note: the documentation for this function says "The behavior of the TRANSLATE function is similar to using multiple REPLACE functions. TRANSLATE does not, however, replace a character more than once. This is dissimilar to multiple REPLACE functions, as each use would replace all relevant characters." However, whenever I test it, it does multiple replacements.

If you want a function that replaces all occurrences and/or if you are using a version of SQL Server prior to 2017, take a look at our Translate function in our free SDU Tools for Developers and DBAs. It does just that.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.