What datatype to use for PK?

**Gary** · Jul 23 '05, 08:01 AM

Re: What datatype to use for PK?

BIGINT is stored one byte smaller than DECIMAL(18,0). 8 bytes vs. 9
bytes.

Not likely to significantly change performance unless you're talking
millions of rows being processed.

**Don Vaillancourt** · Jul 23 '05, 08:01 AM

Re: What datatype to use for PK?

I always thought that decimal stored it's values in text format. So for
decimal(18,0) MSSQL would take up 18 bytes. Or am I partially right and
MSSQL uses a nibble to represent a digit.

Gary wrote:[color=blue]
> BIGINT is stored one byte smaller than DECIMAL(18,0). 8 bytes vs. 9
> bytes.
>
> Not likely to significantly change performance unless you're talking
> millions of rows being processed.
>[/color]

--
Don Vaillancourt
Director of Software Development
WEB IMPACT INC.
phone: 416-815-2000 ext. 245
fax: 416-815-2001
email: donv@web-impact.com <mailto:donv@we bimpact.com>
web: http://www.web-impact.com
Web Impact Inc. <http://www.web-impact.com>
This email message is intended only for the addressee(s) and contains
information that may be confidential and/or copyright.

If you are not the intended recipient please notify the sender by reply
email and immediately delete this email.

Use, disclosure or reproduction of this email by anyone other than the
intended recipient(s) is strictly prohibited. No representation is made
that this email or any attachments are free of viruses. Virus scanning
is recommended and is the responsibility of the recipient.

**Gary** · Jul 23 '05, 08:01 AM

Re: What datatype to use for PK?

That is not correct. DECIMAL (18,0) uses 9 bytes stored in hexadecimal
format, not text format. Check BOL.

**Hugo Kornelis** · Jul 23 '05, 08:01 AM

Re: What datatype to use for PK?

On Wed, 09 Feb 2005 15:06:33 -0500, Don Vaillancourt wrote:
[color=blue]
>I always thought that decimal stored it's values in text format. So for
>decimal(18,0 ) MSSQL would take up 18 bytes. Or am I partially right and
>MSSQL uses a nibble to represent a digit.[/color]

Hi Don,

From Books Online:

Precision Storage bytes
1 - 9 5
10-19 9
20-28 13
29-38 17

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

**tal_mcmahon@hotmail.com** · Jul 23 '05, 08:01 AM

Re: What datatype to use for PK?

Just a thought,
I have found that using unique identifiers for PK columns have benefits
enough to outweigh the size and code differences.

If i use a Unique identifier i do not need to make a return trip to a
client with the id generated by the Database. I can create a Unique
identifier almost anywhere and push the parent pk to the children
without a need for all of the Create a Parent step, now create details,
now create details for the details.

Also, When i use GUID for pk i can mix together values from different
tables and still have a unique column within the view. for example I
can grab address info from both the Customer and the Vendor tables to
create a view that can return all addresses.

anyway, just a thought

Tal

**Gary** · Jul 23 '05, 08:02 AM

Re: What datatype to use for PK?

The only caveat to using unique identifier is that it can create
indexes that are up to four times larger than using an int, for
example. That could cause as much as 4X disk I/O to retrieve your disk
pages. Only a concern for large tables, though.

**Erland Sommarskog** · Jul 23 '05, 08:02 AM

Re: What datatype to use for PK?

Don Vaillancourt (donv@webimpact .com) writes:[color=blue]
> I always thought that decimal stored it's values in text format. So for
> decimal(18,0) MSSQL would take up 18 bytes. Or am I partially right and
> MSSQL uses a nibble to represent a digit.[/color]

The format you get up to a client is a record with precision, scale
and sign, and there are up to 16 bytes for the value.

typedef struct tagDB_NUMERIC {
BYTE precision;
BYTE scale;
BYTE sign;
BYTE val[16];
} DB_NUMERIC;

As for the question whether to use bigint or decimal(18,0), my answer is
neither. The prime option should be to use natural keys, and natural
keys rarely calls for 64-bit numbers. When you need artificial keys,
integer should do in most cases.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

**Gary** · Jul 23 '05, 08:02 AM

Re: What datatype to use for PK?

Erland, where did you get that typedef code? Is that from SQL Server
source code?

**David Portas** · Jul 23 '05, 08:02 AM

Re: What datatype to use for PK?

> When i use GUID for pk i can mix together values from different[color=blue]
> tables and still have a unique column within the view.[/color]

Why is that an advantage? If two rows represent the same physical
entity then you usually want to be able to identify them as being the
same - otherwise you end up with unwanted duplicates. That is why
natural keys are important. Billing the same customer twice isn't
usually good for business!

--
David Portas
SQL Server MVP
--

**Greg D. Moore \(Strider\)** · Jul 23 '05, 08:02 AM

Re: What datatype to use for PK?

"Don Vaillancourt" <donv@webimpact .com> wrote in message
news:CisOd.9068 8$vO1.567838@nn rp1.uunet.ca...[color=blue]
> Over the years I have always used the decimal(18,0) as the datatype for
> primary keys. Aside from the number of significant numbers involved,
> would BigInt type be better for performance or is decimal(18,0) still[/color]
okay.

I think it depends on what your primary key is. If it's an ISBN for
example, I don't think either of these would work.

**Erland Sommarskog** · Jul 23 '05, 08:02 AM

Re: What datatype to use for PK?

David Portas (REMOVE_BEFORE_ REPLYING_dporta s@acm.org) writes:[color=blue]
> Why is that an advantage? If two rows represent the same physical
> entity then you usually want to be able to identify them as being the
> same - otherwise you end up with unwanted duplicates. That is why
> natural keys are important. Billing the same customer twice isn't
> usually good for business![/color]

As long as the customer pays both bills it is!

As for the use of artificial keys - if the thing inserted into the database
is an order by a web customer, there is no natural key until it has been
inserted. The order-entry system must generate the key itself.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

**Erland Sommarskog** · Jul 23 '05, 08:02 AM

Re: What datatype to use for PK?

Gary (gary_strader@w cc.ml.com) writes:[color=blue]
> Erland, where did you get that typedef code? Is that from SQL Server
> source code?[/color]

The OLE DB Refeference Manual in the MDAC Books Online. It's also in MSDN
Library.

Of course, that's a general interface which is independent of the data
source, but I would not be surprised if the format is taken from SQL
server.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

**David Portas** · Jul 23 '05, 08:02 AM

Re: What datatype to use for PK?

A natural key is simply a product of the way some data is represented
in a table - a subset of the attributes that you need to record. In the
case of an online order that might be (user,order_dat etime) for
example. A natural key is never "generated" internally because it
always represents something that is "outside the system" - that is the
critical distinction between a natural and artificial key.

--
David Portas
SQL Server MVP
--

**Mike Sherrill** · Jul 23 '05, 08:02 AM

Re: What datatype to use for PK?

On 9 Feb 2005 14:00:03 -0800, "tal_mcmahon@ho tmail.com"
<tal_mcmahon@ho tmail.com> wrote:

[snip][color=blue]
>Also, When i use GUID for pk i can mix together values from different
>tables and still have a unique column within the view. for example I
>can grab address info from both the Customer and the Vendor tables to
>create a view that can return all addresses.[/color]

I'd like to see the conceptual model behind that.

--
Mike Sherrill
Information Management Systems

What datatype to use for PK?

What datatype to use for PK?

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment