Hello,
         please give me the syntax to add or drop the Identity property for
a column in a table using Alter table statement.
                                                      Is the syntax
different for SQL Server 7.0 and SQL Server 2000
                                                      Thank you

Hi

It is the same for both SQL Server 7.0 and 2000.

You can't change the property of a column to make it / not make it an
identity column.
The only way is to drop the column and then re-add it.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: m***@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

Show quoteHide quote

If  you know how to do a proper data model and write good SQL, you
would NEVER use an IDENTITY column in the first place.   You are just
mimicking a sequential file in an RDBMS.

This is not true. Take a customer table. A customer is identified by a
customer code. If i have ten thousand customers and i DONT use an identity
column then i have to "hit and miss" attempt to generate a unique code for
my 10,001st customer. That does nothing for useability. This is a classic
case of when an identity column is the perfect solution. Your ascertion is
just plain dumb.

You are just
No he is not. Hes simply creating a unique key. Just because the key happens
to be generated sequentially does not make the table a sequential file.


Richard

Since I use industry standards instead of proprietary non-relational
extensions.  I grab their nine-digit DUNS number when i do a credit
check.  Have you ever researched a job before you started coding?  Or
are one of those people who jsut pops a ID columns on everything so he
can start coding before thinking?

And where is this key in the reality of the data model?  How is it
validated and verified?  If you want to use a sequence, say check
numbers, then you have to maintain them in a table and provide an audit
trail for them.  A check digit would also be a good idea.

It is created inside the machinery and nothing to do with the data
model at all.  It is simply a sequential, number unrelated to the data
which is used to get to the physical location of physical storage.  Try
this with a table that has IDENTITY.

BEGIN
INSERT INTO Foobar VALUES (1,2,3);
DELETE FROM Foobar;
INSERT INTO Foobar VALUES (1,2,3);
END;

This is a logical "no-op" but what happened to the physical storage and
you "pseudo-key"?   Get a good book on basic RDBMS and learn the
differencs in logical and physical levels of design.

There is the logical data model, and then there is the physical data model.
The logical data model shouldn't contain IDENTITY columns except where
necessary, e.g., to facilitate abstraction.  Once you've finished your
logical data model, you must adjust it to account for the limitations of the
RDBMS you've chosen, to improve performance and concurrency, and to insulate
yourself and your application programs from changes that are bound to occur
to natural key values.  I put IDENTITY columns on almost every table in the
physical model.  The only exceptions are historical tables (see below) and
those tables that participate in a generalization-specialization
relationship, in which case the specialized table obtains its key value from
the most general table.  I do this for several reasons:  (1) To ensure that
each atomic value in the database is located in exactly one place, which
reduces lock contention, (2) to ensure that each row in the database has an
immutable primary key value, which insulates against natural key value and
structural changes--simplifying application program development, enabling
the use of disconnected datasets and message queues, facilitating
replication and transformation of information into OLAP databases, and
minimizing the work required to deal with a structure change, (3) to
eliminate the need for cascading updates, which increase the complexity of
locking and deadlock avoidance, (4) to improve performance because INTs
usually join faster than CHARs, and (5) to track changes to rows over time
so that I can answer questions like, "Who changed what, where, when and
how?", and "What shift was so-n-so on on July 17, 2004?"  I am able to find
out, and sometimes undo, what a user did in a single transaction, without
shutting down the system and performing a point-in-time restore.  Tracking
changes also simplifies the logical data model because I can obtain the
exact state of the database at any point in time, so I don't have to record
the column values that were used to perform some calculation in the same row
as the calculated value in case the value needs to be recalculated at some
later date, nor do I need to record the values used to produce a document,
in case the document needs to be reproduced.

IDENTITY columns values are never, NEVER presented to a user.  If the users
need to create ad-hoc queries, I create a set of views that mirror the
structure of the logical data model, replacing the IDENTITY values with the
corresponding natural key values.  Natural keys in the logical data model
become UNIQUE constraints in the physical model.

IDENTITY columns are your friend, you should embrace them because they can
save you a ton of headaches and a lot of extra code.  They do NOT, however,
eliminate the need for a sound logical data model and wise natural key
selection.


customers and I DON'T use an identity column then I have to "hit and miss"
attempt to generate a unique code for my 10,001st customer. <<
generated sequentially does not make the table a sequential file. <<
Show quoteHide quote

customers and I DON'T use an identity column then I have to "hit and miss"
attempt to generate a unique code for my 10,001st customer. <<
You seem to under the impression that everyone elses problem domains are the
same as yours. What if the customer doesn't have a DUNs number? Are you
going to require all potential customers to make a submission in order to
get one? And why would you require the users of your system to remember/key
nine digit numbers if they are never going to have more than 15,000 customer
records. Have you ever actually spoken with the users and data entry
personel of your systems? Many if not most prefer to key account codes
directly rather than having to resort to an alphabetical search.

Its about horses for courses. Your approach is all about you and some
*purist* wet dream. The rest of us live in the real world where your
approach offers no additional benefit to the customer, nor the customers'
customers, while increasing the developmental and operational costs of the
system. Not every system is enterprise level.


generated sequentially does not make the table a sequential file. <<
Customer code = Company generated code for customer which comes from
database = Identity column. Its quite simple.

Well again its quite simple, you dont. If the types of operations above are
not neccessary then you're not constrained by the fact that Identity columns
inhibit them. Therefore your point is moot.

Or maybe you could learn to read the question posed by the poster, which is
a physical question about adding/deleting an Indentity column. Forget your
*logical* differences, you've stated quite clearly that we should *never*
use them which at a physical level is just plain absurd. Developers like you
do nothing except unneccessarily inflate costs simply to satisfy their own
obsessive/compulsive desires for purity.

Richard

Using surrogate keys is beneficial at every level, not just for small
systems.  In fact, it is the larger systems that benefit the most, for a
number of reasons: lower development costs, reduced lock contention,
increased join performance,  higher availability, etc.

Show quoteHide quote

I agree.  And I hope that some day SQL Server has a surrogate key, as
defined by Dr. Cood. .  IDENTITY, GUIDs, etc. do not qualify at all.

I disagree.  A GUID does qualify.  Codd's 1979 paper mentioned having some
surrogate generation mechanism that generates database-wide unique
indentifiers.  A GUID is most definitely unique throughout a database.  I
would argue, however, that a surrogate needn't be unique throughout the
database, just unique within the entity it is defined upon.

systems.  In fact, it is the larger systems that benefit the most,  .. <<
Show quoteHide quote

DELL does exactly that!  They use an electronic payment system that
depends on this.  And with the current security requirements, you are
goignto see more and more of this.

You begin your system with the assumption of lack of growth or business
failure.  Interesting.  But as you said, not everyone works at the
enterprise level.  And some of us never will.  They do not have learn
it; they have to validate and verify it.

Simple-minded.  IDENTITY is not company generated; it is hardware
generated. Big difference!  That hardware has no polciies and
procedures, no documentation for valdiation and verification, etc.  The
classic shock for IDENTITY is what to do the first time you get a gap
in the sequence.  That is one of the first things I look for in an
internal data audit.

It takes time to design encodings.  The customer needs to have an
identifier for the accounting system, orders, etc.  What we want in
this identifier is validation (check digits, grep(), etc.) and
verification (a trusted external source which both we and the customer
agree about).  If they have no tax number, no DUNS, no SAN, not even an
email address, no anything, then we need to consider creating our own
codes.

That means setting up the mechanisms for tracking, issuing and using
the codes.  Just off the top of your head, can you name the types of
scales?  types of encoding schemes?  at least three check digit
formulas?  These are basic skills that a database designer needs.

Okay, you are saying that we hire only perfect users, perfect data
entry clerks, etc. so the system can get along fine with unrecoverable
errors because nobody will make mistakes.  And you think that I am not
living in the real world?

In the real world, you will get a SOX audit or build a data warehouse.
Doing things right instead of "quick and dirty" is vital.    Developers
spend about 80% of development costs on identifying and correcting
defects according to NIST (the National Institute of Standards and
Technology).

Would you go to a doctor who is also opposed to "purity" -- that is,
doing the job right?  But don't worry, by not wasting time on a little
research and planning, Dr. Nick Rivera  was able do that heart surgery
in 15 minutes.   Who cares what happens in a month or two?

Actually, I don't do much development any more.  I teach SQL classes
and I am one of the auditors who comes in behind sloppy, non-obsessive
newbies and cleans up the mess.

I am there to reduce the total costs of systems. Most of the lifetime
costs will be after deployment.  In the case of databases, the largest
factor is lack of data quality and getting locked into proprietary
code, so the system will not port or scale.  Both of these problems can
be avoided at the start, if you have a good database designer and
programmers with a sense of professionalism.

And look at the size of Dell. Try telling that to a burger bun distributor
with 3500 customers running small one and two employee burger joint
operations.

