Hi, I use SQL Server Enterprise Manager for MS SQL Express and i want
to change the column properites for [int]
type of column, but I cann't change it.

More in details...
I'd like to set autoincremntal option and in order to change that I am
trying to change Identity specification from no to yes, without
success.
It is primary key too,
Name AricleID,
int type,
Primary set of binding --> ((0))
Full text specification --> No
Identity specification --> No --> I want to change this

Everything else is disabled

Thanks

Mike (ablyplus (AT) yahoo (DOT) com) writes:
First of all, why do you want to use IDENTITY? IDENTITY is primarily
of interest when you have many simultaneous insert operations. In that
case, rolling your own with SELECT MAX causes serialization and poor
throughput.

But if you don't have a high-concurrency scenario, there is little reason
to use IDENTITY. It's likely to cause your more trouble than benefit in
the long run. Rolling your own is very simple:

BEGIN TRANSACTION

SELECT @id = coalesce(MAX(id), 0) + 1 FROM tbl WITH (UPDLOCK)

INSERT (id, ,...)
VALUES (@id, ....)

COMMIT TRANSACTION

One of the many problems with IDENTITY is the one you are facing now:
there is no ALTER TABLE syntax for adding the IDENTITY property to a
column, but you need to create a new table and copy data over, and in
that process, make sure that you include all triggers, indexes and whatnots.

Why SSMS will not let you change the propery, I don't know, but it's a
good thing that it does. SSMS would generate the kind of update I
described above. And had SSMS done it right, it would have been a good
thing, but the true story is that a number of serious bugs with that
script engine. I strongly advice you to implement all table changes
with ALTER TABLE.




--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

I want to use Identity specification because I'd like to get
autoincremental values started from 1 to n

Now, for ID i get some values like 2347658...

I want to set as follows:
data type "int" (or other numeric type),
Identity=Yes,
Identity Seed=1 and Identity Increement=1

On Fri, 23 Nov 2007 22:29:19 +0000 (UTC), Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

Mike (ablyplus (AT) yahoo (DOT) com) writes:
For the existing data? Why? Assuming that the id is an artificial key,
the values are of no importance, and 5 is as good as 98987.




--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

For me is important to be 1,2,3,4,5,6 ... because it is important for
the business logic of application, now, I have some random values
instead.

Please, help me, what is the best way to do that.

Thanks
On Sat, 24 Nov 2007 17:25:24 +0000 (UTC), Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

Mike wrote:

Fix the application. Failing that, I think this will work:

1) Empty the table
2) Follow instructions at http://doc.ddart.net/mssql/sql70/dbcc_5.htm
3) Reload the table

Mike <ablyplus (AT) yahoo (DOT) com> wrote in news:urrgk3h811nj02q97ifojjlvvoaelkvkmm@
4ax.com:

Then you don't want to use IDENTITY. You'll just end up
with this problem later, over all again.

Don't be stubborn.... FIX THE APPLICATION.

Mike (ablyplus (AT) yahoo (DOT) com) writes:
So you need the series to be continuous? That is, gaps are not
permitted? In such case you must not use IDENTITY. Watch this:

CREATE TABLE myident(a int IDENTITY,
b int NOT NULL)
go
INSERT myident(b) VALUES (12)
INSERT myident(b) VALUES (NULL)
INSERT myident(b) VALUES (122)
go
SELECT * FROM myident ORDER BY a
go
DROP TABLE myident

An IDENTITY value is "consumed" no matter the INSERT succeeds or
not. And this is precisely why IDENTITY is good for scalability: there
is no number that is locked and which causes serialisation.

If you what to change the ID to be a running number, you can do this
with the existing data:

UPDATE tbl
SET ID = b.rowno
FROM tbl a
JOIN (SELECT ID, rowno = row_number() OVER(ORDER BY ID)
FROM tbl b) ON a.ID = b.ID

But do this first in BEGIN/ROLLBACK TRANSACTION, because I did not test it.

As for assigning new ids, please review my previous post in thread.

I said previously that you needed to rebuild the table, but that is
not true. You can use ALTER TABLE ALTER COLUMN to add an IDENTITY column,
and then use ALTER TABLE DROP COLUMN to get rid of the old. Finally,
use sp_rename to rename the column.

But whatever: do under no circumstances use the table-designer GUI
in Mgmt Studio. It is not reliable.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

I think that you missed the concept of IDENTITY and the Relational
Model. A data type in SQL has to:

1) NULL-able
2) More than one column can have the same data type
3) Has to take CHECK() constraints
4) Appropriate computations can done on it (numeric, string or
temporal)

IDENTITY has none of the properties of a data type because it is not a
data type at all. It is an exposed physical locator attached to a
table, not a property of a column. It is derived from the physical
storage used on one machine, like pointer chains in the old
navigational DBs or row_ids or hash tables.

What does this mean in your Logical data model? Since it has to
reference something in the reality of that data model to be a valid
RDBMS, how do you validate and verify it?

I would guess that you do none of these basic things, but are
mimicking a sequential tape file application which depends on counting
records in procedural code. Do you have cursors, too?

The whole idea of SQL is to use sets and declarative code. This is
probably just the tip of the iceberg and all you will have is more and
more kludges piled on each other. The thing will run for awhile, but
it will choke from lack of data integrity or the inability to scale up
or to port to another platform.

Fix the design, then fix the application.

IDENTITY is a property that we give to one column in the table in the same
vain that we can only give the PRIMARY KEY property to one column in the
table.

The column that has the IDENTITY property can have all the aspects you speek
of - you are compeltely wrong.

Rubbish, it's the property of the column.

Will you PLEASE RTFM and STOP guessing!

What total utter rubbish.

People use IDENTITY successfully for surrogate key and they follow all
Codd's rules for surrogates - but the DB world has moved on from Codd's
original definitions - see Date and others for a start.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]


"--CELKO--" <jcelko212 (AT) earthlink (DOT) net> wrote