Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.
Loading

SQLServer Quiz 2010 - The View, The Table and The Clustered Index Confusion

  • In SQL Server 2005 a single table can have maximum 249 non clustered index and 1 clustered index. In SQL Server 2008 a single table can have maximum 999 non clustered index and 1 clustered index. It is widely believed that a table can have only 1 clustered index and the belief is true. Here is the question to all of you from my side.Let us assume I am creating view from the table itself and create clustered index on it. In my view I am selecting complete table itself.

    Now run the following script and tell me the answers on these questions:

    Q1. Does the table use an index created on itself?

    Q2. Does the view use an index created on itself?

    Q3. Do both queries use the same index? If yes, why? If no, why not?

    Your answer may vary based on your version of SQL Server. Please check your version by using following statement.

    SELECT @@VERSION
    

    Please include your version details with your answer.

    If you do not know how to read execution plan and index used you can run following statement and make sure that result set is in text format(CTRL+T)

    SET SHOWPLAN_ALL ON
    GO
    SELECT ID1,ID2,SomeData
    FROM mySampleTable
    GO
    SELECT ID1,ID2,SomeData
    FROM SampleView
    GO
    

    Bonus Question: Does this mean that my table has now effective two clustered index?

    The script:

    USE tempdb
    GO
    -- Create sample Table
    CREATE TABLE mySampleTable(ID1 INT, ID2 INT, SomeData VARCHAR(100))
    INSERT INTO mySampleTable(ID1,ID2,SomeData)
    SELECT TOP 1000 ROW_NUMBER()OVER (ORDER BY o1.name),
    ROW_NUMBER()OVER (ORDER BY o1.name DESC),
            o1.name
    FROM sys.all_objects o1
    CROSS JOIN sys.all_objects o2
    GO
    -- Create Index on Table
    CREATE UNIQUE CLUSTERED INDEX [IX_SampleView] ON mySampleTable
    (
        ID1 ASC
    )
    GO
    -- Create sample View
    CREATE VIEW SampleView
    WITH SCHEMABINDING
    AS
    SELECT ID1,ID2,SomeData
    FROM dbo.mySampleTable
    GO
    -- Create Index on View
    CREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView]
    (
        ID2 ASC
    )
    GO
    -- Enable Execution Plan using CTRL + M
    SELECT ID1,ID2,SomeData
    FROM mySampleTable
    GO
    SELECT ID1,ID2,SomeData
    FROM SampleView
    GO
    /*
    Q1: Does table uses index created on itself?
    Q2: Does view uses Index created on itself?
    Please explain reasoning behind the same.
    */
    -- Clean up
    DROP VIEW SampleView
    DROP TABLE mySampleTable
    GO
    

    Posted on 10-06-2010 00:00 |
    Pinal Dave
    151 · 1% · 326

34  Answers  

