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.

Allow for INT datatypes as IDENTITY

13 posts in Product Futures Discussion Last posting was on 2002-11-13 10:11:17.0Z
Matt Rogish Posted on 2002-10-02 23:54:07.0Z
From: "Matt Rogish" <matt@fanhome.com>
Subject: Allow for INT datatypes as IDENTITY
Date: Wed, 2 Oct 2002 19:54:07 -0400
Lines: 12
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
Message-ID: <jp2ZS5maCHA.197@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: woh-166-196-2.woh.rr.com 24.166.196.2
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:286
Article PK: 93455

What are the reasons for forcing IDENTITY-marked columns to be NUMERIC? I
hypothesize that it has something to do with ident burning (INT on 32-bit
different size than 64 bit?) such that it is easier to code, but there end
up being problems with datatype mismatches NUMERIC to INT for example. It
would be nice to allow INTs to be IDENTITY (also make porting easier).

Thanks,

--
Matt


Rob Verschoor Posted on 2002-10-03 08:26:08.0Z
Reply-To: "Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
References: <jp2ZS5maCHA.197@forums.sybase.com>
Subject: Re: Allow for INT datatypes as IDENTITY
Date: Thu, 3 Oct 2002 10:26:08 +0200
Lines: 45
Organization: Sypron B.V.
MIME-Version: 1.0
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
Message-ID: <lF1RSjraCHA.251@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: a221111.upc-a.chello.nl 62.163.221.111
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:285
Article PK: 93454


"Matt Rogish" <matt@fanhome.com> wrote in message
news:jp2ZS5maCHA.197@forums.sybase.com...
> What are the reasons for forcing IDENTITY-marked columns to be NUMERIC?
I
> hypothesize that it has something to do with ident burning (INT on 32-bit
> different size than 64 bit?) such that it is easier to code, but there
end
> up being problems with datatype mismatches NUMERIC to INT for example.
It
> would be nice to allow INTs to be IDENTITY (also make porting easier).
>
> Thanks,
>
> --
> Matt

