Search found 149 matches

Author Message

Microsoft SQL Server

Post 27-Sep-2020 22:10

[Quote]

Базы данных. Проектирование, программирование, управление и администрирование
Год издания: 2020
Автор: Владимир Волк
Издательство: Лань
ISBN: 978-5-8114-4189-1
Язык: Русский
Формат: PDF
Качество: Издательский макет или текст (eBook)
Интерактивное оглавление: Нет
Количество страниц: 242
Описание:
Учебник посвящен базам данных — одному из направлений IT-индустрии, в рамках которого традиционно рассматриваются технологии надежного хранения больших объемов информации, ее эффективного поиска и извлечения по запросам потребителей. Материал, представленный в учебнике, охватывает основные фазы жизненного цикла базы данных: последовательно обсуждается основные концепции и проблематика баз данных, рассматриваются процессы их проектирования, программирования и управления, а также технологии их администрирования с целью достижения высокой производительности доступа к данным и обеспечения требуемого уровня информационной безопасности. База данных рассматривается как информационная модель предметной области, а ее разработка — как многоэтапный процесс последовательного преобразования концептуальной модели в логическую (реляционную) модель данных на стадии проектирования, последующей программной реализации логической модели средствами языка SQL и настройки параметров физической модели в процессе эксплуатации базы данных.
Основное внимание в учебнике уделено прикладным аспектам технологий баз данных, изложение материала сопровождается многочисленными примерами и листингами программного SQL-кода. Учебник содержит практикум по проектированию, программированию и администрированию баз данных, включающий примеры и практические задания для самостоятельного выполнения.
Предназначен для студентов IT-специальностей и может быть использован преподавателями при подготовке лекционных курсов, проведении практических и лабораторных занятий, курсовом проектировании.

Примеры страниц

Мои остальные раздачи - [url=tracker.php?rid=43141996]https://rutracker.org/forum/tracker.php?rid=43141996[/url]

Microsoft SQL Server

Post 23-Sep-2020 11:35

[Quote]

Learning SQL, 3rd edition / Изучаем SQL, 3-е издание
Год издания: 2020
Автор: Beaulieu A. / Бьюли А.
Издательство: O'Reilly
ISBN: 978-1-492-05761-1
Язык: Английский
Формат: PDF
Качество: Издательский макет или текст (eBook)
Интерактивное оглавление: Да
Количество страниц: 380
Описание: As data floods into your company, you need to put it to work right away—and SQL is the best tool for the job. With the latest edition of this introductory guide, author Alan Beaulieu helps developers get up to speed with SQL fundamentals for writing database applications, performing administrative tasks, and generating reports. You’ll find new chapters on SQL and big data, analytic functions, and working with very large databases.
Each chapter presents a self-contained lesson on a key SQL concept or technique using numerous illustrations and annotated examples. Exercises let you practice the skills you learn. Knowledge of SQL is a must for interacting with data. With Learning SQL, you’ll quickly discover how to put the power and flexibility of this language to work.
  • Move quickly through SQL basics and several advanced features
  • Use SQL data statements to generate, manipulate, and retrieve data
  • Create database objects, such as tables, indexes, and constraints with SQL schema statements
  • Learn how datasets interact with queries; understand the importance of subqueries
  • Convert and manipulate data with SQL’s built-in functions and use conditional logic in data statements

Примеры страниц

Оглавление

Preface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi
1. A Little Background. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Introduction to Databases 1
Nonrelational Database Systems 2
The Relational Model 5
Some Terminology 7
What Is SQL? 8
SQL Statement Classes 9
SQL: A Nonprocedural Language 10
SQL Examples 11
What Is MySQL? 13
SQL Unplugged 14
What’s in Store 15
2. Creating and Populating a Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Creating a MySQL Database 17
Using the mysql Command-Line Tool 18
MySQL Data Types 20
Character Data 20
Numeric Data 23
Temporal Data 25
Table Creation 27
Step 1: Design 27
Step 2: Refinement 28
Step 3: Building SQL Schema Statements 30
Populating and Modifying Tables 33
Inserting Data 33
Updating Data 38
Deleting Data 39
When Good Statements Go Bad 39
Nonunique Primary Key 39
Nonexistent Foreign Key 40
Column Value Violations 40
Invalid Date Conversions 40
The Sakila Database 41
3. Query Primer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Query Mechanics 45
Query Clauses 47
The select Clause 48
Column Aliases 50
Removing Duplicates 51
The from Clause 53
Tables 53
Table Links 56
Defining Table Aliases 57
The where Clause 58
The group by and having Clauses 60
The order by Clause 61
Ascending Versus Descending Sort Order 63
Sorting via Numeric Placeholders 64
Test Your Knowledge 65
Exercise 3-1 65
Exercise 3-2 65
Exercise 3-3 65
Exercise 3-4 65
4. Filtering. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
Condition Evaluation 67
Using Parentheses 68
Using the not Operator 69
Building a Condition 70
Condition Types 71
Equality Conditions 71
Range Conditions 73
Membership Conditions 77
Matching Conditions 79
Null: That Four-Letter Word 82
Test Your Knowledge 85
Exercise 4-1 86
Exercise 4-2 86
Exercise 4-3 86
Exercise 4-4 86
5. Querying Multiple Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
What Is a Join? 87
Cartesian Product 88
Inner Joins 89
The ANSI Join Syntax 91
Joining Three or More Tables 93
Using Subqueries as Tables 95
Using the Same Table Twice 96
Self-Joins 98
Test Your Knowledge 99
Exercise 5-1 99
Exercise 5-2 99
Exercise 5-3 100
6. Working with Sets. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
Set Theory Primer 101
Set Theory in Practice 104
Set Operators 105
The union Operator 106
The intersect Operator 108
The except Operator 109
Set Operation Rules 111
Sorting Compound Query Results 111
Set Operation Precedence 112
Test Your Knowledge 114
Exercise 6-1 114
Exercise 6-2 114
Exercise 6-3 114
7. Data Generation, Manipulation, and Conversion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
Working with String Data 115
String Generation 116
String Manipulation 121
Working with Numeric Data 129
Performing Arithmetic Functions 129
Controlling Number Precision 131
Handling Signed Data 133
Working with Temporal Data 134
Dealing with Time Zones 134
Generating Temporal Data 136
Manipulating Temporal Data 140
Conversion Functions 144
Test Your Knowledge 145
Exercise 7-1 145
Exercise 7-2 145
Exercise 7-3 145
8. Grouping and Aggregates. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147
Grouping Concepts 147
Aggregate Functions 150
Implicit Versus Explicit Groups 151
Counting Distinct Values 152
Using Expressions 153
How Nulls Are Handled 153
Generating Groups 155
Single-Column Grouping 155
Multicolumn Grouping 156
Grouping via Expressions 157
Generating Rollups 157
Group Filter Conditions 159
Test Your Knowledge 160
Exercise 8-1 160
Exercise 8-2 160
Exercise 8-3 160
9. Subqueries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
What Is a Subquery? 161
Subquery Types 163
Noncorrelated Subqueries 163
Multiple-Row, Single-Column Subqueries 164
Multicolumn Subqueries 169
Correlated Subqueries 171
The exists Operator 173
Data Manipulation Using Correlated Subqueries 174
When to Use Subqueries 175
Subqueries as Data Sources 176
Subqueries as Expression Generators 182
Subquery Wrap-Up 184
Test Your Knowledge 185
Exercise 9-1 185
Exercise 9-2 185
Exercise 9-3 185
10. Joins Revisited. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187
Outer Joins 187
Left Versus Right Outer Joins 190
Three-Way Outer Joins 191
Cross Joins 192
Natural Joins 198
Test Your Knowledge 199
Exercise 10-1 200
Exercise 10-2 200
Exercise 10-3 (Extra Credit) 200
11. Conditional Logic. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201
What Is Conditional Logic? 201
The case Expression 202
Searched case Expressions 202
Simple case Expressions 204
Examples of case Expressions 205
Result Set Transformations 205
Checking for Existence 206
Division-by-Zero Errors 208
Conditional Updates 209
Handling Null Values 210
Test Your Knowledge 211
Exercise 11-1 211
Exercise 11-2 211
12. Transactions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213
Multiuser Databases 213
Locking 214
Lock Granularities 214
What Is a Transaction? 215
Starting a Transaction 217
Ending a Transaction 218
Transaction Savepoints 219
Test Your Knowledge 222
Exercise 12-1 222
13. Indexes and Constraints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223
Indexes 223
Index Creation 224
Types of Indexes 229
How Indexes Are Used 231
The Downside of Indexes 232
Constraints 233
Constraint Creation 234
Test Your Knowledge 237
Exercise 13-1 237
Exercise 13-2 237
14. Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239
What Are Views? 239
Why Use Views? 242
Data Security 242
Data Aggregation 243
Hiding Complexity 244
Joining Partitioned Data 244
Updatable Views 245
Updating Simple Views 246
Updating Complex Views 247
Test Your Knowledge 249
Exercise 14-1 249
Exercise 14-2 250
15. Metadata. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 251
Data About Data 251
information_schema 252
Working with Metadata 257
Schema Generation Scripts 257
Deployment Verification 260
Dynamic SQL Generation 261
Test Your Knowledge 265
Exercise 15-1 265
Exercise 15-2 265
16. Analytic Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267
Analytic Function Concepts 267
Data Windows 268
Localized Sorting 269
Ranking 270
Ranking Functions 271
Generating Multiple Rankings 274
Reporting Functions 277
Window Frames 279
Lag and Lead 281
Column Value Concatenation 283
Test Your Knowledge 284
Exercise 16-1 284
Exercise 16-2 285
Exercise 16-3 285
17. Working with Large Databases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287
Partitioning 287
Partitioning Concepts 288
Table Partitioning 288
Index Partitioning 289
Partitioning Methods 289
Partitioning Benefits 297
Clustering 297
Sharding 298
Big Data 299
Hadoop 299
NoSQL and Document Databases 300
Cloud Computing 300
Conclusion 301
18. SQL and Big Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 303
Introduction to Apache Drill 303
Querying Files Using Drill 304
Querying MySQL Using Drill 306
Querying MongoDB Using Drill 309
Drill with Multiple Data Sources 315
Future of SQL 317
A. ER Diagram for Example Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 319
B. Solutions to Exercises. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321
Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 349

