Welcome to our blog post on SQL Server concepts! In this article, we will explore some important terms and ideas related to SQL Server that every developer and database administrator should be familiar with.
Policy Management Terms
Before diving into the details of SQL Server concepts, let’s first understand some key terms related to Policy-based management:
- Target: A type of entity that is managed by Policy-based management, such as a table, database, or index.
- Facet: A property that can be managed in policy-based management, such as the name of a trigger or the auto shrink property of a database.
- Conditions: Criteria that specify the state of a facet as true or false.
- Policy: A set of rules specified for server objects or database properties.
The ‘FILLFACTOR’ Argument
The ‘FILLFACTOR’ argument is an important aspect of index creation in SQL Server. It specifies the percentage of space on each index page to be filled with data, leaving free space for future table growth. A fill-factor value of 70, for example, means that 30% of each page will be left empty.
Interestingly, in SQL Server, the fill-factor settings of 0 and 100 are considered equal. This means that if you set the fill-factor to either 0 or 100, the Database Engine will fill pages to their capacity while creating indexes.
It’s important to note that creating a clustered index with a fill-factor less than 100 may increase the amount of space the data occupies, as the Database Engine reallocates the data while building the clustered index.
The ROLLUP Clause
The ROLLUP clause is a powerful tool in SQL Server that allows for aggregate operations on multiple levels in a hierarchy. By adding the WITH ROLLUP clause in the GROUP BY clause, you can easily perform sums on different levels without adding any new columns.
Limitations of Views
Views in SQL Server have some limitations that you should be aware of:
- The ORDER BY clause does not work in views.
- Adding or removing columns from a table does not automatically reflect in the view until it is refreshed.
- Indexed views have certain restrictions, such as not allowing UNION operations, nested views, self joins, outer joins, and cross-database queries.
- Views do not support the COUNT(*) function, but they can support COUNT_BIG(*).
Covered Index
A covered index is an index that can satisfy a query just by its index keys, without needing to access the data pages. This means that SQL Server can produce the results directly from the index, as it covers all the columns used in the query. This can greatly improve query performance.
Managing Table Size
When data is deleted from a table in SQL Server, the size of the table is not immediately reduced. Instead, the pages are marked as free and new data is inserted into those pages first. Over time, a background process de-allocates the pages, finally reducing the table size.
Understanding Wait Types
In SQL Server, there are three types of wait types:
- Resource Waits: Occur when a worker requests access to a resource that is currently used by another worker or not yet available.
- Queue Waits: Occur when a worker is idle, waiting for work to be assigned.
- External Waits: Occur when an SQL Server worker is waiting for an external event.
Managing Log File Size
If your transaction log file in SQL Server is growing too big, you can manage its size using the following options:
- Convert the recovery model to Simple Recovery to prevent extraordinary growth of the log file.
- Start taking regular transaction log backups in Full Recovery Model to control the log file growth.
Encrypted Stored Procedures
If a stored procedure is encrypted in SQL Server, its definition cannot be seen in the Activity Monitor.
We hope this article has provided you with a better understanding of some important SQL Server concepts. Stay tuned for more informative blog posts on SQL Server!