Топ-50 розширених запитань з SQL Server з детальними відповідями

1. В чому різниця між кластеризованим та некластеризованим індексом у SQL Server?

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

2. Поясніть концепцію Always On Availability Groups у SQL Server.

SQL Server Always On Availability Groups — це рішення для високої доступності та відновлення після аварій рівня підприємства, введене в SQL Server 2012. Воно дозволяє кільком базам даних переходити до вторинного репліки в разі недоступності основного репліки. Кожна група доступності складається з одного основного репліки та кількох вторинних реплік, які можуть бути синхронними або асинхронними. Вторинні репліки також можуть використовуватися для операцій лише для читання, щоб зменшити навантаження на основний сервер. Always On підтримує автоматичні та ручні переведення в режим відмови та забезпечує захист даних через репліки, розподілені на кількох серверах або дата-центрах.

3. Що таке розбиття (Partitioning) у SQL Server і чому це корисно?

Розбиття (Partitioning) у SQL Server розділяє таблицю або індекс на менші, зручніші для керування частини, при цьому вони розглядаються як один об'єкт. Дані розподіляються на основі функції розбиття, яка відображає рядки на конкретні розділи залежно від значень стовпців, таких як дата чи регіон. Розбиття покращує продуктивність запитів, дозволяючи операціям сканувати лише відповідні розділи, а не всю таблицю. Воно також допомагає в обслуговуванні даних, спрощуючи архівування та прискорюючи резервне копіювання. Основні типи розбиття включають розбиття за діапазоном (range partitioning) та за хешем (hash partitioning). Однак впровадження розбиття вимагає ретельного планування, щоб уникнути накладних витрат.

4. Що таке розширені події (Extended Events) у SQL Server і як їх використовувати?