The assumption again is on YOUR behalf. You cant seem to get your head out
of your own experiences. Example: A distributor in a mature market with 4500
customers/potential customers. Which is a classic small business market.
Even at growth rates of 10% end over end, which is never going to happen
(bearing in mind we are talking about actually industry entrance, not sales
dollars) we are never going to need more than 15,000 customer records.

Whats to verify? Seed an Identity at 10,000 and let it run in increments of
5. A customer key has to be numeric and 5 digits in length between (using
the above example) 10,000 and 50,000. Done. You're right it is simple and
its also cost effective. All this rubbish about what generates the key is
just noise in many differenet markets. Hardware/software who cares? The
point is does it work and does it satisfy the customers immediate and mid
term needs.

constrained by the fact that Identity columns inhibit them. <<
Perfect data entry clerks? One advantage of using an identity column to
generate a customer key is that the data entry clerk DOESN'T enter it. As
you said it's hardware generated. If there is a high need/chance of
recoverability etc etc then as i said the answer is simple: DONT USE THEM.
My argument is not for always and every use of Identity columns, that would
be just plain silly..... as silly as your ascertion that we should NEVER use
them.

us live in the real world where your approach offers no additional benefit
to the customer, nor the customers' customers, while increasing the
developmental and operational costs of the system. Not every system is
enterprise level. <<
No in the real world the customer has a budget and you go to them with a
proposal and say this is what we can to for that budget. This is what you
will get and this is what you wont get. Sometimes "quick and dirty" is
exactly what they want and about all they can afford. 99% of our customers
couldn't care less about your SOX audit or NIST surveys. If you were to meet
with you'd be talking right over their heads.

simply to satisfy their own obsessive/compulsive desires for purity. <<
No i wouldn;t. But then i wouldn't go to a $300 an hour specialist when all
i need is a prescription from my GP either so i dont your analogy supports
your argument.


Being a professional is not about being a purist. Being professional means
listening to the customers wants and needs and developing a system that
suits those needs. Not running off and doing what YOU think they want. It
not about never cutting corners; its about knowing which corners you can cut
and the likely downstream effects/consequences/restrictions that cutting
such corners will incur and putting the measures in place to monitor them.

If you're dealing with large scale clients with large budgets and
sophisticated needs then yes the things you speak of are absolutely
warranted. But thats beside the point. You ascertion is that these things
are ALWAYS warranted which is just plain absurd for thousands of different
system out there.

Database designers and software engineers are not the most important people
in systems development; the customers are.....your one size fits all
approach is one of telling the customers what they'll have and what they'll
pay for and how things will be done BECAUSE ICT SAYS SO; and i can think of
of a couple of hundred small and medium business owners and managers who
would simply turn around and tell you to &^%$&* off. Thats the real world.
Where money talks and ICT is still just another cost center not an
investment.

In the classroom you might well have unlimited resources to fulfull your
perfect system requirement but in the real world there are fiscal
constraints where your TCO arguments dont always fly. Identity columns have
their place and its unreasonable to suggest otherwise. And if im wrong then
why does every DBMS package i can ever remember looking at have inbuilt for
support them. Answer: Because there is a demand for them. Thats the real
world. It might be a different real world from your real world but its a
real world never the less. One size fits all just plain doesn't work. Your
argument is bogus and contrary to everything i see when i look around. Hence
my wet dream theory.

Then you ought to buy a package and not have one of your two employees
flipping burgers and designing databases :)

LOL!  Do you believe that people deliberately have auto accidents, so
the use of seat belts is not a good idea.  Your duty is to prevent
users from hurting themselves.

No.  It is about solving **real** problems for the customer.  "Hey,
doctor, shut up and give me more drugs!  I don't want to know about
cancer treatments.  That is academic crap and all I want is short-term
results."

The customers are paying you for your superior judgement and
experience.  If they ask you to do something you know to be dangerous,
you are obligated to refuse.  In fact, I just had a few weeks of email
with an old friend reviewing a falwed design; he quit the job because
they would not listen to his very detailed analysis of the current
design flaws.  He quit and passed my name to his former client, if they
want a second opinion.  What is scary is that they are a medical
company.

"One size fits all" is the philosophy of IDENTITY and other exposed
physical locators, not my "careful research and industry standard
codes" approach :)

Everyone has a TCO argument, even if they shove their hands over their
ears and scream like a child denying reality.

with 3500 customers running small one and two employee burger joint
operations. <<

No im talking about using your DUNS requirement. A burger shop isn't going
to get a dun just because their burger bun supplier requires one for their
IS.



the answer is simple: DONT USE THEM. <<

Well of course it is but your argument is that you cant prevent them from
hurting themselves when using IDENTITY columns which is false.



developing a system that suits those needs. <<

Show quoteHide quoteAnd his resignation is precisely my point. The customer doesn't always want
to hear it. And yes they do say this is all academic crap and all i want are
short term results. They say it often. Their not geeks like us. They dont
see the beauty of well architected solutions. They see cost centers. And
they often think short term. And they often want it yesterday. Dealing with
customers is about finding the middle ground between what they want and what
is feasible. Superior judgement and experience sure. But we cant have it all
our own way. You often have to bend the rules and Identity columns can be a
great way to do that. You can say my way or the highway all you like but the
customer will just up and finding themselves another contractor.

world never the less. One size fits all just plain doesn't work. <<

Show quoteHide quoteNo "one size fits all" is like you saying "never use them". Let me
reiterate:everything you've suggested is bang on...but only for some
customers not for all. Thats one size fits all. My argument is that
sometimes we do it your way; sometimes we do it their way. Sometimes
another.

At the end of the day i think we'll just have to agree to disgree because i
know that 60% of our revenue is derived from systems that feature Identity
columns. We've yet to see an excessive downside to using them in these
projects and in terms of cost/benefit/useability etc etc they have been very
beneficial. In terms of these projects, your purist approach reeks of
unneccesary complexity and additional baseline development costs, that have
no additional benefits to the customer in either the short or mid term
ranges. For these type of projects a 5-10% increase in cost can kill its
positioning and be a deal total breaker. Yours is an argument that i just
dont buy.

As the Germans used to refer to us Slavs, "the lesser races" who do not
require the same level of living as the "Proper Races"/  Or the British
class system?  Or the Indian Caste system?  Would you got to a doctor
who thinks this way?   This is not professionalism.

Hot damn!  If I give you 25% of what I bill them in the next 2-5 years
to correct errors (on the assumption they are still in business) and
get them thru a SOX audit so they do not go to a federal prison.
Programmers like this have given me a six digit income in the last two
decades.

Hello?  The value of an IDENTITY column is arbitrary. The only requrement is
that its value for each row be unique within the table.  A gap does not
imply that that at one time a row existed.  How can you audit based on that
criterion?  How can you in good conscience charge your customers for that?
Used correctly, a surrogate (IDENTITY, GUID, etc.) neither adds nor detracts
from the value of a row.  Its purpose is to provide a more reliable and less
time sensitive mechanism to identify a row.  The relational model is
three-dimensional: (table, primary key, column). A result set returned from
a query is a snapshot of the information that was available at a specific
point in time.  By the time a user finishes working with the information and
is ready to commit his changes, the value of the database will most probably
have changed.  A snapshot taken at a later point in time may not be the same
as the first.  In order for an application program to consistently identify
the correct row to update, the key value must not have changed between the
point in time that the application read the information and the point in
time that the application is ready to commit the changes, or some mechanism
must be found to detect any key value changes that occurred during that
interval and deal with them.  There are three ways to handle this situation.
(1) You could lock the rows when the application reads them, and hold the
locks while the user is working on the rows, or until the user returns from
lunch or golfing.  (2) You could add a key change log  to record changes to
key values as they occur, so that the application program can determine if a
key value changed and what the new value for the key is so it can locate the
correct row; of course this introduces a lot of extra code to record key
changes and to detect them in the application program.  The log is necessary
because two key values may have been swapped, so the same rows that were
read out could refer to different rows when the changes are ready to be
committed.  It's not enough to simply fail if the row doesn't exist anymore.
(3) You can use a surrogate key to permanently identify each row in the
table, which has none of the disadvantages of locking the rows for an
extended amount of time as in (1), or the additional overhead required to
log and detect key changes as in (2).  It is necessary to detect whether
another user changed a row that you're working on, but that's a lot easier
to do if you know the primary key value will not change between the time
that you read a row and the time it is ready to be commited.


require all potential customers to make a submission in order to get one? <<
digit numbers if they are never going to have more than 15,000 customer
records [sic]. <<
database = Identity column. Its quite simple. <<
Show quoteHide quoteconstrained by the fact that Identity columns inhibit them. <<
us live in the real world where your approach offers no additional benefit
to the customer, nor the customers' customers, while increasing the
developmental and operational costs of the system. Not every system is
enterprise level. <<
simply to satisfy their own obsessive/compulsive desires for purity. <<
Show quoteHide quote

You love to overlook the fact that locating a DUNS value in a timely fashion may
not be feasible for the system in question. The company for which you are
designing the system may not want to pay for DUNS lookups. Even if they are
willing, it might take too long for data entry people to locate the value. Most
importantly, it may be the case that the data necessary to accurately locate a
DUNS may not be available.

