1

Lets suppose we create a table called "SampleTable" and made "ID" column a primary key. SQL Server by default creates a clustered index on the table. This physically sorts the data on the disk. Now suppose we create a view called "SampleView" with schema binding as select * from "SampleTable" and create a clustered index on the "SampleView". Now SQL Server will create a clustered index on the view and physically sort the data on the disk according to the clustered index.

If the data is already sorted on disk in SampleTable, how can it be sorted again in SampleView?

Improve this question
0

1 Answer 1

Reset to default
2

When you create the clustered index on SampleView, SQL Server has to materialize the data in the index. Normally, a view does not have a separate copy of the data. It is normally just a stored query. An indexed view changes that.

Since the indexed view has a separate copy of the data, it can be sorted in whatever way you'd like. Normally, of course, you wouldn't have an indexed view that simply replicates all the data in a table. You'd normally only create an indexed view if you're going to materialize a subset of data based on various conditions or if you're going to materialize some aggregate data.

Improve this answer
4
  • What I understand is, Index view make a copy of the data. Therefore physical clustering is possible. This means suppose I have 1Gb of data in table, but when I create an Index view which makes an exact copy of data with some overheads, so the total memory consume will be something like 2 GB + some overheads of data. Am i right ?
    – waleedansari
    Commented Sep 30, 2015 at 7:07
  • Another thing to ask is since index view is having a copy of data, if we update index view, will the reflection be seen on the original data of the table ?
    – waleedansari
    Commented Sep 30, 2015 at 8:02
  • @waleedansari - If you created an indexed view that had 1 GB of data, the clustered index would occupy ~1 GB of space on disk (memory generally implies RAM not disk). I can't imagine a scenario where you would want to create an indexed view that was updatable, not sure that's even possible. If it is then, yes, SQL Server would update the data in the underlying table. Far, far more common would be that SQL Server has to replicate changes to the table in the indexed view.
    – Justin Cave
    Commented Sep 30, 2015 at 19:04
  • thanks @justin :) for answering my lengthy questions in comment as well. You look nice in Display :)
    – waleedansari
    Commented Oct 1, 2015 at 3:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.