Script to create an index for a table variable

Table variables can have a non-clustered or a clustered index. Further, an order can be specified for index column(s).

Script to create a UNIQUE clustered index on a table variable (note you can create a UNIQUE or non-unique clustered index for a table variable)

DECLARE @table_source TABLE ( ProductId INT ,Product VARCHAR(50),   
                              UNIQUE CLUSTERED (Product));
--above will create an index in ascending order of Product
--to create an index with a descending order of  Product
--use the script below (note: you can also use multiple columns as in
--(Product DESC, ProductId ASC) when defining an index)
--DECLARE @table_source TABLE ( ProductId INT ,Product VARCHAR(50),                              --                              UNIQUE CLUSTERED (Product DESC));

An aletrnate create script is as below.

DECLARE @table_source TABLE ( ProductId INT ,Product VARCHAR(50),                                  INDEX IX1 UNIQUE CLUSTERED (Product DESC));

Script to create a Non-unique Clustered Index

DECLARE @table_source TABLE ( ProductId INT ,Product VARCHAR(50),   
                              INDEX IX1 CLUSTERED (Product ASC));

Script to create a NON-UNIQUE clustered index on a table variable

DECLARE @table_target TABLE ( ProductId INT ,Product VARCHAR(50), 
                              INDEX IX1 CLUSTERED(Product));

If you define a primary key for a table variable, then automatically a clustered index is created for the primary key column(s). so this is another way to create a clustered index for a table variable.

DECLARE @table_source TABLE ( ProductId INT ,Product VARCHAR(50) PRIMARY KEY);
--an alternate script for creating primary key on one or more columns is as below
---DECLARE @table_source TABLE ( ProductId INT ,Product VARCHAR(50),
                              PRIMARY KEY (ProductId ASC, Product DESC));

Script to create a non-clustered index on a table variable (note that we do not need a UNIQUE constraint for such an index)

DECLARE @table_source TABLE ( ProductId INT ,Product VARCHAR(50),   
                             INDEX IX1 NONCLUSTERED (Product));

If you have a primary key (non-clustered index) and also a clustered index on another column then the default order of rows in SELECT statement will be decided by the primary key and not the clustered index. Keep in mind that only 1 clustered index can be created per table and it decides the storage order of the rows for that table.