Створення користувальницьких стовпців даних

Ці стовпці зберігають дані користувача Формально їх можна розділити на дві категорії: використовувані для ідентифікації людей, місць, подій або дій і використовувані для додаткового опису людей, місць, подій і дій

Таблиці SQL Server можуть містити до 1024 шпальт в той же час вважається, що в таблиці добре сформованою реляційної бази даних їх число не повинно перевищувати двадцяти пяти

Стовпці даних створюються в процесі визначення таблиці і перераховуються в інструкції CREATE TABLE Стовпці перераховуються в круглих дужках, при цьому вказується імя стовпця, який використовується в ньому тип даних, а також додаткові атрибути, обмеження, допустимість порожніх значень і значення за замовчуванням Загальний синтаксис наступний:

CREATE TABLE імя_та бліци (імя_стовпця тип_даних атрибути, імя_стовпця тип_даних атрибути)

Стовпці даних можуть додаватися й в існуючі таблиці за допомогою інструкції ALTER TABLE ADD COLUMN:

ALTER TABLE імя_та бліци

ADD імя_стовпця тип_даних атрибути

Існуючі стовпці можуть бути змінені за допомогою інструкції ALTER TAQBLE ALTER COLUMN:

ALTER TABLE імя_таблиці ALTER COLUMN імя_стовпця

новий_тіп_данних а трибуто

Для того щоб отримати список стовпців поточної бази даних, виконайте запит до уявлень каталогів sysobjects і syscolumns

Типи даних стовпців

Тип даних стовпця служить для двох цілей

■ Він забезпечує перший рівень цілісності даних Символьні дані не можуть бути занесені в стовпці типів дати-часу і числового типу На практиці мені зустрічалися бази даних, всі стовпці яких мали тип nvarchar для полегшення введення даних Однак такий підхід не можна назвати правильним Тип даних відіграє важливу роль як інструмент перевірки допустимості даних, і його краще не обходити увагою

■ Він визначає обсяг дискового простору, що виділяється для стовпця

Символьні типи даних

SQL Server підтримує декілька символьних типів даних (табл 172)

Типи даних, що використовують таблицю Unicode, особливо корисні для зберігання тексту, написаного на декількох мовах Проте за це доводиться розплачуватися подвоєнням займаного простору Деякі розробники використовують для всіх своїх символьних стовпців тип nvarchar, в той час як інші не хочуть його використовувати ні за які гроші Особисто я раджу використовувати дані Unicode тільки для тексту на іноземних мовами в інших випадках краще використовувати типи char, varchar і text

Таблиця 172 Символьні типи даних

Тип даних

Опис

Розмір в байтах

Char (п)

Символьні дані фіксованої довжини Можуть містити до 8000 символів і використовувати прийнятий за замовчуванням порядок і набір символів

Задана довжина, помножена на 1 байт

Nchar (п)

Символьні дані фіксованої довжини в таблиці Unicode

Задана довжина, помножена на 2 байта

Varchar (п)

Символьні дані змінної довжини Можуть містити до 8000 символів і використовувати прийнятий за замовчуванням порядок і набір символів

По 1 байту на символ

varchar(max)

Символьні дані змінної довжини Можуть містити до 2 Гбайт інформації і використовувати прийнятий за замовчуванням порядок і набір символів

По 1 байту на символ

nvarchar(n)

Символьні дані змінної довжини, що зберігають до 8000 символів при використанні порядку, прийнятого за замовчуванням

За 2 байти на символ

nvarchar(max)

Символьні дані змінної довжини, що зберігають до 2 Гбайт Використовується таблиця Unicode і порядок, прийнятий за замовчуванням

За 2 байти на символ

text

Символьні дані змінної довжини, що містять до 2147483647 символів

По 1 байту на символ

ntext

Символьні дані в таблиці Unicode, що містять до 1073741823 символів

За 2 байти на символ

sysname

Користувальницький тип даних, використовуваний для імен таблиць і стовпців, – еквівалент типу nvarchar (128)

За 2 байти на символ

