SQL Server Performance & Troubleshooting – Where Milliseconds Matter 🚀

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, I love database theory because it includes mathematics, statistics, logic, programming and data organization .  I wrote this post because i read a post on linkedin about the well known  " 201 bucket limitation " in the statistics used in SQL Server. Bob Ward (the Principal Architect at Microsoft, in the photo on the right while dreaming statistics and rugby...lol ) rensponded to the author of the linkedin post asking him to give a real example where this was a limitation. He also said he would verify. I then asked myself whether the logic with which statistics are managed could not be improved in any way without increasing their size.   In the post on Linkedin was also referred to a post of the well-known mr. brent Ozar : The 201 Buckets Problem, Part 1: Why You Still Don’t Get Accurate Estimates Brent used the Stack Overflow 2010 database database for his examples and I will do the same in this post. If you want to follow my examples then, first of all, down...

Hi guys, Welcome to this new post! This time we will talk about temporal features and temporal table (attention please... not temporary tables). We never talked about this topic in this blog but today is the right day to do it! Why is this an important topic? Well, how many times you had to  track changes in data contained into a table? I am sure, so many times! Probably you solved this problem of tracking changes to your data (and so to know what happened to your data in the past) using triggers. A trigger is fired every time the value of a field in a table change. This trigger will add a row into a log table. It is necessary to know that this operation is natively supported by many database systems and also our Microsoft SQL Server has been supporting it for several years. Enjoy the reading!   SQL:2011 and the Temporal Features. A Bit of Theory. We start talking about temporal tables by saying that they are supported by different database engines because they have been d...

Hi Guys, Welcome to this new post! We have already spoken many times about sargability . For example here: Write high performing query: Is your query SARGABLE? SQL Server queries, write them Sargable: the ISNULL function   https://sqlserverperformace.blogspot.com/2019/11/sargable-queries-part-2-examples.html Sargability is the key factor to having a performant query but talking about dates is not always easy to get this property . Today we will see how to turn a non sargable query using dates into a sargable one . Obviously the difference in performance is really remarkable! Enjoy! Working with dates in a sargable way Sargability is a great property!     We can say that a query is sargable when its WHERE clause can use index in seek mode . I suggest you read my other posts where I tell you why often the queries are not sargable. For example when I use a function, but there are other cases.. And often when we handle dates we use functions. Who has never used the date...