Identity column: primary

Showing posts with label primary. Show all posts

Friday, March 30, 2012

if i want to join two sql tables ...

do they have to have a common primary key?

Not necessarily. You can get a CROSS JOIN of both tables but I am not sure how much help that would be.

|||

I have these two tables

id int Unchecked
CategoryID int Checked
ListTitle nvarchar(50) Checked
Blurb nvarchar(250) Checked
FileName nvarchar(50) Checked
ByLine nvarchar(50) Checked
HTMLCopy nvarchar(MAX) Checked
MainStory bit Checked
MainStoryImageFile nvarchar(50) Checked
Publish bit Checked
Date smalldatetime Checked

and

CategoryID int Unchecked
CategoryTitle nvarchar(50) Checked

and i need to get to the categoryTitle along with stuff from the first table. any idea?

|||

What does 'Checked' and 'UnChecked' mean? Also post some sample data from each of the tables and expected output, makes it easier for me to work on some data.

|||

ooops. checked and unchecked was just the allow nulls check box ticked or not in my table definition. i need to get category name and stuff from the second table shown here. Is this what you mean.

1Making It2Spending It3Staying In4Going Out5Style6Driving7Sport8Travel9Mind GamesNULLNULL

28710 WORST TENNIS PLAYERSBlurb about the 10 worst tennis players goes in here.Bravo2.jpgThey Were Very GoodNo 10. Jimmy Connors 
he was not very good he was not very good he was not very good he was not very good he was not very good he was not very good he was not very good
No 9. Vitas Geralitis 
he was not very good he was not very good he was not very good he was not very good he was not very good he was not very good he was not very good he was not very good he was not very good
No 8. Bjorn Borg 
he was not very good he was not very good he was not very good he was not very good he was not very good he was not very good he was not very good he was not very good he was not very goodTrueO2.jpgTrue21/08/2007 13:43:0029710 BEST DEFENDERSBlurb about the 10 best defenders players goes in here.Yahoo.jpgCouldn't beat these guysmain list copy goes here.TrueBravo2.jpgTrue21/08/2007 17:00:0030710 BEST SQUASH PLAYERSBlurb about the 10 best squash players goes in here.Five.jpgGood raquet guysThis is were the main list copy goesTrueTWI.jpgTrue21/08/2007 17:01:0031410 FUNNIEST FILMSBlurb about the 10 funniest films goes in here.Bravo2.jpgThese were very funny filmsHere is the main copy for the funniest films here.TrueFive.jpgTrue21/08/2007 17:06:0032410 SCARIEST FILMSBlurb about the 10 scariest films goes in here.O2.jpgHold on to your seat.This is where the scary story copy goes.TrueBravo2.jpgTrue21/08/2007 17:07:0033410 BEST THEATRES IN LONDONBlurb about the 10 best theatres goes in here.BBC.jpgGreat places to see playsHere is the list copy for the theatresTrueYahoo.jpgTrue21/08/2007 17:10:0034710 BEST BADMINTON PLAYERSBlurb about the 10 best badminton players goes in here.Bravo2.jpgThese were great playersCOPY FOR THISTrueTWI.jpgTrue21/08/2007 17:12:0035510 BEST JEAN LABELBlurb about the 10 best jeans labels goes in here.Five.jpgJeans with the best fitCopy about the jeans goes in hereTrueYahoo.jpgTrue21/08/2007 22:29:0036310 BEST DVD'SBlurb about the 10 best dvd's goes in here.IMG World.jpgEnjoy these filmsworkTrueO2.jpgFalse21/08/2007 22:44:0037310 BEST BOARD GAMESBlurb about the 10 best board games goes in here.Yahoo.jpgHave playing these games at homeNo. 10 Chess 
This is the classic board game
No. 9 Backgammon 
Hope you are good with numbers.TrueYahoo.jpgTrue21/08/2007 22:49:0038710 GREATEST FOOTBALL TEAMSBlurb about the 10 greatest football teams goes in here.BBC.jpgThese teams created footballNo10. Westham United 
The greatest team of all time. The greatest team of all time. The greatest team of all time.The greatest team of all time.
No9. Arsenal 
Not a bad outfit now Henry has left. Not a bad outfit now Henry has left. Not a bad outfit now Henry has left. Not a bad outfit now Henry has left.
No 8. Liverpool 
Three times European Champions. Three times European Champions.Three times European Champions. Three times European Champions. Three times European Champions.TrueBravo2.jpgTrue21/08/2007 23:06:0039710 CLEVEREST RULES IN FOOTBALLBlurb about the 10 cleverest rules goes in here.Five.jpgThese are stupid rules<h3>No.10 Taken off for treatment</h3>
in football Fifa rules state that a player must leave the pitch if in need of treatment for an injury which can easily tempt a nervous team into ankle-stamping the opposition's best player minutes before the whistle. Those precious seconds waiting for the ref to bring him back on could prove vital. Those precious seconds waiting for the ref to bring him back on could prove vital. Those precious seconds waiting for the ref to bring him back on could prove vital. Those precious seconds waiting for the ref to him back on could prove vital.
<h3>No.9 No concessions for disabled people in golf</h3>
American Casey Martin was no world-class golfer, but in the late 90s he was doing reasonably well on the PGA's minor league Nike Tour. Martin had a birth defect in his leg attributed to Klippel Trenaunay Weber syndrome, which made walking painful. But the PGA wouldn't let him use a golf cart to get round. So he sued them.
<h3>No.8 No coaching mid-match in tennis</h3>
Think of every sport and you'll see a ranting manager or trainer somewhere on the sidelines. Except tennis, where players have to wait until they change ends for a hurried chat with the man who takes 15 per cent. Even toilet breaks are escorted, to ensure the rule isn't broken.
<h3>No.7 Not being allowed to over-celebrate in football</h3>
In a career that spanned over 500 matches, Alan Hansen scored just 14 goals. True, he was a defender, but had the day ever come that the dour Scot popped one in for his national team (0 goals from 26 games), he'd have gone absolutely bananas. And if today, probably booked.
<h3>No.6 Taken off for treatment</h3>
in football Fifa rules state that a player must leave the pitch if in need of treatment for an injury which can easily tempt a nervous team into ankle-stamping the opposition's best player minutes before the whistle. Those precious seconds waiting for the ref to bring him back on could prove vital. Those precious seconds waiting for the ref to bring him back on could prove vital. Those precious seconds waiting for the ref to bring him back on could prove vital. Those precious seconds waiting for the ref to him back on could prove vital.
<h3>No.5 No concessions for disabled people in golf</h3>
American Casey Martin was no world-class golfer, but in the late 90s he was doing reasonably well on the PGA's minor league Nike Tour. Martin had a birth defect in his leg attributed to Klippel Trenaunay Weber syndrome, which made walking painful. But the PGA wouldn't let him use a golf cart to get round. So he sued them.
<h3>No.4 No coaching mid-match in tennis</h3>
Think of every sport and you'll see a ranting manager or trainer somewhere on the sidelines. Except tennis, where players have to wait until they change ends for a hurried chat with the man who takes 15 per cent. Even toilet breaks are escorted, to ensure the rule isn't broken.
<h3>No.3 Not being allowed to over-celebrate in football</h3>
In a career that spanned over 500 matches, Alan Hansen scored just 14 goals. True, he was a defender, but had the day ever come that the dour Scot popped one in for his national team (0 goals from 26 games), he'd have gone absolutely bananas. And if today, probably booked.
<h3>No.2 Taken off for treatment</h3>
in football Fifa rules state that a player must leave the pitch if in need of treatment for an injury which can easily tempt a nervous team into ankle-stamping the opposition's best player minutes before the whistle. Those precious seconds waiting for the ref to bring him back on could prove vital. Those precious seconds waiting for the ref to bring him back on could prove vital. Those precious seconds waiting for the ref to bring him back on could prove vital. Those precious seconds waiting for the ref to him back on could prove vital.
<h3>No.1 No concessions for disabled people in golf</h3>
American Casey Martin was no world-class golfer, but in the late 90s he was doing reasonably well on the PGA's minor league Nike Tour. Martin had a birth defect in his leg attributed to Klippel Trenaunay Weber syndrome, which made walking painful. But the PGA wouldn't let him use a golf cart to get round. So he sued them.TrueBravo2.jpgTrue22/08/2007 10:13:0027410 BEST HOME MADE CAKESBlurb about the 10 best home made cakes goes in here.Yahoo.jpgxxTrueBravo2.jpgFalse21/08/2007 13:38:0016710 BEST FOOTBALLERSBlurb about the 10 best football players goes in here.BBC.jpgThese were the besthtml copy goes here html copy goes here html copy goes here html copy goes here html copy goes here html copy goes here html copy goes here html copy goes here html copy goes hereTrueFive.jpgTrue20/08/2007 17:44:0040710 BEST WORLD CUP GOALSBlurb about the 10 world cup goals goes in here.Bravo2.jpgThe best goals everNo 10. Pele 
he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it
No 9. Maradonna 
This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals
No 8. Zico 
Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal
No 7. Zico 
Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal
No 6. Pele 
he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it
No 5. Maradonna 
This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals
No 4. Pele 
he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it
No 3. Maradonna 
This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals
No 2. Pele 
he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it
No 1. Maradonna 
This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finalsTrueBravo2.jpgTrue22/08/2007 10:32:0041710 BEST FAST BOWLERSBlurb about the 10 best fast bowlers goes in here.O2.jpgPhil Hilton10. Sir Richard Hadlee: 
Small but perfectly formed, Sir Richard’s ‘tache was like many Kiwi attempts to keep up with the cricketing powerhouses, not bad but a little short of what is required for world class. 
Sir Richard 
 
