Any performance issues if we use BigInt

3 Dec 2005 10:03 PM

Bob

In the past, I've always used "int" for the Identity field. But as the
application usage increases, it seems to demand for values that exceed the
"int" scope. I'm thinking of upgrading all my Primary Keys that were "Int"
to "BigInt".

I'd like to know if there's any performance degradation by doing so.

Also, what would be a compatible datatype of BigInt in Classic ASP and in
ASP.NET 2.0 C#.

Thanks for your response.

4 Dec 2005 12:23 AM

Erland Sommarskog

Bob (spamfree@nospam.com) writes:

> In the past, I've always used "int" for the Identity field. But as the
> application usage increases, it seems to demand for values that exceed
> the "int" scope. I'm thinking of upgrading all my Primary Keys that
> were "Int" to "BigInt".
>
> I'd like to know if there's any performance degradation by doing so.

There is one, although not dramatic. bigint takes up eight bytes, where
as int takes up four bytes. This means that you row size increases, and
you can fit fewer rows per page, so to read the same number of rows, SQL
Server will have to access more pages.

int goes all the way to 2,147 milliards, which is quite lot, so int may
last longer than you believe.

> Also, what would be a compatible datatype of BigInt in Classic ASP and in
> ASP.NET 2.0 C#.

No idea.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

4 Dec 2005 5:10 PM

Alexander Kuznetsov

> int goes all the way to 2,147 milliards,

Erland, you can set the seed value to -2 billion and get twice as much
values out of a regular int, right?

4 Dec 2005 11:02 PM

Erland Sommarskog

Alexander Kuznetsov (AK_TIREDOFSPAM@hotmail.COM) writes:

>> int goes all the way to 2,147 milliards,
>
> Erland, you can set the seed value to -2 billion and get twice as much
> values out of a regular int, right?

Correct, as long as you plan for it ahead. Not very fun of changing all
that when you are about reach 2^31-1.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

4 Dec 2005 2:16 AM

Dan Guzman

> Also, what would be a compatible datatype of BigInt in Classic ASP and in
> ASP.NET 2.0 C#.

Bigint is a 64-bit number so you can use Int64 in .NET languages. Assuming
you are using VBScript in ASP, all data types are variant. I believe
variant subtype double would be used for a large bigint value.

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quote

"Bob" <spamfree@nospam.com> wrote in message
news:u$pzOOF%23FHA.740@TK2MSFTNGP11.phx.gbl...
> In the past, I've always used "int" for the Identity field. But as the
> application usage increases, it seems to demand for values that exceed the
> "int" scope. I'm thinking of upgrading all my Primary Keys that were
> "Int"
> to "BigInt".
>
> I'd like to know if there's any performance degradation by doing so.
>
> Also, what would be a compatible datatype of BigInt in Classic ASP and in
> ASP.NET 2.0 C#.
>
> Thanks for your response.
>
>

4 Dec 2005 2:34 AM

--CELKO--

The right answer is not to ever use IDENTITY, but to find a relational
key.

You don't seem to know that fields and columns are totally different,
which is probably why you ask this kind of question about a table
property.

In effect, if this was a furniture forum you would be asking for the
best kind of rocks to smash screws into furniture. The kludge answer
is "Granite!", but the right answer is to take the time to learn about
screws, screw drivers, nails, glue, etc.