انواع داده در SQL Server

در این آموزش با انواع داده های SQL Server از جمله عددی، رشته کاراکتری، رشته باینری، تاریخ و زمان و سایر انواع داده آشنا خواهید شد.

بررسی اجمالی انواع داده های SQL Server

در SQL Server، یک ستون، متغیر و پارامتر دارای مقداری است که با یک نوع (type) مرتبط است یا به عنوان نوع داده (data type) نیز شناخته می شود. نوع داده مشخصه ای است که نوع داده ای را که این اشیاء می توانند ذخیره کنند را مشخص می کند. data type می تواند یک عدد صحیح، رشته کاراکتر، پولی، تاریخ و زمان و غیره باشد.

SQL Server فهرستی از انواع داده ها را ارائه می کند و همه انواع داده هایی را که می توانید از آنها استفاده کنید، تعریف می کند، به عنوان مثال، تعریف یک ستون یا اعلام یک متغیر.

تصویر زیر سیستم کلی انواع داده های SQL Server را نشان می دهد:

توجه داشته باشید که SQL Server انواع داده های ntext ، text و image را در نسخه بعدی خود حذف خواهد کرد. بنابراین، شما باید از استفاده از این نوع داده ها خودداری کنید و به جای آن از انواع داده های nvarchar(max) ، varchar(max) و varbinary(max) استفاده کنید.

انواع داده های عددی دقیق (Exact numeric)

انواع داده های Exact numeric اعداد دقیقی مانند عدد صحیح، اعشاری یا مقدار پولی را ذخیره می کنند. در فیلدهای Exact numeric دقیقاً مقداری را که به آنها نسبت می دهیم ذخیره می شود.

* بیت یکی از سه مقدار 0، 1 و NULL را ذخیره می کند.

* انواع داده های int ، bigint , smallint و tinyint داده های عدد صحیح را ذخیره می کنند.

* انواع داده های اعشاری (decimal) و عددی (numeric) اعدادی را ذخیره می کنند که دقت و مقیاس ثابتی دارند. توجه داشته باشید که اعشاری و عددی مترادف هستند.

* نوع داده پول و smallmoney مقادیر ارز را ذخیره می کند.

انواع داده Exact Numeric یا داده عددی در SQL و ویژگی های آنها در زیر نشان داده شده است.

1- Int : قابلیت ذخیره سازی 4 بایت را دارا می باشد و قادر به ذخیره سازی محدوده اعداد صحیح 31^2- یا (2,147,483,648-) تا 1-31^2 یا (2,147,483,647) می باشد. مترادف int در SQL-92 کلمه ی integer می باشد.

2- Bigint : قابلیت ذخیره سازی 8 بایت را دارا می باشد و محدوده اعداد صحیح 9,223,372,036,854,775,808- تا 9,223,372,036,854,775,807 را شامل شده و کاربرد آن هنگامی است که مقدار داده صحیح بیش از نوع داده int باشد.

3- Smallint : قابلیت ذخیره 2 بایت داده صحیح را داراست و می تواند اعدادی بین 32,768- تا 32,767 را شامل شود.

4- Tinyint : قادر به ذخیره تنها یک بایت می باشد و محدوده اعداد صحیح 0 تا 255 را در بر می گیرد.

5- Bit : مقادیر صحیح 0 یا 1 و همچنین null را می پذیرد. به عنوان مثال برای وارد کردن داده هایی مانند Y/N به معنی بله و خیر یا T/F به معنی درست یا نادرست به کار می رود .

6-Numeric و Decimal : این نوع داده ها برای ذخیره داده هایی با اعداد اعشاری دارای ممیز شناور بدون گرد کردن استفاده می شوند و باید در آنها کل ارقام قابل ذخیره (دقت) و ارقام اعشاری قرار گرفته در سمت راست ممیز اعشار مشخص شود . این نوع داده ها محدود1-38^10- تا 1-38^10 را در بر می گیرند.

