‫انتخاب نوع داده‌ی مناسب مخصوص ذخیره سازی مقادیر پولی در SQL Server

ساخت وبلاگ

آخرین مطالب

امکانات وب

‫انتخاب نوع داده‌ی مناسب مخصوص ذخیره سازی مقادیر پولی در SQL Server

درحال حاضر، باتوجه به خرده نداشتن مقادیر پولی در ایران، عموما از نوع‌های int و bigint برای ذخیره سازی این مقادیر استفاده می‌شود؛ اما در آینده با احتمال حذف تعدادی از صفرها، نیاز به ثبت خرده‌ها هم ضروری خواهد بود و در اینجا این سؤال مهم مطرح می‌شود که نوع داده‌ای مناسب برای انجام اینکار چیست؟ برای نمونه در SQL Server، نوع‌های داده‌ای decimal، money، smallmoney و امثال آن وجود دارند که در این مطلب، تفاوت‌های مهم آن‌ها و روش صحیح انتخاب نوع داده‌ای مناسب مخصوص اینکار را بررسی خواهیم کرد.


مشکل مهم نوع داده‌ای int جهت ذخیره سازی مقادیر پولی

فرض کنید جدول ساده‌ای را با دو فیلد Id و Price دارید که نوع مبلغ آن‌را با توجه به عدم داشتن خرده در واحد پولی، int انتخاب کرده‌اید:
CREATE TABLE [Test1](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Price] [int] NOT NULL, CONSTRAINT [PK_Test1] PRIMARY KEY CLUSTERED
(
[Id] ASC
));
اگر در این جدول فقط 7 رکورد زیر را ثبت کنیم:
 Insert into Test1 values (1000000000),(1000000000),(1000000000),(1000000000),(1000000000),(1000000000),(1000000000)
به نظر شما خروجی کوئری ساده‌ی زیر که جهت نمایش جمع مبالغ وارد شده تهیه شده، چیست؟
select sum(price) from Test1
خروجی آن فقط استثنای زیر است!
Arithmetic overflow error converting expression to data type int.
عنوان می‌کند که جمع آن از بازه‌ی اعداد صحیح خارج شده‌است و در سیستمی که نوع مبالغ آن‌را int انتخاب کرده‌اید، دیر یا زود به این مشکل خواهید رسید. فقط کافی است کاربران، یکسالی با آن برنامه کار کنند!
برای حل این مشکل می‌توان به صورت موقت، نوع داده‌ای را به bigint تبدیل کرد و مجددا جمع رکوردها را محاسبه کرد:
select sum(cast(price as bigint)) from Test1
یک روش دیگر مواجه شدن با این مساله، عدم انتخاب نوع int برای فیلد Price، از ابتدای کار است.


از نوع داده‌ای float برای ذخیره سازی مقادیر پولی استفاده نکنید!

هیچگاه نباید از نوع داده‌ی float برای ذخیره سازی مقادیر پولی استفاده کرد؛ از این جهت که این نوع اعداد، به صورت تقریبی از یک مقدار decimal و به صورت باینری در SQL Server ذخیره می‌شوند. به همین جهت به محض ذخیره شدن، با عددی غیر دقیق مواجه خواهیم بود. همچنین مقایسه‌ی دقیق این نوع اعداد هم مشکلات خاصی را به همراه دارد.
DECLARE @f AS FLOAT = '29545428.0211111';
SELECT CAST(@f AS NUMERIC(28, 14)) AS value;



SQL Server چگونه مقادیر پولی money و small money را ذخیره می‌کند؟

SQL Server برای کار با مقادیر پولی، دو نوع MONEY و SMALLMONEY را ارائه می‌دهد که شبیه به نوع‌های BIGINT و INT، نیاز به 8 و 4 بایت برای ذخیره سازی دارند. در عمل نوع MONEY شبیه به نوع DECIMAL(19,4) و نوع SMALLMONEY همانند DECIMAL(10,4) رفتار می‌کند. یعنی نوع MONEY می‌تواند تا 15 رقم دسیمال پیش از ممیز و 4 رقم اعشار را ذخیره کند و نوع SMALLMONEY تنها می‌تواند 6 رقم دسیمال و 4 رقم اعشاری را ذخیره کند.
اما ... هرچند نوع داده‌ی MONEY و DECIMAL(19,4) به ظاهر یکی هستند، اما به نحو متفاوتی بر روی دیسک سخت ذخیره می‌شوند. برای نمونه فرض کنید که قصد داریم عدد 4,513.19 را یکبار به صورت MONEY و بار دیگر به صورت SMALLMONEY ذخیره کنیم که در نهایت به جدول زیر می‌رسیم:


همانطور که مشاهده می‌کنید، نوع‌های MONEY و SMALLMONEY، دقیقا همانند BIGINT هشت بایتی و INT، چهار بایتی ذخیره می‌شوند و عملا در پشت صحنه‌ی SQL Server، اعداد صحیح هستند. اما نوع DECIMAL(19,4) که هرچند شبیه به MONEY عمل می‌کند، 9 بایتی است.


الگوریتم انتخاب نوع داده‌ی مناسب ذخیره سازی مقادیر پولی

در فلوچارت زیر که از کتاب «Donald Knuth’s "The Art of Computer Programming – Volume 1".» انتخاب شده، روش مواجه شدن با انواع و اقسام نوع‌های داده‌ای عددی را به خوبی مشخص می‌کند که آیا عدد در حال ذخیره شدن، خرده دارد یا خیر؟ آیا از 922,337,203,685,477.5807 کوچکتر است یا خیر و امثال آن که در تصمیم‌گیری نهایی مؤثر هستند:


اعدادی را که در این نمودار مشاهده می‌کنید، در جدول زیر بهتر توضیح داده شده‌اند. به عبارتی چه تفاوتی بین نوع Money و Decimal(19,4) مشابه وجود دارد:



تفاوت مهم نوع Money و Decimal(19,4)، در دقت آن‌ها است

 تا اینجا به نظر آنچنان تفاوتی بین نوع Money و Decimal(19,4) وجود ندارد و نوع money اتفاقا یک بایت را کمتر اشغال می‌کند و کوچکتر است. اما تفاوت اصلی را با مثال زیر بهتر می‌توان توضیح داد:
CREATE TABLE MoneyTest ( Mon1 money, Mon2 AS Mon1*Mon1, Mon3 AS Mon1*Mon1*Mon1, Dec1 decimal(19,4), Dec2 AS Dec1*Dec1, Dec3 AS Dec1*Dec1*Dec1, MonDec AS Mon1*Dec1, DecMon AS Dec1*Mon1);
در اینجا جدولی تهیه شده که دو ستون اصلی Mon1 و Dec1 را دارد و مابقی ستون‌های آن، محاسباتی هستند:


همانطور که مشاهده می‌کنید، با ضرب دو عدد دسیمال، مقادیر پیش و پس از ممیز، یعنی precision و scale تغییر کرده‌اند، اما در مورد money چنین چیزی رخ نداده و ثابت است. برای مثال زمانیکه با یک عدد DECIMAL(4,2) کار می‌کنیم، اگر آن‌را ضربدر همین عدد کنیم، به یک عدد DECIMAL(8,4) خواهیم رسید که البته حداکثر precision ممکن آن در SQL Server عدد 38 است، اما یک چنین تغییری در حین ضرب اعداد از نوع money رخ نمی‌دهد.

موضوع دقت را با مثال زیر بهتر می‌توان بررسی کرد:
CREATE TABLE [MoneyTest](
[Id] [int] IDENTITY(1,1) NOT NULL,
decimalMoney decimal(19,4),
moneyMoney money CONSTRAINT [PK_MoneyTest] PRIMARY KEY CLUSTERED
(
[Id] ASC
));
فرض کنید جدولی را داریم با دو فیلد از نوع Money و مشابه آن یعنی decimal(19,4) به صورت فوق. اگر رکوردهای زیر را به آن اضافه کنیم:
INSERT INTO MoneyTest
VALUES
(12321423442.3456,12321423442.3456),
(1111111.1919,1111111.1919)
و سپس سعی کنیم که جمع اعداد وارد شده را محاسبه کنیم:
SELECT * FROM MoneyTest
SELECT SUM(decimalMoney) AS [sumDecimal], SUM(moneyMoney) AS [sumMoney]
FROM MoneyTest
به نتیجه‌ی زیر می‌رسیم:


همانطور که مشخص است در حین محاسباتی مانند جمع و منها و محاسبه‌ی sum، تفاوتی بین این نوع‌ها نیست. اما اگر سعی در تقسیم آن‌ها کنیم:
DECLARE @moneyPer money, @decimalPer decimal(19,4)
SET @moneyPer = (SELECT moneyMoney FROM MoneyTest WHERE id = 2)/((SELECT moneyMoney FROM MoneyTest WHERE id = 1))
SET @decimalPer = (SELECT decimalMoney FROM MoneyTest WHERE id = 2)/((SELECT decimalMoney FROM MoneyTest WHERE id = 1))
SELECT @moneyPer AS[moneyPer], @decimalPer AS [decimalPer];
به خروجی زیر می‌رسیم:


نتیجه‌ی واقعی 0,00009 است که پس از گرد شدن، به 0.0001 مقدار دسیمال می‌رسیم، اما این دقت در نوع money از دست رفته‌است.

نکته‌ی مهمی که در اینجا قابل مشاهد‌ه‌است، محدود نبودن نتیجه‌ی حاصل، به دقت اعشارها در عدد decimal تعریف شده و scale تعریف شده‌ی اولیه‌ی آن است. نمونه‌ی دیگر آن‌را در مثال زیر می‌توانید مشاهده کنید که هرچند عدد دسیمال تعریف شده، فقط 2 رقم اعشاری دارد، اما در حین تقسیم، از این مساله صرفنظر شده و خروجی آن محدود به 2 رقم اعشار نیست؛ برخلاف نوع money که حداکثر 4 رقم ثابت اعشاری را بیشتر نمی‌تواند داشته باشد:
DECLARE @M MONEY = 1234, @D DECIMAL(6,2) = 1234
SELECT @M/$1000000 AS [MONEY] , @D/$1000000 AS [DECIMAL]



نتیجه‌گیری

برای ذخیره سازی مقادیر پولی در SQL Server، اگر سیستم شما OLTP-like است و با اعدادی مانند 1000.24 کار می‌کنید و حداکثر می‌خواهید جمع و منهای آن‌‌ها را محاسبه کنید، انتخاب نوع  MONEY و یا  SMALLMONEY بسیار مناسب است؛ اما اگر سیستم شما OLAP-like است و در آن اعمال ضرب و تقسیم زیاد رخ می‌دهد، فقط از نوع Decimal استفاده کنید.


DECLARE @dOne DECIMAL(19,4) = 1, @dThree DECIMAL(19,4) = 3, @mOne MONEY = 1, @mThree MONEY = 3
SELECT (@dOne/@dThree) * @dThree AS DecimalResult, (@mOne/@mThree) * @mThree AS MoneyResult
دانلود نرم افزار و برنامه...
ما را در سایت دانلود نرم افزار و برنامه دنبال می کنید

برچسب : نویسنده : دانلودی dld بازدید : 134 تاريخ : دوشنبه 16 مرداد 1402 ساعت: 18:52

آرشیو مطالب

پيوندهای روزانه

لینک دوستان

خبرنامه