Підключення до SQL Server засобами Access

Безперебійна робота

Користуючись програмою Access, ви прагнете мінімізувати перерви у роботі, але можливості серверної бази даних Access обмежені. Резервне копіювання бази даних Access важливе для захисту даних, але для цього користувач мусить перейти в автономний режим. Також трапляються незаплановані простої під час обслуговування та оновлення апаратного або програмного забезпечення, простою мережі чи перебою в живленні, неполадок устаткування, порушення системи безпеки або навіть кібератаки. Щоб мінімізувати час простою та його наслідки для бізнесу, можна створити резервну копію бази даних SQL Server під час її використання. Крім того, SQL Server має високу доступність (HA) і стратегію аварійного відновлення (DR). Комбінація цих двох технологій називається HADR. Додаткові відомості див. в публікаціях Безперебійна робота та відновлення баз даних і Забезпечення безперебійної роботи з SQL Server (електронна книга).

Резервне копіювання під час використання

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

Більшість операцій резервного копіювання виконується відразу, крім керування файлами та стискання баз даних. І навпаки, створити або видалити файл бази даних під час резервного копіювання не вдасться. Докладніше див. в статті Відомості про резервне копіювання.

Висока доступність і аварійне відновлення (HADR)

Два найпоширеніших метода забезпечення високої доступності та безперебійної роботи – це віддзеркалення та кластеризація. SQL Server інтегрує технологію віддзеркалення та кластеризації за допомогою екземплярів відмовостійкого кластера AlwaysOn і груп забезпечення доступності AlwaysOn.

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

Кластеризація – це рішення на рівні сервера, яке об'єднує сервери в єдине сховище даних. Користувачі сприймають його як один екземпляр. Користувачі підключаються до цього екземпляра та не цікавляться, який сервер зараз активний. У разі відмови одного із серверів або його переводу в автономний режим для обслуговування взаємодія з користувачем не змінюється. Диспетчер кластерів контролює кожен сервер у кластері за допомогою підтвердження функціонування. Він визначає, коли активний сервер у кластері переходить у автономний режим, і плавно перемикається на інший сервер. Затримка під час перемикання буває різною.

Додаткові відомості див. в статтях Екземпляри відмовостійкого кластера AlwaysOn та Групи забезпечення доступності AlwaysOn: висока доступність і аварійне відновлення.

На початок сторінки

Безпека SQL Server

Хоча базу даних Access можна захистити за допомогою шифрування бази даних і Центру безпеки та конфіденційності, SQL Server має додаткові захисні функції. Далі розглядаються три можливості для користувачів Access. Докладні відомості див. в статті Безпека SQL Server.

Автентифікація баз даних

У SQL Server передбачено чотири способи автентифікації баз даних. Будь-який із них можна вказати в рядку підключення ODBC. Докладні відомості див. в статті Зв’язок із даними або імпорт даних із бази даних SQL Server Azure. Кожен спосіб має свої переваги.

Інтегрована автентифікація Windows    Використовуйте облікові дані Windows для перевірки користувачів, створення ролей безпеки та обмеження доступу користувачів до функцій і даних. У програмі можна скористатися обліковими даними домену та легко керувати правами користувачів. За потреби введіть імена учасників служби (SPN). Докладні відомості див. в статті Вибір режиму аутентифікації.

Автентифікація SQL Server    Користувачі підключаються за допомогою облікових даних, збережених у базі даних. Для цього потрібно ввести ідентифікатор входу та пароль під час першого входу в базу даних у сеансі. Докладні відомості див. в статті Вибір режиму аутентифікації.

Інтегрована аутентифікація Azure Active Directory    Підключайтеся до бази даних SQL Server Azure за допомогою Azure Active Directory. Якщо налаштувати автентифікацію Azure Active Directory, додаткові ім’я для входу та пароль непотрібні. Докладні відомості див. в статті Підключення до бази даних SQL за допомогою аутентифікації Azure Active Directory.

Автентифікація за допомогою пароля в Active Directory    Підключайтеся за допомогою облікових даних, збережених у Azure Active Directory. Для цього введіть ім’я для входу та пароль. Докладні відомості див. в статті Підключення до бази даних SQL за допомогою аутентифікації Azure Active Directory.

Порада    Скористайтеся функцією виявлення загроз, щоб отримувати оповіщення про підозрілу активність у базі даних, що вказує на потенційну загрозу порушення безпеки бази даних SQL Server Azure. Докладні відомості див. в статті Виявлення загроз базі даних SQL.

Безпека програм

SQL Server має дві захисні функції на рівні програми, якими можна скористуватися за допомогою Access.

Динамічне маскування даних    Приховайте конфіденційну інформацію, замаскувавши її від непривілейованих користувачів. Наприклад, можна частково або повністю приховати номери соціального страхування.

Часткова маска даних

Повна маска даних

Задати маску даних можна кількома способами. ЇЇ можна застосувати до різних типів даних. Маскування даних передбачено на рівні таблиці та стовпця для певного набору користувачів і застосовується до запитів в режимі реального часу. Докладні відомості див. в статті Динамічне маскування даних.

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

Існує два типи предикатів безпеки.

• Предикат фільтра фільтрує рядки із запиту. Фільтр прозорий, і кінцевий користувач не підозрює про його використання.

• Предикат блокування запобігає несанкціонованим діям і створює виняток, якщо не вдалося виконати дію.

Докладні відомості див. в статті Захист на рівні рядка.

Захист даних за допомогою шифрування

Захищайте дані, коли зберігаєте, передаєте або використовуєте їх, не впливаючи на продуктивність бази даних. Додаткові відомості див. в статті Шифрування SQL Server.

Шифрування даних для збереження    Щоб захистити особисті дані, збережені на автономному носії, на рівні фізичного сховища, скористайтеся шифруванням збережених даних, також відомим як прозоре шифрування даних (TDE). Тоді ваші дані будуть захищені, навіть якщо фізичний носій вкрадено або знищено неналежним чином. Функція TDE виконує шифрування в реальному часі та розшифрування баз даних, резервних копій і журналів транзакцій без жодних змін у ваших програмах.

Шифрування даних для передачі    Щоб захиститися від перехоплення даних та атак за допомогою посередника, можна зашифрувати дані, що передаються через мережу. SQL Server підтримує протокол TLS 1.2 для забезпечення високозахищеного зв’язку. Протокол TDS також використовується, щоб захистити зв'язок у ненадійних мережах.

Шифрування даних для роботи з клієнтом    Щоб захистити особисті дані під час використання, вам потрібна функція Always Encrypted. Драйвер на клієнтському комп’ютері зашифровує та розшифровує особисті дані, не повідомляючи ключі шифрування обробнику баз даних. Тому зашифровані дані бачать лише користувачі, які відповідають за керування цими даними, а не інші привілейовані користувачі, які не надано доступ. Залежно від вибраного типу шифрування функція Always Encrypted може дещо обмежити функціональність бази даних, як-от пошук, групування та індексування зашифрованих стовпців.

На початок сторінки

Вирішення проблем із конфіденційністю

Проблеми з конфіденційністю настільки поширені, що Європейський Союз визначив юридичні вимоги у Генеральному регламенті із захисту персональних даних (GDRP). На щастя, внутрішній сервер SQL Server якнайкраще задовольняє ці вимоги. Далі описано три етапи застосування регламенту GDPR.

Крок 1. Оцінювання ризику невідповідності законодавству та керування ним

Регламент GDPR вимагає визначити особисті відомості, що містяться в таблицях й файлах, і вести їхній облік. Ці відомості можуть стосуватися будь-чого: імені, фотографії, адреси електронної пошти, реквізитів банку, дописів у соціальних мережах, медичної інформації або навіть IP-адреси.

Новий інструмент SQL Data Discovery and Classification, вбудований у SQL Server Management Studio, дає можливість виявити, класифікувати, підписати конфіденційні дані та повідомити про них, застосовуючи два атрибути метаданих до стовпців.

• Підписи    Визначення рівня конфіденційності даних

• Типи відомостей    Подальша деталізація типів даних, збережених у стовпці

Інший механізм виявлення – це повнотекстовий пошук, який включає використання предикатів CONTAINS і FREETEXT і такі функції із значеннями набору рядків, як-от CONTAINSTABLE і FREETEXTTABLE, що застосовуються разом з інструкцією SELECT. За допомогою повнотекстового пошуку можна знайти в таблицях слова, словосполучення або варіанти слова, наприклад синоніми чи флективні форми. Додаткові відомості див. в статті Повнотекстовий пошук.

Крок 2. Захист особистих відомостей