Microsoft SQL Server

Post 06-Aug-2020 12:35

[Quote]

MongoDB: полное руководство
Год издания: 2020
Автор: Шеннон Брэдшоу, Йон Брэзил, Кристина Ходоров
Издательство: ДМК
ISBN: 978-5-97060-792-3
Язык: Русский
Формат: PDF
Качество: Издательский макет или текст (eBook)
Интерактивное оглавление: Да
Количество страниц: 541
Описание: Эта книга представляет собой исчерпывающее руководство по работе с MongoDB 4.2 – мощной документоориентированной системой управления базами данных. Авторы, внесшие личный вклад в создание и развитие MongoDB, начинают описание системы с самых азов (история создания, базовая терминология) и постепенно переходят к более сложным темам (выполнение запросов, индексация, агрегирование, транзакции, наборы реплик, управление операциями, шардинг и администрирование данных, долговечность, мониторинг и безопасность).
Читатель получит конкретные советы по написанию приложения, которое хорошо работает с MongoDB, выяснит, какие системные настройки следует учитывать при ее развертывании и как устанавливать MongoDB в Windows, OS X и Linux.
MongoDB – это не реляционная, а документоориентированная система управления базами данных. Основной причиной отказа от реляционной модели является упрощение горизонтального масштабирования, но есть и другие преимущества. Документоориентированная СУБД заменяет концепцию «строки» более гибкой моделью, «документом». Позволяя использовать вложенные документы и массивы, документоориентированный подход дает возможность представлять сложные иерархические отношения с помощью одной записи.
Прочитав руководство, вы научитесь:
• работать с MongoDB, выполнять операции записи, находить документы и создавать сложные запросы;
• индексировать коллекции, агрегировать данные и использовать транзакции для своего приложения;
• настраивать набор локальных реплик и оценивать взаимодействие репликации с вашим приложением;
• настраивать компоненты кластера и выбирать ключ шардинга для различных приложений;
• исследовать аспекты администрирования приложений, настраивать аутентификацию и авторизацию;
• использовать статистику при мониторинге, резервном копировании и восстановлении развертываний, а также системные настройки при развертывании MongoDB.
Издание предназначено для разработчиков объемных баз данных, масштабирование которых является одной из приоритетных задач.

Примеры страниц

Оглавление

Оглавление
Предисловие 16
Предисловие от издательства 20
Часть I
Введение в MongoDB 21
Глава 1. Введение 22
Глава 2. Начало работы 26
Глава 3. Создание, обновление и удаление документов 52
Глава 4. Выполнение запросов 79
Часть II
Разработка приложения 101
Глава 5. Индексы 102
Глава 6. Специальные типы индексов и коллекций 168
Глава 7. Знакомство с фреймворком агрегации 198
Глава 8. Транзакции 243
Глава 9. Разработка приложений 251
Часть III
Репликация 271
Глава 10. Настройка набора реплик 272
Глава 11. Компоненты набора реплик 296
Глава 12. Подключение к набору реплик из своего приложения 308
Глава 13. Администрирование 320
Часть IV
Шардинг 337
Глава 14. Знакомство с шардингом 338
Глава 15. Конфигурирование шардинга 352
Глава 16. Выбор ключа шардинга 369
Глава 17. Администрирование шардинга 389
Часть V
Администрирование приложений 423
Глава 18. Смотрим, что делает ваше приложение 424
Глава 19. Обеспечение безопасности в MongoDB 444
Глава 20. Долговечность 462
Часть VI
Администрирование сервера 471
Глава 21. Настройка MongoDB в рабочем окружении 472
Глава 22. Мониторинг MongoDB 483
Глава 23. Создание резервных копий 495
Глава 24. Развертывание MongoDB 508
Приложение А. Установка MongoDB 526
Приложение B. Внутреннее устройство MongoDB 531
Об авторах 536
Об изображении на обложке 537
Предметный указатель 538

Microsoft SQL Server

Post 04-Aug-2020 23:05

[Quote]

Database Reliability Engineering. Designing and Operating Resilient Database Systems / Базы данных. Инжиниринг надежности
Год издания: 2020
Автор: Laine Campbell, Charity Majors / Лейн Кэмпбелл, Черити Мейджорс
Переводчик: Сандицкая Е.
Издательство: Питер
ISBN: 978-5-4461-1310-1
Серия: Бестселлеры O’Reilly
Язык: Русский
Формат: PDF
Качество: Издательский макет или текст (eBook)
Интерактивное оглавление: Да
Количество страниц: 304
Описание: В сфере IT произошла настоящая революция - с инфраструктурой стали работать как с кодом. Этот процесс создает не только новые проблемы, но и возможности для обеспечения безотказной работы баз данных. Авторы подготовили это практическое руководство для всех, кто желает влиться в сообщество современных инженеров по обеспечению надежности баз данных (database reliability engineers, DBRE).
В этой книге:
• требования к сервисам хранения данных и управление рисками.
• создание и развитие архитектуры, обеспечивающей прозрачную поддержку базы данных.
• оптимизация процесса управления релизами.
• хранение, индексирование и репликация данных.
• определение характеристик хранилища данных и подбор оптимальных вариантов его использования.
• исследование компонентов архитектуры и создание архитектур, ориентированных на обработку больших данных.

Примеры страниц

Мои остальные раздачи - [url=tracker.php?rid=43141996]https://rutracker.org/forum/tracker.php?rid=43141996[/url]

Microsoft SQL Server

Post 03-Jun-2020 15:20

[Quote]

Database Internals: A Deep Dive into How Distributed Data Systems Work / Внутреннее устройство Баз данных: Погружение в работу распределенных систем
Год издания: 2019
Автор: Petrov A. / Петров А.
Издательство: O'Reilly
ISBN: 978-1492040347
Язык: Английский
Формат: PDF
Качество: Издательский макет или текст (eBook)
Интерактивное оглавление: Да
Количество страниц: 371
Описание: When it comes to choosing, using, and maintaining a database, understanding its internals is essential. But with so many distributed databases and tools available today, it’s often difficult to understand what each one offers and how they differ. With this practical guide, Alex Petrov guides developers through the concepts behind modern database and storage engine internals.
Throughout the book, you’ll explore relevant material gleaned from numerous books, papers, blog posts, and the source code of several open source databases. These resources are listed at the end of parts one and two. You’ll discover that the most significant distinctions among many modern databases reside in subsystems that determine how storage is organized and how data is distributed.
This book examines:
Storage engines: Explore storage classification and taxonomy, and dive into B-Tree-based and immutable Log
Structured storage engines, with differences and use-cases for each
Storage building blocks: Learn how database files are organized to build efficient storage, using auxiliary data structures such as Page Cache, Buffer Pool and Write-Ahead Log
Distributed systems: Learn step-by-step how nodes and processes connect and build complex communication patterns
Database clusters: Which consistency models are commonly used by modern databases and how distributed storage systems achieve consistency

Примеры страниц

Оглавление

Part I. Storage Engines
1. Introduction and Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
DBMS Architecture 8
Memory- Versus Disk-Based DBMS 10
Durability in Memory-Based Stores 11
Column- Versus Row-Oriented DBMS 12
Row-Oriented Data Layout 13
Column-Oriented Data Layout 14
Distinctions and Optimizations 15
Wide Column Stores 15
Data Files and Index Files 17
Data Files 18
Index Files 18
Primary Index as an Indirection 20
Buffering, Immutability, and Ordering 21
Summary 22
2. B-Tree Basics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Binary Search Trees 26
Tree Balancing 27
Trees for Disk-Based Storage 28
Disk-Based Structures 29
Hard Disk Drives 30
Solid State Drives 30
vOn-Disk Structures 32
Ubiquitous B-Trees 33
B-Tree Hierarchy 35
Separator Keys 36
B-Tree Lookup Complexity 37
B-Tree Lookup Algorithm 38
Counting Keys 38
B-Tree Node Splits 39
B-Tree Node Merges 41
Summary 42
3. File Formats. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Motivation 46
Binary Encoding 47
Primitive Types 47
Strings and Variable-Size Data 49
Bit-Packed Data: Booleans, Enums, and Flags 49
General Principles 50
Page Structure 52
Slotted Pages 52
Cell Layout 54
Combining Cells into Slotted Pages 56
Managing Variable-Size Data 57
Versioning 58
Checksumming 59
Summary 60
4. Implementing B-Trees. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
Page Header 61
Magic Numbers 62
Sibling Links 62
Rightmost Pointers 63
Node High Keys 64
Overflow Pages 65
Binary Search 67
Binary Search with Indirection Pointers 67
Propagating Splits and Merges 68
Breadcrumbs 69
Rebalancing 70
Right-Only Appends 71
Bulk Loading 72
Compression 73
vi | Table of ContentsVacuum and Maintenance 74
Fragmentation Caused by Updates and Deletes 75
Page Defragmentation 76
Summary 76
5. Transaction Processing and Recovery. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Buffer Management 81
Caching Semantics 83
Cache Eviction 83
Locking Pages in Cache 84
Page Replacement 85
Recovery 88
Log Semantics 90
Operation Versus Data Log 91
Steal and Force Policies 91
ARIES 92
Concurrency Control 93
Serializability 94
Transaction Isolation 95
Read and Write Anomalies 95
Isolation Levels 96
Optimistic Concurrency Control 98
Multiversion Concurrency Control 99
Pessimistic Concurrency Control 99
Lock-Based Concurrency Control 100
Summary 108
6. B-Tree Variants. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
Copy-on-Write 112
Implementing Copy-on-Write: LMDB 113
Abstracting Node Updates 113
Lazy B-Trees 114
WiredTiger 114
Lazy-Adaptive Tree 116
FD-Trees 117
Fractional Cascading 118
Logarithmic Runs 119
Bw-Trees 120
Update Chains 121
Taming Concurrency with Compare-and-Swap 121
Structural Modification Operations 122
Consolidation and Garbage Collection 123
Table of Contents | viiCache-Oblivious B-Trees 124
van Emde Boas Layout 125
Summary 127
7. Log-Structured Storage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
LSM Trees 130
LSM Tree Structure 132
Updates and Deletes 136
LSM Tree Lookups 137
Merge-Iteration 137
Reconciliation 140
Maintenance in LSM Trees 141
Read, Write, and Space Amplification 143
RUM Conjecture 144
Implementation Details 145
Sorted String Tables 145
Bloom Filters 146
Skiplist 148
Disk Access 150
Compression 151
Unordered LSM Storage 152
Bitcask 153
WiscKey 154
Concurrency in LSM Trees 155
Log Stacking 157
Flash Translation Layer 157
Filesystem Logging 159
LLAMA and Mindful Stacking 160
Open-Channel SSDs 161
Summary 162
Part I Conclusion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
Part II. Distributed Systems
8. Introduction and Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
Concurrent Execution 171
Shared State in a Distributed System 173
Fallacies of Distributed Computing 174
Processing 175
Clocks and Time 176
viii | Table of ContentsState Consistency 177
Local and Remote Execution 178
Need to Handle Failures 178
Network Partitions and Partial Failures 179
Cascading Failures 180
Distributed Systems Abstractions 181
Links 182
Two Generals’ Problem 187
FLP Impossibility 189
System Synchrony 190
Failure Models 191
Crash Faults 191
Omission Faults 192
Arbitrary Faults 193
Handling Failures 193
Summary 193
9. Failure Detection. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
Heartbeats and Pings 196
Timeout-Free Failure Detector 197
Outsourced Heartbeats 198
Phi-Accural Failure Detector 199
Gossip and Failure Detection 200
Reversing Failure Detection Problem Statement 201
Summary 202
10. Leader Election. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205
Bully Algorithm 207
Next-In-Line Failover 208
Candidate/Ordinary Optimization 209
Invitation Algorithm 210
Ring Algorithm 211
Summary 212
11. Replication and Consistency. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215
Achieving Availability 216
Infamous CAP 216
Use CAP Carefully 217
Harvest and Yield 218
Shared Memory 219
Ordering 221
Consistency Models 222
Table of Contents | ixStrict Consistency 223
Linearizability 223
Sequential Consistency 227
Causal Consistency 229
Session Models 233
Eventual Consistency 234
Tunable Consistency 235
Witness Replicas 236
Strong Eventual Consistency and CRDTs 238
Summary 240
12. Anti-Entropy and Dissemination. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243
Read Repair 245
Digest Reads 246
Hinted Handoff 246
Merkle Trees 247
Bitmap Version Vectors 248
Gossip Dissemination 250
Gossip Mechanics 251
Overlay Networks 251
Hybrid Gossip 253
Partial Views 254
Summary 255
13. Distributed Transactions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257
Making Operations Appear Atomic 258
Two-Phase Commit 259
Cohort Failures in 2PC 261
Coordinator Failures in 2PC 262
Three-Phase Commit 264
Coordinator Failures in 3PC 265
Distributed Transactions with Calvin 266
Distributed Transactions with Spanner 268
Database Partitioning 270
Consistent Hashing 271
Distributed Transactions with Percolator 272
Coordination Avoidance 275
Summary 277
14. Consensus. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279
Broadcast 280
Atomic Broadcast 281
x | Table of ContentsVirtual Synchrony 282
Zookeeper Atomic Broadcast (ZAB) 283
Paxos 285
Paxos Algorithm 286
Quorums in Paxos 287
Failure Scenarios 288
Multi-Paxos 291
Fast Paxos 292
Egalitarian Paxos 293
Flexible Paxos 296
Generalized Solution to Consensus 297
Raft 300
Leader Role in Raft 302
Failure Scenarios 304
Byzantine Consensus 305
PBFT Algorithm 306
Recovery and Checkpointing 309
Summary 309
Part II Conclusion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313
A. Bibliography. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 317
Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337

Microsoft SQL Server

Post 30-May-2020 17:25

[Quote]

Mastering SQL Server 2017 / Овладеваем SQL Server 2017
Год издания: 2019
Автор: Radivojević M., Sarka D., et al. / Радивоевич М., Сарка Д., и др.
Издательство: Packt Publishing
ISBN: 978-1-83898-320-8
Язык: Английский
Формат: PDF
Качество: Издательский макет или текст (eBook)
Интерактивное оглавление: Да
Количество страниц: 684
Описание: Learn
Use columnstore indexes to make storage and performance improvements
Extend database design solutions using temporal tables
Exchange JSON data between applications and SQL Server
Migrate historical data to Microsoft Azure by using Stretch Database
Design the architecture of a modern Extract, Transform, and Load (ETL) solution
Implement ETL solutions using Integration Services for both on-premise and Azure data
About
Microsoft SQL Server 2017 uses the power of R and Python for machine learning and containerization-based deployment on Windows and Linux. By learning how to use the features of SQL Server 2017 effectively, you can build scalable apps and easily perform data integration and transformation.
You’ll start by brushing up on the features of SQL Server 2017. This Learning Path will then demonstrate how you can use Query Store, columnstore indexes, and In-Memory OLTP in your apps. You'll also learn to integrate Python code in SQL Server and graph database implementations for development and testing. Next, you'll get up to speed with designing and building SQL Server Integration Services (SSIS) data warehouse packages using SQL server data tools. Toward the concluding chapters, you’ll discover how to develop SSIS packages designed to maintain a data warehouse using the data flow and other control flow tasks.
By the end of this Learning Path, you'll be equipped with the skills you need to design efficient, high-performance database applications with confidence.
This Learning Path includes content from the following Packt books:
SQL Server 2017 Developer's Guide by Miloš Radivojević, Dejan Sarka, et. al
SQL Server 2017 Integration Services Cookbook by Christian Cote, Dejan Sarka, et. al
Features
Work with temporal tables to access information stored in a table at any time
Get familiar with the latest features in SQL Server 2017 Integration Services
Program and extend your packages to enhance their functionality

Примеры страниц

Оглавление