9. Jack Russell: 
Eccentric wicket-keeper who could tell to within a matter of seconds how long his Weetabix had been soaked in milk for. Perhaps England’s greatest ever gloveman, now an artist, his moustache was as bedraggled as his trademark floppy hat. 
 
8. Clive Lloyd: 
A powerful batsman who, as captain from 1974 to 1985, was largely responsible for the West Indies’ extraordinary success. Also a star for Lancashire, the world’s greatest county cricket club. Great man, great ‘tache. 
 
7. Robin Smith: 
Nicknamed The Judge because of his hair, Smith combined his ‘tache with a mullet. The Judge did not wear a grill on his helmet, which meant the bowlers had a clear view of that top lip while he was hammering the fastest of bowling with supersonic cuts and hooks. He visibly enjoyed the regular snatches of chin music he received from the West Indian quicks. 
 
6. Kapil Dev: 
An Indian legend, Kapil Dev was a fine batsmen and the greatest pace bowler his country has ever produced. Competed in an era of great all-rounders, and like Botham and Hadlee he also had a decent ‘tache. 
 
5. Sir Ian Botham: 
The great man had to make an appearance. Not many men can look good with shoulder length, semi-permed hair and a moustache. But Beefy did, plus he managed to be the world’s greatest ever all-rounder at the same time. What a legend! 
 
