Unlocking Power with Updatable Views in SQL Server

Question

Post reply

Unlocking Power with Updatable Views in SQL Server

Almighty

SSCrazy

Points: 2001
More actions
April 22, 2024 at 12:00 am

#4401880

Comments posted to this topic are about the item Unlocking Power with Updatable Views in SQL Server

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 1

Instead of views, I use a lot TVF and CTE, the limits are the same as for views.

Here an example:

create table a (i int)
GO
create function myfn_a(@i int)
returns table
as
    return 
    SELECT * FROM a
     WHERE a.i = @i
GO
INSERT INTO myfn_a(NULL) 
output inserted.*
select 1

update myfn_a(1) SET
i += 1
output inserted.*

delete myfn_a(2)
output deleted.*

tmyers 75436 Grasshopper Points: 13 More actions · Answer 2

My primary use for updateable views is for bulk inserts into tables with more columns than the source files have. The views' columns match the source files' columns. BULK INSERT is oblivious to the extra columns, including the identity columns which are updated in the process.

I did have one project some years ago with a view based on three underlying tables tied to a UI with inserts, updates and deletes. This was on a SQL Server 2008 R2 server. In that project I created INSTEAD OF triggers to handle all of the DML operations because I was under the impression that they were necessary at the time. However, my read of this article suggests that the DML operations occur automatically without triggers. Is this true?

Henry B. Stinson SSC Eights! Points: 949 More actions · Answer 3

The view used to create the view left out the age column, therefore the insert statements that include age should fail.

tmyers 75436 Grasshopper Points: 13 More actions · Answer 4

While I do agree that INSERT statements using the view SHOULD fail (because I believe age is important in this context), it WON'T fail because the age column allows NULL values.

I would only leave out of views that are meant for DML operations those columns with default values and/or identity values.