HELP!!-Urgent

"I've created a customer table and I want to give each customer a
unique id e.g 'ES001' But sql server won't let me I've looked at
various books to no avail.

My second question is when I delete a row from a table which had an ID
of 2 when I put in a new row it gives the new row and ID of 3 how do i
make it give the row an ID of 2."You can use a view, trigger or a calculated column to derive a
meaningful surrogate key from your data, or you can generate it in your
application. What exactly do you have a problem with? What do you mean
by "sql server won't let me"?

As for your second question, the whole point of an IDENTITY key (I
assume that's what you are referring to) is that it is internal,
auto-generated and meaningless. If you care about what the value should
be then you shouldn't use IDENTITY. If you are just trying to number
each row then why put that information in the table at all - just
display the numbers when you query it or print a report.
--
David Portas
SQL Server MVP
--|||Salim (s_aboobaker@.hotmail.com) writes:
> "I've created a customer table and I want to give each customer a
> unique id e.g 'ES001' But sql server won't let me I've looked at
> various books to no avail.

CREATE TABLE customers (id char(5) NOT NULLL,
...
CONSTRAINT pk_customers PRIMARY KEY (id))
go
INSERT customers (id, ...)
VALUES ('ES001', ...

> My second question is when I delete a row from a table which had an ID
> of 2 when I put in a new row it gives the new row and ID of 3 how do i
> make it give the row an ID of 2."

INSERT tbl (id, ...)
VALUES (2, ...)

As David said, don't use IDENTITY if you want to specify the key values
yourself.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Ehere to begin? You are doing everything wrong because you do not
understand the basic concepts of RDBMS. A table is not a file; it
models an entity in reality. To find a unique identifier, you need to
look at your data model.

**by definition** the IDENTITY extension in T-SQL can never be a key.
It is a sequential numbering that id derived from the state of the
hardware at the time of insertion. It has nothing whatsoever to do
with your data model or the reality being modelled.

What you have is a file system written in SQL. We used to re-use fixed
length preallocated records in a lot of the early disk OS, just like
you are trying to do.

Please get a book on the basics before you learn all the kludges that
people will give you and learn to do it right.|||--CELKO-- (jcelko212@.earthlink.net) writes:
> Ehere to begin? You are doing everything wrong because you do not
> understand the basic concepts of RDBMS. A table is not a file; it
> models an entity in reality. To find a unique identifier, you need to
> look at your data model.
> **by definition** the IDENTITY extension in T-SQL can never be a key.
> It is a sequential numbering that id derived from the state of the
> hardware at the time of insertion. It has nothing whatsoever to do
> with your data model or the reality being modelled.

Where do you get all the crap from?

This is the original post?

"I've created a customer table and I want to give each customer a
unique id e.g 'ES001' But sql server won't let me I've looked at
various books to no avail.

My second question is when I delete a row from a table which had an ID
of 2 when I put in a new row it gives the new row and ID of 3 how do i
make it give the row an ID of 2."

No talk of IDENTITY, no talk about files. In fact it is obvious that
ES001 has nothing to do with IDENTITY.

> What you have is a file system written in SQL. We used to re-use fixed
> length preallocated records in a lot of the early disk OS, just like
> you are trying to do.
> Please get a book on the basics before you learn all the kludges that
> people will give you and learn to do it right.

All I can say to unexperienced people who are following this group is
to put Joe Celko in your kill files. He can't read the posts he replies
to properly. He repeats misconceptions about SQL Server all over again.
His examples are full of sloppy errors. His tone against unexperienced
users is unacceptable.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||s_aboobaker@.hotmail.com (Salim) wrote in news:d84b91a3.0501280315.1f9a13c4
@.posting.google.com:

> "I've created a customer table and I want to give each customer a
> unique id e.g 'ES001' But sql server won't let me I've looked at
> various books to no avail.
> My second question is when I delete a row from a table which had an ID
> of 2 when I put in a new row it gives the new row and ID of 3 how do i
> make it give the row an ID of 2."

Somebody's not getting their money's worth out of *this* consultant.