{"id":84457,"date":"2019-06-01T01:42:31","date_gmt":"2019-06-01T01:42:31","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=84457"},"modified":"2019-06-01T01:42:31","modified_gmt":"2019-06-01T01:42:31","slug":"not-null-persisted-computed-columns-and-what-they-allow","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/not-null-persisted-computed-columns-and-what-they-allow\/","title":{"rendered":"NOT NULL Persisted Computed Columns (And What They Allow)"},"content":{"rendered":"

Ok, so I have been, and will mostly stay, in a bit of blog hibernation. I am super busy trying to finish up a rather large number of tasks (a few chapters in a book, 2 more SQL Saturdays, and as much theme park time as I can get in before the end of June, when I am having my knee replaced\u2026) But as I was working today I discovered something that I wanted to throw together a blog on because I doubt most of my readers will know about this feature, and I wanted to try it out to the fullest. I had not seen this before in the course of writing database design\/implementation\u00a0books, coding many databases, etc for 20 years (ok, more than 20 years, since my first book on SQL Server Database Design was 19 years ago!)<\/em><\/p>\n

This started out to be a quick, simple article, but it grew long to show all the interesting if possibly silly techniques that you can do with NOT NULL persisted computed columns.<\/em><\/p>\n

I have for a long time known that persisted columns can be used in UNIQUE constraints and indexes. They never needed to be declare as persisted, because the engine would persist them for you when the index was added. I regularly use PERSISTED computed columns because they are important for performance, especially when used in reporting. What I had no idea of was that they could be used with other types of constraints. I am not at all suggesting at this point that you would want to employ every one of the techniques presented, or even that they are all good ideas (in fact, one of these ideas I will present is kind of horrifying from a proper design standpoint.) The way I see it, things you can do with code are like tools you find in that big toolbox you inherited. You play around with them, get a feel for how they work, but mostly have no clue what they are good for until you run into that need.<\/p>\n

It is just good to know what “can” be done in code, and this is the point of my post today. Here are some tools you probably didn’t realize you had in your toolbox, and one day you may find a good reason to use them. (And ideally comment on my post with those ideas for the rest of us.)<\/p>\n

Note that the examples in this article (other than simple computed columns) only work with persisted computed columns, if you use a non-persisted type, you will receive the following error:<\/p>\n

Msg 8183, Level 16, State 1, Line 7\r\nOnly UNIQUE or PRIMARY KEY constraints can be created on computed columns, \r\nwhile CHECK, FOREIGN KEY, and NOT NULL constraints require that computed \r\ncolumns be persisted.<\/pre>\n
In the following block of code, I will create a table to use for the first set of examples.<\/p>\n
USE tempdb;\r\nGO \r\nCREATE TABLE MainTable\r\n(\r\n\tMainTableId int NOT NULL,\r\n\tValue  nvarchar(10) NULL\r\n);<\/pre>\n
Now say we want to make a computed column on the Value column, that upper cases the value. We might add:<\/p>\n
ALTER TABLE dbo.MainTable \r\n   ADD ValueUpper AS (UPPER(Value)) PERSISTED;<\/pre>\n
If you query the catalog, you can see that the column allows NULL values, which seems obvious since the source column allows NULL values.<\/p>\n
SELECT COLUMNS.COLUMN_NAME, COLUMNS.IS_NULLABLE\r\nFROM   INFORMATION_SCHEMA.COLUMNS\r\nWHERE  COLUMNS.TABLE_SCHEMA = 'dbo'\r\n  AND  COLUMNS.TABLE_NAME = 'MainTable'<\/pre>\n
This returns:<\/p>\n
COLUMN_NAME        IS_NULLABLE\r\n------------------ -----------\r\nMainTableId        NO\r\nValue              YES\r\nValueUpper         YES<\/pre>\n
The source column allows NULL values, so the computed does too. Let\u2019s add another ValueUpperFormatted, that is definitely NOT NULL because we will base it on a COALESCEd value.<\/p>\n
ALTER TABLE dbo.MainTable \r\n   ADD ValueUpperFormatted AS (UPPER(COALESCE(Value,''))) PERSISTED;<\/pre>\n
Even though this clearly could not allow NULL values, query the metadata, and you will see it still allows NULL values.<\/p>\n
COLUMN_NAME          IS_NULLABLE\r\n-------------------- -----------\r\nMainTableId          NO\r\nValue                YES\r\nValueUpper           YES\r\nValueUpperFormatted  YES<\/pre>\n
We can fix this by declaring that the column is NOT NULL in the creation of the computed column (this was the piece of information I did not realize before today, and I learned it because I noticed it in a script that RedGate\u2019s SQL Compare created.):<\/p>\n
ALTER TABLE dbo.MainTable DROP COLUMN ValueUpperFormatted;\r\nALTER TABLE dbo.MainTable \r\n    ADD ValueUpperFormatted \r\n         AS (UPPER(COALESCE(Value,''))) PERSISTED NOT NULL;<\/pre>\n
Check the metadata again, and you will see that ValueUpperFormatted now will not allow NULL values.\u00a0<\/p>\n
INSERT INTO dbo.MainTable(MainTableId, Value)\r\nVALUES(1, NULL);\r\n\r\nSELECT *\r\nFROM dbo.MainTable;<\/pre>\n
Which returns:<\/p>\n
MainTableId Value      ValueUpper ValueUpperFormatted\r\n----------- ---------- ---------- -----------------\r\n1           NULL       NULL       <\/pre>\n
Let’s clear out the data in the table:<\/p>\n
DELETE FROM dbo.MainTable;<\/pre>\n
And now, what if we define another computed column that clearly could be have a NULL result, but define it as NOT NULL;<\/p>\n
ALTER TABLE dbo.MainTable ADD ValueUpperNotNull AS (UPPER(Value)) PERSISTED NOT NULL;<\/pre>\n
This works, and the metadata indeed says the column does not allow NULL values, so if you try to add data to the table that has a NULL value:<\/p>\n
INSERT INTO dbo.MainTable(MainTableId, Value)\r\nVALUES(1, NULL);<\/pre>\n
You get what would be a bit of a head-scratcher (I am personally glad this is not how I learned of this feature, or it would have driven me a little nuts):<\/p>\n
Msg 515, Level 16, State 2, Line 87\r\nCannot insert the value NULL into column 'ValueUpperNotNull',\r\ntable 'tempdb.dbo.MainTable'; column does not allow nulls. \r\nINSERT fails.<\/pre>\n
On the other hand, making a computed column not allow NULL values is a fantastic tool for the database designer to know that the expression they have crafted does not match that they expected it to never output a NULL value (and in my opinion, it is better to catch these things BEFORE 2.5 million rows have been inserted that don’t meet the requirements you have set up.)<\/p>\n
Next, let’s add a check constraint our computed column. For this example, we are just going to make sure that the value in the table is a palindrome (because this is something that every data architect has come across at least one in their life, right?). So Value = REVERSE(Value);<\/p>\n
ALTER TABLE dbo.MainTable\r\n  ADD CONSTRAINT CHK_MainTable_ValueIsAPalindrome \r\n        CHECK (MainTable.ValueUpper = REVERSE(Value) \r\n               AND LEN(Value) > 1);<\/pre>\n
We don’t need to check for NULL values, if the Boolean expression is TRUE or NULL, the operation will succeed.<\/p>\n
INSERT INTO dbo.MainTable(MainTableId, Value)\r\nVALUES(1,'abcd');<\/pre>\n
This throws the following error:<\/p>\n
Msg 547, Level 16, State 0, Line 110\r\nThe INSERT statement conflicted with the CHECK constraint\r\n\"CHK_MainTable_ValueIsAPalindrome\". The conflict occurred in \r\ndatabase \"tempdb\", table \"dbo.MainTable\".<\/pre>\n
But the following code:<\/p>\n
INSERT INTO dbo.MainTable(MainTableId, Value)\r\nVALUES(1,'dcbabcd');<\/pre>\n
Succeeds. Using the UPPER value, lets this work with case sensitive data as well, so that might be useful. In reality, if you put anything semi-complex in the computed column, based on multiple other columns, it might be good to make sure that the output is in some allowed range. For example, say you had a complex CASE expression, you could have NULL as the default, and make the column fail on a NULL value, but that could be more confusing than using a CHECK constraint with at least a descriptive name to point out the invalid value.<\/p>\n
—————-<\/p>\n
Speaking of confusing, for fun, there are two other types of things we can do with a persisted computed column that are pretty interesting, if perhaps at least somewhat impractical.This is using one as a PRIMARY KEY or a FOREIGN KEY.<\/p>\n
In the following table, we will let the user enter an integer value for the base value of the key, then use a persisted computed column to format the value and use this as the PRIMARY KEY value. The datatype of a computed column can be interesting based on whatever the source data is, so we need to make the size exactly defined for when we associate the foreign key, hence the cast to nchar(6)):<\/p>\n
CREATE TABLE dbo.Type1 --generic, domain style data\r\n(\r\n\tType1Base int NOT NULL,\r\n\tType1Id AS CAST((N'1' +       \r\n                      RIGHT(CONCAT('00000',Type1Base),5)) \r\n                      AS nchar(6)) PERSISTED NOT NULL \r\n\t\t\tCONSTRAINT PKType1 PRIMARY KEY\r\n);\r\n--Then insert a couple of rows\r\nINSERT INTO dbo.Type1(Type1Base) VALUES (1),(2)\r\nGO\r\n-- and retrieve them:\r\nSELECT *\r\nFROM   dbo.Type1;<\/pre>\n
You can see that the data is entered and is formatted:<\/p>\n
Type1Base   Type1Id\r\n----------- -------\r\n1           100001\r\n2           100002<\/pre>\n
Finally, let’s do a really sort of scary example of a way to implement a FOREIGN KEY constraint on a column that contains values that could be for multiple tables. WARNING: This example feels completely useless as a “real world” technique to me. Using the toolbox analogy, this is akin to that proprietary wrench you got in a box of cereal when you were 10 and left it in the toolbox. Probably useless, but a tool, nonetheless.<\/p>\n
We will create another domain table for another type:<\/p>\n
CREATE TABLE dbo.Type2 --generic, domain style data\r\n(\r\n\tType2Base int NOT NULL,\r\n\tType2Id AS CAST((N'2' +       \r\n                      RIGHT(CONCAT('00000',Type2Base),5)) \r\n                      AS nchar(6)) PERSISTED NOT NULL \r\n\t\t\tCONSTRAINT PKType2 PRIMARY KEY\r\n);\r\n--Then insert a couple of rows\r\nINSERT INTO dbo.Type2(Type2Base) VALUES (1),(2)<\/pre>\n
So now, someone has mixed Type1 and Type2 data in a column, and you cannot change that about your data, but you want to validate it (and doing this in a constraint is usually better, plus, in the final output you will have the “proper” versions of the columns in your table as well.<\/p>\n
CREATE TABLE dbo.TypedData\r\n(\r\n\tTypedDataId int \r\n            CONSTRAINT PKTypeData PRIMARY KEY,\r\n\tTypeId nchar(6) NOT NULL\r\n);\r\nINSERT INTO dbo.TypedData(TypedDataId, TypeId)\r\nVALUES(1,N'100001'),(2, N'100002'),\r\n      (3, N'200001'),(4, N'200002')\r\nGO\r\nSELECT *\r\nFROM  dbo.TypedData<\/pre>\n
This returns the following:<\/p>\n
TypedDateId TypeId\r\n----------- ------\r\n1           000001\r\n2           000002\r\n3           100001\r\n4           100002<\/pre>\n
Since we know that the algorithm to tell the source values apart is based on a prefix (it might be another column, or maybe a more clever parsing system than 1 for Type1, also). We can add:<\/p>\n
ALTER TABLE dbo.TypedData\r\n\tADD Type1Id AS \r\n        CASE WHEN TypeId LIKE '1%' THEN TypedData.TypeId END   \r\n                                                   PERSISTED;\r\nALTER TABLE dbo.TypedData\r\n\tADD Type2Id AS \r\n        CASE WHEN TypeId LIKE '2%' THEN TypedData.TypeId END  \r\n                                                   PERSISTED;<\/pre>\n
Again, note: this is not a good design. Seriously. If you are making another human being do this because of how you implemented a databases, it is not a good thing.\u00a0 Next, add FOREIGN KEY constraints to the two columns:<\/p>\n
ALTER TABLE dbo.TypedData\r\n\tADD CONSTRAINT TypeData$References$Type1 \r\n        FOREIGN KEY (Type1Id) REFERENCES dbo.Type1(Type1Id);\r\nALTER TABLE dbo.TypedData\r\n\tADD CONSTRAINT TypeData$References$Type2 \r\n        FOREIGN KEY (Type2Id) REFERENCES dbo.Type2(Type2Id);<\/pre>\n
Now, through a bit of a roundabout manner, we are able to validate that the data in the TypeId column is either that of a row in Type1 or Type2 (additional work would be required to make sure that the value in TypeId follows the expected formats, for example, adding a check constraint that makes sure either Type1Id or Type2Id are not both NULL.<\/p>\n
ALTER TABLE dbo.TypedData\r\n\tADD CONSTRAINT CHKTypedData_ValidateTypeId \r\n           CHECK (Type1Id IS NOT NULL OR Type2Id IS NOT NULL);<\/pre>\n
Now you can test it out by trying to put in invalid data, such as a value prefixed with a 3.<\/p>\n
INSERT INTO dbo.TypedData(TypedDataId, TypeId)\r\nVALUES(5,N'300000')<\/pre>\n
This gives you an error in the check constraint:<\/p>\n
Msg 547, Level 16, State 0, Line 198\r\nThe INSERT statement conflicted with the CHECK constraint\r\n\"CHKTypedData_ValidateTypeId\". The conflict occurred in \r\ndatabase \"tempdb\", table \"dbo.TypedData\".<\/pre>\n
If the data is formatted correctly with a prefix of 1:<\/p>\n
INSERT INTO dbo.TypedData(TypedDataId, TypeId)\r\nVALUES(5,N'100006');<\/pre>\n
This gives you an error with the foreign key constraint. Prefix the invalid value with a 2, and you will see the error with the other FOREIGN KEY constraint. Put in a proper value:<\/p>\n
INSERT INTO dbo.TypedData(TypedDataId, TypeId)\r\nVALUES(5,N'200001');<\/pre>\n
Success. (Well, it works, you still are stuck with some really weird code to deal with!)<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
Ok, so I have been, and will mostly stay, in a bit of blog hibernation. I am super busy trying to finish up a rather large number of tasks (a few chapters in a book, 2 more SQL Saturdays, and as much theme park time as I can get in before the end of June,……<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[19684],"class_list":["post-84457","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/84457","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=84457"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/84457\/revisions"}],"predecessor-version":[{"id":84458,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/84457\/revisions\/84458"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=84457"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=84457"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=84457"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=84457"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}