Регламент GDPR вимагає захищати особисті відомості та обмежувати доступ до них. Крім стандартних кроків для керування доступом до мережі та ресурсів, як-от настройки брандмауера, можна використовувати нижче наведені захисні функції SQL Server, щоб керувати доступом до даних.

• Автентифікація SQL Server, щоб керувати ідентифікаційними даними та запобігти несанкціонованому доступу.

• Захист на рівні рядка, щоб обмежити доступ до рядків у таблиці, виходячи із зв'язку між користувачем і цими даними.

• Динамічне маскування даних, щоб обмежити можливість ознайомитися з особистими даними, приховавши їх від непривілейованих користувачів.

• Шифрування, щоб забезпечити захист особистих даних під час передавання та збереження, і захист від порушення конфіденційності, зокрема на сервері.

Докладні відомості див. в статті Безпека SQL Server.

Крок 3. Оперативні відповіді на запити

Регламент GDPR вимагає зберігати записи про обробку особистих даних і надавати їх контрольно-наглядовим органам на їхню вимогу. Якщо виникають проблеми, зокрема випадкове розголошення даних, елементи керування захистом дають змогу швидко відреагувати на них. Якщо потрібно звітувати, слід забезпечити швидкий доступ до даних. Наприклад, регламент GDPR вимагає звітувати контрольно-наглядовому органу про порушення безпеки даних “не пізніше ніж через 72 години після того, як це було виявлено”.

SQL Server 2017 допомагає із звітуванням кількома способами.

• SQL Server Audit забезпечує наявність постійних записів про доступ у базу даних і обробку даних. Ця функція виконує детальну перевірку і відстежує діяльність у базі даних. Це дає змогу виявити потенційні загрози, можливі зловживання та порушення системи безпеки. Ви можете легко провести експертизу даних.

• Темпоральні таблиці SQL Server – це системно-версійні таблиці користувачів, які дають змогу зберегти всю історію змін даних. Їх можна використовувати, щоб легко звітувати і проводити аналіз станом на певний момент часу.

• SQL Vulnerability Assessment дає змогу виявити проблеми з безпекою та дозволами. Коли проблему виявлено, також можна деталізувати звіти бази даних, щоб знайти рішення проблеми.

Докладні відомості див. в публікаціях Створення платформи довіри (електронна книга) і Шляхи виконання регламенту GDPR.

На початок сторінки

Створення знімків баз даних

Знімок бази даних – це статичне подання (лише для читання) бази даних SQL Server у певний момент часу. Хоча файл бази даних Access можна скопіювати, щоб оперативно створити знімок бази даних, у програму Access не вбудовано таку технологію, як-от у SQL Server. Знімок бази даних можна використовувати, щоб готувати звіти на основі даних на момент створення знімка. Крім того, можна використовувати знімок бази даних, щоб зберігати історичні дані, наприклад дані за кожний фінансовий квартал, для зведення звітів за кінець звітного періоду. Радимо дотримуватися таких порад:

• Присвоєння імені знімку    Для кожного знімка потрібне унікальне ім'я в базі даних. Щоб полегшити ідентифікацію знімка, додайте до його імені призначення та часові рамки. Наприклад, зробіть три знімки бази даних AdventureWorks за день з 06:00 до 18:00 (24-годинний формат часу) з інтервалом 6 годин і дайте їм такі імена: AdventureWorks_snapshot_0600, AdventureWorks_snapshot_1200 і AdventureWorks_snapshot_1800.

• Обмеження кількості знімків    Кожний знімок бази даних зберігається, доки він не буде повністю видалений. Оскільки кожний наступний знімок ставатиме більшим, можливо, знадобиться зберегти дисковий простір, видаливши старіший знімок після створення нового. Наприклад, якщо ви створюєте щоденні звіти, зберігайте знімок бази даних протягом 24 годин, а потім видаліть та замініть його на новий.

• Підключення до потрібного знімка    Щоб скористатися знімком бази даних, зовнішній сервер Access має знати правильне розташування. Коли старий знімок замінюється на новий, потрібно переспрямувати програму Access до нового знімка. Додайте логіку до зовнішнього сервера Access, щоб підключитися до потрібного знімка бази даних.

Ось так можна створити знімок бази даних.

CREATE DATABASE AdventureWorks_dbss1800 ON  
( NAME = AdventureWorks_Data, FILENAME =   
'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\AdventureWorks_snapshot_0600' )  
AS SNAPSHOT OF AdventureWorks;  

Докладні відомості див. в статті Знімки бази даних (SQL Server).

На початок сторінки

Керування паралельним доступом

Якщо багато людей одночасно намагаються змінити дані в базі даних, потрібна система керування, щоб зміни, внесені одним користувачем, не впливали негативно на зміни, зроблені іншим користувачем. Це називається керування паралельним доступом. Є дві основні стратегії — песимістичне блокування та оптимістичне блокування. Блокування не дозволяє змінювати дані таким чином, щоб це могло впливати на інших користувачів. Крім того, блокування забезпечує цілісність бази даних, особливо щодо запитів. В іншому випадку результати могли б бути неочікуваними. Бази даних Access і SQL Server мають важливі відмінності в тому, як вони реалізують ці стратегії керування паралельним доступом.

За замовчуванням у програмі Access використовується оптимістичне блокування. Воно надає право на блокування першому користувачу, який намагається змінити запис. У програмі Access відображається діалогове вікно Конфлікт записування для іншого користувача, який намагається змінити той самий запис у той самий час. Щоб усунути конфлікт, інший користувач може зберегти запис, скопіювати його до буфера обміну або видалити зміни.

Крім того, щоб змінити стратегію керування паралельним доступом, можна використовувати властивість RecordLocks. Ця властивість впливає на форми, звіти й запити та має три параметри.

• Без блокувань    Якщо встановити цей параметр у формах, відображатиметься діалогове вікно Конфлікт записування під час спроб одночасно редагувати той самий запис. Якщо встановити цей параметр у звітах, записи не блокуватимуться під час перегляду або друку. Якщо встановити цей параметр у запитах, записи не блокуватимуться під час виконання запиту. Так програма Access застосовує оптимістичне блокування.

• Усі записи    Якщо форму відкрито в поданні форми або у вікні табличного подання даних, усі записи в базовій таблиці або запиті блокуватимуться під час попереднього перегляду або друку звіту, а також під час виконання запиту. Користувачі можуть читати заблоковані записи.

• Редагований запис    Якщо встановити цей параметр для форм і запитів, сторінка записів блокується, коли користувач починає редагувати будь-яке поле запису. Вона лишається заблокованою, допоки користувач не перейде до іншого запису. Одночасно той самий запис може редагувати лише один користувач. Так програма Access застосовує песимістичне блокування.

Докладні відомості див. в статтях Діалогове вікно “Конфлікт записування” та Властивість RecordLocks.

У SQL Server керування паралельним доступом працює ось таким чином.

• Песимістичне    Коли користувач виконує дію, яка спричиняє встановлення блоку, інші користувачі не можуть виконати дії, що конфліктують із механізмом блокування, доки власник не зніме його. Такий спосіб керування паралельним доступом застосовується переважно в середовищах із високим рівнем змагання за можливість користуватися даними.

• Оптимістичне    Якщо встановлено оптимістичне керування паралельним доступом, дані не блокуються, коли користувачі читають їх. Коли користувач оновлює дані, система перевіряє, чи інший користувач не змінив дані після читання. Якщо інший користувач оновив дані, відображається помилка. Побачивши помилку, користувач зазвичай відкочує цю транзакцію й починає нову. Такий спосіб керування паралельним доступом застосовується переважно в середовищах із низьким рівнем змагання за можливість користуватися даними.

Спосіб керування паралельним доступом можна вказати, вибравши кілька рівнів ізоляції транзакції за допомогою інструкції SET TRANSACTION. Вони визначають рівень захисту транзакції від змін, внесених іншими транзакціями.

 SET TRANSACTION ISOLATION LEVEL
 { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ  
    | SNAPSHOT
    | SERIALIZABLE
 }

Докладні відомості див. в публікації Посібник із блокування транзакцій і керування версіями рядків.

На початок сторінки

Покращення продуктивності запитів

Якщо ви вже маєте досвід роботи з наскрізним запитом у базі даних Access, скористайтеся прогресивними засобами SQL Server, щоб працювати ефективніше.

На відміну від бази даних Access, SQL Server створює паралельні запити для оптимізації виконання запитів і операцій з індексами на комп'ютерах із більш ніж одним мікропроцесором (ЦП). Оскільки SQL Server може виконувати запити або операції з індексами паралельно, використовуючи кілька робочих потоків системи, це робиться швидко та ефективно.