I think (but I'm not 100% sure) the original reason for making identity
columns 'numeric' is because of the wider range of the 'numeric' datatype.
Combined with the risk of identity gaps, an 'int' could reach its end very
soon when it's used as an identity column.
Since 12.0, you can use the identity_gap setting to limit the size of
potential gaps, so the original argument might not apply anymore. But I
have no problem with identity columns being numeric, except one: the
'modulo' operator is not allowed for 'numeric' datatypes, which can be a
problem fro writing application code.
Instead of asking for 'int' for an identity column, my request would be to
allow 'modulo' on 'numeric' instead (in fact, I have issued ISUG
enhancement request e01_056 for this).

HTH,

Rob
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0

Author of "The Complete Sybase ASE Quick Reference Guide"
Online orders accepted at http://www.sypron.nl/qr

mailto:rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------


Carl Kayser Posted on 2002-10-03 11:56:27.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
References: <jp2ZS5maCHA.197@forums.sybase.com> <lF1RSjraCHA.251@forums.sybase.com>
Subject: Re: Allow for INT datatypes as IDENTITY
Date: Thu, 3 Oct 2002 07:56:27 -0400
Lines: 56
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4133.2400
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400
Message-ID: <2uUtyXtaCHA.313@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 146.142.35.25
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:284
Article PK: 93452

Perhaps having a new 8-byte integer datatype would cleanly allow for an
identity column that allows for many-row tables, identity gaps, and modular
division?

"Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote in
message news:lF1RSjraCHA.251@forums.sybase.com...
> "Matt Rogish" <matt@fanhome.com> wrote in message
> news:jp2ZS5maCHA.197@forums.sybase.com...
> > What are the reasons for forcing IDENTITY-marked columns to be NUMERIC?
> I
> > hypothesize that it has something to do with ident burning (INT on
32-bit
> > different size than 64 bit?) such that it is easier to code, but there
> end
> > up being problems with datatype mismatches NUMERIC to INT for example.
> It
> > would be nice to allow INTs to be IDENTITY (also make porting easier).
> >
> > Thanks,
> >
> > --
> > Matt
>
> I think (but I'm not 100% sure) the original reason for making identity
> columns 'numeric' is because of the wider range of the 'numeric'
datatype.
> Combined with the risk of identity gaps, an 'int' could reach its end very
> soon when it's used as an identity column.
> Since 12.0, you can use the identity_gap setting to limit the size of
> potential gaps, so the original argument might not apply anymore. But I
> have no problem with identity columns being numeric, except one: the
> 'modulo' operator is not allowed for 'numeric' datatypes, which can be a
> problem fro writing application code.
> Instead of asking for 'int' for an identity column, my request would be to
> allow 'modulo' on 'numeric' instead (in fact, I have issued ISUG
> enhancement request e01_056 for this).
>
> HTH,
>
> Rob
> -------------------------------------------------------------
> Rob Verschoor
>
> Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
>
> Author of "The Complete Sybase ASE Quick Reference Guide"
> Online orders accepted at http://www.sypron.nl/qr
>
> mailto:rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
> http://www.sypron.nl
> Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
> -------------------------------------------------------------
>


Matt Rogish Posted on 2002-10-03 12:51:37.0Z
From: "Matt Rogish" <matt@fanhome.com>
References: <jp2ZS5maCHA.197@forums.sybase.com> <lF1RSjraCHA.251@forums.sybase.com>
Subject: Re: Allow for INT datatypes as IDENTITY
Date: Thu, 3 Oct 2002 08:51:37 -0400
Lines: 44
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
Message-ID: <H#ztwrtaCHA.313@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: woh-166-196-2.woh.rr.com 24.166.196.2
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:283
Article PK: 93453

Rob,

The identity gap problem makes sense, too. However since numerics are
packed values it would be nice if we could get away from that and instead
use IEEE native types (INT). As Carl stated the 8-byte BIGINT would be fine
here.

--
Matt

"Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote in
message news:lF1RSjraCHA.251@forums.sybase.com...
> I think (but I'm not 100% sure) the original reason for making identity
> columns 'numeric' is because of the wider range of the 'numeric'
datatype.
> Combined with the risk of identity gaps, an 'int' could reach its end very
> soon when it's used as an identity column.
> Since 12.0, you can use the identity_gap setting to limit the size of
> potential gaps, so the original argument might not apply anymore. But I
> have no problem with identity columns being numeric, except one: the
> 'modulo' operator is not allowed for 'numeric' datatypes, which can be a
> problem fro writing application code.
> Instead of asking for 'int' for an identity column, my request would be to
> allow 'modulo' on 'numeric' instead (in fact, I have issued ISUG
> enhancement request e01_056 for this).
>
> HTH,
>
> Rob
> -------------------------------------------------------------
> Rob Verschoor
>
> Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
>
> Author of "The Complete Sybase ASE Quick Reference Guide"
> Online orders accepted at http://www.sypron.nl/qr
>
> mailto:rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
> http://www.sypron.nl
> Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
> -------------------------------------------------------------
>


Bret Halford Posted on 2002-10-04 02:45:22.0Z
Message-ID: <3D9D00C2.3A944ED9@sybase.com>
Date: Thu, 03 Oct 2002 20:45:22 -0600
From: Bret Halford <bret@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.76 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Allow for INT datatypes as IDENTITY
References: <jp2ZS5maCHA.197@forums.sybase.com> <lF1RSjraCHA.251@forums.sybase.com> <H#ztwrtaCHA.313@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 47
NNTP-Posting-Host: 10.22.120.35
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:281
Article PK: 93450

Just what do you mean by "numerics are packed values"?


-bret

Matt Rogish wrote:
>
> Rob,
>
> The identity gap problem makes sense, too. However since numerics are
> packed values it would be nice if we could get away from that and instead
> use IEEE native types (INT). As Carl stated the 8-byte BIGINT would be fine
> here.
>
> --
> Matt
>
> "Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote in
> message news:lF1RSjraCHA.251@forums.sybase.com...
> > I think (but I'm not 100% sure) the original reason for making identity
> > columns 'numeric' is because of the wider range of the 'numeric'
> datatype.
> > Combined with the risk of identity gaps, an 'int' could reach its end very
> > soon when it's used as an identity column.
> > Since 12.0, you can use the identity_gap setting to limit the size of
> > potential gaps, so the original argument might not apply anymore. But I
> > have no problem with identity columns being numeric, except one: the
> > 'modulo' operator is not allowed for 'numeric' datatypes, which can be a
> > problem fro writing application code.
> > Instead of asking for 'int' for an identity column, my request would be to
> > allow 'modulo' on 'numeric' instead (in fact, I have issued ISUG
> > enhancement request e01_056 for this).
> >
> > HTH,
> >
> > Rob
> > -------------------------------------------------------------
> > Rob Verschoor
> >
> > Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
> >
> > Author of "The Complete Sybase ASE Quick Reference Guide"
> > Online orders accepted at http://www.sypron.nl/qr
> >
> > mailto:rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
> > http://www.sypron.nl
> > Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
> > -------------------------------------------------------------
> >


Matt Rogish Posted on 2002-10-04 04:39:33.0Z
From: "Matt Rogish" <matt@fanhome.com>
References: <jp2ZS5maCHA.197@forums.sybase.com> <lF1RSjraCHA.251@forums.sybase.com> <H#ztwrtaCHA.313@forums.sybase.com> <3D9D00C2.3A944ED9@sybase.com>
Subject: Re: Allow for INT datatypes as IDENTITY
Date: Fri, 4 Oct 2002 00:39:33 -0400
Lines: 22
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
Message-ID: <KNSEp91aCHA.197@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: woh-166-196-2.woh.rr.com 24.166.196.2
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:280
Article PK: 93451

Bret,

What is stated in the ASE manual:
Exact numeric types: decimal numbers
Use the exact numeric types, numeric and decimal, for numbers that include
decimal points. Data stored in numeric and decimal columns is packed to
conserve disk space and preserves its accuracy to the least significant
digit after arithmetic operations. The numeric and decimal types are
identical in all respects but one: Only numeric types with a scale of 0 can
be used for the IDENTITY column.

--
Matt

"Bret Halford" <bret@sybase.com> wrote in message
news:3D9D00C2.3A944ED9@sybase.com...
> Just what do you mean by "numerics are packed values"?
>
>
> -bret


Bret Halford Posted on 2002-10-04 18:56:47.0Z
Message-ID: <3D9DE46F.A4CA0496@sybase.com>
Date: Fri, 04 Oct 2002 12:56:47 -0600
From: Bret Halford <bret@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.76 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Allow for INT datatypes as IDENTITY
References: <jp2ZS5maCHA.197@forums.sybase.com> <lF1RSjraCHA.251@forums.sybase.com> <H#ztwrtaCHA.313@forums.sybase.com> <3D9D00C2.3A944ED9@sybase.com> <KNSEp91aCHA.197@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 35
NNTP-Posting-Host: 157.133.80.136
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:279
Article PK: 93449

Hi Matt,

Thanks for pointing that out - I think I'll see about having that
wording
changed, it seems somewhat misleading.

NUMERIC isn't packed in the usual sense of a "packed decimal" format,
where
a half-byte is used for each digit (with considerable waste). NUMERIC
is actually rather like an INT, except that it has the sign bit in a
seperate
byte. It is a counter of the number of "scale" units, i.e. if scale is
0,
then it is just a count of the number of 1s, if scale is 2, then it is a
counter
of the number of 1/100ths. a value of 123 in a numeric(3,0) has the
same
bit pattern as a value of 1.23 in a numeric(3,2), 0x007b.

-bret

Matt Rogish wrote:
>
> Bret,
>
> What is stated in the ASE manual:
> Exact numeric types: decimal numbers
> Use the exact numeric types, numeric and decimal, for numbers that include
> decimal points. Data stored in numeric and decimal columns is packed to
> conserve disk space and preserves its accuracy to the least significant
> digit after arithmetic operations. The numeric and decimal types are
> identical in all respects but one: Only numeric types with a scale of 0 can
> be used for the IDENTITY column.
>
> --
> Matt
>
> "Bret Halford" <bret@sybase.com> wrote in message
> news:3D9D00C2.3A944ED9@sybase.com...
> > Just what do you mean by "numerics are packed values"?
> >
> >
> > -bret


Matt Rogish Posted on 2002-10-04 20:57:41.0Z
From: "Matt Rogish" <matt@fanhome.com>
References: <jp2ZS5maCHA.197@forums.sybase.com> <lF1RSjraCHA.251@forums.sybase.com> <H#ztwrtaCHA.313@forums.sybase.com> <3D9D00C2.3A944ED9@sybase.com> <KNSEp91aCHA.197@forums.sybase.com> <3D9DE46F.A4CA0496@sybase.com>
Subject: Re: Allow for INT datatypes as IDENTITY
Date: Fri, 4 Oct 2002 16:57:41 -0400
Lines: 41
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
Message-ID: <M9fBPg#aCHA.313@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: woh-166-196-2.woh.rr.com 24.166.196.2
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:278
Article PK: 93447

Bret,

I see, yes I was worried it was as you had guessed.

How efficient is the optimizer when dealing with NUMERIC( x, 0 ) columns?
Or another way, does it optimize differently with NUMERIC( x, 0 ) vs
NUMERIC( x, z )?

Reason why I ask is that again the IDENTITY columns are all searched with
integer parameters, e.g.
WHERE ident_col = 123

How efficient are NUMERIC compared to INT?

Thanks again,

--
Matt

"Bret Halford" <bret@sybase.com> wrote in message
news:3D9DE46F.A4CA0496@sybase.com...
> Hi Matt,
>
> Thanks for pointing that out - I think I'll see about having that
> wording changed, it seems somewhat misleading.
>
> NUMERIC isn't packed in the usual sense of a "packed decimal" format,
> where a half-byte is used for each digit (with considerable waste).
NUMERIC
> is actually rather like an INT, except that it has the sign bit in a
> seperate byte. It is a counter of the number of "scale" units, i.e. if
scale is
> 0, then it is just a count of the number of 1s, if scale is 2, then it is
a
> counter of the number of 1/100ths. a value of 123 in a numeric(3,0) has
the
> same bit pattern as a value of 1.23 in a numeric(3,2), 0x007b.
>
> -bret


Jason Webster Posted on 2002-11-06 20:56:02.0Z
From: "Jason Webster" <jason.webster@nospam.mail.state.ky.us>
References: <jp2ZS5maCHA.197@forums.sybase.com> <lF1RSjraCHA.251@forums.sybase.com> <H#ztwrtaCHA.313@forums.sybase.com> <3D9D00C2.3A944ED9@sybase.com> <KNSEp91aCHA.197@forums.sybase.com> <3D9DE46F.A4CA0496@sybase.com> <M9fBPg#aCHA.313@forums.sybase.com>
Subject: Re: Allow for INT datatypes as IDENTITY
Date: Wed, 6 Nov 2002 15:56:02 -0500
Lines: 77
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4522.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
Message-ID: <C7bX6idhCHA.198@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 205.204.186.5
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:260
Article PK: 93432

This is a bit of a late post, but my experience has been that as long as the
column datatype size is greater than or equal to (in bytes that is) the
literal or variable in a search arg, the optimizer handles things
adequately. I.E.
create table a (x numeric(10))
create index n on a(x)
declare @z int
select x from a where x=@z
Since a.x is 6 bytes and @z is four (on solaris 2.8 anyway), the index is
used.

However, that's not the case here:
create table a(x numeric(3))
create index n on a(x)
declare @z int
select x from a where x=@z
This time we get a table scan.

Although I've never confirmed this, my assumption has always been that
shrinking a datatype risks an overflow so ase expands smaller datatypes to
larger during implicit conversions, and since expanding all the values in an
index would be expensive, a table scan is done. I'd like to hear some
confirmation on that though.

Also, we have been using "identity burning set factor"=1 and "identity grab
size" = 1 to limit gaps on identities to one value since version 11.0.2 with
numeric(10) identity columns without incident.

Jason Webster

"Matt Rogish" <matt@fanhome.com> wrote in message
news:M9fBPg#aCHA.313@forums.sybase.com...
> Bret,
>
> I see, yes I was worried it was as you had guessed.
>
> How efficient is the optimizer when dealing with NUMERIC( x, 0 ) columns?
> Or another way, does it optimize differently with NUMERIC( x, 0 ) vs
> NUMERIC( x, z )?
>
> Reason why I ask is that again the IDENTITY columns are all searched with
> integer parameters, e.g.
> WHERE ident_col = 123
>
> How efficient are NUMERIC compared to INT?
>
> Thanks again,
>
> --
> Matt
>
> "Bret Halford" <bret@sybase.com> wrote in message
> news:3D9DE46F.A4CA0496@sybase.com...
> > Hi Matt,
> >
> > Thanks for pointing that out - I think I'll see about having that
> > wording changed, it seems somewhat misleading.
> >
> > NUMERIC isn't packed in the usual sense of a "packed decimal" format,
> > where a half-byte is used for each digit (with considerable waste).
> NUMERIC
> > is actually rather like an INT, except that it has the sign bit in a
> > seperate byte. It is a counter of the number of "scale" units, i.e. if
> scale is
> > 0, then it is just a count of the number of 1s, if scale is 2, then it
is
> a
> > counter of the number of 1/100ths. a value of 123 in a numeric(3,0)
has
> the
> > same bit pattern as a value of 1.23 in a numeric(3,2), 0x007b.
> >
> > -bret
>
>


Matt Rogish Posted on 2002-11-11 19:38:00.0Z
From: "Matt Rogish" <matt@fanhome.com>
References: <jp2ZS5maCHA.197@forums.sybase.com> <lF1RSjraCHA.251@forums.sybase.com> <H#ztwrtaCHA.313@forums.sybase.com> <3D9D00C2.3A944ED9@sybase.com> <KNSEp91aCHA.197@forums.sybase.com> <3D9DE46F.A4CA0496@sybase.com> <M9fBPg#aCHA.313@forums.sybase.com> <C7bX6idhCHA.198@forums.sybase.com>
Subject: Re: Allow for INT datatypes as IDENTITY
Date: Mon, 11 Nov 2002 14:38:00 -0500
Lines: 57
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
Message-ID: <oDggOkbiCHA.130@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: woh-166-196-2.woh.rr.com 24.166.196.2
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:254
Article PK: 93425

Jason,

It seems to work as you suggest (provided our datatypes are larger than
NUMERIC( 10, 0 )).

I still would rather use a better datatype for IDENTITY columns than
NUMERIC. Pound-for-pound INT is better (more values in less space, not in a
'packed' format, etc). "What about larger identity values than 2.1
billion?" Two suggestions:
1) In that case use the NUMERIC facility
2) Provide us with an 8-byte BIGINT (value ranges are quite large)

For those of us with requirements of NUMERIC( 10, 0 ) or less an INT is a
far better choice. It's smaller (so data and indexes are more likely to be
in the cache) and really a much better datatype to represent and store
integer values.

--
Matt

"Jason Webster" <jason.webster@nospam.mail.state.ky.us> wrote in message
news:C7bX6idhCHA.198@forums.sybase.com...
> This is a bit of a late post, but my experience has been that as long as
the
> column datatype size is greater than or equal to (in bytes that is) the
> literal or variable in a search arg, the optimizer handles things
> adequately. I.E.
> create table a (x numeric(10))
> create index n on a(x)
> declare @z int
> select x from a where x=@z
> Since a.x is 6 bytes and @z is four (on solaris 2.8 anyway), the index is
> used.
>
> However, that's not the case here:
> create table a(x numeric(3))
> create index n on a(x)
> declare @z int
> select x from a where x=@z
> This time we get a table scan.
>
> Although I've never confirmed this, my assumption has always been that
> shrinking a datatype risks an overflow so ase expands smaller datatypes to
> larger during implicit conversions, and since expanding all the values in
an
> index would be expensive, a table scan is done. I'd like to hear some
> confirmation on that though.
>
> Also, we have been using "identity burning set factor"=1 and "identity
grab
> size" = 1 to limit gaps on identities to one value since version 11.0.2
with
> numeric(10) identity columns without incident.
>
> Jason Webster


Anthony Mandic Posted on 2002-11-12 05:27:14.0Z
Message-ID: <3DD09132.29248B0A@start.com.au>
Date: Tue, 12 Nov 2002 16:27:14 +1100
From: Anthony Mandic <am_is_not@start.com.au>
Organization: Mandic Consulting Pty. Ltd.
X-Mailer: Mozilla 4.61 [en] (WinNT; I)
MIME-Version: 1.0
Subject: Re: Allow for INT datatypes as IDENTITY
References: <jp2ZS5maCHA.197@forums.sybase.com> <lF1RSjraCHA.251@forums.sybase.com> <H#ztwrtaCHA.313@forums.sybase.com> <3D9D00C2.3A944ED9@sybase.com> <KNSEp91aCHA.197@forums.sybase.com> <3D9DE46F.A4CA0496@sybase.com> <M9fBPg#aCHA.313@forums.sybase.com> <C7bX6idhCHA.198@forums.sybase.com> <oDggOkbiCHA.130@forums.sybase.com>
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 12
NNTP-Posting-Host: 203.3.176.10
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:253
Article PK: 93426


Matt Rogish wrote:

> I still would rather use a better datatype for IDENTITY columns than
> NUMERIC. Pound-for-pound INT is better (more values in less space, not in a
> 'packed' format, etc). "What about larger identity values than 2.1
> billion?" Two suggestions:
> 1) In that case use the NUMERIC facility
> 2) Provide us with an 8-byte BIGINT (value ranges are quite large)

