Continuing on last week’s post on security principals, this week’s installment in the series on SQL Server security takes a look at securables.
Category: SQL Server concepts
The SQL Server security model, part 1: principals
There are a number of layers in the SQL Server security model, giving you a nearly infinite number of ways to set up access control on your server and databases. Security is a huge topic, and there are literally entire books on it, so this series of articles is designed to give you just a quick overview of the SQL Server security model to get you started.
In this first installment, I’ll go through the different types of security principals that are available, as well as how they connect to each other.
Schemabinding and table spools
There are obvious advantages as well as challenges to schemabinding things, and whether you do or don’t is often a matter of preference. However, in some cases, you need schemabinding; when you’re designing indexed views, and to optimize user-defined functions. Here’s why:
Blocking/non-blocking aggregate operators
Some database operations can be performed in distinctly different manners, with different impacts on query performance. One important example of such an operation is calculating an aggregate. In this article, we’ll take a look at how aggregates can be “blocking” or “non-blocking”, how it affects memory allocation, and ultimately, what impact this has on your query.
Datawarehouse modelling: Inmon vs Kimball
If you’re into business intelligence, data warehousing and analytics, you will have heard an endless number of references to Bill Inmon and Ralph Kimball. These two figureheads in datawarehousing architecture have produced an immense number of books, articles, training seminars, etc. While many of their strategies and modelling approaches are similar, they have near-opposite views on other aspects.
HASH JOIN deep-dive
Among the three different types of join operators used by SQL Server, the HASH JOIN does some of the hardest work. It scales pretty well and is very suitable for parallel processing. As such, it can be very powerful in many applications, but hash joins can potentially consume quite a bit of memory, so seeing on in your query plan could be an indicator of a performance tuning issue in your query or data.
An introduction to filtered indexes
Filtered indexes are an excellent performance tuning feature found on SQL Server 2008 and later. Basically, by adding a WHERE clause to a CREATE INDEX statement, you can make the index smaller, more specific and faster.
Minimally logged operations
Today, we’re going to look at which database operations can be performed with what’s known as “minimal logging”, which can result in a considerable performance boost. Included is a quick overview of transaction logging and what it’s good for.
Introduction to partitioning
Partitioning is not only a great way to improve performance in large tables, but you can also use it to manage the physical storage of different chunks of data without keeping it in different tables.
Error handling using TRY-CATCH
As of SQL Server 2005, you can handle errors using a TRY-CATCH block, similar to “real” programming languages. This enables you to trap most common errors and handle them, instead of having your entire batch or procedure fail with an error message.