7- Money : برای ذخیره مقادیر پول کاربرد دارد البته برای پول هایی مانند ریال کارایی چندانی ندارد. در ذخیره داده ای با واحد دلار که پول خرد دارد مثلا 2 دلار و 75 سنت، استفاده از این نوع داده نمود بهتری دارد. چرا که این نوع داده دقیقا 4 رقم در سمت راست ممیز اعشار خواهد داشت. اندازه ذخیره سازی آن 8 بایت بوده و مقادیر 922,337,203,685,477.5808- تا 922,337,203,685,477.5807 را شامل می شود . همچنین این نوع داده برای هر نوع ارزی به کار برده می شود.

8- Small money : اندازه آن 4 بایت است و مقادیر ارزی موجود در محدوده 214,748.3648- تا 214,748.3647 را می تواند ذخیره کند. این نوع داده دقیقاً 4 رقم در سمت راست ممیز اعشار خواهد داشت و برای هر نوع ارزی کاربرد دارد.

انواع داده های عددی تقریبی (Approximate numeric)

انواع داده Approximate Numeric در SQL داده های عددی با ممیز شناور را ذخیره می کند. نوع داده Approximate numeric مقداری تقریبی را از عددی که به آن نسبت می دهید ذخیره می کند. آنها اغلب در محاسبات علمی استفاده می شوند.

اگر داده های با ممیز شناور با دقت لازم در فرمت باینری سرور قابل ارائه نباشند ، SQL Server آنها را گرد می کند. وقتی که انواع داده با ممیز شناور را در نظر بگیریم ، در واقع دقت را برای حداکثر تعداد ارقام مجاز در هر دو طرف ممیز اعشار مشخص کرده ایم . نوع داده ها ی real و float برای ذخیره ی داده هایی های مناسب هستند که دقت مطلق برای آنها ضروری نیست و داده ها ارقام بسیار کوچک و یا بسیار بزرگ را شامل می شوند. مثل داده های علمی و آماری.

1- float(n) : محدوده 308^10*1.79- تا 308^10*1.79 برای این data type مشخص شده است. n تعداد بیت های استفاده شده برای ذخیره مقدار اعشاری یک float number در نماد علمی است، در نتیجه دقت و حجم ذخیره سازی را مشخص می کند. n ارزشی در محدوده 1 تا 53 را دارا می باشد. فضای ذخیره سای این نوع داده 8 بایت است.

2- Real : این نوع داده محدوده اعداد اعشاری 38^10*3.40- تا 38^10*3.40 را در بر می گیرد. همانطور که مشاهده می شود دقت اینگونه اعداد تا 38 رقم می باشد. فضای لازم برای ذخیره سازی این نوع داده 4 بایت است. همچنین در SQL Server نوع داده real مترادف با float(24) می باشد.

انواع داده های تاریخ و زمان (Date & Time)

انواع داده Date and Time یا زمان و ساعت در SQL، که داده های مربوط به زمان و تاریخ را ذخیره می کنند در زیر لیست شده اند.

1- Date : برای ذخیره تاریخ استاده می شود. فرمت رشته ای آن به صورت YYYY-MM-DD می باشد که YYYY محدوده ارقام 0001 تا 9999 را برای نمایش سال و MM ارقام 01 تا 12 را برای نمایش ماه و DD ارقام 01 تا 31 را با توجه به ماه مورد نظر برای نمایش روز می تواند در بر گیرد. مقدار پیش فرض این نوع داده 01-01-1900 است. اندازه حافظه مورد نیاز این نوع داده 3 بایت و ثابت است.

2- Time : این نوع داده زمان را در خود ذخیره می کند . فرمت رشته ای آن به صورت hh:mm:ss[.nnnnnnn] می باشد و محدوده 00:00:00.0000000 تا 23:59:59.9999999 را می تواند شامل شود. hh اعداد 0 تا 23 را برای نمایش ساعت و mm عددی دو رقمی از 0 تا 59 را برای دقیقه و ss عددی دو رقمی از 0 تا 59 را برای نمایش ثانیه می تواند در بر گیرد. n صفر تا هفت رقمی است که بیانگر کسر ثانیه می باشد و محدوده ارقام 0 تا 9999999 برای آن لحاظ شده است. دقت این نوع داده 100 نانو ثانیه می باشد و اندازه حافظه مورد نیاز برای ذخیره سازی آن 5 بایت و ثابت است .

