identity, plus pk?

We have a table that has an identity field along with 5 other domain
fields. The identity field is not declared as a primary key. The
table has 3.5 million records.

A consultant was hired recently to provide insight. His major
recommendation: modify the table to make the identity field a primary
key (i.e., alter table add constraint...)

Is that sound advice? Is it OK to have a table with identity but no
primary keys? What would be the impact on performance?Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files.

Actually, IDENTITY cannot be a relational key by definition. I would
drop that column and construct a proper key from the other columns. I
am willing to bet that you will fidn that you have a lot of invalid and
redudant data in this "non-table".|||>From a strict performance perspective, the presence or absence of keys
has no real effect, especially given the fact that you've managed to
collect 3.5 million records of data without relational constraints.

Your consultant probably meant to encourage you to build a unique
clustered index, which is built by default when you add a primary key
constraint. However, they are not the same; a clustered unique index
can exist without a primary key, and a primary key need not be
clustered (it must, however, be unique). Check the Books OnLine for
clustered indexes, or visit www.sql-server-performance.com for more
help with indexes.

Stu|||(newtophp2000@.yahoo.com) writes:
> We have a table that has an identity field along with 5 other domain
> fields. The identity field is not declared as a primary key. The
> table has 3.5 million records.
> A consultant was hired recently to provide insight. His major
> recommendation: modify the table to make the identity field a primary
> key (i.e., alter table add constraint...)
> Is that sound advice? Is it OK to have a table with identity but no
> primary keys? What would be the impact on performance?

If the table does have a primary key, defining one is a very good idea.
If the identity column is the only column that is unique in the table,
then there is not much choice.

It's difficult to say what the performance might be, since I don't know what
indexes there are on the table today. But if there are none at all, then
adding an index on the identity column is likely to improve things.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> If the table does have a primary key, defining one is a very good idea.
> If the identity column is the only column that is unique in the table,
> then there is not much choice.
> It's difficult to say what the performance might be, since I don't know what
> indexes there are on the table today. But if there are none at all, then
> adding an index on the identity column is likely to improve things.

Erland & Stu,

Thank you very much for your input; it was right on the money. The
table as it stands does not have any indexes and the identity field
provides the uniqueness criteria for us. The performance is quite
good. We will do some tests to see the impact of a primary
key/clustered index on overall performance before moving forward.

Identity Field plus leading characters

I was wondering if the identity collumn of a table can include the same
first 3 characters. All I want to do is add DMC to the front. If I cant
do this in the database, I guess I could just append it to the data when
displaying it. But if this is possible I could really do with using it.
Thanks in advance!
SteveWhy don't you use a view:
CREATE VIEW dbo.v_foo
AS
SELECT idColumn = 'DMC'+RTRIM(fooID) FROM dbo.foo
Or a computed column:
CREATE TABLE dbo.foo
(
fooID INT IDENTITY(1,1),
idColumn AS CONVERT(VARCHAR(12),'DMC'+RTRIM(fooID))
)
"Dooza" <steve@.dont.spam.me.dooza.tv> wrote in message
news:%23CwbtDvsFHA.1256@.TK2MSFTNGP09.phx.gbl...
>I was wondering if the identity collumn of a table can include the same
>first 3 characters. All I want to do is add DMC to the front. If I cant do
>this in the database, I guess I could just append it to the data when
>displaying it. But if this is possible I could really do with using it.
> Thanks in advance!
> Steve|||It is doable, but it does not mean it is helpful. You will be wasting disk
space.
create table t1 (
c1 int not null identity unique,
c2 as 'DMC' + ltrim(c1)
)
insert into t1 default values
insert into t1 default values
insert into t1 default values
select * from t1
drop table t1
go
AMB
"Dooza" wrote:

> I was wondering if the identity collumn of a table can include the same
> first 3 characters. All I want to do is add DMC to the front. If I cant
> do this in the database, I guess I could just append it to the data when
> displaying it. But if this is possible I could really do with using it.
> Thanks in advance!
> Steve
>|||Hi Aaron,
Thats not a bad idea. Thanks for your help.
Steve
Aaron Bertrand [SQL Server MVP] wrote:
> Why don't you use a view:
> CREATE VIEW dbo.v_foo
> AS
> SELECT idColumn = 'DMC'+RTRIM(fooID) FROM dbo.foo
> Or a computed column:
> CREATE TABLE dbo.foo
> (
> fooID INT IDENTITY(1,1),
> idColumn AS CONVERT(VARCHAR(12),'DMC'+RTRIM(fooID))
> )
>
>
> "Dooza" <steve@.dont.spam.me.dooza.tv> wrote in message
> news:%23CwbtDvsFHA.1256@.TK2MSFTNGP09.phx.gbl...
>
>
>|||Hi there,
How much wasted space are we talking about here? I dont think I will be
having more than 5000 records.
Steve
Alejandro Mesa wrote:
> It is doable, but it does not mean it is helpful. You will be wasting disk
> space.
> create table t1 (
> c1 int not null identity unique,
> c2 as 'DMC' + ltrim(c1)
> )
> insert into t1 default values
> insert into t1 default values
> insert into t1 default values
> select * from t1
> drop table t1
> go
>
> AMB
> "Dooza" wrote:
>|||> How much wasted space are we talking about here? I dont think I will be
> having more than 5000 records.
Well, that's really not the point, I suppose. I see no reason to store
'DMC' in the table at all, if *every signle row* will always be prefixed by
it. This sounds more like a job for the presentation layer or, as I
previously suggested, a view. Taking more disk space than necessary is a
subjective thing, but in general, I think it is a mistake to do it when it
buys you nothing, such as in this case.
Driving 120 mph is dangerous, but how dangerous is it really? Not very, so
long as you don't hit something. :-)|||Aaron Bertrand [SQL Server MVP] wrote:
>
> Well, that's really not the point, I suppose. I see no reason to store
> 'DMC' in the table at all, if *every signle row* will always be prefixed b
y
> it. This sounds more like a job for the presentation layer or, as I
> previously suggested, a view. Taking more disk space than necessary is a
> subjective thing, but in general, I think it is a mistake to do it when it
> buys you nothing, such as in this case.
> Driving 120 mph is dangerous, but how dangerous is it really? Not very, s
o
> long as you don't hit something. :-)
I completely agree with you. I will do as you suggest, and use a view.
Thanks for your help!
Steve|||Dooza wrote:
> Aaron Bertrand [SQL Server MVP] wrote:
>
>
> I completely agree with you. I will do as you suggest, and use a view.
> Thanks for your help!
> Steve
One extra thing I have thought about. Say I wanted the ID to start with
DMC0001, the first ID is going to be 1, not 0001. How can I do this?
Steve|||> One extra thing I have thought about. Say I wanted the ID to start with
> DMC0001, the first ID is going to be 1, not 0001. How can I do this?
'DCM'+RIGHT('0000'+RTRIM(col_name),4)|||> One extra thing I have thought about. Say I wanted the ID to start with
> DMC0001, the first ID is going to be 1, not 0001. How can I do this?
And just as a warning, you said before that you would only have 5000 rows,
however please remember that an IDENTITY column is prone to gaps
(transaction rollbacks, deletes) so I'm not so sure I agree that reserving
only 4 digits is a wise choice. Or, at least, I hope you are prepared to
re-work everything when you need to make more room.
A