Розширені події (Extended Events) — це легковагова система моніторингу продуктивності у SQL Server, введена як заміна SQL Profiler. Вони дозволяють збирати, фільтрувати та аналізувати інформацію про активність системи, запити та проблеми з продуктивністю. Розширена подія складається з подій (специфічні явища, такі як виконання запиту), дій (додаткові дані для збору) та цілей (де зберігаються дані подій, наприклад, файли чи пам'ять). Наприклад, ви можете використовувати розширені події для відстеження повільних запитів, вирішення проблем з блокуванням чи моніторингу заблокованих транзакцій. На відміну від Profiler, розширені події є високонастроюваними та менш ресурсомісткими, що робить їх придатними для виробничих середовищ.

5. Які існують типи реплікації SQL Server і коли вони використовуються?

SQL Server надає три основні типи реплікації:

1. Реплікація знімка (Snapshot Replication): Створює знімок даних на певний момент часу і застосовує його до підписника. Ідеально підходить для статичних або рідко змінюваних даних.

2. Транзакційна реплікація (Transactional Replication): Безперервно реплікує зміни від публікатора до підписника, зберігаючи узгодженість. Підходить для застосунків, що потребують майже реального часу оновлень.

3. Злиттєва реплікація (Merge Replication): Синхронізує дані між публікатором та підписником, дозволяючи зміни з обох кінців. Корисна для розподілених систем з відключеними або мобільними користувачами. Кожен тип реплікації має свої особливості, і вибір залежить від латентності, обсягу та двосторонніх потреб у даних.

6. Поясніть концепцію рівнів ізоляції (Isolation Levels) у SQL Server і їх важливість.

Рівні ізоляції визначають ступінь видимості однієї транзакції для інших одночасних транзакцій.
SQL Server підтримує п'ять рівнів ізоляції:

1. Read Uncommitted: Дозволяє читати незавершені зміни (брудні читання).

2. Read Committed: Запобігає брудним читанням, але дозволяє нерепліковані читання.

3. Repeatable Read: Запобігає брудним та нереплікованим читанням, але дозволяє фантомні читання.

4. Serializable: Забезпечує повну ізоляцію, запобігаючи всім проблемам із конкуренцією, але з більшими накладними витратами.

5. Snapshot: Використовує версії рядків для забезпечення узгодженості транзакцій без блокувань.

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

7. В чому різниця між RAISERROR та THROW у SQL Server?

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

THROW, введений у SQL Server 2012, спрощує обробку помилок, завжди повертаючи поточний контекст транзакції та номер помилки. Це більш послідовно з сучасними практиками обробки помилок та краще інтегрується з блоками TRY…CATCH. На відміну від RAISERROR, THROW не підтримує власні рівні серйозності.

8. Що таке індекси Columnstore та коли їх слід використовувати?

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

1. Кластеризований індекс Columnstore: Вся таблиця зберігається в колонковому форматі, що робить її оптимізованою для читання.

2. Некластеризований індекс Columnstore: Конкретні стовпці зберігаються в колонковому форматі разом з таблицею, що базується на рядках. Індекси Columnstore найкраще підходять для великих наборів даних з важкими запитами на читання, які включають операції SUM, AVG або COUNT.

9. Що таке динамічне маскування даних (Dynamic Data Masking) у SQL Server?

Динамічне маскування даних (DDM) — це функція безпеки, введена в SQL Server 2016. Вона дозволяє частково або повністю маскувати чутливі дані в результатах запитів без зміни вихідних даних. Це досягається шляхом застосування правил маскування до конкретних стовпців. Типи маскування включають:

1. Маскування за замовчуванням: Замінює символи на загальні (наприклад, XXXX).

2. Маскування електронних адрес: Маскує електронні адреси, зберігаючи їх структуру.

3. Користувацьке маскування: Дозволяє вказати власний формат.

4. Випадкове маскування: Генерує випадкові числа для числових полів. DDM корисне для обмеження відкриття даних, при цьому дозволяючи користувачам виконувати запити до бази даних.

10. Що таке Query Store у SQL Server?

Query Store, введений у SQL Server 2016, — це функція, що захоплює плани виконання запитів та статистику часу виконання з часом. Вона дозволяє адміністраторам баз даних аналізувати та усувати проблеми з продуктивністю запитів. Основні переваги Query Store включають:

1. Відстеження історії продуктивності запитів.
2. Ідентифікація змін планів, що впливають на продуктивність.
3. Примусове використання конкретних планів для стабільної продуктивності. Query Store активується на рівні бази даних та зберігає дані в системній таблиці, надаючи відомості про запити з великим впливом та дозволяючи налаштовувати продуктивність.

11. Що таке дедлок (Deadlock) у SQL Server і як його можна вирішити?

Дедлок виникає, коли дві або більше транзакцій блокують одна одну, тримаючи ресурси та чекаючи, щоб інша звільнила їх. Наприклад, транзакція A блокує ресурс X і потребує ресурс Y, в той час як транзакція B блокує ресурс Y і потребує ресурс X. SQL Server автоматично виявляє дедлоки і вирішує їх, завершуючи одну з транзакцій (жертву), щоб звільнити ресурси. Для вирішення та запобігання дедлокам:

1. Використовуйте логіку транзакцій без дедлоків, доступаючи ресурси в одному й тому ж порядку.
2. Мінімізуйте тривалість транзакцій та конфлікти блокувань.
3. Увімкніть графік дедлоків у розширених подіях (Extended Events) для усунення проблем.
   4.
   Розгляньте можливість використання рівня ізоляції, такого як READ COMMITTED SNAPSHOT, щоб уникнути проблем із блокуваннями.

12. Що таке CTE, і чим вони відрізняються від тимчасових таблиць та представлень?

Common Table Expression (CTE) — це тимчасовий набір результатів, визначений у SQL-запиті. Це покращує читабельність та може бути використано у рекурсивних запитах. Різниця:

1. CTE: Існує тільки під час виконання одного запиту. Не може мати індексів.

2. Тимчасові таблиці (Temp Tables): Зберігаються в tempdb, можуть мати індекси та зберігаються протягом сесії.

3. Представлення (Views): Персистентні, багаторазові та можуть включати індекси (індексовані представлення). CTE найкраще використовуються для спрощення складних запитів, тоді як тимчасові таблиці підходять для зберігання проміжних даних.

13. Що таке індекс повнотекстового пошуку (Full-Text Index) у SQL Server?

Індекс повнотекстового пошуку (Full-Text Index) дозволяє ефективно шукати текстові дані, збережені в SQL Server. На відміну від стандартних індексів, він дозволяє виконувати лінгвістичні пошуки (наприклад, пошук за відмінками, синонімами чи фразами). Індексація повнотекстових даних використовує Full-Text Catalog для збереження індексованих ключових слів і підтримує запити, що використовують CONTAINS або FREETEXT. Це ідеально підходить для застосунків, таких як пошукові системи чи сховища документів. Індекси повнотекстового пошуку вимагають активації функції Full-Text Search у SQL Server.

14. Що таке статистика SQL Server і чому вона важлива?

Статистика SQL Server — це об'єкти, що зберігають інформацію про розподіл значень у стовпцях таблиці. Вони використовуються оптимізатором запитів для генерування ефективних планів виконання. Статистика допомагає SQL Server оцінити:

1. Кількість рядків, що будуть повернуті запитом.
2. Використання індексів.
3. Стратегії об'єднання (JOIN).

Оновлення застарілих статистик за допомогою UPDATE STATISTICS або активація AUTOUPDATESTATISTICS покращує продуктивність запитів.

15. Що таке файлові групи SQL Server і як їх використовувати?

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

1. Оптимізації продуктивності шляхом розподілу даних на кілька дисків.
2. Спрощення резервного копіювання та відновлення.
3. Керування великими таблицями, розміщуючи окремі таблиці або індекси в окремих файлових групах.

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

16. Що таке курсор SQL Server і коли його слід уникати?

Курсор (Cursor) — це об'єкт бази даних, який використовується для отримання та маніпулювання рядками по одному. Хоча курсори корисні для операцій по рядках, вони є ресурсомісткими та повільними. Курсори слід уникати, використовуючи операції на основі наборів (наприклад, об'єднання (joins) або віконні функції (window functions)), які є швидшими та ефективнішими. Курсори слід використовувати лише тоді, коли обробка рядків поодинці є неминучою.

