SQL Express - Identity specification property - how to change

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

Re: SQL Express - Identity specification property - how to change

Mike (ablyplus@yahoo.com) writes:
> 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

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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: SQL Express - Identity specification property - how to change

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
wrote:

>Identity specification

Re: SQL Express - Identity specification property - how to change

Mike (ablyplus@yahoo.com) writes:
> I want to use Identity specification because I'd like to get
> autoincremental values started from 1 to n

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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: SQL Express - Identity specification property - how to change

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
wrote:

>Mike (ablyplus@yahoo.com) writes:
>> I want to use Identity specification because I'd like to get
>> autoincremental values started from 1 to n
>
>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.

Re: SQL Express - Identity specification property - how to change

Mike wrote:

> 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.

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

Re: SQL Express - Identity specification property - how to change

Mike wrote in news:urrgk3h811nj02q97ifojjlvvoaelkvkmm@
4ax.com:

> 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.
>

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.

Re: SQL Express - Identity specification property - how to change

Mike (ablyplus@yahoo.com) writes:
> 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.

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.

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

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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: SQL Express - Identity specification property - how to change

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.

>> 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. <<

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.

Re: SQL Express - Identity specification property - how to change

>
> 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.

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.

> It is an exposed physical locator attached to a
> table, not a property of a column.

Rubbish, it's the property of the column.

Will you PLEASE RTFM and STOP guessing!

> 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 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--" wrote in message
news:9ee9fc71-1654-43c6-b03d-a738a875e881@w40g2000hsb.google groups.com...
>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.
>
>>> 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. <<
>
> 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.

Re: SQL Express - Identity specification property - how to change

Tony Rogerson (tonyrogerson@torver.net) writes:
>> 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.
>...
> The column that has the IDENTITY property can have all the aspects you
> speek of - you are compeltely wrong.

Sorry, Tony, but sometimes Joe is right. An IDENTITY column cannot be
nullable, and you can only have one of them in a table. Not that see
any practical importance of this.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: SQL Express - Identity specification property - how to change

> 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.

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

Yes, like the PRIMARY KEY property, it is a property of the *table* as
a whole. It is not a data type, which was my point. It is based on
the order of physical insertion into physical storage in a particular
table of a particular schema on a particular piece of hardware in a
particular release of SQL Server. It has nothing to do with a data
model.

Dr. Codd admitted the PRIMARY KEY property was a mistake shortly after
his first papers. Unfortunately, it got into SQL because we were
basing the first SQL products on a sort key used for tape merging!

>> The column that has the IDENTITY property can have all the aspects you speak of - you are completely wrong. <<

Try this:

CREATE TABLE Foobar
(foo_id INTEGER IDENTITY (1,1) NULL,
bar_code INTEGER NOT NULL);

INSERT INTO Foobar (42);

It failed, didn't it? It is not NULL-able! This is a Basic
requirement of a data type.

INSERT INTO Foobar (foo_id, bar_code) VALUES (-12, 4);

It failed, didn't it? It does not allow insertion of a value; another
basic data type property. Another one is

INSERT INTO Foobar
SELECT new_bar FROM Floob;

What is the ordering of the foo_id values? Unpredictable! But since
it is exposed to the user, it should be deterministic. This is a
version of the Information Principle, but you probably don't see it.
Now try this one:

UPDATE Foobar SET foo_id = foo_id -2;

It failed, didn't it? you are not allow to update IDENTITY; another
basic data type property. They do DELETE okay, however.

Let's add an explicit DEFAULT clause! Opps! That is not allowed and
that is a basic column property.

CHECK() constraints seem to work with IDENTITY, but you get some weird
stuff.

Since you don't read Standards, UNIQUE and PRIMARY KEY constraints are
called uniqueness constraints in SQL. I can have UNIQUE on a NULL-
able column. The PRIMARY KEY is defined as UNIQUE + NOT NULL, with a
restriction that it is used only once. This makes the column(s) the
default targets of REFERENCES clauses and many older SQL products do
special things with it to speed up searching. There have been
proposals that we drop it in modern RDBMS, but the "code museum
effect" is very strong

>> People use IDENTITY successfully for surrogate keys 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. <<

No, they don't; how many times do I have to post the quote about being
exposed to a user? That means you can see it, you had to explicitly
declare it and you can do operations on it.

And you might actually want to read some of Date's papers. While we
disagree on much, we are both "big fans" of Dr. Codd and natural keys.
He is probably more so than me -- I don't mind artificial keys if you
can control them properly.

Re: SQL Express - Identity specification property - how to change

BTW, all this talk just reminded me of a problem at a client site.

They are making unfortunately frequent use of the IDENTITY column in
conjunction with Primary Key.

So... we have something like

CREATE TABLE FOO
(
FOO_ID int identity(1,1),
Server_name varchar(20)
)

And then the PK is set to FOO_ID

Then a script that says:
insert into FOO Values ('server_bob');
insert into FOO Values ('server_chris');

Unfortunately, if this script is rerun, we now have in the table

1, server_bob
2, server_chris
3, server_bob
4, server_chris

So.. now we have some queries that end up seeing 1 OR 3 for server_bob and 2
or 4 for Server_chris (FOO_ID is a foreign key into other tables whose
inserts I've left out).

So, you can't do a join on FOO correctly. Very nasty.


I've brought this up a few times and still waiting for them to fix it.

Now, personally I'm a fan of the Identity column... it can be useful at
times.... this is certainly NOT one of them.


--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

"--CELKO--" wrote in message
news:653a8c55-6b2c-4ee9-8143-e1bc65682ac4@s36g2000prg.google groups.com...
>> 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.
>
>>> IDENTITY is a property that we give to one column in the table in the
>>> same vein that we can only give the PRIMARY KEY property to one column
>>> in the table. <<
>
> Yes, like the PRIMARY KEY property, it is a property of the *table* as
> a whole. It is not a data type, which was my point. It is based on
> the order of physical insertion into physical storage in a particular
> table of a particular schema on a particular piece of hardware in a
> particular release of SQL Server. It has nothing to do with a data
> model.
>
> Dr. Codd admitted the PRIMARY KEY property was a mistake shortly after
> his first papers. Unfortunately, it got into SQL because we were
> basing the first SQL products on a sort key used for tape merging!
>
>>> The column that has the IDENTITY property can have all the aspects you
>>> speak of - you are completely wrong. <<
>
> Try this:
>
> CREATE TABLE Foobar
> (foo_id INTEGER IDENTITY (1,1) NULL,
> bar_code INTEGER NOT NULL);
>
> INSERT INTO Foobar (42);
>
> It failed, didn't it? It is not NULL-able! This is a Basic
> requirement of a data type.
>
> INSERT INTO Foobar (foo_id, bar_code) VALUES (-12, 4);
>
> It failed, didn't it? It does not allow insertion of a value; another
> basic data type property. Another one is
>
> INSERT INTO Foobar
> SELECT new_bar FROM Floob;
>
> What is the ordering of the foo_id values? Unpredictable! But since
> it is exposed to the user, it should be deterministic. This is a
> version of the Information Principle, but you probably don't see it.
> Now try this one:
>
> UPDATE Foobar SET foo_id = foo_id -2;
>
> It failed, didn't it? you are not allow to update IDENTITY; another
> basic data type property. They do DELETE okay, however.
>
> Let's add an explicit DEFAULT clause! Opps! That is not allowed and
> that is a basic column property.
>
> CHECK() constraints seem to work with IDENTITY, but you get some weird
> stuff.
>
> Since you don't read Standards, UNIQUE and PRIMARY KEY constraints are
> called uniqueness constraints in SQL. I can have UNIQUE on a NULL-
> able column. The PRIMARY KEY is defined as UNIQUE + NOT NULL, with a
> restriction that it is used only once. This makes the column(s) the
> default targets of REFERENCES clauses and many older SQL products do
> special things with it to speed up searching. There have been
> proposals that we drop it in modern RDBMS, but the "code museum
> effect" is very strong
>
>>> People use IDENTITY successfully for surrogate keys 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. <<
>
> No, they don't; how many times do I have to post the quote about being
> exposed to a user? That means you can see it, you had to explicitly
> declare it and you can do operations on it.
>
> And you might actually want to read some of Date's papers. While we
> disagree on much, we are both "big fans" of Dr. Codd and natural keys.
> He is probably more so than me -- I don't mind artificial keys if you
> can control them properly.
>

Re: SQL Express - Identity specification property - how to change

> Sorry, Tony, but sometimes Joe is right. An IDENTITY column cannot be
> nullable, and you can only have one of them in a table. Not that see
> any practical importance of this.

Ok - point 1 is right, granted.

The IDENTITY isn't a column, it's a property of the column - you can't have
two PRIMARY KEY's either!

Tony.

--
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]

Re: SQL Express - Identity specification property - how to change

> CREATE TABLE Foobar
> (foo_id INTEGER IDENTITY (1,1) NULL,
> bar_code INTEGER NOT NULL);
>
> INSERT INTO Foobar (42);
>
> It failed, didn't it? It is not NULL-able! This is a Basic
> requirement of a data type.

I realise by saying DIDN'T IT you actually haven't even run the syntax
yourself because you would find the CREATE TABLE itself fails.

Do you even have SQL SErver installed?

>
> INSERT INTO Foobar (foo_id, bar_code) VALUES (-12, 4);
>
> It failed, didn't it? It does not allow insertion of a value; another
> basic data type property. Another one is

That's the whole point as you well know - it';s immutable so you can't do
that - follows Codd's rule....

> INSERT INTO Foobar
> SELECT new_bar FROM Floob;
>
> What is the ordering of the foo_id values? Unpredictable! But since
> it is exposed to the user, it should be deterministic. This is a
> version of the Information Principle, but you probably don't see it.
> Now try this one:

Follows Codd's rule....

Why do you need ordering? foo_id is used as a surrogate key.

Why do you not have a UNIQUE constraint on it and a PRIMARY KEY on bar_code?

> UPDATE Foobar SET foo_id = foo_id -2;
>
> It failed, didn't it? you are not allow to update IDENTITY; another
> basic data type property. They do DELETE okay, however.

How many more times - IDENTITY is NOT NOT NOT NOT a flaming DATA TYPE!!!!!!!

It's a PROPERTY of a column.

Again, follows Codd's rule for immuatable.

> Let's add an explicit DEFAULT clause! Opps! That is not allowed and
> that is a basic column property.

How many more times - IDENTITY is NOT NOT NOT NOT a flaming DATA TYPE!!!!!!!

It's a PROPERTY of a column.

>
> CHECK() constraints seem to work with IDENTITY, but you get some weird
> stuff.

Like what? Example - you are making it up again. Myth mungering....

> Since you don't read Standards, UNIQUE and PRIMARY KEY constraints are
> called uniqueness constraints in SQL. I can have UNIQUE on a NULL-
> able column. The PRIMARY KEY is defined as UNIQUE + NOT NULL, with a
> restriction that it is used only once. This makes the column(s) the
> default targets of REFERENCES clauses and many older SQL products do
> special things with it to speed up searching. There have been
> proposals that we drop it in modern RDBMS, but the "code museum
> effect" is very strong

Yes, I read Books Online on a daily basis.

> No, they don't; how many times do I have to post the quote about being
> exposed to a user? That means you can see it, you had to explicitly
> declare it and you can do operations on it.

And how many more times do you want to be the odd ONE out in the whole
industry?

The user is the application, the user is NOT the developer.

Using IDENTITY as a surrogate is fine for application plumbing so long as
the user in front of the application does not see or use it.

>
> And you might actually want to read some of Date's papers. While we
> disagree on much, we are both "big fans" of Dr. Codd and natural keys.
> He is probably more so than me -- I don't mind artificial keys if you
> can control them properly.

You've just got a beef about IDENTITY because you totally misunderstand the
basics on it - go back and read the f'in manual.

--
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]

Re: SQL Express - Identity specification property - how to change

> CREATE TABLE FOO
> (
> FOO_ID int identity(1,1),
> Server_name varchar(20)
> )

Just get them to do this....

> CREATE TABLE FOO
> (
> FOO_ID int identity(1,1),
Server_name varchar(20) ----> UNIQUE <-----
> )

Then all is well, well it would have been if they are started out properly
in the first place.

Tony.

--
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]


"Greg D. Moore (Strider)" wrote in message
news:13kn4214hevva58@corp.supernews.com...
>
>
> BTW, all this talk just reminded me of a problem at a client site.
>
> They are making unfortunately frequent use of the IDENTITY column in
> conjunction with Primary Key.
>
> So... we have something like
>
> CREATE TABLE FOO
> (
> FOO_ID int identity(1,1),
> Server_name varchar(20)
> )
>
> And then the PK is set to FOO_ID
>
> Then a script that says:
> insert into FOO Values ('server_bob');
> insert into FOO Values ('server_chris');
>
> Unfortunately, if this script is rerun, we now have in the table
>
> 1, server_bob
> 2, server_chris
> 3, server_bob
> 4, server_chris
>
> So.. now we have some queries that end up seeing 1 OR 3 for server_bob and
> 2 or 4 for Server_chris (FOO_ID is a foreign key into other tables whose
> inserts I've left out).
>
> So, you can't do a join on FOO correctly. Very nasty.
>
>
> I've brought this up a few times and still waiting for them to fix it.
>
> Now, personally I'm a fan of the Identity column... it can be useful at
> times.... this is certainly NOT one of them.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>
> "--CELKO--" wrote in message
> news:653a8c55-6b2c-4ee9-8143-e1bc65682ac4@s36g2000prg.google groups.com...
>>> 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.
>>
>>>> IDENTITY is a property that we give to one column in the table in the
>>>> same vein that we can only give the PRIMARY KEY property to one column
>>>> in the table. <<
>>
>> Yes, like the PRIMARY KEY property, it is a property of the *table* as
>> a whole. It is not a data type, which was my point. It is based on
>> the order of physical insertion into physical storage in a particular
>> table of a particular schema on a particular piece of hardware in a
>> particular release of SQL Server. It has nothing to do with a data
>> model.
>>
>> Dr. Codd admitted the PRIMARY KEY property was a mistake shortly after
>> his first papers. Unfortunately, it got into SQL because we were
>> basing the first SQL products on a sort key used for tape merging!
>>
>>>> The column that has the IDENTITY property can have all the aspects you
>>>> speak of - you are completely wrong. <<
>>
>> Try this:
>>
>> CREATE TABLE Foobar
>> (foo_id INTEGER IDENTITY (1,1) NULL,
>> bar_code INTEGER NOT NULL);
>>
>> INSERT INTO Foobar (42);
>>
>> It failed, didn't it? It is not NULL-able! This is a Basic
>> requirement of a data type.
>>
>> INSERT INTO Foobar (foo_id, bar_code) VALUES (-12, 4);
>>
>> It failed, didn't it? It does not allow insertion of a value; another
>> basic data type property. Another one is
>>
>> INSERT INTO Foobar
>> SELECT new_bar FROM Floob;
>>
>> What is the ordering of the foo_id values? Unpredictable! But since
>> it is exposed to the user, it should be deterministic. This is a
>> version of the Information Principle, but you probably don't see it.
>> Now try this one:
>>
>> UPDATE Foobar SET foo_id = foo_id -2;
>>
>> It failed, didn't it? you are not allow to update IDENTITY; another
>> basic data type property. They do DELETE okay, however.
>>
>> Let's add an explicit DEFAULT clause! Opps! That is not allowed and
>> that is a basic column property.
>>
>> CHECK() constraints seem to work with IDENTITY, but you get some weird
>> stuff.
>>
>> Since you don't read Standards, UNIQUE and PRIMARY KEY constraints are
>> called uniqueness constraints in SQL. I can have UNIQUE on a NULL-
>> able column. The PRIMARY KEY is defined as UNIQUE + NOT NULL, with a
>> restriction that it is used only once. This makes the column(s) the
>> default targets of REFERENCES clauses and many older SQL products do
>> special things with it to speed up searching. There have been
>> proposals that we drop it in modern RDBMS, but the "code museum
>> effect" is very strong
>>
>>>> People use IDENTITY successfully for surrogate keys 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. <<
>>
>> No, they don't; how many times do I have to post the quote about being
>> exposed to a user? That means you can see it, you had to explicitly
>> declare it and you can do operations on it.
>>
>> And you might actually want to read some of Date's papers. While we
>> disagree on much, we are both "big fans" of Dr. Codd and natural keys.
>> He is probably more so than me -- I don't mind artificial keys if you
>> can control them properly.
>>
>
>

Re: SQL Express - Identity specification property - how to change

"Tony Rogerson" wrote in message
news:figb7m$gpp$1$8300dec7@news.demon.co.uk...
>> CREATE TABLE FOO
>> (
>> FOO_ID int identity(1,1),
>> Server_name varchar(20)
>> )
>
> Just get them to do this....
>
>> CREATE TABLE FOO
>> (
>> FOO_ID int identity(1,1),
> Server_name varchar(20) ----> UNIQUE <-----
>> )
>
> Then all is well, well it would have been if they are started out properly
> in the first place.

Unfortunately, on my list of things to fix, this isn't the highest on the
list!

(and just found a case where the Product team WANTS the "Server_name" to be
non-unique. It makes absolutely no sense to the developers or me, but
that's a fight for another day.)


>
> Tony.
>

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Re: SQL Express - Identity specification property - how to change

>> Then all is well, well it would have been if they are started out properly in the first place [NOT NULL UNIQUE on natural key]. <<

This should be a no-op, but it scrambles everything

BEGIN
DELETE FROM Foo
WHERE server_name = 'server_bob';
INSERT INTO Foo VALUES ('server_bob');
END;

And you added the cost of an extra index with the UNIQUE, too!

Re: SQL Express - Identity specification property - how to change

--CELKO-- (jcelko212@earthlink.net) writes:
> This should be a no-op, but it scrambles everything
>
> BEGIN
> DELETE FROM Foo
> WHERE server_name = 'server_bob';
> INSERT INTO Foo VALUES ('server_bob');
> END;
>
> And you added the cost of an extra index with the UNIQUE, too!

Why should that by necessity be a no-op? Say that the table definition is

CREATE TABLE Foo(server_name varchar(15) NOT NULL PRIMARY KEY,
addedwhen datetime NOT NULL DEFAULT CURRENT_TIMESTAMP);

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: SQL Express - Identity specification property - how to change

> This should be a no-op, but it scrambles everything
>
> BEGIN
> DELETE FROM Foo
> WHERE server_name = 'server_bob';
> INSERT INTO Foo VALUES ('server_bob');
> END;
>

Why are you looking at the surrogate key - that's for the plumbing - you
should not care about it's value - it's not important - remember Codds
rules?

Because of the foriegn keys set up you'll have also deleted all the data
from all the tables that referenecd this so basically your database would be
empty - is this really an example?

And how on earth does it scramble 'server_name'?

> And you added the cost of an extra index with the UNIQUE, too!

A bit of maths; your natural key is 50 bytes long, you have this
situation....

create table Users (
user_name varchar(50) not null primary key
)
-- max row length 50

create table Tickets (
ticket_code varchar(10) not null primary key
)
-- max row length 10

create table Permissions (
ticket_code varchar(10) not null references Tickets( ticket_code ),
user_name varchar(50) not null references Users( user_name )
)
-- max row length 60

OR the surrogate way....

create table Users (
users_id int not null IDENTITY unique clustered,
user_name varchar(50) not null primary key
)
-- max row length 54

create table Tickets (
tickets_id int not null IDENTITY unique clustered,
ticket_code varchar(10) not null primary key
)
-- max row length 14

create table Permissions (
tickets_id int not null references Tickets( tickets_id ),
users_id not null references Users( users_id )
)
-- max row length 8

Is the maths clearer now?

Which table has the most rows? The base table or the tables that reference
it? The talbes that reference it usually have a many to 1 relationship
between the foreign table and the base table.

From the application perspective, what is better along your 3g or GPRS
56Kbit connection? Passing 100 rows that each are 4 bytes (the users_id for
the plumbing) or passing 100 rows that are 50 bytes each for the plumbing?

And I would really like an answer as to why you think a UNIQUE index on a 4
byte column on the base table gives a bigger overhead than using the bloated
natural keys in the reference tables.

Will you please now actually take the time and digest what is going on here
instead of burying your head in the sand as per usual because this surrogate
key method in SQL Server seems beyond you.

--
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--" wrote in message
news:7bca372f-f117-4ce9-aae2-615a9a4959ef@v4g2000hsf.googleg roups.com...
>>> Then all is well, well it would have been if they are started out
>>> properly in the first place [NOT NULL UNIQUE on natural key]. <<
>
> This should be a no-op, but it scrambles everything
>
> BEGIN
> DELETE FROM Foo
> WHERE server_name = 'server_bob';
> INSERT INTO Foo VALUES ('server_bob');
> END;
>
> And you added the cost of an extra index with the UNIQUE, too!

Re: SQL Express - Identity specification property - how to change

Don't forget all the other data that get's deleted as well - all the tables
that reference the Foo table which likely contain time important information
like order_date, trade_date etc...

--
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]

Re: SQL Express - Identity specification property - how to change

Tony Rogerson (tonyrogerson@torver.net) writes:
> Don't forget all the other data that get's deleted as well - all the
> tables that reference the Foo table which likely contain time important
> information like order_date, trade_date etc...

Yeah, if there are some cascading foreign keys, or triggers a lot of
things can happen when Joe runs a no-op!



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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx