Comprehensive SQL Server Glossary

Comprehensive SQL Server Glossary for Senior DBAs

1. Always On Availability Groups: A high-availability and disaster recovery solution that provides a set of databases that fail over together.
2. Audit: A feature that tracks and logs events that occur on the SQL Server for security and compliance purposes.
3. Backup: A copy of database data for recovery in case of data loss, corruption, or hardware failure.
4. Blocking: A situation where one connection to the database holds a lock and prevents another connection from accessing the same resource.
5. Checkpoint: A process that writes all dirty pages (modified pages) from the buffer cache to disk to reduce recovery time.
6. Clustered Index: An index that determines the physical order of data in a table, providing faster data retrieval.
7. Collation: A set of rules that determine how data is sorted and compared, important for handling linguistic and cultural differences.
8. Common Table Expression (CTE): A temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.
9. Cross Apply: A join operation that allows you to join a table with the output of a table-valued function.
10. Data Compression: Reducing the storage size of data to save space and improve I/O efficiency.
11. Data Masking: A method to hide sensitive data in non-production environments to protect it from unauthorized access.
12. Database Snapshot: A read-only, static view of a database, useful for reporting and data backup purposes.
13. Deadlock: A situation where two or more transactions block each other by holding locks on resources the other transactions need.
14. Dynamic Management Views (DMVs): Views that provide insight into the health, performance, and state of the SQL Server environment.
15. Execution Plan: A roadmap created by SQL Server for executing a query, showing the sequence of operations.
16. Extended Events: A lightweight performance monitoring system that captures and stores data about various events for troubleshooting.
17. Extended Stored Procedure: A function written in a programming language such as C that is callable from SQL Server.
18. Failover Cluster: A high-availability feature that automatically shifts workloads to standby servers in case of hardware or software failures.
19. Fill Factor: A setting that determines the amount of free space left in an index page during index creation or rebuild.
20. Full-Text Search: A search functionality that allows users to run full-text queries against character-based data in SQL Server tables.
21. Index: A database object that improves the speed of data retrieval by providing quick access paths.
22. Isolation Level: Defines the degree to which the operations in one transaction are isolated from those in other transactions.
23. Linked Server: A configuration that enables SQL Server to execute commands against OLE DB data sources on different servers.
24. Log Shipping: A process of automating the backup of transaction logs from a primary server to a secondary server.
25. Mirroring: A high-availability solution that maintains a copy of a database on another server to ensure continuity.
26. Multi-Statement Table-Valued Function: A user-defined function that returns a table and can contain multiple statements.
27. Normalization: The process of organizing database tables to reduce redundancy and improve data integrity.
28. Page Split: A process that occurs when there is not enough room on a page for a new row, causing SQL Server to split the page into two.
29. Partitioning: Dividing a database object into smaller, more manageable pieces to improve performance and manageability.
30. Plan Cache: A memory area where SQL Server stores execution plans to improve query performance by reusing plans.
31. Policy-Based Management: A feature that allows you to create and enforce policies for managing SQL Server instances.
32. PolyBase: A feature that allows you to query data from external sources like Hadoop or Azure Blob Storage using T-SQL.
33. Query Optimization: The process of enhancing query performance through various techniques like indexing, statistics, and query rewriting.
34. Rebuild Index: An operation that drops and re-creates an index, removing fragmentation and reordering pages.
35. Recursive Query: A query that references itself to produce a result set, often used for hierarchical or tree-structured data.
36. Reorganize Index: An operation that defragments and compacts the pages of an index without dropping and re-creating it.
37. Replication: The process of copying and distributing data and database objects from one database to another and synchronizing between databases for consistency.
38. Row-Level Security (RLS): A feature that enables you to control access to rows in a database table based on the characteristics of the user executing a query.
39. Row Versioning: A technique used by SQL Server to manage concurrency by keeping versions of rows to avoid blocking.
40. SARGable: A term indicating that a query can take advantage of indexes due to the way it is written, improving performance.
41. Scalar Function: A user-defined function that returns a single value of a specified data type.
42. Service Broker: A feature for building scalable, secure, and asynchronous messaging and queuing applications.
43. SQL Injection: A code injection technique that might destroy your database by inserting or "injecting" SQL code via user input.
44. Stored Procedure: A precompiled collection of SQL statements and optional control-of-flow statements stored under a name and processed as a unit.
45. TempDB: A system database used for temporary storage of intermediate results, temporary tables, and other temporary storage needs.
46. Transaction Log: A record of all transactions and database modifications to ensure ACID compliance.
47. Transparent Data Encryption (TDE): Encrypts data files to prevent unauthorized access to data at rest.
48. User-Defined Data Type: A data type that is based on an existing SQL Server data type, created to ensure consistency and reuse of data definitions.

#SQLServer #DatabaseAdministration #DBA #SQLServerTips #DataManagement #SQLPerformance #DatabaseSecurity #ITProfessionals #TechInsights #SeniorDBA #SQLServerOptimization #DatabaseExperts #SQLServerCommunity #TechLeadership #DataProtection