17. Що таке Row-Level Security (RLS) у SQL Server?

RLS (Row-Level Security) обмежує доступ до конкретних рядків у таблиці на основі ролей користувачів або атрибутів. Введена в SQL Server 2016, вона використовує політики безпеки та фільтри для накладання обмежень. Наприклад: керівник відділу продажів може бачити всі записи, а продавець — тільки свої. RLS реалізується за допомогою вбудованих функцій таблиць (inline table-valued functions) та предикатів.

18. Що таке пам'ятки таблиць у SQL Server, оптимізовані для пам'яті?

Таблиці, оптимізовані для пам'яті, є частиною функції In-Memory OLTP і зберігають дані в пам'яті для швидшого доступу. Вони ідеально підходять для сценаріїв з високими вимогами до продуктивності, таких як аналітика в реальному часі або системи, що активно обробляють транзакції. Вони підтримують:

1. Параметри для збереження та незбереження даних (Durable and Non-Durable).
2. Нативно скомпільовані збережені процедури для покращення продуктивності.

19. Що таке Transparent Data Encryption (TDE)?

TDE шифрує файли бази даних SQL Server в стані спокою, забезпечуючи захист від несанкціонованого доступу до фізичних сховищ. Воно шифрує всю базу даних, включаючи резервні копії, використовуючи ключ шифрування бази даних.

20. Що таке SQL Server PolyBase?

PolyBase дозволяє виконувати запити до зовнішніх даних, збережених у Hadoop, Azure Blob Storage або інших зовнішніх системах, використовуючи T-SQL. Це забезпечує безшовну інтеграцію для великих даних та підтримує операції ETL.
Що таке індексовані представлення і коли їх слід використовувати?

Індексоване представлення — це представлення, яке зберігає дані фізично, покращуючи продуктивність для складних запитів.

22. У чому різниця між SARGable та non-SARGable запитами?

SARGable запит дозволяє SQL Server ефективно використовувати індекси, тоді як non-SARGable запит запобігає використанню індексів через некоректне фільтрування (наприклад, функції на стовпцях).

23. Що таке дзеркалювання бази даних?

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

24. Поясніть використання TempDB у SQL Server.

TempDB — це системна база даних, що використовується для тимчасових таблиць, змінних і операцій, таких як сортування або хешування.

25. Що таке ланцюжок володіння між базами даних (cross-database ownership chaining)?

Це дозволяє об'єктам однієї бази даних отримувати доступ до об'єктів іншої бази без необхідності явних прав доступу.

26. Що таке SQL-ін'єкція і як її можна запобігти?

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

27. У чому різниця між реплікацією та логовим шипінгом (log shipping)?

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

28. Що таке Service Broker у SQL Server?

Service Broker дозволяє здійснювати асинхронне повідомлення та обробку всередині SQL Server, що корисно для розподілених систем.

29. Що таке зв'язаний сервер (Linked Server)?

Зв'язаний сервер дозволяє SQL Server виконувати запити до інших джерел даних, таких як Oracle чи MySQL, за допомогою OLE DB.

30. Яка мета sp_executesql?

sp_executesql виконує динамічний SQL з параметризацією запитів, що покращує продуктивність і безпеку.

31. Що таке оповіщення SQL Server Agent?

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

32. У чому різниця між SQLCMD та BCP?

SQLCMD — це утиліта командного рядка для виконання T-SQL, тоді як BCP експортує/імпортує великі обсяги даних.

33. Що таке DAC (Dedicated Admin Connection)?

DAC надає спеціальний доступ для усунення неполадок SQL Server, коли інші з'єднання не працюють.

34. Що таке поділ сторінок (page splitting) у SQL Server?

Поділ сторінок відбувається, коли сторінка даних заповнюється, що призводить до зниження продуктивності.

35. Що таке Filestream у SQL Server?

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

36. У чому різниця між DELETE та TRUNCATE?

DELETE видаляє конкретні рядки, тоді як TRUNCATE видаляє всі рядки без запису кожного видалення.