Preface 1
Chapter 1: Introduction to SQL Server 2017 8
Security 9
Row-Level Security 9
Dynamic data masking 10
Always Encrypted 10
Engine features 11
Query Store 11
Live query statistics 12
Stretch Database 12
Database scoped configuration 13
Temporal Tables 14
Columnstore indexes 14
Containers and SQL Server on Linux 15
Programming 16
Transact-SQL enhancements 16
JSON 16
In-Memory OLTP 17
SQL Server Tools 17
Business intelligence 18
R in SQL server 18
Release cycles 19
Summary 19
Chapter 2: SQL Server Tools 20
Installing and updating SQL Server Tools 21
New SSMS features and enhancements 26
Autosave open tabs 27
Searchable options 28
Enhanced scroll bar 29
Execution plan comparison 30
Live query statistics 33
Importing flat file Wizard 35
Vulnerability assessment 40
SQL Server Data Tools 44
Tools for developing R and Python code 48
RStudio IDE 49
R Tools for Visual Studio 2015 56
Setting up Visual Studio 2017 for data science applications 57
Summary 59Table of Contents
[ ii ]
Chapter 3: JSON Support in SQL Server 60
Why JSON? 61
What is JSON? 62
Why is it popular? 63
JSON versus XML 63
JSON objects 63
JSON object 64
JSON array 65
Primitive JSON data types 65
JSON in SQL Server prior to SQL Server 2016 67
JSON4SQL 67
JSON.SQL 67
Transact-SQL-based solution 67
Retrieving SQL Server data in JSON format 68
FOR JSON AUTO 68
FOR JSON PATH 71
FOR JSON additional options 73
Add a root node to JSON output 73
Include NULL values in the JSON output 74
Formatting a JSON output as a single object 75
Converting data types 77
Escaping characters 78
Converting JSON data in a tabular format 78
OPENJSON with the default schema 79
Processing data from a comma-separated list of values 84
Returning the difference between two table rows 85
OPENJSON with an explicit schema 86
Import the JSON data from a file 89
JSON storage in SQL Server 2017 92
Validating JSON data 93
Extracting values from a JSON text 95
JSON_VALUE 95
JSON_QUERY 98
Modifying JSON data 101
Adding a new JSON property 101
Updating the value for a JSON property 103
Removing a JSON property 105
Multiple changes 106
Performance considerations 107
Indexes on computed columns 108
Full-text indexes 110
Summary 112
Chapter 4: Stretch Database 113
Stretch DB architecture 113
Is this for you? 115Table of Contents
[ iii ]
Using Data Migration Assistant 116
Limitations of using Stretch Database 123
Limitations that prevent you from enabling the Stretch DB features for a
table 123
Table limitations 123
Column limitations 124
Limitations for Stretch-enabled tables 125
Use cases for Stretch Database 126
Archiving of historical data 126
Archiving of logging tables 126
Testing Azure SQL database 126
Enabling Stretch Database 127
Enabling Stretch Database at the database level 127
Enabling Stretch Database by using wizard 128
Enabling Stretch Database by using Transact-SQL 135
Enabling Stretch Database for a table 136
Enabling Stretch DB for a table by using wizard 137
Enabling Stretch Database for a table by using Transact-SQL 139
Filter predicate with sliding window 142
Querying stretch databases 142
Querying and updating remote data 145
SQL Server Stretch Database pricing 147
Stretch DB management and troubleshooting 149
Monitoring Stretch Databases 150
Pause and resume data migration 154
Disabling Stretch Database 154
Disable Stretch Database for tables by using SSMS 155
Disabling Stretch Database for tables using Transact-SQL 156
Disabling Stretch Database for a database 157
Backing up and restoring Stretch-enabled databases 157
Summary 159
Chapter 5: Temporal Tables 160
What is temporal data? 161
Types of temporal tables 162
Allen's interval algebra 163
Temporal constraints 166
Temporal data in SQL Server before 2016 166
Optimizing temporal queries 168
Temporal features in SQL:2011 172
System-versioned temporal tables in SQL Server 2017 173
How temporal tables work in SQL Server 2017 173
Creating temporal tables 174
Period columns as hidden attributes 180
Converting non-temporal tables to temporal tables 181
Migrating an existing temporal solution to system-versioned tables 184
Altering temporal tables 187Table of Contents
[ iv ]
Dropping temporal tables 191
Data manipulation in temporal tables 192
Inserting data in temporal tables 192
Updating data in temporal tables 193
Deleting data in temporal tables 196
Querying temporal data in SQL Server 2017 196
Retrieving temporal data at a specific point in time 197
Retrieving temporal data from a specific period 199
Retrieving all temporal data 201
Performance and storage considerations with temporal tables 202
History retention policy in SQL Server 2017 202
Configuring the retention policy at the database level 202
Configuring the retention policy at the table level 202
Custom history data retention 207
History table implementation 208
History table overhead 211
Temporal tables with memory-optimized tables 213
What is missing in SQL Server 2017? 217
SQL Server 2016 and 2017 temporal tables and data warehouses 217
Summary 221
Chapter 6: Columnstore Indexes 222
Analytical queries in SQL Server 223
Joins and indexes 225
Benefits of clustered indexes 229
Leveraging table partitioning 231
Nonclustered indexes in analytical scenarios 233
Using indexed views 235
Data compression and query techniques 236
Writing efficient queries 241
Columnar storage and batch processing 243
Columnar storage and compression 243
Recreating rows from columnar storage 245
Columnar storage creation process 246
Development of columnar storage in SQL Server 248
Batch processing 251
Nonclustered columnstore indexes 257
Compression and query performance 257
Testing the nonclustered columnstore index 260
Operational analytics 266
Clustered columnstore indexes 267
Compression and query performance 268
Testing the clustered columnstore index 269
Using archive compression 270
Adding B-tree indexes and constraints 272
Updating a clustered columnstore index 277
Deleting from a clustered columnstore index 281
Summary 283Table of Contents
[ v ]
Chapter 7: SSIS Setup 284
Introduction 284
SQL Server 2016 download 284
Getting ready 284
How to do it... 285
Installing JRE for PolyBase 289
Getting ready 290
How to do it... 290
How it works... 295
Installing SQL Server 2016 296
Getting ready 296
How to do it... 296
SQL Server Management Studio installation 317
Getting ready 317
How to do it... 317
SQL Server Data Tools installation 321
Getting ready 322
How to do it... 322
Testing SQL Server connectivity 330
Getting ready 330
How to do it... 331
Chapter 8: What Is New in SSIS 2016 333
Introduction 333
Creating SSIS Catalog 334
Getting ready 334
How to do it... 335
Custom logging 339
Getting ready 340
How to do it... 340
How it works... 343
There's more... 343
Create a database 344
Create a simple project 345
Testing the custom logging level 367
See also 373
Azure tasks and transforms 373
Getting ready 374
How to do it... 374
See also 380
Incremental package deployment 380
Getting ready 380
How to do it... 381
There's more... 388
Multiple version support 388Table of Contents
[ vi ]
Getting ready 389
How to do it... 389
There's more... 390
Error column name 391
Getting ready 391
How to do it... 391
Control Flow templates 399
Getting ready 400
How to do it... 400
Chapter 9: Key Components of a Modern ETL Solution 406
Introduction 406
Installing the sample solution 410
Getting ready 410
How to do it... 411
There's more... 412
Deploying the source database with its data 412
Getting ready 412
How to do it... 413
There's more... 421
Deploying the target database 423
Getting ready 423
How to do it... 424
SSIS projects 428
Getting ready 431
How to do it... 432
Framework calls in EP_Staging.dtsx 436
Getting ready 439
How to do it... 440
There's more... 442
Chapter 10: Dealing with Data Quality 443
Introduction 443
Profiling data with SSIS 446
Getting ready 446
How to do it... 447
Creating a DQS knowledge base 451
Getting ready 452
How to do it... 452
Data cleansing with DQS 456
Getting ready 456
How to do it... 456
Creating a MDS model 460
Getting ready 460
How to do it... 460Table of Contents
[ vii ]
Matching with DQS 468
Getting ready 468
How to do it... 471
Using SSIS fuzzy components 477
Getting ready 478
How to do it... 478
Chapter 11: Unleash the Power of SSIS Script Task and Component 482
Introduction 482
Using variables in SSIS Script task 484
Getting ready 484
How to do it... 484
Execute complex filesystem operations with the Script task 487
Getting ready 487
How to do it... 487
Reading data profiling XML results with the Script task 491
Getting ready 491
How to do it... 491
Correcting data with the Script component 494
Getting ready 494
How to do it... 495
Validating data using regular expressions in a Script component 500
Getting ready 501
How to do it... 501
Using the Script component as a source 511
How to do it... 511
How it works... 518
Using the Script component as a destination 518
Getting ready 518
How to do it... 518
How it works... 524
Chapter 12: On-Premises and Azure Big Data Integration 525
Introduction 525
Azure Blob storage data management 526
Getting ready 526
How to do it... 526
Installing a Hortonworks cluster 531
Getting ready 531
How to do it... 532
Copying data to an on-premises cluster 534
Getting ready 534
How to do it... 534
Using Hive – creating a database 539
Getting ready 539Table of Contents
[ viii ]
How to do it... 540
There's more... 542
Transforming the data with Hive 542
Getting ready 542
How to do it... 542
There's more... 545
Transferring data between Hadoop and Azure 546
Getting ready 546
How to do it... 546
Leveraging a HDInsight big data cluster 553
Getting ready 553
How to do it... 553
There's more... 558
Managing data with Pig Latin 558
Getting ready 558
How to do it... 558
There's more... 560
Importing Azure Blob storage data 561
Getting ready 561
How to do it... 561
There's more... 565
Azure Data Factory and SSIS 566
Chapter 13: Extending SSIS Custom Tasks and Transformations 567
Introduction 567
Designing a custom task 568
Getting ready 570
How to do it... 572
How it works... 586
Designing a custom transformation 587
How to do it... 591
How it works... 612
Managing custom component versions 613
Getting ready 614
How to do it... 614
How it works... 619
Chapter 14: Scale Out with SSIS 2017 620
Introduction 620
SQL Server 2017 download and setup 621
Getting ready 621
How to do it... 621
There's more... 635
SQL Server client tools setup 635
Getting ready 636
How to do it... 636Table of Contents
[ ix ]
Configuring SSIS for scale out executions 643
Getting ready 643
How to do it... 644
There's more... 648
Executing a package using scale out functionality 648
Getting ready 648
How to do it... 649
Other Books You May Enjoy 659
Index 662
Code: https://github.com/PacktPublishing/Mastering-SQL-Server-2017-/archive/master.zip

Microsoft SQL Server

Post 22-May-2020 17:10

[Quote]

Seven Databases in Seven Weeks: A Guide to Modern Databases and the NoSQL Movement, 2nd edition / Семь баз данных за семь недель: руководство по современным базам данных и движению NoSQL, 2-е издание
Год издания: 2018
Автор: Perkins L., Redmond E., Wilson J. / Перкинс Л., Редмонд Э., Уилсон Дж.
Издательство: The Pragmatic Bookshelf
ISBN: 978-1-68050-253-4
Серия: The Pragmatic Programmers
Язык: Английский
Формат: PDF
Качество: Издательский макет или текст (eBook)
Интерактивное оглавление: Да
Количество страниц: 354
Описание: Data is getting bigger and more complex by the day, and so are the choices in handling that data. As a modern application developer you need to understand the emerging field of data management, both RDBMS and NoSQL. Seven Databases in Seven Weeks takes you on a tour of some of the hottest open source databases today. In the tradition of Bruce A. Tate's Seven Languages in Seven Weeks, this book goes beyond your basic tutorial to explore the essential concepts at the core each technology.
Redis, Neo4J, CouchDB, MongoDB, HBase, Riak and Postgres. With each database, you'll tackle a real-world data problem that highlights the concepts and features that make it shine. You'll explore the five data models employed by these databases-relational, key/value, columnar, document and graph-and which kinds of problems are best suited to each.
You'll learn how MongoDB and CouchDB are strikingly different, and discover the Dynamo heritage at the heart of Riak. Make your applications faster with Redis and more connected with Neo4J. Use MapReduce to solve Big Data problems. Build clusters of servers using scalable services like Amazon's Elastic Compute Cloud (EC2).
Discover the CAP theorem and its implications for your distributed data. Understand the tradeoffs between consistency and availability, and when you can use them to your advantage. Use multiple databases in concert to create a platform that's more than the sum of its parts, or find one that meets all your needs at once.
Seven Databases in Seven Weeks will take you on a deep dive into each of the databases, their strengths and weaknesses, and how to choose the ones that fit your needs.

Примеры страниц

Оглавление

Acknowledgments . . . . . . . . . . . vii
Preface . . . . . . . . . . . . . . ix
1. Introduction . . . . . . . . . . . . . 1
It Starts with a Question 2
The Genres 3
Onward and Upward 8
2. PostgreSQL . . . . . . . . . . . . . 9
That’s Post-greS-Q-L 9
Day 1: Relations, CRUD, and Joins 10
Day 2: Advanced Queries, Code, and Rules 21
Day 3: Full Text and Multidimensions 36
Wrap-Up 50
3. HBase . . . . . . . . . . . . . . 53
Introducing HBase 54
Day 1: CRUD and Table Administration 55
Day 2: Working with Big Data 67
Day 3: Taking It to the Cloud 82
Wrap-Up 88
4. MongoDB . . . . . . . . . . . . . 93
Hu(mongo)us 93
Day 1: CRUD and Nesting 94
Day 2: Indexing, Aggregating, Mapreduce 110
Day 3: Replica Sets, Sharding, GeoSpatial, and GridFS 124
Wrap-Up 132
5. CouchDB . . . . . . . . . . . . . 135
Relaxing on the Couch 135Day 1: CRUD, Fauxton, and cURL Redux 137
Day 2: Creating and Querying Views 145
Day 3: Advanced Views, Changes API, and Replicating Data 158
Wrap-Up 174
6. Neo4J . . . . . . . . . . . . . . 177
Neo4j Is Whiteboard Friendly 177
Day 1: Graphs, Cypher, and CRUD 179
Day 2: REST, Indexes, and Algorithms 189
Day 3: Distributed High Availability 202
Wrap-Up 207
7. DynamoDB . . . . . . . . . . . . . 211
DynamoDB: The “Big Easy” of NoSQL 211
Day 1: Let’s Go Shopping! 216
Day 2: Building a Streaming Data Pipeline 233
Day 3: Building an “Internet of Things” System
Around DynamoDB 246
Wrap-Up 255
8. Redis . . . . . . . . . . . . . . 259
Data Structure Server Store 259
Day 1: CRUD and Datatypes 260
Day 2: Advanced Usage, Distribution 274
Day 3: Playing with Other Databases 289
Wrap-Up 303
9. Wrapping Up . . . . . . . . . . . . 305
Genres Redux 305
Making a Choice 309
Where Do We Go from Here? 309
A1. Database Overview Tables . . . . . . . . . 311
A2. The CAP Theorem . . . . . . . . . . . 315
Eventual Consistency 316
CAP in the Wild 317
The Latency Trade-Off 317
Bibliography . . . . . . . . . . . . 319
Index . . . . . . . . . . . . . . 321

Microsoft SQL Server

Post 22-May-2020 16:00

[Quote]

Pro SQL Server 2019 Administration, 2nd edition / Профессиональное администрирование SQL Server 2019, 2-е издание
Год издания: 2019
Автор: Carter P.A. / Картер П.А.
Издательство: Apress
ISBN: 978-1-4842-5089-1
Язык: Английский
Формат: PDF
Качество: Издательский макет или текст (eBook)
Интерактивное оглавление: Да
Количество страниц: 941
Описание: Use this comprehensive guide for the SQL Server DBA, covering all that practicing database administrators need to know to get their daily work done. Updated for SQL Server 2019, this edition includes coverage of new features such as Memory-optimized TempDB Metadata, and Always Encrypted with Secure Enclaves. Other new content includes coverage of Query Store, resumable index operations, installation on Linux, and containerized SQL.
Pro SQL Server 2019 Administration takes DBAs on a journey that begins with planning their SQL Server deployment and runs through installing and configuring the instance, administering and optimizing database objects, and ensuring that data is secure and highly available. Finally, readers will learn how to perform advanced maintenance and tuning techniques.
This book teaches you to make the most of new SQL Server 2019 functionality, including Data Discovery and Classification. The book promotes best-practice installation, shows how to configure for scalability and high workloads, and demonstrates the gamut of database-level maintenance tasks such as index maintenance, database consistency checks, and table optimizations.

Примеры страниц

Оглавление

Part I: Installation and Configuration
1. Planning the Deployment
2. GUI Installation
3. Server Core Installation
4. Installation on Heterogeneous Operating Systems
5. Configuring the Instance
Part II: Database Administration
6. Database Configuration
7. Table Optimizations
8. Indexes and Statistics
9. Database Consistency
Part III: Security, Resilience, and Scaling Workloads
10. SQL Server Security Model
11. Encryption
12. Backups and Restores
13. High Availability and Disaster Recovery Concepts
14. Implementing AlwaysOn Availability Groups
15. Implementing Log Shipping
16. Scaling Workloads
Part IV: Performance and Maintenance
17. SQL Server Metadata
18. Locking and Blocking
19. Extended Events
20. Query Store
21. Distributed Replay
22. Automating Maintenance Routines
23. Policy-Based Management
24. Resource Governor
Исходный код: https://github.com/Apress/pro-sql-server-2019-admin

Microsoft SQL Server

Post 22-May-2020 16:00

[Quote]

Pro T-SQL 2019 / Профессиональный T-SQL 2019
Год издания: 2019
Автор: Noble E. / Ноубл Э.
Издательство: Apress
ISBN: 978-1-4842-5590-2
Язык: Английский
Формат: PDF
Качество: Издательский макет или текст (eBook)
Интерактивное оглавление: Да
Количество страниц: 410
Описание: Design and write simple and efficient T-SQL code in SQL Server 2019 and beyond. Writing T-SQL that pulls back correct results can be challenging. This book provides the help you need in writing T-SQL that performs fast and is easy to maintain. You also will learn how to implement version control, testing, and deployment strategies.
Hands-on examples show modern T-SQL practices and provide straightforward explanations. Attention is given to selecting the right data types and objects when designing T-SQL solutions. Author Elizabeth Noble teaches you how to improve your T-SQL performance through good design practices that benefit programmers and ultimately the users of the applications. You will know the common pitfalls of writing T-SQL and how to avoid those pitfalls going forward.
What You Will Learn
Choose correct data types and database objects when designing T-SQL
Write T-SQL that searches data efficiently and uses hardware effectively
Implement source control and testing methods to streamline the deployment process
Design T-SQL that can be enhanced or modified with less effort
Plan for long-term data management and storage

Примеры страниц

Оглавление

Part I. Building Understandable T-SQL
1. Data Types
2. Database Objects
3. Standardizing T-SQL
4. Designing T-SQL
Part II. Building Performant T-SQL
5. Set-Based Design
6. Hardware Usage
7. Execution Plans
8. Optimize T-SQL
Part III. Building Manageable T-SQL
9. Coding Standards
10. Source Control
11. Testing
12. Deployment
Part IV. Building Maintainable T-SQL
13. Functional Design
14. Logging
15. Managing Data Growth
Исходный код: https://github.com/Apress/pro-t-sql-2019

Microsoft SQL Server

Post 20-May-2020 20:40

[Quote]

SQL for Data Analytics / SQL для аналитики данных
Год издания: 2019
Автор: Malik U., Goldwasser M., Johnston B. / Малик У., Гольдвассер М., Джонстон Б.
Издательство: Packt Publishing
ISBN: 978-1-78980-735-6
Язык: Английский
Формат: PDF
Качество: Издательский макет или текст (eBook)
Интерактивное оглавление: Да
Количество страниц: 387
Описание: SQL for Data Analytics covers everything you need progress from simply knowing basic SQL to telling stories and identifying trends in data. You'll be able to start exploring your data by identifying patterns and unlocking deeper insights. You'll also gain experience working with different types of data in SQL, including time-series, geospatial, and text data. Finally, you'll understand how to become productive with SQL with the help of profiling and automation to gain insights faster.
By the end of the book, you'll able to use SQL in everyday business scenarios efficiently and look at data with the critical eye of analytics professional.

Примеры страниц

Оглавление

