SQL: Update or insert of view or function 'xxx' failed because it contains a derived or constant field

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.

 

Opinion: Why no special characters in passwords? Are you a target?

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.

And if you're still using complexity rules (like at least one upper, one lower, one numeric, etc.), read the NIST recommendations on this:

NIST's new password rules – what you need to know

Attack target?

If your website won't allow special characters in passwords, or reasonably long passwords (like a passphrase), it's an indication of poor coding, and it also makes you look like a potentially good target for attacks.

It certainly doesn't make your company look good.

Don't do this!

Learning Mandarin: Relative times for Years

I wrote in previous posts how Chinese refer to relative days , relative weeks and relative months in Mandarin. In this last post of this short series, we'll look at how they refer to years.

(Nián) is the word for Year. It's pronounced roughly like "knee-en".

今年 (Jīnnián) means "This year"

Years don't use up and down words like weeks and months. Instead:

去年 (Qùnián) means "Last year" (where Qù means "Go" – or in this case gone)

明年 (Míngnián) means "Next year" (where Míng literally means "Clear or bright")

Other periods are a bit different though. You don't double up the words like they do with weeks:

前年 (Qiánnián) is "The year before last". Qián usually relates to "in front of".

次年 (Cì nián) can be used for "The year after next", although there are alternatives to this.

Learning Mandarin

I'll write more soon on the best methods for learning Mandarin. If you want to get a taste for it in the meantime though, my current favorite site is iTalki, and my favorite teacher by far is Amy He. If you decide to try it, click here and it's cheaper for both you and me.

Snowflake SQL for SQL Server Users – UNDROP

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".

Ever have a sinking feeling when you've run a script that drops an object and suddenly realized you were connected to the wrong database or server?

If you've been in this industry for any length of time, I know you have.

Things go wrong, no matter how careful you are. So it's good to have an option to help you out.

can be used to bring back a table that's previously been dropped. By default, that works for up to 24 hours but it's also possible to have longer retention periods.

When things really go wrong

But it's not just tables that can be undropped. For the really adventurous, there are also:

And these commands are fast. We're talking milliseconds.

There's a psychological aspect to this as well. Having the ability to recover so easily is also useful for increasing the confidence of the people who are making the changes. (Note: I'm not suggesting that should make people more careless. It's just important to know there is a better fallback).

SQL: (SQL Server) The certificate chain was issued by an authority that is not trusted

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.

SDU Tools: Script Database Object Permissions in SQL Server T-SQL

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

 

SQL: Am I really going to run out of bigint values?

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.

T-SQL 101: #52 Using TRANSLATE to make multiple string replacements in SQL Server

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.

Learning Mandarin: Relative times for Months

I wrote in previous posts how Chinese refer to relative days and relative weeks in Mandarin. In this post, we'll look at how they refer to months.

(Yuè) is the word for Month.

这个月 (Zhège yuè) means "This month"

Similar to weeks, the words for up and down are used to make previous and next months:

上个月 (Shàng gè yuè) means "Last month" (where Shàng means "Up")

下个月 (Xià gè yuè) means "Next month" (where Xià means "Down")

Other periods are a bit different though. While you can double the ups and downs like we could with weeks:

上上个月 (Shàng shàng gè yuè) is "The month before last" or literally "up up month".

下下个月 (Xià xià gè yuè) can be used for "The month after next", even though Google translates it as "Next Month" (I think it's just wrong on that).

More commonly, I seem to hear these variations used:

前一个月 (Qián yīgè yuè) can be used for "The month before last" or "The previous month". Qián means "In front of".

下个月之后 (Xià gè yuè zhīhòu) seems pretty common for "The month after next". The zhīhòu part means pretty much "after that".

Learning Mandarin

I'll write more soon on the best methods for learning Mandarin. If you want to get a taste for it in the meantime though, my current favorite site is iTalki, and my favorite teacher by far is Amy He. If you decide to try it, click here and it's cheaper for both you and me.

Snowflake SQL for SQL Server Users – Sequences: The good and the not so good

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.

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:

  • START WITH = 1            (you can even leave out the equals sign)
  • INCREMENT BY = 1      (again the equals is optional)
  • COMMENT = 'some comment'

Snowflake syntax also supports IDENTITY(seed, increment) if you want to define that at a table level. That is like a single table sequence but might help in migrating SQL Server code.

What I do really like are the options for replacing or only creating if not exists:

We can't do that in SQL Server today.  (I've complained to the SQL Server team that these types of options don't exist for sequences).

Getting the next value for a sequence is easy enough:

Like SQL Server, there's no guarantee that you won't have gaps in your numbers that are allocated.

There is a table-level function called GETNEXTVAL() that takes the sequence name as a parameter. It's a one row table function that generates a unique value. It's used for precise control of sequence generation when many tables are involved.

The not so good news

First up, there's no concept of anything like currval in Oracle (current value), or @@IDENTITY, or SCOPE_IDENTITY() in SQL Server. The documentation argues that this is an intentional omission as it encourages row by row coding. Can't say I buy that argument.

The biggest challenge (by far) is that you can't reset the next value to be issued by either an IDENTITY or Sequence. This really needs to get fixed. Lots of people are complaining about it in the Snowflake Lodge (user forums) and it just isn't a good thing. Here's an example:

  • You define a table using an IDENTITY column or a Sequence, let's say for a primary key.
  • You need to load existing data into it. This works easily as there is no concept of something like SET IDENTITY INSERT ON/OFF. You can just insert.
  • Next row that gets added to the table will now probably have a duplicate primary key value added.
  • And as Snowflake also doesn't check primary keys (or most other constraints), you'll really just end up with multiple rows with the same primary key.

ALTER SEQUENCE does allow you to change the increment, but won't let you change the next value to be issued. (Or the seed).

You have to drop and recreate the sequence. And now here's the even-nastier part: if you drop the existing sequence, it doesn't stop you doing it, but the column that was using it still references it. So even when you recreate a sequence with the same name, your next insert will blow up with an object not found. (Again, people complaining about this in the Snowflake lodge).

This means that you can't add sequences as defaults to tables until you've finished loading all your data. That's just not sensible, and breaks all the rules about separating DDL and data. The DDL that you write for a table should not be dependent upon what data happens to be loaded into a table.

How could you then write DDL that's used in multiple places? (Unless you just always start with a gigantic seed value).

Opinion

This aspect of the product needs to be fixed. It's hard to believe it's so broken. Wish it wasn't so.