Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

newid() values

8 posts in Cluster Last posting was on 2012-02-22 18:56:43.0Z
Mark Posted on 2012-02-17 14:52:25.0Z
From: Mark <mlibner@yahoo.com>
Reply-To: mlibner@yahoo.com
Organization: Security Finance
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:9.0) Gecko/20111222 Thunderbird/9.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.cluster
Subject: newid() values
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f3e69a9$1@forums-1-dub>
Date: 17 Feb 2012 06:52:25 -0800
X-Trace: forums-1-dub 1329490345 10.22.241.152 (17 Feb 2012 06:52:25 -0800)
X-Original-Trace: 17 Feb 2012 06:52:25 -0800, vip152.sybase.com
Lines: 13
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.cluster:239
Article PK: 48514

If this is a stupid question I apologize but I have to ask, is the value
returned by newid() guaranteed to be unique regardless of the number of
servers I have in the cluster? Right now we only have one dedicated to
writing but that may not always be the case. Rob's book states the
values are unique among different servers but I wanted to ask pertaining
to ASE Cluster.

Adaptive Server Enterprise/15.5/EBF 19856 Cluster Edition ESD#4
ONE-OFF/P/x86_64/Enterprise Linux/asear155/2545/64-bit/FBO/Tue Jan 17
22:52:51 2012

Thanks,
Mark


J Posted on 2012-02-17 17:00:53.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.cluster
Subject: Re: newid() values
Reply-To: J@bogusemailAddress.com
Message-ID: <4f3e8337.1529793031@forums.sybase.com>
References: <4f3e69a9$1@forums-1-dub>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 17 Feb 2012 09:00:53 -0800
X-Trace: forums-1-dub 1329498053 10.22.241.152 (17 Feb 2012 09:00:53 -0800)
X-Original-Trace: 17 Feb 2012 09:00:53 -0800, vip152.sybase.com
Lines: 71
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.cluster:240
Article PK: 48518

On 17 Feb 2012 06:52:25 -0800, Mark <mlibner@yahoo.com> wrote:

Yes the values will be unique. There will be a range of id's
available to each instance in the server to prevent excessive locking
to obtain new values. When the range is exhausted then a new set of
values will be doled out to the instances. Of course this means that
there are gaps.

Here is a simple example on two instances, dtm1 and dtm2. I did an
identical insert from each instance.

1> create table idTable (instancename varchar(10), id numeric(8,0)
identity)
2> go
1> insert idTable values (@@instancename) /* performed identical
insert from instance, dtm1*/
2> go
(1 row affected)
1> select * from idTable
2> go
instancename id
------------ -----------
dtm2 1
dtm1 25001

(2 rows affected)

There are default sizes which can be overridden for specific objects:

1> sp_configure "identity"
2> go
Msg 17411, Level 16, State 1:
Server 'dtm', Procedure 'sp_configure', Line 725:
Configuration option is not unique.

Parameter Name Default Memory Used
Config Value Run Value Unit
Type Instance Name
------------------------------ -------------------- -----------
-------------------- --------------------
--------------------
-------------------- ------------------------------
identity burning set factor 5000 0
5000 5000 number
static cluster cluster-wide
identity grab size 1 0
1 1 number
dynamic cluster-wide
identity reservation size 1 0
1 1 number
dynamic cluster-wide
size of auto identity column 10 0
10 10 bytes
dynamic cluster-wide

(1 row affected)

>If this is a stupid question I apologize but I have to ask, is the value
>returned by newid() guaranteed to be unique regardless of the number of
>servers I have in the cluster? Right now we only have one dedicated to
>writing but that may not always be the case. Rob's book states the
>values are unique among different servers but I wanted to ask pertaining
>to ASE Cluster.
>
>Adaptive Server Enterprise/15.5/EBF 19856 Cluster Edition ESD#4
>ONE-OFF/P/x86_64/Enterprise Linux/asear155/2545/64-bit/FBO/Tue Jan 17
>22:52:51 2012
>
>Thanks,
>Mark