Preface i
Understanding and Describing Data 1
Introduction .................................................................................................... 2
The World of Data .......................................................................................... 2
Types of Data ........................................................................................................ 2
Data Analytics and Statistics .............................................................................. 3
Types of Statistics ................................................................................................ 4
Activity 1: Classifying a New Dataset ................................................................. 5
Methods of Descriptive Statistics ................................................................ 6
Univariate Analysis .............................................................................................. 6
Data Frequency Distribution .............................................................................. 6
Exercise 1: Creating a Histogram ....................................................................... 7
Exercise 2: Calculating the Quartiles for Add-on Sales ................................. 13
Central Tendency ............................................................................................... 16
Exercise 3: Calculating the Central Tendency of Add-on Sales .................... 17
Dispersion ........................................................................................................... 19
Exercise 4: Dispersion of Add-on Sales ............................................................ 20
Bivariate Analysis ............................................................................................... 21
Scatterplots ........................................................................................................ 21
Exercise 5: Calculating the Pearson Correlation Coefficient
for Two Variables ............................................................................................... 29
Activity 2: Exploring Dealership Sales Data .................................................... 36
Working with Missing Data ............................................................................... 36Statistical Significance Testing ................................................................... 37
Common Statistical Significance Tests ............................................................ 39
Summary ....................................................................................................... 39
The Basics of SQL for Analytics 43
Introduction .................................................................................................. 44
Relational Databases and SQL ................................................................... 44
Advantages and Disadvantages of SQL Databases ....................................... 45
Basic Data Types of SQL .............................................................................. 47
Numeric ............................................................................................................... 47
Character ............................................................................................................ 48
Boolean ............................................................................................................... 48
Datetime ............................................................................................................. 49
Data Structures: JSON and Arrays ................................................................... 49
Reading Tables: The SELECT Query ............................................................ 50
Basic Anatomy and Working of a SELECT Query ............................................ 50
Basic Keywords in a SELECT Query .................................................................. 51
Exercise 6: Querying the Salespeople Table Using
Basic Keywords in a SELECT Query .................................................................. 57
Activity 3: Querying the customers Table Using Basic Keywords
in a SELECT Query .............................................................................................. 59
Creating Tables ............................................................................................ 60
Creating Blank Tables ........................................................................................ 60
Exercise 7: Creating a Table in SQL .................................................................. 61
Creating Tables with SELECT ............................................................................. 62
Updating Tables ........................................................................................... 62
Adding and Removing Columns ....................................................................... 62Adding New Data ............................................................................................... 63
Updating Existing Rows ..................................................................................... 64
Exercise 8: Updating Tables in SQL .................................................................. 65
Deleting Data and Tables ............................................................................ 66
Deleting Values from a Row ............................................................................. 66
Deleting Rows from a Table .............................................................................. 66
Deleting Tables ................................................................................................... 67
Exercise 9: Unnecessary Reference Table ....................................................... 67
Activity 4: Marketing Operations ..................................................................... 68
SQL and Analytics ........................................................................................ 69
Summary ....................................................................................................... 69
SQL for Data Preparation 71
Introduction .................................................................................................. 72
Assembling Data .......................................................................................... 72
Connecting Tables Using JOIN .......................................................................... 72
Types of Joins ...................................................................................................... 74
Exercise 10: Using Joins to Analyze Sales Dealership .................................... 82
Subqueries .......................................................................................................... 83
Unions ................................................................................................................. 84
Exercise 11: Generating an Elite Customer Party
Guest List using UNION ..................................................................................... 86
Common Table Expressions ............................................................................. 88
Transforming Data ....................................................................................... 89
Exercise 12: Using the CASE WHEN Function to Get Regional Lists ............. 90
Activity 5: Building a Sales Model Using SQL Techniques ............................. 98
Summary ....................................................................................................... 99Aggregate Functions for Data Analysis 101
Introduction ................................................................................................ 102
Aggregate Functions .................................................................................. 102
Exercise 13: Using Aggregate Functions to Analyze Data .......................... 105
Aggregate Functions with GROUP BY ...................................................... 106
GROUP BY ....................................................................................................... 106
Multiple Column GROUP BY ......................................................................... 110
Exercise 14: Calculating the Cost by Product Type Using GROUP BY ....... 111
Grouping Sets .................................................................................................. 112
Ordered Set Aggregates ................................................................................. 114
The HAVING Clause .................................................................................... 115
Exercise 15: Calculating and Displaying Data Using
the HAVING Clause ......................................................................................... 116
Using Aggregates to Clean Data and Examine Data Quality ................ 117
Finding Missing Values with GROUP BY ....................................................... 117
Measuring Data Quality with Aggregates .................................................... 118
Activity 6: Analyzing Sales Data Using Aggregate Functions ..................... 119
Summary ..................................................................................................... 120
Window Functions for Data Analysis 123
Introduction ................................................................................................ 124
Window Functions ..................................................................................... 124
The Basics of Window Functions ................................................................... 125
Exercise 16: Analyzing Customer Data Fill Rates over Time ...................... 130
The WINDOW Keyword ................................................................................... 132
Statistics with Window Functions ............................................................ 133
Exercise 17: Rank Order of Hiring ................................................................. 134
Window Frame ................................................................................................ 135Exercise 18: Team Lunch Motivation ............................................................ 138
Activity 7: Analyzing Sales Using Window Frames
and Window Functions ................................................................................... 140
Summary ..................................................................................................... 141
Importing and Exporting Data 143
Introduction ................................................................................................ 144
The COPY Command .................................................................................. 144
Getting Started with COPY ............................................................................. 145
Copying Data with psql .................................................................................. 146
Configuring COPY and \copy ......................................................................... 148
Using COPY and \copy to Bulk Upload Data to Your Database ................. 148
Exercise 19: Exporting Data to a File for Further Processing in Excel ...... 150
Using R with Our Database ....................................................................... 154
Why Use R? ....................................................................................................... 154
Getting Started with R .................................................................................... 154
Using Python with Our Database ............................................................. 157
Why Use Python? ............................................................................................ 157
Getting Started with Python .......................................................................... 158
Exercise 20: Exporting Data from a Database within Python ................... 158
Improving Postgres Access in Python with SQLAlchemy and Pandas ..... 160
What is SQLAlchemy? ..................................................................................... 161
Using Python with Jupyter Notebooks ......................................................... 162
Reading and Writing to our Database with Pandas .................................... 164
Performing Data Visualization with Pandas ................................................ 165
Exercise 21: Reading Data and Visualizing Data in Python ........................ 165
Writing Data to the Database Using Python ................................................ 167
Improving Python Write Speed with COPY .................................................. 168Reading and Writing CSV Files with Python ................................................. 170
Best Practices for Importing and Exporting Data .................................. 171
Going Password-Less ...................................................................................... 171
Activity 8: Using an External Dataset to Discover Sales Trends ................ 172
Summary ..................................................................................................... 173
Analytics Using Complex Data Types 175
Introduction ................................................................................................ 176
Date and Time Data Types for Analysis .................................................. 176
Starting with the Date Type ........................................................................... 177
Transforming Date Types ............................................................................... 180
Intervals ........................................................................................................... 182
Exercise 22: Analytics with Time Series Data .............................................. 184
Performing Geospatial Analysis in Postgres ........................................... 186
Latitude and Longitude .................................................................................. 186
Representing Latitude and Longitude in Postgres ..................................... 187
Exercise 23: Geospatial Analysis ................................................................... 189
Using Array Data Types in Postgres ......................................................... 191
Starting with Arrays ........................................................................................ 191
Using JSON Data Types in Postgres ......................................................... 194
JSONB: Pre-Parsed JSON ................................................................................. 197
Accessing Data from a JSON or JSONB Field ................................................ 197
Creating and Modifying Data in a JSONB Field ............................................ 200
Exercise 24: Searching through JSONB ......................................................... 200
Text Analytics Using Postgres .................................................................. 202
Tokenizing Text ............................................................................................... 202
Exercise 25: Performing Text Analytics ........................................................ 204
Performing Text Search ................................................................................. 208Optimizing Text Search on Postgres ............................................................. 211
Activity 9: Sales Search and Analysis ............................................................ 213
Summary ..................................................................................................... 214
Performant SQL 217
Introduction ................................................................................................ 218
Database Scanning Methods .................................................................... 219
Query Planning ................................................................................................ 220
Scanning and Sequential Scans ..................................................................... 220
Exercise 26: Interpreting the Query Planner ............................................... 222
Activity 10: Query Planning ............................................................................ 226
Index Scanning ................................................................................................ 227
The B-tree Index .............................................................................................. 228
Exercise 27: Creating an Index Scan ............................................................. 230
Activity 11: Implementing Index Scans ........................................................ 235
Hash Index ....................................................................................................... 236
Exercise 28: Generating Several Hash Indexes
to Investigate Performance ........................................................................... 237
Activity 12: Implementing Hash Indexes ...................................................... 242
Effective Index Use ......................................................................................... 243
Performant Joins ........................................................................................ 244
Exercise 29: Determining the Use of Inner Joins ......................................... 245
Activity 13: Implementing Joins ..................................................................... 251
Functions and Triggers .............................................................................. 252
Function Definitions ....................................................................................... 253
Exercise 30: Creating Functions without Arguments ................................. 254
Activity 14: Defining a Maximum Sale Function .......................................... 257
Exercise 31: Creating Functions with Arguments
Using a Single Function .................................................................................. 258Activity 15: Creating Functions with Arguments ......................................... 260
Triggers ............................................................................................................. 261
Exercise 32: Creating Triggers to Update Fields .......................................... 263
Activity 16: Creating a Trigger to Track Average Purchases ...................... 268
Killing Queries ................................................................................................. 269
Exercise 33: Canceling a Long Query ............................................................ 269
Activity 17: Terminating a Long Query ......................................................... 271
Summary ..................................................................................................... 272
Using SQL to Uncover the Truth – a Case Study 275
Introduction ................................................................................................ 276
Case Study .................................................................................................. 276
Scientific Method ............................................................................................ 276
Exercise 34: Preliminary Data Collection Using SQL Techniques .............. 277
Exercise 35: Extracting the Sales Information ............................................. 279
Activity 18: Quantifying the Sales Drop ........................................................ 284
Exercise 36: Launch Timing Analysis ............................................................ 286
Activity 19: Analyzing the Difference in the Sales Price Hypothesis ......... 293
Exercise 37: Analyzing Sales Growth by Email Opening Rate .................... 296
Exercise 38: Analyzing the Performance
of the Email Marketing Campaign ................................................................ 305
Conclusions ...................................................................................................... 309
In-Field Testing ................................................................................................ 310
Summary ..................................................................................................... 311
Appendix 313
Index 361

Microsoft SQL Server

Post 15-May-2020 12:35

[Quote]

Путь программиста T-SQL
Год издания: 2020
Автор: Трунин В.
Издательство: info-comp.ru
ISBN: отсутствует
Язык: Русский
Формат: PDF
Качество: Издательский макет или текст (eBook)
Интерактивное оглавление: Да
Количество страниц: 204
Описание: Данная книга в первую очередь предназначена, как я уже сказал, для начинающих программистов SQL, в частности T-SQL. Если направление Вашей работы больше связано с администрированием Microsoft SQL Server, то Вам лучше выбрать другую книгу, так как книга «Путь программиста T-SQL» ориентирована все-таки на разработку, хотя, если Вы начинающий администратор, она будет Вам полезна, так как знания и умения писать SQL запросы требуется всем, кто работает с SQL сервером, к тому же тема базового администрирования в книге затронута.
Если говорить в целом, в книге затронуты все те моменты, с которыми Вы столкнетесь, когда будете разрабатывать инструкции на T-SQL, писать запросы на выборку, в общем, просто работать с Microsoft SQL Server. Книгу можно смело рекомендовать обычным администраторам, программистам, инженерам, в обязанности которых входит сопровождение Microsoft SQL Server.

Примеры страниц

Оглавление

Оглавление 2
Предисловие 8
Введение 10
Об авторе 10
Начало пути программиста 10
Развитие и становление программистом 11
Благодарность 11
Глава 1 - Базы данных 12
Глава 2 -Типы данных в SQL Server 34
Глава 3 - Таблицы 42
Глава 4 - Выборка данных – оператор SELECT 47
Глава 5 - Представления 77
Глава 6 - Модификация данных в таблицах 82
Глава 7 - Индексы 99
Глава 8 - Ограничения 107
Глава 9 - Программирование на T-SQL 114
Глава 10 - Функции в языке T-SQL 128
Глава 11 - Хранимые процедуры 142
Глава 12 - Триггеры в T-SQL 147
Глава 13 - Курсоры в T-SQL 153
Глава 14 - Транзакции в T-SQL 156
Глава 15 - Работа с XML в T-SQL 160
Глава 16 - Дополнительные полезные возможности языка T-SQL 170
Глава 17 - Администрирование сервера и базы данных 191
Глава 18 - Microsoft SQL Server во всей красе! 198
Заключение 201

Microsoft SQL Server

Post 14-May-2020 18:20

[Quote]

Principles Of Distributed Database System, 4th edition / Принципы Распределенных Систем баз данных, 4-ое издание
Год издания: 2020
Автор: Özsu M.T., Valduriez P. / Озсу М.Т., Валдуриес П.
Издательство: Springer
ISBN: 978-3-030-26253-2
Язык: Английский
Формат: PDF
Качество: Издательский макет или текст (eBook)
Интерактивное оглавление: Да
Количество страниц: 681
Описание: The fourth edition of this classic textbook provides major updates. This edition has completely new chapters on Big Data Platforms (distributed storage systems, MapReduce, Spark, data stream processing, graph analytics) and on NoSQL, NewSQL and polystore systems. It also includes an updated web data management chapter that includes RDF and semantic web discussion, an integrated database integration chapter focusing both on schema integration and querying over these systems. The peer-to-peer computing chapter has been updated with a discussion of blockchains. The chapters that describe classical distributed and parallel database technology have all been updated.
The new edition covers the breadth and depth of the field from a modern viewpoint. Graduate students, as well as senior undergraduate students studying computer science and other related fields will use this book as a primary textbook. Researchers working in computer science will also find this textbook useful.
This textbook has a companion web site that includes background information on relational database fundamentals, query processing, transaction management, and computer networks for those who might need this background. The web site also includes all the figures and presentation slides as well as solutions to exercises (restricted to instructors).

Примеры страниц

Оглавление

