Using CREATE to create an index

Now we have a basic understanding of the index and how it can impact the query performance, let us take an example. The [Customer Key] column on the [Fact].[Sale] table is the column that uniquely identifies each row in the table; hence adding an index to this column would optimize the query performance.

Let’s create an [IX_Fact_Sale_CustomerKey] index on [Fact].[Sale] table at the [Customer Key] column. Here is the code example of creating it:

CREATE INDEX [IX_Fact_Sale_CustomerKey] ON [Fact].[Sale]
(
[Customer Key] ASC
);

The dynamic management view, sys.indexes, provides the lists of the index info created in the database. Use the query and look at the results to familiarize yourself with them:

select * from sys.indexes where name='IX_Fact_Sale_CustomerKey';

The result provides the index name, with other details, such as the object_id type as the index type, type_desc as the index type description, index_id, and so...