Mark Posted on 2012-02-17 19:09:31.0Z
From: Mark <mlibner@yahoo.com>
Reply-To: mlibner@yahoo.com
Organization: Security Finance
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:9.0) Gecko/20111222 Thunderbird/9.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.cluster
Subject: Re: newid() values
References: <4f3e69a9$1@forums-1-dub> <4f3e8337.1529793031@forums.sybase.com>
In-Reply-To: <4f3e8337.1529793031@forums.sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f3ea5eb@forums-1-dub>
Date: 17 Feb 2012 11:09:31 -0800
X-Trace: forums-1-dub 1329505771 10.22.241.152 (17 Feb 2012 11:09:31 -0800)
X-Original-Trace: 17 Feb 2012 11:09:31 -0800, vip152.sybase.com
Lines: 74
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.cluster:241
Article PK: 48520

So the newid() function works the same way as an identity column on a
table by caching a set of values on each server instance?

On 2/17/2012 12:00 PM, J wrote:
> On 17 Feb 2012 06:52:25 -0800, Mark<mlibner@yahoo.com> wrote:
>
> Yes the values will be unique. There will be a range of id's
> available to each instance in the server to prevent excessive locking
> to obtain new values. When the range is exhausted then a new set of
> values will be doled out to the instances. Of course this means that
> there are gaps.
>
> Here is a simple example on two instances, dtm1 and dtm2. I did an
> identical insert from each instance.
>
> 1> create table idTable (instancename varchar(10), id numeric(8,0)
> identity)
> 2> go
> 1> insert idTable values (@@instancename) /* performed identical
> insert from instance, dtm1*/
> 2> go
> (1 row affected)
> 1> select * from idTable
> 2> go
> instancename id
> ------------ -----------
> dtm2 1
> dtm1 25001
>
> (2 rows affected)
>
> There are default sizes which can be overridden for specific objects:
>
> 1> sp_configure "identity"
> 2> go
> Msg 17411, Level 16, State 1:
> Server 'dtm', Procedure 'sp_configure', Line 725:
> Configuration option is not unique.
>
> Parameter Name Default Memory Used
> Config Value Run Value Unit
> Type Instance Name
> ------------------------------ -------------------- -----------
> -------------------- --------------------
> --------------------
> -------------------- ------------------------------
> identity burning set factor 5000 0
> 5000 5000 number
> static cluster cluster-wide
> identity grab size 1 0
> 1 1 number
> dynamic cluster-wide
> identity reservation size 1 0
> 1 1 number
> dynamic cluster-wide
> size of auto identity column 10 0
> 10 10 bytes
> dynamic cluster-wide
>
> (1 row affected)
>
>> If this is a stupid question I apologize but I have to ask, is the value
>> returned by newid() guaranteed to be unique regardless of the number of
>> servers I have in the cluster? Right now we only have one dedicated to
>> writing but that may not always be the case. Rob's book states the
>> values are unique among different servers but I wanted to ask pertaining
>> to ASE Cluster.
>>
>> Adaptive Server Enterprise/15.5/EBF 19856 Cluster Edition ESD#4
>> ONE-OFF/P/x86_64/Enterprise Linux/asear155/2545/64-bit/FBO/Tue Jan 17
>> 22:52:51 2012
>>
>> Thanks,
>> Mark


Sherlock, Kevin [TeamSybase] Posted on 2012-02-17 19:19:20.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.cluster
References: <4f3e69a9$1@forums-1-dub> <4f3e8337.1529793031@forums.sybase.com> <4f3ea5eb@forums-1-dub>
Subject: Re: newid() values
Lines: 79
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f3ea838@forums-1-dub>
Date: 17 Feb 2012 11:19:20 -0800
X-Trace: forums-1-dub 1329506360 10.22.241.152 (17 Feb 2012 11:19:20 -0800)
X-Original-Trace: 17 Feb 2012 11:19:20 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.cluster:242
Article PK: 48519

No, he is confusing identity() with newid().

