Microsoft Azure Database Administrator Associate: key terms

Flashcards for this exam Microsoft Azure Database Administrator Associate guide Class dates

• Active Geo-ReplicationAn Azure SQL Database feature that maintains up to four readable secondary databases in the same or other regions using asynchronous replication.Advanced
• Always EncryptedA SQL Server and Azure SQL feature that encrypts sensitive columns on the client so the database engine and DBAs never see plaintext.Advanced
• Always On Availability Groups (AG)A SQL Server high-availability and disaster-recovery feature that replicates a group of databases to secondary replicas with automatic or manual failover.Advanced
• Asynchronous ReplicationReplication in which the primary acknowledges writes immediately and ships changes to replicas afterward.Intermediate
• Auto-Failover GroupAn Azure SQL Database and Managed Instance feature that replicates databases to another region and fails them over together behind stable listener endpoints.Advanced
• Automatic TuningAn Azure SQL and SQL Server capability that automatically forces last-good plans and, in Azure SQL Database, can create or drop indexes.Advanced
• Azure SQL DatabaseAzure fully managed PaaS relational database engine based on the latest stable SQL Server.Foundational
• Azure SQL Elastic PoolA shared pool of compute and storage resources for multiple Azure SQL databases with variable, unpredictable usage.Intermediate
• Azure SQL HyperscaleAn Azure SQL Database service tier with a distributed storage architecture that supports databases up to 128 TB, fast scaling and near-instant backups.Advanced
• Azure SQL Managed InstanceAzure PaaS offering with near-100 percent compatibility with the SQL Server database engine, deployed into a virtual network.Intermediate
• B-Tree IndexA balanced tree index that keeps keys sorted and supports equality, range and ORDER BY lookups in logarithmic time.Intermediate
• BlockingA situation in which one session waits for a lock held by another session; it resolves when the holder finishes.Intermediate
• Cascading Referential ActionForeign key options (CASCADE, SET NULL, SET DEFAULT, NO ACTION, RESTRICT) that define what happens to child rows when a parent row changes.Intermediate
• CAST FunctionThe ANSI SQL function that explicitly converts a value to another data type: CAST(expression AS type).Foundational
• Change Data Capture (Data Engineering) (CDC)Identifying and capturing row-level inserts, updates and deletes in a source so only changes are propagated downstream.Intermediate
• Check ConstraintA rule that limits the values a column can accept to those that satisfy a Boolean expression.Intermediate
• Clustered IndexAn index that determines the physical order in which table rows are stored, so the table itself becomes the index's leaf level.Intermediate
• COALESCE FunctionA function that returns the first non-NULL argument in its list.Foundational
• Column-Level SecurityRestricting access to specific columns, for example through column GRANTs, masking policies or object-level security.Intermediate
• Columnstore IndexA SQL Server and Azure SQL index that stores data column by column in compressed segments for fast analytic scans.Advanced
• Common Table Expression (CTE)A named temporary result set defined with WITH that can be referenced in the statement that follows.Intermediate
• Composite IndexAn index built on two or more columns, ordered by the first column, then the second, and so on.Intermediate
• Connection PoolingReusing a pool of open database connections across requests instead of opening a new connection each time.Intermediate
• Covering IndexAn index that contains every column a query needs, so the query can be answered from the index alone.Advanced
• CursorA database object that lets procedural code process a query result one row at a time.Advanced
• Data Control Language (DCL)The SQL commands that manage permissions: GRANT, REVOKE and, in SQL Server, DENY.Foundational
• Database AuditingTracking and recording database events such as logins, queries, permission changes and data modifications for security and compliance.Intermediate
• Database IndexA data structure that speeds up row lookups on one or more columns at the cost of extra storage and slower writes.Foundational
• Database StatisticsMetadata about the distribution of values in columns and indexes, used by the optimizer to estimate row counts.Advanced
• DeadlockA cycle in which two or more transactions each hold a lock the other needs, so none can proceed.Intermediate
• Default ConstraintA constraint that supplies a value for a column when an INSERT does not specify one.Foundational
• Dirty ReadReading data written by another transaction that has not yet committed and may be rolled back.Intermediate
• DTU Purchasing ModelAn Azure SQL Database pricing model that bundles CPU, memory and I/O into Database Transaction Units across Basic, Standard and Premium tiers.Intermediate
• Dynamic Data Masking (DDM)Masking sensitive column values in query results for unauthorized users at run time without changing the stored data.Intermediate
• Dynamic SQLSQL statements built as strings at run time and then executed, for example with EXEC, sp_executesql or EXECUTE IMMEDIATE.Advanced
• Failover Cluster Instance (FCI)A SQL Server instance installed across clustered nodes with shared storage, so the entire instance fails over to another node.Advanced
• Fill FactorThe percentage of each index leaf page that SQL Server fills when building or rebuilding an index, leaving the rest free for growth.Advanced
• Full Table ScanReading every row of a table to answer a query, typically because no usable index exists or most rows are needed.Intermediate
• Full-Text IndexAn index that supports fast word- and phrase-based searches inside large text columns.Intermediate
• GRANT StatementThe DCL statement that gives privileges on database objects to users or roles.Foundational
• Identity ColumnA column whose values the database generates automatically, typically incrementing integers, often used as a surrogate key.Intermediate
• Implicit ConversionAutomatic conversion of one data type to another by the database engine when an expression mixes types.Intermediate
• Index FragmentationDisorder in index pages, either logical (pages out of order) or low page density, which reduces scan efficiency.Advanced
• Index SeekAn access method that navigates an index tree directly to the matching keys instead of reading the whole index or table.Advanced
• Isolation (ACID)The ACID property that concurrent transactions do not see each other's intermediate, uncommitted changes.Foundational
• Key LookupAn execution-plan operation in which the engine uses a nonclustered index and then fetches the remaining columns from the clustered index for each row.Advanced
• Leftmost Prefix RuleThe principle that a composite index can be used for seeks only when queries filter on its leading column(s).Advanced
• LockingThe mechanism that controls concurrent access by placing shared (read) or exclusive (write) locks on resources.Intermediate
• Log ShippingA disaster-recovery method that automatically backs up transaction logs on a primary and restores them on secondary servers.Advanced
• Lost UpdateAn anomaly in which two transactions read the same value and both write back, so one overwrites the other's change.Advanced
• Materialized ViewA database object that stores the precomputed result of a query and refreshes it as base data changes.Intermediate
• MERGE StatementA single DML statement that inserts, updates or deletes target rows based on whether they match rows in a source.Intermediate
• Multiversion Concurrency Control (MVCC)A concurrency method that keeps multiple versions of rows so readers see a consistent snapshot without blocking writers.Advanced
• Non-Repeatable ReadAn anomaly in which reading the same row twice in one transaction returns different values because another transaction committed a change in between.Intermediate
• Nonclustered IndexA separate index structure containing key values and pointers to the data rows; a table can have many.Intermediate
• Page SplitAn operation in which a full index page is divided in two to make room for a new row, causing fragmentation and extra logging.Advanced
• Parameterized QueryA query that uses placeholders for input values that are bound separately, so the input is never treated as SQL code.Intermediate
• Phantom ReadAn anomaly in which re-running a range query returns new or missing rows because another transaction inserted or deleted rows.Intermediate
• PIVOT and UNPIVOTSQL operators that rotate data: PIVOT turns row values into columns, and UNPIVOT turns columns into rows.Intermediate
• Plan RegressionA performance drop caused by the optimizer switching a query to a worse execution plan.Advanced
• Point-in-Time Recovery (PITR)Restoring a database to its exact state at a specific moment, for example just before an accidental delete.Intermediate
• Query Execution PlanThe sequence of physical operations (scans, seeks, joins, sorts) that the database chooses to run a query.Intermediate
• Query OptimizerThe database component that evaluates possible ways to run a query and picks the lowest-cost plan.Intermediate
• Query StoreA SQL Server and Azure SQL feature that captures the history of queries, plans and runtime statistics for performance troubleshooting.Advanced
• Read CommittedAn isolation level in which a transaction sees only committed data, though values may change between reads.Intermediate
• Read Committed Snapshot Isolation (RCSI)A SQL Server and Azure SQL option that makes Read Committed use row versions, so readers do not block writers.Advanced
• Read ReplicaAsynchronously replicated, read-only copy of a database used to offload read traffic and support disaster recovery.Intermediate
• Read UncommittedThe lowest isolation level, which takes no shared locks on reads and allows dirty reads.Intermediate
• Recovery ModelA SQL Server database setting (Simple, Full or Bulk-logged) that controls how the transaction log is kept and which restores are possible.Advanced
• Recursive CTEA CTE that references itself to traverse hierarchical or graph data such as org charts or bills of materials.Advanced
• Repeatable ReadAn isolation level that guarantees rows read once will return the same values if read again in the same transaction.Advanced
• REVOKE StatementThe DCL statement that removes previously granted privileges from a user or role.Foundational
• ROLLUP and CUBEGROUP BY extensions that add subtotal rows: ROLLUP for hierarchical subtotals and CUBE for every combination of the grouping columns.Advanced
• Row-Level Security (RLS)Restricting which rows a user can see based on their identity or role, enforced by the data platform or semantic model.Intermediate
• ROW_NUMBER FunctionA window function that assigns a unique sequential integer to rows within each partition according to the ORDER BY.Intermediate
• Sargable QueryA query whose predicates can use an index seek or pruning (Search ARGument ABLE), typically because columns are not wrapped in functions.Advanced
• SerializableThe strictest isolation level, in which concurrent transactions behave as if they had run one after another.Advanced
• Snapshot IsolationAn isolation level in which each transaction reads a consistent snapshot of the data as of its start, using row versions.Advanced
• SQL Data TypesThe categories of values a column can hold: numeric (INT, DECIMAL, FLOAT), character (CHAR, VARCHAR), date/time, Boolean, binary and semi-structured.Foundational
• SQL Logical Query Processing OrderThe order in which SQL logically evaluates clauses: FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, then LIMIT/TOP.Intermediate
• Stored ProcedureA named, precompiled set of SQL (and procedural) statements stored in the database and executed with CALL or EXEC.Intermediate
• Synchronous ReplicationReplication in which a write is acknowledged only after it is committed on both the primary and the replica.Intermediate
• Table PartitioningDividing a large table into smaller physical pieces by a key such as date, while queries still see a single table.Intermediate
• Transact-SQLMicrosoft's SQL dialect for SQL Server, Azure SQL, Synapse and Fabric Warehouse, with procedural extensions.Foundational
• Transaction Isolation LevelA setting that defines which concurrency anomalies a transaction may see: Read Uncommitted, Read Committed, Repeatable Read or Serializable.Intermediate
• Transaction LogA sequential record of every change made by transactions, used for recovery, rollback and replication.Intermediate
• Transaction Log BackupA backup of the transaction log records since the last log backup, enabling point-in-time recovery and freeing log space.Advanced
• TriggerProcedural code that runs automatically in response to INSERT, UPDATE or DELETE events (or DDL events) on a table.Intermediate
• TRUNCATE StatementA statement that quickly removes all rows from a table while keeping its structure, typically with minimal logging.Intermediate
• TRY_CAST FunctionA conversion function that returns NULL instead of raising an error when the value cannot be converted.Intermediate
• Unique ConstraintA constraint that prevents duplicate values in a column or column combination.Foundational
• vCore Purchasing ModelAn Azure SQL pricing model in which you choose virtual cores, memory and storage independently, with General Purpose, Business Critical and Hyperscale tiers.Intermediate
• Window FrameThe subset of rows within a partition that a window function considers, set with ROWS or RANGE BETWEEN.Advanced
• Window FunctionA function that computes a value across a set of rows related to the current row, defined with OVER(), without collapsing them.Intermediate
• Write-Ahead Logging (WAL)A technique in which changes are recorded in a durable log before being applied to data files.Advanced