Or an unsigned int since the values used are seldom negative.

-am © 2002


Matt Rogish Posted on 2002-11-12 22:30:51.0Z
From: "Matt Rogish" <matt@fanhome.com>
References: <jp2ZS5maCHA.197@forums.sybase.com> <lF1RSjraCHA.251@forums.sybase.com> <H#ztwrtaCHA.313@forums.sybase.com> <3D9D00C2.3A944ED9@sybase.com> <KNSEp91aCHA.197@forums.sybase.com> <3D9DE46F.A4CA0496@sybase.com> <M9fBPg#aCHA.313@forums.sybase.com> <C7bX6idhCHA.198@forums.sybase.com> <oDggOkbiCHA.130@forums.sybase.com> <3DD09132.29248B0A@start.com.au>
Subject: Re: Allow for INT datatypes as IDENTITY
Date: Tue, 12 Nov 2002 17:30:51 -0500
Lines: 14
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
Message-ID: <aSCOhppiCHA.259@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: woh-166-196-2.woh.rr.com 24.166.196.2
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:252
Article PK: 93427

I considered that but figured it was more 'pie in the sky' than BIGINT since
BIGINT is implemented in other DBMS' whereas UNSIGNED INT is only in MySQL
IIRC.

--
Matt

"Anthony Mandic" <am_is_not@start.com.au> wrote in message
news:3DD09132.29248B0A@start.com.au...
> Or an unsigned int since the values used are seldom negative.
>
> -am © 2002


