GilaMonster (12/20/2016)

---

Maybe. Depends whether SQL uses the indexed view or goes to...

A) I've got this working. I couldn't work out how to dynamically build CREATE INDEX code from an existing table, so this is hard coded in external .sql files.

B)...

I've written a Powershell script which does the following...

I've searched some more, and now realize that I can't disable the clustered index and have bulk inserts work.

What I really need is a way to drop all indexes (convert...

pietlinden (9/4/2016)

---

Looks like this should do it...

http://stackoverflow.com/questions/11301383/automatically-drop-and-recreate-current-indexes

EXEC sp_MSforEachTable 'ALTER INDEX ALL ON ? DISABLE'

and

EXEC sp_MSforEachTable 'ALTER INDEX ALL ON ? REBUILD'

is all you need if you want to...

I meant automatically, as the row is created.

I'll implement this as a trigger. If there's a better approach please let me know.

Let me restate the question then...

If I run this code:

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[tmp].[DF_mytable_recnum]') AND type = 'D')

BEGIN

ALTER TABLE [tmp].[mytable] DROP CONSTRAINT [DF_mytable_recnum]

END

GO

IF ...

Hmmm...I may need to rethink this...

My code works for the lookup columns...but fails miserably on the non-lookup columns. D'oh.

I either:

1) Need to add an additional update for all the...

Yep, I ran into this after posting.

Here is my current code against my "real application" tables:

drop trigger vFact_Cases_Instead_Update

go

create trigger vFact_Cases_Instead_Update

on vFact_Cases

instead of update

as

begin

set nocount on

--select * from inserted

--select * from...

Ok, this code works. My "real application" will need to update 8 dim table FK's in the fact table. The dim tables aren't large (< 1K rows), so...

Thanks that certainly helped performance - down from 40 seconds to 1 second!. And now that I see the code, it makes sense, reducing the passes over the data.

Payments...

I've made progress on this issue, and have a view which works, except 1) I'd prefer it was editable (for the WriteOff table's columns), and 2) it would be good...

I'm used to being able to specify an order by in a view in other dialects of SQL. I find it handy.

Rather than Microsoft deprecating (or silently...