Calculating Prime Numbers With One Query (2023-06-14)

SQLServerCentral Newsletter for June 14, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Calculating Prime Numbers With One Query]( - [Building a Kimball Data Warehouse]( - [Are PostgreSQL memory parameters magic?]( - [From the SQL Server Central Blogs - Microsoft Announcement: Microsoft Fabric]( - [From the SQL Server Central Blogs - Introduction to SQL Server Query Store]( - [SQL Server Execution Plans, Third Edition, by Grant Fritchey]( Question of the Day - [Managed Instance Memory per vCore Limits]( The Voice of the DBA  Actively Choosing Compatibility SQL Server databases have had a compatibility level for a long time. This is a setting that enables the database to process code as if it were a particular version. The levels go from 80 (SQL Server 2000) to 160 (SQL Server 2022). Each time (almost) a new version of SQL Server has been released, there has been a new compatibility level. However, not all versions can support all levels. For example, my SQL 2022 instance can support levels back to 100 (SQL Server 2008). If I wanted to get a database to act like a version older than 2008, for example, 2005, I would need to install a SQL Server 2012 or older instance. There is a table of engine versions and supported compatibility levels on the [ALTER DATABASE Docs page](. When you create a new database, by default, it is at the current compatibility level. However, if you upgrade a database, the level might not change. There are some limits to which versions are supported, so an upgrade might change your database. An administrator might choose to keep an older level for [compatibility purposes](. Perhaps your code has an identifier that is now a keyword. Perhaps you expect some code to behave a certain way. However, not all changes are protected by the compatibility level. Most of the time, an administrator must manually change this, which is something that can slip through the cracks. If you don't change this right away, likely it isn't going to change. There was an interesting post from Brent Ozar recently that [explained a bit about compatibility levels in a SQL Server database](. I wonder how many people actually actively choose a level or they just accept the default level for that instance. Brent gives some advice in the post, and his recommendations vary a bit, depending on whether you are happy with the system or not. He also recommends measuring your system and then evaluating a change. Especially if this is a database for vendor software. I don't think changing or updating this is a priority, but I also think that being aware of when your level doesn't match the instance and documenting this is important. At some point, through many upgrades, you might find your level isn't supported any longer. Then your database might have immediate issues. A good monitoring system can let you know when you have mismatches that can be evaluated when there is time. Each version of SQL Server adds new features, like the changes for [Intelligent Query Processing](. In general, we want to take advantage of these if we can. However, not all workloads respond positively, so as Brent mentions, you need to test and evaluate your workload. Hopefully, you have a clear "things are better" or "things are worse" when changing levels. When you get some queries that perform better and some worse, then you have some choices to make. Often the default is "do nothing," which may or may not be the best decision, but the devil you know is sometimes easier to deal with than the one you don' t know. That's fine but consciously make that choice. Keep an eye on your system and don't just accept defaults, whether those are from Microsoft or the ones you've left after an upgrade. Actively manage your systems to get the best performance you can for clients. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Calculating Prime Numbers With One Query]( wfw311 from SQLServerCentral This article looks at calculating prime numbers less than n with one query. [SQLServerCentral Article]( [Building a Kimball Data Warehouse]( ab5sr from SQLServerCentral Learn about the Kimball method for data warehouses and how you can get started building one. [External Article]( [Are PostgreSQL memory parameters magic?]( Additional Articles from SimpleTalk Ordinary PostgreSQL users often do not know that PostgreSQL configuration parameters exist, let alone what they are and what they mean. Some parameter tuning can improve the database performance, and we are usually talking about 10%, 20%, and in rare cases, up to 50% performance improvement. [Blog Post]( From the SQL Server Central Blogs - [Microsoft Announcement: Microsoft Fabric]( gana20m from Ganapathi's MSSQLLover Yesterday at Microsoft Build, a significant announcement took place—the introduction of Microsoft Fabric, which is now available for public preview. Satya Nadella, the CEO of Microsoft, went as far... [Blog Post]( From the SQL Server Central Blogs - [Introduction to SQL Server Query Store]( Matthew McGiffen from Matthew McGiffen DBA Introduced with SQL 2016, Query Store was, probably without doubt, the most anticipated and talked out new feature. In this post we'll just take a brief look at it,... [SQL Server Execution Plans eBook, Third Edition, by Grant Fritchey]( [SQL Server Execution Plans, Third Edition, by Grant Fritchey]( Grant Fritchey from SQLServerCentral Every Database Administrator, developer, report writer, and anyone else who writes T-SQL to access SQL Server data, must understand how to read and interpret execution plans. This book leads you right from the basics of capturing plans, through how to interrupt them in their various forms, graphical or XML, and then how to use the information you find there to diagnose the most common causes of poor query performance.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Managed Instance Memory per vCore Limits There are three editions of Azure SQL Managed Instance, as of June 2023 (General Purpose, Business Critical, and Memory-Optimized). What are the amounts of RAM that I get per vCore for these editions? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Getting the Winners I get a JSON string that looks like this: DECLARE @JSON NVARCHAR(MAX) = N'{ "WinnerIDs": [11,14,15,16,45], "Race": "Furry Scurry", "RaceStartDate": "2022-05-20T08:00:00", "RaceEndDate": "2022-05-20T10:00:00", "Entries": 100 }'; I want to parse out the values of the winners to show each on a separate line of the result set. I try a couple of things. First, this: SELECT oj.Race, oj.WinnerIDs FROM OPENJSON (@JSON) WITH (Race NVARCHAR(100) '$.Race' , WinnerIDs NVARCHAR(MAX) '$.WinnerIDs' AS JSON) oj Second, this: SELECT oj.Race, oj2.WinnerIDs FROM OPENJSON (@JSON) WITH (Race NVARCHAR(100) '$.Race' , WinnerIDs NVARCHAR(MAX) '$.WinnerIDs' AS JSON) oj INNER JOIN OPENJSON (@JSON) WITH (Race NVARCHAR(100) '$.Race' , WinnerIDs NVARCHAR(MAX) '$.WinnerIDs' AS JSON) oj2 ON oj.WinnerIDs = oj2.WinnerIDs Third this: SELECT oj.Race, oj.[value] AS WinnerID FROM OPENJSON (JSON_QUERY(@JSON, '$.WinnerIDs')) AS oj Fourth and lastly, this: SELECT oj.Race, oj2.[value] AS WinnerIDs FROM OPENJSON (@JSON) WITH (Race NVARCHAR(100) '$.Race' , WinnerIDs NVARCHAR(MAX) '$.WinnerIDs' AS JSON) oj CROSS APPLY OPENJSON(oj.WinnerIDs) AS oj2 Which of these returns these results? Race WinnerIDs Furry Scurry 11 Furry Scurry 14 Furry Scurry 15 Furry Scurry 16 Furry Scurry 45 Answer: Fourth code Explanation: The last query with the cross apply will work. The first and second ones return a single row with all winnerIDs in one column. The third one doesn't work, and the last one does. Note, there are other ways to do this. Ref: - open_json - [ - json_query - [ [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [Vulnerability Assessment removed?]( - In Brent Ozar's list of links today, I learned that Microsoft has removed the Vulnerability Assessment tool from SSMS as of v19.1. I recently started using it in my SQL Server environment and found it a great tool -- I was able to demonstrate to management why some of their security shortcuts were a bad […] SQL Server 2017 - Development [Need help with a Query logic]( - I have a table which tracks of activity when users from one specific app login and logout. But I have seen some cases where let's say a user logins at 11:40 on March 24th and then logins again at 11:55 on the same day without logging out. The application doesn't allow that but I am […] SQL Server 2016 - Administration [Database copy issue through - COPY DB WIZARD]( - I have a production server with SQL server 2016 version (13.0.5026.0) and a database with 25 GB of Data and 525 tables in it. Now, I want to transfer this production DB to my development environment with SQL server 2016 version (13.0.5026.0). I am using DB copy wizard for DB copy to the new instance […] SQL Server 2016 - Development and T-SQL [Help with a SQL Unpivot]( - I am trying to use unpivot to extract some data, however, the results are getting messed up on at least one record. By this I mean it is out of order which it should be CLNUM: MODEL: CLSTATUS: CLSTATDATE: CLSTOP: Here is the query any help or advice would be appreciated. WITH ClientsWithOpenMattersCTE (ClientNo, HowMany) […] [SQL help with partition]( - With the following data, I need to create a column that will display a counter for each VID, Acc where PrgFld is not 'XXX' I am trying to do this with partition, but it's increasing the counter when PrgFld is XXX. I would prefer to not increase the counter or show 0 for XXX. Is […] [NOT LIKE Alternatives in WHERE clause]( - Hello, I need help to improve an existing (hence I cannot change table's structure nor the content's logic) WHERE clause, please. The table consists of returned products with their respective reason(s). In case there is more than one reason, they are concatenated. The request as shown in the mock-up below, has to retrieve a list […] Administration - SQL Server 2014 [Database backups going to device]( - Hi Friends, How can I know the exact device where my backups are going on? I only see {67BAB2D0-A1DC-44F8-BF73-EBAFD5AE3220}16 as one device. However, I do not find out where are my backup files located. Some databases are going to a particular drive, but others are using that expression, Thank you, Best Regards, SQL Server 2019 - Administration [Upgrading from 2016 to 2019 with Encryption]( - Hi, I have a few AWS EC2 instances currently running SQL Server 2016. I'd now like to upgrade these to SQL Server 2019 but the problem is that some of the data has been encrypted at the column level. I understand that, after SQL 2016, the encryption algorithm changed from SHA1 to SHA2. Would this […] SQL Server 2019 - Development [how to de escalte a isolation level, and are there any implications.]( - hi, I have one nested transaction and want to deescalate to outer transaction's isolation/ or lower isolation. is it the right way? SET TRANSACTION ISOLATION LEVEL read committed; BEGIN TRY BEGIN TRAN DBCC USEROPTIONS --want to do some job in read commited SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRY BEGIN TRAN DBCC USEROPTIONS --want to […] [Advanced SQL query help]( - Hi, I am looking for some help with two queries using the following table/data. It would be great to see the most efficient way to achieve these. Queries 1: Show all homes with rent above the average rent value for their Company 2: Show the number of homes registered 0-3 months, 4-6 months, 6-12 months, […] [MSSQL Service Broker DSQL operation on another database causing Broker failure]( - I have established a Service Broker configuration based on Eitan Blumin's excellent example. Advanced Service Broker Sample: Asynchronous Triggers I have crafted my own Stored Procedure. I can get the example to work when I'm performing activities within the same database. If I try to operate outside the current DB I get errors. I'm new […] [Extracting multiple fields from one column]( -   Hello, I have a situation. I am trying to show each distinct event category, event action and event label from 'Hits' column and show the number of times each event occurred and the month that the event occurred– for labels containing “GNAV”. I am not sure why but something is wrong. Do you have […] Analysis Services [How can I show the last opening period for each project in my project dimension]( - I have two attributes hierarchies in my dimension [Dim Project]: Project Code Opening date Project A with 3 opening date : 2023-01-01 , 2023-01-02 and 2023-01-03 Project B with 2 opening date : 2023-01-02 , 2023-01-04 MDX query : Select [Dim Project].[Project Code] .[Project Code].Allmembers * [Dim Project].[Opening date].[Opening date].Allmembers on 0 [Measures].[Measure1] on 1 […] SQL Server 2022 - Administration [Allow only encrypted connections.]( - Hi In SQL22, Is there a way to allow only / force encrypted connections? I have 'force encryption' on and have cert installed:  But when I connect via SSMS I can unselect ' Encrypt Connection' and it connects. My understanding is that means that it is allowing non-encrypted connections: How do I make my […] SQL Server 2022 - Development [SQL Query]( - Hi, i have a sample table: table ID  flag  name 1    0   test1 1    0   test2 1    1   test3 2    0   test4 2    0   test5 3    1   test6 4  […]   [RSS Feed]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -