{"id":406,"date":"2008-03-12T19:42:41","date_gmt":"2008-03-12T19:42:41","guid":{"rendered":"\/blogs\/conor\/post\/Inserts-against-views-An-introduction.aspx"},"modified":"2013-01-01T19:24:21","modified_gmt":"2013-01-01T19:24:21","slug":"inserts-against-views-an-introduction","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/conor\/inserts-against-views-an-introduction\/","title":{"rendered":"Inserts against views – An introduction"},"content":{"rendered":"

Well, I spun the wheel of database topics I have here in my room, and today I think I’ll talk about updates and views…. specifically inserts through non-indexed views.  Since I haven’t blogged about this previously, I’ll start at the beginning.  There are many, many update topics, so don’t feel left out – comment if there’s an update topic that interests you and I’ll get to it.<\/p>\n

UPDATEs are challenging for different reasons than SELECT statements.  While a SELECT statement can have huge combinatorical challenges in terms of the number of different plan choices to consider, UPDATEs often have a relatively small number of plan choices but instead have a number of very difficult performance tradeoffs about which kind of plan tweaks will cause the plan to finish the fastest.  You can imagine that there is a labor tradeoff between making a system work really well for SELECT plan exploration and making a system that can handle all of the detailed tweaks for UPDATE plans – most things are neutral, but getting that last 10% of performance out in one area may impact the other’s ability to innovate.  So, balance is required on the part of the database implementor.<\/p>\n

So if I were building my first database engine and I wanted to update a base table (a “heap”), I may have the following:<\/p>\n

create<\/span> table<\/span> z1 (col1 int<\/span>, col2 decimal<\/span>, col3 nvarchar<\/span>(100));\r\ninsert<\/span> into<\/span> z1 (col1, col2, col3) values<\/span> (1, 2, 'i like cheese'<\/span>)<\/span><\/pre>\n
So if I implement my storage engine with a series of equally sized pages, I can probably figure out how to load each of them into memory and look for a place to store a record that’s a linearization of (1, 2, ‘i like cheese’) on disk.  <\/p>\n
Let’s start making things more complex.<\/p>\n
create<\/span> table<\/span> z2 (col1 int<\/span> primary<\/span> key<\/span>, col2 decimal<\/span>, col3 nvarchar<\/span>(100));\r\ninsert<\/span> into<\/span> z2 (col1, col2, col3) values<\/span> (1, 2, 'i like cheese'<\/span>)\r\n<\/span><\/pre>\n
So if I add a primary key, this is implemented in SQL Server as a clustered index.  This replaces the heap.  So, now to build my own storage engine that does this I’d have a B-Tree implemented and I would find the right place in my B-Tree to insert my new record.  I may need to split some pages to make things fit.  Here’s the plan in SQL Server:<\/p>\n
  |–Clustered Index Insert(OBJECT:([t1].[dbo].[z2].[PK__z2__357D0D3E03317E3D]), SET:([t1].[dbo].[z2].[col1] = RaiseIfNullInsert([@1]),[t1].[dbo].[z2].[col2] = [Expr1003],[t1].[dbo].[z2].[col3] = [Expr1004]), DEFINE:([Expr1003]=CONVERT_IMPLICIT(decimal(18,0),[@2],0), [Expr1004]=CONVERT_IMPLICIT(nvarchar(100),[@3],0)))<\/p>\n
This is nice because there is still just the one structure to manage.  I just load it up, insert my row, and I am a happy man.<\/p>\n
Now, I’ll blog some other day about what happens with multiple indexes and such – today I want to talk about inserts against views, as this has lots of nasty details in its implementation.<\/p>\n
create<\/span> view<\/span> v1 as<\/span> select<\/span> col1, col2, col3 from<\/span> z2 where<\/span> col2 between<\/span> 2 and<\/span> 100<\/span><\/pre>\n
ok, so I’ve added a view against z2 that shows a subset of the rows.  That’s not too bad, or is it?  Well, if I insert\/update the base table, my plan is the same as before, so that’s easy enough.  However, if I try to run an insert against the _view_, what should happen?  The view isn’t stored anywhere.  However, in this case, there is a single table underneath it and the rows from the base table can be “mapped” up through the view, so perhaps I could reverse<\/b> that operation and translate a request to update the view to be an update against the base table.  <\/p>\n
insert<\/span> into<\/span> v1 (col1, col2, col3) values<\/span> (1, 2, 'foo'<\/span>)<\/span><\/pre>\n
  |–Clustered Index Insert(OBJECT:([t1].[dbo].[z2].[PK__z2__357D0D3E03317E3D]), SET:([t1].[dbo].[z2].[col1] = RaiseIfNullInsert([@1]),[t1].[dbo].[z2].[col2] = [Expr1003],[t1].[dbo].[z2].[col3] = [Expr1004]), DEFINE:([Expr1003]=CONVERT_IMPLICIT(decimal(18,0),[@2],0), [Expr1004]=CONVERT_IMPLICIT(nvarchar(100),[@3],0)))<\/p>\n
Hey, that’s pretty neat – SQL Server performs an insert against the base table that looks very similar to what we saw before.  Very nice of them :).<\/p>\n
Ok, let’s talk about the case when the row doesn’t meet the filter:<\/p>\n
insert<\/span> into<\/span> v1 (col1, col2, col3) values<\/span> (3, 250, 'foo'<\/span>)<\/span><\/pre>\n
  |–Clustered Index Insert(OBJECT:([t1].[dbo].[z2].[PK__z2__357D0D3E03317E3D]), SET:([t1].[dbo].[z2].[col1] = RaiseIfNullInsert([@1]),[t1].[dbo].[z2].[col2] = [Expr1003],[t1].[dbo].[z2].[col3] = [Expr1004]), DEFINE:([Expr1003]=CONVERT_IMPLICIT(decimal(18,0),[@2],0), [Expr1004]=CONVERT_IMPLICIT(nvarchar(100),[@3],0)))<\/p>\n
Hrm.  Well, that seems to work too… The ANSI committee got this far when working on their ANSI SQL specification, and they added a neat little keyword “with check option” that allows inserts, updates, and deletes against views to make sure that the resulting row would continue to be exposed through the view after the change before it is allowed.<\/p>\n
create<\/span> view<\/span> v2 as<\/span> select<\/span> col1, col2, col3 from<\/span> z2 where<\/span> col2 between<\/span> 2 and<\/span> 100 with<\/span> check<\/span> option


insert<\/span> into<\/span> v2 (col1, col2, col3) values<\/span> (1, 250, 'foo'<\/span>)<\/span><\/pre>\n
<\/span><\/span><\/p>\n
So if we run the same query as before against this view with “with check option” defined, we get this crazy looking plan:<\/p>\n
<\/p>\n
What’s all this then?  Well, some of this requires a few more operators of the day before I can fully explain the plan.  However, in this case it’s doing the following:<\/p>\n
1. create a dummy row that has the values you want to insert into it.  
2. insert it into the clustered index.
3. perform a check to see if this row matches the filter condition(s)
4. Assert that the check succeeded.  If not, fail the query and roll back the transaction.<\/p>\n
All of that from a harmless little insert statement… <\/p>\n
Well, let’s do something else that is theoretically invertable and see how far this support goes:<\/p>\n
create<\/span> view<\/span> v3 as<\/span> select<\/span> col1 + 1 as<\/span> a, col2, col3 from<\/span> z2 <\/span><\/pre>\n
insert<\/span> into<\/span> v3 (a, col2, col3) values<\/span> (1, 250, 'foo'<\/span>)<\/span><\/pre>\n
Msg 4406, Level 16, State 1, Line 1
Update or insert of view or function ‘v3’ failed because it contains a derived or constant field.<\/p>\n
So I claim that this could be supported as an updatable view type because the domain of col1 is integer and the scalar expression on it is invertable.  Unfortunately, none of the vendors really support this to my knowledge.  So, instead of it being a straight reference to a column in z2, we could create an expression that is the proper query needed to make the view v3 consistent by inserting a different <\/b>row into z2.  <\/p>\n
I’ve done two operators – basically the two easiest ones in any query processor.  However, lots of different operators can be supported and have some set of rules about invertability that can be used to perform inserts, updates, and deletes against them.<\/p>\n
So that’s scratching the surface on updates.  I’ll try to post up a few more entries on different parts of the system, but I hope you’ve learned something today about how view updatability is implemented.<\/p>\n
Thanks,<\/p>\n
Conor<\/p>\n","protected":false},"excerpt":{"rendered":"
Well, I spun the wheel of database topics I have here in my room, and today I think I’ll talk about updates and views…. specifically inserts through non-indexed views.  Since I haven’t blogged about this previously, I’ll start at the beginning.  There are many, many update topics, so don’t feel left out – comment if […]<\/p>\n","protected":false},"author":6,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-406","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"\nInserts against views - An introduction - Conor Cunningham<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/conor\/inserts-against-views-an-introduction\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Inserts against views - An introduction - Conor Cunningham\" \/>\n<meta property=\"og:description\" content=\"Well, I spun the wheel of database topics I have here in my room, and today I think I’ll talk about updates and views…. specifically inserts through non-indexed views.  Since I haven’t blogged about this previously, I’ll start at the beginning.  There are many, many update topics, so don’t feel left out – comment if […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/conor\/inserts-against-views-an-introduction\/\" \/>\n<meta property=\"og:site_name\" content=\"Conor Cunningham\" \/>\n<meta property=\"article:published_time\" content=\"2008-03-12T19:42:41+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-01T19:24:21+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.sqlskills.com\/blogs\/conor\/content\/binary\/withcheckoption.jpg\" \/>\n<meta name=\"author\" content=\"Conor Cunningham\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Conor Cunningham\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/inserts-against-views-an-introduction\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/inserts-against-views-an-introduction\/\",\"name\":\"Inserts against views - An introduction - Conor Cunningham\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#website\"},\"datePublished\":\"2008-03-12T19:42:41+00:00\",\"dateModified\":\"2013-01-01T19:24:21+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/inserts-against-views-an-introduction\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/conor\/inserts-against-views-an-introduction\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/inserts-against-views-an-introduction\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Inserts against views – An introduction\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/\",\"name\":\"Conor Cunningham\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3\",\"name\":\"Conor Cunningham\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g\",\"caption\":\"Conor Cunningham\"},\"url\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/author\/conor\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Inserts against views - An introduction - Conor Cunningham","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/conor\/inserts-against-views-an-introduction\/","og_locale":"en_US","og_type":"article","og_title":"Inserts against views - An introduction - Conor Cunningham","og_description":"Well, I spun the wheel of database topics I have here in my room, and today I think I’ll talk about updates and views…. specifically inserts through non-indexed views.  Since I haven’t blogged about this previously, I’ll start at the beginning.  There are many, many update topics, so don’t feel left out – comment if […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/conor\/inserts-against-views-an-introduction\/","og_site_name":"Conor Cunningham","article_published_time":"2008-03-12T19:42:41+00:00","article_modified_time":"2013-01-01T19:24:21+00:00","og_image":[{"url":"http:\/\/www.sqlskills.com\/blogs\/conor\/content\/binary\/withcheckoption.jpg"}],"author":"Conor Cunningham","twitter_misc":{"Written by":"Conor Cunningham","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/inserts-against-views-an-introduction\/","url":"https:\/\/www.sqlskills.com\/blogs\/conor\/inserts-against-views-an-introduction\/","name":"Inserts against views - An introduction - Conor Cunningham","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#website"},"datePublished":"2008-03-12T19:42:41+00:00","dateModified":"2013-01-01T19:24:21+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/inserts-against-views-an-introduction\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/conor\/inserts-against-views-an-introduction\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/inserts-against-views-an-introduction\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/conor\/"},{"@type":"ListItem","position":2,"name":"Inserts against views – An introduction"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/conor\/","name":"Conor Cunningham","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/conor\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3","name":"Conor Cunningham","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g","caption":"Conor Cunningham"},"url":"https:\/\/www.sqlskills.com\/blogs\/conor\/author\/conor\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/posts\/406","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/comments?post=406"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/posts\/406\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/media?parent=406"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/categories?post=406"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/tags?post=406"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}