Числові типи даних

SQL Server підтримує кілька числових типів даних (табл 173)

Таблиця 173 Числові типи даних

Тип даних

Опис

Розмір в байтах

bit

1 або 0

1 біт

tinyint

Цілі числа від 0 до 255

1 байт

smallint

Цілі числа від -32768 до 32767

2 байта

int

Цілі числа від -2147483648 до 2147483647

4 байта

bigint

Цілі числа від -2Л63 до 2 ~ 63

8 байтів

decimal АБО numeric

Числа з фіксованою точністю до 10 38 +1

Варіюється залежно від довжини

money

Числа від -2 ~ 63 до 2 ~ 63 з точністю до однієї десятитисячної

8 байтів

Тип даних

Опис

Розмір в байтах

smallmoney

Числа від -214748,3648 до +214748,3647 з точністю до однієї десятитисячної

4 байта

float

Числа з плаваючою комою від-179Е +308

4 байти або 8 байтів в залежності від

до 179Е +308

точності

real

Числа з плаваючою комою з точністю до 24 знаків

4 байта

При роботі з грошовими значеннями особливо ретельно вибирайте тип даних Використання для них типів float і real може привести до помилок округлення Типи даних money і smallmoney мають фіксовану точність в чотири знаки (тобто до однієї сотої копійки) Для деяких монетарних значень клієнту може знадобитися точність до однієї копійки У цьому випадку більш підходящим виявиться тип decimal

Типи даних дати-часу

SQL Server зберігає в одному стовпці дату і час і використовує для цього типи даних date time і smalldatetime (табл 174) Основним ралічіем між цими двома типами є точність та облік сторіччя Якщо в стовпці повинна зберігатися тільки дата і при цьому не повинен враховуватися період до XX століття, то цілком підійде тип small date time Якщо потрібно додатково зберігати значення часу, точності типу smalldatetime може не вистачити

Таблиця 174 Типи даних дати-часу

Тип даних

Опис

Розмір в байтах

datetime

smalldatetime

Значення дати й часу від 1 січня 1753 до 31 грудня 9999 з точністю до трьох мілісекунд

Значення дати й часу з 1 січня 1900 року до 6 червня 2079 з точністю до однієї хвилини

8 байтів 4 байти

Юліанський календар набув чинності з 1 січня 1753 Так як СУБД SQL Server не хоче розбиратися, які нації та релігії і як використовували дати до 1753 роки, вона взагалі виключає їх з розгляду Хоча в загальному випадку це не складає особливої ​​проблеми, деякі історичні та генеалогічні бази даних вимагають використання і більш ранніх дат В якості обхідного маневру я рекомендую використовувати для таких даних стовпець на основі типу char, застосовуючи при цьому тригер або збережену процедуру для перевірки форматування і допустимості дати при введенні

Деякі програмісти (які не є адміністраторами баз даних) вибирають для стовпців дат символьні типи даних, проте це може призвести до плутанини при виконанні перетворень Використовуйте для фільтрації коректних дат функцію isDate ()

Інші типи даних

Інші типи даних перелічені та описані в табл 175 Вони здатні покрити потреби довічних обєктів і варіантних даних

Таблиця 175 Інші типи даних

Тип даних

Опис

Розмір в байтах

timestamp

Унікальне в межах бази даних випадкове значення, що генерується при кожному оновленні

8 байтів

uniqueidentifier

Генерується системою 16-байтовое значення

16 байтів

binary(n)

Дані фіксованої довжини аж до 8000 байтів

Визначається довжиною

binary(max)

Дані фіксованої довжини аж до 8000 байтів

Визначається довжиною

varbinary

Двійкові дані змінної довжини аж до 8000

Число використовуваних

байтів

байтів

image

Двійкові дані змінної довжини аж до

Число використовуваних

2147483647 байтів

байтів

sql_variant

Може зберігати будь-які типи даних довжиною до 2147483647 байтів

Тип даних timestamp, раніше відомий як rowversion, може стати в нагоді для пошуку втрачених оновлень (детально про нього йтиметься у розділі 51) Тип uniqueidentif ier стане в нагоді для створення первинних ключів, особливо в репліцируємих базах даних Більш докладно про це типі див вище, в розділі Створення первинних ключів.

Обчислювані стовпці

Обчислювані стовпці мають в таблицях особливу цінність, оскільки містять результат зумовленого вираження так само, як і уявлення (тобто збережені інструкції SELECT), однак без зайвого навантаження на систему Такі стовпці не зберігають реальних даних – вони їх обчислюють в момент виконання запиту

Обчислювані стовпці також позитивно впливають на цілісність даних Вони обчислюють значення на рівні таблиці, не довіряючи цю операцію кожному конкретному користувачеві За ним можуть навіть створюватися індекси

Синтаксис обчислюваних стовпців протилежний присвоєнню псевдоніма: імя_стовпця AS вираз

Таблиця OrderDetail навчальної бази даних OBXKites містить обчислюваний стовпець для розширеної ціни, що і продемонстровано далі в її визначенні:

CREATE TABLE dboOrderDetail (

Quantity NUMERIC(7,2) NOT NULL,

UnitPrice MONEY NOT NULL,

ExtendedPrice AS Quantity * UnitPrice Persisted,

)

ON [Primary]

Go

У версії SQL Server 2005 обчислювані стовпці можуть залишатися на диску Новинка ^ ^то значно підвищує продуктивність бази даних при виконанні 2005 пошуку

Обмеження і значення стовпців за умовчанням

База даних завжди настільки хороша, наскільки хороші її дані Обмеження являють собою високошвидкісну перевірку допустимості значень або правил бізнес-логіки, виконувану на рівні ядра бази даних Крім перевірки типу даних, SQL Server містить пять типів обмежень

■ Обмеження первинного ключа Гарантують унікальність первинного ключа і відсутність у ньому порожніх значень

■ Обмеження зовнішнього ключа Гарантують вказівку значення на допустимий ключ

■ Допустимість порожніх значень Перевіряють наявність в стовпці порожніх значень, якщо вони неприпустимі

■ Обмеження перевірки Ці булеві обмеження задаються користувачем

■ Обмеження на унікальність Гарантують унікальність значень

SQL Server також пропонує використання параметрів стовпців

■ Значення за замовчуванням Якщо інструкція INSERT не вставляти в стовпець ніякого значення, в нього підставляється значення за замовчуванням

Значення стовпця, задані за замовчуванням, на одній із сторінок Books Online розглядаються як один з типів обмежень, проте на іншій сторінці в списку обмежень вони не згадані Я вважаю це властивістю стовпця, так як насправді ніяких перевірок введення даних користувачем не проводиться Також не форсується підтримку цілісності даних Водночас значення, задані за замовчуванням, є цінним параметром стовпчика

Допустимість порожніх значень

Пусте значення можна розцінювати як невідоме Як правило, порожні значення зявляються при неповному введенні користувачем даних у рядок

Додаткова Про те, як визначати, виявляти і обробляти порожні значення, див в розділі 8

інформація

Чи допустимі в стовпці порожні значення, визначається за допомогою атрибуту стовпця NULL або NOT NULL

За замовчуванням у нових стовпцях SQL Server передбачає неприпустимість порожніх значень, проте цей режим роботи можна змінити за допомогою властивості підключення ansi_ null_df lt_on Стандарт ANSI за замовчуванням передбачає допустимість порожніх значень, якщо, звичайно, у визначенні стовпця не було явно вказано NOT NULL

Так як за замовчуванням SQL Server і стандарт ANSI припускають протилежні режими допустимості порожніх значень, краще не покладатися на випадок і при визначенні стовпця явно вказувати параметр null або not null

У наступному прикладі продемонстровані режими роботи з порожніми значеннями ANSI і SQL Server, прийняті за замовчуванням У першому прикладі демонструється робота SQL Server Спочатку загальний параметр ansi null встановлюється в помилкове значення false, а параметр підключення ansi null dflt off – в значення on:

USE TempDB

EXEC sp_dboption TempDB# ANSI_NULL_DEFAULT, false;

SET ANSI_NULL_DFLT_OFF ON

Далі створюється таблиця NullTest без явної вказівки допустимості порожніх значень:

CREATE TABLE NullTest(

PK INT IDENTITY,

One VARCHAR(50)

)

Потім ми намагаємося вставити в таблицю пусте значення:

INSERT NullTest(One)

VALUES (NULL)

і в результаті отримуємо помилку:

Server: Msg 515, Level 16, State 2, Line 1 Cannot insert the value NULL into column ’One1, table TempDBdboNullTest; column does not allow nulls INSERT fails

The statement has been terminated

Оскільки режим допустимості порожніх значень був встановлений в прийнятий за замовчуванням в SQL Server на момент створення таблиці, пусте значення таблиця не прийняла У другому прикладі ми перебудуємо цю таблицю з використанням стандартів ANSI:

EXEC sp_dboption TempDB, ANSI_NULL_DEFAULT, true;

SET ANSI_NULL_DFLT_ON ON

DROP TABLE NullTest

CREATE TABLE NullTest(

PK INT IDENTITY,

One VARCHAR(50)

)

Тепер ми намагаємося вставити в неї порожнє значення:

INSERT NullTest(One)

VALUES (NULL)

і отримуємо позитивний результат:

(1 row(s) affected)

Обмеження на унікальність

Обмеження на унікальність аналогічно унікальному індексу або обмеження первинного ключа Воно гарантує те, що в різних рядках стовпчика не міститимуть однакові значення Цей параметр частіше всього використовується, коли в таблиці потрібно забезпечити відсутність дубльованих рядків (наприклад, в списку співробітників унікальним повинен бути ідентифікаційний код, а в списку клієнтів – ОКПО)

У утиліті Management Studio обмеження на унікальність встановлюється у вкладці Index діалогового вікна властивостей таблиці Цей процес ідентичний установці індексу, але з однією відмінністю: в цьому випадку замість індексу вибирається обмеження

У програмному коді обмеження на унікальність можна встановити, встановивши у визначенні стовпця параметр UNIQUE Наведемо приклад:

CREATE TABLE Employee (

EmployeeID INT PRIMARY KEY NONCLUSTERED,

EmployeeNumber CHAR(8)UNIQUE,

LastName NVARCHAR(35),

FirstName NVARCHAR(35)

)

Insert Employee (EmployeelD, EmployeeNumber, LastName, FirstName)

Values( 1, 1, 1 Wilson1, Bob)

Insert Employee (EmployeelD, EmployeeNumber, LastName, FirstName) Values( 2, l1, Smith1, Joe)

В результаті виконання цього пакету інструкцій буде отримана помилка:

Server: Msg 2627, Level 14, State 2, Line 1

Violation of UNIQUE KEY constraint UQ______ Employee____ 68487DD7.

Cannot insert duplicate key in object Employee.

The statement has been terminated

Щоб додати обмеження на унікальність у вже існуючу таблицю, можна використовувати інструкцію ALTER TABLE:

ALTER TABLE Employee

ADD CONSTRAINT EmpNumUnique UNIQUE (EmployeeNumber)

Обмеження перевірки

Обмеження перевірки є перевіркою цілісності даних на рівні рядків Як правило, це невелика формула, яка повертає булево значення true або false Обмеження перевірки мають доступ до всіх даними поточного рядка – вони не можуть звернутися до інших рядках або виконати пошук У обмеження перевірки можуть бути включені скалярні функції, про які ми говорили в розділі 8

Додаткова Обмеження перевірки можуть містити користувальницькі скалярні функції інформація (про них мова піде в главі 22), які можуть виконувати послідовність _, інструкцій Т-SQL В результаті виклик такої функції в обмеженні перевірки

відкриває майже необмежені можливості, в тому числі і можливість складного пошуку Водночас складні перевірки правил бізнес-логіки найчастіше виконуються в тригерах after

Обмеження перевірки корисні для підтримки загальних правил допустимості даних, а також простих правил бізнес-логіки Як приклади можна привести перевірку перевищення датою звільнення дати прийому на роботу (або дати народження плюс 18 років)

Обмеження перевірки на порядок швидше табличних тригерів Якщо деякий правило можна перевірити за допомогою такого обмеження, то використовуйте його, а не тригер

У наступному прикладі обмеження забезпечує перевищення табельною номером співробітника (поле EmployeeNumber) значення 1:

Drop Table Employee CREATE TABLE Employee (

EmployeelD INT PRIMARY KEY NONCLUSTERED,

EmployeeNumber CHAR(8) CHECK (EmployeeNumber &gt 1),

LastName NVARCHAR(35),

FirstName NVARCHAR(35)

)

Insert Employee (EmployeelD, EmployeeNumber, LastName, FirstName)

Values( 2, 1, Smith, Joe)

Виконання цього пакету інструкцій призведе до помилки:

Server: Msg 547, Level 16, State 1, Line 1

INSERT statement conflicted with COLUMN CHECK constraint

‘ CK___ Employee Employ      5FB337D6

The conflict occurred in database ’tempdb1, table Employee1, column 1 EmployeeNumber

The statement has been terminated

Для додавання обмеження перевірки в існуючу таблицю використовують інструкцію ALTER TABLE:

ALTER TABLE Employee

ADD CONSTRAINT NoHireSmith CHECK (Lastname &lt&gt SMITH)

Значення за замовчуванням

Це значення SQL Server вставляє в таблицю, якщо воно не було визначено в інструкції INSERT в явному вигляді Значення за замовчуванням набувають особливого значення, коли в стовпці неприпустимі порожні значення У цьому випадку, якщо не вставити в такий стовпець значення, а значення за замовчуванням у ньому не визначені, вся операція вставки буде відкинута Значення за замовчуванням може бути одним з наступних

■ Допустиме статичне числове або символьне значення, таке як 123 або local

Про Скалярная системна функція, така як GetDate () або NewID ()

■ Скалярная функція, певна користувачем

■ Пусте значення

Тип значення за замовчуванням повинен бути сумісний з типом даних стовпця

Якщо таблиця створюється в Management Studio, то значення за замовчуванням визначається як одна з властивостей стовпця

У програмному коді значення за замовчуванням додається як один з параметрів визначення стовпця при створенні таблиці або вже згодом, за допомогою інструкції ALTER TABLE CREATE CONSTRAINT

Наступний приклад (з скороченнями) взято з визначення таблиці Product навчальної бази даних OBXKites Значним стовпця за замовчуванням ActiveDate є поточна дата: CREATE TABLE dboProduct (

ActiveDate DATETIME NOT NULL DEFAULT GETDATE(),

* * )

Значення за замовчуванням можна встановлювати і після створення таблиці У наступному прикладі для визначення поточних обмежень запускається процедура, що зберігається sp_help, потім обмеження віддаляється і згодом заново встановлюється за допомогою інструкції ALTER TABLE: sp_help Product

Результат буде наступним: constraint_type constraint_name

DEFAULT on column ActiveDate

DF___________________ Product___ ActiveD__ 7F6 0ED5 9

Тепер інструкція ALTER TABLE видаляє існуюче значення за замовчуванням:

ALTER TABLE Product

DROP CONSTRAINT DF Product ActiveD 7F60ED59

після чого ми знову встановлюємо його:

ALTER TABLE Product

ADD CONSTRAINT ActiveDefault DEFAULT GetDateO FOR ActiveDate

Каталог даних

Незважаючи на те що в SQL Server формально відсутнє каталог даних, йому на заміну можуть прийти користувача типи даних Користувача типи являють собою іменовані обєкти, що володіють наступними додатковими властивостями

■ Визначено тип даних і довжина

■ Визначено допустимість порожніх значень

■ Зумовлені правила, які можуть бути застосовані до даного типу даних

■ Зумовлені значення за замовчуванням, застосовні до типу даних

У сильно нормалізованих базах даних, в яких не існує східних даних у різних таблицях, концепція каталогу даних може здатися недоречною Водночас хороший стандарт типу даних у звичайному магазині може виявитися дуже корисним Наприклад, якщо в кожній з баз даних перераховані одні й ті ж співробітники, програмування всіх рівнів полегшується і дозволить уникнути помилок Щоб створити каталог даних з правил, значний за замовчуванням і користувача типів даних, а потім застосувати все це до безлічі баз даних, краще всього створити окремий сценарій DataCatalog sql, а потім запускати його в кожній базі або помістити в базу даних моделі

Правила, визначені користувачем

Правило аналогічно обмеження перевірки, за винятком того, що створюється незалежно і тільки потім застосовується до колонку Правило складається тільки з імені та булева висловлювання Булево вираз може посилатися на дані за допомогою символу @, за яким слідує імя стовпця

У наступному прикладі продемонстровано створення правила, яке перевіряє дні народження і гарантує, що всі вони в минулому:

– Обумовлений користувачем правило

CREATE RULE BirthdateRule AS @Birthdate &lt= Getdate()

Щоб застосувати це правило до колонку таблиці або для користувача типу даних, використовується збережена процедура sp_bindrule Першим аргументом цієї процедури є імя правила, а другий – обєкт, до якого воно застосовується У наступному прикладі вищеописане правило BirthdayRule застосовується до колонку BirthDate таблиці Person:

EXEC sp_bindrule

@rulename = 1BirthdateRule,

@objname = 1 PersonBirthdate;

Правила розглядаються як засіб зворотної сумісності версій SQL Server Їх використання не рекомендовано компанією Microsoft На зміну їм прийшли обмеження перевірки, що поміщаються безпосередньо в стовпці Правила можна розглядати швидше з точки зору практики програмування, а не використання Якщо у вашій базі даних поки ще залишилися правила, то замініть їх обмеженнями перевірки

У утиліті Management Studio правила створюються і застосовуються у вузлі Rules кожної з баз даних У той же час більшість розробників, які використовують правила, виносять їх у зовнішній сценарій

Значення за замовчуванням, визначені користувачем

Значення за замовчуванням легко створити безпосередньо у визначенні таблиці, хоча, подібно правилами, вони продовжують існування виключно з міркувань зворотної сумісності Обєкти значень за замовчуванням являють собою іменовані значення, які можуть бути застосовані до безлічі таблиць Значення за умовчанням можуть створюватися і застосовуватися до стовпців в Management Studio у вузлі Defaults кожної з баз даних

У наступному прикладі створюється значення за замовчуванням, рівне поточній даті, після чого воно застосовується до колонку Hi redate:

CREATE DEFAULT HireDefault AS GetdateО go

sp_bindefault HireDefault1, ContactHiredate;

Користувача типи даних

Користувача типи даних призначають імена системним типам і застосовують до них обмеження допустимості порожніх значень Іменовані користувача типи даних потім можуть використовуватися так само, як і системні, в будь-якому визначенні таблиці

Тип даних SysName розроблений компанією Microsoft спеціально для зберігання системних імен (таблиць, стовпців) в шпальтах

У утиліті Management Studio користувача типи даних створюються у вузлі User-Defined Data Туре кожної з баз даних Для їх створення також може використовуватися системна збережена процедура sp_addtype, якій в якості аргументів передаються імя, тип даних і параметр допустимості порожніх значень У наступному прикладі створюється користувача тип даних, до якого застосовуються значення за замовчуванням і правило, а потім цей тип даних використовується при створенні таблиці:

EXEC sp_addtype

@typename = Birthdate,

@phystype = SmallDateTime,

@nulltype = NOT NULL;

go

EXEC sp_bindefault

@defname = BirthdateDefault,

@objname = Birthdate,

@futureonly = futureonly;

EXEC sp_bindrule

@rulename = BirthdateRule,

@objname = PersonBirthdate;

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*