1 Introduction ................................................................. 1
1.1 What Is a Distributed Database System? ............................ 1
1.2 History of Distributed DBMS ....................................... 3
1.3 Data Delivery Alternatives ........................................... 5
1.4 Promises of Distributed DBMSs .................................... 7
1.4.1 Transparent Management of Distributed and
Replicated Data............................................ 7
1.4.2 Reliability Through Distributed Transactions............ 10
1.4.3 Improved Performance .................................... 11
1.4.4 Scalability ................................................. 13
1.5 Design Issues ......................................................... 13
1.5.1 Distributed Database Design ............................. 13
1.5.2 Distributed Data Control .................................. 14
1.5.3 Distributed Query Processing............................. 14
1.5.4 Distributed Concurrency Control ......................... 14
1.5.5 Reliability of Distributed DBMS ......................... 15
1.5.6 Replication................................................. 15
1.5.7 Parallel DBMSs ........................................... 16
1.5.8 Database Integration ...................................... 16
1.5.9 Alternative Distribution Approaches ..................... 16
1.5.10 Big Data Processing and NoSQL......................... 16
1.6 Distributed DBMS Architectures .................................... 17
1.6.1 Architectural Models for Distributed DBMSs ........... 17
1.6.2 Client/Server Systems..................................... 20
1.6.3 Peer-to-Peer Systems...................................... 22
1.6.4 Multidatabase Systems.................................... 25
1.6.5 Cloud Computing ......................................... 27
1.7 Bibliographic Notes .................................................. 31
xixii Contents
2 Distributed and Parallel Database Design ............................... 33
2.1 Data Fragmentation .................................................. 35
2.1.1 Horizontal Fragmentation................................. 37
2.1.2 Vertical Fragmentation .................................... 52
2.1.3 Hybrid Fragmentation..................................... 65
2.2 Allocation............................................................. 66
2.2.1 Auxiliary Information ..................................... 68
2.2.2 Allocation Model.......................................... 69
2.2.3 Solution Methods ......................................... 72
2.3 Combined Approaches ............................................... 72
2.3.1 Workload-Agnostic Partitioning Techniques ............ 73
2.3.2 Workload-Aware Partitioning Techniques ............... 74
2.4 Adaptive Approaches ................................................ 78
2.4.1 Detecting Workload Changes ............................. 79
2.4.2 Detecting Affected Items ................................. 79
2.4.3 Incremental Reconfiguration.............................. 80
2.5 Data Directory ........................................................ 82
2.6 Conclusion ............................................................ 83
2.7 Bibliographic Notes .................................................. 84
3 Distributed Data Control .................................................. 91
3.1 View Management ................................................... 92
3.1.1 Views in Centralized DBMSs............................. 92
3.1.2 Views in Distributed DBMSs ............................. 95
3.1.3 Maintenance of Materialized Views ...................... 96
3.2 Access Control ....................................................... 102
3.2.1 Discretionary Access Control............................. 103
3.2.2 Mandatory Access Control ............................... 106
3.2.3 Distributed Access Control ............................... 108
3.3 Semantic Integrity Control ........................................... 110
3.3.1 Centralized Semantic Integrity Control .................. 111
3.3.2 Distributed Semantic Integrity Control................... 116
3.4 Conclusion ............................................................ 123
3.5 Bibliographic Notes .................................................. 123
4 Distributed Query Processing ............................................ 129
4.1 Overview.............................................................. 130
4.1.1 Query Processing Problem................................ 130
4.1.2 Query Optimization ....................................... 133
4.1.3 Layers Of Query Processing .............................. 136
4.2 Data Localization..................................................... 140
4.2.1 Reduction for Primary Horizontal Fragmentation ....... 141
4.2.2 Reduction with Join ....................................... 142
4.2.3 Reduction for Vertical Fragmentation .................... 143
4.2.4 Reduction for Derived Fragmentation.................... 145
4.2.5 Reduction for Hybrid Fragmentation..................... 148Contents xiii
4.3 Join Ordering in Distributed Queries ................................ 149
4.3.1 Join Trees .................................................. 149
4.3.2 Join Ordering .............................................. 151
4.3.3 Semijoin-Based Algorithms .............................. 153
4.3.4 Join Versus Semijoin ...................................... 156
4.4 Distributed Cost Model .............................................. 157
4.4.1 Cost Functions............................................. 157
4.4.2 Database Statistics ........................................ 159
4.5 Distributed Query Optimization ..................................... 161
4.5.1 Dynamic Approach........................................ 161
4.5.2 Static Approach ........................................... 165
4.5.3 Hybrid Approach .......................................... 169
4.6 Adaptive Query Processing .......................................... 173
4.6.1 Adaptive Query Processing Process ...................... 174
4.6.2 Eddy Approach ............................................ 176
4.7 Conclusion ............................................................ 177
4.8 Bibliographic Notes .................................................. 178
5 Distributed Transaction Processing ...................................... 183
5.1 Background and Terminology ....................................... 184
5.2 Distributed Concurrency Control .................................... 188
5.2.1 Locking-Based Algorithms ............................... 189
5.2.2 Timestamp-Based Algorithms ............................ 197
5.2.3 Multiversion Concurrency Control ....................... 203
5.2.4 Optimistic Algorithms .................................... 205
5.3 Distributed Concurrency Control Using Snapshot Isolation ....... 206
5.4 Distributed DBMS Reliability ....................................... 209
5.4.1 Two-Phase Commit Protocol ............................. 211
5.4.2 Variations of 2PC.......................................... 217
5.4.3 Dealing with Site Failures ................................ 220
5.4.4 Network Partitioning ...................................... 227
5.4.5 Paxos Consensus Protocol ................................ 231
5.4.6 Architectural Considerations ............................. 234
5.5 Modern Approaches to Scaling Out Transaction Management .... 236
5.5.1 Spanner .................................................... 237
5.5.2 LeanXcale ................................................. 237
5.6 Conclusion ............................................................ 239
5.7 Bibliographic Notes .................................................. 241
6 Data Replication ........................................................... 247
6.1 Consistency of Replicated Databases ............................... 249
6.1.1 Mutual Consistency ....................................... 249
6.1.2 Mutual Consistency Versus Transaction Consistency ... 251
6.2 Update Management Strategies...................................... 252
6.2.1 Eager Update Propagation ................................ 253
6.2.2 Lazy Update Propagation ................................. 254xiv Contents
6.2.3 Centralized Techniques ................................... 254
6.2.4 Distributed Techniques.................................... 255
6.3 Replication Protocols ................................................ 255
6.3.1 Eager Centralized Protocols .............................. 256
6.3.2 Eager Distributed Protocols............................... 262
6.3.3 Lazy Centralized Protocols ............................... 262
6.3.4 Lazy Distributed Protocols ............................... 268
6.4 Group Communication ............................................... 269
6.5 Replication and Failures ............................................. 272
6.5.1 Failures and Lazy Replication ............................ 273
6.5.2 Failures and Eager Replication ........................... 273
6.6 Conclusion ............................................................ 276
6.7 Bibliographic Notes .................................................. 277
7 Database Integration—Multidatabase Systems ......................... 281
7.1 Database Integration ................................................. 282
7.1.1 Bottom-Up Design Methodology ........................ 283
7.1.2 Schema Matching ......................................... 287
7.1.3 Schema Integration ........................................ 296
7.1.4 Schema Mapping .......................................... 298
7.1.5 Data Cleaning ............................................. 306
7.2 Multidatabase Query Processing .................................... 307
7.2.1 Issues in Multidatabase Query Processing ............... 308
7.2.2 Multidatabase Query Processing Architecture ........... 309
7.2.3 Query Rewriting Using Views ............................ 311
7.2.4 Query Optimization and Execution ...................... 317
7.2.5 Query Translation and Execution......................... 329
7.3 Conclusion ............................................................ 332
7.4 Bibliographic Notes .................................................. 334
8 Parallel Database Systems ................................................. 349
8.1 Objectives............................................................. 350
8.2 Parallel Architectures ................................................ 352
8.2.1 General Architecture ...................................... 353
8.2.2 Shared-Memory ........................................... 355
8.2.3 Shared-Disk ............................................... 357
8.2.4 Shared-Nothing............................................ 358
8.3 Data Placement ....................................................... 359
8.4 Parallel Query Processing............................................ 362
8.4.1 Parallel Algorithms for Data Processing ................. 362
8.4.2 Parallel Query Optimization .............................. 369
8.5 Load Balancing ....................................................... 374
8.5.1 Parallel Execution Problems .............................. 374
8.5.2 Intraoperator Load Balancing............................. 376
8.5.3 Interoperator Load Balancing............................. 378
8.5.4 Intraquery Load Balancing ............................... 378Contents xv
8.6 Fault-Tolerance ....................................................... 383
8.7 Database Clusters .................................................... 384
8.7.1 Database Cluster Architecture ............................ 385
8.7.2 Replication................................................. 386
8.7.3 Load Balancing............................................ 386
8.7.4 Query Processing.......................................... 387
8.8 Conclusion ............................................................ 390
8.9 Bibliographic Notes .................................................. 390
9 Peer-to-Peer Data Management........................................... 395
9.1 Infrastructure ......................................................... 398
9.1.1 Unstructured P2P Networks .............................. 399
9.1.2 Structured P2P Networks ................................. 402
9.1.3 Superpeer P2P Networks ................................. 406
9.1.4 Comparison of P2P Networks ............................ 408
9.2 Schema Mapping in P2P Systems ................................... 408
9.2.1 Pairwise Schema Mapping................................ 408
9.2.2 Mapping Based on Machine Learning Techniques ...... 409
9.2.3 Common Agreement Mapping ........................... 410
9.2.4 Schema Mapping Using IR Techniques .................. 411
9.3 Querying Over P2P Systems......................................... 411
9.3.1 Top-k Queries ............................................. 412
9.3.2 Join Queries ............................................... 424
9.3.3 Range Queries ............................................. 425
9.4 Replica Consistency .................................................. 428
9.4.1 Basic Support in DHTs ................................... 429
9.4.2 Data Currency in DHTs ................................... 431
9.4.3 Replica Reconciliation .................................... 432
9.5 Blockchain ............................................................ 436
9.5.1 Blockchain Definition ..................................... 437
9.5.2 Blockchain Infrastructure ................................. 438
9.5.3 Blockchain 2.0............................................. 442
9.5.4 Issues....................................................... 443
9.6 Conclusion ............................................................ 444
9.7 Bibliographic Notes .................................................. 445
10 Big Data Processing ........................................................ 449
10.1 Distributed Storage Systems ......................................... 451
10.1.1 Google File System ....................................... 453
10.1.2 Combining Object Storage and File Storage ............. 454
10.2 Big Data Processing Frameworks ................................... 455
10.2.1 MapReduce Data Processing ............................. 456
10.2.2 Data Processing Using Spark ............................. 466
10.3 Stream Data Management ........................................... 470
10.3.1 Stream Models, Languages, and Operators .............. 472
10.3.2 Query Processing over Data Streams..................... 476
10.3.3 DSS Fault-Tolerance ...................................... 483xvi Contents
10.4 Graph Analytics Platforms........................................... 486
10.4.1 Graph Partitioning......................................... 489
10.4.2 MapReduce and Graph Analytics ........................ 494
10.4.3 Special-Purpose Graph Analytics Systems .............. 495
10.4.4 Vertex-Centric Block Synchronous....................... 498
10.4.5 Vertex-Centric Asynchronous ............................ 501
10.4.6 Vertex-Centric Gather-Apply-Scatter .................... 503
10.4.7 Partition-Centric Block Synchronous Processing........ 504
10.4.8 Partition-Centric Asynchronous .......................... 506
10.4.9 Partition-Centric Gather-Apply-Scatter .................. 506
10.4.10 Edge-Centric Block Synchronous Processing ........... 507
10.4.11 Edge-Centric Asynchronous .............................. 507
10.4.12 Edge-Centric Gather-Apply-Scatter ...................... 507
10.5 Data Lakes ............................................................ 508
10.5.1 Data Lake Versus Data Warehouse ....................... 508
10.5.2 Architecture ............................................... 510
10.5.3 Challenges ................................................. 511
10.6 Conclusion ............................................................ 512
10.7 Bibliographic Notes .................................................. 512
11 NoSQL, NewSQL, and Polystores ........................................ 519
11.1 Motivations for NoSQL .............................................. 520
11.2 Key-Value Stores ..................................................... 521
11.2.1 DynamoDB ................................................ 522
11.2.2 Other Key-Value Stores ................................... 524
11.3 Document Stores ..................................................... 525
11.3.1 MongoDB ................................................. 525
11.3.2 Other Document Stores ................................... 528
11.4 Wide Column Stores ................................................. 529
11.4.1 Bigtable .................................................... 529
11.4.2 Other Wide Column Stores ............................... 531
11.5 Graph DBMSs ........................................................ 531
11.5.1 Neo4j....................................................... 532
11.5.2 Other Graph Databases ................................... 535
11.6 Hybrid Data Stores ................................................... 535
11.6.1 Multimodel NoSQL Stores ............................... 536
11.6.2 NewSQL DBMSs ......................................... 537
11.7 Polystores ............................................................. 540
11.7.1 Loosely Coupled Polystores .............................. 540
11.7.2 Tightly Coupled Polystores ............................... 544
11.7.3 Hybrid Systems ........................................... 549
11.7.4 Concluding Remarks ...................................... 553
11.8 Conclusion ............................................................ 554
11.9 Bibliographic Notes .................................................. 555Contents xvii
12 Web Data Management .................................................... 559
12.1 Web Graph Management............................................. 560
12.2 Web Search ........................................................... 562
12.2.1 Web Crawling ............................................. 563
12.2.2 Indexing ................................................... 566
12.2.3 Ranking and Link Analysis ............................... 567
12.2.4 Evaluation of Keyword Search ........................... 568
12.3 Web Querying ........................................................ 569
12.3.1 Semistructured Data Approach ........................... 570
12.3.2 Web Query Language Approach ......................... 574
12.4 Question Answering Systems........................................ 580
12.5 Searching and Querying the Hidden Web ........................... 584
12.5.1 Crawling the Hidden Web ................................ 585
12.5.2 Metasearching ............................................. 586
12.6 Web Data Integration................................................. 588
12.6.1 Web Tables/Fusion Tables ................................ 589
12.6.2 Semantic Web and Linked Open Data ................... 590
12.6.3 Data Quality Issues in Web Data Integration ............ 608
12.7 Bibliographic Notes .................................................. 615
A Overview of Relational DBMS ............................................ 619
B Centralized Query Processing............................................. 621
C Transaction Processing Fundamentals ................................... 623
D Review of Computer Networks............................................ 625
References......................................................................... 627
Index ............................................................................... 663

Microsoft SQL Server

Post 09-May-2020 03:10

[Quote]

SQL Server 2017 Query Performance Tuning
Год издания: 2018
Автор: Fritchey G.
Издательство: Apress
ISBN: 978-1-4842-3888-2
Язык: Английский
Формат: PDF/ePub
Качество: Издательский макет или текст (eBook)
Интерактивное оглавление: Да
Количество страниц: 947
Описание: Identify and fix causes of poor performance. You will learn Query Store, adaptive execution plans, and automated tuning on the Microsoft Azure SQL Database platform. Anyone responsible for writing or creating T-SQL queries will find valuable the insight into bottlenecks, including how to recognize them and eliminate them.
This book covers the latest in performance optimization features and techniques and is current with SQL Server 2017. If your queries are not running fast enough and you’re tired of phone calls from frustrated users, then this book is the answer to your performance problems.
SQL Server 2017 Query Performance Tuning is about more than quick tips and fixes. You’ll learn to be proactive in establishing performance baselines using tools such as Performance Monitor and Extended Events. You’ll recognize bottlenecks and defuse them before the phone rings. You’ll learn some quick solutions too, but emphasis is on designing for performance and getting it right. The goal is to head off trouble before it occurs.
What You'll Learn
- Use Query Store to understand and easily change query performance
- Recognize and eliminate bottlenecks leading to slow performance
- Deploy quick fixes when needed, following up with long-term solutions
- Implement best practices in T-SQL to minimize performance risk
- Design in the performance that you need through careful query and index design
- Utilize the latest performance optimization features in SQL Server 2017
- Protect query performance during upgrades to the newer versions of SQL Server

Примеры страниц

Оглавление

About the Author xxv
About the Technical Reviewer xxvii
Acknowledgments xxix
Introduction xxxi
Chapter 1: SQL Query Performance Tuning 1
Chapter 2: Memory Performance Analysis 23
Chapter 3: Disk Performance Analysis 51
Chapter 4: CPU Performance Analysis 69
Chapter 5: Creating a Baseline 87
Chapter 6: Query Performance Metrics 103
Chapter 7: Analyzing Query Performance 131
Chapter 8: Index Architecture and Behavior 185
Chapter 9: Index Analysis 231
Chapter 10: Database Engine Tuning Advisor 273
Chapter 11: Query Store 293
Chapter 12: Key Lookups and Solutions 319
Chapter 13: Statistics, Data Distribution, and Cardinality 337
Chapter 14: Index Fragmentation 403
Chapter 15: Execution Plan Generation 451
Chapter 16: Execution Plan Cache Behavior 471
Chapter 17: Parameter Sniffing 511
Chapter 18: Query Recompilation 529
Chapter 19: Query Design Analysis 569
Chapter 20: Reduce Query Resource Use 607
Chapter 21: Blocking and Blocked Processes 631
Chapter 22: Causes and Solutions for Deadlocks 699
Chapter 23: Row-by-Row Processing 721
Chapter 24: Memory-Optimized OLTP Tables and Procedures 753
Chapter 25: Automated Tuning in Azure SQL Database and SQL Server 783
Chapter 26: Database Performance Testing 825
Chapter 27: Database Workload Optimization 839
Chapter 28: SQL Server Optimization Checklist 883
Index 911

Microsoft SQL Server

Post 08-Apr-2020 18:20

[Quote]

T-SQL Window Functions, 2nd edition / Оконные функции T-SQL, 2-ое издание
Год издания: 2020
Автор: Itzik Ben-Gan / Ицик Бен-Ган
Жанр или тематика: Программирование баз данных
Издательство: Microsoft Press
ISBN: 978-0135861448
Язык: Английский
Формат: EPUB
Качество: Издательский макет или текст (eBook)
Интерактивное оглавление: Да
Количество страниц: 352
Описание: Most T-SQL developers recognize the value of window functions for data analysis calculations. But they can do far more, and recent optimizations make them even more powerful. In T-SQL Window Functions, renowned T-SQL expert Itzik Ben-Gan introduces breakthrough techniques for using them to handle many common T-SQL querying tasks with unprecedented elegance and power. Using extensive code examples, he guides you through window aggregate, ranking, distribution, offset, and ordered set functions. You'll find a detailed section on optimization, plus an extensive collection of business solutions - including novel techniques available in no other book.
Добротная книга по оконным функциям в Т-SQL во втором издании (актуально для SQL Server 2019) от эксперта в этой области.

Примеры страниц

Оглавление

Contents at a Glance
Introduction
CHAPTER 1 SQL Windowing
CHAPTER 2 A Detailed Look at Window Functions
CHAPTER 3 Ordered Set Functions
CHAPTER 4 Row-Pattern Recognition in SQL
CHAPTER 5 Optimization of Window Functions
CHAPTER 6 T-SQL Solutions Using Window Functions
Index

Microsoft SQL Server

Post 28-Mar-2020 18:10

[Quote]

Основы технологий баз данных. Второе издание
Год издания: 2020
Автор: Б. А. Новиков, Е. А. Горшкова, Н. Г. Графеева
Издательство: ДМК-Пресс
ISBN: 978-5-97060-841-8
Язык: Русский
Формат: PDF
Качество: Издательский макет или текст (eBook)
Интерактивное оглавление: Да
Количество страниц: 583
Описание: Материал первой части учебного пособия составляет основу для базового курса и содержит краткий обзор требований и критериев оценки СУБД и баз данных, теоретическую реляционную модель данных, основные конструкции языка запросов SQL, организацию доступа к базе данных PostgreSQL, вопросы проектирования приложений и основные расширения, доступные в системе PostgreSQL.
Вторая часть, добавленная в настоящем издании, содержит материал, который будет полезен разработчикам баз данных и СУБД. В ней подробно рассматриваются структуры хранения, методы выполнения и оптимизации запросов, дополнительные возможности языка SQL, средства поддержки согласованности и надежности. Рассмотрены средства программирования серверов баз данных, средства расширения функциональности PostgreSQL, вопросы создания систем с репликацией, параллельных и распределенных систем баз данных.

Примеры страниц

Мои остальные раздачи - [url=tracker.php?rid=43141996]https://rutracker.org/forum/tracker.php?rid=43141996[/url]
 

The time now is: Today 20:14

All times are GMT + 4 Hours