37. Що таке фільтровані індекси (Filtered Index)?

Фільтровані індекси індексують лише підмножину рядків, покращуючи продуктивність та зменшуючи обсяг пам'яті.

38. Що таке CLR інтеграція у SQL Server?

CLR дозволяє писати збережені процедури чи функції за допомогою мов .NET, таких як C#.

39. Що таке Log Sequence Number (LSN)?

LSN унікально ідентифікує запис у журналі транзакцій, що допомагає в відновленні та реплікації.

40. Що таке паралелізм запитів (Query Parallelism)?

SQL Server розділяє запити на кілька потоків для швидшого виконання, ефективно використовуючи процесорні ядра.

41. Що таке SQL Server Query Store?

Query Store, введений у SQL Server 2016, допомагає моніторити продуктивність запитів, зберігаючи плани запитів і статистику виконання. Це дозволяє:

1. Аналізувати регресії продуктивності запитів.
2. Визначати та примусово використовувати певні плани запитів.
3. Зберігати дані продуктивності запитів протягом часу.

Для активації Query Store використовуйте: ALTER DATABASE [DatabaseName] SET QUERY_STORE = ON; Це потужний інструмент для усунення неполадок у продуктивності.

42. Що таке розподілені таблиці у SQL Server?

Розподілені таблиці ділять таблицю на менші, зручніші для управління частини, зазвичай за певним стовпцем, наприклад, датою або ID. Переваги:

1. Покращена продуктивність запитів шляхом сканування лише відповідних частин.
2. Спрощення управління даними (наприклад, архівування).
3. Розподіл частин між файловими групами для кращої продуктивності I/O. Розподіл здійснюється за допомогою схем та функцій розподілу.
   Яка мета SQL Server Resource Governor?

Resource Governor (Управитель ресурсів) керує навантаженням SQL Server і споживанням системних ресурсів. Він допомагає:

1. Виділяти CPU, пам'ять та I/O для конкретних робочих навантажень.

2. Запобігати впливу ресурсоємних запитів на інших користувачів.

3. Визначати пули ресурсів і групи робочих навантажень.

Використовуйте його для забезпечення передбачуваної продуктивності критичних робочих навантажень.

44. Що таке SQL Server Always Encrypted?

Always Encrypted забезпечує, щоб чутливі дані залишалися зашифрованими як у стані спокою, так і під час передачі. Основні характеристики:

1. Шифрування та дешифрування відбуваються на стороні клієнта.

2. SQL Server ніколи не бачить незашифрованих даних.

3. Захищає чутливі стовпці, такі як номери кредитних карток чи соціального страхування. Для реалізації використовуйте Column Master Key та Column Encryption Key.

45. Що таке Stretch Databases у SQL Server?

Stretch Database дозволяє переносити "холодні" або рідко використовувані дані до Azure, залишаючи гарячі дані на місці. Переваги:

1. Зменшення витрат на зберігання при збереженні можливості запитів.

2. Безшовний доступ до розтягнутих даних через T-SQL.

3. Не вимагає змін у додатках.

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

46. Що таке об'єкт Sequence у SQL Server?

Об'єкт Sequence генерує послідовні числа, незалежно від таблиць, на відміну від стовпця IDENTITY. Переваги:

1. Можна використовувати в кількох таблицях.

2. Можливість налаштування інкременту та початкових значень.

3. Дозволяє циклічне використання чисел. Створіть sequence за допомогою: CREATE SEQUENCE MySequence START WITH 1 INCREMENT BY 1; Отримайте наступне значення за допомогою NEXT VALUE FOR.

47. У чому різниця між ONLINE та OFFLINE перезбираннями індексів?

1. ONLINE: Дозволяє користувачам доступати таблицю під час процесу перезбирання, ідеально для мінімізації часу простою.

2. OFFLINE: Блокує таблицю, забороняючи доступ користувачам. Використовуйте ONLINE перезбирання для систем з високою доступністю, хоча воно може мати додаткові накладні витрати.

48. У чому різниця між синхронною та асинхронною реплікацією?

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

2. Асинхронна реплікація: Основний сервер не чекає підтвердження від вторинного сервера, надаючи пріоритет продуктивності над надійністю. Синхронну реплікацію використовують для критичних систем, які потребують високої доступності.

49. Що таке індексовані обчислювальні стовпці у SQL Server?

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

50. Що таке Columnstore Index у SQL Server?

Columnstore індекси зберігають дані в колонковому форматі, що є ідеальним для аналітичних запитів та сховищ даних. Переваги:

1. Покращене стиснення, що зменшує вимоги до зберігання.

2. Швидша продуктивність запитів для великих наборів даних.

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

Перекладено з: Top 50 Advanced SQL Server Questions with In-Depth Answers