Using this logic, how do you handle identifying something like a customer? You
can't use government identification values for privacy reasons. You can't hold
on to credit card numbers because those change and people do business by means
other than credit cards. You can't use email addresses because believe it or not
some people do not have an email address. So what value "in the reality of the
data model" do you use to identify a customer?


Thomas

<< So what value "in the reality of the data model" do you use to identify a
customer>>
That's easy; by definition a "customer" is someone who has paid you
something for something. So, to uniquely identify your customers - just
ensure that each one has paid you some unique dollar/cents amount (or Euros,
or cows, or whatever is the going currency). So, for a single scoop of ice
cream - you can start out by charging the first customer $1.00  Then the
next customer shows up and you charge him/her $1.01 -- and so on. Imagine
how your profit margins will shoot up as you near you 100,000th customer.
Have your customers hang on to their receipts (or just take a digital photo
for convenience). Next time they show up you just charge them the same
amount as before. If they forget their receipt AND your photo-dollar-amount
database has crashed, then just ask how much they paid on their last visit;
they'll certainly know that). This all has the nice side-effect of
motivating customers to become "early" customers - which will drive your
early business (and likely repeat business). If you accidentially duplicate
a customer, then you'll at least have your "data repair costs" somewhat
offset by the fact that they will have paid more (perhaps substantaily more)
during subsequent purchases. Brilliant, simple, and cost-effective.

-HTH


Show quoteHide quote

ROFL. Nice. I'll have to bring this one up at my next design meeting.


Thomas

Look at the current laws; you are required to have certain government
identification values for security reasons. Scary, and probably going
to get worse.

Better tell PayPal and eBay about that.  They seem to do find with
credit card numbers.

Better most of the Internet businesses that do exactly that.  Their
customers self-select by virtue of being on the Internet to place the
orders in the first place.

It depends on the reality.  I pulled up the DUNS as an example of an
industry standard that is widely used and well-known. The SAN (Standard
Address Number) in the book trade is a more specialized version.

DUNS is a problem because a single company may have several of them, or they
may have none.  You could have two warehouses, one in Mechanicsville, and
one in Harrisburg, and you could have a ship to DUNS and a bill to DUNS for
each warehouse.  (Haven't you heard of EDI?)  So, how do you really identify
the customer?

people do business by means other than credit cards. <<
not have an email address. <<
a customer? <<
Show quoteHide quote

Multiple DUNS are not a problem -- you build a table of all the DUNS
that relate them to what or who you define as customer for your
reporting system.

X3H2 used to talk to X12.  I wish it had worked out better, but it
looks like various XML versions are going to do the job.

This is a common fallacy. You have to have identification at time of purchase to
authenticate that the card holder, for example, is the same person named on the
credit card. However, that is a *far* cry from *capturing* that person's
identification number. There are no privacy issues if you don't capture the
number in the first place. Yes, there are times when you must capture said
governement number, however that is orthogonal to the discussion at hand.

How do you generally capture and identify customers in say a standard retail
establishment? You can't store people's driver's license numbers for a host of
reason including privacy, out of state numbers, foreigners and people that
simply do not have a driver's license number.

Yes, but the do not IDENTIFY customers by their credit card number. Granted,
they do identify them by the email address but that has caused problems where a
couple was sharing the same email address but wanted separate PayPal/EBay
accounts.

Yes, most Internet businesses *capture* an email address. However, they do not
IDENTIFY customers by their email address. Like I said, the scenario where two
people share an email address but want separate accounts happens all the time.
Further, what happens when that person's email address changes? Again, this
already causes problems with Internet business (I know, I've built transaction
sites where we had this problem).

So, I am honestly asking Joe, how do you generally identify customers in your
systems? I'm truly curious how you handle this problem including all of the
privacy issues, lack of data issues and such.


Thomas

The syntax to add an IDENTITY column is:

ALTER TABLE tableName ADD columnName INT IDENTITY(seed, increment) NOT NULL

Where seed is the number you want to start with, and increment is the amount
to increment on each successive INSERT.  For example, ALTER TABLE Employee
ADD EmployeeID INT IDENTITY(1, 1) NOT NULL


The syntax to drop an IDENTITY column is:

ALTER TABLE tableName DROP columnName




Show quoteHide quote

what if you are creating a DTS job and editing the default sql code to drop
and reccreate a table? given the table has identity values, how can i use
existing id values but enable identity property?


Show quoteHide quote

Problem With Stored Procedure
Undo Functionality
Transaction in SP
problem with context info
How to split a column