"Mark" <mlibner@yahoo.com> wrote in message news:4f3ea5eb@forums-1-dub...
> So the newid() function works the same way as an identity column on a
> table by caching a set of values on each server instance?
>
> On 2/17/2012 12:00 PM, J wrote:
>> On 17 Feb 2012 06:52:25 -0800, Mark<mlibner@yahoo.com> wrote:
>>
>> Yes the values will be unique. There will be a range of id's
>> available to each instance in the server to prevent excessive locking
>> to obtain new values. When the range is exhausted then a new set of
>> values will be doled out to the instances. Of course this means that
>> there are gaps.
>>
>> Here is a simple example on two instances, dtm1 and dtm2. I did an
>> identical insert from each instance.
>>
>> 1> create table idTable (instancename varchar(10), id numeric(8,0)
>> identity)
>> 2> go
>> 1> insert idTable values (@@instancename) /* performed identical
>> insert from instance, dtm1*/
>> 2> go
>> (1 row affected)
>> 1> select * from idTable
>> 2> go
>> instancename id
>> ------------ -----------
>> dtm2 1
>> dtm1 25001
>>
>> (2 rows affected)
>>
>> There are default sizes which can be overridden for specific objects:
>>
>> 1> sp_configure "identity"
>> 2> go
>> Msg 17411, Level 16, State 1:
>> Server 'dtm', Procedure 'sp_configure', Line 725:
>> Configuration option is not unique.
>>
>> Parameter Name Default Memory Used
>> Config Value Run Value Unit
>> Type Instance Name
>> ------------------------------ -------------------- -----------
>> -------------------- --------------------
>> --------------------
>> -------------------- ------------------------------
>> identity burning set factor 5000 0
>> 5000 5000 number
>> static cluster cluster-wide
>> identity grab size 1 0
>> 1 1 number
>> dynamic cluster-wide
>> identity reservation size 1 0
>> 1 1 number
>> dynamic cluster-wide
>> size of auto identity column 10 0
>> 10 10 bytes
>> dynamic cluster-wide
>>
>> (1 row affected)
>>
>>> If this is a stupid question I apologize but I have to ask, is the value
>>> returned by newid() guaranteed to be unique regardless of the number of
>>> servers I have in the cluster? Right now we only have one dedicated to
>>> writing but that may not always be the case. Rob's book states the
>>> values are unique among different servers but I wanted to ask pertaining
>>> to ASE Cluster.
>>>
>>> Adaptive Server Enterprise/15.5/EBF 19856 Cluster Edition ESD#4
>>> ONE-OFF/P/x86_64/Enterprise Linux/asear155/2545/64-bit/FBO/Tue Jan 17
>>> 22:52:51 2012
>>>
>>> Thanks,
>>> Mark


Mark Posted on 2012-02-20 16:27:37.0Z
From: Mark <mlibner@yahoo.com>
Reply-To: mlibner@yahoo.com
Organization: Security Finance
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:9.0) Gecko/20111222 Thunderbird/9.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.cluster
Subject: Re: newid() values
References: <4f3e69a9$1@forums-1-dub> <4f3e8337.1529793031@forums.sybase.com> <4f3ea5eb@forums-1-dub> <4f3ea838@forums-1-dub>
In-Reply-To: <4f3ea838@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f427479$1@forums-1-dub>
Date: 20 Feb 2012 08:27:37 -0800
X-Trace: forums-1-dub 1329755257 10.22.241.152 (20 Feb 2012 08:27:37 -0800)
X-Original-Trace: 20 Feb 2012 08:27:37 -0800, vip152.sybase.com
Lines: 86
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.cluster:243
Article PK: 48521

Hi Kevin,

Do you know the answer?

Thanks,
Mark

