index and primary key

By defining a numeric field in table as primary key, will the table be indexed on that particular field?yes, a primary key always gets an index, that's how the database system determines if a value exists already or not (for uniqueness)|||am extending my qn a littl bit

suppose the table has the following structure

myTable
(
myPK bigint identity (Primary key)
myUniqNo bigint
myName varchar (50)
)

can i create an index on myUniqNo, if myUniqNo is unique..|||You can create an index on almost any column, whether it is unique or not. You can create a unique constraint or a unique index on a column if there are no duplicate values in the column.

I'd recommend using a constraint instead of an index unless there is some specific, compelling reason for using the index.

-PatP|||yes you can

but then, if myUniqNo is going to be unique, why do you want an IDENTITY column as the primary key?

and by the way, why bigint? are you planning on having over 2 billion rows?|||but then, if myUniqNo is going to be unique, why do you want an IDENTITY column as the primary key?A surrogate key for the existing surrogate key? That way they can allow updates to their existing column?

Ow, ooo, ow! Quit throwing things, that hurts!!!

-PatP|||Yes, but then they can change the "key" without having to cascade all of the updates...|||I'd recommend using a constraint instead of an index unless there is some specific, compelling reason for using the index.Can you tell us why you'd recommend that? Do you know that when you create a unique constraint you implicitly create a unique index?|||Yes I'm sure Pat knows...I think Pat is spouting party line...M$ reccomends that as well...

Never could figure out why...or maybe we did and I forgot...|||About the only thing that a unique constraint has going for it as opposed to a unique index is that you can have a foreign key dependent on a unique constraint. After that, it gets a bit fuzzy. Does anyone know of any articles where the order of checks is done for an insert in SQL Server? For example, are check constraints checked before foreign keys are? Or do some triggers fire before computed columns are generated? That sort of information might give some insight.|||When UNIQUE constraint gets created, a UNIQUE index gets created at the same time with the same name. If you drop the constraint the index gets dropped with it, also implicitly. Trigger never gets to execute if uniqueness is violated either due to constraint or unique index.

Talking about differences, the only one I see is that while constraint is very strict in respect to controlling RI, unique index can be altered in such a way, where in a batch of 100 rows attempted to be inserted there is 1 duplicate row, 99 will be successfully inserted. Nothing can be done to accomplish the same with unique constraint. That's why MS (and Pat) recommend using constraints over indexes.|||Yes I'm sure Pat knows...I think Pat is spouting party line...M$ reccomends that as well...Not hardly... The only time I "spout party line" is when I'm actually at the party.

Creating a constraint creates metadata. Some programs use metadata now, and more will in the future. Metadata is an important stepping stone toward getting real "relational algebra" tools (especially things like OLAP), which will make life lots easier for everyone as they become more readily available.

-PatP|||dont forget the null.
you have to mention the 1 null...|||according to this thread (http://www.dbforums.com/t998479.html) there is a dodgy way around the "only 1 null in a unique index" problem, but i haven't confirmed that it works, i just bookmarked it|||Not hardly... The only time I "spout party line" is when I'm actually at the party.

Creating a constraint creates metadata. Some programs use metadata now, and more will in the future. Metadata is an important stepping stone toward getting real "relational algebra" tools (especially things like OLAP), which will make life lots easier for everyone as they become more readily available.

-PatP

Good Point...so where's the party?|||according to this thread (http://www.dbforums.com/t998479.html) there is a dodgy way around the "only 1 null in a unique index" problem, but i haven't confirmed that it works, i just bookmarked it

according to ruprect, its as simple as setting the column to not null. :D|||"...unique index can be altered in such a way, where in a batch of 100 rows attempted to be inserted there is 1 duplicate row, 99 will be successfully inserted. "

That is one I haven't seen before.

Got code?|||Never mind. Didn't read your post clearly.