Analyze Data with a Serverless SQL pool in Azure Synapse Analytics (2023-06-07)

SQLServerCentral Newsletter for June 7, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Analyze Data with a Serverless SQL pool in Azure Synapse Analytics]( - [Build a Better WordPress Archives Page Using MySQL]( - [From the SQL Server Central Blogs - Book Review: SQL Query Design Patterns and Best Practices]( - [From the SQL Server Central Blogs - Automating Database Maintenance on Azure SQL]( - [Build a Better WordPress Archives Page Using MySQL]( - [SQL Server 2022 Query Performance Tuning: Troubleshoot and Optimize Query Performance]( Question of the Day - [Max Managed Instance CPUs]( The Voice of the DBA  Creating Test Data This editorial was originally published on Jan 9, 2020. It is being re-run as Steve is out of town. Test data is hard to come by, and I agree with [Brent Ozar]( " I get so frustrated when I hear trainers/presenters/bloggers/idealists talk about how developers should be using purpose-built-from-scratch data sets with no real customer data in ’em. The real world just rarely works that way." I agree, and I know this is a hard problem. I'm one of those that tries to get people to use non-production data. I know there isn't an easy solution, and there isn't a one-off solution here. Really, my frustration is with the fact that many development people just don't see the problem as one that requires ongoing effort. However, as I've seen unit testing grow and become commonplace, I do expect that eventually we will start to understand that it is worth some effort to produce test data. Often we want a lot of things. We want large data sets, similar to production. We need some randomness in there because we won't think of every edge case. We need some consistent values because we will use them to initially evaluate our logic and finding new values all the time is frustrating. Is there a good way to do this? I've seen some good, creative ways of building test data, such as [Andy Mallon's post]( from last year. I do think routines like this are good, but they need to be a) shared with other developers, b) easy for other developers to find, and c) incorporated into some developer database creation process. To me, this ought to be a process that is maintained by all developers and DBAs, and one that ensures a new development environment can be created in seconds, or possibly minutes. This often means some pre-staging work to ensure developers can quickly and easily hydrate or reset a new database. Certainly products like [SQL Provision]( can help, but these still require some work to ensure that developers have the test data that is useful for them. Ultimately, I think maintaining test data is part of being a developer. Having a central, consistent set (or two), is a part of working in a software team. When we start to accept this as part of our work, we can build more consistent and reliable processes. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Analyze Data with a Serverless SQL pool in Azure Synapse Analytics]( arindamxs from SQLServerCentral Learn how to query data stored in text files from Azure Synapse Analytics using the Serverless pool that is included with each workspace. [External Article]( [Build a Better WordPress Archives Page Using MySQL]( Additional Articles from SimpleTalk I recently wrote about a way to generate visual, interactive calendars in HTML by combining a calendar table with data from an events table. But let’s start from the beginning, by starting from the end. [Blog Post]( From the SQL Server Central Blogs - [Book Review: SQL Query Design Patterns and Best Practices]( MarlonRibunal from Marlon Ribunal - SQL, Code, Coffee, etc. This book will provide you with a good foundation upon which a consistent convention for practicing your day-to-day operations can have a solid footing in your database discipline, with... [Blog Post]( From the SQL Server Central Blogs - [Automating Database Maintenance on Azure SQL]( hellosqlkitty from SQLKitty Database maintenance is crucial for the health and performance of any database system, including those in Azure. Without regular maintenance, databases can become slow, unstable, and prone to crashes... [SQLServerCentral Article]( [Build a Better WordPress Archives Page Using MySQL]( Site Owners from SimpleTalk I recently wrote about a way to generate visual, interactive calendars in HTML by combining a calendar table with data from an events table. But let’s start from the beginning, by starting from the end. [SQL Server 2022 Query Performance Tuning]( [SQL Server 2022 Query Performance Tuning: Troubleshoot and Optimize Query Performance]( Additional Articles from SQLServerCentral Troubleshoot slow-performing queries and make them run faster. Database administrators and SQL developers are constantly under pressure to provide more speed. This new edition has been redesigned and rewritten from scratch based on the last 15 years of learning, knowledge, and experience accumulated by the author. The book Includes expanded information on using extended events, automatic execution plan correction, and other advanced features now available in SQL Server.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Max Managed Instance CPUs In May 2023, what is the maximum number of vCores I have have on any type of Azure SQL Managed Instance? 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) Refresh Limits Can I use sp_refreshsqlmodule to check the metadata for CLR stored procedures and functions? Answer: No Explanation: No. This system proc cannot be used on CLR stored procedures or functions. This procedure refreshes the metadata for a non-schemabound object passed in as a parameter. This can be used with stored procedures, functions, views, DML trigers, or DDL triggers. Ref: sp_refreshsqlmodule - [ [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 […] [Best migration approach]( - Hi Colleagues, I am considering the across-the-wire migration method to SQL Server 2017. However, my source is an SQL Server 2014 Availability group with three nodes, one asynchronous. Is launching a new VM on the remote site and the same cluster to migrate this first node a good idea? Later, demote the old node, then […] 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 - Development and T-SQL [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 […] [MS SQL Query - JOIN mutiple tables but it duplicates results]( - MS SQL Query - JOIN multiple tables but it duplicate results; Good day! it duplicate results when i run this query below;  - if i comment out "LEFT JOIN BBI$Barcodes AS BAR", the query results are correct. Thank you! 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 [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 […] [T-SQL Query Help]( - Hello, I am working on one of the SQL Logic, i need help to build core logic to do the rest, below is ddl, create table #x1 ( task varchar(100), subtask varchar(100), status varchar(100) ) insert into #x1 values ('a','x','completed') insert into #x1 values ('b','l','overdue') insert into #x1 values ('b','m','completed') insert into #x1 values ('b','n','not […] [SSIS package configuration connecting to design-time instance]( - I have an SSIS package which uses SQL Server Configurations. This uses an expression-based, project connection manager but is connecting to the instance at development time as opposed to the instance at run time. However the remainder of the package is using the run time version of the connection manager. The expression-based values come from […] [MSSQL STRING_AGG() Conversion failed when converting the varchar value to int.]( - Short story, I needed to convert a table insert/update trigger to a Service Broker task. That's not the issue but it might help explain the structures being used. The INSERTED table is used but is passed from the Trigger as XML variable to the SP and executed asynchronously under the Broker Service. Again this is […] SQL Azure - Administration [Scheduled Jobs in Azure SQL Databases Failing for Auto-Paused DB]( - Hello! I have somewhat similar kind of PowerShell script which executes the stored procedure on a schedule. Since the database is set to auto-pause, job is failing at times with the error message below: "Exception calling "Open" with "0" argument(s): on server is not currently available. Please retry the connection later. " How can I […] 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 […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -