SQL Server Performance Blog where milliseconds matter

Hi guys, Today I wanted to answer mr. Brian Walker who wrote me an email and pointed out a series of important notes about the concatenation of the text we talked about yesterday I thank him because what I like most is the exchange of ideas! Brian sent me a script where we see that the syntax we talked about yesterday has some limitations. Let's see them. First of all he created a table #name and filled it with some data CREATE TABLE #Names (Name varchar(20)) INSERT #Names VALUES ('X1') , ('X2') , ('X3') , ('Y1') , ('Y2') , ('Y3') , ('Z1') , ('Z2') , ('Z3') These are our data... He showed me that putting in the "order by" clause the field name it's ok! DECLARE @Names varchar(1000) SELECT @Names = ISNULL(@Names, '') + ' ' + N.Name FROM #Names AS N ORDER BY N.Name SELECT @Names SET @Names...

Hi  guys,  Today we take a little break, just a little post for a useful super tip!   Today a colleague of mine had developed a function that used a CTE to do what today I will show you can be done with a single line of SQL code. Needless to say, everything was extremely slow.   Enjoy the reading of this supertip! Concatenates text from multiple rows into a single string Suppose you have an order or an invoice with a variable number of rows. Think of the Ordtes and OrdRig tables that we have used in so many examples in this blog. We extract the data of an order with this command: SELECT id, idordtes, Descr FROM ordrig WHERE IdOrdtes = 33 ...the data:       Suppose we need to have all descriptions on the same row. How to do it without cursor ore CTE? Simple!  DECLARE @Names VARCHAR(8000) SELECT @Names = ISNULL(@Names,'') + ' ' + Descr FROM ordrig WHERE IDordtes = 33 SELECT @Names   Trying this you will get: et vo...

Hi guys, Here we are together again to continue the discussion started in the previous article you find here. Previously I told you the history of Table Variables and we have also seen their limits. Today is the time to tell you the news that accompany the release of the 2019 version of SQL Server , starting from the Table Variables . Are you ready? Get comfortable and let's go! Table Variables deferred compilation Let's immediately give a name to the novelty introduced by SQL Server 2019 regarding Table Variables: the Table Variables deferred compilation As the name explains, the compilation of the Table Variables becomes Deferred. Deferred in the sense that it happens not at the moment in which the table is created but is postponed (deferred) at the moment in which the table is used in our Query .  More in detail, the cardinality estimate occurs at the first use, after which no other recompilation will take place . In fact, it is therefore a compromise between recompiling ...

Hi Guys, After the success of the previous post where we compared temp table and table variable, today I wanted to go back to talking to you again about table variables. This time i will tell you about their history and about their evolution over time . We will also talk about the trace flag 2453 which will allow us to have higher performance in some cases. But now let's go!  Table Variables: The history of... Tables of the table variable type date back to the days of SQL Server 2000. Created to handle temporary data faster than Temp Tables, it was chosen to limit recompilations and not provide them with statistics . In fact, the idea turned out to be successful ... halfway! In fact, if you store only a few lines in a Variable Table, you will have excellent performance. But if you need to remember happier that performance will drop. They will drop precisely because of the lack of statistics. So over time the Table Variables got a bad name! Without statistics for SQL Server a Tab...

Hi Guys,   Welcome back! Some time ago I was asked what the differences were between the temporary tables and in-memory table variables . I wrote this post with the intent to clarify! ...I hope I have succeeded. Today I wanted to present a comparison between temporary tables (#) and variables of type table (@) . We will analyze in detail the differences between these two types of tables to understand when it is better to use one type rather than another . But not only that: You will have the opportunity to see how to read the transaction log and use the RML utilities to generate the parallel workload necessary for our analysis. We use the term "temporary tables" because it often happens in our calculations that we need an "intermediate" table on which to place data. Due to the fact that our tables are "temporary" after use we will not need to delete them: this is certainly a great convenience. "Temp Tables" (#) Vs. "Table Variables...

 Hi Guys, Welcome back to this blog. This will be a light post just to review some important concepts that i have already discussed in the past. We talked about the Sargable concept here: Write high performing query: Is your query SARGABLE? Sargable Queries part 2. Examples   In shoert, your query is sargable when it can use index by doing a SEEK (and not a SCAN) operation. This way you can read from a table only the specific rows  you need. This approach is also the only  one that leads to scalable performance. We have already seen that functions are not Sargable and we have seen some examples and therefore our ISNULL function is also not! So how can we proceed? so how can we write the same logic in a sargable way? How to replace the ISNULL function using Sargable logic Suppose you are reading data from a table.  Table in our example is JDT1 , a standard Journal Entry Table from SAP ONE. You need to extract rows where the column SourceId is equal to 420. Now if...