What is new in Copilot, the new features available (2024-05-27)

SQLServerCentral Newsletter for May 27, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [What is new in Copilot, the new features available]( - [SQL Server Temporary Stored Procedures]( - [From the SQL Server Central Blogs - Dynamically Unpivot columns in SQL]( - [From the SQL Server Central Blogs - Unlock the Power of Your Data: From Basic to Advanced Data Analysis]( - [Pro T-SQL 2022: Toward Speed, Scalability, and Standardization for SQL Server Developers]( Question of the Day - [Implementing Change Data Capture]( The Voice of the DBA  Managed Instance Impressions Several years ago, I heard about a new product coming in Azure that would provide an IaaS (infrastructure as a service) VM to run SQL Server, with Microsoft managing most of the admin tasks for the instance, like patching and backups. That didn't seem like a big load to me, and I wondered if anyone would actually pay for this product. After all, don't most companies find managing patches and backups? That product became [Azure SQL Managed Instance]( and I've been surprised at the adoption. Quite a few clients have adopted this as a way to lift and shift (mostly) to the cloud in an easy fashion without the restrictions of Azure SQL Database. This looks like a "normal" on-premises SQL Server, and there are both high-performance (Business Critical tier) and average-performance (General Purpose tier) versions of the product that let you choose what level of price/performance you need to achieve. I'm curious today, and I have a question. What are your impressions of Managed Instance (MI)? Whether you use it, you have heard of it, or if you just read this description. Give me a few thoughts on whether this makes sense, performs well, or has issues you need (or wish would be) addressed. I've heard there are some issues with I/O, but I also have clients who find it performs very well for them. I hear similar things from on-premises SQL Server instances, so I often think that either the software is designed well or the hardware architecture doesn't match the workload. There have been a lot of enhancements to MI since its release, including the ability to backup and restore to/from SQL Server 2022. There is even [an offer from Microsoft that lets you try out MI for free]( (for a period of time). This is a way for you to test migrate a database to the cloud and measure the performance. You might need to do some work to measure your current performance in a way that lets you determine how MI stacks up. You will also need to do some financial number crunching to decide whether there is an ROI that makes sense. If you do that, be sure you reach out to your internal finance people to understand the differences between CapEx and OpEx expenditures for your analysis. Paying $50k a year for an MI license isn't the same as spending $50k for a server and hosting. I'm not sure what I think of MI. Like many offerings, I think there are places where it makes sense and places where it doesn't. It's not a simple decision for me as an abstract question. For specific situations, I might lean one way or the other, but I'd want to do some workload analysis to justify or discard my initial thoughts. Share your thoughts and impressions today. You might help some of us learn more about why we might or might not use MI. You might even help clarify your own thoughts by writing them down. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [What is new in Copilot, the new features available]( Daniel Calbimonte from SQLServerCentral Learn how Daniel Calbimonte is using the AI in Bing to have some fun, write some code, and get help. [External Article]( [SQL Server Temporary Stored Procedures]( Additional Articles from MSSQLTips.com Learn about creating stored procedures in the SQL Server tempdb database and why you might want to create temporary stored procedures. [Blog Post]( From the SQL Server Central Blogs - [Dynamically Unpivot columns in SQL]( DataOnWheels from DataOnWheels Picture this, your data ingestion team has created a table that has the sales for each month year split into different columns. At first glance, you may think “what’s... [Blog Post]( From the SQL Server Central Blogs - [Unlock the Power of Your Data: From Basic to Advanced Data Analysis]( Tracy McKibben from RealSQLGuy - Helping You To Become A SQL Hero Data isn't just about numbers and spreadsheets. It holds stories, patterns, and the answers to questions that can drive your business forward. To unlock this potential, you need the... [Pro T-SQL 2022: Toward Speed, Scalability, and Standardization for SQL Server Developers]( Site Owners from SQLServerCentral Learn how to write and design simple and efficient T-SQL code. This is a hands-on book that teaches you how to write better T-SQL with examples and straightforward explanations.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Implementing Change Data Capture I have a complex environment that uses quite a few SQL Server 2022 servers. We have a powerful SQL Server running Enterprise that hosts most of our transaction data. However, there are remote locations that run Standard, Web, Express, and Express with Advanced Services editions for local collection of data before the data is transferred to our central Enterprise Edition server. We are considering using Change Data Capture (CDC) to lower the amount of data we transfer from clients. Which editions can we implement this feature on? 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) Inserting Transaction Marks When is a transaction mark first inserted into the msdb.dbo.logmarkhistory table? Answer: When the transaction commits Explanation: The mark is inserted when the transaction commits. Ref: Recovery of Related Databases That Contain Marked Transaction - [ [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 [Backup with Checksum Show No Issues, While DBCC CHECKDB Reports Errors]( - I took a backup with the CHECKSUM option, which was successful and showed no errors. However, when I restored the same backup, it showed DBCC CHECKDB errors. I want to understand why the checksum did not detect any issues, but DBCC CHECKDB did. What are the issues that a checksum will not detect but DBCC […] [Linked Server Product Name?]( - Just came across something weird. Was wondering if anyone else has ever seen this. All the years I've worked with SQL server linked server objects, I have always put "SQL Server" in product name. All the code examples on the internet include that or mention (in BOL) "Product name Type the product name of the […] [Can index fragmentation cause the database to enter suspect mode?]( - My database was in suspect mode, and when I checked using DBCC CHECKDB, it returned errors. As mentioned in the link below, I used the REPAIR_ALLOW_DATA_LOSS option with the DBCC CHECKDB command, but few errors persisted. Later, I dropped and recreated the indexes. This resolved the issue. My question is whether index fragmentation could […] SQL Server 2016 - Development and T-SQL [Multiple deadlocks occurring with contentious code]( - We are seeing multiple frequent deadlocks occurring with the following extract of code, which forms part of a stored procedure called sp_WS_Save_Record_Details. We have a web service that calls multiple instances of this stored procedure simultaneously. Records in the Record_Stub table are identified by an external reference. The code is trying to ensure that if […] [select string removing two sets of characters]( - Hi , I need help replacing a certain strings from a query response. My searches are pointing to a replace in the actual table but I am looking for sql that returns a different value than what is in the table. I am selecting a varchar(25) field and want to return the characters without the […] [View generating different plan]( - Hello  I have a view based upon a number of tables I'm selecting the same data from this view on 3 identical servers (same memory, processors, tempdb, settings etc.) The amount of data is nearly the same - Same on most dimensional tables; 10's of thousand out of millions of the fact (so < .5%) […] SQL 2012 - General [getting logged in user name to the pc that running the report]( - hi all, is there anyway in SQL to getting logged in user name to windows on the pc that running the power bi report connected to SQL as i've tried some scripts but it retrieved the logged in user to the SQL server itself I did search on some and found that script but it […] SQL Server 2019 - Administration [Swapping ports between 2 instances - can't understand results]( - Have 2 instances on the same TestServer 1. Default 2014 Dev edition on port 1433 2. Named 2019 Express instance "SQLExpress2019" on port 15191 SQLBrowser is working. TCP=1433 port is open on this TestServer. I swapped ports between the 2 instances and restarted the services. Can't tell if UDP 1434 is open.. UDP port 1434 […] [SQL JOB add/modified]( - Is there a trigger to notify when a new SQL Agent job is created/modified? SQL Server 2019 - Development [Unable to connect to SQL 2019 using Pyodbc]( - Development Environment I am learning how to use pyodbc and accessing database in SQL server 2019. Here are the software versions I am using: SQL server 2019 VS Code 1.89.1 Microsoft ODBC driver 18 for SQL Server Python 3.12.3 pyodbc 5.1.0 Developer laptop: Windows 11 Connection Errors: When running python test program in VS, it […] [Aggregating over multiple columns]( - Hello friends, See setup code below. I have a working solution but I am concerned that the DISTINCT is going to kill performance. I am hoping to see some alternative ways to solve this problem so I can compare performance with my real data. DROP TABLE IF EXISTS #Sales CREATE TABLE #Sales ( Country VARCHAR […] Connecting [Can you connect Power Apps to SQL Server if you work in the government?]( - My agency has been using the combo of on-premises SQL Server for the back end and local desktop software for the front end. Now upper management is saying that they want web applications only. And they really want to use our MS 365 license rather than have to pay for anything else. I'm investigating Power […] Design Ideas and Questions [Database design help and tips for a small app used to coach tennis players.]( - Hi, I hope this isn't too long of a question- I tried to make as brief as simplified as possible but maybe I am posting to the wrong place. I am a tennis coach and creating a database/app to automate tasks related to students, programs, practices, performances, and testing. I am finishing the database design […] SQL Server 2022 - Development [I have few snapshot transactions, now i need to put noloc in all tables.]( - I have few snapshot transactions , now my admin has informed me that put no lock in all tables in snapshot transactions, as it was blocking locking deadlocking with others. so I suggested to put read uncommited instead of snapshot when u are putting no lock in all tables of the transaction. q1) snapshote takes […] [Querystore bug(?), not showing data]( - My company where I worked recently upgraded to SQL Server 2022 Enterprise and Querystore started to behave odd (the problem was detected then anyway). If I start Querystore, and filter data, "last hour/last 12 hours" etc everything seems to work ok. But if I pick "custom" and manually specify date+time (even if it's the same […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -