liveBookmanning.com

What you will learn in 100 SQL Server Mistakes and How to Avoid Them

by Peter Carter

Read it on liveBook Get the book at manning.com

Chapter by chapter

Concepts explained in this book

• composite keyA composite key is a key whose identity is formed from multiple columns.
• recovery point objectiveRPO, or recovery point objective, is the maximum acceptable amount of data loss measured in time or recoverable data.
• index fragmentationExternal fragmentation is the degree to which an index's pages are stored out of logical sequence rather than physical order.
• database normalizationDatabase normalization is the relational database design process of structuring tables to reduce redundant data and dependency anomalies.
• capacity planningCapacity planning is the process of estimating and validating the computing resources required to meet a system's workload demands.
• artificial keyAn artificial key is a key with an assigned value that has no inherent business meaning, such as an incrementing identifier.
• natural keyA natural key is a key formed from attributes that have inherent business meaning and identify an entity.
• internal index fragmentationInternal index fragmentation is the degree to which the pages of an index are not filled to their intended fullness.
• transaction log backupA transaction log backup is a saved sequence of transaction-log records that supports recovery and log-space management.
• differential backupA differential backup contains database changes made since the full backup that established its differential base.
• SQL ServerSQL Server is Microsoft's relational database management system for storing, managing, transforming, and analyzing data.
• atomic valueAn atomic attribute contains a single indivisible value rather than a compound or repeating collection.
• full backupA full backup is a complete copy of all data and database objects needed to restore a database to the state captured by the backup.
• availability groupAn availability group is a SQL Server high-availability and disaster-recovery configuration that maintains synchronized database replicas.
• full recovery modelThe full recovery model is a SQL Server recovery configuration that preserves transaction-log records for comprehensive point-in-time recovery when transaction log backups are taken.
• data flowA data flow is an SSIS pipeline that moves rows through source, transformation, buffering, and destination components.
• xp_cmdshellxp_cmdshell is a SQL Server extended stored procedure that executes operating-system commands from within SQL Server.
• XACT_ABORTXACT_ABORT is a SQL Server session setting that causes a transaction to terminate and roll back when a run-time error occurs.
• data flow taskA data flow task is an SSIS task that moves and transforms rows between data sources and destinations through connected data-flow components.
• bad page splitA bad page split is a page split in the middle of an index that relocates data to a newly allocated page outside the existing physical order.
• candidate keyA candidate key is a minimal set of attributes that uniquely identifies every row in a relation.
• data dictionaryA data dictionary is a document describing data attributes, their meanings, rules, example values, types, dependencies, and lineage.
• first normal formFirst normal form is a relational design state in which attributes are atomic and uniquely named, repeating groups are removed, and rows are uniquely identifiable.
• functional dependencyA functional dependency is a relationship in which one set of attributes determines the value of another set of attributes.
• provenanceData lineage is the trace of a data item’s origin and transformations through systems and storage locations.
• repeating groupA repeating group is a set of columns representing multiple occurrences of the same kind of attribute within a relation.
• second normal formSecond normal form is a relational design state in which the relation is in first normal form and every attribute depends on the whole key.
• subtypingA subtype is a specialized entity type that inherits common attributes from a supertype and may add unique attributes.
• superkeyA super key is a set of attributes that uniquely identifies every row in a relation, whether or not it is minimal.
• supertypeA supertype is a generalized entity type whose common attributes and relationships can be inherited by more specialized subtypes.
• third normal formThird normal form is a relational design state in which the relation is in second normal form and non-key attributes are not transitively dependent on the key.
• transitive dependencyA transitive dependency occurs when a non-key attribute depends on another non-key attribute rather than directly on the key.
• NVARCHARNVARCHAR is a SQL Server variable-length Unicode character data type that stores characters using UTF-16 encoding.
• page densityPage density is the proportion of a data or index page occupied by stored rows or index entries relative to its usable capacity.
• sys.dm_db_index_physical_statssys.dm_db_index_physical_stats is a SQL Server dynamic management function that reports physical storage and fragmentation information for indexes.
• Simple Recovery modelThe SIMPLE recovery model is a SQL Server recovery configuration that permits transaction-log space to be reused after transactions are no longer needed for recovery and does not support transaction log backups.
• B-treeA B-tree is a balanced, multi-level tree structure whose pages contain keys and pointers for ordered data access.
• nonclustered indexA nonclustered index is a separate index structure that stores keys and row locators without determining the table's physical row order.
• OLE DB destinationAn OLE DB destination is an SSIS data-flow component that inserts incoming rows into a database through an OLE DB connection.
• principle of least privilegeLeast privilege is a security principle that grants each user only the permissions required for their duties.
• connection managerAn SSIS connection manager is a package object that stores connection information and provider settings for a data source or destination.
• SSIS packageAn SSIS package is a deployable unit that contains control-flow tasks, data flows, connections, and configuration for an integration workflow.
• ACIDACID is a set of transaction properties requiring atomicity, consistency, isolation, and durability.
• catch clauseTRY..CATCH is T-SQL control-flow syntax that executes protected code in a TRY block and transfers control to a CATCH block when a catchable error occurs.
• object namingA meaningful object name describes the purpose or contents of a database object so that its role can be understood without inspecting its implementation.
• uptimeHigh availability is the capability of a system to remain accessible and operational despite failures or maintenance events.
• sa accountThe sa account is SQL Server's built-in administrator login for mixed-mode authentication.
• versioningA version control system records successive versions of files and supports collaboration, comparison, and recovery of earlier states.
• RAISERRORRAISERROR is a SQL Server statement or function for generating an error message with specified error information and severity behavior.
• severityAn error severity level is a SQL Server classification indicating the informational, user-correctable, administrative, or fatal nature of an error.
• throwTHROW is a SQL Server statement that rethrows an original exception or raises a specified ad hoc error.
• virtual log fileA virtual log file is a logical subdivision of a SQL Server transaction log file that stores a portion of its transaction log data.
• failoverFailover is the transfer of service from a failed or inactive primary system to a secondary system.
• database transactionA database transaction is a logical unit of work whose operations are committed or rolled back as a group.
• alertingAlerting is the automated notification of personnel or systems when an event requires investigation or corrective action.
• stored procedureA stored procedure is a named, executable routine stored in a database and capable of accepting parameters and performing database operations.
• clustered indexA clustered index is an index whose key determines the physical order of rows in a table or indexed view.
• recovery modelA recovery model is a SQL Server database configuration that determines transaction-log handling and available recovery capabilities.
• disaster recoveryDisaster recovery is the set of processes and capabilities for restoring systems and data after a disruptive failure or disaster.
• ETL windowAn ETL window is the allotted time period in which an extract, transform, and load process must complete.
• retention policyA data retention policy specifies how long stored data must be preserved before it may be deleted or archived.
• self-documenting codeSelf-documenting code is code whose names and structure make its purpose understandable without relying on explanatory comments.
• fillfactorFill factor is an index configuration specifying the percentage of page space initially filled with index data.
• sysadmin fixed server roleThe sysadmin fixed server role is a SQL Server fixed server role whose members have unrestricted administrative permissions over the instance.
• transaction logA transaction log file is the physical database file that stores transaction log records for recovery and transaction processing.
• partition pruningPartition elimination is an optimization that excludes table partitions that cannot contain rows matching a query's predicates.
• deployment pipelineA CI/CD pipeline is an automated workflow that integrates, tests, and delivers software changes through continuous integration and continuous delivery or deployment.
• failover clustered instanceFailover clustering is a high-availability architecture in which clustered nodes take ownership of shared storage and database services after a node failure.
• recovery time objectiveRTO, or recovery time objective, is the maximum acceptable duration of service downtime after a disruption.
• columnstore indexA columnstore index is an index that stores and compresses column values in segments to support efficient analytical queries over large row sets.
• universally unique identifierA globally unique identifier (GUID) is a 16-byte identifier designed to be unique across a broad system scope.
• style guideA coding standard is a documented set of stylistic and technical rules for writing software consistently within a project.
• naming standardA naming convention is a prescribed pattern for naming software elements such as schemas, tables, columns, or code symbols.
• DBCC SHRINKFILEDBCC SHRINKFILE is a SQL Server command that reduces the size of a specified database file when space can be released.
• SQL injectionSQL injection is an attack technique that alters application-generated database statements by supplying crafted input that is interpreted as SQL code.
• snapshot isolationSnapshot isolation is a SQL Server transaction isolation level that gives a transaction a consistent versioned view of data as of the time the transaction begins.
• SQL Server Database ProjectVisual Studio Database Projects are Visual Studio project types for developing and managing SQL Server database schemas and deployment artifacts.
• configuration managementConfiguration management is the practice of maintaining systems in a known, consistent, and compliant state throughout their lifecycle.
• Desired State ConfigurationPowerShell Desired State Configuration is Microsoft's declarative configuration-management framework for defining and enforcing system states.
• database shrink operationA data file shrink reduces the allocated size of an individual database data file by relocating pages and releasing space to the operating system.
• maxdopMAXDOP is a SQL Server configuration setting that limits the maximum number of processors used for parallel query or index operations.
• redirect rowRedirect-row behavior sends rows that encounter component errors or truncations to a separate error output instead of discarding them or failing the component.
• schemaA schema is a named namespace in a database that contains and organizes securable objects.
• SQL Server Agent alertThe SQL Server Agent alerting subsystem is functionality that detects configured SQL Server conditions and notifies operators or initiates responses.
• artificial primary keyAn artificial primary key is a generated key introduced to identify rows independently of the table's natural business attributes.
• clustered index seekA clustered index seek traverses a clustered index to locate rows matching an index key or key range.
• index pageAn index page is a fixed-size database page containing part of an index structure and its keys or pointers.
• unique nonclustered indexA unique nonclustered index is a nonclustered index that enforces uniqueness for its key values.
• foreign keyA foreign key is a column or set of columns whose values reference a candidate key in another or the same table.
• database serviceA cloud service model in which a provider manages database infrastructure and database software for customers.
• partitioning keyA partitioning key is the column or expression whose values determine the partition containing each table row.
• compressionData compression encodes stored database data in a more compact representation to reduce storage and I/O requirements.
• primary keyA primary key is a column or set of columns that uniquely identifies each row in a table.
• SQL Server Integration ServicesSQL Server Integration Services is Microsoft's platform for building workflows that extract, transform, and load data.
• restore chainA restore chain is the ordered sequence of backup files required to restore a database to a desired state or point in time.
• data typeA data type defines the kind of value that a database column or expression can contain and how that value is interpreted.
• covering indexA covering index is an index containing all columns needed to filter, join, and return a query's results, thereby avoiding a lookup of the base table.
• atomicityAtomicity is the transaction property that makes all of a transaction's operations succeed or fail as one unit.
• lateral movementLateral movement is an attacker's progression from one compromised system or account to additional systems within an environment.
• failure tableA failure table is a database table that stores input rows rejected during an integration or loading operation for later examination.
• data truncationData truncation is an error condition in which incoming data exceeds the capacity of a destination column or configured field length.
• error outputAn error output is an SSIS component output that determines how rows affected by errors or truncations are handled.
• flat file sourceA flat file source is an SSIS data-flow component that reads rows from a text file through a flat file connection.
• software updatePatching is the process of applying software updates that fix defects, address vulnerabilities, or improve supported behavior.
• SQL Server installationA SQL Server installation is the process of deploying and configuring SQL Server software and its selected components on a host environment.
• success constraintA success constraint is an SSIS precedence constraint that permits a downstream task to run only after the preceding task succeeds.
• leaf levelThe leaf level is the lowest level of a tree-based index, containing the entries or rows used for data access.
• query plannerA query optimizer selects an efficient execution plan for a declarative database query.
• transaction log fragmentationTransaction log fragmentation is the condition in which a transaction log contains a disproportionate number of virtual log files.
• linear growth patternLinear growth is a growth pattern in which an amount increases by approximately the same quantity over equal intervals.
• SQL Server instanceA SQL Server instance is a running installation of SQL Server that provides database services and contains databases and associated server-level configuration.
• SQL Server Enterprise editionSQL Server Enterprise edition is the SQL Server edition that provides the broadest set of advanced enterprise features and capabilities.
• SQL Server Standard editionSQL Server Standard edition is a SQL Server edition providing core relational database capabilities for general production workloads.
• bulk-logged recovery modelThe bulk-logged recovery model is a SQL Server recovery configuration that minimally logs certain bulk operations while limiting point-in-time recovery.
• hierarchyidhierarchyid is a SQL Server system data type that represents positions in a hierarchical structure and provides methods for hierarchical operations.
• database management toolA database engine is the subsystem that processes queries, manages transactions, and accesses stored database data.
• execution planA query plan is the execution strategy selected or generated for processing a database query.
• xml shreddingXML shredding is the process of extracting data from XML markup and organizing it into relational values.
• application consistent snapshotAn application-consistent snapshot is a storage snapshot taken after an application has been placed in a state that permits its data to be recovered coherently.
• crash consistent snapshotA crash-consistent snapshot captures storage as it exists during or immediately after a crash without ensuring that application I/O has been flushed coherently.
• disaster recovery testingDisaster recovery testing is a planned exercise that validates whether systems, data, procedures, and personnel can recover after a disruptive failure.
• Azure SQL DatabaseAzure SQL Database is Microsoft's fully managed relational database service hosted on Microsoft Azure.
• referential integrityReferential integrity is the property that relationships between tables contain only references to existing valid key values.
• autoshrinkAutoshrink is a SQL Server database option that automatically reduces database files when they contain reclaimable unused space.
• dirty readA dirty read is the retrieval of data changed by another transaction before that change has been committed.
• aggregate transformationAn aggregate transformation groups input rows and computes aggregate values such as averages, counts, sums, minima, or maxima.
• blocking transformationA blocking transformation withholds all output until it has received and processed all input rows.
• derived column transformationA derived column transformation creates or replaces output columns by evaluating expressions for each input row.
• SSIS expressionAn SSIS expression is an expression language construct combining columns, functions, operators, and literals to produce a value in an Integration Services data flow.
• upsert destinationAn upsert destination is a data-flow component that inserts rows absent from a target and updates rows that already exist.
• attack surfaceAn attack surface is the collection of system components and exposed capabilities that could be targeted to compromise a system.
• whole-value substitution attackA whole-value substitution attack is an attack in which an attacker replaces one encrypted record value with a ciphertext copied from another record to cause unauthorized plaintext substitution.
• JSONJSON is a lightweight text format for representing structured and semi-structured data using objects, arrays, and name-value pairs.
• automationAutomation is the execution of operational or administrative tasks by software with limited manual intervention.
• golden imageA gold image is a preconfigured operating-system or software image used as a standardized basis for creating deployed systems.
• row count conditionA row count condition is a test assertion that verifies the number of rows returned by a result set.
• scalar value conditionA scalar value condition is a test assertion that compares a single returned value with an expected value.
• test conditionA test condition is an assertion that evaluates whether a test result satisfies an expected property.
• test scriptA test script is executable test code that invokes the functionality under test with specified inputs.
• common table expressionA common table expression is a named temporary result set defined within a statement that can be referenced by the statement and, in recursive form, by its own definition.
• Database MailDatabase Mail is SQL Server functionality for sending email messages from database applications and administrative processes.
• good page splitA good page split is a page allocation event that adds a new page at the end of an index without moving existing data to preserve order.
• data flow sourceA data flow source is an SSIS component that reads rows from an external data source into a data flow.
• entity-relationship diagramAn entity relationship diagram is a diagram that represents data entities, their attributes, and the relationships between them.
• data-tier applicationA data-tier application is an application architecture component that encapsulates database schema, data, and related database deployment or management artifacts.
• creative schedulingCreative scheduling is the deliberate placement of database operations in available capacity periods to avoid resource contention.
• filegroup backupA filegroup backup is a backup of one or more SQL Server database filegroups rather than the entire database.
• parameterized dateA parameterized date is a process date supplied as an input value rather than embedded as a fixed expression in code.
• rerunnable processA rerunnable process is a process that can be executed again after partial failure without code changes or unintended duplicate results.
• exponential growth patternAn exponential growth pattern is a growth model in which an amount increases by a constant proportion or multiplier over equal time intervals.
• finopsCloud financial management is the practice of monitoring, allocating, optimizing, and governing spending on cloud resources and services.
• headroomCapacity headroom is reserved unused resource capacity intended to absorb unexpected workload growth or demand variation.
• configuration resourceA configuration resource is a code unit that evaluates and changes one specific type of system configuration.
• DependsOn propertyThe DependsOn property declares that one DSC resource must be processed after another resource or configuration condition.
• DSC configurationA DSC configuration is a declarative PowerShell specification of desired system configuration resources and their settings.
• Managed Object FormatManaged Object Format is a standardized text representation of instrumentation, configuration, and management object definitions.
• set methodA set method changes a configuration resource so that it matches its desired state.
• SqlServerDscSqlServerDsc is a PowerShell module that provides DSC resources for installing and configuring SQL Server.
• test methodA test method determines whether a configuration resource currently matches its desired state.
• fixed-length stringA fixed-length string is a character data type whose storage allocation is determined by its declared length and may include padding.
• variable-length dataA variable-length string is a character data type whose stored size can vary with the length of its value.
• data corruptionDatabase corruption is an invalid or inconsistent state in which stored database structures or data no longer conform to their required format or integrity constraints.
• mixed-mode authenticationMixed-mode authentication is a SQL Server authentication configuration that permits both Windows authentication and SQL Server authentication.
• LinuxAn open-source Unix-like operating-system family that can host SQL Server.
• query processing feedbackA query feedback feature is an adaptive SQL Server optimization capability that uses execution history to adjust query-processing decisions.
• control server permissionThe CONTROL SERVER permission is a SQL Server permission that grants control over the SQL Server instance.
• DDL triggerA DDL trigger is a SQL Server trigger that executes in response to database-definition or instance-level configuration events.
• execute permissionAn execute permission grants the ability to invoke a specified SQL Server routine or procedure.
• impersonate permissionAn impersonate permission allows a principal to execute under the security context of another database or server security principal.
• xp_cmdshell proxyAn xp_cmdshell proxy is a SQL Server configuration that supplies a secondary security context for executing xp_cmdshell on behalf of non-sysadmin users.
• custom imageA custom cloud image is an organization-specific machine-image template prepared with selected operating-system and application configuration.
• snapshotA database snapshot is a read-only, point-in-time view of a SQL Server database that preserves pages as they existed when the snapshot was created.
• SQLSQL is a declarative language for querying and managing relational data.
• trace flagA SQL Server trace flag is a configuration switch that changes specified database-engine behavior, diagnostics, or execution characteristics.
• DOP feedbackDOP feedback is an adaptive SQL Server query-processing feature that evaluates execution history and adjusts a query's degree of parallelism.
• SQL Server Agent operatorA SQL Server Agent operator is a configured notification recipient for SQL Server Agent alerts and jobs.
• WITH LOG optionThe WITH LOG option causes a message raised by RAISERROR to be written to the SQL Server error log and Windows application log.
• for json clauseFOR JSON PATH is a T-SQL JSON serialization mode that provides explicit control over the hierarchy and names of generated properties.
• json path expressionA JSON path is an expression that identifies a location within a JSON document, including object properties and array elements.
• json_valueJSON_VALUE is a SQL Server function that extracts a scalar value from a JSON document at a specified path.
• openjsonOPENJSON is a SQL Server table-valued function that parses JSON text and exposes its elements as relational rows and columns.
• semistructured formatA semistructured format represents data with flexible, irregular structure in which records need not share the same fields.
• Docker volumeA Docker volume is a Docker-managed persistent storage resource whose data resides outside a container's writable layer.
• point-in-time recoveryPoint-in-time recovery is restoration of a database to a selected moment within a sequence of backed-up transaction-log records.
• virtual machineA virtual machine is a software-defined computer that runs an operating system and applications in an isolated virtualized environment.
• log truncationLog truncation is the process of marking obsolete transaction-log portions for reuse after they are no longer required for recovery or other database operations.
• memory-optimized tableA memory-optimized table is a SQL Server table whose data and access structures are designed to reside and operate primarily in memory.
• dynamic sqlDynamic SQL is SQL text constructed at runtime and then submitted for execution rather than being fixed in advance.
• validationInput validation is the process of checking supplied data against allowed formats, values, or constraints before processing it.
• scheduling engineA scheduling engine is software that determines when configured jobs should run and coordinates their execution.
• log blockA log block is a container for transaction log records and the unit in which those records are flushed to disk.
• char(n)CHAR is a SQL Server fixed-length, non-Unicode character data type that stores one byte per character and pads values to the declared length.
• column offset arrayA column offset array is row metadata containing offsets that identify where variable-length column values begin.
• ncharNCHAR is a SQL Server fixed-length Unicode character data type that stores characters using two bytes per character.
• varchar data typeVARCHAR is a SQL Server variable-length, non-Unicode character data type that stores values using an 8-bit code page unless a UTF-8 collation is applied.
• breakpointA breakpoint is a marked execution location at which a debugger pauses program execution.
• debuggerA debugging tool is software functionality that helps inspect execution state and diagnose processing problems.
• Error List windowThe Error List window is a Visual Studio panel that reports source-code, build, and IntelliSense errors.
• Execute With DebuggerExecute With Debugger is a query-execution command that starts T-SQL execution under interactive debugging control.
• Immediate windowThe Immediate window is a debugger console for evaluating expressions or queries in the current execution context.
• Locals windowThe Locals window is a debugger panel that displays the current values of local variables and parameters.
• Step IntoStep Into is a debugger command that advances execution into the next statement or called routine.
• filegroupA filegroup is a named SQL Server collection of database files that provides a unit for organizing and allocating database objects.
• partition functionA partition function defines the boundary values and range direction used to divide data into partitions.
• partition schemeA partition scheme maps table partitions to database filegroups.
• SQL Server auditSQL Server Audit is a SQL Server feature that records specified database and server events for review and compliance.
• availability ninesThe number of nines is an availability notation that expresses uptime as a percentage with a corresponding count of nines.
• descriptive languageA descriptive language specifies desired results without prescribing the exact operations used to produce them.
• completion constraintA completion constraint is an SSIS precedence constraint that permits a downstream task to run after the preceding task executes regardless of its outcome.
• data pathA data path is an SSIS data-flow connection that routes rows from one data-flow component to another according to row-processing results.
• failure constraintA failure constraint is an SSIS precedence constraint that permits a downstream task to run only after the preceding task fails.
• precedence constraintA precedence constraint is an SSIS control-flow connection that determines when a downstream task runs relative to a preceding task.
• sys.dm_db_index_usage_statssys.dm_db_index_usage_stats is a SQL Server dynamic management view that reports usage counts and last-use times for indexes used since the Database Engine last cycled.
• asynchronous replicationAsynchronous replication propagates data changes to a replica without requiring the primary operation to wait for replica confirmation.
• synchronous replicationSynchronous replication propagates each data change to a secondary system and waits for its completion before completing the change on the primary.
• SQL Server Developer editionSQL Server Developer edition is a feature-complete SQL Server edition licensed for development and testing rather than production use.
• page compressionPage compression reduces SQL Server data-page storage by applying row compression together with prefix and dictionary compression.
• row compressionRow compression is SQL Server storage compression that reduces the space used by individual row values and removes unnecessary fixed-length padding.
• sp_estimate_data_compression_savingssp_estimate_data_compression_savings is a SQL Server system stored procedure that estimates storage savings from applying data compression to a table or index.
• autogrowthAutogrowth is SQL Server's automatic expansion of a database file when the file requires additional space.
• entityAn entity is a distinct type of thing represented in a data model and typically implemented as a table or related set of data.
• Windows Server Core imageWindows Server Core is a minimal Windows Server installation option without the standard graphical user interface.
• manual failoverManual failover is the deliberate transfer of database service to a secondary system by an operator or administrator.
• instant file initializationInstance file initialization is a SQL Server storage operation that initializes database files when they are created or expanded.
• database master keyA database master key is a SQL Server database-level cryptographic key that protects other cryptographic objects.
• certificateA certificate is a cryptographic object containing a public key and identity information that can support encryption or authentication.
• set-based processingSet-based processing operates on a collection of rows as a whole through relational operations rather than iterating over individual rows.
• optimistic isolation levelAn optimistic isolation level uses row versions or similar mechanisms to reduce reader-writer locking rather than enforcing extensive blocking locks.
• select starSELECT * is SQL wildcard projection syntax that returns all columns from the selected table or result source.
• repositoryA repository is a version-controlled storage area containing a project's files and their revision history.
• database pageA data page is a fixed-size SQL Server storage unit containing rows or portions of rows from database objects.
• log shippingLog shipping is a SQL Server disaster-recovery technology that automatically backs up transaction logs, copies them to another server, and restores them to a secondary database.
• database administratorA database administrator is a professional responsible for operating, maintaining, securing, and managing database systems.
• clustered columnstore indexA clustered columnstore index is a columnstore index that organizes the table's data itself in columnar storage.
• availability zoneAn availability zone is an isolated physical location within a cloud region that provides independent infrastructure for improving workload resilience.
• cost of downtimeCost of downtime is the financial impact incurred while an application is unavailable, including lost revenue and other consequences.
• five ninesFive-nines availability is a service availability target of 99.999 percent uptime over a defined measurement period.
• HA/DR architectureAn HA/DR architecture is a system design combining high-availability and disaster-recovery capabilities to maintain service and recover from failures.
• redundant infrastructureRedundant infrastructure is duplicated computing, storage, or network infrastructure arranged to continue service when a component fails.
• OPENXMLOPENXML is a SQL Server table-valued function that exposes rows and columns from an XML document or parsed XML representation.
• sp_xml_preparedocumentsp_xml_preparedocument is a SQL Server system stored procedure that parses XML text and creates an in-memory document representation identified by a handle.
• XML parserAn XML parser is software that reads XML text and constructs a structured representation of its elements, attributes, and content.
• XPathXPath is a W3C language for navigating and selecting nodes in an XML document.
• auditAuditing is the systematic recording and review of security-relevant actions and events for accountability and investigation.
• Database Engine Tuning AdvisorDatabase Engine Tuning Advisor is a SQL Server graphical tool that analyzes workloads and recommends changes to indexes, indexed views, partitioning, and other physical database structures.
• data load optimizationData load optimization is the tuning of data-ingestion settings and operations to reduce load duration and resource overhead.
• table lockA table lock is a database lock held at table scope that restricts concurrent access to the locked table.
• nonrepudiationNonrepudiation is the property that provides evidence linking an action to its originator so the originator cannot credibly deny performing it.
• relational engineThe SQL Server Database Engine component that processes relational queries and applies relational query-processing logic.
• storage engineA storage engine is the database subsystem that reads and writes persistent data and transaction information.
• NOLOCK query hintThe NOLOCK query hint is a SQL Server table hint that permits reads without acquiring shared locks, allowing potentially uncommitted data to be read.
• auto-grow settingAn autogrowth setting specifies how much a database file grows when it runs out of allocated space and how growth is constrained.
• buffer poolA buffer pool is the region of SQL Server memory used to cache data and index pages for database operations.
• max server memoryMax Server Memory is a SQL Server configuration setting that limits the amount of memory available to the SQL Server buffer pool.
• keyA database key is a column or combination of columns used to identify rows or establish relationships between tables.
• one-to-many relationshipA one-to-many relationship is an association in which one record in an entity can be associated with multiple records in another entity.
• RESTORE VERIFYONLYRESTORE VERIFYONLY is a SQL Server command that checks whether a backup can be read and is suitable for restoration without restoring its data.
• index reorganizationIndex reorganization is an online index-maintenance operation that compacts and orders existing index pages without creating a replacement index structure.
• dictionary compressionDictionary compression stores repeated value representations once in a page dictionary and replaces row occurrences with pointers to those values.
• prefix compressionPrefix compression stores a shared prefix once and represents other values in the same column by their differences from that prefix.
• log sequence numberAn LSN, or log sequence number, is an identifier assigned to a transaction log record to represent its position or identity in the transaction log.
• lock pages in memoryLock Pages In Memory is a memory-management configuration that prevents SQL Server memory pages from being trimmed from the working set.
• bulk insertionA bulk insert operation loads a large volume of rows into a database in an optimized manner.
• bit data typeBIT is a SQL Server integer data type that stores 0, 1, or NULL and represents Boolean values.
• smallintSMALLINT is a SQL Server integer data type that stores signed whole-number values using two bytes.
• TINYINTTINYINT is a SQL Server integer data type that stores nonnegative whole-number values using one byte.
• object prefixAn object prefix is a naming convention that adds a type-indicating string to the beginning of a database object name.
• group managed service accountA group managed service account is a Windows-managed service identity that provides automatic password management and restricted noninteractive use.
• service accountA service account is a noninteractive identity used by a software service to access operating-system or network resources.
• repeatable read isolation levelRepeatable Read is a transaction isolation level that prevents dirty and nonrepeatable reads but can allow phantom reads.
• serializable isolation levelSerializable is a transaction isolation level that provides the strongest standard protection against concurrent-read consistency anomalies, including phantom reads.
• server groupA server group is a collection of registered SQL Server instances managed as a unit for connection and query operations.
• SQL Server Central Management ServerCentral Management Server is a SQL Server feature for organizing registered instances and executing queries across groups of those instances.
• COPY_ONLYCOPY_ONLY is a SQL Server backup option that creates a backup without changing the differential base or affecting the normal backup sequence.
• cardinality estimation feedbackCardinality estimation feedback is an adaptive SQL Server feature that identifies inaccurate row-count estimates and improves subsequent plan choices.
• memory grant feedbackMemory grant feedback is an adaptive SQL Server feature that adjusts the memory allocated to a query based on execution history.
• basic availability groupA Basic Availability Group is a limited SQL Server availability-group feature supporting one replica that remains inactive until failover.
• enterprise-only featureAn enterprise-only feature is a SQL Server capability restricted to Enterprise edition.
• sys.dm_db_persisted_sku_featuressys.dm_db_persisted_sku_features is a SQL Server dynamic management view that reports edition-specific features persisted in a database.
• native XMLNative XML storage is the persistence of XML content in an XML representation rather than decomposing it into relational structures.
• block cacheA buffer cache is memory containing database pages brought in from persistent storage for query access.
• query executorThe query executor is the SQL Server component that runs an established query execution plan.
• AUTOGROW_ALL_FILESAUTOGROW_ALL_FILES is a SQL Server filegroup option that causes all files in the filegroup to grow when one file reaches its growth threshold.
• Read Committed SnapshotRead Committed Snapshot is a SQL Server isolation mode that implements read-committed semantics by reading row versions instead of acquiring shared locks.
• ad hoc backupAn ad hoc backup is a database backup taken outside the regularly scheduled backup plan for an immediate restore point or data refresh.
• FOR XMLFOR XML is SQL Server query syntax that serializes relational query results as XML.
• Execute T-SQL taskAn Execute T-SQL task is an SSIS control-flow task that executes one or more T-SQL statements against a configured connection.
• sorting algorithmA sort operation arranges rows according to specified ordering keys during query execution or index maintenance.
• authenticator columnAn authenticator column is a column whose value is cryptographically bound to encrypted data and checked during decryption to detect moving that ciphertext to another record.
• immutable storageImmutable storage is storage configured so that retained data cannot be modified or deleted during its protection period.
• custom roleA custom role is a user-defined SQL Server role with specifically selected permissions for a required task.
• db_backupoperatordb_backupoperator is a SQL Server fixed database role that grants permissions to back up a database.
• fixed server roleA fixed server role is a predefined SQL Server server-level role with a defined set of permissions.
• ##ms_databasemanager####MS_DatabaseManager## is a SQL Server 2022 fixed server role that grants permissions for creating and deleting databases.
• SQL Server permissionsSQL Server permissions are authorization grants that control which operations a principal can perform on SQL Server securable objects.
• schema compareSchema comparison is the process of identifying differences between database schema definitions or project schemas.
• database audit specificationA database audit specification is a SQL Server database-level object that defines actions to record within a database through an audit.
• server audit specificationA server audit specification is a SQL Server object that defines instance-level actions to record through an audit.
• Windows Security logThe Windows security log is an operating-system event log that records security-related events and is protected by Windows access controls.
• ROW_NUMBER functionROW_NUMBER() is a window function that assigns a sequential integer to rows within an ordered window partition, optionally restarting for each partition.
• WindowsWindows is a family of Microsoft operating systems that can host SQL Server.
• read uncommittedRead Uncommitted is a transaction isolation level that permits reads of data without acquiring shared locks, including data changes that have not been committed.
• build serviceA build service is an automated service that compiles or packages software and executes associated validation steps.
• dacpacA dacpac file is a SQL Server database deployment package containing XML definitions of database objects.
• deployment serviceA deployment service is an automated service that delivers packaged software to target environments in a release sequence.
• pivotingThe PIVOT operator transforms grouped row values into columns while applying an aggregation to associated values.
• gitGit is an open-source distributed version control system for tracking changes to files and coordinating software development.
• GitHubGitHub is a hosted software-development platform for storing Git repositories and collaborating on code changes.
• pull requestA pull request is a proposal to review changes from one repository branch and merge them into another branch.
• cloud provider sql server imageA cloud provider SQL Server image is a provider-maintained virtual machine image with SQL Server preinstalled and licensing integration enabled.
• license-included modelA license-included licensing model bundles the software license cost into the recurring price of the provisioned cloud resource.
• multiple log file configurationA multiple log file configuration is a database configuration containing more than one file for storing transaction-log records.
• RAID 1RAID-1 is a mirrored storage layout that maintains identical copies of data on separate disks.
• optimize_for_sequential_keyOPTIMIZE_FOR_SEQUENTIAL_KEY is a SQL Server index option that applies flow control to reduce contention from concurrent inserts into the last page of a sequential-key index.
• lock contentionLock contention is competition among concurrent database operations for incompatible locks on the same resources.
• backup integrityBackup integrity is the completeness, accessibility, and internal validity of a backup needed for reliable restoration.
• IDEAn integrated development environment is a software application that combines source-code editing, development tools, and often debugging or build capabilities.
• Windows Server with Desktop ExperienceWindows Server with Desktop Experience is a Windows Server installation option that includes the standard graphical user interface and related desktop components.
• SQL Server Data ToolsSQL Server Data Tools is Microsoft's Visual Studio tooling for developing and managing SQL Server data solutions.
• hash joinA hash join builds or probes a hash table to match rows from two relational inputs.
• join hintA join hint directs SQL Server toward a specified physical join algorithm for a logical join.
• merge joinA merge join combines two inputs by scanning their rows in matching key order.
• nested loop joinA nested loops join processes one input row at a time and searches the other input for matching rows.
• physical join operatorA physical join operator is an execution algorithm that implements a logical join between relational inputs.
• query hintA query hint is a directive attached to a SQL Server query that influences or overrides query-optimizer decisions.

These guides are generated from the text of Manning books by Manning's knowledge graph and link to the sections they draw on. The full books are available on liveBook and at manning.com.