4. Graham Gooch: 
Goochie ran the full gauntlet of facial hair during his career, from facial hair to full beard via designer stubble and his famous Zapata moustache. He was at his best though when sporting the ‘tache, as his 333 against India would testify. Interestingly, his 333 was boosted by hundreds from Allan Lamb and Robin Smith in a marvellous Test match for hairy-lips. 
 
3. David Boon: 
Tasmania’s all-time favourite cricketing son, Boonie would have held the Australian prize for the greatest cricketing ‘tache, but for the presence of the world beating Big Merv. Rumour has it that he was considered to be featured on the Aussue $5 note but they couldn’t fit his moustache on. 
 
2. Adam Hollioake: 
Perhaps a surprising pick for second place, Hollioake managed to do what other England captain’s have never, that is win an international ODI tournament when he led an inexperienced team to the Champions Trophy in Sharjah. His Aussie background maybe why he sported an incredible handlebar moustache for the start of the 2004 season. 
 
1. Merv Hughes: 
The clear winner by a hairy mile. According to Cricinfo, the big-hearted Australian fast bowler “was a lively character armed with an imposing run-up and delivery action, a classic fast bowlers’ glare down the pitch, a mischievous sense of humour and a moustache of incredible proportions”. Merv’s facial appendage is listed in Wikipedia as one of the all-time leading handlebar moustaches. Today’s “metrosexual” modern cricketers would do well to follow his lippy lead.TrueBravo2.jpgTrue22/08/2007 12:10:00

|||

I presume you're looking at the tables through Database Explorer - and that by Checked/Unchecked you are referring to the "Allow Nulls" column in the Table Editor. So describe your columns instead as "id int NOT NULL" or "CategoryID int NULL" and people will understand you.

To answer your question, you'll be doing an INNER JOIN on the tables by the CategoryID.

However, be careful about your first table's definition where all but the id column are nullable i.e. may be unknown. That immediately looks like poor design. Does every record in the first table have to belong to a category? Then the CategoryID field shouldn't be null in the first table, and you should define a foreign key relationship between it and the Category table (you can do this through point-and-click within the Explorer tool).

If you do so, then when you use the Query Editor in Database Explorer to graphically build your query, you'll see them joined together in exactly the way you want.

|||

Assuming your first table (with listtitle, filename etc) is called Category and second table with Title is called CategoryTitle you can write a query as:

SELECT C.id ,C.CategoryID ,C.ListTitle ,C.Blurb ,C.FileName ,C.ByLine ,C.HTMLCopy,C.MainStory ,C.MainStoryImageFile ,C.Publish ,C.Date ,C.TitleFROM Category CJOIN CategoryTitle CTon C.CategoryID = CT.CategoryID

|||

my first table is called shortlist and the second is called categories so can i write the below or do i have to put those Cs in??

SELECT id ,CategoryID ,ListTitle ,Blurb ,FileName ,ByLine ,HTMLCopy,MainStory ,MainStoryImageFile ,Publish ,Date ,CategoryTitleFROM shortlistJOIN CategoryTitleon shortlist.CategoryID = Categories.CategoryID

|||

Looking at the structure of your tables it looks like you want to select all the data for a specific category? The only value in the second table that is not in the first is CategoryTitle. So if you want to select all the data from table1 along with the CetegoryTitle Value from Table2... use something like this:

Select a.*, b.CategoryTitle from table1 a
inner join table2 b
on a.CategoryID = b.CategoryID
WHERE a.CategoryID = 'value'

I am assuming you are passing a CategoryID. Remember to replace the table1 and table2 with the table names and the value with the correct param(CategoryID). If this is not what you were trying to do, just send me what you would like to select and what value you are passing into query!

|||

Just saw that you posted your table names, so the query will look like this:

Select a.*, b.CategoryTitle from shortlist a
inner join categories b
on a.CategoryID = b.CategoryID

OR:

Select a.*, b.CategoryTitle from shortlist a
inner join categories b
on a.CategoryID = b.CategoryID
WHERE a.CategoryID = 'value'

If you want a specific category, hope this helps and good luck!

|||

SELECT id,S.CategoryID,ListTitle ,Blurb ,FileName ,ByLine ,HTMLCopy,MainStory ,MainStoryImageFile ,Publish ,Date ,CategoryTitleFROM shortlist SJOIN CategoryTitle Con S.CategoryID = C.CategoryID

When you have a column that is present in both tables, you will have to specify from which table you want the value retrieved from, otherwise SQL Server will complain

with an "Ambiguous column name CategoryId". So you need to put prefix the column name with the tablename or alias. If the column names are long its a lot of typing.

So we alias it with an acronym that is meaningful. Here I used S for Shortlist and C for CategoryTitle so you can prefix the CategoryId with either "S." or "C.".

|||Thank you very much. that worked. What if i want to make the CategoryTitle uppercase? :)|||

This will make the CategoryTitle upper case:

Select a.*, UPPER(b.CategoryTitle) from shortlist a
inner join categories b
on a.CategoryID = b.CategoryID

|||

I would do it at the front end, but there is an UPPER() function if you have to do it in SQL.

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

Wednesday, March 21, 2012

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 Values

Hello,
I have the Following Problem:
I have some tables on a SQL Server database that have primary keys
without the identity property.
This was necessary for importing data from old databases...
Now I want to change some primary key columns to be an Identity.
Of crourse the identity seed should be set to a value higher than the
highest existing value in the column.
I can do that easily with the Enterprise Manager, but how can I do that
with transact sql statements?
Regards FerdinandMake the changes in the table designer of EM but don't save them yet. Then
look on the toolbar for the button that is 3rd from the left. It will show
you how EM makes the changes.
Andrew J. Kelly SQL MVP
"Ferdinand Zaubzer" <ferdl@.gmx.at> wrote in message
news:uXnBWaeHGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have the Following Problem:
> I have some tables on a SQL Server database that have primary keys without
> the identity property.
> This was necessary for importing data from old databases...
> Now I want to change some primary key columns to be an Identity.
> Of crourse the identity seed should be set to a value higher than the
> highest existing value in the column.
> I can do that easily with the Enterprise Manager, but how can I do that
> with transact sql statements?
> Regards Ferdinand|||Why would you destroy a perfectly good primary key by replacing it with an
identity column?
I know there are different schools of thought on this one, but I have always
found a logical primary key based on the actual values in the table to be
far more intuitive than an identity field, which is little more than an
artificial row number in my book. Having keys based on real values makes
joining to other tables far easier, even if slightly more typing and storage
is used in the process.
From a programming and maintainability perspective, I would stick with the
original primary keys.
Of course, this is just my opinion. Some folks consider an identity field
to be a requirement on every table. I avoid using them as a general rule.
"Ferdinand Zaubzer" <ferdl@.gmx.at> wrote in message
news:uXnBWaeHGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have the Following Problem:
> I have some tables on a SQL Server database that have primary keys
> without the identity property.
> This was necessary for importing data from old databases...
> Now I want to change some primary key columns to be an Identity.
> Of crourse the identity seed should be set to a value higher than the
> highest existing value in the column.
> I can do that easily with the Enterprise Manager, but how can I do that
> with transact sql statements?
> Regards Ferdinand

Monday, March 19, 2012

Identity Ranges

I'm using Merge replication on a database that was designed using integer identity columns for primary keys. When I create a publisher it's great because Sql Server will create rowguid columns for me on most of the tables; actually all but one table.

The problem comes when I try and use identity ranges for the subscribers. As a test I set up a range on one table, it only allowed for 10 in the range with an 80% threshold. I wanted to see what would happen when say my publisher db inserts 11 new rows. Well, I found out that after the 8th new row it wouldn't insert anymore as the range was exceeded and it gave me an error message saying
The identity range managed by replication is full and must be updated by a replication agent.

The question is; which agent needs to run in order for the new range to be assigned to the publisher? I have seen some people talk about an sp_ that can be run, but in a production environment I wont this to be automatic.

My altenative to ranged identies is using guid uniqueidentifiers. See my other post on this!!

regards

GrahamI assume this is SQL Server 2000.

To answer your questions:
Maybe the table on which merge is not adding the rowguid col is because the table already has a rowguid column?

And since you have 80% threashold, it is failing after the 8th row and I assume you have pub_idrange and range values each set to 10.
You can get by this situation by increasing the numbers, so that the probablity of them running out of numbers is less. Say like 10,000 or 100,000.

And the agent to be run when the id range is full is the merge agent. Running the merge agent will refresh the ranges on publisher and subscriber.
On the publisher, you can also run the sp_adjustpublisheridentityrange to refresh the range and that way you dont need to run the merge agent.

But typically in a production scenario, it is recommended that:
1. You have a decent sized value for the publisher and subscriber id range values
2. Merge agent to run frequently. That way the ranges will be refreshed (if needed) when merge agent runs.

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.

Monday, March 12, 2012

Identity Primary Field in Merge Replication

Hello I currently have a merge replication set up with 4 subscribers. A primary field for one of my tables is set to a integer indetity.

What Ive noticed is that depending on which database I enter data into, the indentity field (primary key) is set within a certain range I.e

On Server 1 - Values start from 1 then 2,3,4 etc etc

Server 2 - 24001, 24002, 24003 etc etc

Server 3 - 46001, 46002, 46003 etc etc

Server 4 - 68001, 68002, 68003

My question is what happens when these ranges eventually conflict? Do they automatically gain a different range such as 142 001, 142 002 etc etc?

Ive tried looking in SQL Help, and a quick search here, Im just after some confirmation before I implement this to my app.

cheers

I wouldn;t advice you use only this for your primary key.

Check out this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1423523&SiteID=1

Identity Primary Field in Merge Replication

Hello I currently have a merge replication set up with 4 subscribers. A primary field for one of my tables is set to a integer indetity.

What Ive noticed is that depending on which database I enter data into, the indentity field (primary key) is set within a certain range I.e

On Server 1 - Values start from 1 then 2,3,4 etc etc

Server 2 - 24001, 24002, 24003 etc etc

Server 3 - 46001, 46002, 46003 etc etc

Server 4 - 68001, 68002, 68003

My question is what happens when these ranges eventually conflict? Do they automatically gain a different range such as 142 001, 142 002 etc etc?

Ive tried looking in SQL Help, and a quick search here, Im just after some confirmation before I implement this to my app.

cheers

I wouldn;t advice you use only this for your primary key.

Check out this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1423523&SiteID=1

Identity or GUID?

OK Guys,
We are considering replacing the standard IDENTITY column with the UNIQUEIDE
NTIFIER column for all our primary keys.
Good? Bad? Crazy? WTF!?
Any thoughts on index clustering, performance, portability, managability, et
c... would be appreciated.
RobertHi
http://www.sql-server-performance.c...red_indexes.asp
"rmg66" <rgwathney__xXx__primepro.com> wrote in message news:eZPZpnWiGHA.428
4@.TK2MSFTNGP05.phx.gbl...
OK Guys,
We are considering replacing the standard IDENTITY column with the UNIQUEIDE
NTIFIER column for all our primary keys.
Good? Bad? Crazy? WTF!?
Any thoughts on index clustering, performance, portability, managability, et
c... would be appreciated.
Robert|||From what I have read and understand Indexes which gets built on GUID wud be
pretty heavy and so it might have a performance hit.
http://www.thescripts.com/forum/thread82632.html -- this might help.
Best Regards
Vadivel
http://vadivel.blogspot.com
"rmg66" wrote:

> OK Guys,
> We are considering replacing the standard IDENTITY column with the UNIQUEI
DENTIFIER column for all our primary keys.
> Good? Bad? Crazy? WTF!?
> Any thoughts on index clustering, performance, portability, managability,
etc... would be appreciated.
> Robert
>|||Hi Robert,
Why? The only reason I can think is that you are moving more to a
distributed database architecture and you want to guarentee that the
surrogate key (its not really a primary key, the primary key is part of your
data) is unique across databases.
You can still use IDENTITY but encode a site ID into the schema.
NEWID() is random in its generation so the insert will be random across your
index so, you will cause additional IO because the data will be more spread
across the disk (array) so, you might end up with more locking contention
too.
In a word - don't do it.
Oh, also - its a lot harder to debug and 'see' guids when you are working
with the data under DBA mode ;).
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:eZPZpnWiGHA.4284@.TK2MSFTNGP05.phx.gbl...
OK Guys,
We are considering replacing the standard IDENTITY column with the
UNIQUEIDENTIFIER column for all our primary keys.
Good? Bad? Crazy? WTF!?
Any thoughts on index clustering, performance, portability, managability,
etc... would be appreciated.
Robert|||2005 will have a new function called:
newsequentialid
http://msdn2.microsoft.com/en-us/library/ms189786.aspx
for 2000
http://www.sqldev.net/xp/xpguid.htm
is an option to overcome the "randomness" of NEWID()
There is a performance hit for using NEWID() in 2000. I won't deny that.
However... .
The big advantage of using GUIDS is that I can
Create my Relationships OUTSIDE of tsql code, aka, (for me) inside DotNet
code.
Read my previous post at:
http://groups.google.com/group/micr...be27c58c993dab7
I don't know if there is a super correct answer.
It depends on what you got going on.
Personally, me and my company are making great strides to get the business
logic OUT OF THE Database, and into the business layer.
See
http://www.codeproject.com/gen/desi...sinessLogic.asp
for more info
Most times, I'm going with some kind of GUID usage, but not the NEWID stuff.
If replication is in your plans, then you need to seriously consider
abandoning IDENTITY's.
But you should research and judge for yourself.
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:%23ih$yCXiGHA.3904@.TK2MSFTNGP02.phx.gbl...
> Hi Robert,
> Why? The only reason I can think is that you are moving more to a
> distributed database architecture and you want to guarentee that the
> surrogate key (its not really a primary key, the primary key is part of
your
> data) is unique across databases.
> You can still use IDENTITY but encode a site ID into the schema.
> NEWID() is random in its generation so the insert will be random across
your
> index so, you will cause additional IO because the data will be more
spread
> across the disk (array) so, you might end up with more locking contention
> too.
> In a word - don't do it.
> Oh, also - its a lot harder to debug and 'see' guids when you are working