Anthony Mandic Posted on 2002-11-13 10:11:17.0Z
Message-ID: <3DD22545.6D640392@start.com.au>
Date: Wed, 13 Nov 2002 21:11:17 +1100
From: Anthony Mandic <am_is_not@start.com.au>
Organization: Mandic Consulting Pty. Ltd.
X-Mailer: Mozilla 4.61 [en] (WinNT; I)
MIME-Version: 1.0
Subject: Re: Allow for INT datatypes as IDENTITY
References: <jp2ZS5maCHA.197@forums.sybase.com> <lF1RSjraCHA.251@forums.sybase.com> <H#ztwrtaCHA.313@forums.sybase.com> <3D9D00C2.3A944ED9@sybase.com> <KNSEp91aCHA.197@forums.sybase.com> <3D9DE46F.A4CA0496@sybase.com> <M9fBPg#aCHA.313@forums.sybase.com> <C7bX6idhCHA.198@forums.sybase.com> <oDggOkbiCHA.130@forums.sybase.com> <3DD09132.29248B0A@start.com.au> <aSCOhppiCHA.259@forums.sybase.com>
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 7
NNTP-Posting-Host: 203.3.176.10
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:251
Article PK: 93434


Matt Rogish wrote:
>
> I considered that but figured it was more 'pie in the sky' than BIGINT since
> BIGINT is implemented in other DBMS' whereas UNSIGNED INT is only in MySQL
> IIRC.

Could always go for both :-)

-am © 2002