If Exists Statement Problem...

Can someone give me a hand with this?

Ok I have a table called allstocks, with a primary key of ticker, I also have a todaysstocks table with a primary key of ticker.

Todaysstock table updates the allstocks table with this statement,

insert into allstocks (exchange,transdate,ticker,[opened date],[closed date],[over/under]) select exchange,[todays date],ticker,[opened date], [closed date],[over/under] from todaysstocks

this works fine, unless the ticker already exists. How do I write the statement, that if the ticker already exists, then update the rest of the fields with the new info, or delete the row and recreate it?

ANy help would be appreciated. Thanksif exists (select 1 from allstock a inner join todaysstocks t on a.ticker=t.ticker)
delete a from allstock a inner join todaysstocks t on a.ticker=t.ticker
...|||insert into allstocks (exchange,transdate,ticker,[opened date],[closed date],[over/under])
select exchange,[todays date],ticker,[opened date], [closed date],[over/under]
from todaysstocks a
where
ticker not in (select ticker from allstock b where b.ticker = a.ticker)
--|||This may be a hog...
insert into allstocks (exchange,transdate,ticker,[opened date],[closed date],[over/under])
select exchange,[todays date],ticker,[opened date], [closed date],[over/under]
from todaysstocks a
where
ticker not in (select ticker from allstock b where b.ticker = a.ticker)
--
This one will use an index (if one exists) on ticker:

insert into allstocks (exchange,transdate,ticker,[opened date],[closed date],[over/under])
select a.exchange,a.[todays date],a.ticker,a.[opened date], a.[closed date],a.[over/under]
from todaysstocks a left outer join allstocks b on a.ticker=b.ticker
where b.ticker is null

But this will not alter the data that is already in allstocks, rather it will insert 0 rows. The question was:

How do I write the statement, that if the ticker already exists, then update the rest of the fields with the new info, or delete the row and recreate it?|||You need to passes to accomplish this. The first statement updates existing rows, and the second statement adds new rows.

--Update existing records (you can eliminate fields that are part of the natural key):
update allstocks
set allstocks.exchange = todaysstocks.exchange,
allstocks.transdate = todaysstocks.[todays date],
allstocks.ticker = todaysstocks.ticker,
allstocks.[opened date] = todaysstocks.[opened date],
allstocks.[closed date] = todaysstocks.[closed date],
allstocks.[over/under] = todaysstocks.[over/under]
from allstocks
inner join todaysstocks on allstocks.keyfields = todaysstocks.keyfields

--Add new records:
insert into allstocks (exchange, transdate, ticker, [opened date], [closed date], [over/under])
select exchange, [todays date], ticker, [opened date], [closed date], [over/under]
from todaysstocks
left outer join allstocks on todaysstocks.keyfields = allstocks.keyfields
where allstocks.keyfields is null

Identity_Insert OFF

I try to insert values to a field (which is a bigint identity(1 ,1) primary key) and i take message that Identity_Insert is OFF
What i should do ?
thank you
This is sample code pasted from SQL Books Online from the SET
IDENTITY_INSERT property:
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT products ON
GO
-- Attempt to insert an explicit ID value of 3
INSERT INTO products (id, product) VALUES(3, 'garden shovel').
GO
You can find the answers to almost every question about syntax in BOL.
HTH,
Mary
On Wed, 14 Apr 2004 23:06:03 -0700, George
<anonymous@.discussions.microsoft.com> wrote:

>I try to insert values to a field (which is a bigint identity(1 ,1) primary key) and i take message that Identity_Insert is OFF
>What i should do ?
>thank you

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 range management

hi all, got a major problem, having done some mods to my replicated
database, the subscriber, no has primary key violations. im using sql server
identity range management so this should be fine,
i have performed an table upgrade in this way before.
scrpit out the repliation,
remove the replaction from the publisher by deleting the publication and
then using sp_removeddbrepliation.
makeing my changes
then running the script to rebuild the repliaction.
however this time, in serveral tables at the subscriber the id ranges seem
to have gone back to range that have been used.
is there anyway i can get the ranges updated bearing in mind that im using
the auto identity range management.
Thanks Andrew
Andrew,
you could use dbcc checkident to reseed manually, edit the check constraints
accordingly, and change the values in MSrepl_identity_range on the
subscriber. This table is used to check if the subscriber has used up its
range or reached the threshold. The new range you set would be obtained from
MSrepl_identity_range on the distributor, which is the master table and is
used to generate new values. The values in this table (MSrepl_identity_range
on the distributor) would need to be changed to avoid a future potential
conflict.
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||After dropping the publication you will need to connect to the subscribers
and drop the replication check constraints on the tables. This is a
"problem" I have reported to Microsoft.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"andrew bourne" <andrewbourne@.vardells.com> wrote in message
news:eMFNzaihFHA.1948@.TK2MSFTNGP12.phx.gbl...
> hi all, got a major problem, having done some mods to my replicated
> database, the subscriber, no has primary key violations. im using sql
server
> identity range management so this should be fine,
> i have performed an table upgrade in this way before.
> scrpit out the repliation,
> remove the replaction from the publisher by deleting the publication and
> then using sp_removeddbrepliation.
> makeing my changes
> then running the script to rebuild the repliaction.
> however this time, in serveral tables at the subscriber the id ranges seem
> to have gone back to range that have been used.
> is there anyway i can get the ranges updated bearing in mind that im using
> the auto identity range management.
> Thanks Andrew
>
|||hi all
i have stopped client connecteding to the subcriber
ok so if at the subscriber i change the next_seed value to the current_max
value in the msrepl_identity_range.
at the distributer i change the next_seed value to a range that is out of
the way.
If i then run the merge agent, will that pick up that the subcribers ranges
need changeing and change them to the value in the next_seed specified in
the distributor.
Thanks In Advance Andrew
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eij4KLjhFHA.576@.tk2msftngp13.phx.gbl...
> Andrew,
> you could use dbcc checkident to reseed manually, edit the check
> constraints accordingly, and change the values in MSrepl_identity_range on
> the subscriber. This table is used to check if the subscriber has used up
> its range or reached the threshold. The new range you set would be
> obtained from MSrepl_identity_range on the distributor, which is the
> master table and is used to generate new values. The values in this table
> (MSrepl_identity_range on the distributor) would need to be changed to
> avoid a future potential conflict.
> HTH
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Andrew,
I'm suggesting you bypass the automatic management and assign a range
yourself. Setting the values in msrepl_identity_range on the subscriber for
the range you want, msrepl_identity_range on the distributor to make sure it
is greater than the subscriber range, issuing a dbcc checkident, and
changing the check constraints will allow things to proceed as per normal,
and running the merge agent will register that anything has been changed
manually.
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Identity Range Management

Good morning All,
In the context of a merge replica:
Does this range serve as surrogate key (Primary Key) but after the sync, the
server overlooks it when inserting new rows in the published table and
generate a sequential one?
Here's what I am trying to achieve:
My disconnected users, have to issue unique File# to their customers.
Once a file number is given to a customer, the customer will use this file
number to reference his/her case for ever.
I want this number to be the unique identifier of his/her record in my table.
If I use this range model, then that would solve the problem provided that
the server will use this number as the primary key for the table where
customer cases are stored.
I read a few articles about the range model but it never read that this
number is kept or used as a primary key in a table.
What am I missing here?
Also
How can we reduce the large skipped numbers not used between sync?
Thanks
YOW
Yes. So the identity column which may or may not be the primary key or may
be one of the columns involved in the primary key could appear on two nodes
simultaneously if Automatic Identity Range Management is not working
correctly. This will lead to a conflict when the merge agent runs and can
cause one of the inserts to be rolled back and replaced by the winning
insert.
If it is working correctly or if there are other columns in the primary key,
or if the identity value is the only key in a primary key or unique index
and you are perfectly partitioned it should work.
Automatic identity range management promotes efficient use of the identity
ranges. If you notice that the ranges are not being used efficiently on the
publisher lower the publisher range. If it is not being used efficiently on
all subscribers, lower that range. If it is not being used efficiently on
one or the subscribers, but is on the other you are out of luck.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message news:C2F1728A-0241-412E-AF9D-CAB3BE2CB04C@.microsoft.com...
> Good morning All,
> In the context of a merge replica:
> Does this range serve as surrogate key (Primary Key) but after the sync,
the
> server overlooks it when inserting new rows in the published table and
> generate a sequential one?
> Here's what I am trying to achieve:
> My disconnected users, have to issue unique File# to their customers.
> Once a file number is given to a customer, the customer will use this file
> number to reference his/her case for ever.
> I want this number to be the unique identifier of his/her record in my
table.
> If I use this range model, then that would solve the problem provided that
> the server will use this number as the primary key for the table where
> customer cases are stored.
> I read a few articles about the range model but it never read that this
> number is kept or used as a primary key in a table.
> What am I missing here?
> Also
> How can we reduce the large skipped numbers not used between sync?
> Thanks
> YOW
|||What do you think if i go for ROWGUID instead?
"Hilary Cotter" wrote:

> Yes. So the identity column which may or may not be the primary key or may
> be one of the columns involved in the primary key could appear on two nodes
> simultaneously if Automatic Identity Range Management is not working
> correctly. This will lead to a conflict when the merge agent runs and can
> cause one of the inserts to be rolled back and replaced by the winning
> insert.
> If it is working correctly or if there are other columns in the primary key,
> or if the identity value is the only key in a primary key or unique index
> and you are perfectly partitioned it should work.
> Automatic identity range management promotes efficient use of the identity
> ranges. If you notice that the ranges are not being used efficiently on the
> publisher lower the publisher range. If it is not being used efficiently on
> all subscribers, lower that range. If it is not being used efficiently on
> one or the subscribers, but is on the other you are out of luck.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
> message news:C2F1728A-0241-412E-AF9D-CAB3BE2CB04C@.microsoft.com...
> the
> table.
>
>
|||They tend not to make good PK's. Have a look at this for more info on why
not.
http://www.aspfaq.com/show.asp?id=2504
Its in the bottom section.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message news:8587BCD9-84AB-4719-BFFA-8A7BD607A847@.microsoft.com...[vbcol=seagreen]
> What do you think if i go for ROWGUID instead?
> "Hilary Cotter" wrote:
may[vbcol=seagreen]
nodes[vbcol=seagreen]
can[vbcol=seagreen]
key,[vbcol=seagreen]
index[vbcol=seagreen]
identity[vbcol=seagreen]
the[vbcol=seagreen]
on[vbcol=seagreen]
on[vbcol=seagreen]
in[vbcol=seagreen]
sync,[vbcol=seagreen]
file[vbcol=seagreen]
that[vbcol=seagreen]
this[vbcol=seagreen]
|||From your first answer:
if Automatic Identity Range Management is not working correctly. This will
lead to a conflict when the merge agent runs and can cause one of the
inserts to be rolled back and replaced by the winning insert"
What could make Automated IRM not work properly?
Thanks
"Hilary Cotter" wrote:

> They tend not to make good PK's. Have a look at this for more info on why
> not.
> http://www.aspfaq.com/show.asp?id=2504
> Its in the bottom section.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
> message news:8587BCD9-84AB-4719-BFFA-8A7BD607A847@.microsoft.com...
> may
> nodes
> can
> key,
> index
> identity
> the
> on
> on
> in
> sync,
> file
> that
> this
>
>
|||Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message news:37219273-4565-47B0-AB13-1D01132A0658@.microsoft.com...
> From your first answer:
> if Automatic Identity Range Management is not working correctly. This
will[vbcol=seagreen]
> lead to a conflict when the merge agent runs and can cause one of the
> inserts to be rolled back and replaced by the winning insert"
> What could make Automated IRM not work properly?
>
> Thanks
> "Hilary Cotter" wrote:
why[vbcol=seagreen]
in[vbcol=seagreen]
or[vbcol=seagreen]
two[vbcol=seagreen]
and[vbcol=seagreen]
winning[vbcol=seagreen]
primary[vbcol=seagreen]
on[vbcol=seagreen]
efficiently[vbcol=seagreen]
efficiently[vbcol=seagreen]
wrote[vbcol=seagreen]
and[vbcol=seagreen]
customers.[vbcol=seagreen]
this[vbcol=seagreen]
in my[vbcol=seagreen]
provided[vbcol=seagreen]
where[vbcol=seagreen]
|||I'd like to say bugs, but I am not really convinced that there are bugs with
this. Properly sized it does seem to work well - or at least it has worked
well for us on several installations.
If you don't size your data type or your ranges correctly or run the agent
in continuous mode, it might not update the ranges in time. This seems to be
the biggest problem with it.
It also seems that if you are monkeying around with the metadata that the
merge agent will not detect that an article is under automatic range
management when it runs and then check the local and remote ranges. It
normally does this check when it first runs. I have seen cases where the
detection proc never runs, but when I try to repro it on a clean database I
am unable to do so. So I think its something i have messed up along the way.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message news:37219273-4565-47B0-AB13-1D01132A0658@.microsoft.com...
> From your first answer:
> if Automatic Identity Range Management is not working correctly. This
will[vbcol=seagreen]
> lead to a conflict when the merge agent runs and can cause one of the
> inserts to be rolled back and replaced by the winning insert"
> What could make Automated IRM not work properly?
>
> Thanks
> "Hilary Cotter" wrote:
why[vbcol=seagreen]
in[vbcol=seagreen]
or[vbcol=seagreen]
two[vbcol=seagreen]
and[vbcol=seagreen]
winning[vbcol=seagreen]
primary[vbcol=seagreen]
on[vbcol=seagreen]
efficiently[vbcol=seagreen]
efficiently[vbcol=seagreen]
wrote[vbcol=seagreen]
and[vbcol=seagreen]
customers.[vbcol=seagreen]
this[vbcol=seagreen]
in my[vbcol=seagreen]
provided[vbcol=seagreen]
where[vbcol=seagreen]

Identity question

I have two fields that I am concerned with be unique.

Id, and Name.

The id is set to the primary key which automatically makes it unique. How would I set the Name to be unique as well?You add a constraint to it. And making it a primary key doesn't make it an identity. That's yet another type of constraint you apply to it. :)|||Ok here is the example

create table tester(a varchar primary key, b varchar(20) unique);

insert into tester values ('1','2');
insert into tester values ('1','2');
insert into tester values ('2','2');

the first insert statement inserts perfectly, and there will be errors with the second and third statement as a is primary key and is unique and second b is unique. so only first statement gets inserted in to the table.|||Ok, so I cannot specify it at the table level. It must be set at time of insert?|||You can check the uniqueness of the field when u try to insert or update the record/data. with data i think the table is useless.

So the concept of uniqueness comes when u r trying to do some transactions with the table right.

Phani...|||You DO specify it at the table level, either when issuing a CREATE or ALTER table statement.|||Is there any way to do it via Enterprise Manager. I've already created the tables.|||Yes. Check out the"Creating a Unique Constraint" article in MSDN (also in Books Online). It provdes step-by-step instructions. You might actually need a Unique Index instead (follow the link in the article for more information).

Terri|||I actually figured it out late last night, this article is exactly what I did.

Thanks for the help guys.

IDENTITY on non-PK

Is there a way to have an "autonumber" on a non-Primary Key column in SQL
Server?
Thanks,
Ericyou can use IDENTITY on any field, regardless of whether it is a PK
<Eric> wrote in message news:OitsJtRQFHA.576@.TK2MSFTNGP15.phx.gbl...
> Is there a way to have an "autonumber" on a non-Primary Key column in SQL
> Server?
> Thanks,
> Eric
>
--== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News==-
--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||Sure, as long as it's the only one in the table.
Thomas
<Eric> wrote in message news:OitsJtRQFHA.576@.TK2MSFTNGP15.phx.gbl...
> Is there a way to have an "autonumber" on a non-Primary Key column in SQL
> Server?
> Thanks,
> Eric
>|||Sorry...I worded that poorly.
Suppose you already have a PK column that is an identity column. It also
has the identity seed and increment specified.
As Thomas said, you can only have one identity column per table. Is there
an easy way, then, to mimic the identity seed and identity increment
properties on a different column besides the PK?
Thanks,
Eric
<Eric> wrote in message news:OitsJtRQFHA.576@.TK2MSFTNGP15.phx.gbl...
> Is there a way to have an "autonumber" on a non-Primary Key column in SQL
> Server?
> Thanks,
> Eric
>|||You can generate an incrementing key with an INSERT statement but that isn't
the same as an IDENTITY.
http://www.google.co.uk/groups?selm...r />
roups.com
Please explain your requirement. Why do you want to generate two artificial
keys?
David Portas
SQL Server MVP
--|||Thanks for the reply. It turns out my colleauge just needed to have a
sequential way to order search results while ignoring the primary key. He
just added a date/time column with a default of GETDATE(), and used "ORDER
BY myDate" in the SELECTs.
Eric
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:x8-dnQ-Q1IkZI8PfRVn-2g@.giganews.com...
> You can generate an incrementing key with an INSERT statement but that
isn't
> the same as an IDENTITY.
>
http://www.google.co.uk/groups?selm...ooglegroups.com

> Please explain your requirement. Why do you want to generate two
artificial
> keys?
> --
> David Portas
> SQL Server MVP
> --
>|||OK. Ordering by date makes much more sense than sorting on an artificial
key.
David Portas
SQL Server MVP
--

Identity Key possible problem

Has anyone experience any problems with identity key value not incrementing properly? I have a table with an identity key which should increment by one but has missing value. I would just like to exclude the possiblity of a SQL Server error.

Missing values happen for identity columns when transactions are rolled back.

Each time when you do an insert, the identity value gets incremented, but if the transaction that contains the insert does a rollback, you will loose the value.

You are guaranteed that you will never get the same identity value, but it is possible to get holes in the numbers.

Identity Key

I know identity key in a table can cause problems when the table is replicated. Should we avoid using identity key altogether, if we don't know in advance whether replication will come into the picture?

Thanks for any advice.I'm not absolutely positive, but I would think that if the source and destination tables have the same identity seed (starting value) and identity increment, then there should be no data corruption.|||A problem may arise if the source table's identity key is in broken sequence.

Thanks.

Identity Increment = Greyed Out (actually, all properties are greyed out)

I am looking to make the primary key auto increment by 1. I have found by looking around the internet that you need to do this in the Tables -> [Table Name] -> Columns -> [Column Name], Properties window, and I see the "Identity Increment" however all of the properties there are greyed out -- I can't access them.

Any ideas on how to make this work?

For some background:

I'm running SQL Server 2005 with Visual Studio 2005. To create this database, I right-clicked on my project, and went to 'Add SQL Database', I filled in the columns all from within visual studio.

Identity Increment is only avalible for integer types, like tinyint, smallint, int, bigint, etc.

Identity Error when replicating

I have some tables in a database that is tranactionally replicated to another
server.
Each table has a column which is the primary key as well as having the
Identity property set to Yes. The subscribing table has the same settings
for the corresponding column except that Indentity is set to 'Yes (Not for
replication)'.
When the tables replicate from an Insert transaction, everything is fine.
However, when doing an Update transaction, I get a "Cannot update identity
column 'RecordID'." error (Where RecordID is the column with the key and ID).
Is my server having mood swings?
How can I resolve this without setting the Indentity property to NO on the
subscriber?
p.s. I know that setting the subscriber column Indentity Property to Yes
isn't necessary since the publisher takes care keeping the key unique for me.
But if my publishing server ever goes down I would like a quick transition
to the subscribing server without having to change all the identity settings.
Roger,
on the subscriber there shouldn't be the identity attribute at all. You
could remove this attribute (identity - No), or edit the replication stored
procedures and comment out the second section. If you want it all to work on
failover you could use Queued Updating Subscribers, in which case the
replication stored procedures are coded differently and you won't have this
issue. Also, you'll have to consider the identity range, and the queued
updating option will do this for you if you select automatic range
management.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I just read the discussion between bert, Paul, and Hillary concerning this.
My scenario is not as intensive as bert's since I only have to deal with one
publisher and one subscriber.
I noticed bert was able to install SP4 beta but I couldn't find it. Is for
SQLServer2003? I am using SQLS2k.
How does one create Queued Update Subscribers? While I still be able to
retain my identity settings in my subscriber tables that use them?
"Roger Denison" wrote:

> I have some tables in a database that is tranactionally replicated to another
> server.
> Each table has a column which is the primary key as well as having the
> Identity property set to Yes. The subscribing table has the same settings
> for the corresponding column except that Indentity is set to 'Yes (Not for
> replication)'.
> When the tables replicate from an Insert transaction, everything is fine.
> However, when doing an Update transaction, I get a "Cannot update identity
> column 'RecordID'." error (Where RecordID is the column with the key and ID).
> Is my server having mood swings?
> How can I resolve this without setting the Indentity property to NO on the
> subscriber?
> p.s. I know that setting the subscriber column Indentity Property to Yes
> isn't necessary since the publisher takes care keeping the key unique for me.
> But if my publishing server ever goes down I would like a quick transition
> to the subscribing server without having to change all the identity settings.
|||Roger,
I'm not really sure what identity settings you currently have on your
subscriber. If you mean the actual Identity attribute, then queued updating
subscribers will add it (NFR). If you mean the identity value (number), then
this will be overwritten by the automatic range management unless you use a
nosync initialization. Nosync will have its ownb issues here in that you'll
have to manually reseed the subscriber tables on failover, so I'd not really
recommend it is you have a choice.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||When you say "edit the replication stored procedures" are you talking about
the sp_MSins_TableName, sp_MSupd_TableName, etc, procs? If such is the case,
the section to comment out is after the 'else' statement?
If I go with Queued Updating, can I still keep the identity attribute
property of the column set to Yes on the subscriber?
Roger.
"Paul Ibison" wrote:

> Roger,
> on the subscriber there shouldn't be the identity attribute at all. You
> could remove this attribute (identity - No), or edit the replication stored
> procedures and comment out the second section. If you want it all to work on
> failover you could use Queued Updating Subscribers, in which case the
> replication stored procedures are coded differently and you won't have this
> issue. Also, you'll have to consider the identity range, and the queued
> updating option will do this for you if you select automatic range
> management.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Roger,
yes this is the section I was referring to, but I would definitely go with
queued updating subscribers instead. The Identity property should be Yes,
Not For Replication on the publisher and it'll be transferred in this way to
the subscriber. Also don't forget to enable automatic range management to
make your life easier, otherwise you'll have to reseed each identity column
after failover.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Identity columns problem in replication among n no of subscribers

I have created Transaction Replication.
Nearly 100's of tables there in my database.
I have created Recordid (identity) column with primary key in each table and
all are incremented by 1.
Front End application in VB6 is 100% dependable on this column. Changes in
this column may spoil my work of VB6.
Synchronisation is done @. subscribers side using below activex component in
VB6
Set objSQLDist = CreateObject("SQLDistribution.SQLDistribution.2")
Subscribers side does not show identity columns with primary key. It shows
only name of the column as INT.
Front application is failed to run @. subscribers side.
Pls suggest how to Replicate identity column among n no. of subscribers.
Best Regards
Sanjay
You will have to create different identity seeds and an increments of 2 on
both sides - on the subscriber a seed of 2 with an increment of 2, and on
the publisher an seed of 1 with an increment of 1.
Make sure the identity column has the not for replication property on it.
Then set your articles up to not modify the existing tables on the
subscriber and in your pre-snapshot script have your table creation scripts
(along with their respective indexes).
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
news:OUx4ONMBHHA.4472@.TK2MSFTNGP03.phx.gbl...
>I have created Transaction Replication.
> Nearly 100's of tables there in my database.
> I have created Recordid (identity) column with primary key in each table
> and all are incremented by 1.
> Front End application in VB6 is 100% dependable on this column. Changes in
> this column may spoil my work of VB6.
> Synchronisation is done @. subscribers side using below activex component
> in VB6
> Set objSQLDist = CreateObject("SQLDistribution.SQLDistribution.2")
> Subscribers side does not show identity columns with primary key. It shows
> only name of the column as INT.
> Front application is failed to run @. subscribers side.
> Pls suggest how to Replicate identity column among n no. of subscribers.
> Best Regards
> Sanjay
>
>
>
>
>
|||Dear Friend Hilary
Thanks for the help.
But one more solution can be there if I give different IDENTITY ranges to
each subscribers.
Pls help me If you know how to give diff IDENTITY ranges to each
subscriber.
Thanks in Advance
Best Regards
Sanjay
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ut$xlYMBHHA.4292@.TK2MSFTNGP02.phx.gbl...
> You will have to create different identity seeds and an increments of 2 on
> both sides - on the subscriber a seed of 2 with an increment of 2, and on
> the publisher an seed of 1 with an increment of 1.
> Make sure the identity column has the not for replication property on it.
> Then set your articles up to not modify the existing tables on the
> subscriber and in your pre-snapshot script have your table creation
> scripts (along with their respective indexes).
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
> news:OUx4ONMBHHA.4472@.TK2MSFTNGP03.phx.gbl...
>
|||You certainly can, so you can go to your subscriber and do a
dbcc('mytablename', checkident, reseed, 10000000) and it will probably work.
However you will always have to monitor the range you assigned and adjust it
as necessary.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
news:%23e4pfjMBHHA.1220@.TK2MSFTNGP04.phx.gbl...
> Dear Friend Hilary
> Thanks for the help.
> But one more solution can be there if I give different IDENTITY ranges to
> each subscribers.
> Pls help me If you know how to give diff IDENTITY ranges to each
> subscriber.
> Thanks in Advance
> Best Regards
> Sanjay
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ut$xlYMBHHA.4292@.TK2MSFTNGP02.phx.gbl...
>
|||Why do you need the identity property on the subscribers if you have
transactional replication, where the subscribers are treated as RO?
Perhaps this is being used as a failover server? If this is the case, the
easiest way to set it up is to enable automatic identity range management,
large range sizes and queued updating subscribers.
This way the subscriber can start entering data once the publisher is down
without any meddling with identity ranges on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Dear friend Hilary
Thanks for your full co-operation. My all doubts are clear now except below
2.
1. If I am having file called EMP with identity column Recid.
I want to give automatic range control on publisher for n no. subscribers
what will be the command ?
2. If I give range 1001 to 2000 to subscriber1, will he (subscriber1) be
able to store or pull others data range from 1 to 1000 or 2001 to 3000.
Best Regards
Sanjay
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u5x4t7MBHHA.4428@.TK2MSFTNGP04.phx.gbl...
> You certainly can, so you can go to your subscriber and do a
> dbcc('mytablename', checkident, reseed, 10000000) and it will probably
> work. However you will always have to monitor the range you assigned and
> adjust it as necessary.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
> news:%23e4pfjMBHHA.1220@.TK2MSFTNGP04.phx.gbl...
>
|||For automatic identity range management on the publisher for queued and
merge replication you have to drop your publication and subscriptions and
enable this feature - in the articles tab of the create publication wizard.
A subscriber with a range of 1000-2000 will be able to receive replicated
commands for other ranges if the not for replication constraint is enabled.
Otherwise he will not.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
news:%239Zlc%23yBHHA.3380@.TK2MSFTNGP04.phx.gbl...
> Dear friend Hilary
> Thanks for your full co-operation. My all doubts are clear now except
> below 2.
> 1. If I am having file called EMP with identity column Recid.
> I want to give automatic range control on publisher for n no. subscribers
> what will be the command ?
> 2. If I give range 1001 to 2000 to subscriber1, will he (subscriber1) be
> able to store or pull others data range from 1 to 1000 or 2001 to 3000.
> Best Regards
> Sanjay
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u5x4t7MBHHA.4428@.TK2MSFTNGP04.phx.gbl...
>

Identity column: what happens when it runs out?

I have a table containing an identity column (bigint) as its primary key.
The table will have very frequent insertions/deletions from it.
Many items will be added, but they aren't really expected to be there for
long. I can never expect that the table is empty, however.
So, eventually, the new ID's that get added will increment up to the maximum
bigint value. What happens then? Does it automatically wrap and start
over?
If it starts over, how does it handle any values that may still exist?
How should I handle this?
Thanks!
--
Adam Clauss
cabadam@.tamu.eduYou'll receive an overflow error if IDENTITY reaches the upper bound of the
datatype. Is that likely with a BIGINT? Assuming you start at zero then even
if you generated 1 billion rows per second, 24x7 it would still take nearly
300 years before you hit the ceiling. Your hardware will fall apart rather
sooner! If you're still not convinced then there's always NUMERIC instead.
David Portas
SQL Server MVP
--|||Hmm.. ok, I hadn't realized that bigint ran quite THAT big...
Thanks!
--
Adam Clauss
cabadam@.tamu.edu
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:Y7qdnZR3Gc1nVG_cRVn-oA@.giganews.com...
> You'll receive an overflow error if IDENTITY reaches the upper bound of
> the datatype. Is that likely with a BIGINT? Assuming you start at zero
> then even if you generated 1 billion rows per second, 24x7 it would still
> take nearly 300 years before you hit the ceiling. Your hardware will fall
> apart rather sooner! If you're still not convinced then there's always
> NUMERIC instead.
> --
> David Portas
> SQL Server MVP
> --
>|||> I have a table containing an identity column (bigint) as its primary key.
> The table will have very frequent insertions/deletions from it.
> Many items will be added, but they aren't really expected to be there for
> long. I can never expect that the table is empty, however.
Do you really need an IDENTITY column?

> So, eventually, the new ID's that get added will increment up to the
maximum
> bigint value. What happens then? Does it automatically wrap and start
> over?
No, you will get an overflow error.

> How should I handle this?
Not having an IDENTITY column?
A|||In that case maybe you don't need it. INTEGER is half the size of BIGINT and
still can store values from -2,147,483,648 to 2,147,483,647.
David Portas
SQL Server MVP
--|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OdRGx9MAFHA.2016@.TK2MSFTNGP15.phx.gbl...
> Do you really need an IDENTITY column?
What else would I use? No other column would necessarily be unique.
Adam Clauss
cabadam@.tamu.edu|||> > Do you really need an IDENTITY column?
> What else would I use? No other column would necessarily be unique.
Oh great, another "I don't have a key so I'll make one up"... are you saying
that you could have multiple rows with the exact same data in every column?
What exactly are you trying to model?|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OgwuDLPAFHA.2804@.TK2MSFTNGP15.phx.gbl...
> Oh great, another "I don't have a key so I'll make one up"
You made MVP with that kind of an attitude? If this is the help you are
offering, I don't want any.
... are you saying
> that you could have multiple rows with the exact same data in every
> column?
That would be what I just said. Like I said though, if that's the kind of
help you are offering, thanks, but no thanks.
Adam Clauss
cabadam@.tamu.edu|||> That would be what I just said. Like I said though, if that's the kind of
> help you are offering, thanks, but no thanks.
Great, have fun. Maybe you'll get lucky and Celko won't stumble across this
one.|||If you are concerned about about running out of IDENTITY values with a
"numeric/int" based datatype, I would suggest you to look at the
UNIQUEIDENTIFIER datatype.
I am a VERY VERY VERY firm believer in the UNIQUEIDENTIFIER datatype (aka
the GUID [Globally Unique Identifier] in .NET and elsewhere). I resisted it
at first... but came around to understand it and what it could do for my
applications...
It will take a bit change on your part to convert to the GUID way of
thinking, but it might be worth it for you. I know it was for me but well
worth it!!!
Chris
"Adam Clauss" <cabadam@.nospam.tamu.edu> wrote in message
news:uKir3hMAFHA.2600@.TK2MSFTNGP09.phx.gbl...
>I have a table containing an identity column (bigint) as its primary key.
>The table will have very frequent insertions/deletions from it.
> Many items will be added, but they aren't really expected to be there for
> long. I can never expect that the table is empty, however.
> So, eventually, the new ID's that get added will increment up to the
> maximum bigint value. What happens then? Does it automatically wrap and
> start over?
> If it starts over, how does it handle any values that may still exist?
> How should I handle this?
> Thanks!
> --
> Adam Clauss
> cabadam@.tamu.edu
>

Identity column jumps indefinitely

Guys,

Iam new to this forum, Hello to all...
Iam facing a problem in my application. Have recently noticed that my primary key column which is an " identity " with increment 1 being set.
But now iam noticing a various jumps in the number instead of 1. The numbers in the jump is not consistent.
Has anyone faced this kinda problem.
???many people have seen this situation

those who are using identity columns only to provide identity (uniqueness) do not see a problem at all

those who are concerned about gaps in the sequence of numbers, as representing a problem for their application, should re-design their application so that they don't rely on identity columns|||I agree with your comments not to use the identity on the application.
But in my case, i dont delete the records, it automatically jumps the numbers.
say for example a record is created with number 301 today morning
during afternoon there is another new record with number 899.
But why this jump is happening. Iam curious to know about it...|||yes, i'm curious too

when it gets close to the 2-billion number, you may want to have a look at it again

:)|||This is old info, off the top of head, but as I remember it:

Basically, the Identity feature 'grabs' a block of numbers, and doles them out. Not sure what the default is. Assume 100 (1-100). When the first insert happens, it gives out '1'.
For performance, the server grabs them in bunches, and saves the next, (101), so it doesn't need to keep getting locks for each insert. If the db server goes down, the next record will get '101'.

Don't use identity for consecutive numbering.

Jay Grubb
Technical Consultant
OpenLink Software
Web: http://www.openlinksw.com:
Product Weblogs:
Virtuoso: http://www.openlinksw.com/weblogs/virtuoso
UDA: http://www.openlinksw.com/weblogs/uda
Universal Data Access & Virtual Database Technology Providers

Identity column as a primary key with foreign key relationships

Greetings All,
We want to have a user_id which will be a primary key and all other tables
will be joining on this user_id. My question is
Option 1: Have user_id an identity and have foreign key on this identity
table.
Option 2: Dont use identity as user_id and generate user_id with some logic.
Our group is having mixed opion and we will go with maximum number of
suggestions we get here. Also please include why you think the option you
selected is right.
Thanks,
Arshad
--
Arshad
arshadmd-nospam@.gmail.comIDENTITY all the way unless you have good reason for genrerating your
own ID. What justification /resoning do you have for not using it?|||Arshad,
Unfortunately you will also get mixed opinions here. You may read a
response from Celko where he will insult you and insist that you never
use an identity column as a primary key. Others will tell you that it
is OK. I've been through a similar issue and here is what I've learned:
1) If your project will ever be moved to a system like Oracle, you
don't want to use Identity columns as primary keys. If you will never
move your db to another system other than SQL, then using an identity
column as a primary key is OK.
2) I personally have used identity columns as primary keys in many
different scenarios and I have never had a problem with it.
3) Creating a primary key with a stored procedure or some other logic
will work but it only creates more work in coding for you and adds
additional bulk to the table that is not necessary because you will
still have the unique identity as well as a unique primary key.
4) Using multiple columns to create your primary key (social sec. and
birthday) is possible, but that can lead to confusi table relationships
and additional bulk to each related table. I like my table
relationships to be simple and intuitive, which is why I use identity
columns as primary keys.
The bottom line is that you cannot port your db to other systems if you
do this, but if porting your code to another system is not likely to
happen, then the decision is a matter of preference for the developers.
I find using identity columns as primary keys keeps the design simple
and does not add needless bulk and clutter.|||Use an autogenerated Primary key of type integer for a primary key on which
you build relationships. If you also want to use this primary key as a
userId by which to find records this is fine say you want this to be a
customer number. However having a customer 1 followed by a customer 2 etc,,
may not be a good idea if these codes are used to allow any customer access
to the database, say via internet. Its a better idea in such a case to have
non-sequential customer identifications. So a combination of RecordId as the
primary key being automatically genereated and used for relations PK-FK and
a separate USERID field that you generate and that must also be unique is
probably better. One way to generate a non sequential ID is by using a
default value derived as follows. (rand((datepart(month,getdate()) * 100000
+ datepart(second,getdate()) * 1000 + datepart(millisecond,getdate()))) *
1000000000). This generates a random number based on the seeds of the
computer date, you can ensure that its unique by making the column a unique
index.
In my view the primary key should never be part of data being viewed or used
directly by the end user and should always be created automatically by the
database engine and should ONLY be used for that particular purpose. Other
than that the basic most generally accepted rule is make the database engine
itself do as much of the work as possible for ensuring data correctnes,
there is also the fact that there are practical situations in which you may
wnt to change a userid, if the userid is the primary key you have to do
cascading updates and this takes time. If your PK is totally outside of what
users may be allowed to use, there are no cascades involved for updates,
ever.
Also, having the database do the work, removes the onus on the individual
programmer of having to call procedures specifically for creating
Primarykeys or ensuring relationship constraints are correctly applied. If
you do this in code you WILL make mistakes and you WILL forget to call the
procedures sometime.
I've seen many apps that do this in code (still today) and have had to
transfer data from these to corrrectly structured relational tables and each
time I have found things like orphan records or duplicate keys where none
should be.
Hope ths helps and that I haven't started anyone ranting and raving :-)
RD
"Arshad" <Arshad@.discussions.microsoft.com> wrote in message
news:19DC4A02-8C08-4F99-BB86-56CD9309892D@.microsoft.com...
> Greetings All,
> We want to have a user_id which will be a primary key and all other tables
> will be joining on this user_id. My question is
> Option 1: Have user_id an identity and have foreign key on this identity
> table.
> Option 2: Dont use identity as user_id and generate user_id with some
> logic.
> Our group is having mixed opion and we will go with maximum number of
> suggestions we get here. Also please include why you think the option you
> selected is right.
> Thanks,
> Arshad
> --
> Arshad
> arshadmd-nospam@.gmail.com|||On Fri, 29 Jul 2005 08:50:01 -0700, Arshad wrote:
>Greetings All,
>We want to have a user_id which will be a primary key and all other tables
>will be joining on this user_id. My question is
>Option 1: Have user_id an identity and have foreign key on this identity
>table.
>Option 2: Dont use identity as user_id and generate user_id with some logic.
>Our group is having mixed opion and we will go with maximum number of
>suggestions we get here. Also please include why you think the option you
>selected is right.
>Thanks,
>Arshad
Hi Arshad,
In general, keys can fulfill two distinct functions.
Their first function is to provide a link between a row in a table and
an entity in the real world outside of the database. This is what I call
the business key, since in most cases, the business dictates what key to
use. If the users are employees and the HR department issues employee
numbers, than the business key is the employee number. In an American
tax-related database, SSN would be the business key. In a database that
supports the upkeep of a computer network, the username assigned by the
sysadmins for logging on to the network would be the business key. For
my dentists' customers, last name + address + date of birth might
qualify as the business key. And so on, and so on.
You should only consider having the database generate the business key
if there is at present no business key - and you'll still need to find
who'se in charge and get him or her to sign of on your proposal, since
it's not your job to change the business' processes.
The second function of a key is to link a row in one table to a related
row in (usually) another table - the well known FOREIGN KEY constraint.
In most cases, the FOREIGN KEY will refer to the PRIMARY KEY of the
related table. But it can also refer to any column (or combination of
columns) that is declared as UNIQUE in the related table.
My usual procedure is:
- First, find the business key, This one is always needed, since there
is no sense in storing data in a database if it can't be related back to
the real-world entities that it's supposed to describe.
- Second, determine of there will be any other tables referring to the
rows in this table. If there are, then determine if the business key is
a good condidate for implementing the FOREIGN KEY constraint. If it
isn't (e.g. becuase it is prone to frequent change, or because it is so
long that it would degrade performance in the database), then I'll
introduce a surrogate key - and in 99.9% of all cases, IDENTITY serves
fine as a surrogate key.
That leaves me with two possible designs:
1. Business key is suitable to be used in the FK relationship:
CREATE TABLE Tab1 (BusinessKey some_datatype NOT NULL,
other columns,
PRIMARY KEY (BusinessKey)
)
CREATE TABLE Tab2 (BusinessKeyForOtherTable other_datetype NOT NULL,
FK_To_Tab1 some_datetype [NOT] NULL,
other columns,
PRIMARY KEY (BusinessKeyForOtherTable)
FOREIGN KEY (FK_To_Tab1)
REFERENCES Tab1 (BusinessKey)
ON UPDATE CASCADE
ON DELETE NO ACTION
)
2. Business key is not suitable for FK relationship - use surrogate key:
CREATE TABLE Tab1 (Tab1_ID int NOT NULL IDENTITY,
BusinessKey some_datatype NOT NULL,
other columns,
PRIMARY KEY (Tab1_ID),
UNIQUE (BusinessKey)
)
CREATE TABLE Tab2 (Tab2_ID int NOT NULL IDENTITY,
BusinessKeyForOtherTable other_datetype NOT NULL,
FK_To_Tab1 int [NOT] NULL,
other columns,
PRIMARY KEY (Tab2ID),
UNIQUE (BusinessKeyForOtherTable)
FOREIGN KEY (FK_To_Tab1)
REFERENCES Tab1 (Tab1_ID
ON UPDATE NO ACTION -- Note this change!!
ON DELETE NO ACTION
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Arshad wrote:
> Greetings All,
> We want to have a user_id which will be a primary key and all other tables
> will be joining on this user_id. My question is
> Option 1: Have user_id an identity and have foreign key on this identity
> table.
i use this method 99% of the time and have no problems.
the bigger issue for me is what should be the clustered index on the
table.|||Arshad:
Identities do make things easier, but they do bring along some extra
baggage that you need to be aware of.
As Elroyskimms suggested problems regarding another vendor's DBMS
(although there is a work around in Oracle w/ sequences), you will also
need to consider if you ever plan on any type of replication or merging
of data between databases, as the uniqueness of the key does not exist in
this scope.
If you skin is thick enough :>), I'd recommend posting this to
microsoft.public.sqlserver.programming, and await the verbal assault from
Joe Celko. If you can get past his insults and obtuse style, he does
have a good insight on "some" issues.
Jeff Clausius
SourceGear
=?Utf-8?B?QXJzaGFk?= <Arshad@.discussions.microsoft.com> wrote in
news:19DC4A02-8C08-4F99-BB86-56CD9309892D@.microsoft.com:
> Greetings All,
> We want to have a user_id which will be a primary key and all other
> tables will be joining on this user_id. My question is
> Option 1: Have user_id an identity and have foreign key on this
> identity table.
> Option 2: Dont use identity as user_id and generate user_id with some
> logic.
> Our group is having mixed opion and we will go with maximum number of
> suggestions we get here. Also please include why you think the option
> you selected is right.
> Thanks,
> Arshad
>|||Jeff Clausius wrote:
> If you skin is thick enough :>), I'd recommend posting this to
> microsoft.public.sqlserver.programming, and await the verbal assault from
> Joe Celko. If you can get past his insults and obtuse style, he does
> have a good insight on "some" issues.
I couldn't have said it better myself!
-E|||When I create a diagram, and thereby make constraints, it is nice to
have a singe field to link to that is unique. If there isn't a single
field that makes it unique then and Identity is the perfect choice.
Since I now have this unique key, why not have it the primary key?
This ID is never going to change, nor can it be changed because of the
constraints, with records from other tables referencing it. What I
really despise is when two tables are linked together by more than one
field. If I want to make other unique key constraints I can, but for
consistency I always make the Identity column the primary key.
Another thing I like to do is always name the Identity column ID. That
way I immediately know it is the identity and the primary key for the
table I am looking at. Now lets say the name of the table is
"Department". Now when I link it to the Department Table to the
Employee table, I create a field in the Employee table called
"DepartmentID", and link it to the ID column in the Department table.
So I always know that a field that just ends in "..ID" references the
Primary Key and Identity of another table, and I know the name of the
table, because it is what is in front of "ID". I never have to wonder
or look at constraints to see if a field is referencing another table.
I always know what table and field it references just by the name of the
field. Even those who hate identity columns admin there are times that
they make sense. I say if you are ever going to use them, always use
them. Consistency is the key to making things simple.
Ken Cushing
West Valley City
kcushing
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1761833.html

Identity column as a primary key with foreign key relationships

Greetings All,
We want to have a user_id which will be a primary key and all other tables
will be joining on this user_id. My question is
Option 1: Have user_id an identity and have foreign key on this identity
table.
Option 2: Dont use identity as user_id and generate user_id with some logic.
Our group is having mixed opion and we will go with maximum number of
suggestions we get here. Also please include why you think the option you
selected is right.
Thanks,
Arshad
Arshad
arshadmd-nospam@.gmail.com
IDENTITY all the way unless you have good reason for genrerating your
own ID. What justification /resoning do you have for not using it?
|||Arshad,
Unfortunately you will also get mixed opinions here. You may read a
response from Celko where he will insult you and insist that you never
use an identity column as a primary key. Others will tell you that it
is OK. I've been through a similar issue and here is what I've learned:
1) If your project will ever be moved to a system like Oracle, you
don't want to use Identity columns as primary keys. If you will never
move your db to another system other than SQL, then using an identity
column as a primary key is OK.
2) I personally have used identity columns as primary keys in many
different scenarios and I have never had a problem with it.
3) Creating a primary key with a stored procedure or some other logic
will work but it only creates more work in coding for you and adds
additional bulk to the table that is not necessary because you will
still have the unique identity as well as a unique primary key.
4) Using multiple columns to create your primary key (social sec. and
birthday) is possible, but that can lead to confusi table relationships
and additional bulk to each related table. I like my table
relationships to be simple and intuitive, which is why I use identity
columns as primary keys.
The bottom line is that you cannot port your db to other systems if you
do this, but if porting your code to another system is not likely to
happen, then the decision is a matter of preference for the developers.
I find using identity columns as primary keys keeps the design simple
and does not add needless bulk and clutter.
|||Use an autogenerated Primary key of type integer for a primary key on which
you build relationships. If you also want to use this primary key as a
userId by which to find records this is fine say you want this to be a
customer number. However having a customer 1 followed by a customer 2 etc,,
may not be a good idea if these codes are used to allow any customer access
to the database, say via internet. Its a better idea in such a case to have
non-sequential customer identifications. So a combination of RecordId as the
primary key being automatically genereated and used for relations PK-FK and
a separate USERID field that you generate and that must also be unique is
probably better. One way to generate a non sequential ID is by using a
default value derived as follows. (rand((datepart(month,getdate()) * 100000
+ datepart(second,getdate()) * 1000 + datepart(millisecond,getdate()))) *
1000000000). This generates a random number based on the seeds of the
computer date, you can ensure that its unique by making the column a unique
index.
In my view the primary key should never be part of data being viewed or used
directly by the end user and should always be created automatically by the
database engine and should ONLY be used for that particular purpose. Other
than that the basic most generally accepted rule is make the database engine
itself do as much of the work as possible for ensuring data correctnes,
there is also the fact that there are practical situations in which you may
wnt to change a userid, if the userid is the primary key you have to do
cascading updates and this takes time. If your PK is totally outside of what
users may be allowed to use, there are no cascades involved for updates,
ever.
Also, having the database do the work, removes the onus on the individual
programmer of having to call procedures specifically for creating
Primarykeys or ensuring relationship constraints are correctly applied. If
you do this in code you WILL make mistakes and you WILL forget to call the
procedures sometime.
I've seen many apps that do this in code (still today) and have had to
transfer data from these to corrrectly structured relational tables and each
time I have found things like orphan records or duplicate keys where none
should be.
Hope ths helps and that I haven't started anyone ranting and raving :-)
RD
"Arshad" <Arshad@.discussions.microsoft.com> wrote in message
news:19DC4A02-8C08-4F99-BB86-56CD9309892D@.microsoft.com...
> Greetings All,
> We want to have a user_id which will be a primary key and all other tables
> will be joining on this user_id. My question is
> Option 1: Have user_id an identity and have foreign key on this identity
> table.
> Option 2: Dont use identity as user_id and generate user_id with some
> logic.
> Our group is having mixed opion and we will go with maximum number of
> suggestions we get here. Also please include why you think the option you
> selected is right.
> Thanks,
> Arshad
> --
> Arshad
> arshadmd-nospam@.gmail.com
|||On Fri, 29 Jul 2005 08:50:01 -0700, Arshad wrote:

>Greetings All,
>We want to have a user_id which will be a primary key and all other tables
>will be joining on this user_id. My question is
>Option 1: Have user_id an identity and have foreign key on this identity
>table.
>Option 2: Dont use identity as user_id and generate user_id with some logic.
>Our group is having mixed opion and we will go with maximum number of
>suggestions we get here. Also please include why you think the option you
>selected is right.
>Thanks,
>Arshad
Hi Arshad,
In general, keys can fulfill two distinct functions.
Their first function is to provide a link between a row in a table and
an entity in the real world outside of the database. This is what I call
the business key, since in most cases, the business dictates what key to
use. If the users are employees and the HR department issues employee
numbers, than the business key is the employee number. In an American
tax-related database, SSN would be the business key. In a database that
supports the upkeep of a computer network, the username assigned by the
sysadmins for logging on to the network would be the business key. For
my dentists' customers, last name + address + date of birth might
qualify as the business key. And so on, and so on.
You should only consider having the database generate the business key
if there is at present no business key - and you'll still need to find
who'se in charge and get him or her to sign of on your proposal, since
it's not your job to change the business' processes.
The second function of a key is to link a row in one table to a related
row in (usually) another table - the well known FOREIGN KEY constraint.
In most cases, the FOREIGN KEY will refer to the PRIMARY KEY of the
related table. But it can also refer to any column (or combination of
columns) that is declared as UNIQUE in the related table.
My usual procedure is:
- First, find the business key, This one is always needed, since there
is no sense in storing data in a database if it can't be related back to
the real-world entities that it's supposed to describe.
- Second, determine of there will be any other tables referring to the
rows in this table. If there are, then determine if the business key is
a good condidate for implementing the FOREIGN KEY constraint. If it
isn't (e.g. becuase it is prone to frequent change, or because it is so
long that it would degrade performance in the database), then I'll
introduce a surrogate key - and in 99.9% of all cases, IDENTITY serves
fine as a surrogate key.
That leaves me with two possible designs:
1. Business key is suitable to be used in the FK relationship:
CREATE TABLE Tab1 (BusinessKey some_datatype NOT NULL,
other columns,
PRIMARY KEY (BusinessKey)
)
CREATE TABLE Tab2 (BusinessKeyForOtherTable other_datetype NOT NULL,
FK_To_Tab1 some_datetype [NOT] NULL,
other columns,
PRIMARY KEY (BusinessKeyForOtherTable)
FOREIGN KEY (FK_To_Tab1)
REFERENCES Tab1 (BusinessKey)
ON UPDATE CASCADE
ON DELETE NO ACTION
)
2. Business key is not suitable for FK relationship - use surrogate key:
CREATE TABLE Tab1 (Tab1_ID int NOT NULL IDENTITY,
BusinessKey some_datatype NOT NULL,
other columns,
PRIMARY KEY (Tab1_ID),
UNIQUE (BusinessKey)
)
CREATE TABLE Tab2 (Tab2_ID int NOT NULL IDENTITY,
BusinessKeyForOtherTable other_datetype NOT NULL,
FK_To_Tab1 int [NOT] NULL,
other columns,
PRIMARY KEY (Tab2ID),
UNIQUE (BusinessKeyForOtherTable)
FOREIGN KEY (FK_To_Tab1)
REFERENCES Tab1 (Tab1_ID
ON UPDATE NO ACTION-- Note this change!!
ON DELETE NO ACTION
)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Arshad wrote:
> Greetings All,
> We want to have a user_id which will be a primary key and all other tables
> will be joining on this user_id. My question is
> Option 1: Have user_id an identity and have foreign key on this identity
> table.
i use this method 99% of the time and have no problems.
the bigger issue for me is what should be the clustered index on the
table.
|||Arshad:
Identities do make things easier, but they do bring along some extra
baggage that you need to be aware of.
As Elroyskimms suggested problems regarding another vendor's DBMS
(although there is a work around in Oracle w/ sequences), you will also
need to consider if you ever plan on any type of replication or merging
of data between databases, as the uniqueness of the key does not exist in
this scope.
If you skin is thick enough :>), I'd recommend posting this to
microsoft.public.sqlserver.programming, and await the verbal assault from
Joe Celko. If you can get past his insults and obtuse style, he does
have a good insight on "some" issues.
Jeff Clausius
SourceGear
=?Utf-8?B?QXJzaGFk?= <Arshad@.discussions.microsoft.com> wrote in
news:19DC4A02-8C08-4F99-BB86-56CD9309892D@.microsoft.com:

> Greetings All,
> We want to have a user_id which will be a primary key and all other
> tables will be joining on this user_id. My question is
> Option 1: Have user_id an identity and have foreign key on this
> identity table.
> Option 2: Dont use identity as user_id and generate user_id with some
> logic.
> Our group is having mixed opion and we will go with maximum number of
> suggestions we get here. Also please include why you think the option
> you selected is right.
> Thanks,
> Arshad
>
|||Jeff Clausius wrote:

> If you skin is thick enough :>), I'd recommend posting this to
> microsoft.public.sqlserver.programming, and await the verbal assault from
> Joe Celko. If you can get past his insults and obtuse style, he does
> have a good insight on "some" issues.
I couldn't have said it better myself!
-E
|||When I create a diagram, and thereby make constraints, it is nice to have a singe field to link to that is unique. If there isn't a single field that makes it unique then and Identity is the perfect choice. Since I now have this unique key, why not have it the primary key? This ID is never going to change, nor can it be changed because of the constraints, with records from other tables referencing it. What I really despise is when two tables are linked together by more than one field. If I want to make other unique key constraints I can, but for consistency I always make the Identity column the primary key.
Another thing I like to do is always name the Identity column ID. That way I immediately know it is the identity and the primary key for the table I am looking at. Now lets say the name of the table is "Department". Now when I link it to the Department Table to the Employee table, I create a field in the Employee table called "DepartmentID", and link it to the ID column in the Department table. So I always know that a field that just ends in "..ID" references the Primary Key and Identity of another table, and I know the name of the table, because it is what is in front of "ID". I never have to wonder or look at constraints to see if a field is referencing another table. I always know what table and field it references just by the name of the field. Even those who hate identity columns admin there are times that they make sense. I say if you are ever going to use them, always use them. Consistency is the key to making things simple.
Ken Cushing
West Valley City

Identity column as a primary key with foreign key relationships

Greetings All,
We want to have a user_id which will be a primary key and all other tables
will be joining on this user_id. My question is
Option 1: Have user_id an identity and have foreign key on this identity
table.
Option 2: Dont use identity as user_id and generate user_id with some logic.
Our group is having mixed opion and we will go with maximum number of
suggestions we get here. Also please include why you think the option you
selected is right.
Thanks,
Arshad
Arshad
arshadmd-nospam@.gmail.comIDENTITY all the way unless you have good reason for genrerating your
own ID. What justification /resoning do you have for not using it?|||Arshad,
Unfortunately you will also get mixed opinions here. You may read a
response from Celko where he will insult you and insist that you never
use an identity column as a primary key. Others will tell you that it
is OK. I've been through a similar issue and here is what I've learned:
1) If your project will ever be moved to a system like Oracle, you
don't want to use Identity columns as primary keys. If you will never
move your db to another system other than SQL, then using an identity
column as a primary key is OK.
2) I personally have used identity columns as primary keys in many
different scenarios and I have never had a problem with it.
3) Creating a primary key with a stored procedure or some other logic
will work but it only creates more work in coding for you and adds
additional bulk to the table that is not necessary because you will
still have the unique identity as well as a unique primary key.
4) Using multiple columns to create your primary key (social sec. and
birthday) is possible, but that can lead to confusi table relationships
and additional bulk to each related table. I like my table
relationships to be simple and intuitive, which is why I use identity
columns as primary keys.
The bottom line is that you cannot port your db to other systems if you
do this, but if porting your code to another system is not likely to
happen, then the decision is a matter of preference for the developers.
I find using identity columns as primary keys keeps the design simple
and does not add needless bulk and clutter.|||Use an autogenerated Primary key of type integer for a primary key on which
you build relationships. If you also want to use this primary key as a
userId by which to find records this is fine say you want this to be a
customer number. However having a customer 1 followed by a customer 2 etc,,
may not be a good idea if these codes are used to allow any customer access
to the database, say via internet. Its a better idea in such a case to have
non-sequential customer identifications. So a combination of RecordId as the
primary key being automatically genereated and used for relations PK-FK and
a separate USERID field that you generate and that must also be unique is
probably better. One way to generate a non sequential ID is by using a
default value derived as follows. (rand((datepart(month,getdate()) * 100000
+ datepart(second,getdate()) * 1000 + datepart(millisecond,getdate()))) *
1000000000). This generates a random number based on the seeds of the
computer date, you can ensure that its unique by making the column a unique
index.
In my view the primary key should never be part of data being viewed or used
directly by the end user and should always be created automatically by the
database engine and should ONLY be used for that particular purpose. Other
than that the basic most generally accepted rule is make the database engine
itself do as much of the work as possible for ensuring data correctnes,
there is also the fact that there are practical situations in which you may
wnt to change a userid, if the userid is the primary key you have to do
cascading updates and this takes time. If your PK is totally outside of what
users may be allowed to use, there are no cascades involved for updates,
ever.
Also, having the database do the work, removes the onus on the individual
programmer of having to call procedures specifically for creating
Primarykeys or ensuring relationship constraints are correctly applied. If
you do this in code you WILL make mistakes and you WILL forget to call the
procedures sometime.
I've seen many apps that do this in code (still today) and have had to
transfer data from these to corrrectly structured relational tables and each
time I have found things like orphan records or duplicate keys where none
should be.
Hope ths helps and that I haven't started anyone ranting and raving :-)
RD
"Arshad" <Arshad@.discussions.microsoft.com> wrote in message
news:19DC4A02-8C08-4F99-BB86-56CD9309892D@.microsoft.com...
> Greetings All,
> We want to have a user_id which will be a primary key and all other tables
> will be joining on this user_id. My question is
> Option 1: Have user_id an identity and have foreign key on this identity
> table.
> Option 2: Dont use identity as user_id and generate user_id with some
> logic.
> Our group is having mixed opion and we will go with maximum number of
> suggestions we get here. Also please include why you think the option you
> selected is right.
> Thanks,
> Arshad
> --
> Arshad
> arshadmd-nospam@.gmail.com|||On Fri, 29 Jul 2005 08:50:01 -0700, Arshad wrote:

>Greetings All,
>We want to have a user_id which will be a primary key and all other tables
>will be joining on this user_id. My question is
>Option 1: Have user_id an identity and have foreign key on this identity
>table.
>Option 2: Dont use identity as user_id and generate user_id with some logic
.
>Our group is having mixed opion and we will go with maximum number of
>suggestions we get here. Also please include why you think the option you
>selected is right.
>Thanks,
>Arshad
Hi Arshad,
In general, keys can fulfill two distinct functions.
Their first function is to provide a link between a row in a table and
an entity in the real world outside of the database. This is what I call
the business key, since in most cases, the business dictates what key to
use. If the users are employees and the HR department issues employee
numbers, than the business key is the employee number. In an American
tax-related database, SSN would be the business key. In a database that
supports the upkeep of a computer network, the username assigned by the
sysadmins for logging on to the network would be the business key. For
my dentists' customers, last name + address + date of birth might
qualify as the business key. And so on, and so on.
You should only consider having the database generate the business key
if there is at present no business key - and you'll still need to find
who'se in charge and get him or her to sign of on your proposal, since
it's not your job to change the business' processes.
The second function of a key is to link a row in one table to a related
row in (usually) another table - the well known FOREIGN KEY constraint.
In most cases, the FOREIGN KEY will refer to the PRIMARY KEY of the
related table. But it can also refer to any column (or combination of
columns) that is declared as UNIQUE in the related table.
My usual procedure is:
- First, find the business key, This one is always needed, since there
is no sense in storing data in a database if it can't be related back to
the real-world entities that it's supposed to describe.
- Second, determine of there will be any other tables referring to the
rows in this table. If there are, then determine if the business key is
a good condidate for implementing the FOREIGN KEY constraint. If it
isn't (e.g. becuase it is prone to frequent change, or because it is so
long that it would degrade performance in the database), then I'll
introduce a surrogate key - and in 99.9% of all cases, IDENTITY serves
fine as a surrogate key.
That leaves me with two possible designs:
1. Business key is suitable to be used in the FK relationship:
CREATE TABLE Tab1 (BusinessKey some_datatype NOT NULL,
other columns,
PRIMARY KEY (BusinessKey)
)
CREATE TABLE Tab2 (BusinessKeyForOtherTable other_datetype NOT NULL,
FK_To_Tab1 some_datetype [NOT] NULL,
other columns,
PRIMARY KEY (BusinessKeyForOtherTable)
FOREIGN KEY (FK_To_Tab1)
REFERENCES Tab1 (BusinessKey)
ON UPDATE CASCADE
ON DELETE NO ACTION
)
2. Business key is not suitable for FK relationship - use surrogate key:
CREATE TABLE Tab1 (Tab1_ID int NOT NULL IDENTITY,
BusinessKey some_datatype NOT NULL,
other columns,
PRIMARY KEY (Tab1_ID),
UNIQUE (BusinessKey)
)
CREATE TABLE Tab2 (Tab2_ID int NOT NULL IDENTITY,
BusinessKeyForOtherTable other_datetype NOT NULL,
FK_To_Tab1 int [NOT] NULL,
other columns,
PRIMARY KEY (Tab2ID),
UNIQUE (BusinessKeyForOtherTable)
FOREIGN KEY (FK_To_Tab1)
REFERENCES Tab1 (Tab1_ID
ON UPDATE NO ACTION -- Note this change!!
ON DELETE NO ACTION
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Arshad wrote:
> Greetings All,
> We want to have a user_id which will be a primary key and all other tables
> will be joining on this user_id. My question is
> Option 1: Have user_id an identity and have foreign key on this identity
> table.
i use this method 99% of the time and have no problems.
the bigger issue for me is what should be the clustered index on the
table.|||Arshad:
Identities do make things easier, but they do bring along some extra
baggage that you need to be aware of.
As Elroyskimms suggested problems regarding another vendor's DBMS
(although there is a work around in Oracle w/ sequences), you will also
need to consider if you ever plan on any type of replication or merging
of data between databases, as the uniqueness of the key does not exist in
this scope.
If you skin is thick enough :> ), I'd recommend posting this to
microsoft.public.sqlserver.programming, and await the verbal assault from
Joe Celko. If you can get past his insults and obtuse style, he does
have a good insight on "some" issues.
Jeff Clausius
SourceGear
examnotes <Arshad@.discussions.microsoft.com> wrote in
news:19DC4A02-8C08-4F99-BB86-56CD9309892D@.microsoft.com:

> Greetings All,
> We want to have a user_id which will be a primary key and all other
> tables will be joining on this user_id. My question is
> Option 1: Have user_id an identity and have foreign key on this
> identity table.
> Option 2: Dont use identity as user_id and generate user_id with some
> logic.
> Our group is having mixed opion and we will go with maximum number of
> suggestions we get here. Also please include why you think the option
> you selected is right.
> Thanks,
> Arshad
>|||Jeff Clausius wrote:

> If you skin is thick enough :> ), I'd recommend posting this to
> microsoft.public.sqlserver.programming, and await the verbal assault from
> Joe Celko. If you can get past his insults and obtuse style, he does
> have a good insight on "some" issues.
I couldn't have said it better myself!
-E|||When I create a diagram, and thereby make constraints, it is nice to
have a singe field to link to that is unique. If there isn't a single
field that makes it unique then and Identity is the perfect choice.
Since I now have this unique key, why not have it the primary key?
This ID is never going to change, nor can it be changed because of the
constraints, with records from other tables referencing it. What I
really despise is when two tables are linked together by more than one
field. If I want to make other unique key constraints I can, but for
consistency I always make the Identity column the primary key.
Another thing I like to do is always name the Identity column ID. That
way I immediately know it is the identity and the primary key for the
table I am looking at. Now lets say the name of the table is
"Department". Now when I link it to the Department Table to the
Employee table, I create a field in the Employee table called
"DepartmentID", and link it to the ID column in the Department table.
So I always know that a field that just ends in "..ID" references the
Primary Key and Identity of another table, and I know the name of the
table, because it is what is in front of "ID". I never have to wonder
or look at constraints to see if a field is referencing another table.
I always know what table and field it references just by the name of the
field. Even those who hate identity columns admin there are times that
they make sense. I say if you are ever going to use them, always use
them. Consistency is the key to making things simple.
Ken Cushing
West Valley City
kcushing
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1761833.html

Identity and primary key

I confuse about Identity and primary key, what is the different between them. One table can have no primary key. Right? Thanks.Yes a table doesn't need a primary key...but if it's for relational data you should.

Heap tables wouldn't have a PK.

And IDENTITY Column is a special property that enables a column to set an incremental value...so you can't have the same value twice.

Alot of people CONFUSE this as a Primary Key...they even go out of their way to make it one...so would you do?

CREATE TABLE State (
StateId int IDENTITY(1,1) PRIMARY KEY
, StateCd char(2)
, StateName varchar(50))

And then still have to create a unique contraint on statedCd?

Doesn't make sense does it.

Some people will argue that at some point a state will change their code, and then you could just update the state table and be done with it, and not have to update every other table that stores state code.

I find that argument amusing.|||Glad we could entertain you! :D|||definitely i'm with brett on this one, the surrogate key for state code is insane

;)|||I agree in principle, but as I've said before I have a code library of reusable functions, procedures, and subroutines that work off of GUIDs. So to me, the extra column is worth the time saved in development and maintenance.|||you use a GUID on a table of state codes and state names?

WTF OMG LOL!

no offence :)|||I can just picture the performance on a system built based on PK's made of GUID's! Are they all clustered too?|||Not to launch the debate for the millionth time...but I've never heard of anyone using a GUID for code tables....

Yo, blind dude...what does it buy you?|||I've never seen a performance hit. These are not Terabyte databases. And as far as a GUID on State codes, what does he cost me? There are only 50 records, so how much more disk space or processing time does it require?
What it buys me is a lot of flexibility importing data and a lot of scalability across the enterprise. I can have multiple copies of the database at separate locations and merge the data without having to worry about key conflicts. I can pre-assign IDs to staging data and load it without ever having to figure out what incremental value was assigned to the record, for later processing. I can set up a single table in the database, for tracking record modification for instance, and have referential integrity between it and every other table in the database based solely on the Primary Key (or unique index, which is what I normally assign to the GUID value). I've developed Rapid Application Development templates in Access that allow me to create a new form based upon a table and have it automatically included in the menu system and synchronized with all the related forms in about fifteen minutes.
I can't tell you the number of times a client has come to me with a modification requirement that I was able to implement quickly just because my schema was based upon GUID surrogate keys.
I've use natural keys, identity keys, and GUIDs, and I've just decided that I can do a lot of cool SQL with GUIDs.
As far as a performance hit, MSSQL's own replication relies upon GUIDs.
So I'm gonna keep usin' 'em. Nyah! :p|||And as far as a GUID on State codes, what does he cost me? There are only 50 records, so how much more disk space or processing time does it require?well, okay, now, either you use a GIUD on the state code table just for the hell of it (insanity) with the 2-char state code as the primary key, or else you're using the GIUD as the primary key, in which case your 2-million-row employee address database has 2 million 16-byte GUIDs where a 2-char code would normally be

so how much more disk space and processing does it require? 2 million times 26 bytes -- yeah, i know, that's not much space, but the processing might hurt a lot

and don't forget you also have to always join to the state code table to find out that

123 sesame street, hollywood, 3F2434E0-4F76-12F3-9A2C-0305E54C3301

is in florida and not california

insane, i tell you, insane|||Yes, it is a disadvantage to have to join to the State code table to look up the state for a GUID. But that is a drawback of any surrogate key. Honestly, I don't always use surrogate keys for lookup tables, but when I do I use a GUID.

Here is an excellent article on the pros and cons of GUIDs and INTs:
http://www.informit.com/articles/article.asp?p=25862&redir=1

...so I ran a test on two tables. One table used INT, the other GUID. 1,000,000 rows in each table. 4,500 searches on each table. No discernable difference. Script is attached.

For gig and up tables, maybe the size of the GUID would start to affect performance. I don't have time to test that. But I'm not convinced the performance hit would be at all significant. How many times to I have to save 1 millisecond on a search to justify even 15 more minutes writing code?

Go ahead and laugh, but I chuckle every time I see a post on this forum asking how to retrieve the IDs of a batch of records just inserted. :cool:|||OK, read the article...lots of caveats there....|||Yeah, the article is great, for "Intro to Database Design 101". Either the author does not want to talk about "real" caveats, or he doesn't know about them himself. Here's the SHOWCONTIG results for both tables from Lindman's example, so YOU (Lindman) tell me what's wrong with this picture:

...wait a minute, I can't give you a final result because Lindman's script is still running, after 45 minutes...Is it because of those NEWID() function calls or because of IO? Well, it's IO, according to Perfmon. Anyway, based on partial results (250,000 rows or so) here's the output:


DBCC SHOWCONTIG scanning 'TestIdentity' table...

Table: 'TestIdentity' (795149878); index ID: 1, database ID: 9

TABLE level scan performed.

- Pages Scanned........................: 1445

- Extents Scanned.......................: 0

- Extent Switches.......................: 0

- Avg. Pages per Extent..................: 0.0

- Scan Density [Best Count:Actual Count]......: 0.00% [0:0]

- Logical Scan Fragmentation ..............: 0.55%

- Extent Scan Fragmentation ...............: 0.00%

- Avg. Bytes Free per Page................: 0.0

- Avg. Page Density (full)................: 99.81%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC SHOWCONTIG scanning 'TestGUID' table...

Table: 'TestGUID' (779149821); index ID: 1, database ID: 9

TABLE level scan performed.

- Pages Scanned........................: 2581

- Extents Scanned.......................: 0

- Extent Switches.......................: 0

- Avg. Pages per Extent..................: 0.0

- Scan Density [Best Count:Actual Count]......: 0.00% [0:0]

- Logical Scan Fragmentation ..............: 99.11%

- Extent Scan Fragmentation ...............: 0.00%

- Avg. Bytes Free per Page................: 0.0

- Avg. Page Density (full)................: 69.56%
Pay attention to Logical Scan Fragmentation.|||Pay attention to the results, Czarjabarov. There was no discernable or consistent difference after running 4500 selects.

Outahere.|||Pay attention to the results, Czarjabarov. There was no discernable or consistent difference after running 4500 selects.

Outahere.You wouldn't be showing "online" if you were really "outahere", and please refrain yourself from chauvinistic comments,it's not gonna get you anywhere (at least not where you want to be) :rolleyes:

...And, for your information, not all apps are written to support the primitivism of the logic that you used in your example, so there, - outahere ;)|||boys will be boys...I guess...

But where does chauvinistic come from?

Damn hangover...|||rdjabarov. How did you get "0 extents scanned" in your DBCC SHOWCONTIG result?

Personally, I look at scan density myself, but that is for lack of seeing any Microsoft article that comprehensively explains any difference between the various densities.|||Brett left the country column out of his table definition. It doesn't make sense to me to try and track states without their country.

How do you deal with multiple states having the same code? Does AB refer to a state in Canada or Brazil?

How do you cope with states changing their names? Better yet, how do you cope with them changing their country? Worst of all, how do you deal with states changing their geographic boundaries?

If you deal with a small subset of states (say just the provinces of Canada), this problem seems simple. If you have to deal with the more generic idea of "states" in the abstract form, it gets really complicated.

-PatP|||so, pat, you are saying simple = integer key, complicated = guid key?

i don't buy it|||Pat goes mia, and then comes up with that...

ok..ok...I buy the thing where a State (actually a province) changes countries...when is quebec going to susceed?

I still subscribe to the theory that it is something new...what do you do with history if you decide to change a key?

Did the Entity just dissappear?|||No, not at all. The GUID versus INT issue is neither simple nor complex. If you buy into the idea of a surrogate key, how it is formatted is irrelevant, as long as the key values are unique.

What I meant to say is that if you only consider a small subset of politically stable states (such as those in the United States), the issues you face are quite simple. If you consider all states worldwide, then all of the issues get a lot more complex.

-PatP|||Pat goes mia, and then comes up with that...

ok..ok...I buy the thing where a State (actually a province) changes countries...when is quebec going to susceed?

I still subscribe to the theory that it is something new...what do you do with history if you decide to change a key?

Did the Entity just dissappear?Quebec succeeds about three times a day (sometimes from Canada, sometimes from other parts of Quebec), just visit any bar in the Province and you'll find someone in the process of declaring independance!

Trying to figure out how to track history is another issue entirely. Better than the issue of succession, how do you deal with the problems when a county (like Yugoslavia) splits, the residents can't decide on how many new countries are created (two, three, or more), or what the new states political alignments are, and the geographic boundaries are in dispute. That is a nightmare to model, and there are at least two other cases that may arise soon that make it look like child's play!

Once I get over the last ten days, I'll have some doozies to tell. Right now, I'm still just trying to get my feet back under me on a consistant basis.

-PatP|||No, not at all. The GUID versus INT issue is neither simple nor complex. If you buy into the idea of a surrogate key, how it is formatted is irrelevant, as long as the key values are unique.i am relieved to hear it

i personally think the 4-byte integer is a lot nicer of a surrogate than the 16-character guid, it's nice to get confirmation that the difference -- other than the obvious difference in table and index space -- is irrelevant

yes, that was a bit of a troll, because i do realize how difficult it is to migrate identity keys

:)|||by the way, the word you americans are having so much trouble spelling is secede

probably because it's been so long since somebody (*cough*the south*cough*) tried to do it down there

:)|||Yes, but remember that when the South tried it they were unsecessful.

My point in all this is not to claim that GUIDs are better than INTs for surrogates. They offer some additional functionality at an additional cost. It's a trade-off, like any other feature you would build into an app.

But claims that using GUIDs are going to make your application run like a slug are just gibberish.|||by the way, the word you americans are having so much trouble spelling is secedeThey are homonyms Rudy... That is a joke (taught to me years ago by a lady from Quebec, so nobody blow a gasket on it). I'll try to remember to include a smiley in the future.

-PatP|||Yes, but remember that when the South tried it they were unsecessful.

My point in all this is not to claim that GUIDs are better than INTs for surrogates. They offer some additional functionality at an additional cost. It's a trade-off, like any other feature you would build into an app.

But claims that using GUIDs are going to make your application run like a slug are just gibberish.Plus the fact that GUIDs are relatively randomly distributed fixes a number of the problems that Mr Celko and I wrangled over that are caused by the way that IDENTITY values are distributed. He finally conceeded that the nature of the surrogate key wasn't his primary problem, it was the way those keys were being used... GUIDs discourage that.

-PatP|||They are homonyms Rudythey may very well be, the way you pronounce them, but for everybody else, they aren't

:) :o :cool: ;) :rolleyes: :mad: :o :) :p :)|||Homonyms are still accepted in most of the U.S., though in many States is now illegal to use two of them in the same sentence.|||they may very well be, the way you pronounce them, but for everybody else, they aren't

:) :o :cool: ;) :rolleyes: :mad: :o :) :p :)The words never sounded exactly the same to me, but they obviously sounded the same (or at least similar enough) to her that she made the joke. She was quite "French" in many ways and very proud of it, so sometimes her humor was a bit difficult for me to follow.

-PatP|||i know that feeling, i have difficulty both understanding humour as well as conveying it

except if i'm telling a joke in person, then it's obvious

for example, this might not come across as funny at all when written down, but i guarantee you, if you heard and saw me tell it, you'd ROFL

cowboy goes into a bar and orders a drink
"say," he says to the bartender, "where is everybody? this bar is usually busier than this"
"oh," says the bartender, "they're all down at the public square, today's the day they're hanging brown paper pete"
"brown paper pete?" asks the cowboy, "who's he?"
"oh, you know," says the bartender, "his pants are made of brown paper, his vest is made of brown paper, even his hat is made of brown paper"
"oh," says the cowboy, "what're they hanging him for?"
"rustling"|||ah... you must be french rudy, I hear the french have no concept of humor eh.
insert emoticom here.|||Let's see, where is that recipe.....Ahh...add one can gasoline...light match...

So, how are things in Nunavut these days? (ducks)

The only rule of thumb I have been given, but which I admittedly do not get to use very often, is if the natural primary key value(s) is likely to change, then you should use a surrogate key. If they are going to be static, then you should use the natural key. The only exception to that is if the natural key is going to make for a poor index (by being way too wide). The only problem with these rules is that they are entirely subjective. Does half of the Northwest Territory re-naming itself count as a common enough change? None of us (in the US, anyway) saw it coming.

In case you are behind on news, Vermont debates about seceding (happy Rudy? ;-)) about once every 10 years or so, and there is a group in Northern California that wants to separate from Southern California. Then there was a story a few years back about a vote to separate a piece of LA into a new town.

On the other hand, Natural keys make life so much easier in highly normalized schemas..|||Hmmm...Seems I missed one, too...

http://zapatopi.net/cascadia.html|||http://zapatopi.net/cascadia.htmlthat's hilarious

thank you for that link

i love how well it's done

this is from the same guy who created the AFDB which i wrote about last year -- http://rudy.ca/afdb.html|||rdjabarov. How did you get "0 extents scanned" in your DBCC SHOWCONTIG result?

Personally, I look at scan density myself, but that is for lack of seeing any Microsoft article that comprehensively explains any difference between the various densities.I actually ran it on Yukon, and this value is not computed because...well, just take my word for it. For 7.0 and 2K it will show the number of extents used by a table or index.|||Ahh. Good thing the local users group is going to explain some of that:

http://www.nesql.org/default.aspx|||Where? I don't see anything except the main page|||Should be the next meeting. Dec 9.