July 24, 2013 at 11:07 am
Sean already said what I wanted to say.
Thanks, Kapil!
July 25, 2013 at 1:20 am
Great question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 25, 2013 at 1:38 am
I supposed (incorrectly) that the ALTER VIEW would fail due to the addition of the non-determinitic column, CurrentDate, to the indexed view.
Learned lots this morning! Nice question!
July 25, 2013 at 1:51 am
This was removed by the editor as SPAM
July 25, 2013 at 1:55 am
sneumersky (7/24/2013)
That said, there are probably other potential uses for indexed views (BI or non-BI related) than I can think of at this moment in time.
I use indexed view to filter rows and have small index, same as CREATE INDEX with WHERE clause.
But, indexed views can reference other tables in JOIN.
e.g.:
create table a(k int, to_process bit default 1)
go
create view v_a
with schemabinding
as
select a.k
from dbo.a
where to_process = 1
go
create unique clustered index idx_v_a on v_a(k)
go
-- after process a record unset flag
declare @k int
begin tran
update top(1) a set to_process = 0,@k = k
-- processing
print 'processing: ' + cast(k as varchar(10))
if @@error = 0
commit
else
rollback
July 25, 2013 at 10:26 am
Andrew Diniz (7/25/2013)
I supposed (incorrectly) that the ALTER VIEW would fail due to the addition of the non-determinitic column, CurrentDate, to the indexed view.Learned lots this morning! Nice question!
Even if you attempted to create a unique clustered index on the view as defined in the ALTER VIEW statement, it would indeed fail because GETDATE() is non-deterministic and indexes cannot be created on views with non-deterministic definitions.
I picked the right answer because I knew that ANY attempt to create an index on view that defines a column as the result of a non-deterministic function would fail. There was only one choice that included "Error" as the result of Scenario 3.
Jason Wolfkill
July 25, 2013 at 10:29 am
wolfkillj (7/25/2013)
Andrew Diniz (7/25/2013)
I supposed (incorrectly) that the ALTER VIEW would fail due to the addition of the non-determinitic column, CurrentDate, to the indexed view.Learned lots this morning! Nice question!
Even if you attempted to create a unique clustered index on the view as defined in the ALTER VIEW statement, it would indeed fail because GETDATE() is non-deterministic and indexes cannot be created on views with non-deterministic definitions.
I picked the right answer because I knew that ANY attempt to create an index on view that defines a column as the result of a non-deterministic function would fail. There was only one choice that included "Error" as the result of Scenario 3.
*cough*
Actually, the view is altered twice. And the second ALTER effectively undoes the first, removing the GETDATE() column again. So that would not be a reason for the CREATE INDEX to fail.
July 25, 2013 at 10:53 am
Good question but your script needs the ";" replaced with GO in spots. 😎
July 25, 2013 at 12:00 pm
Hugo Kornelis (7/25/2013)
wolfkillj (7/25/2013)
Andrew Diniz (7/25/2013)
I supposed (incorrectly) that the ALTER VIEW would fail due to the addition of the non-determinitic column, CurrentDate, to the indexed view.Learned lots this morning! Nice question!
Even if you attempted to create a unique clustered index on the view as defined in the ALTER VIEW statement, it would indeed fail because GETDATE() is non-deterministic and indexes cannot be created on views with non-deterministic definitions.
I picked the right answer because I knew that ANY attempt to create an index on view that defines a column as the result of a non-deterministic function would fail. There was only one choice that included "Error" as the result of Scenario 3.
*cough*
Actually, the view is altered twice. And the second ALTER effectively undoes the first, removing the GETDATE() column again. So that would not be a reason for the CREATE INDEX to fail.
There are indeed two ALTER statements and I did indeed overlook the second. I tend to approach QotDs with lots of code by first skimming through the code looking for any red flags that might indicate what the question is really about. Here, I saw the first ALTER VIEW with a nondeterministic function and in my peripheral vision saw the CREATE INDEX statement in Scenario 3. My brain immediately said "Aha!" because I know off the top of my head that you can't create an index on a view like that (for some reason, the flag didn't go up immediately at Scenario 1's attempt to create a nonclustered index without a unique clustered index). When I saw that only one answer had "Error" as the outcome of Scenario 3, I decided that I had found the crux of the problem and didn't need to read any further. So, I arrived at the correct answer by flawed reasoning. Just lucky today, I guess! 😀
Jason Wolfkill
July 25, 2013 at 3:34 pm
Good Question kapil
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 5, 2013 at 3:17 am
Good question on indexes on views. 🙂
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply