CODEWITHSUNDEEP
c#asp.netsql
ethem
ethem

Reputation: 2918

what type shall use to store 12 digit value shall I use decimal or nvarchar in SQL DB?

I need to store an CARD ID number in Database. So there is no calculation just a search of the ID and putting the value in Session as property in a class.

The is ID is always numeric and it's 12 positions. e.g. 123456789012 and I would like to show on the screen in this format. 123.456.789.012 (every 3 digit a dot).

I tried a test and defined Decimal(12,0) in database and I have put this value in database: 555666777888

then I try to display on the screen I used this code (CardID is decimal):

  lblCardID.Text = ent.CardID.ToString("0:#,###")

but it shows on the screen like this: 555,666,77:7,888

where is the colon (:) coming from?

question additional: - What type shall use in MS SQL to store this value in Database. Decimal (12,0) or Nvarchar(12) ?

Upvotes: 3

Views: 5213

Answers (8)

Grzegorz Gierlik
Grzegorz Gierlik

Reputation: 11232

I would use bigint because it needs only 8 bytes per value.

decimal(12,0) needs 9 bytes and varchar or nvarchar even more (12 or 24 bytes respectively in case of storing 12 digits).

Smaller column size makes indexes smaller, which make indexes faster in use.

Formatting numbers can be done in application. It's also much easier to change formatting in app in case of requirements change.

Upvotes: 1

onedaywhen
onedaywhen

Reputation: 57093

Does your identifier's domain have matematical properties, other than being composed of digits? If not, your value is fixed width, so use CHAR(12). Do not forget to add appropriate domain checks (no characters other than digits, no leading zero, etc) e.g. 

CREATE TABLE Cards 
(
 card_ID CHAR(12) NOT NULL
    UNIQUE
    CONSTRAINT card_ID__all_digits 
       CHECK (card_ID NOT LIKE '%[^0-9]%'), 
    CONSTRAINT card_ID__no_leading_zero
       CHECK (card_ID NOT LIKE '[1-9]%)')
);

Upvotes: 0

phoog
phoog

Reputation: 43076

The colon is coming from the colon in your format string. The "0:" at the beginning of the format string is needed when you are using string.Format(), as a placeholder to identify which of the arguments to format, but not if you are using ToString() (since there's only one value being formatted).

Upvotes: 2

Jan
Jan

Reputation: 16048

Try writing 

lblCardID.Text = ent.CardID.ToString("#,###")

You can user the decimal(12,0) or the bigint datatype. bigint requires one byte less (8 bytes total) per stored value.

Upvotes: 3

OTTA
OTTA

Reputation: 1081

If it's an ID number store it as a string datatype, you're not going to be doing sums on it, you also won't have problems losing any leading zeros. You could also then store the card id with the embedded dots, sorting out your formatting problems.

Upvotes: 0

PaulStock
PaulStock

Reputation: 11283

If it's never going to be calculated on, I would store it as char(12).

Then in your code, split it with something like this and use the replace function to convert commas to dots:

lblCardID.Text = ent.CardID.ToString("#,###").Replace(",", ".")

Upvotes: 0

Jason
Jason

Reputation: 3960

If you need to store the formatting, and it's just a numeric value, use varchar, don't waste time with nvarchar as it increases your storage size and won't do you any good unless you expect special (international) chars

Upvotes: 0

user610217
user610217

Reputation:

nvarchar is definitely not needed. if it's always 12 digits, char(12) would be fine, but I think a 64-bit integer would be most appropriate.

Upvotes: 4

Related Questions