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

-------------------------------------------------------------

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,

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

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

-bret

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

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

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

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

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

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

-am Â© 2002

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

Could always go for both :-)

-am Â© 2002