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]
Exploring Azure Virtual Machine networking settings for ICMP, RDP and SQL Server [More]
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]
There are times you want to get comma (or another character) delimited values from a given field in a SQL table. There are several ways of achieving this goal but I am going to show you a simple manner I usually employ to obtain the desired format. [More]
In my previous article Simple way to get comma delimited values from a field by using SQL I showed how to get a comma delimited string from a field in a SQL table. But, there are other times you want to get the same but the other way around, that is, converting each element in a comma (or another character) delimited string variable into a row value in a table. How to get this, then? [More]
PIVOT operator is mainly used to convert a result set from a SQL query into another one by rotating unique values in a specified field column, throughout the original result set, into new column fields in the final result set [More]
In this article I'm going to show some useful SQL using "Recursive Queries" and "Windowing Functions". So, the article is meant to intermediate level SQL programmers or those who want to know a little more about this language. Detailed explanations about syntax can be obtained in Microsoft documentation (Windowing | Recursivity). It's not the goal of this article to explain all that again. So, have a look there to go deeper into it. [More]
Some days ago, I had to help others to solve a problem with SQL. This issue was related to convert fields in a result set into values in rows into the final desired result set. So, we are talking about making use of UNPIVOTING functionality. Finally, I thought, why not writing a little tip about? [More]
Here is a link to the meetup we held in the dotNetters community some months ago. IT was amazing for me to talk about such an interesting subjects :) [More]
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]
This SQL tip is about generating random int values between upper and lower limits.You can also use the attached content to create a simple template database to make your own demos or tests. [More]