Transaction SQL to add index to SQL Server 2012 Table - and options

I have added a column Regions to table Members. This field will be used in searches through members to find Members available for the visitors region. Please advise the TA-SQL to do this - I don't understand all the options - clustered etc?  Region will be used in a lot of intensive searches with other columns. Thanks
Ian WhiteOwner and FounderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Deepak ChauhanSQL Server DBACommented:
However you can create only one clustered index on a table. Please check first the primary key as primary key create a cluster index automatically.  If clustered index already exists , you can create non clustered index on the table and you can include 16 columns according to need.

You can create clustered Index using this sample T-sql using this code. or using GUI in SSMS.

---Clustered Index.
    ON dbo.<tableName>  (ColumnName);

--Non clustered index
    ON dbo.<tableName>  (ColumnName);

Here is the more example of create index Tsql with examples

And this is the definition
Mike EghtebasDatabase and Application DeveloperCommented:
If this is an existing table, most likely it already has a cluster index.

If the you will supplying filter criteria to search for Regions fields then, as suggested, run:

CREATE NONCLUSTERED INDEX indexRegions ON dbo.Members  (Regions );

If you are not going yo search by Regions, then there is no need to create this index.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ian WhiteOwner and FounderAuthor Commented:
Ok Thanks very much. So what is the advantage of adding multiple columns in one index. Is this like a super index with all the common visitor/user  search criteria used concatenated together?
Mike EghtebasDatabase and Application DeveloperCommented:
Table_1 (a table with no cluster index or any kind of index is called heap there is no order at all except on RID). RID column (row identifier) is a hidden column automatically generated.
RID   SSN     FName     LastName      City        State      Region
1     -----1    Mike        Smith       Seattle    OR         NW
2     -----6    Joe         Peterson    LA         CA          W
3     -----4    Sheila      Brown       Dallas     TX         SW
4     -----8    Scott       White       Ohio       IL         MW   
5     -----9    Mike        Davisin     Ohio       IL         MW

Open in new window

When user assign or runs T-SQL to create PK on SSN column, then an index is created in B-Tree (binary tree) structure like below, not visible to users, sorted by SSN. This B-Tree + heap table is known as clustered index.
RID   SSN     
1     -----1    
3     -----4    
2     -----6    
4     -----8    
5     -----9    

Open in new window


none cluster on Region for example will be like:
RID  Region
4    MW
9    MW   
1    NW
3    SW
2    W

Open in new window

A none cluster on two column example will be like FName     LastName  like:
RID   FName       LastName
2     Joe         Peterson
9     Mike        Davison
1     Mike        Smith
4     Scott       White
3     Sheila      Brown   

Open in new window

I will invite another expert to comment on my post to make sure I have it all correctly stated.


Note to Ian White: This post has been modified couple of time. Please review to make sure you have read the latest revision.
Mike EghtebasDatabase and Application DeveloperCommented:
"A primary key (in a referenced table) creates a unique index on PK. But foreign keys (in the referencing tables) are not indexed. Such indexes could improve the performance of joins based on those relationships. Because SQL Server doesn’t create such indexes automatically, it’s your responsibility to identify the cases where they can be useful and create them. So when working on index tuning, one interesting area to examine is foreign key columns, and evaluating the benefits of creating indexes on those."
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.