What datatype to use for PK?

[Don Vaillancourt]

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 okay.
--
Don Vaillancourt
Director of Software Development
WEB IMPACT INC.
phone: [phone] ext. 245
fax: [phone]
email: do**@web-impact.com <mailto:do**@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]

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]

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:

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
Director of Software Development
WEB IMPACT INC.
phone: [phone] ext. 245
fax: [phone]
email: do**@web-impact.com <mailto:do**@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]

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

[Hugo Kornelis]

On Wed, 09 Feb 2005 15:06:33 -0500, Don Vaillancourt wrote:

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.

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]

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]

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]

Don Vaillancourt (do**@webimpact .com) writes:

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.

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, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Gary]

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

[David Portas]

> When i use GUID for pk i can mix together values from different

tables and still have a unique column within the view.

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
--