Subscribe to Notifications
Previous 1 | 2 | 3 | 4 Next
  • Score
    8

    Q1: Does table uses index created on itself?

    Ans.) used the index (scan) created on table

    Q2: Does view uses Index created on itself?

    Ans.) used the index (scan) created on table, not used the one created on view.

    Q3: Does both of the query uses same index? If yes, why?If no, why not?

    Ans.) well, both query has used same index because it is all depend on the SQL Server Query optimizer, It decides to use clustered index created on base table is more effective than the one created on the view. however if you are sure that you want to use the Index created on View while querying view than use following query.

    SELECT ID1,ID2,SomeData FROM SampleView WITH (NOEXPAND)
    

    Bonus Question: Does this mean that my table has now effective two clustered index?

    Ans.) well it depends. creating index on view going to consume extra space and create overhead on DML statements, at the same time by default it is not going to be used except you manually specified it with NOEXPAND hint. so you are not going to get benefit even in SELECT. So in this case we can say, no we don't have two effective clustered index. event if you remove index from Table and have index on View only, you are not going to use that index except NOEXPAND query hint.

    Now, If I use above query hint always when we query the view, you are surely going to get benefit of index made on view but why you need it? as if there is no index there on view though it is going to use index of table and give us benefit of performance than why should we consume some extra space?

    Though you can't say that you have two effective index on table as second index is not on table but on view.

    BTW, I have ran these samples on "SQL Server 2008 (RTM)" version.

    Replied on Oct 6 2010 4:24AM  . 
    Ritesh Shah
    75 · 2% · 747
  • Score
    8

    Q1: Does table uses index created on itself?

    Yes, Table uses the index created on itself.

    Q2: Does view uses Index created on itself?

    No, View also use the index created on table only.

    Q3. Does both of the query uses same index? If yes, why?If no, why not?

    Yes. Both queries use the index on the table only.

    The reason for above behaviour is, as both indexes having same size(8 pages), and on same set of columns and having same values across all levels of index. So, both queries will give same performance.

    In this case, even when we query the view directly, still the Engine will choose the base table. If we wants to override this behaviour, using the hint "NOEXPAND" will force the index on view only when we query the index.

    SELECT ID1,ID2,Somedata from sampleview with (NOEXPAND)
    

    apart from this in case of small tables, even creating the index view on that table, still uses the table index only, even when we query the indexed view directly.....

    This behaviour is same across sql 2005 and sql 2008. But I think Enterprise edition might have more advantage with indexed view than other versions. Expression editions will not use indexedviews.

    Replied on Oct 6 2010 4:37AM  . 
    Ramireddy
    2 · 40% · 12972
  • Score
    1

    For the Bonus question, about 2 clustered indexes on the table

    If we just about both stores same data in same format, then on that basis "Yes". But, If we consider other technical details "NO"

    Why yes,

    1)Both Indexes has same table data at leaf levels. Both tables have same values at Non-Leaf Levels(In case, both key columns are same)

    Why No,

    1) A table has only one clustered index. Every Table can identify its clustered index with the value "1" and only one object is allowed to have ID "1".

    2) Both indexes belongs to different objects.

    But There is an advantage with this approach. This approache will be useful in more Dataware housing or reporting scenarios, where you have a huge no of records. 99% of the queries run are "select" statements only and insert/update/deletes are very rare.Usually the queries in these scenarios, will depends on multiple number of columns. If these columns are key columns of CI, then only They can perform well. Otherwise they can't perform well. By creating indexed views on different set of columns, these will improve performance

    We can achieve this same indexed view effect by using an index which covers all columns of a table also. 3) Both stored in different Pages.

    Replied on Oct 6 2010 4:44AM  . 
    Ramireddy
    2 · 40% · 12972
  • Score
    10

    This is very interesting question.

    My SQL Server Version is Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 (Build 7600: )

    In my case both of the query is using the Index [IX_SampleView]. The normal expectation was that Index on Table will be used by Table and Index on View will be used by View. However, here the original table's index is used for view as well table as well.

    Q1: Does table uses index created on itself?

    Yes

    Q2: Does view uses Index created on itself?

    No

    Q3: Does both of the query uses same index? If yes, why?If no, why not?

    Yes. The reason is listed here in the Improving Performance with SQL Server 2008 Indexed Views (Link:http://msdn.microsoft.com/en-us/library/dd171921%28SQL.100%29.aspx )

    If you read the white paper it is explained there

    The cost of using the indexed view may exceed the cost of getting the data from the base tables, or the query is so simple that a query against the base tables is fast and easy to find. This often happens when the indexed view is defined on small tables. You can use the NOEXPAND hint if you want to force the query processor to use the indexed view. This may require you to rewrite your query if you don't initially reference the view explicitly. You can get the actual cost of the query with NOEXPAND and compare it to the actual cost of the query plan that doesn't reference the view. If they are close, this may give you confidence that the decision of whether or not to use the indexed view doesn't matter.

    Bonus Question: Does this mean that my table has now effective two clustered index?

    No. Table can not have multiple clustered index. It still have only one clustered index. Even though view has clustered index, it has technically no effect on the original table's Index. System Tables and other meta data will also not show it.

    The formatting of the question is bit off. I have corrected it here.

    USE tempdb
    GO
    -- Create sample Table
    CREATE TABLE mySampleTable (ID1 INT, ID2 INT, SomeData VARCHAR(100))
    INSERT INTO mySampleTable (ID1,ID2,SomeData)
    SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY o1.name),
            ROW_NUMBER() OVER (ORDER BY o1.name DESC),
            o1.name
    FROM sys.all_objects o1
    CROSS JOIN sys.all_objects o2
    GO
    -- Create Index on Table
    CREATE UNIQUE CLUSTERED INDEX [IX_SampleView] ON mySampleTable
    (
        ID1 ASC
    )
    GO
    -- Create sample View
    CREATE VIEW SampleView
    WITH SCHEMABINDING
    AS
    SELECT ID1,ID2,SomeData
    FROM dbo.mySampleTable
    GO
    -- Create Index on View
    CREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView]
    (
        ID2 ASC
    )
    GO
    -- Enable Execution Plan using CTRL + M
    SELECT ID1,ID2,SomeData
    FROM mySampleTable
    GO
    SELECT ID1,ID2,SomeData
    FROM SampleView
    GO
    -- Clean up
    DROP VIEW SampleView
    DROP TABLE mySampleTable
    GO
    
    Replied on Oct 6 2010 4:46AM  . 
    Nupur Dave
    174 · 1% · 284
  • Score
    6

    I tried it with two version of SQL Server:

    Microsoft SQL Server 2005 - 9.00.4053.00 (X64) May 26 2009 14:13:01 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2)

    Microsoft SQL Server 2008 (RTM) - 10.50.1600.1 (X64) Arp 2 2010 15:48:46 14:13:01 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 (Build 7600: ) (Hypervisor)

    On both servers I've got the same results:

    Q1: Does table uses index created on itself?

    Yes: |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[mySampleTable].[IX_SampleView]))

    Q2: Does view uses Index created on itself?

    No: |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[mySampleTable].[IX_SampleView]))

    Please explain reasoning behind the same.

    The reason for answer of Q1 is obvious: If selecting from an table, views from the table are taken into account of quere optimizer.

    The reason for answer of Q2 is first obvious when you're kowing, that views are expanded by the query optimizer. Meaning that the optimizer "looks" into the view-definition and optimizes for the underlying query. In our case this means, that not the select from the view SAMPLE_VIEW is optimized but the select from the underlying table dbo.mySampleTable is optimized. Therefor the index on the view is totally ignored.

    This behavior can be avoided (SQL Server 2008 and later) through query hint "WITH (NOEXPAND)". The view definition will not be expanded but the query on the view itself will be optimized and Index IX_SampleView will be taken into account (and in our case also used).

    Bonus Question: Does this mean that my table has now effective two clustered index?

    No. The table has in our case on clustered index. An index on a view produces a materialized view - meaning there is physically duplicate of the table, which means that we've got two tables with one clustered index on each.

    Replied on Oct 6 2010 5:07AM  . 
    MarkusE
    728 · 0% · 45
  • Score
    6

    Version: Microsoft SQL Server 2005 - 9.00.4053.00

    Q1: Does table uses index created on itself?

    Yes

    Q2: Does view uses Index created on itself?

    No. The view will use the index created on the table, too.

    Q3: Does both of the query uses same index? If yes, why?If no, why not?

    Yes.

    The query can reference the indexed view directly, or, more importantly, the query optimizer can select the view if it determines that the view can be substituted for some or all of the query in the lowest-cost query plan. (http://technet.microsoft.com/en-us/library/cc917715.aspx).

    The Query optimizer obviously decided not to use the index of the view since this would require additional steps during query optimization (Since one of the first steps is to expand the metadata of the view and use the underlying query, the table reference and the table index need to be replaced with view reference and view index without gaining any benefit).

    Using the table index will be more efficient.

    Bonus Question: Does this mean that my table has now effective two clustered index?

    No.

    A clustered index on a view will cause the view to be materialized. (BOL, section "Types of Views, Indexed Views").

    Therefore, we're technically talking about two separate tables.

    Replied on Oct 6 2010 5:12AM  . 
    lmu92
    0 · 0% · 0
  • Score
    6

    Version: SQL 2008 R2 Express

    Q1: Does table uses index created on itself?

    Yes

    Q2: Does view uses Index created on itself?

    No - uses the table clustered index

    Q3: Does both of the query uses same index? If yes, why? If no, why not?

    Yes they use the same index. The reason behind this is to do with view expansion. If a view is referred to directly in the FROM clause, then the query optimizer will expand the view until it finds the base tables. In this instance, the optimizer determines that the base table is "mySampleTable", and uses the clustered index on the table to extract the data. In order to stop this behaviour, a query hint of "NOEXPAND" can be used to stop the view expansion and effectively treat the view like an ordinary table with a clustered index, e.g.

    SELECT ID1, ID2, SomeData
    FROM SampleView WITH (NOEXPAND)
    

    Using the query hint above results in the query optimizer choosing the clustered index on the view.

    Bonus Question: Does this mean that my table has now effective two clustered index?

    Yes it does, however as stated above the second clustered index can only be used if the query hint is added in every query. It also means the clustered index is chosen by the developer rather than the optimizer, therefore two clustered indexes can be used, but not in a dynamic way when running queries.

    Replied on Oct 6 2010 5:18AM  . 
    Mike Lewis (@SQLTuna)
    42 · 4% · 1336
  • Score
    6

    This is an interesting question and the results are surprising.

    If an indexed view is created with all the columns of a table that has a clustered index, do you consider the table having two clustered indexes?

    No. No. No

    Q1: Does table uses index created on itself?

    Yes

    Q2: Does view uses Index created on itself?

    No. I thought that it will be used by view but after running the query and examining it , both the queries use same index.

    Q3: Does both of the queries uses same index? If yes, why? If no, why not?

    Yes. They use same index it is proved after running the query.

    The reason is simple. Views are expanded by the query optimizer. The optimizer examines into the view-definition, optimizes the query. Select from the view SAMPLE_VIEW is not optimized but the select from the original table is optimized. Hence the index on the view is ignored.

    Bonus Question: Does this mean that my table has now effective two clustered index?

    No. A Table cannot have multiple clustered indexes. Confused!! Table contains only one clustered index. The Views clustered index will not have any effect on the given table's Index.

    Server Version Used to run the queries : Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 (Build 7600: )

    Replied on Oct 6 2010 5:53AM  . 
    Vamshi
    134 · 1% · 376
  • Score
    6

    Version of SQL Server is: "Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 (Build 7600: )"

    Does table uses index created on itself?

    Yes.

    Does view uses Index created on itself?

    No. The view used the index created on the base table.

    Does both of the query uses same index? If yes, why? If no, why not?

    Yes both of the query uses same index on my machine. The reason behind this is that the query processor (By default) expands the view definition (when parsing and binding the query), and usually the query optimizer matches the indexed views during optimization. But there are cases where the rewritten queries (internaly during optimization) are not possible to match indexed views anymore, Or the query was catergorized as a trivial query that do not that much of optimization (You can overcome the expansion of the query using NOEXPAND hint to force the query processor to force the use of the indexed view in the final query plan). So both queries are the same for the optimizer (after expansion) and to both of them when the required conditions (SQL Server edition, session options, there is a match between the view index and the query, ...) are all met, then the optimizer try to use an indexed view in its query plan only if the query optimizer determines it is beneficial to do so. But in this particular case the optimizer used the index on the base table for both queries.

    Does this mean that my table has now effective two clustered index?

    It depeneds on the perspective.

    If all the required conditions to use indexed view are met then the optimizer can decide between the two clustered indexes (the one on base table, and the one on the indexed view) based on the estimated cost for using the indexes. So it is practicaly yes because all the queries that target the base table can choose between those two indexes (and any other indexes).

    If we are looking on the maintance of the clustered index, then the answer also would be yes, because that the operations that affect the data of the base table will maintain the two clustered indexes.

    But if not all the required conditions to use indexed view are met, or the index on the indexed view is so expensive to use (compared to the other indexes on the base table), hence we are not using that index at ll, then the answer would be no.

    Finally the physical perspective states that even though we can relate the clustered index on indexed view to the base table. But the index is not on the base the table so it is not a antoher clustered index for that table, then the answer would be no.

    To sum up in my opinion the overhead of maintaining the clustered index on the indexed view, in addition to the clustered index on the table for all the operations that affects the data of the base table, and the possibilty of choosing between the two indexes for the queries that target the base table makes that table effectivly as if it has two clustered index (even though it is not).

    Replied on Oct 6 2010 5:55AM  . 
    Muhammad Al Pasha
    27 · 6% · 1920
  • Score
    5

    Microsoft SQL Server 2008 (SP1) - 10.0.2766.0 (X64)

    Feb 25 2010 12:51:37

    Copyright (c) 1988-2008 Microsoft Corporation

    Enterprise Edition (64-bit) on Windows NT 6.0 (Build 6001: Service Pack 1) (VM)

    Q1: Does table uses index created on itself?

    Yep, select from a table with clustered index uses the index.

    Q2: Does view uses Index created on itself?

    Nope, It uses the table's one

    *Q3: Does both of the query uses same index? If yes, why?If no, why not? *

    Yes, Sql Server does not predict a optimization reading from the materialiced view, so it reads from table using the table´s clustered index.

    Bonus Question: Does this mean that my table has now effective two clustered index?

    No, a clustered index is the data directly from the table. In this situation, we have the clustered index from the table, and the data duplicated due to the materialiced view in other clustered index (as it was another table).

    Is impossible to have 2 clustered indexs.

    Replied on Oct 6 2010 8:35AM  . 
    diegoperdices
    113 · 1% · 459
Previous 1 | 2 | 3 | 4 Next

Your Answer


Sign Up or Login to post an answer.
Please note that your answer will not be considered as part of the contest because the competition is over. You can still post an answer to share your knowledge with the community.

Copyright © Rivera Informatic Private Ltd.