SQL Server Performance Blog where milliseconds matter

Hi Guys!  Welcome back! Today we will talk about GROUPING SETS. As usual I will try to explain what we are talking about in the clearest possible way and we will always give examples . So what is a GROUPING SET? GROUPING SETS can be considered an extension of the GROUP BY clause in fact it gives you the possibility to combine multiple GROUP BY clauses . How many times do we have to show in video a series of data that maybe adding them up and regrouping them for more than one field? How many times have we had to use UNION clauses? Often many clauses union together make the code less readable and less maintainable. The solution is to use GROUPING SETS. Enjoy the reading!   GROUPING SETS  As said, a grouping set can be considered as an extension of the GROUP BY clause. Speaking in a technical way, SQL grouping sets are a way to group data in SQL queries by multiple dimensions. Now, in a typical SQL GROUP BY clause, data is grouped by one or mor...

Hi guys, After the deep previous post about the SQL statistic today we will talk about a more more easy topic. Have you ever needed to extract from a table the second row, or the third or the n-th row? This post is for you!    Extract ..Get..Select... the n-th row in a table We can solve this task in more than one way. To follow my example you can simply execute the following command that create and fill the Ord table with some data Create Table Ord (Id int identity (1,1) primary key , Code varchar (10)) Insert into Ord (Code) values ( 'A' ),( 'B' ),( 'C' ),( 'D' ),( 'E' ),( 'F' ),( 'G' ),( 'H' ),( 'I' ) This is our table:   The first way I seen many times is using a derivate table .   Yes, you can solte the request by split the problem in two parts. In the first part you can get the first n-th rows. This will be the derivate table.   Then you can get first row of this derivate table orde...

Hi guys, In the last four posts we talked about the T-SQL news that SQL Server 2022 brought us. SQL Server 2022 and the DATE_BUCKET function. Work more easily with dates  SQL Server 2022 and the GREATEST / LEAST T-SQL commands   SQL Server 2022 and the STRING_SPLIT command .. string splitting made easy!  SQL Server 2022 and the GENERATE-SERIES command .. a T-SQL language enhancement  Today we will talk about another T-SQL extension.  We will talk about the Window clause in the window functions . As always, I will try to be as clear as possible! Enjoy this post!   The Window Clause With SQL Server 2022, Microsoft introduce in the window functions the concept of window . For the sake of truth this is not a feature invented by Microsoft because this concept is part of the ISO/IEC SQL standard. If you doesn't know what a window function is, you can read here: Learn SQL Server Window functions and Running Totals in five minutes  Looking at the example bel...

Hi Guys, Welcome back to this little new midweek post. Today we will see two more simple but useful new T-SQL commmands added in SQL Server 2022.   I usually don't like commands that aren't ANSI standard as much, but now these commands are used by more than one database engine. For example, our new GREATEST and LEAST commands are supported by PostgreSQL, MySQL and Oracle. So if they are comfortable, they are welcome! Enjoy the reading! GREATEST / LEAST commands   These new two functions returns the maximum value and then mininum value from a list of one or more expressions .   As paramters they accept a list of comma-separated expressions of any comparable data type . Furthermore these functions requires at least one argument and supports no more than 254 arguments.    For example we can write: SELECT GREATEST   ( 'Brad Pitt' , N 'Angelina Jolie' , 'Tom Hanks' ) AS VALUE; or SELECT LEAST   ( 'Brad Pitt' , N 'Angelina Jo...

Hello friends! Welcome back to this short post. We will speak about Alias and only two minute to learn some important things is really a big deal ! So think big and think that Share knowledge is the only way! Alias I think that everyone know what an alias is. We can have a table and call it with another name or we can have a field of a table and call it with another another name. SELECT CODE FROM ORDTES SELECT CODE as CODICE FROM ORDTES as ORDINI  Where 'as' is optional   Using alias if your statement represents a derived table then you can refer to it through the alias: SELECT * FROM ORTDES JOIN (SELECT CODE AS CODICE FROM ORDTES) AS ORDINI ON ORDINI.CODICE = ORDTES.CODE   Tips: You can see the Alias also int the execution plan   And then.. Suppose now to play with our usual ORDTES table. Suppose again you need to write a statement to get the max number of document. Using a not efficient way (i know) we could write: SELECT id FR...

Hi Friends, March has arrived, spring is coming and i am writing this post after a sunny thuesday. Not bad at all! Just a good coffee and it is time for our basic post of today! We will talk about the COALESCE and IIF functions and the CASE statement.  Maybe someone has never used them or ...doesn't know the secret at the end of the post! Hey don't cheat, don't scroll down! please! COALESCE The function coalesce return the first not null parameter from the left For example: SELECT COALESCE (NULL,1,3) return 1 SELECT COALESCE (‘A’,’B’,’C’) return ‘A’   I like this function because it accepts a variable number of parameters as input. Few functions do this.. In all honesty I would like other functions that behave this way, for example the MAX and MIN... COALESCE, IIF or CASE? There are other ways to get the same result as the following query: Select coalesce (g.qttgiai,g.qttimpi,g.qttmani) from products g   I can use for examp...

Hello friends, Paraphrasing the title of the american television show " Say Yes to the dress ", our title of today is " Say no to the NOT IN operator ". Why?  Well, we will show you that the " Not in " operator can be substituited with the " Not Exists " operator and that normally the Not Exists operator is fastest ! How we write a Query ultimately determines how much it will be efficient. Yes, SQL Server is a declarative language ( we tell him what we want to achieve and he internally decides how to get it ) but if we help him by applying a series of good rules we will get improvements from the point of view of performance. But now, as usual, we will show you with an example Let's go! For the example we will use our tables ORDRIG and PRODUCTS. Each table have a clustered index on the ID integer field: CREATE CLUSTERED INDEX IDX_ORDRIG_ID ON ORDRIG(ID) CREATE CLUSTERED INDEX IDX_PRODUCTS_ID ON PRODUCTS(ID) Well...