Запити – це критично важливий компонент, який покращує загальну продуктивність бази даних. Неприпустимі запити виконуються, допоки не вичерпається час очікування, вони поглинають такі ресурси, як-от ЦП і пам'ять, і діють як бандити в мережі. Це перешкоджає доступу до критично важливих службових відомостей. Навіть один неприпустимий запит може спричинити серйозні проблеми з продуктивністю бази даних.

Додаткові відомості див. в публікації Швидке виконання запитів із SQL Server (електронна книга).

Оптимізація запитів

Щоб проаналізувати продуктивність запитів і покращити її, потрібно відразу кілька інструментів: оптимізатор запитів, плани виконання та сховище запитів.

Оптимізатор запитів

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

Додаткові відомості див. в статті Оптимізатор запитів SQL Server.

План виконання

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

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

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

• зміни, внесені до таблиці або подання, на які посилається запит (інструкції ALTER TABLE і ALTER VIEW);

• змінення індексів у плані виконання;

• оновлення статистики з плану виконання, автоматичне або відповідно до інструкції UPDATE STATISTICS;

Докладні відомості див. в статті Плани виконання.

Сховище запитів

Сховище запитів надає уявлення про вибір плану виконання та продуктивність. Воно спрощує вирішення проблем із продуктивністю допомагаючи швидко знаходити відмінності в продуктивності, спричинені змінами в плані виконання. Сховище запитів збирає телеметричні дані, як-от журнал запитів, плани, статистику середовища виконання та статистику очікування. Щоб реалізувати сховище запитів, скористайтесь інструкцією ALTER DATABASE.

ALTER DATABASE AdventureWorks2012 SET QUERY_STORE = ON;

Докладні відомості див. в статті “Відстеження продуктивності за допомогою сховища запитів.

ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 140;

Способи створення запитів

На сервері SQL Server передбачено кілька способів виконання запитів, і кожен із них має свої переваги. Важливо дізнатися більше про них, щоб зробити правильний вибір для програми Access. Найкращий спосіб створити запити TSQL – інтерактивно редагувати та тестувати їх за допомогою редактора Transact-SQL у SQL Server Management Studio (SSMS). Він має функцію IntelliSense, яка допомагає вибрати потрібні ключові слова та перевірити на наявність синтаксичних помилок.

Подання

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

CREATE VIEW HumanResources.EmployeeHireDate AS  
SELECT p.FirstName, p.LastName, e.HireDate  
FROM HumanResources.Employee AS e JOIN Person.Person AS p  
ON e.BusinessEntityID = p.BusinessEntityID;

Щоб досягти оптимальної продуктивності та редагувати результати подання, створіть індексоване подання. Воно зберігається в базі даних, як-от таблиця, і має свій простір для зберігання. Запит щодо подання створюється так само, як щодо таблиці. Щоб використовувати подання в програмі Access, підключіться до нього так само, як до таблиці. Ось приклад індексованого подання.

CREATE VIEW Sales.vOrders  
WITH SCHEMABINDING  
AS  
    SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,  
        OrderDate, ProductID, COUNT_BIG(*) AS COUNT  
    FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o  
    WHERE od.SalesOrderID = o.SalesOrderID  
    GROUP BY OrderDate, ProductID;  

CREATE UNIQUE CLUSTERED INDEX IDX_V1   
    ON Sales.vOrders (OrderDate, ProductID);  

Проте існують обмеження. Якщо йдеться про кілька базових таблиць або подання містить агрегатні функції чи речення DISTINCT, дані не можна оновлювати. Якщо SQL Server повертає повідомлення про помилку, бо не знає, який запис видалити, можливо, потрібно додати тригер видалення в подання. Крім того, не можна використовувати речення ORDER BY, як це роблять у запиті в програмі Access.

Докладні відомості див. в статтях Подання та Створення індексованого подання.

Збережені процедури

Збережена процедура – це група з однієї або кількох інструкцій TSQL, які одержують вхідні параметри, повертають вихідні параметри та вказують на успіх або відмову за допомогою значення стану. Вони служать проміжним рівнем між зовнішнім сервером Access і внутрішнім сервером SQL Server. Збережені процедури можуть бути простими, як-от інструкція SELECT, або складними, як будь-яка програма. Ось приклад.

CREATE PROCEDURE HumanResources.uspGetEmployees   
    @LastName nvarchar(50),   
    @FirstName nvarchar(50)   
