Take maximum advantage of Grouping Sets, Roll-up, Cube and Pivoting

This article explains, with detailed examples, how to use PIVOTING to take maximum advantage of queries making use of GROUPING SETS. Besides, ROLLUP and CUBE operators will be analysed as well. We'll see throughout the article that it's very easy to get simple, practical and powerful queries by integrating all of them in the same query.

I think that master these operators contributes to make cleaner code and save a lot of time when developing. Thus, I hope this article will be useful to both improve your knowledge and develop faster cool queries for your reports, forms or views.

[More]

Row Level Security in SQL Server

Row-Level Security let us control access to data at row level for any SQL Server table based on users, roles, membership or working context. It tremendously simplifies and improve the options we have to secure, filter, implement restrictions or eventually deal with our data over certain scenarios. What is most relevant for me is that all the security policy can be set centrally at a database level preventing developers from taking care of adding custom where clauses to enforce security. It makes the management of data much more reliable and maintainable.

[More]

Recursive SQL with Common Table Expressions

This post intends to set out how to make recursive SQL queries in a simple and straightforward manner using Common Table Expressions (CTE).

What are CTEs?

As it is said in Microsoft SQL Server documentation a CTE (Common Table Expression) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing (important for the purposes in this post!) and can be referenced multiple times in the same query.

[More]