Please explain automatic identity range management

Hi,
MS SQL Server 2005 RTM. Could anyone please point me to a good source
describing how the automatic identity range management works for merge
repliaction or explain it? In particular, how the options of
Publisher/Subscriber identity range size in Managemen Studio work,
preferrably with examples. I use merge replication between Express and
Standart editions and non-overlapping partitions. Having read some
documentation on this subject I came to conclusions, which contradict the way
everything actually works. For example, if i have an empty table with an
identity column, and I define Publisher identity range size 20000 and
Subscriber identity range size 20000, I expect that the first 20k rows on
Publisher will have identities 1-20000, but the first subscriber will get
ranges 20001 - 40000 and 40001 - 60000. What I get is pretty much different
from that. Although no records were inserted into the table at the Publisher
the first Subscriber starts numbering at 160022, the second - at 80002 and so
on. What's going on?
-- Many thanks, Oskar
Basically there is a 100% buffer in the ranges to prevent the ranges from
being blown between syncs. So the first range is 20,001-40,000 (but with the
buffer it really is 20,001 to 60,000) and the second range is 80,0001 to
100,000 (but with the buffer it is 80,000 to 120,000). It further more seems
like there is a third subscriber somewhere where owns the range from 120,001
to 140,000 (but with the buffer it is 120,001 to 160,000), and then you are
looking at a 4th subscriber which is from 160,001 to 180,000 (but with the
buffer it is from 160,001 to 200,000).
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
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:BB90CCE0-55E0-4C39-9F8E-5E13DC4FFD2F@.microsoft.com...
> Hi,
> MS SQL Server 2005 RTM. Could anyone please point me to a good source
> describing how the automatic identity range management works for merge
> repliaction or explain it? In particular, how the options of
> Publisher/Subscriber identity range size in Managemen Studio work,
> preferrably with examples. I use merge replication between Express and
> Standart editions and non-overlapping partitions. Having read some
> documentation on this subject I came to conclusions, which contradict the
> way
> everything actually works. For example, if i have an empty table with an
> identity column, and I define Publisher identity range size 20000 and
> Subscriber identity range size 20000, I expect that the first 20k rows on
> Publisher will have identities 1-20000, but the first subscriber will get
> ranges 20001 - 40000 and 40001 - 60000. What I get is pretty much
> different
> from that. Although no records were inserted into the table at the
> Publisher
> the first Subscriber starts numbering at 160022, the second - at 80002 and
> so
> on. What's going on?
> -- Many thanks, Oskar
>
|||Thanks Hilary. In fact I found the answer to my question almost myself. Thing
is that MS SQL Server 2005 keeps track of every identity range it has ever
assigned to a subscriber at each reinitialization. Each time a subscriber is
initialized, new identity range is assigned to it and no longer can be used
by other subscribers or the same subscriber if it needs to be reinitialized.
So this explains, why identity ranges didn't start at values I expected.
-- Thanks, Oskar.
"Hilary Cotter" wrote:

> Basically there is a 100% buffer in the ranges to prevent the ranges from
> being blown between syncs. So the first range is 20,001-40,000 (but with the
> buffer it really is 20,001 to 60,000) and the second range is 80,0001 to
> 100,000 (but with the buffer it is 80,000 to 120,000). It further more seems
> like there is a third subscriber somewhere where owns the range from 120,001
> to 140,000 (but with the buffer it is 120,001 to 160,000), and then you are
> looking at a 4th subscriber which is from 160,001 to 180,000 (but with the
> buffer it is from 160,001 to 200,000).
> --
> 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
>
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:BB90CCE0-55E0-4C39-9F8E-5E13DC4FFD2F@.microsoft.com...
>
>

pk-fk problems

Hallo,

table A
x : int, increment, identity, index
relationship: fk_b_a has pk in A:X , fk in B:X
enforce for repl , insert i update-selected

Table B
x : int, index
relationship: fk_b_a has pk in A:X , fk in B:X
enforce for repl , insert i update-selected

relationship name in B is marked with infinity symbol.

Cant remove record from A, bacause
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_B_A'.
The conflict occurred in database 'xxx''.

Relevant record from B is removed properly.

CREATE procedure deleteAB(@.ser int,@.rv int output)
as
begin
delete from A where x=@.ser
delete from B where x=@.ser
set @.rv=0
end

what is wrong ?

Tia
pluton> delete from A where x=@.ser
> delete from B where x=@.ser

