SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Mohd Nizamuddin / Indexed View / Latest Posts

RE: Indexed View

BudaCli — Tue, 15 Dec 2009 01:43:54 GMT

[quote][b]Ray Laubert (12/8/2009)[/b][hr]Wasn't sure about CharIndex ...Has anyone ever used CharIndex on an indexed view?[/quote]But this was not because of the null reason...

RE: Indexed View

stoklosa — Thu, 10 Dec 2009 07:50:30 GMT

What's about this question?In SQL 2000 - CharIndex is nondeterministichttp://msdn.microsoft.com/en-us/library/aa214775(SQL.80).aspxIn SQL 2005 - CharIndex is deterministichttp://msdn.microsoft.com/en-us/library/ms178091(SQL.90).aspxSorry, but question didn't precise about which version sql server was going.Please give me my lost point :)

RE: Indexed View

hakan.winther — Wed, 09 Dec 2009 01:46:42 GMT

[quote][b]Ray Laubert (12/8/2009)[/b][hr]Thanks, that is what I would have expected. CharIndex would create a situation that invalidates the Index View and basically forces SQL to the underlying tables.But this does not in itself invalidate the question.[/quote]You are right it doesn't invalidate the question, but it is misleading information to everyone trying to create an indexed view with char index, and I think that is a bad.

RE: Indexed View

mohd.nizamuddin — Tue, 08 Dec 2009 21:04:32 GMT

Thanks a lot Steve.

RE: Indexed View

Adam Haines — Tue, 08 Dec 2009 16:06:38 GMT

You have to add the noexpand hint to the query to use the view's index. The noexpand hint forces the optimizer to use the views data instead of the underlying tables and indexes. You have to remember that the optimizer is still cost efficient and sometimes it thinks the using the underlying data is cheaper than using the view data, which may be true in certain cases. Anyway you must use the noexpand hint to make the optimizer look at the view and its indexes only. On the flip side, you can use the expand hint to make the optimizer expand the view.

RE: Indexed View

Ray Laubert — Tue, 08 Dec 2009 09:34:46 GMT

Thanks, that is what I would have expected. CharIndex would create a situation that invalidates the Index View and basically forces SQL to the underlying tables.But this does not in itself invalidate the question.

RE: Indexed View

Mike Mullen — Tue, 08 Dec 2009 08:40:18 GMT

I tried an example containing a charindex column in SQL2005, and while it did create the index on the view, the following warning was shown when it was created.Warning: The optimizer cannot use the index because the select list of the view contains a non-aggregate expression.The execution plan for queries using the view show the use of indexes on the underlying tables, rather than the index on the view.So, while the system allows you to create an index on a view containing a charindex column, it doesn't appear to be of any use.

RE: Indexed View

Ray Laubert — Tue, 08 Dec 2009 07:28:39 GMT

Wasn't sure about CharIndex since it can return a null value and which could invalidate the index statement for the view. Has anyone ever used CharIndex on an indexed view?

RE: Indexed View

Steve Jones - SSC Editor — Tue, 08 Dec 2009 07:24:09 GMT

Explanation corrected.

RE: Indexed View

vk-kirov — Tue, 08 Dec 2009 06:38:23 GMT

[quote][b]hakan.winther (12/8/2009)[/b][hr][quote][b]vk-kirov (12/8/2009)[/b][hr][quote][b]mohd.nizamuddin (12/8/2009)[/b][hr]However, in the example the functions Count(*), SUM, MAX, MIN etc are not a deterministic function.[/quote]This is incorrect.[quote]All of the [b]aggregate[/b] and string built-in functions are [b]deterministic[/b].[/quote][/quote]Count is a deterministic function, but it is not allowed anyway. You should use COUNT_BIG instead.[/quote]I know this :-) What I only said is: "aggregate functions are deterministic", nothing about indexed views :-)

RE: Indexed View

hakan.winther — Tue, 08 Dec 2009 05:39:14 GMT

[quote][b]vk-kirov (12/8/2009)[/b][hr][quote][b]mohd.nizamuddin (12/8/2009)[/b][hr]However, in the example the functions Count(*), SUM, MAX, MIN etc are not a deterministic function.[/quote]This is incorrect. Check BOL (Deterministic and Nondeterministic Functions): [url]http://msdn.microsoft.com/en-us/library/ms178091.aspx[/url][quote]All of the [b]aggregate[/b] and string built-in functions are [b]deterministic[/b].[/quote][/quote]Count is a deterministic function, but it is not allowed anyway. You should use COUNT_BIG instead.

RE: Indexed View

ser_s — Tue, 08 Dec 2009 03:33:23 GMT

Exactly, aggregate functions are deterministic. So they are allowed in indexed view. "Correct" answer is incorrect! :-)Deterministic and Nondeterministic Functions:http://msdn.microsoft.com/en-us/library/ms178091.aspx

RE: Indexed View

vk-kirov — Tue, 08 Dec 2009 02:31:47 GMT

[quote][b]mohd.nizamuddin (12/8/2009)[/b][hr]However, in the example the functions Count(*), SUM, MAX, MIN etc are not a deterministic function.[/quote]This is incorrect. Check BOL (Deterministic and Nondeterministic Functions): [url]http://msdn.microsoft.com/en-us/library/ms178091.aspx[/url][quote]All of the [b]aggregate[/b] and string built-in functions are [b]deterministic[/b].[/quote]

RE: Indexed View

mohd.nizamuddin — Tue, 08 Dec 2009 00:54:57 GMT

Hi,Thanks a lot,[quote][b]CirquedeSQLeil (12/7/2009)[/b][hr]The wording in the explanation don't seem to match the documentation.[url]http://msdn.microsoft.com/en-us/library/ms191432(SQL.90).aspx[/url]Explanation said that the view must not contain any deterministic functions. The referenced article states that functions must be deterministic and then continues to lay out what is not acceptable.[/quote]Actually the explanation should be like "When you create an indexed view, the view definition must not contain any [b][i]non-[/i][/b] deterministic functions". However, in the example the functions Count(*), SUM, MAX, MIN etc are not a deterministic function.

RE: Indexed View

SQLRNNR — Mon, 07 Dec 2009 23:40:40 GMT

The wording in the explanation don't seem to match the documentation.[url]http://msdn.microsoft.com/en-us/library/ms191432(SQL.90).aspx[/url]Explanation said that the view must not contain any deterministic functions. The referenced article states that functions must be deterministic and then continues to lay out what is not acceptable.

Indexed View

mohd.nizamuddin — Mon, 07 Dec 2009 23:37:36 GMT

Comments posted to this topic are about the item [B]Indexed View[/B]