SQL Server Performance Blog where milliseconds matter

Hi guys, It's time for news,  the new CTP 2.1 of SQL Server 2022 has just been released. Let's see together what are the innovations introduced. Enjoy!     SQL Server 2022 CTP 2.1   I love talking about T-SQL commands and so I'm happy to show you the new T-SQL commands just added with this latest CTP (community technical preview)  Bit Manipulation functions Bit manipulation functions are introduced perhaps to match other database engines such as mysql. They could be useful in certain areas   Now we have a LEFT_SHIFT function.   Through this function we can shift the bits of an integer to the left. There are 2 parameters. the input decimal value and the number of bit to shift.   For example: select LEFT_SHIFT (256,1) Heres the result: Rememeber that shifting one bit to the left is equal to multiply the initial value by 2 . The RIGHT_SHIFT function instead shift bits to the right.  Shifting to the right is equivalent to divide by 2. ...

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 all for another short but useful post! This time I will tell you about another T-SQL TVF function introduced by the newest SQL Server 2022 ! This function will permit us to easily cope with the needs that arise when we have to work with dates. DATE_BUCKET The DATE_BUCKET function collapses a date/time to a fixed interval and let me say that this is a great idea! Now there is no more need to round datetime values, then extract date parts and perform conversions to and from other types like float. Example We can write: declare @dt datetime2 ; set @dt = '2022-03-21 19:45:01.123' Select date_bucket (MONTH, 1, @dt) Executing the command you will get:  All the dates will be rounded to a one month precision. Prior to SQL Server 2022 we should have written: declare @dt datetime2 ; set @dt = '2022-03-21 19:45:01.123' Select datefromparts ( YEAR (@dt), MONTH (@dt), 1) That's all for today! p.s. ... are you genuinely ...

Hi Guys and welcome back to this new post! Do you want to learn another useful T-SQL command with me? Well, today we will show how to split strings easily , so.....no more recursive left o copy statements usage!   The STRING_SPLIT function The String_Split function is a table-valued function that splits a string into rows of substrings. Obviously, you can choose the separator character to use . Well first I have to tell you that this function is part of SQL Server since version 13.00 (SQL 2016).but we are talking about it today because it has been significantly improved.   Using SQL Server 2022 we have a new parameter enable_ordinal:     If this parameter is set to 1 this function will return also a new column ordinal. For example i can write the following command:   SELECT value, ordinal FROM STRING_SPLIT ( 'My name is luke' , ' ' , 1) When i execute it i will get:       I get four rows,  a row for each word (the separat...

Hi Guys, Welcome back! I have to admit that I like to find out what is added in each new version of SQL, especially the T-SQL language part. There will be a lot to talk about .. So let's start! Today we see the first improvement of T-SQL language that the new SQL Server 2022 bring us: the GENERATE_SERIES command The GENERATE_SERIES command I bet that when we are asked to generate a series of numbers such as 1,2,3, .. or 2, 4,6 etc etc immediately we think of the CTE (the well-known "common table expression") or the function of ranking ROW_NUMBER. Someone then invents some more original but still functional approach. Let's see some ways currently used to generate a series of values: Using a CTE: WITH Cte AS ( SELECT 1 AS x UNION ALL SELECT x + 1 FROM Cte WHERE x < 5 ) SELECT * FROM Cte -- remember to add the option maxrecursion... Executing this command we will have:   Or we could use a ranking function: SELECT ROW_NUMBER () OVER (Ordery...

Hi guys, Today we will start talking about the features that the new SQL Server 2022 bring us. We have already talked about it here. In particular, today I want to talk to you about the new feature called   Parameter Sensitive Plan Optimization or PSP . This feature is part of the family of features known as Intelligent Query Processing and aims to improve the performance of existing workloads. I want to remark that this is a big performance improvement and one of the main highlights of this new version of SQL Server .   This improvement comes at no cost and without any changes to the application code. Parameter Sensitive Plan (PSP) Optimization   Starting from the idea that creating an execution plan (determine which is the best way to solve a Query) require resources and time. Microsoft has developed into SQL Server 2022 a mechanism to save and reuse created execution plans. Briefly, the first time a query is executed its execution plan is created and stored in th...

Hi Guys, SQL Server 2022 is coming! … and as always happens, each new version is always eagerly awaited ! Suffice it to say that since its announcement at Ignite in November 2021 , the early adoption program (EAP) has received over 2,500 requests to join the private preview. A big number!   During the SQLBits 2022 event that took place in March 2022 Bob Ward announced that the SQL Server Public Preview is coming by the end of the first half of the calendar year 2002 .   Application to join the EAP are now closed. Demos were made during the event using the CTP 1.3 version. While the version used in the EAP was CTP 1.0. I have already talked about the new features introduced by SQL Server 2022 here: Ladies and gentlemen ...drumroll... SQL Server 2022 is among us! New features and improvements!     That's all for today and stay tuned! Luca Support me  Previous post: SQL Server, Non-updating updates ...why avoid them clear...