Its ok, order :)

pluton

pk-fk delete problens

Hallo,
table A
x : int, increment, identity, index
relationship: fk_b_a has pk in A:X , fk in B:X
enforce for repl , insert i update-selected
Table B
x : int, index
relationship: fk_b_a has pk in A:X , fk in B:X
enforce for repl , insert i update-selected
relationship name in B is marked with infinity symbol.
Cant remove record from A, bacause
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_B_A'.
The conflict occurred in database 'xxx''.
Relevant record from B is removed properly.
CREATE procedure deleteAB(@.ser int,@.rv int output)
as
begin
delete from A where x=@.ser
delete from B where x=@.ser
set @.rv=0
end
what is wrong ?
Tia
pluton> delete from A where x=@.ser
> delete from B where x=@.ser
Its ok, order
pluton

Pk to be initiated every time?

i want primary key of table to be inserted automatically. i've set its in desgin table as Identity = Yes; Seed=1

i want my application all other attributes except primary key which i've set atutomatically inserted.

but by doing this; it doesn't insert its value. instead it enters 0

can u plz help me in doing so?

Hi,

You are probably not doing things in the right way. It's a good idea to try ceating the table using a create table script and see if the problem persists or not:

CREATE TABLE TableName( PrimaryKeyColumnNameINT IDENTITY PRIMARY KEYNOT NULL, Column1 DataType, ...)

Happy SQLing!

Mehrdad

PK and Timestamp in same table?

Is there any reason to have a row that is the PK/Identity and a row
that is datatype Timestamp in the same table?

Does this in any way help speeding up row updates?

Thanks,
lqI think you mean columns :P

Timestamps should have nothing to do with speeding up updates (or any
query). They should also not be used in a PK/Identity combo. BOL has
a nice comment on that second notion:

"timestamp is used typically as a mechanism for version-stamping table
rows...."

"The value in the timestamp column is updated every time a row
containing a timestamp column is inserted or updated. This property
makes a timestamp column a poor candidate for keys, especially primary
keys. Any update made to the row changes the timestamp value, thereby
changing the key value. If the column is in a primary key, the old key
value is no longer valid, and foreign keys referencing the old value
are no longer valid. If the table is referenced in a dynamic cursor,
all updates change the position of the rows in the cursor. If the
column is in an index key, all updates to the data row also generate
updates of the index.
"|||Oops. Yes, I meant columns!

Thanks for that.

Smewhere I thought I read that having a TimeStamp column would speed up
UPDATE activities on rows.|||"laurenq uantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1128703331.710695.156120@.f14g2000cwb.googlegr oups.com...
> Is there any reason to have a row that is the PK/Identity and a row
> that is datatype Timestamp in the same table?
> Does this in any way help speeding up row updates?

Can't see that adding a timestamp field would speed anything up - in fact
the presence of a field that is auto-populated means that, by definition,
every time you INSERT or UPDATE the row, it'll have to do some work writing
the current date and time to the table.

A timestamp is, of course, useful as a "last modified date/time" indicator
for your applications, though.

D.|||laurenq uantrell wrote:
> Is there any reason to have a row that is the PK/Identity and a row
> that is datatype Timestamp in the same table?
> Does this in any way help speeding up row updates?
> Thanks,
> lq

Timestamp is useful for checking if a row has been updated by someone
else since you read it, so you don't need to check every other column
value and compare.

It's also a good idea if you use Access (and possibly other) front-end
if you have any floating point data types (even dates) as floating point
errors can cause the front end to think the row has been updated by
someone else even if it hasn't. (You've no doubt seen in CDMA,
timestamps recomended as cures for such problems).|||> A timestamp is, of course, useful as a "last modified date/time" indicator
> for your applications, though.

Laurenq referred to the timestamp *data type* in his post. The timestamp
data type is a misnomer because it is not related to date or time. The
system-generated timestamp is simply an 8 byte binary value that is
guaranteed to be unique within a database that is updated automatically
whenever any data in the row changes. Consequently, the primary purpose of
timestamp is for optimistic concurrency checks to see if the row was updated
by another user. For example:

UPDATE MyTable
SET
SomeColumn1 = @.SomeValue1,
SomeColumn2 = @.SomeValue2,
SomeColumn3 = @.SomeValue3
WHERE
MyPK = @.MyPK AND
MyTimestamp = @.OriginalMyTimestamp

IF @.@.ROWCOUNT = 0
BEGIN
RAISERROR ('Data was updated or deleted by another user', 16, 1)
END

--
Hope this helps.

Dan Guzman
SQL Server MVP

"David Cartwright" <dscartwright@.hotmail.com> wrote in message
news:di8842$pq7$1@.nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
> "laurenq uantrell" <laurenquantrell@.hotmail.com> wrote in message
> news:1128703331.710695.156120@.f14g2000cwb.googlegr oups.com...
>> Is there any reason to have a row that is the PK/Identity and a row
>> that is datatype Timestamp in the same table?
>> Does this in any way help speeding up row updates?
> Can't see that adding a timestamp field would speed anything up - in fact
> the presence of a field that is auto-populated means that, by definition,
> every time you INSERT or UPDATE the row, it'll have to do some work
> writing the current date and time to the table.
> A timestamp is, of course, useful as a "last modified date/time" indicator
> for your applications, though.
> D.|||The short answer is "No", it makes the table bigger and size will slow
down operations (probably not by much, but some).

The right answer is first, get the logical design right. An IDENTITY
cannot ever be a logical key, so is this thing actually a table at all?
What would the TIMESTAMP mean in your data model?|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> Oops. Yes, I meant columns!
> Thanks for that.
> Smewhere I thought I read that having a TimeStamp column would speed up
> UPDATE activities on rows.

As Trevor said - you don't have to check all columns to check for
concurrent updates, so the WHERE clauses of your updates are slightly
faster.

But as Celko pointed out, eight bytes more means bigger table, and degrades
performance.

I would say that timestamp is mainly a booster for development, as it
makes checks for concurrent updates easier to implement.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Pivotting Data

I have this table:

CREATE TABLE [dbo].[SHIP_HISTORY] (
[SHIPID] [int] IDENTITY (1, 1) NOT NULL ,
[PRODUCT] [nvarchar] (18) NOT NULL ,
[ORDERNUM] [char] (10) NOT NULL ,
[SHIP_DATE] [smalldatetime] NOT NULL ,
[WHSE] [nvarchar] (5) NOT NULL ,
[UNITS] [real] NOT NULL
) ON [PRIMARY]

It contains 4 years of sales history.

I need to pivot the data for a form in my front end (Access). The query was simple in Access, but too much data caused the form to be too slow.
I'm thinking I'll pivot the data in SQL first, and just link my front end to the new table.

I started to write a sproc to do this, and tested it before getting too far. For some reason, this is returning 166 rows, when I expect to seee only 4 (one for each year).
There is one row for each year that contains my totaled data, and the rest of the rows contain zeros. Any ideas what would be causing this?

Here's the SQL I'm using:

SELECT YEAR(sHIP_DATE) AS YEAR,
'1' = CASE WHEN DatePart(ww,[SHIP_DATE]) = 1 THEN SUM(UNITS)
ELSE 0
END,
'2' = CASE WHEN DatePart(ww,[SHIP_DATE]) = 2 THEN SUM(UNITS)
ELSE 0
END
FROM SHIP_HISTORY
GROUP BY Year([SHIP_DATE]), DatePart(ww,[SHIP_DATE])

If anyone has a better idea for how to do this, I'd welcome that, too!

ThanksYou are grouping by DatePart(ww,[SHIP_DATE]), and thus get rows for each of these values, even though you are not displaying DatePart(ww,[SHIP_DATE]) in your SELECT list.|||Try this:

SELECT YEAR(sHIP_DATE) AS YEAR,
SUM(CASE WHEN DatePart(ww,[SHIP_DATE]) = 1 THEN UNITS ELSE 0 END) AS '1',
SUM(CASE WHEN DatePart(ww,[SHIP_DATE]) = 2 THEN UNITS ELSE 0 END) AS '2'
FROM SHIP_HISTORY
GROUP BY Year([SHIP_DATE])|||[edit] posted before reading your second post|||Try this:

SELECT YEAR(sHIP_DATE) AS YEAR,
SUM(CASE WHEN DatePart(ww,[SHIP_DATE]) = 1 THEN UNITS ELSE 0 END) AS '1',
SUM(CASE WHEN DatePart(ww,[SHIP_DATE]) = 2 THEN UNITS ELSE 0 END) AS '2'
FROM SHIP_HISTORY
GROUP BY Year([SHIP_DATE])

That works perfectly.
Thanks, Blindman!