> with the data under DBA mode ;).
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "rmg66" <rgwathney__xXx__primepro.com> wrote in message
> news:eZPZpnWiGHA.4284@.TK2MSFTNGP05.phx.gbl...
> OK Guys,
> We are considering replacing the standard IDENTITY column with the
> UNIQUEIDENTIFIER column for all our primary keys.
> Good? Bad? Crazy? WTF!?
> Any thoughts on index clustering, performance, portability, managability,
> etc... would be appreciated.
> Robert
>
>|||My organization is in the midst of trying to replace our UNIQUEIDENTIFIER
clusetered primary keys with IDENTITY fields. Two reasons: 1) making the
clustered index a UNIQUEIDENTIFIER field increases the size of all the
nonclustered indexes; and 2) UNIQUEIDENTIFIER fields generated with the
NEWID() function are not sequential, so your joins will be much less
efficient.
Oh, and I forgot the last one: changing back is a pain!
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:eZPZpnWiGHA.4284@.TK2MSFTNGP05.phx.gbl...
OK Guys,
We are considering replacing the standard IDENTITY column with the
UNIQUEIDENTIFIER column for all our primary keys.
Good? Bad? Crazy? WTF!?
Any thoughts on index clustering, performance, portability, managability,
etc... would be appreciated.
Robert|||> I don't know if there is a super correct answer.
> It depends on what you got going on.
> Personally, me and my company are making great strides to get the business
> logic OUT OF THE Database, and into the business layer.
>
You've missed the boat Sloan, the current thinking is to put the business
logic back into the database because its centralised and easier to manage -
plus you get better resource usage through cached execution code etc...
Google Jim Gray and look up some of his thinking on this.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"sloan" <sloan@.ipass.net> wrote in message
news:%23UU$cfXiGHA.3408@.TK2MSFTNGP05.phx.gbl...
> 2005 will have a new function called:
> newsequentialid
> http://msdn2.microsoft.com/en-us/library/ms189786.aspx
> for 2000
> http://www.sqldev.net/xp/xpguid.htm
> is an option to overcome the "randomness" of NEWID()
>
> There is a performance hit for using NEWID() in 2000. I won't deny that.
> However... .
> The big advantage of using GUIDS is that I can
> Create my Relationships OUTSIDE of tsql code, aka, (for me) inside DotNet
> code.
>
> Read my previous post at:
> http://groups.google.com/group/micr...be27c58c993dab7
>
> I don't know if there is a super correct answer.
> It depends on what you got going on.
> Personally, me and my company are making great strides to get the business
> logic OUT OF THE Database, and into the business layer.
> See
> http://www.codeproject.com/gen/desi...sinessLogic.asp
> for more info
>
> Most times, I'm going with some kind of GUID usage, but not the NEWID
> stuff.
> If replication is in your plans, then you need to seriously consider
> abandoning IDENTITY's.
> But you should research and judge for yourself.
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:%23ih$yCXiGHA.3904@.TK2MSFTNGP02.phx.gbl...
> your
> your
> spread
>
> SQL
>|||Why are you considering this? What gains do you expect, or why do you
think you need GUIDs? Aren't Identities working for you?
Have you seen the presentation of Kimberly Tripp on Index Optimization?
(see
http://www.microsoft.com/uk/technet...aspx?videoid=29)
In a fragment of it, she discusses the use of GUIDs as the clustered
index key, and how this use can cause massive fragmentation and (as a
result) abysmal performance.
BTW: there are different opinions about the policy to use an Identity
(or other surrogate key) as the Primary Key for each table. My personal
opinion is, that such a key should never be the first choice. So IMO you
should not have such a policy. One should always try to find a natural
key, and only choose a surrogate key if no useful natural key is found,
or for performance reasons (which means the natural key would still be
an alternate key, enforced with a Unique constraint).
HTH,
Gert-Jan

> rmg66 wrote:
> OK Guys,
> We are considering replacing the standard IDENTITY column with the
> UNIQUEIDENTIFIER column for all our primary keys.
> Good? Bad? Crazy? WTF!?
> Any thoughts on index clustering, performance, portability,
> managability, etc... would be appreciated.
> Robert
>|||Not trying to pick a fight, Tony, but you're one of the few that I've
seen advocate this. I haven't read any of Jim Gray's stuff, but I'll
take a look at it. It seems odd to me to put business logic back into
the database because of the issues of object-relational impedance,
scalability, and general performance considerations.
I prefer clean seperation, myself; I'm even beginning tto question the
need for stored procs because of the failure to seperate business logic
from data retrieval.
Stu
Tony Rogerson wrote:
> You've missed the boat Sloan, the current thinking is to put the business
> logic back into the database because its centralised and easier to manage
-
> plus you get better resource usage through cached execution code etc...
> Google Jim Gray and look up some of his thinking on this.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a S
QL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "sloan" <sloan@.ipass.net> wrote in message
> news:%23UU$cfXiGHA.3408@.TK2MSFTNGP05.phx.gbl...|||Well, that's great if you're married to Sql Server.
I love Sql Server, don't get me wrong, its my bread and butter.
But you can't guarantee you'll always be in a Sql Server world.
And I don't think I've "missed the boat".
My DataLayer objects return:
IDataReader's
DataSets (typed and untyped)
XmlDocuments
Scalars
voids (or nothings... as in, just make sure what I called worked)
Because I have a good DataLayer, I can switch out the backend database at
any given moment.
Yeah, there will be some issues, but not as drastic as complicated business
logic in my tsql.
The database is usually the bottleneck of any well designed system.
And the quicker I get in and get out, the better.
I'll take a look at Jim Gray's stuff. (is this the same Jim Gray who does
interviews for espn/nba?)
That's fine to say "There are other options out there, which have BL in the
database"
But "you missed the boat", ... thats a little strong for for advocating
another opinion in favor of what I and alot of others have proposed.
Perhaps the experience of having my company merge with another company, with
diffrent RDBMS systems has influenced me somewhat.
I'll stick with the good DataLayer design for now.
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:OHgQiOaiGHA.4284@.TK2MSFTNGP05.phx.gbl...
business
> You've missed the boat Sloan, the current thinking is to put the business
> logic back into the database because its centralised and easier to
manage -
> plus you get better resource usage through cached execution code etc...
> Google Jim Gray and look up some of his thinking on this.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "sloan" <sloan@.ipass.net> wrote in message
> news:%23UU$cfXiGHA.3408@.TK2MSFTNGP05.phx.gbl...
that.
DotNet
http://groups.google.com/group/micr...993dab7

business
contention
working
a
managability,
>

IDENTITY limit

I defined a primary field using integer 4 bytes as IDENTITY.
What will be the limit of this number?
I am not sure the number is big enough in 10 years of time after my
application have been running."Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>I defined a primary field using integer 4 bytes as IDENTITY.
> What will be the limit of this number?
> I am not sure the number is big enough in 10 years of time after my
> application have been running.
>
2^31-1 (2,147,483,647)
Keep in mind that all numbers are used, even if not allocated.
So if someone begins a transaction, inserts 1000 rows and rolls it back,
then 1000 IDENTITY values are "used" up.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||2 billion. If that's not enough, you could start the seed at -2 billion and
double the capacity that way. If that's still not enough, you could use
BIGINT (and start at a negative number here, too), and if your app is going
to find a way to exceed that limit, then you should probably consider not
using an integer at all.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>I defined a primary field using integer 4 bytes as IDENTITY.
> What will be the limit of this number?
> I am not sure the number is big enough in 10 years of time after my
> application have been running.
>|||Hello,
To add to Greg, the limitation wil be based on the usage. Incase if you feel
the INT is not big enough then go ahead and use the BIGINT data type
which will allow 2^63-1 (9,223,372,036,854,775,807) and the storage will be
8 bytes.
Thanks
Hari
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:ufoLBFdZHHA.2316@.TK2MSFTNGP04.phx.gbl...
> "Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>>I defined a primary field using integer 4 bytes as IDENTITY.
>> What will be the limit of this number?
>> I am not sure the number is big enough in 10 years of time after my
>> application have been running.
> 2^31-1 (2,147,483,647)
> Keep in mind that all numbers are used, even if not allocated.
> So if someone begins a transaction, inserts 1000 rows and rolls it back,
> then 1000 IDENTITY values are "used" up.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
>

IDENTITY limit

"Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>I defined a primary field using integer 4 bytes as IDENTITY.
> What will be the limit of this number?
> I am not sure the number is big enough in 10 years of time after my
> application have been running.
>
2^31-1 (2,147,483,647)
Keep in mind that all numbers are used, even if not allocated.
So if someone begins a transaction, inserts 1000 rows and rolls it back,
then 1000 IDENTITY values are "used" up.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
Hello,
To add to Greg, the limitation wil be based on the usage. Incase if you feel
the INT is not big enough then go ahead and use the BIGINT data type
which will allow 2^63-1 (9,223,372,036,854,775,807) and the storage will be
8 bytes.
Thanks
Hari
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:ufoLBFdZHHA.2316@.TK2MSFTNGP04.phx.gbl...
> "Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
> 2^31-1 (2,147,483,647)
> Keep in mind that all numbers are used, even if not allocated.
> So if someone begins a transaction, inserts 1000 rows and rolls it back,
> then 1000 IDENTITY values are "used" up.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
>

IDENTITY limit

I defined a primary field using integer 4 bytes as IDENTITY.
What will be the limit of this number?
I am not sure the number is big enough in 10 years of time after my
application have been running."Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>I defined a primary field using integer 4 bytes as IDENTITY.
> What will be the limit of this number?
> I am not sure the number is big enough in 10 years of time after my
> application have been running.
>
2^31-1 (2,147,483,647)
Keep in mind that all numbers are used, even if not allocated.
So if someone begins a transaction, inserts 1000 rows and rolls it back,
then 1000 IDENTITY values are "used" up.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||2 billion. If that's not enough, you could start the seed at -2 billion and
double the capacity that way. If that's still not enough, you could use
BIGINT (and start at a negative number here, too), and if your app is going
to find a way to exceed that limit, then you should probably consider not
using an integer at all.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>I defined a primary field using integer 4 bytes as IDENTITY.
> What will be the limit of this number?
> I am not sure the number is big enough in 10 years of time after my
> application have been running.
>|||Hello,
To add to Greg, the limitation wil be based on the usage. Incase if you feel
the INT is not big enough then go ahead and use the BIGINT data type
which will allow 2^63-1 (9,223,372,036,854,775,807) and the storage will be
8 bytes.
Thanks
Hari
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:ufoLBFdZHHA.2316@.TK2MSFTNGP04.phx.gbl...
> "Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
> 2^31-1 (2,147,483,647)
> Keep in mind that all numbers are used, even if not allocated.
> So if someone begins a transaction, inserts 1000 rows and rolls it back,
> then 1000 IDENTITY values are "used" up.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
>

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.

Friday, March 9, 2012

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

Wednesday, March 7, 2012

Identity Columns - Design Question

Is it a good practice to use identty columns as primary keys? or should
we generate our own keys using a seed table?
Does using Identity give any performance advantages?
Thank you,Hi

> Is it a good practice to use identty columns as primary keys? or should
> we generate our own keys using a seed table?
If you don't care about gaps you can use an IDENTITY propertry as a
PFRIMARY KEY , just thinking about your business requirements which I don't
know , actuallty there was lots of discussions about this in this newsgpoup

> Does using Identity give any performance advantages?
Yes, it is , especially if it has a CLUSTERED INDEX .
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1149591940.115554.74460@.y43g2000cwc.googlegroups.com...
>
> Is it a good practice to use identty columns as primary keys? or should
> we generate our own keys using a seed table?
> Does using Identity give any performance advantages?
> Thank you,
>|||There is a slight performance gain on large tables.
But I don't use them anymore due to re-design issues on upgrades and third
party ware and a number of other integrations.
"Uri Dimant" wrote:

> Hi
>
> If you don't care about gaps you can use an IDENTITY propertry as a
> PFRIMARY KEY , just thinking about your business requirements which I don
't
> know , actuallty there was lots of discussions about this in this newsgpo
up
>
>
> Yes, it is , especially if it has a CLUSTERED INDEX .
>
>
> "S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
> news:1149591940.115554.74460@.y43g2000cwc.googlegroups.com...
>
>|||This is an altered version of an older response on the same subject, with
emphasis on fundamentals:
A key is an attribute or set of attributes that can uniquely identify an
entity in the conceptual model. At the corresponding logical level, a column
or set of columns that can uniquely identify a row in a table is defined as
a key. In reality, an entity may have more than one candidate that provides
such identification ( hence are called candidate keys ), however due to
obvious reasons one of those candidate keys should be treated as primal (
hence called primary key ) based on practical considerations. Such
considerations should include:
i. Familiarity - meaningful to the user.
ii. Stability - non-volatile, should not be altered frequently.
iii. Simplicity - so that queries are easy to express and optimize.
iv. Irreducibility - no proper subset of the key should uniquely identify a
row in the table. ( some treat this also as a derivation to 2NF, which
prohibits partial key dependencies )
Having stated the above guidelines, regardless of the complexity in your
business model & data requirements, an intelligent database design can find
a middle ground by trading off certain characteristics without compromising
the integrity of the data. And one such compromise is the logical surrogate
key which usually defies the characteristic of familiarity but provides
excellent stability and simplicity. Simple and stable keys are often
recommended over composite/volatile keys esp. when they are used in
referential integrity constraints.
http://www.datraverse.com/technology/sql.php
One of the fundamental principles guiding relational design, is a concept
called physical data independence, which can be simply put as: the users
must be presented with a logical view of the data & need not deal with the
physical implementation and storage details. That is primarily why we deal
with tables, columns, keys etc ( which is logical) instead of files, disk
indices, row positions etc (which is physical). Thus you will see why and
how a surrogate in a clean logical model, should also conform to physical
data independence.
Features like identity, GUID etc provide uniqueness, but there are questions
regarding whether they provide sufficient data independence esp. with
ordering of identity column values at the time of generation. Some aspects
of this issue were addressed here:
http://blogs.msdn.com/sqltips/default.aspx?p=2
The non-updateability of identity columns arguably invalidates relational
assignments and therefore the information principle. Since the internal
mechanism that generates identity values are mostly unknown to the user,
practical data design and quality guidelines involving data verification and
validation cannot be applied to such DBMS generated values either.
At the logical level, performance should be least of your considerations in
key selection, since the primary goal of keys is entity identification that
preserves data integrity. Performance is purely dependent upon the physical
implementation of a database. Obviously values generated and tied closely
and directly to the physical model tend to be better performing. Also values
that are smaller in physical size can improve certain query performance due
to faster disk access i/o etc. Given that identity values are of numeric
datatypes and often smaller in physical size, queries utilizing identity
columns tend to have the same performance benefits.
Anith|||I have read ("i have never tried it out myself") -- Identity values cannot
be used in merge replication.
Best Regards
Vadivel
http://vadivel.blogspot.com
"S Chapman" wrote:

>
> Is it a good practice to use identty columns as primary keys? or should
> we generate our own keys using a seed table?
> Does using Identity give any performance advantages?
> Thank you,
>|||If you have no natural primary key then using the IDENTITY property is
significantly better than roling your own because you won't get the same
contention (blocking and serialisation) you get with other roled methods.
IDENTITY does give a performance advantage - there is no locking to get the
next number.
The one di

vantage is that you can get gaps because the calculation for
next number is not transaction aware but that doesn't matter in most cases
i've seen in business.
You really need to PRIMARY KEY using your natural key and then use a
surrogate key (the IDENTIITY) everywhere else - in sps, joins etc... the
primary key is then just meta data.
eg.
create table sector (
sector_name nvarchar(100) not null constraint pk_sector primary
key nonclustered,
id int not null constraint sk_sector unique clustered
)
create table sector_coverage (
sector_id int not null references sector( id ),
individual_id int not null references individual( id )
)
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1149591940.115554.74460@.y43g2000cwc.googlegroups.com...
>
> Is it a good practice to use identty columns as primary keys? or should
> we generate our own keys using a seed table?
> Does using Identity give any performance advantages?
> Thank you,
>|||Agreed Tony
"Tony Rogerson" wrote:

> If you have no natural primary key then using the IDENTITY property is
> significantly better than roling your own because you won't get the same
> contention (blocking and serialisation) you get with other roled methods.
> IDENTITY does give a performance advantage - there is no locking to get th
e
> next number.
> The one di

vantage is that you can get gaps because the calculation for
> next number is not transaction aware but that doesn't matter in most cases
> i've seen in business.
> You really need to PRIMARY KEY using your natural key and then use a
> surrogate key (the IDENTIITY) everywhere else - in sps, joins etc... the
> primary key is then just meta data.
> eg.
> create table sector (
> sector_name nvarchar(100) not null constraint pk_sector primary
> key nonclustered,
> id int not null constraint sk_sector unique clustered
> )
> create table sector_coverage (
> sector_id int not null references sector( id ),
> individual_id int not null references individual( id )
> )
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a S
QL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
> news:1149591940.115554.74460@.y43g2000cwc.googlegroups.com...
>
>

Friday, March 30, 2012

if i want to join two sql tables ...

If Exists Statement Problem...

Wednesday, March 21, 2012

Identity_Insert OFF

identity, plus pk?

Identity Values

Monday, March 19, 2012

Identity Ranges

identity range management

Identity Range Management

Identity question

Monday, March 12, 2012

Identity Primary Field in Merge Replication

Identity Primary Field in Merge Replication

Identity or GUID?

IDENTITY limit

IDENTITY limit

IDENTITY limit

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

Friday, March 9, 2012

Identity Error when replicating

Identity columns problem in replication among n no of subscribers

Wednesday, March 7, 2012

Identity Columns - Design Question

Identity column

Blog Archive

About Me