3- Datetime2 : فرمت آن به صورت YYYY-MM-DD hh:mm:ss [.fractional seconds] می باشد و محدوده تاریخی 01-01-0001 تا 31-12-9999 را با مقدار پیش فرض 00:00:00 01-01-1900 شامل می شود n می تواند صفر تا هفت رقم در محدوده ی 0-9999999 را برای نمایش کسر اعشار در بر گیرد. دقت آن 100 نانو ثانیه می باشد . حافظه مورد نیاز برای ذخیره ی ارقام با دقت 3 مقدار 3 بایت برای دقت های 3 و 4 حدود 7 بایت و برای دقت های دیگر 8 بایت می باشد.

4- Datetime : ستون هایی که این نوع داده برای آنها مشخص شده است می توانند تاریخ های January 1, 1753 تا December 31, 9999 و ساعت را از 00:00:00 تا 23:59:59.997 را در خود جای دهند. مقدار پیش فرض این داده در SQL Server برابر با 00:00:00 01-01-1900 است. با توجه به فرمت در نظر گرفته شده hh:mm:ss [.nnnnnn] برای ساعت nمی تواند صفر تا سه رقم از محدوده اعداد 0 تا 999 را برای بخش کسر ثانیه شامل شود. دقت آن اعداد گرد شده رو به افزایش از 0.000 ، 0.003 یا 0.007 ثانیه می باشد و فضای لازم برای ذخیره سازی آن 8 بایت است.

5- Smalldatetime : برای ذخیره تاریخ های 01-01-1900 تا 06-06-2079 و نیز ساعت های 00:00:00 23:59:59 از smalldatetime با دقت یک دقیقه استفاده می شود. نکته ای که در این نوع داده مطرح می باشد این است که ثانیه هایی که از 29.998 و یا کمتر از آن هستند به نزدیکترین دقیقه ی قبلی و نیز ثانیه هایی که 29.999 و یا بیشتر از این مقدار هستند به نزدیکترین دقیقه ی بعد این مقدار گرد می شوند . به عنوان مثال مقدار 2007-05-09 23:59:59 به مقدار 2007-05-10 00:00:00 گرد و تبدیل می شود. مقدار پیش فرض در این نوع داده 00:00:00 01-01-1900 می باشد و حافظه ی مورد نیاز برای ذخیره سازی آن 4 بایت و ثابت است.

