Re: Need pkey data type of 32 < x < 64 bits size.
From: Steve Kass (skass_at_drew.edu)
Date: 03/22/04
- Next message: fred: "variable and querysting"
- Previous message: Tenaya: "Re: use of tempdb by union all"
- In reply to: jan: "Need pkey data type of 32 < x < 64 bits size."
- Next in thread: Hugo Kornelis: "Re: Need pkey data type of 32 < x < 64 bits size."
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 22 Mar 2004 11:27:49 -0500
Jan,
You've already observed the important difference between character and
binary types. Character types are for words, and trailing spaces don't
matter, case and accents may or may not matter, and bytes that represent
non-printing characters may not be treated conveniently. I think
binary(5) would work out for you, but I've never tried this to see if
there is any down side from implicit conversions that might take place:
declare @b binary(5)
set @b = 0x
while @b < 10 begin
select @b
set @b = 1 + @b
end
Steve Kass
Drew University
jan wrote:
>Hi all,
>We have a database with 32 bit object identifiers. As we expect this
>to grow considerably we're worried that we may run out of this
>'address' space, but the obvious alternative of a 64 bit bigint is not
>something I'm comfortable with as these identifiers are pervasive -
>the idea of adding an extra four bytes on to just about every row in
>our database is something I'm very unhappy about; we really expect to
>be scaling very large indeed.
>If I could find something of 40 bits I think that would do us for the
>moment, but I can't find anything suitable. A char(5) is superficially
>okay but the SQL standard method of string comparison, where trailing
>spaces are effectively ignored, might have some unexpected surprises,
>and I'm not sure how to efficiently get the next one up from the
>current one; something which is obviously very easy to do with
>integers.
>I had to experimented with decimal (9, 0) but while this takes five
>bytes, which is good, it will take nine decimal digits at the most,
>which is not so good. I presume this is BCD under the skin.
>I could of course add an extra column, a tinyint, but that puts an
>extra load on the SQL optimiser, and frankly, gives it an extra chance
>to make a mistake (I've had to track down a few bad query plans).
>Any suggestions would be welcome.
>
>By the way what is the difference between binary and varbinary? if
>char and varchar can represent the entire range of Ascii (and I
>thought it did) what it is the point of their binary counterparts?
>
>cheers
>
>jan
>
>
- Next message: fred: "variable and querysting"
- Previous message: Tenaya: "Re: use of tempdb by union all"
- In reply to: jan: "Need pkey data type of 32 < x < 64 bits size."
- Next in thread: Hugo Kornelis: "Re: Need pkey data type of 32 < x < 64 bits size."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
