Creating new indexes in Sql Server 2005 SSMS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Creating new indexes in Sql Server 2005 SSMS

Author

Topic

vondiplo
Starting Member

8 Posts

Posted - 2008-08-05 : 18:06:58

Hello All,

I've got a small question here. I see that I can do one of the two, either alter a table to hold a constraint which serves as an index (see code 1 for example) or, I could create a new index on a table (see code 2 for example). What is the difference between the two? what is more advisable?

Furthermore, If I create in SSMS a new index in the query editor it doesn't appear in the tables 'index' pan. However, if I create an index in the query editor it appears. any ideas why?

Code 1:

/****** Object: Index [PK_Users] Script Date: 08/06/2008 01:03:18 ******/
ALTER TABLE [dbo].[Users] ADD CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

Code 2:

CREATE UNIQUE NONCLUSTERED INDEX [IX_Users_Status] ON [dbo].[Users]
(
[Status] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Description of index' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Users', @level2type=N'INDEX',@level2name=N'IX_Users_Status'

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-05 : 20:53:23

Didn't get it?

vondiplo
Starting Member

8 Posts

Posted - 2008-08-06 : 00:16:31

What's the difference between the two codes?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-06 : 00:21:02

One is applying a primary key constraint, the other a unique index. You should be using a unique constraint though instead of a unique index as constraints make your intention clear that you want data integrity.

In your scenario though, you probably just want the primary key constraint. You should almost always have a PK on each of the tables and then you can add unique constraints to further make data unique.

You can only have on PK per table. PKs do not allow any columns with NULL data. You can have multiple unique constraints/indexes on a table, plus they allow NULLs.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

vondiplo
Starting Member

8 Posts

Posted - 2008-08-06 : 10:53:03

So let's if I got it right. The unique constraint automatically creates some sort of an index on the column? That index is named after my constraint? So now I can enjoy the efficiency of an index and still clarify my intent of a constraint?

If so, Why should I declare an index on keys? Don't they come with the key automatically?

Thanks a million

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-06 : 11:43:34

Yes you get an index with the unique constraint. You also get an index on primary key constraints. But you don't get an index on foreign keys, so those need to be added manually.

Most every table should have a primary key and most every table should have a clustered index.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources