Print list of tables with Identity row set to NOT FOR REPLICATION

Does anyone know if there's an SQL command i can run that will list
the tables in a database that have an identity column set to NOT FOR
REPLICATION?

Many thanks

Dan Williams."Dan Williams" <dan_williams@.newcross-nursing.com> wrote in message
news:2eac5d02.0406030812.2651f9e6@.posting.google.c om...
> Does anyone know if there's an SQL command i can run that will list
> the tables in a database that have an identity column set to NOT FOR
> REPLICATION?
> Many thanks
> Dan Williams.

select TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where columnproperty(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdNotForRepl') =
1

Simon|||Cool. Thanks for that, it worked a treat.

Dan

"Simon Hayes" <sql@.hayes.ch> wrote in message news:<40bf6492$1_1@.news.bluewin.ch>...
> "Dan Williams" <dan_williams@.newcross-nursing.com> wrote in message
> news:2eac5d02.0406030812.2651f9e6@.posting.google.c om...
> > Does anyone know if there's an SQL command i can run that will list
> > the tables in a database that have an identity column set to NOT FOR
> > REPLICATION?
> > Many thanks
> > Dan Williams.
> select TABLE_NAME, COLUMN_NAME
> from INFORMATION_SCHEMA.COLUMNS
> where columnproperty(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdNotForRepl') =
> 1
> Simon

PRIMARYKEY VIOLATION

Hi,
I am dealing with merge replication.
For identity columns EVEN seed value is set in Publisher database
and ODD seed value is set in Subscriber database(increment value 2) to avoid
insertion conflicts.
For a particular table, usually the records are inserted from
subscriber(through application) and rarely from publisher. During this change
an error 'Primary Violation' occurs.
(1) What is the reason for this?
(2) Is there any way to avoid this?
(3) How can I get the next identity value to be generated?
Thanks,
Soura
Its hard to say. Use the conflict viewer to see if you can figure out where
the two rows are coming from. Partitioning is the way to avoid this, but it
looks like you have done this.
A DBCC Checkident('tablename') will give you the current value, so add the
increment to it to get the next value.
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
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:4208FC88-5A06-46F8-BE66-64EFF7ECDBC4@.microsoft.com...
> Hi,
> I am dealing with merge replication.
> For identity columns EVEN seed value is set in Publisher database
> and ODD seed value is set in Subscriber database(increment value 2) to
> avoid
> insertion conflicts.
> For a particular table, usually the records are inserted from
> subscriber(through application) and rarely from publisher. During this
> change
> an error 'Primary Violation' occurs.
> (1) What is the reason for this?
> (2) Is there any way to avoid this?
> (3) How can I get the next identity value to be generated?
> Thanks,
> Soura
>
>
sql

Primary/Foreign/Identity keys & Encryption

Hi all!

I'm just getting my feet wet with how encryption works in SQL 2005. With regards to the encryption of primary / foreign keys, I'm not entirely clear on the best approach. Below are three examples of typical table structures I currently have:

== Customers table ==
CustomerID (PK, int, Identity)
CustomerName (varchar)

== Orders table ==
OrderID (PK, int, Identity)
CustomerID (int, foreign key)
CreditCardNumber (varchar)

== OrderDetails table (1 to Many) ==
OrderID (PK/FK, int)
ItemNumber (PK, int)
ItemDescription (varchar)

The Customers and Orders tables use identity values as their primary keys. From what I can tell, CustomerID in the Customers table cannot be encrypted and OrderID in the Orders table cannot be encrypted because they are identity values. In these cases, would it be safer (in terms of security) to create a separate, meaningless identity key column in the Customers table and then remove the identity attribute from CustomerID so I can encrypt CustomerID?

Similarily in the OrderDetails table, OrderID and ItemNumber form a composite key. These values are important in that I don't want them to be tampered with. Am I better off creating a separate identity key column which becomes the table's primary key ... then encrypt both the OrderID and ItemNumber columns in this table?

Any ideas are appreciated.

Thank you,
Ben

Hey Ben,

You have the principle behind encryption correct.

The tricky thing is when you use encryption on keys, a lot of the value of having these keys goes away. For eample, because encryption is non-deterministic, you won't be able to use OrderID as a FK in the OrderDetails table (unless if you encrypt once and then insert into both tables, but this leaks information. Then again, this might be acceptable in your application). You can still use PK, but they will behave differently. For example, because encryption is non-deterministic, just having the primary keys no longer guarantees that the columns will be unique. If you try to insert Encrypted("id1") and then Encrypted("id1") again, you actually end up with two different cipher values so the the table will allow both inserts.

Security basically destroys information (well encrypted data is indistinguishable from random data) while the point of using keys is to preserve information for reference. Consider, for example, the difficulties you will encounter attempting to do searches or joins on CustomerID and OrderID if they are encrypted (Laurentiu has a good blog entry on this here: http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx).

You can also check Laurentiu's blog for an example of creating an application using encryption: http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx this doesn't completely solve your problems, but it might be useful in seeing one way to apply encryption.

Please let us know if you would like more information or have further questions.

Sung

sql

primary keys

I am using the IDENTITY feature to generate the values fo rmy primary keys. I understand that primary keys need to be unique.

If I undestand correctly then they need to be uniquely identified in all table with in the sam database.

I am not sure if when I create a primary key using this feature. Do I need to change the seed and increment values in order to have primary keys for each table that are truely unique? This seems like the way to make all of the PK values in a database unique.

-Thanks to anyone that may be able to help

First off, a reminder that just setting an identity column doesn't make a row unique to the user. Having values:

Identity Name

1 Fred

2 Fred

Will make the two rows indistiguishable. So an Alternate Key on Name would make this far more ideal.

And yes, if you truly need your integer identity index keys to be unique in a database, a combination of seed and a check constraint will work:

create table first
(
firstId int identity(1,1) check (firstId between 1 and 100)
)
create table second
(
secondId int identity(101,1) check (secondId between 101 and 200)
)
go
insert into first default values;
insert into first default values;
insert into second default values;
insert into second default values;

select *

from first

select *

from second

firstId

--

1

2

secondId

--

101

102

Another method is to just use a guid for your keys. Of course guids are much larger and unwieldly to work with, but they are another way to go.

A big question is why do you need the values to be unique in the database? Just having them unique in the table they are in is generally good enough. It will definitely make it easier to work with since you won't have to be concerned with ranges of values later, especially if the data grows greater than initially expected.

|||

Primary key is the Domain Integrity Constraint. Here the Domain means a single table. Primary key insures the uniqueness of the table (single domain). It never validates other tables. Practically your understanding about PK is not possible. Bcs the number of records in each table is non-deterministic values. You can't make any assumption to accommodate this. Think about performance if i have more tables in my database.

But, UNIQUEIDENTIFIER typed values are used to identify the unique value across the table/database/server. Since it use the GUID on every generation the GUID must be unique.

|||

Thanks for the info. I need to do some studying on domain constraints and the different things that can be used to maintain integrity in this type of situation.

-Thanks

|||

Thanks for the info. I need to do some studying on domain constraints and the different things that can be used to maintain integrity in this type of situation.

-Thanks for your help

Primary key, Identity, Integrity constraints

Hi,

We will be using SQL Compact Edition in our application. We are doing some tests to make sure that SQL Compact is suitable for our requirments.

Our problem is with Primary keys and how to maintain data integrity since we will have 100+ clients inserting, updating, and synchronizing with SQL Server.

Can we control Identity range?

Shall we use UniqueIdentifires insted of int?

How can we detect errors at the client side? (For example, if two clients are trying to synchronize 2 columns with the same primary key value)

We will use Merge replication.

Thank you

First, understand that a SQLCe database is SINGLE user. If you want to share the data, you'll need to replicate to a SQL Server Workgroup edition or better. In this case, you'll need to use UniqueIdentifiers to make sure each separate database has unique values for the PKs. This is discussed in my new EBook. See www.hitchhikerguides.net.sql

primary key wont auto-update

I discovered the cause:
in SQL Server Enterprise Manager, open up db tree, right click the table, go - 'design' and set 'Identity' to yes.You were confusing "Primary Key" and "Identity Value". They are two different concepts which just happen to be frequently applied to the same column.

Primary key vs Identity column

What is the different between Primary key and Identity Column? and when should I them?

(Im just a beginner with databases...)

A primary key is a column that has only unique entries and is used as the key to that table, often by other tables as a foreign key. It can be an Identity, but it doesn;t have to be. An identity is a unique, system assigned data entry that guarantees uniqueness and is often apporpriately used as a primary key in a table.

For example, let's say you had 20 people in a table, and you recorded each with their different phone number. Since the phone is unique it could be a primary key. But now, suppose you want to add someone who lives in the same house as someone already in your table. Their phone can't match anyone else's since that violates the uniqueness of the primary key. You need to give them a dummy phone number, which is useless data. Using an identity column solves that, and even if someone moves and changes phone number, the primary key in the identity stays the same.

Jeff

Primary key violation

Hi,
I have got a very peculier kind of problem. My package is running on SQL 2000. There is a identity primary key in a table. Now when I submit the data from 2 different computer at the same time. Only one data is storing. The reason behind this is the primary key violation. as both the data are sending the request to the database at the same time.............n as the primary key is th identity column, it is storing one that value which is able to store the data at the forst hand.
Now plz help me out in this regard............. :confused:Help you do what?

Eliminate the dups, Or remove the constraint?|||The target table should have the original IDENTITY field and a LOCATION field as primary key. Make sure that the field does not have IDENTITY property on the target column. The process should be modified to change data retrieval from a table to a view where an artificial LOCATION column is added. That's at least how I'd do it. Give us more details maybe someone will come up with something better.|||The target table should have the original IDENTITY field and a LOCATION field as primary key. Make sure that the field does not have IDENTITY property on the target column. The process should be modified to change data retrieval from a table to a view where an artificial LOCATION column is added. That's at least how I'd do it. Give us more details maybe someone will come up with something better.

Really...man I hate surrogates....|||rdajabarov's solution is the cleanest, but tsk, tsk,... should'a used GUIDs... ;)

Gotta love those surrogate (GUID) keys!|||I'm confused, are you inserting the values into the identity column on the target table or letting target table generate the identity value?

Blindman what's the storage size for a GUID?|||binary(16)|||bm - you're right, GUID would be perfect for this implementation. 2 things that I have against it as far as the original posting goes:


1. Will have to completely redesign the db, and what's most painful, - redesign the app.
2. As I posted before, it's easier to type a number in the search by key field, than a GUID value.|||CREATE TABLE [dbo].[vfar_Bact_phylum_tb] (
[Bact_sr_phylum] [int] IDENTITY (1, 1) NOT NULL ,
[Bact_phylum] [varchar] (30) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[vfar_Bact_phylum_tb] WITH NOCHECK ADD
CONSTRAINT [PK_vfar_Bact_phylum_tb] PRIMARY KEY CLUSTERED
(
[Bact_sr_phylum]
) ON [PRIMARY]
GO

This is my table structure. Now from two different computers i'm sending the data to be submitted to this table at the same time. But unfortunately only single data is being saved.

All i want is to save both the data, no matter.........how many simultaneous request is going to the DB.

No, not at all.........its not at all possible to change the table design at this point of time.|||Then get rid of the constraint|||...and of IDENTITY property.|||bm - you're right, GUID would be perfect for this implementation. 2 things that I have against it as far as the original posting goes:


1. Will have to completely redesign the db, and what's most painful, - redesign the app.
2. As I posted before, it's easier to type a number in the search by key field, than a GUID value.

1) Even worse, will have to redesign source apps.
2) GUIDS are a bitch to type, but users shouldn't be entering them anyway. I think surrogate keys should be absolutely invisible to the users.

But yeah, it's too late for this guy's purpose.sql

Primary Key in View

how to create view in sql server 2005 that recognize Identity And Primary key of base tableif you want to know about the metadata in SQL Server Server like Constraints / Tables etc. you can use the INFORMATION_Schema Views, in this case you would query the INFORMATION_SCHEMA.Constraints view.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

SELECT COLS.table_schema
,COLS.Table_name
,COLS.COLUMN_NAME
,cols.CONSTRAINT_NAME
,ac.IS_identity
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS COLS
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS CONS
ON COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME
JOIN sys.all_columns ac
ON OBJECT_NAME(ac.object_id) = COLS.table_name
AND ac.name=COLS.COLUMN_NAME
WHERE CONS.CONSTRAINT_TYPE LIKE 'PRIMARY KEY'
ORDER BY COLS.CONSTRAINT_NAME, COLS.ORDINAL_POSITION

as i wrote this pretty quick and didnt test it fully.. using it is on your own risk ;)

Guldmann, platon.dk

primary key in the network

Hi,

My database has a table. This table has a column ( named : No.) set to be a primary key, and it's ( identity). When several people access the database in the same time and add some data, some of them will see this error message : ( column (No.) doesn't allow repeated values) How can I solve this problem?

Thanks.

Hi,

My database has a table. This table has a column ( named : No.) set to be a primary key, and it's ( identity). When several people access the database in the same time and add some data, some of them will see this error message : ( column (No.) doesn't allow repeated values) How can I solve this problem?

Thanks a lot.

|||

Are you inserting explicit values for the identity column, or letting SQL Server autonumber them for you?

Thanks,

John

|||

SQL Server autonumber them for me.

one more easy question:

What's the kind of the network SQL server works with? Is it peer to peer ( workgroup ) or server (domain ) network?

thanks

|||

I am still unsure what is going on in your first problem, if you could provide a code sample or more information it would help a lot. As for your second question, what do you mean by what kind of network does it work with? Are you asking about windows authentication for SQL Server? The architecture is client server, not p2p but I am unsure why you have broken these two communication archetypes into workgroup/domain groupings so I am not sure how to answer that question.

Thanks,

John

|||The identity column is maintained by SQL Server only, until you specify the Identity Insert to ON, to insert values for yourself. My next step if I were you would be to sniff the commands that are executed against the database using SQL Server profiler. I guess that the error message either comes from anothert column having A UNIQUE constraint build upon it or someone uses the IDENTITY INSERT command to insert values manually.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
--sql

Primary Key falling short

Plz tell me what to do if Primary key fall short and execed the max size of
the datatype like integer with Identity increment option.If the identity value falls short you will get an arithmetic overflow error.
But then you could change your identity field to another data type. For
example, if your identity is using the int data type you can then change to
bigint that, according to SQL Server 2000 BOL, can hold integers from from
-2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"Ravi" wrote:
> Plz tell me what to do if Primary key fall short and execed the max size of
> the datatype like integer with Identity increment option.|||you have to drop the primary key and change the column datatype.
e.g.
create table tb1(i tinyint identity(254,1),constraint pk primary key(i))
go
insert tb1 default values
insert tb1 default values
--overflow error here
insert tb1 default values
go
alter table tb1 drop constraint pk
go
alter table tb1 alter column i bigint
go
alter table tb1 add constraint pk primary key(i)
go
--okay now
insert tb1 default values
go
select * from tb1
go
drop table tb1
-oj
"Ravi" <Ravi@.discussions.microsoft.com> wrote in message
news:CEAF4CD0-8F20-4E8F-ADF2-6490A60857E5@.microsoft.com...
> Plz tell me what to do if Primary key fall short and execed the max size
> of
> the datatype like integer with Identity increment option.sql

Primary Key falling short

If the identity value falls short you will get an arithmetic overflow error.
But then you could change your identity field to another data type. For
example, if your identity is using the int data type you can then change to
bigint that, according to SQL Server 2000 BOL, can hold integers from from
-2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"Ravi" wrote:

> Plz tell me what to do if Primary key fall short and execed the max size o
f
> the datatype like integer with Identity increment option.Plz tell me what to do if Primary key fall short and execed the max size of
the datatype like integer with Identity increment option.|||If the identity value falls short you will get an arithmetic overflow error.
But then you could change your identity field to another data type. For
example, if your identity is using the int data type you can then change to
bigint that, according to SQL Server 2000 BOL, can hold integers from from
-2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"Ravi" wrote:

> Plz tell me what to do if Primary key fall short and execed the max size o
f
> the datatype like integer with Identity increment option.|||you have to drop the primary key and change the column datatype.
e.g.
create table tb1(i tinyint identity(254,1),constraint pk primary key(i))
go
insert tb1 default values
insert tb1 default values
--overflow error here
insert tb1 default values
go
alter table tb1 drop constraint pk
go
alter table tb1 alter column i bigint
go
alter table tb1 add constraint pk primary key(i)
go
--okay now
insert tb1 default values
go
select * from tb1
go
drop table tb1
-oj
"Ravi" <Ravi@.discussions.microsoft.com> wrote in message
news:CEAF4CD0-8F20-4E8F-ADF2-6490A60857E5@.microsoft.com...
> Plz tell me what to do if Primary key fall short and execed the max size
> of
> the datatype like integer with Identity increment option.|||you have to drop the primary key and change the column datatype.
e.g.
create table tb1(i tinyint identity(254,1),constraint pk primary key(i))
go
insert tb1 default values
insert tb1 default values
--overflow error here
insert tb1 default values
go
alter table tb1 drop constraint pk
go
alter table tb1 alter column i bigint
go
alter table tb1 add constraint pk primary key(i)
go
--okay now
insert tb1 default values
go
select * from tb1
go
drop table tb1
-oj
"Ravi" <Ravi@.discussions.microsoft.com> wrote in message
news:CEAF4CD0-8F20-4E8F-ADF2-6490A60857E5@.microsoft.com...
> Plz tell me what to do if Primary key fall short and execed the max size
> of
> the datatype like integer with Identity increment option.

Primary key and identity fields information got lost by "Import Da

Hi all!
I want to import data from Sql Server 2000 into Sql Server 2005.
When i select "Import Data" in 2005 and import tables directly from the 2000
Data Source, the primary keys and the identity fields information is lost in
2005.
Who knows why?
Best regards
Ulrich Schumacher
urlich
How about BACKUP /RESTORE commands
Or you can create a linked server to SQL Server 2000 and run SELECT * INTO
Schema.Mytable FROM SQL2K.Database.DBO.Table it ON SQL Server 2005
"ulrich schumacher" <ulrichschumacher@.discussions.microsoft.com> wrote in
message news:D20F7B7B-5C85-441B-AFE5-14191B3DF6CA@.microsoft.com...
> Hi all!
> I want to import data from Sql Server 2000 into Sql Server 2005.
> When i select "Import Data" in 2005 and import tables directly from the
> 2000
> Data Source, the primary keys and the identity fields information is lost
> in
> 2005.
> Who knows why?
> Best regards
> Ulrich Schumacher
>

Primary key and identity fields information got lost by "Import Da

Hi all!
I want to import data from Sql Server 2000 into Sql Server 2005.
When i select "Import Data" in 2005 and import tables directly from the 2000
Data Source, the primary keys and the identity fields information is lost in
2005.
Who knows why?
Best regards
Ulrich Schumacherurlich
How about BACKUP /RESTORE commands
Or you can create a linked server to SQL Server 2000 and run SELECT * INTO
Schema.Mytable FROM SQL2K.Database.DBO.Table it ON SQL Server 2005
"ulrich schumacher" <ulrichschumacher@.discussions.microsoft.com> wrote in
message news:D20F7B7B-5C85-441B-AFE5-14191B3DF6CA@.microsoft.com...
> Hi all!
> I want to import data from Sql Server 2000 into Sql Server 2005.
> When i select "Import Data" in 2005 and import tables directly from the
> 2000
> Data Source, the primary keys and the identity fields information is lost
> in
> 2005.
> Who knows why?
> Best regards
> Ulrich Schumacher
>

Primary key and identity fields information got lost by "Import Da

Hi all!
I want to import data from Sql Server 2000 into Sql Server 2005.
When i select "Import Data" in 2005 and import tables directly from the 2000
Data Source, the primary keys and the identity fields information is lost in
2005.
Who knows why?
Best regards
Ulrich Schumacherurlich
How about BACKUP /RESTORE commands
Or you can create a linked server to SQL Server 2000 and run SELECT * INTO
Schema.Mytable FROM SQL2K.Database.DBO.Table it ON SQL Server 2005
"ulrich schumacher" <ulrichschumacher@.discussions.microsoft.com> wrote in
message news:D20F7B7B-5C85-441B-AFE5-14191B3DF6CA@.microsoft.com...
> Hi all!
> I want to import data from Sql Server 2000 into Sql Server 2005.
> When i select "Import Data" in 2005 and import tables directly from the
> 2000
> Data Source, the primary keys and the identity fields information is lost
> in
> 2005.
> Who knows why?
> Best regards
> Ulrich Schumacher
>

Primary key and identity

Can someone tell me the difference between making a column primary key column vs. making it identity column?

thanks

Identity just auto increments the value in that column (like autonumber in Access). A primary key uniquely identifies a specific row. Obviously you can have only one primary key per table (you can make a combination of columns the primary key - but I do not recommend this).

Primary Key

I am setting up some tables where I used to have an identity column as the primary key. I changed it so the primary key is not a char field length of 20.

Is there going to be a big performance hit for this? I didn't like the identity field because every time I referenced a table I had to do a join to get the name of object.

EG:

-- Old way
tbProductionLabour
ID (pk)| Descr | fkCostCode
-------
1 | REBAR | 1J

tbTemplateLabour
fkTemplateID | fkLabourID | Manpower | Hours
--------------
1 | 1 | 1 | 0.15

-- New way
tbProductionLabour
Labour | fkCostCode
-------
REBAR | 1J

tbTemplateLabour
fkTemplateID | fkLabour | Manpower | Hours
--------------
1 | REBAR | 1 | 0.15

This is a very basic example, but you get the idea of what I am referring to.

Any thoughts?

MikeI didn't like the identity field because every time I referenced a table I had to do a join to get the name of object.

The light! Don't look at the light!

I guess I'll add you to the "not preffering surrogates" group

http://weblogs.sqlteam.com/brettk/archive/2004/06/09/1530.aspx

Excuse me while I climb back upon my barst...um desk chair...yeah that's right...|||EDIT: Didn't we have this conversation already?|||EDIT: Didn't we have this conversation already?
Probably, but as an in-experienced developer (wannabe) I was wondering if not using a identity field will really make that great of a performance difference.

I think the char(20) for a primary key is better mainly because when I open a table I am not seeing a number which I will have to look up. It also would reduce the number of joins I would have to make (which I guess would be better for performance). But, for looking up values or joining on that field, would the performance reduction be neglable or significant enough to want to use the identity field?

Mike|||Let me ask you this...

What do you think would be faster.

A). An umpteen table join on surrogate keys to get the data, or

B). A SELECT against 1 Table|||Let me ask you this...

What do you think would be faster.

A). An umpteen table join on surrogate keys to get the data, or

B). A SELECT against 1 Table
I would assume option B, but let me ask you this.

What do you think would be faster:
A) Looking up values based on an integer
or
B) Looking up values based on an umpteen char string

?

Mike B|||OK, I'll give you 80/1000 of a second

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 sysname)
CREATE TABLE myTable00(Col1 sysname, Col2 int IDENTITY(1,1))
GO

DECLARE @.x int
SELECT @.x = 1
WHILE @.x < 1000
BEGIN
INSERT INTO myTable99(Col1) SELECT TABLE_NAME FROM INFORMATION_SCHEMA.Tables
INSERT INTO myTable00(Col1) SELECT TABLE_NAME FROM INFORMATION_SCHEMA.Tables
SELECT @.x = @.x + 1
END

INSERT INTO myTable99(Col1) SELECT 'Brett'

CREATE INDEX myIndex99 ON myTable99(Col1)
CREATE INDEX myIndex00 ON myTable00(Col2)

SELECT COUNT(*) FROM myTable99

DECLARE @.x1 datetime, @.y1 datetime, @.x2 datetime, @.y2 datetime
SELECT @.x1 = GetDate()
SELECT @.x1 AS systime, 'Starting int look up'
SELECT * FROM myTable00 WHERE Col2 = 216784
SELECT @.y1 = GetDate()
SELECT @.y1 AS systime, 'Endinging int look up'

SELECT @.x2 = GetDate()
SELECT @.x2 AS systime, 'Starting sysname look up'
SELECT * FROM myTable00 WHERE Col1 = 'Brett'
SELECT @.y2 = GetDate()
SELECT @.y2 AS systime, 'Endinging int look up'

SELECT DATEDIFF(ms,@.x1, @.y1), DATEDIFF(ms,@.x2, @.y2)
GO

SET NOCOUNT OFF
DROP TABLE myTable99
DROP TABLE myTable00
GO|||For small tables with no updates, it doesn't matter much. If you start to update the char values you are using as keys, you'll lose hair very quickly. If you add rows (beyond about 100,000 or so), the numeric keys will be significantly faster, due to lower total physical IO.

The short answer boils down to you can use what you want. As you scale upward, the surrogate keys look better and better!

-PatP|||For small tables with no updates, it doesn't matter much. If you start to update the char values you are using as keys, you'll lose hair very quickly. If you add rows (beyond about 100,000 or so), the numeric keys will be significantly faster, due to lower total physical IO.

The short answer boils down to you can use what you want. As you scale upward, the surrogate keys look better and better!

-PatP
Yeah, I pretty much aggree with that. The tables I am refering to will not be that big and they are kind of complex so I thought it would be best to use as many natural keys as possible. Of course there are places in my DB where I use the identity because I don't think the natural keys are all that good to use.

How many people use CompanyName as a natural key in a companies table?

I understand there maybe more then one CompanyName in the table but should they be unique by appending a number or geographical location, or using the CompanyName / Address as the primary key.

My thought is that it is best to use a surrogate here because to carry a company name and address as a forein key to other tables is probably costly.

There is the argument that the address can change but is that really a problem since you can specify "Cascade update related fields" on the other tables?

I would personally love to see something other then a number when I am looking at these tables but....

Mike B|||At least in my opinion, company name stinks as a primary key. We aren't all that big, but we have several hundred companies scattered wildly about North America with the same name, and on a worldwide basis it gets even worse.

JOINs are cheap. SQL Server makes them nearly free IF you keep the FK value small (INT or smaller) and you've got enough RAM in your server.

-PatP|||JOINs are cheap.

That's gotta be the most open ended statement I've heard in a while...

Also... "Company's with the same name scattered around"?

Either they truly are a different company, which means they are separate legal entity, or they are a site for a company...

Are you essentially saying that accessing 1 table would be slower than accessing many?|||Many are franchises, some just reuse common names in different jurisdictions. The net result is that if you look for companies with names like Subway or McDonald's you find hundreds of hits, most (but not all) with separate EIN values.

If you have to haul a fifty byte VARCHAR off the disk versus a four byte integer for every row in a 34 million row table, versus a join to a table cached in RAM, then the JOIN is cheaper than the single table. While SQL Server is good at hiding physical details from the user, some things are still big enough tasks so that smart design beats brute force every time.

-PatP

primary key

Hi,

My database has a table. This table has a column ( named : No.) set to be a primary key, and it's ( identity). When several people access the database in the same time and add some data, some of them will see this error message : ( column (No.) doesn't allow repeated values) How can I solve this problem?

Thanks.

Hi,

My database has a table. This table has a column ( named : No.) set to be a primary key, and it's ( identity). When several people access the database in the same time and add some data, some of them will see this error message : ( column (No.) doesn't allow repeated values) How can I solve this problem?

Thanks a lot.

|||

Are you inserting explicit values for the identity column, or letting SQL Server autonumber them for you?

Thanks,

John

|||

SQL Server autonumber them for me.

one more easy question:

What's the kind of the network SQL server works with? Is it peer to peer ( workgroup ) or server (domain ) network?

thanks

|||

I am still unsure what is going on in your first problem, if you could provide a code sample or more information it would help a lot. As for your second question, what do you mean by what kind of network does it work with? Are you asking about windows authentication for SQL Server? The architecture is client server, not p2p but I am unsure why you have broken these two communication archetypes into workgroup/domain groupings so I am not sure how to answer that question.

Thanks,

John

|||The identity column is maintained by SQL Server only, until you specify the Identity Insert to ON, to insert values for yourself. My next step if I were you would be to sniff the commands that are executed against the database using SQL Server profiler. I guess that the error message either comes from anothert column having A UNIQUE constraint build upon it or someone uses the IDENTITY INSERT command to insert values manually.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
--

primary key

Hi,

My database has a table. This table has a column ( named : No.) set to be a primary key, and it's ( identity). When several people access the database in the same time and add some data, some of them will see this error message : ( column (No.) doesn't allow repeated values) How can I solve this problem?

Thanks.

Hi,

My database has a table. This table has a column ( named : No.) set to be a primary key, and it's ( identity). When several people access the database in the same time and add some data, some of them will see this error message : ( column (No.) doesn't allow repeated values) How can I solve this problem?

Thanks a lot.

|||

Are you inserting explicit values for the identity column, or letting SQL Server autonumber them for you?

Thanks,

John

|||

SQL Server autonumber them for me.

one more easy question:

What's the kind of the network SQL server works with? Is it peer to peer ( workgroup ) or server (domain ) network?

thanks

|||

I am still unsure what is going on in your first problem, if you could provide a code sample or more information it would help a lot. As for your second question, what do you mean by what kind of network does it work with? Are you asking about windows authentication for SQL Server? The architecture is client server, not p2p but I am unsure why you have broken these two communication archetypes into workgroup/domain groupings so I am not sure how to answer that question.

Thanks,

John

|||The identity column is maintained by SQL Server only, until you specify the Identity Insert to ON, to insert values for yourself. My next step if I were you would be to sniff the commands that are executed against the database using SQL Server profiler. I guess that the error message either comes from anothert column having A UNIQUE constraint build upon it or someone uses the IDENTITY INSERT command to insert values manually.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
--

primary key

Hi,

My database has a table. This table has a column ( named : No.) set to be a primary key, and it's ( identity). When several people access the database in the same time and add some data, some of them will see this error message : ( column (No.) doesn't allow repeated values) How can I solve this problem?

Thanks.

Hi,

My database has a table. This table has a column ( named : No.) set to be a primary key, and it's ( identity). When several people access the database in the same time and add some data, some of them will see this error message : ( column (No.) doesn't allow repeated values) How can I solve this problem?

Thanks a lot.

|||

Are you inserting explicit values for the identity column, or letting SQL Server autonumber them for you?

Thanks,

John

|||

SQL Server autonumber them for me.

one more easy question:

What's the kind of the network SQL server works with? Is it peer to peer ( workgroup ) or server (domain ) network?

thanks

|||

I am still unsure what is going on in your first problem, if you could provide a code sample or more information it would help a lot. As for your second question, what do you mean by what kind of network does it work with? Are you asking about windows authentication for SQL Server? The architecture is client server, not p2p but I am unsure why you have broken these two communication archetypes into workgroup/domain groupings so I am not sure how to answer that question.

Thanks,

John

|||The identity column is maintained by SQL Server only, until you specify the Identity Insert to ON, to insert values for yourself. My next step if I were you would be to sniff the commands that are executed against the database using SQL Server profiler. I guess that the error message either comes from anothert column having A UNIQUE constraint build upon it or someone uses the IDENTITY INSERT command to insert values manually.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
--