آموزش جامع SQL Server (جلسه ۱۳: تعریف داده‌ها – بخش ۲)
1398/03/11 16:27 , میلاد صاحب نظر

آموزش جامع SQL Server (جلسه ۱۳: تعریف داده‌ها – بخش ۲)

در جلسه قبل به توضیح sequenceها،Identity، CREATE TABLE، DROP SCHEMA، ALTER SCHEMA، CREATE SCHEMA، DROP DATABASE، CREATE DATABASE و ALTER TABLE ADD پرداختیم و در این جلسه به توضیح ادامه این موارد خواهیم پرداخت. با ما همراه باشید!

۱- نصب و آشنایی با SQL Server

۱۰-عملگرهای SET وCTEوPIVOT

۱۹-indexها بخش۱

۲۸-توابع تاریخ یا date بخش۲

۲-نوشتن query

۱۱-تغییر داده‌ها

۲۰-indexها بخش۲

۲۹-توابع رشته‌ای بخش۱

۳-محدود کردن ردیف‌ها

۱۲-تعریف داده‌ها بخش۱

۲۱-stored procedureها بخش۱

۳۰- توابع رشته‌ای بخش۲

۴-فیلتر کردن داده‌ها بخش ۱

۱۴-تعریف داده‌ها بخش۲

۲۲- stored procedureها بخش۲

۳۱- توابع رشته‌ای بخش۳

۵-فیلتر کردن داده‌ها بخش۲

۱۴-data typeهای بخش۱

۲۳- stored procedureها بخش۳

۳۲-توابع سیستمی بخش ۱

۶-اتصال جداول بخش۱

۱۵-data typeها بخش۲

۲۴-توابع تعریف شده توسط کاربر

۳۳-توابع سیستمی بخش۲

۷-اتصال جداول بخش۲

۱۶-ویژگی‌ها

۲۵-triggerها

۳۴-window functionها بخش۱

۸-گروه‌بندی داده‌ها

۱۷-expressionها

۲۶-توابع تجمعی

۳۵-window functionها بخش ۲

۹-subquery

۱۸-viewها

۲۷-توابع تاریخ یا date بخش۱

 

 

توالی یا Sequence در SQL Server

در این قسمت در مورد اشیاء توالی یا Sequence در SQL Server برای ایجاد یک توالی از مقادیر عددی بر اساس ویژگی‌های خاص و مشخص شده خواهید آموخت.

یک توالی یا sequence چیست؟

یک توالی یا Sequence، لیستی از اعداد است که در آن‌ لیست ترتیب اعداد بسیار مهم است. برای مثال، {1,2,3} یک توالی است در حالی که {3,2,1} یک توالی کاملاً متفاوت است.

در SQL Server، توالی یا sequence یک شیء مرتبط با شمای تعریف شده توسط کاربر است که یک توالی از اعداد را با توجه به شرایط مشخص شده ایجاد می‌کند.

یک توالی از مقادیر عددی می‌تواند با ترتیب صعودی یا نزولی تعریف شود و در صورت لزوم حتی می‌تواند به صورت دایره‌وار تکرار شود.

دستور CREATE SEQUENCE در SQL Server

برای ایجاد یک‌یک شیء Sequence جدید، باید از دستور CREATE SEQUENCE به شکل زیر استفاده کنید:

CREATE SEQUENCE [schema_name.] sequence_name  
    [ AS integer_type ]  
    [ START WITH start_value ]  
    [ INCREMENT BY increment_value ]  
    [ { MINVALUE [ min_value ] } | { NO MINVALUE } ]  
    [ { MAXVALUE [ max_value ] } | { NO MAXVALUE } ]  
    [ CYCLE | { NO CYCLE } ]  
    [ { CACHE [ cache_size ] } | { NO CACHE } ];

اجازه دهید syntax را به صورت دقیق بررسی کنیم:

Sequence_name یک نام برای Sequence مشخص می‌کند که این نام باید در پایگاه داده فعلی منحصر به فرد باشد.

AS integer_type می‌تواند از هر گونه نوع integer قابل قبولی برای Sequence استفاده ‌کند (مانند BIGINT، INT، SMALLINT، TINYINT یا DECIMAL و NUMERCI با مقیاس صفر. به طور پیش‌فرض، شیء sequence از BIGINT استفاده می‌کند.

START WITH start_value

اولین مقداری که sequence بازمی‌گرداند را مشخص می‌کند. Start_value باید بین بازه (min_value, ax_value) باشد.

در توالی صعودی مقدار start_value به صورت پیش‌فرض min_value و در توالی نزولی مقدار پیش‌فرض max_value است.

INCREMENT BY increment_value

وقتی تابع NEXT VALUE FOR را فراخوانی می‌کنید، مقدار increment_value از شیء Sequence را مشخص می‌کند.

اگر increment_value منفی باشد، شیء Sequence نزولی است، در غیر این صورت، شیء sequence صعودی است. توجه کنید که مقدار increment_value نمی‌تواند صفر باشد.

[{MINVALUE [min_value]} | {NO MINVALUE}]

میزان حداقل را برای شیء sequence مشخص می‌کند. مقدار پیش‌فرض آن برابر با حداقل مقدار نوع داده‌ شیء Sequence است (برای مثال صفر برای TINUYINT و برای بقیه نوع‌های داده یک مقدار منفی).

[{MAXVALUE [max_value]} |{ NO MAXVALUE}]

حداکثر شیء Sequence را مشخص می‌کند. مقدار پیش‌فرض آن برابر با مقدار حداکثر نوع داده شیء sequence است.

[CYCLE | {NO CYCLE}]

اگر می‌خواهید مقدار شیء Sequence برای شیء Sequence صعودی از min_value، یا برای شیء sequence نزولی از max_value مجدداً شروع شود یا وقتی مقدار موجود از min_value یا max_value کمتر یا بیشتر شد می‌خواهید یک Exception ایجاد کنید، از CYCLE استفاده کنید.

SQL Server برای اشیاء Sequence جدید به صورت پیش‌فرض از NO CYCLE استفاده می‌کند.

[{CACHE [cache_size]} | {NO CACHE}]

با حداقل کردن تعداد disk I/O مورد نیاز برای ایجاد ارقام Sequence، تعداد مقادیری که باید cache شوند تا عملکرد Sequence بهبود یابد را مشخص می‌کند. به طور پیش‌فرض، SQL Server از NO CACHE برای اشیاء sequence جدید استفاده می‌کند.

مثال‌های Sequence در SQL Server

اجازه دهید چند مثال برای ایجاد Sequenceها بزنیم.

الف) مثال ایجاد یک Sequence ساده

کد زیر از دستور CREATE SEQUENCE برای ایجاد یک sequence جدید به نام item_counter با نوع integer (یاINT) استفاده می‌کند که از ۱۰ شروع شده و ۱۰ تا ۱۰ تا به مقادیر آن اضافه می‌‌شود:

CREATE SEQUENCE item_counter
    AS INT
    START WITH 10
    INCREMENT BY 10;

می‌توانید شیء sequence در در تصویر زیر در قسمت Programmability>Sequences مشاهده کنید:

می‌توانید شیء sequence در در تصویر زیر در قسمت Programmability>Sequences مشاهده کنید:

کد زیر مقدار فعلی sequence یا توالی item_counter را برمی‌گرداند:

SELECT NEXT VALUE FOR item_counter;

خروجی به این شکل است:

Current_value
-------------
10
 
(1 row affected)

در این مثال، تابع NEXT VALUE FOR یک توالی اعداد از شیء Sequence به نام item_counter ایجاد می‌کند.

هر دفعه که دوباره کد زیر اجرا شود، خواهید دید که مقدار item_counter ۱۰تا ۱۰تا اضافه می‌شود:

SELECT NEXT VALUE FOR item_counter;

این دفعه خروجی به این شکل است:

Current_value
-------------
20
 
(1 row affected)    
ب) مثال استفاده از یک شیء Sequence در یک جدول

ابتدا، یک شمای جدید به نام procurement ایجاد کنید:

CREATE SCHEMA procurement;
GO

بعد؛ یک جدول جدید به نام orders ایجاد کنید:

CREATE TABLE procurement.purchase_orders(
    order_id INT PRIMARY KEY,
    vendor_id int NOT NULL,
    order_date date NOT NULL
);

سپس، یک شیء sequence جدید به نام order_number ایجاد کنید که از ۱ شروع شود و یکی‌یکی افزایش یابد:

CREATE SEQUENCE procurement.order_number 
AS INT
START WITH 1
INCREMENT BY 1;

پس از آن، سه ردیف به جدول procurement.purchase_orders اضافه کنید و از مقادیر ایجاد شده توسط Sequence یا توالی procurement.order_number استفاده کنید:

INSERT INTO procurement.purchase_orders
    (order_id,
    vendor_id,
    order_date)
VALUES
    (NEXT VALUE FOR procurement.order_number,1,'2019-04-30');
 
 
INSERT INTO procurement.purchase_orders
    (order_id,
    vendor_id,
    order_date)
VALUES
    (NEXT VALUE FOR procurement.order_number,2,'2019-05-01');
 
 
INSERT INTO procurement.purchase_orders
    (order_id,
    vendor_id,
    order_date)
VALUES
    (NEXT VALUE FOR procurement.order_number,3,'2019-05-02');

در آخر، محتوای جدول procurement.purchase_orders را مشاهده کنید:

SELECT 
    order_id, 
    vendor_id, 
    order_date
FROM 
    procurement.purchase_orders;

خروجی به این شکل است:

نتایج

پ) مثال استفاده از یک شیء sequence

اول، یک شیء Sequence جدید ایجاد کنید:

CREATE SEQUENCE procurement.receipt_no
START WITH 1
INCREMENT BY 1;

دوم، جداول procurement.goods_receipts و procurement.inovice_receipts را ایجاد کنید:

CREATE TABLE procurement.goods_receipts
(
    receipt_id   INT PRIMARY KEY 
        DEFAULT (NEXT VALUE FOR procurement.receipt_no), 
    order_id     INT NOT NULL, 
    full_receipt BIT NOT NULL,
    receipt_date DATE NOT NULL,
    note NVARCHAR(100),
);
 
 
CREATE TABLE procurement.invoice_receipts
(
    receipt_id   INT PRIMARY KEY
        DEFAULT (NEXT VALUE FOR procurement.receipt_no), 
    order_id     INT NOT NULL, 
    is_late      BIT NOT NULL,
    receipt_date DATE NOT NULL,
    note NVARCHAR(100)
);

توجه کنید که هر دو جدول‌ها دارای receipt_id هستند که مقادیر آن‌ها از Sequence یا توالی procurement.receipt_no گرفته شده است.

سوم، چند ردیف به هر دو جداول اضافه کنید بدون اینکه به مقادیر ستون‌های receipt_id دست بزنید:

INSERT INTO procurement.goods_receipts(
    order_id, 
    full_receipt,
    receipt_date,
    note
)
VALUES(
    1,
    1,
    '2019-05-12',
    'Goods receipt completed at warehouse'
);
INSERT INTO procurement.goods_receipts(
    order_id, 
    full_receipt,
    receipt_date,
    note
)
VALUES(
    1,
    0,
    '2019-05-12',
    'Goods receipt has not completed at warehouse'
);
 
INSERT INTO procurement.invoice_receipts(
    order_id, 
    is_late,
    receipt_date,
    note
)
VALUES(
    1,
    0,
    '2019-05-13',
    'Invoice duly received'
);
INSERT INTO procurement.invoice_receipts(
    order_id, 
    is_late,
    receipt_date,
    note
)
VALUES(
    2,
    0,
    '2019-05-15',
    'Invoice duly received'
);

چهارم، داده را از هر دو جداول query کنید:

SELECT * FROM procurement.goods_receipts;
SELECT * FROM procurement.invoice_receipts;

خروجی به این شکل است:

نتایج

Sequence در برابر ستون‌های Identity

Sequenceها بر خلاف ستون‌های identity مرتبط با یک جدول نیستند. ارتباط بین Sequence و جدول توسط برنامه‌ها کنترل می‌شود. علاوه بر این، یک sequence را می‌توان در بین چندین جدول به اشتراک گذاشت.

جدول زیر تفاوت‌های اصلی بین Sequenceها و ستون‌های identity را نشان می‌دهد:

Property/ ویژگی

Identity

Sequence Object

امکان مشخص کردن حداقل و/یا حداکثر مقادیر increment

نه

بله

امکان تنظیم مجدد مقدار increment

نه

 بله

امکان cache کردن مقدار increment در حال انجام عمل

نه

بله

امکان مشخص کردن نقطه آغاز مقدار increment

بله

بله

امکان مشخص کردن مقدار increment

بله

بله

امکان استفاده در چندین جدول

نه

بله

زمان استفاده از sequenceها

در موارد زیر، به جای یک ستون identity، از یک شیء sequence استفاده می‌شود:

  • برنامه قبل از وارد کردن مقادیر به داخل جدول به یک عدد نیاز داشته باشد.

  • برنامه به اشتراک گذاری اعداد در بین چندین جدول یا چندین ستون در یک جدول مشابه نیاز دارد.

  • برنامه نیاز دارد که وقتی به یک عدد مشخص رسید، دوباره از اول اعداد را آغاز کند.

  • برنامه لازم دارد که چندین عدد هم‌زمان اختصاص داده شوند. توجه کنید که می توانید stored procedure به نام sp_sequence_get_range را فراخوانی کنید تا به یک باره چندین عدد در یک توالی را بازگرداند.

  • برنامه باید مشخصات یک توالی (مانند مقدار حداکثر) را تغییر دهد.

به دست آوردن اطلاعات sequenceها

برای به دست آوردن اطلاعات دقیق از sequenceها، از view به نام sys.sequences استفاده می‌کنیم:

SELECT 
    * 
FROM 
    sys.sequences;

به این صورت، باsequenceها و ایجاد یک توالی از اعداد با شرایط مشخص شده نیز به صورت مثال محور آشنا شدید.

ستون ALTER TABLE ADD در SQL Server

در این قسمت در مورد دستور ALTER TABLE ADD برای افزودن یک یا چند ستون به یک جدول توضیح خواهیم داد.

کد زیر یک ستون جدید به یک جدول اضافه می‌کند:

 

ALTER TABLE table_name
ADD column_name data_type column_constraint;

در این کد:

  • ابتدا، نام جدولی که می‌خواهید به آن یک ستون جدید اضافه کنید مشخص شده است.
  • سپس، نام ستون، data type آن و ویژگی‌های آن (در صورت امکان) مشخص شده‌اند.

اگر می‌خواهید با استفاده از دستور ALTER TABLE چند ستون را یک باره به یک جدول متصل کنید، می‌توانید از syntax زیر استفاده کنید:

ALTER TABLE table_name
ADD 
    column_name_1 data_type_1 column_constraint_1,
    column_name_2 data_type_2 column_constraint_2,
    ...,
    column_name_n data_type_n column_constraint_n;

در این syntax،، یک لیست از ستون‌ها (که با ویرگول از هم جدا شده‌اند) را مشخص می‌کنید که بعد از کلیدواژه ADD قرار گرفته و قصد دارید آن‌ها را به یک جدول اضافه کنید.

مثال‌های ستون ALTER TABLE ADD در SQL Server

کد زیر یک جدول جدید به نام Sales.quotations ایجاد می‌کند:

CREATE TABLE sales.quotations (
    quotation_no INT IDENTITY PRIMARY KEY,
    valid_from DATE NOT NULL,
    valid_to DATE NOT NULL
);

برای اضافه کردن یک ستون جدید به نام description به جدول sales.quotations، باید از کد زیر استفاده کنید:

ALTER TABLE sales.quotations 
ADD description VARCHAR (255) NOT NULL;

کد زیر دو ستون جدید به نام‌های amount و customer_name را به جدول sales.quotations اضافه می‌کند:

ALTER TABLE sales.quotations 
    ADD 
        amount DECIMAL (10, 2) NOT NULL,
        customer_name VARCHAR (50) NOT NULL;

بسیار خب، به این شکل چگونگی استفاده از کد ALTER TABLE ADD برای اضافه کردن یک یا تعداد بیشتری ستون به یک جدول را نیز آموختید.

دستور ALTER TABLE ALTER COLUMN در SQL Server

در این قسمت به آموزش چگونگی استفاده از دستور ALTER TABLE ALTER COLUMN برای اصلاح یک ستون از جدول خواهیم پرداخت.

SQL Server به شما امکان می‌دهد تا تغییرات زیر را در یک ستون موجود از یک جدول اعمال کنید:

  • اصلاح نوع داده
  • تغییر سایز
  • اضافه کردن ویژگی NOT NULL
اصلاح نوع داده ستون

برای اصلاح نوع داده یک ستون، می‌توانید از کد زیر استفاده کنید:

ALTER TABLE table_name 
ALTER COLUMN column_name new_data_type(size);

نوع داده جدید باید با نوع داده قبلی سازگار باشد، در غیر این صورت اگر ستون داده‌‌ای داشته باشد و نتواند آن را تبدیل کند، یک ارور تبدیل دریافت خواهید کرد.

مثال زیر را مشاهده کنید.

ابتدا، یک جدول جدید با ستونی که نوع داده آن INT است ایجاد می‌کنیم:

CREATE TABLE t1 (c INT);

دوم، چند ردیف به جدول اضافه می‌کنیم:

 INSERT INTO t1
    VALUES
        (1),
        (2),
        (3);

سوم، نوع داده ستون را از INT به VARCHAR تغییر می‌دهیم:

ALTER TABLE t1 ALTER COLUMN c VARCHAR (2);

چهارم، یک ردیف جدید با داده از نوع رشته حروف اضافه می‌کنیم:

2
3
INSERT INTO t1
VALUES
    ('@');

پنجم، نوع داده ستون را از VARCHAR به INT بازمی‌گردانیم:

ALTER TABLE t1 ALTER COLUMN c INT;

SQL Server ارور زیر را می‌دهد:

Conversion failed when converting the varchar value '@' to data type int.
تغییر سایز یک ستون

کد زیر یک جدول جدید با یک ستون ایجاد می‌کند که نوع داده آن (VARCHAR(10 است:

CREATE TABLE t2 (c VARCHAR(10));

اجازه دهید چند نمونه داده به جدول t2 وارد کنیم:

INSERT INTO t2
VALUES
    ('SQL Server'),
    ('Modify'),
    ('Column')

می‌توانید سایز ستون را به این شکل افزایش دهید:

ALTER TABLE t2 ALTER COLUMN c VARCHAR (50);

به هر حال، وقتی سایز ستون را کاهش می‌دهید، SQL Server داده موجود را بررسی می‌کند تا ببیند آیا می‌تواند داده را بر اساس سایز جدید تبدیل کند یا خیر. اگر تبدیل موفقیت آمیز نبود، SQL Server کد را متوقف کرده و یک پیغام ارور می‌دهد.

برای مثال، اگر سایز ستون C را به ۵ کاراکتر کاهش دهید:

ALTER TABLE t2 ALTER COLUMN c VARCHAR (5);

SQL Server ارور زیر را می‌دهد:

String or binary data would be truncated.
اضافه کردن ویژگی NOT NULL به یک ستون قابل NULL شدن

کد زیر یک جدول جدید با یک ستون قابل NULL شدن ایجاد می‌کند:

CREATE TABLE t3 (c VARCHAR(50));

کد زیر چند ردیف به جدول اضافه می‌کند:

INSERT INTO t3
VALUES
    ('Nullable column'),
    (NULL);

اگر می‌خواهید ویژگی NOT NULL را به ستون C اضافه کنید، باید ابتدا NULL را به non-null به روز رسانی کنید. برای مثال:

UPDATE t3
SET c = ''
WHERE
    c IS NULL;

و سپس ویژگی NOT NULL را اضافه کنید:

ALTER TABLE t3 ALTER COLUMN c VARCHAR (20) NOT NULL;

به این شکل، چگونگی استفاده از دستور ALTER TABLE ALTER COLUMN در SQL Server برای تغییر چند ویژگی از یک ستون موجود را آموختید.

دستور ALTER TABLE DROP COLUMN در SQL Server

گاهی، لازم است یک یا چند ستون استفاده نشده یا رها شده را از یک جدول حذف کنید. برای این منظور از دستور ALTER TABLE DROP COLUMN به این شکل استفاده می‌کنیم:

ALTER TABLE table_name
DROP column_name;

در این syntax:

  • ابتدا، نام جدولی که قرار است از آن ستون حذف شود مشخص شده است.
  • سپس، نام ستونی که می‌خواهیم حذف کنیم را مشخص کرده‌ایم.

اگر ستونی که می‌خواهیم حذف کنیم دارای ویژگی CHECK باشد، باید ابتدا ویژگی را حذف و سپس ستون را حذف کنیم. همچنین، SQL Server به ما اجازه نمی‌دهد ستونی که دارای یک PRIMARY KEY یا FOREIGN KEY می‌باشد را حذف کنیم.

اگر می‌خواهید چندین ستون را یک باره حذف کنید، باید از syntax زیر استفاده کنید:

ALTER TABLE table_name
DROP column_name_1, column_name_2,...;

در این syntax، ستون‌هایی که می‌خواهید حذف شوند را به شکل یک لیست از ستون‌های مجزا شده با ویرگول در مقابل دستور DROP مشخص می‌کنید.

مثال‌های دستور ALTER TABLE DROP COLUMN در SQL Server

اجازه دهید یک جدول جدید به نام Sales.price_lists برای نمایش ایجاد کنیم.

CREATE TABLE sales.price_lists(
    product_id int,
    valid_from DATE,
    price DEC(10,2) NOT NULL CONSTRAINT ck_positive_price CHECK(price >= 0),
    discount DEC(10,2) NOT NULL,
    surcharge DEC(10,2) NOT NULL,
    note VARCHAR(255),
    PRIMARY KEY(product_id, valid_from)
); 

کد زیر ستون note را از جدول price_lists حذف می‌کند:

ALTER TABLE sales.price_lists
DROP COLUMN note;

ستون price دارای ویژگی CHECK است، بنابراین نمی‌توانید آن را حذف کنید. اگر سعی کنید کد زیر را اجرا کنید، سیستم به شما ارور می‌دهد:

ALTER TABLE sales.price_lists
DROP COLUMN price;

پیام ارور به این شکل است:

The object 'ck_positive_price' is dependent on column 'price'.

برای حذف ستون DROP:

ابتدا، ویژگی CHECK‌را حذف می‌کنیم:

ALTER TABLE sales.price_lists
DROP CONSTRAINT ck_positive_price;

سپس، ستون price را حذف می‌کنیم:

ALTER TABLE sales.price_lists
DROP COLUMN price;

مثال زیر دو ستون discount و surcharge را هم‌زمان حذف می‌کند:

ALTER TABLE sales.price_lists
DROP COLUMN discount, surcharge;

به این صورت، چگونگی استفاده از دستور ALTER TABLE DROP COLUMN برای حذف یک یا چند ستون از یک جدول را نیز آموختید.

Computed columns در SQL Server

در این قسمت، چگونگی استفاده از Computed columns در SQL Server برای استفاده مجدد از منطق محاسبه‌ای در چندین query را خواهید آموخت.

مقدمه‌ای بر Computed columns در SQL Server

اجازه دهید یک جدول جدید به نام persons برای نمایش ایجاد کنیم:

CREATE TABLE persons
(
    person_id  INT PRIMARY KEY IDENTITY, 
    first_name NVARCHAR(100) NOT NULL, 
    last_name  NVARCHAR(100) NOT NULL, 
    dob        DATE
);

حالا دو ردیف به جدول persons اضافه می‌کنیم:

INSERT INTO 
    persons(first_name, last_name, dob)
VALUES
    ('John','Doe','1990-05-01'),
    ('Jane','Doe','1995-03-01');

برای نوشتن query برای به دست آوردن نام‌ کامل افراد در جدول persons، معمولاً از تابع ()CONCAT یا عملگر + به این شکل استفاده می‌کنیم:

SELECT
    person_id,
    first_name + ' ' + last_name AS full_name,
    dob
FROM
    persons
ORDER BY
    full_name;

نتایج

اضافه کردن عبارت full_name در دستور first_name + ‘ ‘ + last_name در هر query کار راحتی نیست.

خوشبختانه، SQL Server یک ویژگی به نام Computed columns برای ما فراهم کرده است که به ما اجازه می‌دهد یک ستون جدید به یک جدول اضافه کنیم که مقدار آن از مقادیر ستون‌های دیگر در همان جدول به دست آمده‌اند.

برای مثال، می‌توانید ستون full_name را با استفاده از دستور ALTER TABLE ADD column به شکل زیر اضافه کنید:

ALTER TABLE persons
ADD full_name AS (first_name + ' ' + last_name);

هر دفعه که یک query برای به دست آوردن داده‌ای از جدول persons می‌نویسیم، SQL Server مقدار ستون full_name را بر اساس عبارت first_name + ‘  ‘ + last_name محاسبه کرده و نتیجه را بازمی‌گرداند:

در زیر یک qery جدید وجود دارد که خیلی کوچک‌تر و جمع و جورتر است:

SELECT 
    person_id, 
    full_name, 
    dob
FROM 
    persons
ORDER BY 
    full_name;

اگر جدول persons را امتحان کنید، می‌توانید ببینید که ستون full_name جدید در لیست ستون‌ها ظاهر می‌شود:

می‌توانید ببینید که ستون full_name جدید در لیست ستون‌ها ظاهر می‌شود

Persisted Computed columns

Computed columns می‌توانند persisted باشند. این بدان معنا است که SQl Server به صورت فیزیکی داده‌های Computed columns را روی دیسک ذخیره می‌کند.

وقتی داده‌ها را در جدول تغییر می‌دهید، SQL Server نتیجه را بر اساس عبارت Computed columns محاسبه کرده و نتایج را در این ستون‌های persisted به صورت فیزیکی ذخیره می‌کند.

وقتی برای به دست آوردن داده‌ای از Persisted Computed columns یک query می‌نویسید، SQL Server فقط باید داده را بدون انجام هیچ محاسبه‌ای بازگرداند. این امر به قیمت اشغال فضای بیشتر در حافظه، از محاسبات بیش از حد جلوگیری می‌کند.

مثال زیر را در نظر بگیرید.

ابتدا، ستون full_name از جدول persons را حذف می‌کنیم:

ALTER TABLE persons
DROP COLUMN full_name;

سپس، ستون fuull_name جدید را با ویژگی PERSISTED به جدول persons اضافه می‌کنیم:

ALTER TABLE persons
ADD full_name AS (first_name + ' ' + last_name) PERSISTED;

توجه کنید که یک Computed column فقط در صورتی PERSISTED است که کد و عبارت آن قطعی یا Deterministic باشد. این بدان معنا است که برای مجموعه‌ای از ورودی‌ها، کد همیشه یک نتیجه ثابت را بازمی‌گرداند.

برای مثال، کد first_name + ‘  ‘ + last_name یک کد قطعی است. به هر حال، تابع ()GETDATE یک تابع غیر قطعی است، چون در هر روز یک مقدار متفاوت بازمی‌گرداند.

این فرمول بر اساس تاریخ تولید و روز، سن فرد را به سال بازمی‌گرداند:

(CONVERT(INT,CONVERT(CHAR(8),GETDATE(),112))-CONVERT(CHAR(8),dob,112))/10000

می‌توانیم از این کد برای تعریف سن به سال در Computed column استفاده کنیم.

کد زیر سعی می‌کند یک Computed column به نام age_in_year را به عنوان یک persisted computed column تعریف کند:

ALTER TABLE persons
ADD age_in_years 
    AS (CONVERT(INT,CONVERT(CHAR(8),GETDATE(),112))-CONVERT(CHAR(8),dob,112))/10000 
PERSISTED;

SQL Server ارور زیر را می‌دهد:

Computed column 'age_in_years' in table 'persons' cannot be persisted because the column is non-deterministic.

اگر ویژگی PERSISTED را حذف کنید، کد باید به درستی کار کند:

ALTER TABLE persons
ADD age_in_years 
    AS (CONVERT(INT,CONVERT(CHAR(8),GETDATE(),112))-CONVERT(CHAR(8),dob,112))/10000;

حالا، می‌توانید سن افراد موجود در جدول persons را به سال به دست آورید:

SELECT 
    person_id, 
    full_name, 
    age_in_years
FROM 
    persons
ORDER BY 
    age_in_years DESC;

نتایج به این شکل است:

نتایج

Syntax برای اضافه کردن computed columns به یک جدول

برای اضافه کردن یک computed column جدید به یک جدول موجود، باید از syntax زیر استفاده کنید:

ALTER TABLE table_name
ADD column_name AS expression [PERSISTED];

در این syntax:

  • اول، نام جدولی که می‌خواهید به آن computed column اضافه کنید مشخص می‌شود.

  • دوم، نام computed column با کدی که مقادیر ستون را بازمی‌گرداند مشخص می‌شود.

  • سوم، اگر کد قطعی است و شما می‌خواهید داده‌های computed column به صورت فیزیکی ذخیره شوند، می‌توانید از ویژگی PERSISTED استفاده کنید.

توجه کنید که می‌توانید یک شاخص یا index به یک persisted computed column اضافه کنید تا سرعت بازگشت داده‌ها از computed column را افزایش دهید. این یک راه حل جایگزین خوب برای شاخص‌ یا indexهای تابعی Oracle یا indexهای کدی PostgreSQL است.

Syntax برای تعریف computed columns در هنگام ایجاد یک جدول جدید

برای تعریف یک computed column در هنگام ایجاد یک جدول، از syntax زیر استفاده می‌شود:

CREATE TABLE table_name(
    ...,
    column_name AS expression [PERSISTED],
    ...
);

به این ترتیب، چگونگی استفاده از computed columns در SQL Server برای استفاده مجدد منطق محاسبه‌ای در چندین query را آموختید.

 

خلاصه

در این دو جلسه در مورد مبحث تعریف داده و دستورات و روش‌های مختلف آن‌ها آموختید. تمام موارد ۱۸ گانه این مبحث مورد بررسی قرار گرفته و با مثال توضیح داده شدند. در جلسه بعدی به مبحث نوع‌های داده در SQL Server خواهیم پرداخت.

با MUG همراه باشید!

 منبع: sqlservertutorial

 مطالب مرتبط

۱۳ دستور مهم SQL که هر برنامه‌نویس باید بداند
چگونه از حملات نفوذ کننده به SQL جلوگیری کنیم؟
 طبقه بندی زبان های برنامه نویسی
روش‌های هوشمندانه برای استفاده از "تلفیق" در SQL
 LINQ(زبان جستجوی یکپارچه)
برنامه نویسی شیءگرا چیست؟

از آخرین دوره های آموزشی و تخفیف ها مطلع شوید

با تکمیل فرم زیر ، از اخبار و اطلاعات به روز برنامه نویسی و تکنولوژی عقب نمانید

آخرین مطالب

آموزش جامع SQL Server (جلسه ۱۲)
آموزش جامع SQL Server (جلسه ۱۲)

دستور UPDATE در SQL Server برای تغییر داده‌های موجود در یک جدول، از دستور UPDATE به شکل زیر استفاده ...

آموزش جامع SQL Server (جلسه ۱۵)
آموزش جامع SQL Server (جلسه ۱۵)

دستور DROP TABLE در SQL Server گاهی، لازم است یک جدول که دیگر استفاده‌ای ندارد را حذف کنید. برای ...

آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)
آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)

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

آخرین دیدگاه ها

دیدگاه خود را درباره این پست بنویسید

فرم ارسال نظرات