6- Datetimeoffset : مقادیر 01-01-0001 تا 31-12-9999 را برای تاریخ و 00:00:00 تا 23:59:59.9999999 را برای ساعت شامل می شود. فرمت آن به شکل YYYY-MM-DD hh:mm:ss [.nnnnnn] می باشد و نیز برای جبران زمانی منطقه از فرمت hh:mm] {-//+}] در نظر گرفته شده است. با توجه به بخش [{+//-}hh:mm] [.nnnnnnn] مقدار n صفر تا هفت رقم در محدوده ی 0 تا 9999999 بوده و hh دو رقم در رنج 14- تا 14 و نیز mm دو رقم در محدوده ی 00 تا 59 را می تواند داشته باشد. محدوده جبران منطقه زمانی برای datetimeoffset از 14:00- تا 14:00+ می باشد. دقت این نوع داده 100 نانو ثانیه بوده و 10 بایت حافظه برای ذخیره سازی به صورت ثابت نیاز دارد.

اگر در حال توسعه یک اپلیکیشن جدید هستید، باید از انواع داده های time ، date ، datetime2 و datetimeoffset استفاده کنید. زیرا این انواع با استاندارد SQL سازگارتر هستند. علاوه بر این، time ، datetime2 و datetimeoffset دارای دقت ثانیه بیشتری هستند و datetimeoffset از منطقه زمانی (time zone) پشتیبانی می کند.

انواع داده های رشته کاراکتری (Character strings)

انواع داده های character string یا رشته های کاراکتری در SQL به شما امکان می دهد داده های با طول ثابت (char) یا با طول متغیر (varchar) را ذخیره کنید. نوع داده متنی می تواند داده های غیر یونیکد را در صفحه کد سرور ذخیره کند.

1- Char(n) : این نوع داده دارای طول ثابتی بوده و n بایت داده در آن بصورت Non-Unicode نگهداری می شود ( n اندازه ذخیره سازی است و می تواند از 1 تا 8000 باشد). اگر در ستونی مقدار مثلاً 25 کاراکتر را برای charدر نظر بگیریم و داده وارد شده کمتر از این مقدار باشد، فضای باقی مانده با space از سمت راست پر خواهد شد که به هنگام رویت داده، این فضا نادیده گرفته می شود.

2- Varchar(n) : دارای طول متغیر بوده، کاراکترهای آن بصورت Non-Unicode هستند و می تواند ارزشی از 1 تا 8000 داشته باشد. داده وارد شده می تواند 0 کاراکتری نیز باشد. اندازه ذخیره سازی این نوع داده ، اندازه ی طول واقعی اطلاعات وارد شده + 2 بایت است. در varchar برخلاف char داده وارد شده تنها به اندازه کاراکترهای وارد شده فضا اشغال می کند و در آن می توان حداکثر طول کاراکتر را مشخص کرد به عنوان مثال varchar(6) نشان می دهد که این نوع داده شامل 6 کاراکتر بوده و حداکثر می تواند شش کاراکتر را در خود ذخیره کند.

3- Varchar(max) : این نوع داده طول متغیر داشته و کاراکتر های آن به صورت non-Unicode هستند. حداکثر حجم ذخیره سازی این نوع داده 1-31^2 یا (2,147,483,647) می باشد (2GB) . داده ی وارد شده می تواند null یا 0 کاراکتر باشد. به اینگونه نوع داده ها large-value data type می گویند.

4- Text : نوع داده ی text نیز دارای طول متغیر بوده و طول رشته ی آن حداکثر 31^2 یا (2,147,483,647) و بصورت non-Unicode در code page سرور می باشد. وقتی که code page از کاراکتر های دو بایتی استفاده می کند، حافظه همچنان 2,147,483,647 است. بسته به نوع رشته کاراکترها، اندازه حافظه و ذخیره سازی ممکن است کمتر از 2,147,483,647 بایت باشد. از این نوع داده برای ذخیره ی داده هایی با بیش از 8000 کاراکتر استفاده می کنند.

انواع داده های رشته کاراکتر یونیکد (Unicode character string)

نوع داده های Unicode String در SQL داده های کاراکتر یونیکد را با طول ثابت (nchar) یا با طول متغیر (nvarchar) ذخیره می کنند.

1- Nchar(n) : این نوع داده طول ثابتی دارد و بایت داده در آن بصورت Unicode نگهداری می شوند ( اندازه ذخیره سازی آن [2*n] است) و چون هر کاراکتر Unicode برای ذخیره سازی به دو بایت نیاز دارد، حداکثر اندازه این نوع 4000 کارکتر است. بنابراین n ارزشی بین 1 تا 4000 را در بر می گیرد. داده ها می توانند رشته هایی تک بایتی یا چند بایتی از حروف، ارقام و سایر سمبل هایی باشند که توسط مجموعه کدهای موجود در database locale پشتیبانی شده اند. همچنین nchar تعداد محدود و مشخصی از کاراکترها را در خود جای می دهد، مثلاً nchar(30) همیشه 30 کاراکتر را ذخیره می کند حتی اگر رشته ای کوتاهتر از 30 کاراکتر به آن اختصاص یابد.

2- Nvarchar(n) : داده ها با طول متغیر در ستون ذخیره می شوند و Unicode می باشند . n می تواند ارزشی بین 1 تا 4000 را شامل شود. داده ی وارد شده می تواند صفر یا Null نیز باشد.

3- Nvarchar(max) : طول داده متغیر و به صورت Unicode است. حداکثر حجم ذخیره سازی این نوع داده 1-31^2 یا (2,147,483,647) می باشد (2GB) . همچنین داده ی وارد شده می تواند صفر یا Null باشد. Navarchar(max) یک large-value data type محسوب می شود.

4- ntext : داده در این نوع طول متغیر داشته،Unicode می باشد و حداکثر طول رشته آن 1-30^2 یا (1,073,741,823) بایت است. حجم ذخیره سازی نیز دو برابر طول رشته ی وارد شده و بر حسب بایت است. معمولا از این نوع داده برای داده هایی با بیش از 8000 کاراکتر استفاده می کنند.

انواع داده های رشته باینری (Binary string)

انواع داده ی Binary String رشته دودویی در SQL داده های باینری با طول ثابت و متغیر را ذخیره می کند.

1- Binary(n) : دارای طول ثابت بوده و n می تواند مقادیر 1 تا 8000 بایت را در بر گیرد. حجم حافظه و ذخیره سازی در آن نیز n است. از باینری زمانی استفاده می شود که طول داده های وارد شده در ستون سازگار باشند.

2- Varbinary(n|max) : داده های باینری طول متغیر دارند. n می تواند مقادیر 1 تا 8000 بایت را شامل شود. Max نیز بیانگر این مطلب است که حداکثر حجم حافظه 1-31^2 بایت است. اندازه ذخیره سازی نیز برابر طول واقعی داده ی وارد شده + 2 بایت است. داده وارد شده می تواند ارزش Null و طول 0 بایت را داشته باشد. عبارت مترادفی که ANSI SQL برای varbinary در نظر گرفته binary varing است. Varbinary هنگامی به کار می رود که اندازه داده های وارد شده در ستون ها بطور قابل توجهی متفاوت باشند و از varbinary max نیز زمانی استفاده می شود که داده های وارد شده در ستون بیش از 8000 بایت باشد.

3- Image : داده های باینری با طول متغیر از 0 تا 1-31^2 یا (2,147,483,647) را شامل می شود. برای ذخیره تصاویر مثلا لوگوی یک شرکت از این نوع داده استفاده می شود. داده یا تصویر ذخیره شده به عنوان رشته ای از بیت ها ذخیره شده و توسط SQL تفسیر نمی شود. تفسیر هر گونه داده تصویری ذخیره شده به کمک Application ها می باشد. مثلا یک Application می تواند یک داده ی موجود در Image column را با فرمت JPEG ذخیره کند. یک Application که داده ها را از Image column می خواند باید به درستی قادر به تشخیص فرمت داده ها و نمایش آنها باشد. یک Image column یک location برای ذخیره رشته بیتها که در واقع value ای را برای داده های تصویری می سازد، فراهم می کند.

دیگر انواع داده در Sql

* Cursor : این نوع داده برای ذخیره ی متغیر ها یا پارامترهای OUTPUT مربوط به store proceduere کاربرد دارد. می توان گفت که cursor به مجموعه ای از سطر ها اشاره دارد. هر متغیری که با این نوع داده ایجاد شود nullable می باشد. باید توجه داشت که این نوع داده نمی تواند به هنگام ایجاد یک table مورد استفاده قرار گیرد.

* sql_variant : برای ذخیره سازی انواع داده های مختلف که SQL Server از آنها پشتیبانی می کند به کار می رود. این نوع داده در ستون ها ، پارامترها ، متغیر ها و مقادیر بازگشتی توابع تعریف شده توسط کاربر استفاده می شود. sql__variant در واقع object های پایگاه داده را قادر می سازد تا datatype های دیگر را ساپورت کنند. یک ستون با نوع sql__variant ممکن است شامل ردیف هایی با انواع داده ی متفاوت باشد، به عنوان مثال برای ستونی که نوع داده ی sql__variant برای آن تعریف شده می تواند مقادیر int یا binary و یا char را شامل شود. این نوع داده به عنوان مقدار پیش فرض نیز در نظر گرفته می شود. Sql__variant حداکثر می تواند طولی برابر با 8016 بایت داشته باشد.

* table : نوع ویژه ای از انواع داده است که می تواند مجموعه ای از نتایج برای پردازش در زمانی دیگر ذخیره نماید.

* rowversion و timetamp : نوع داده rowversion عموماً به عنوان مکانیزمی برای علامت گذاری ردیف های یک جدول استفاده می شود. حافظه ی مورد نیاز برای ذخیره سازی آن 8 بایت است. این نوع داده فقط افزایش یک تعداد شماره را انجام می دهد و قادر به حفظ و نگهداری تاریخ یا ساعت نیست. بنابراین برای ثبت تاریخ یا ساعت از datetime2 استفاده می شود. هر database دارای یک شمارنده است که با درج داده و یا با اجرای عملیات به روزرسانی روی یک table که در بردارنده ی یک ستون از نوع rowversion در پایگاه داده است ، افزایش می یابد. هر جدول می تواند تنها یک ستون از نوع rowversion داشته باشد و هر زمانی که یک ردیف از یک ستون نوع rowversion تغییر یافته و یا در آن درج شود ، مقدار rowversion پایگاه داده افزایش یافته و در ستون موجود با این نوع داده وارد می شود. این ویژگی متغیر بودن باعث می شود که ستون با نوع داده ی rowversion گزینه ی مناسبی برای کلید ها به ویژه برای primary key ها نباشد. نوع دادهtimestamp نیز معادل با rowversion می باشد و از رفتار نوع داده ی مترادف خود تبعیت می کند. در DDL statement ها هر جا که ممکن است از rowversion به جای time stamp استفاده می شود.

نکته : DDL مخفف عبارت Data Definition Language بوده که واژگان آن برای تعریف ساختار داده ی SQL Server 2012 استفاده شده و دستورهای آن برای ایجاد ، تغییر و drop کردن ساختار داده ها در در یک Instance از SQL Server کاربرد دارند.

* Uniqueidentifier : یک ستون یا متغیر local با نوع داده uniqueidentifier می تواند به کمک دو روش زیر مقادیر اولیه را کسب کند: ا- به کمک تابع NEWID و 2- تبدیل یک رشته ی ثابت به فرم xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx که هر x یک عدد هگزا دسیمال در محدوده ی 0 تا 9 یا a تا f را می تواند در برداشته باشد. به عنوان مثال 6F9619FF-8B86-D011-B42D-00C04FC964FF یک مقدار uniqueidentifier معتبر می باشد. همچنین عملگرهای مقایسه ای می توانند با مقادیر uniqueidentifier استفاده می شوند تنها عملیاتی که می توانند در برابر مقادیر uniqueidentifier صورت گیرند انجام مقایسه با کمک عملگرهای (=, <>, <, >, =<, =>) و چک کردن null و یا notnull می باشد.

* Xml : این نوع داده برای ذخیره سازی داده های XML کاربرد دارد . به کمک این نوع داده می توانیم XML instance ها را در یک ستون داشته باشیم و یا یک متغیر از نوع XML را ذخیره کنیم. نمایش instance های ذخیره شده از نوع XML نمی تواند از اندازه 2GB تجاوز کند.

* Hierarchyid : یک نوع داده ی سیستمی با طول متغیر می باشد. از این نوع داده برای نمایش یک position در یک ساختار سلسله مرتبی استفاده می شود. ستون های تعریف شده از نوع hierarchid به صورت خودکار نشان دهنده یک tree نیستند و در واقع یک application است که به تولید و assign کردن مقادیر hierarchyid می پردازد به طوریکه رابطه مورد نظر میان ردیف ها در value ها مشخص شده باشد.