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?
Best Answer
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.