Solved

create non clustered non unique index sql server 2005

Posted on 2011-02-24
7
836 Views
Last Modified: 2012-05-11
Expert,

how to create non unique non clustered index? Is this correct?

CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD
    ON Sales.SalesPerson (SalesQuota);
GO
Thanks,

Lynn
0
Comment
Question by:yrcdba7
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 18

Expert Comment

by:sventhan
ID: 34971982
CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD
    ON Sales.SalesPerson (SalesQuota);
GO

or

CREATE INDEX IX_SalesPerson_SalesQuota_SalesYTD
    ON Sales.SalesPerson (SalesQuota);
GO
0
 
LVL 18

Expert Comment

by:sventhan
ID: 34971991
Syntax:

create index index_name on table(columnname)
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34972098
yes your syntax is currect
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:yrcdba7
ID: 34972101
Thank you,

how about clustered non-unique index?
0
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 34972136
Did you check the msdn? http://msdn.microsoft.com/en-us/library/ms188783.aspx
C. Creating a unique nonclustered index

The following example creates a unique nonclustered index on the Name column of the Production.UnitMeasure table. The index will enforce uniqueness on the data inserted into the Name column.

Copy
USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name from sys.indexes
             WHERE name = N'AK_UnitMeasure_Name')
    DROP INDEX AK_UnitMeasure_Name ON Production.UnitMeasure;
GO
CREATE UNIQUE INDEX AK_UnitMeasure_Name 
    ON Production.UnitMeasure(Name);
GO

Open in new window

If you don't mention UNIQUE, it won't create UNIQUE index.
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34972266
you syntax will create a nonclustered nonunique index

create NONCLUSTERED index ABC_IDX on mytable(mycolumn)  = nonclustered nonunique

create UNIQUE  NONCLUSTERED index ABC_IDX on mytable(mycolumn)  = nonclustered unique

create CLUSTERED index ABC_IDX on mytable(mycolumn)  = clustered nonunique

create unique  CLUSTERED index ABC_IDX on mytable(mycolumn)  = clustered unique
0
 

Author Comment

by:yrcdba7
ID: 34973918
Thankyou, Lynn
0

Featured Post

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question