AS   
    SET NOCOUNT ON;  
    SELECT FirstName, LastName, Department  
    FROM HumanResources.vEmployeeDepartmentHistory  
    WHERE FirstName = @FirstName AND LastName = @LastName  
    AND EndDate IS NULL;  

Якщо використовувати збережену процедуру в програмі Access, зазвичай вона повертає результат у вигляді форми або звіту. Але вона може виконувати інші дії, які не повертають результати, як-от інструкції DDL або DML. Під час використання набору запитів переконайтеся, що властивість Returns Records установлено належним чином.

Докладні відомості див. в статті Збережені процедури.

Загальні вирази таблиці

Загальний вираз таблиці (CTE) схожий на тимчасову таблицю, в який створюється результуючий іменований набір. Він існує лише для виконання одного запиту або інструкції DML. Вираз (CTE) вбудований у тому самому рядку коду, що й інструкція SELECT або DML, в якій він використовується, тоді як створення та використання тимчасової таблиці або подання зазвичай складається з двох етапів. Ось приклад.

-- Define the CTE expression name and column list.  
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)  
AS  
-- Define the CTE query.  
(  
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear  
    FROM Sales.SalesOrderHeader  
    WHERE SalesPersonID IS NOT NULL  
)  
-- Define the outer query referencing the CTE name.  
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear  
FROM Sales_CTE  
GROUP BY SalesYear, SalesPersonID  
ORDER BY SalesPersonID, SalesYear;

Вираз CTE має кілька нижче наведених переваг.

• Оскільки вирази CTE тимчасові, їх не потрібно створювати як постійні об'єкти бази даних, як-от подання.

• Ви можете кілька раз посилатися на один і той самий вираз CTE в запиті або інструкції DML і зробити свій код легко керованим.

• Щоб визначити курсор, можна використовувати запити з посиланням на вираз CTE.

Докладні відомості дивіться вWITH common_table_expression.

Користувацькі функції

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

CREATE FUNCTION dbo.ISOweek (@DATE datetime)  
RETURNS int WITH SCHEMABINDING -- Helps improve performance
WITH EXECUTE AS CALLER  
AS  
BEGIN  
     DECLARE @ISOweek int;  
     SET @ISOweek= DATEPART(wk,@DATE)+1  
          -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104');  
-- Special cases: Jan 1-3 may belong to the previous year  
     IF (@ISOweek=0)   
          SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1   
               AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1;  
-- Special case: Dec 29-31 may belong to the next year  
     IF ((DATEPART(mm,@DATE)=12) AND   
          ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))  
          SET @ISOweek=1;  
     RETURN(@ISOweek);  
END;  
GO  
SET DATEFIRST 1;  
SELECT dbo.ISOweek(CONVERT(DATETIME,'12/26/2004',101)) AS 'ISO Week';  

Функція UDF має певні обмеження. Наприклад, вона не може використовувати певні недетерміновані системні функції, інструкції DML або DDL, а також динамічні запити SQL.

Докладні відомості див. у статті Користувацькі функції.

На початок сторінки

Додавання ключів та індексів

Будь-яка система баз даних має ключі та індекси.

Ключі

Створіть у SQL Server первинні ключі для кожної таблиці та зовнішні ключі для кожної пов'язаної таблиці. SQL Server має функцію, еквівалентну функції типу даних для поля лічильника в Access. Це властивість IDENTITY, яку можна використовувати, щоб створювати значення ключів. Якщо застосувати цю властивість до будь-якого числового стовпця, він стає доступним лише для читання та зберігається системою баз даних. Якщо вставити запис у таблицю з ідентифікаційним стовпцем, система автоматично збільшить значення ідентифікаційного стовпця на 1 і починаючи з 1, але можна керувати цими значеннями за допомогою аргументів.

Додаткові відомості див. в статті Властивість CREATE TABLE, IDENTITY.

Індекси

Як завжди, вибір індексів – це пошуки оптимального співвідношення між швидкістю обробки запитів і вартістю оновлення. У програмі Access існує один тип індексу, а на сервері SQL Server – дванадцять. На щастя, за допомогою оптимізатора запитів можна впевнено вибрати найефективніший індекс. У SQL Azure можна скористатися автоматичним керуванням індексами. Це функція автоматичного налаштування, яка радить, коли додати, а коли видалити індекси. На відміну від Access, на сервері SQL Server потрібно створити власні індекси для зовнішніх ключів. Крім того, щоб підвищити продуктивність запитів, можна створити індекси в індексованому поданні. Недолік індексованого подання – це збільшення непрямих витрат, коли змінюються дані в базових таблицях подання, тому що воно також має оновлюватися. Докладні відомості див. в публікаціях Посібник з архітектури та дизайну індексів SQL Server та Індекси.

На початок сторінки

Транзакції

Виконувати онлайнову обробку транзакцій (OLTP) складно з програмою Access, але порівняно легко із SQL Server. Транзакція – це одиниця роботи з даними, яка виконує всі зміни даних у разі успіху та повертає зміни в разі відмови. Транзакція має чотири властивості, які часто називають ACID.

• Атомарність    Транзакція – це неподільна одиниця роботи з даними. Або вона виконує всі зміни, або жодну.

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

• Ізоляція    Зміни, внесені одночасними транзакціями, ізолюються від поточної транзакції.

• Витривалість    Коли транзакцію завершено, зміни набувають остаточний характер навіть у випадку відмови системи.

Транзакція використовується, щоб забезпечити гарантовану цілісність даних, наприклад під час отримання готівки в банкоматі або автоматичного внесення заробітної плати на рахунок. Транзакції бувають явними, неявними та пакетними. Ось два приклади мови TSQL.

-- Using an explicit transaction

BEGIN TRANSACTION;  
DELETE FROM HumanResources.JobCandidate  
    WHERE JobCandidateID = 13;  
COMMIT;  

-- the ROLLBACK statement rolls back the INSERT statement, but the created table still exists.

CREATE TABLE ValueTable (id int);  
BEGIN TRANSACTION;  
       INSERT INTO ValueTable VALUES(1);  
       INSERT INTO ValueTable VALUES(2);  
ROLLBACK;

Докладні відомості див. в статті Транзакції.

На початок сторінки

Обмеження та тригери

Усі бази даних використовують способи збереження цілісності даних.

Обмеження

У програмі Access цілісність даних на рівні таблиць забезпечується за допомогою пар ключів (зовнішній-первинний), каскадного оновлення й видалення та правил перевірки. Докладні відомості див. в публікаціях Посібник із зв’язків між таблицями та Обмеження вводу даних за допомогою правил перевірки.

SQL Server використовує обмеження UNIQUE і CHECK. Це об’єкти бази даних, які забезпечують цілісність даних у таблицях SQL Server. Щоб перевірити припустимість значення в іншій таблиці, використовуйте обмеження зовнішнього ключа. Щоб значення в стовпці знаходилось в межах діапазону, скористайтесь обмеженням Check. Ці об'єкти – перша лінія захисту, і вони створені для ефективної роботи. Докладні відомості див. в статті Обмеження унікальності та обмеження перевірки.

Тригери

У програмі Access немає тригерів баз даних. У програмі SQL Server можна скористатися тригерами, щоб застосовувати правила цілісності складних даних і цю бізнес-логіку на сервері. Тригер бази даних – це збережена процедура, яка запускається, коли в базі даних відбуваються певні дії. Тригер – це подія, як-от додавання запису до таблиці або його видалення, яка запускає та виконує збережену процедуру. Хоча база даних Access може забезпечувати цілісність зв’язків, коли користувач намагається оновити або видалити дані, SQL Server має потужний набір тригерів. Наприклад, можна запрограмувати тригер, щоб видалити групу записів і забезпечити цілісність даних. Тригери навіть можна додавати до таблиць і подань.

Додаткові відомості див. в статтях Тригери DML, Тригери DDL і Створення тригера T-SQL.

На початок сторінки

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

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

Extended Price: [Quantity] * [Unit Price]

Еквівалентна функція в SQL Server називається обчислюваним стовпцем. Це віртуальний стовпець, який фізично не зберігається в таблиці, якщо його не позначено як PERSISTED. Подібно до обчислюваного стовпця у програмі Access, у виразі він використовує дані з інших стовпців. Щоб створити обчислюваний стовпець, додайте його до таблиці. Наприклад:

CREATE TABLE dbo.Products   
(  
    ProductID int IDENTITY (1,1) NOT NULL  
  , QtyAvailable smallint  
  , UnitPrice money  
  , InventoryValue AS QtyAvailable * UnitPrice  
);  

Докладні відомості див. в статті Вибір обчислюваних стовпців у таблиці.

На початок сторінки

Позначення часу даних

Інколи додається поле таблиці, щоб позначити час створення запису. Тоді можна записати введення даних в журнал. У програмі Access можна просто створити стовпець дат із стандартним значенням =Now(). Щоб записати дату або час у SQL Server, використовуйте тип даних datetime2 із стандартним значенням SYSDATETIME().

Примітка    Не слід плутати значення rowversion і timestamp під час роботи з даними. Ключове слово timestamp – це синонім rowversion у SQL Server, але слід використовувати ключове слово rowversion. У SQL Server rowversion – це тип даних, який являє собою автоматично створені унікальні двійкові числа в базі даних. Він зазвичай використовується, щоб позначити версію рядків у таблиці. Проте тип даних rowversion – це лише число, що збільшується. Він не зберігає дати або часу та не призначений для встановлення позначки часу рядка.

Докладні відомості див. в статті про тип даних rowversion. Докладні відомості про застосування типу даних rowversion, щоб мінімізувати конфлікт записування, див. в статті Перенесення бази даних Access на сервер SQL Server.

На початок сторінки

Керування великими об'єктами

У програмі Access ви керуєте неструктуровані даними, такими як файли, фотографії та зображення, за допомогою типу даних Вкладення. У термінології SQL Server неструктуровані дані називаються BLOB-об'єктом (великий двійковий об'єкт). Існує кілька способів роботи з ними.

FILESTREAM    Щоб зберігати неструктуровані дані у файловій системі, а не в базі даних, використовує тип даних varbinary (максимальний розмір). Докладні відомості див. в статті Доступ до даних FILESTREAM за допомогою Transact-SQL.

FileTable    Зберігає BLOB-об’єкти в спеціальних таблицях, які називаються FileTables, і забезпечує сумісність із програмами Windows. Це так, ніби дані зберігаються в файловій системі без жодних змін у клієнтських програмах. Для таблиць FileTable потрібно використовувати функцію FILESTREAM. Докладні відомості див. в статті FileTables.

Віддалене сховище BLOB-об’єктів (RBS)    Зберігає великі двійкові об’єкти (BLOB-об’єкти) в апаратно-базованих сховищах, а не безпосередньо на сервері. Це економить місце та апаратні ресурси. Докладні відомості див. в статті Дані про великі двійкові об’єкти (BLOB-об’єкти).

На початок сторінки

Робота з ієрархічними даними

Хоча реляційні бази даних, як-от Access, дуже гнучкі, робота з ієрархічними зв'язками – це виняток, який часто вимагає складних інструкцій і кодів SQL. Приклади ієрархічних даних: організаційна структура, файлова система, таксономія мовних термінів і графік зв’язків між веб-сторінками. Щоб легко зберігати ієрархічні дані, створювати запити на отримання таких даних та керувати ними, SQL Server має вбудований тип даних hierarchyid і набір ієрархічних функцій.

Додаткові відомості див. в статті Ієрархічні дані та в навчальному посібнику з використання типу даних hierarchyid.

На початок сторінки

Робота з текстом у форматі JSON

JavaScript Object Notation (JSON) – це веб-служба, яка використовує легкий для читання текст, щоб передавати дані у вигляді пар “атрибут-значення” за допомогою асинхронного зв’язку “браузер-сервер”. Наприклад:

{
"firstName": "Mary",
"lastName": "Contrary",
"spouse": null,
"age": 27
}

У програмі Access немає вбудованих засобів для керування даними JSON, але SQL Server дає змогу легко зберігати, індексувати, запитувати й видобувати дані JSON. Ви можете перетворювати текст JSON на таблицю та зберігати його або форматувати дані безпосередньо в тексті JSON. Наприклад, можна відформатувати для веб-програми результати запиту у вигляді тексту JSON або розташувати структури даних JSON у рядках і стовпцях.

Примітка    JSON не підтримується в VBA. В якості альтернативи можна використовувати формат XML у VBA за допомогою бібліотеки MSXML.

Докладні відомості див. в статті Дані JSON у SQL Server.

На початок сторінки

Ресурси

Тепер саме час дізнатися більше про SQL Server і Transact SQL (TSQL). Як ви вже побачили, у них є багато таких самих функцій, як у програмі Access, а також інші можливості, яких у Access просто немає. Щоб поглибити свої знання, ви можете ознайомитися з наведеними нижче ресурсами.

На початок сторінки