On 2/17/2012 2:19 PM, Sherlock, Kevin [TeamSybase] wrote:
> No, he is confusing identity() with newid().
>
> "Mark"<mlibner@yahoo.com> wrote in message news:4f3ea5eb@forums-1-dub...
>> So the newid() function works the same way as an identity column on a
>> table by caching a set of values on each server instance?
>>
>> On 2/17/2012 12:00 PM, J wrote:
>>> On 17 Feb 2012 06:52:25 -0800, Mark<mlibner@yahoo.com> wrote:
>>>
>>> Yes the values will be unique. There will be a range of id's
>>> available to each instance in the server to prevent excessive locking
>>> to obtain new values. When the range is exhausted then a new set of
>>> values will be doled out to the instances. Of course this means that
>>> there are gaps.
>>>
>>> Here is a simple example on two instances, dtm1 and dtm2. I did an
>>> identical insert from each instance.
>>>
>>> 1> create table idTable (instancename varchar(10), id numeric(8,0)
>>> identity)
>>> 2> go
>>> 1> insert idTable values (@@instancename) /* performed identical
>>> insert from instance, dtm1*/
>>> 2> go
>>> (1 row affected)
>>> 1> select * from idTable
>>> 2> go
>>> instancename id
>>> ------------ -----------
>>> dtm2 1
>>> dtm1 25001
>>>
>>> (2 rows affected)
>>>
>>> There are default sizes which can be overridden for specific objects:
>>>
>>> 1> sp_configure "identity"
>>> 2> go
>>> Msg 17411, Level 16, State 1:
>>> Server 'dtm', Procedure 'sp_configure', Line 725:
>>> Configuration option is not unique.
>>>
>>> Parameter Name Default Memory Used
>>> Config Value Run Value Unit
>>> Type Instance Name
>>> ------------------------------ -------------------- -----------
>>> -------------------- --------------------
>>> --------------------
>>> -------------------- ------------------------------
>>> identity burning set factor 5000 0
>>> 5000 5000 number
>>> static cluster cluster-wide
>>> identity grab size 1 0
>>> 1 1 number
>>> dynamic cluster-wide
>>> identity reservation size 1 0
>>> 1 1 number
>>> dynamic cluster-wide
>>> size of auto identity column 10 0
>>> 10 10 bytes
>>> dynamic cluster-wide
>>>
>>> (1 row affected)
>>>
>>>> If this is a stupid question I apologize but I have to ask, is the value
>>>> returned by newid() guaranteed to be unique regardless of the number of
>>>> servers I have in the cluster? Right now we only have one dedicated to
>>>> writing but that may not always be the case. Rob's book states the
>>>> values are unique among different servers but I wanted to ask pertaining
>>>> to ASE Cluster.
>>>>
>>>> Adaptive Server Enterprise/15.5/EBF 19856 Cluster Edition ESD#4
>>>> ONE-OFF/P/x86_64/Enterprise Linux/asear155/2545/64-bit/FBO/Tue Jan 17
>>>> 22:52:51 2012
>>>>
>>>> Thanks,
>>>> Mark
>


Sherlock, Kevin [TeamSybase] Posted on 2012-02-20 21:16:05.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.cluster
References: <4f3e69a9$1@forums-1-dub> <4f3e8337.1529793031@forums.sybase.com> <4f3ea5eb@forums-1-dub> <4f3ea838@forums-1-dub> <4f427479$1@forums-1-dub>
Subject: Re: newid() values
Lines: 100
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f42b815@forums-1-dub>
Date: 20 Feb 2012 13:16:05 -0800
X-Trace: forums-1-dub 1329772565 10.22.241.152 (20 Feb 2012 13:16:05 -0800)
X-Original-Trace: 20 Feb 2012 13:16:05 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.cluster:244
Article PK: 48522

Mark,
I can't say I have an authoritative answer. But the way I understand it,
newid() is universally unique. My guess is that this would apply even
inside a cluster (CE).

Better to ask this as a tech support case though directly to Sybase. Please
reply back here with their answer.

"Mark" <mlibner@yahoo.com> wrote in message news:4f427479$1@forums-1-dub...
> Hi Kevin,
>
> Do you know the answer?
>
> Thanks,
> Mark
>
> On 2/17/2012 2:19 PM, Sherlock, Kevin [TeamSybase] wrote:
>> No, he is confusing identity() with newid().
>>
>> "Mark"<mlibner@yahoo.com> wrote in message news:4f3ea5eb@forums-1-dub...
>>> So the newid() function works the same way as an identity column on a
>>> table by caching a set of values on each server instance?
>>>
>>> On 2/17/2012 12:00 PM, J wrote:
>>>> On 17 Feb 2012 06:52:25 -0800, Mark<mlibner@yahoo.com> wrote:
>>>>
>>>> Yes the values will be unique. There will be a range of id's
>>>> available to each instance in the server to prevent excessive locking
>>>> to obtain new values. When the range is exhausted then a new set of
>>>> values will be doled out to the instances. Of course this means that
>>>> there are gaps.
>>>>
>>>> Here is a simple example on two instances, dtm1 and dtm2. I did an
>>>> identical insert from each instance.
>>>>
>>>> 1> create table idTable (instancename varchar(10), id numeric(8,0)
>>>> identity)
>>>> 2> go
>>>> 1> insert idTable values (@@instancename) /* performed identical
>>>> insert from instance, dtm1*/
>>>> 2> go
>>>> (1 row affected)
>>>> 1> select * from idTable
>>>> 2> go
>>>> instancename id
>>>> ------------ -----------
>>>> dtm2 1
>>>> dtm1 25001
>>>>
>>>> (2 rows affected)
>>>>
>>>> There are default sizes which can be overridden for specific objects:
>>>>
>>>> 1> sp_configure "identity"
>>>> 2> go
>>>> Msg 17411, Level 16, State 1:
>>>> Server 'dtm', Procedure 'sp_configure', Line 725:
>>>> Configuration option is not unique.
>>>>
>>>> Parameter Name Default Memory Used
>>>> Config Value Run Value Unit
>>>> Type Instance Name
>>>> ------------------------------ -------------------- -----------
>>>> -------------------- --------------------
>>>> --------------------
>>>> -------------------- ------------------------------
>>>> identity burning set factor 5000 0
>>>> 5000 5000 number
>>>> static cluster cluster-wide
>>>> identity grab size 1 0
>>>> 1 1 number
>>>> dynamic cluster-wide
>>>> identity reservation size 1 0
>>>> 1 1 number
>>>> dynamic cluster-wide
>>>> size of auto identity column 10 0
>>>> 10 10 bytes
>>>> dynamic cluster-wide
>>>>
>>>> (1 row affected)
>>>>
>>>>> If this is a stupid question I apologize but I have to ask, is the
>>>>> value
>>>>> returned by newid() guaranteed to be unique regardless of the number
>>>>> of
>>>>> servers I have in the cluster? Right now we only have one dedicated to
>>>>> writing but that may not always be the case. Rob's book states the
>>>>> values are unique among different servers but I wanted to ask
>>>>> pertaining
>>>>> to ASE Cluster.
>>>>>
>>>>> Adaptive Server Enterprise/15.5/EBF 19856 Cluster Edition ESD#4
>>>>> ONE-OFF/P/x86_64/Enterprise Linux/asear155/2545/64-bit/FBO/Tue Jan 17
>>>>> 22:52:51 2012
>>>>>
>>>>> Thanks,
>>>>> Mark
>>


Mark Posted on 2012-02-22 18:56:43.0Z
From: Mark <mlibner@yahoo.com>
Reply-To: mlibner@yahoo.com
Organization: Security Finance
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:10.0.2) Gecko/20120216 Thunderbird/10.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.cluster
Subject: Re: newid() values
References: <4f3e69a9$1@forums-1-dub> <4f3e8337.1529793031@forums.sybase.com> <4f3ea5eb@forums-1-dub> <4f3ea838@forums-1-dub> <4f427479$1@forums-1-dub> <4f42b815@forums-1-dub>
In-Reply-To: <4f42b815@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f453a6b$1@forums-1-dub>
Date: 22 Feb 2012 10:56:43 -0800
X-Trace: forums-1-dub 1329937003 10.22.241.152 (22 Feb 2012 10:56:43 -0800)
X-Original-Trace: 22 Feb 2012 10:56:43 -0800, vip152.sybase.com
Lines: 102
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.cluster:246
Article PK: 48525

I reached out to Sybase rep and he confirmed that the values are indeed
unique across nodes.

On 2/20/2012 4:16 PM, Sherlock, Kevin [TeamSybase] wrote:
> Mark,
> I can't say I have an authoritative answer. But the way I understand it,
> newid() is universally unique. My guess is that this would apply even
> inside a cluster (CE).
>
> Better to ask this as a tech support case though directly to Sybase. Please
> reply back here with their answer.
>
> "Mark"<mlibner@yahoo.com> wrote in message news:4f427479$1@forums-1-dub...
>> Hi Kevin,
>>
>> Do you know the answer?
>>
>> Thanks,
>> Mark
>>
>> On 2/17/2012 2:19 PM, Sherlock, Kevin [TeamSybase] wrote:
>>> No, he is confusing identity() with newid().
>>>
>>> "Mark"<mlibner@yahoo.com> wrote in message news:4f3ea5eb@forums-1-dub...
>>>> So the newid() function works the same way as an identity column on a
>>>> table by caching a set of values on each server instance?
>>>>
>>>> On 2/17/2012 12:00 PM, J wrote:
>>>>> On 17 Feb 2012 06:52:25 -0800, Mark<mlibner@yahoo.com> wrote:
>>>>>
>>>>> Yes the values will be unique. There will be a range of id's
>>>>> available to each instance in the server to prevent excessive locking
>>>>> to obtain new values. When the range is exhausted then a new set of
>>>>> values will be doled out to the instances. Of course this means that
>>>>> there are gaps.
>>>>>
>>>>> Here is a simple example on two instances, dtm1 and dtm2. I did an
>>>>> identical insert from each instance.
>>>>>
>>>>> 1> create table idTable (instancename varchar(10), id numeric(8,0)
>>>>> identity)
>>>>> 2> go
>>>>> 1> insert idTable values (@@instancename) /* performed identical
>>>>> insert from instance, dtm1*/
>>>>> 2> go
>>>>> (1 row affected)
>>>>> 1> select * from idTable
>>>>> 2> go
>>>>> instancename id
>>>>> ------------ -----------
>>>>> dtm2 1
>>>>> dtm1 25001
>>>>>
>>>>> (2 rows affected)
>>>>>
>>>>> There are default sizes which can be overridden for specific objects:
>>>>>
>>>>> 1> sp_configure "identity"
>>>>> 2> go
>>>>> Msg 17411, Level 16, State 1:
>>>>> Server 'dtm', Procedure 'sp_configure', Line 725:
>>>>> Configuration option is not unique.
>>>>>
>>>>> Parameter Name Default Memory Used
>>>>> Config Value Run Value Unit
>>>>> Type Instance Name
>>>>> ------------------------------ -------------------- -----------
>>>>> -------------------- --------------------
>>>>> --------------------
>>>>> -------------------- ------------------------------
>>>>> identity burning set factor 5000 0
>>>>> 5000 5000 number
>>>>> static cluster cluster-wide
>>>>> identity grab size 1 0
>>>>> 1 1 number
>>>>> dynamic cluster-wide
>>>>> identity reservation size 1 0
>>>>> 1 1 number
>>>>> dynamic cluster-wide
>>>>> size of auto identity column 10 0
>>>>> 10 10 bytes
>>>>> dynamic cluster-wide
>>>>>
>>>>> (1 row affected)
>>>>>
>>>>>> If this is a stupid question I apologize but I have to ask, is the
>>>>>> value
>>>>>> returned by newid() guaranteed to be unique regardless of the number
>>>>>> of
>>>>>> servers I have in the cluster? Right now we only have one dedicated to
>>>>>> writing but that may not always be the case. Rob's book states the
>>>>>> values are unique among different servers but I wanted to ask
>>>>>> pertaining
>>>>>> to ASE Cluster.
>>>>>>
>>>>>> Adaptive Server Enterprise/15.5/EBF 19856 Cluster Edition ESD#4
>>>>>> ONE-OFF/P/x86_64/Enterprise Linux/asear155/2545/64-bit/FBO/Tue Jan 17
>>>>>> 22:52:51 2012
>>>>>>
>>>>>> Thanks,
>>>>>> Mark
>


J Posted on 2012-02-21 16:52:17.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.cluster
Subject: Re: newid() values
Reply-To: J@bogusemailAddress.com
Message-ID: <4f43cb9e.1876009812@forums.sybase.com>
References: <4f3e69a9$1@forums-1-dub> <4f3e8337.1529793031@forums.sybase.com> <4f3ea5eb@forums-1-dub> <4f3ea838@forums-1-dub>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 21 Feb 2012 08:52:17 -0800
X-Trace: forums-1-dub 1329843137 10.22.241.152 (21 Feb 2012 08:52:17 -0800)
X-Original-Trace: 21 Feb 2012 08:52:17 -0800, vip152.sybase.com
Lines: 87
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.cluster:245
Article PK: 48523

On 17 Feb 2012 11:19:20 -0800, "Sherlock, Kevin [TeamSybase]"
<kevin.sherlock@teamsybase.com> wrote:

I had a bad day reading this one. I will look a bit into newid().

Jay

>No, he is confusing identity() with newid().
>
>"Mark" <mlibner@yahoo.com> wrote in message news:4f3ea5eb@forums-1-dub...
>> So the newid() function works the same way as an identity column on a
>> table by caching a set of values on each server instance?
>>
>> On 2/17/2012 12:00 PM, J wrote:
>>> On 17 Feb 2012 06:52:25 -0800, Mark<mlibner@yahoo.com> wrote:
>>>
>>> Yes the values will be unique. There will be a range of id's
>>> available to each instance in the server to prevent excessive locking
>>> to obtain new values. When the range is exhausted then a new set of
>>> values will be doled out to the instances. Of course this means that
>>> there are gaps.
>>>
>>> Here is a simple example on two instances, dtm1 and dtm2. I did an
>>> identical insert from each instance.
>>>
>>> 1> create table idTable (instancename varchar(10), id numeric(8,0)
>>> identity)
>>> 2> go
>>> 1> insert idTable values (@@instancename) /* performed identical
>>> insert from instance, dtm1*/
>>> 2> go
>>> (1 row affected)
>>> 1> select * from idTable
>>> 2> go
>>> instancename id
>>> ------------ -----------
>>> dtm2 1
>>> dtm1 25001
>>>
>>> (2 rows affected)
>>>
>>> There are default sizes which can be overridden for specific objects:
>>>
>>> 1> sp_configure "identity"
>>> 2> go
>>> Msg 17411, Level 16, State 1:
>>> Server 'dtm', Procedure 'sp_configure', Line 725:
>>> Configuration option is not unique.
>>>
>>> Parameter Name Default Memory Used
>>> Config Value Run Value Unit
>>> Type Instance Name
>>> ------------------------------ -------------------- -----------
>>> -------------------- --------------------
>>> --------------------
>>> -------------------- ------------------------------
>>> identity burning set factor 5000 0
>>> 5000 5000 number
>>> static cluster cluster-wide
>>> identity grab size 1 0
>>> 1 1 number
>>> dynamic cluster-wide
>>> identity reservation size 1 0
>>> 1 1 number
>>> dynamic cluster-wide
>>> size of auto identity column 10 0
>>> 10 10 bytes
>>> dynamic cluster-wide
>>>
>>> (1 row affected)
>>>
>>>> If this is a stupid question I apologize but I have to ask, is the value
>>>> returned by newid() guaranteed to be unique regardless of the number of
>>>> servers I have in the cluster? Right now we only have one dedicated to
>>>> writing but that may not always be the case. Rob's book states the
>>>> values are unique among different servers but I wanted to ask pertaining
>>>> to ASE Cluster.
>>>>
>>>> Adaptive Server Enterprise/15.5/EBF 19856 Cluster Edition ESD#4
>>>> ONE-OFF/P/x86_64/Enterprise Linux/asear155/2545/64-bit/FBO/Tue Jan 17
>>>> 22:52:51 2012
>>>>
>>>> Thanks,
>>>> Mark
>
>