Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
 Posted - 2008-01-22 : 11:10:53
|
| One tool I use quite often in my ETL process is Indexed View. It works great, but there is a problem ...When the ETL process runs, the first step is to reload the source data. This normally involves a TRUNCATE TABLE of the source tables and then an INSERT. Of course, any source table that is involved in an Indexed View will have a view built on it WITH SCHEMABINDING and, of course, those tables cannot be truncated but rather have to be deleted.I'm finding that the delete process (delete, insert) is much slower than removing the indexed view (drop indexed view, truncate, insert re-create indexed view).The problem is, I have many indexed view and I don't want to maintain a complicated script to keep in sync with my indexed view source code.Anybody have an easy was to deal with this situation? If not, does anybody have an automated way drop/recreate indexed views based on metadata or something?Jayto here knows when |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-01-22 : 11:25:57
|
| save the views' definitons in a temp table, drop them, do stuff to tables and recreate them by executing the definitions from the temp table._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2008-01-22 : 11:29:34
|
| Yeah, and deal with multi-row syscomments crap ... yeah, that would be my last (but maybe only) resort. It would be nice if I could just script the indexes to a temp table and do some sort of alter-view-drop-schemabinding type of thing, but I don't see anyway to do that ...Jayto here knows when |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-01-22 : 11:35:11
|
| forget the syscomments crap use SELECT object_definition(object_id('HumanResources.vEmployee')) -- your view goes herewhich returns the whole definition in a nvarchar(max) variable_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2008-01-22 : 12:05:47
|
| object_definition ... awesomethanks, that helps a bit ...Jayto here knows when |
|
|
|
|
|
|
|
|
ETL Process and Indexed Views