GitHub Copilot - Our Best Ally to for T-SQL (2023-12-04)

SQLServerCentral Newsletter for December 4, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [GitHub Copilot - Our Best Ally to for T-SQL]( - [Championing DevOps: A Panel Webinar]( - [PowerShell Commands to Administer Virtual Machines on Amazon AWS - Part 2]( - [From the SQL Server Central Blogs - Importing Flat Files into Azure SQL DB]( - [From the SQL Server Central Blogs - Beginner's Introduction to PowerShell Remoting]( - [Pro SQL Server 2022 Wait Statistics: A Practical Guide to Analyzing Performance in SQL Server and Azure SQL Database]( Question of the Day - [Zero Statistics Sampling]( The Voice of the DBA  Be Careful with Missing Index Requests One of the things that has been interesting to watch over time is how the SQL Server platform has expanded the amount of information that we get back about the performance of the query optimizer and query processor. While it's not perfect, and there is room for improvement, the advances made with [intelligent query processing]( are helping many systems run faster. Not all queries, but some. As I've done a little work on other platforms, there are ways to [look for potential missing indexes in PostgreSQL]( and [MySQL]( but these aren't built into tools, nor are they easily accessible to developers or DBAs. There's work to be done on many platforms, though I'm not sure if there is more work than required in SQL Server. On all these platforms, you need to dig into queries and understand why they are slow, though the tooling for SQL Server, with graphical plans in SSMS (or with [Plan Explorer]( can make the job easier. One thing SQL Server does is provide missing index recommendations in the query plan. You can find information on this in the docs, but you should make sure you [read the limitations section](. The recommendations returned should not just be run. I should repeat that for junior DBAs, accidental DBAs, and developers: DO NOT JUST RUN THE MISSING INDEX RECOMMENDATIONS WITHOUT TESTING LOTS OF QUERIES. I hate using all caps, but that is important. As an example of why, [watch this short video from Erik Darling]( where he shows that the simplistic view of the missing index is helpful, but not as helpful as it can be. In case that's not enough, there are other issues that [Brent Ozar]( [Phil Factor]( and [Aaron Bertrand]( share some of the problems they've found. There is a wealth of information that is available about queries in SQL Server and how they are processed. It will help you in your career to learn more about performance tuning and how to evaluate queries. We have articles here, there are more on Simple Talk, and Erik Darling produces information every week and also [has training]( to help you learn to tune queries better. There are plenty of others that will help teach you as well. Maybe the best benefit of learning about tuning is that you can learn to write better queries the first time, which means no rework, no effort responding to complaints, and a cheaper bill if you move into the cloud. That might be something you point out to your boss and ask him or her to fund a little education to help you and your employer. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [GitHub Copilot - Our Best Ally to for T-SQL]( Daniel Calbimonte from SQLServerCentral This article examines how GitHub Copilot might be used when working with T-SQL Code. [External Article]( [Championing DevOps: A Panel Webinar]( Additional Articles from Redgate If you aspire to drive Database DevOps at your company, regardless of the sector you work in, this webinar provides a unique opportunity to learn from experts in the finance sector who have successfully paved the way. Join us December 6th. [External Article]( [PowerShell Commands to Administer Virtual Machines on Amazon AWS - Part 2]( Additional Articles from MSSQLTips.com Learn about various PowerShell commands that can be used to administer virtual machines on Amazon AWS. [Blog Post]( From the SQL Server Central Blogs - [Importing Flat Files into Azure SQL DB]( hellosqlkitty from SQLKitty Initially, I thought I would have to use sqlcmd because I’m on a Mac and don’t have SSMS. It turns out Azure Data Studio has a nifty way to... [Blog Post]( From the SQL Server Central Blogs - [Beginner's Introduction to PowerShell Remoting]( SQLPals from Mission: SQL Homeostasis Beginner's Introduction to PowerShell Remoting ... [Pro SQL Server 2022 Wait Statistics: A Practical Guide to Analyzing Performance in SQL Server and Azure SQL Database]( Site Owners from SQLServerCentral Use this practical guide to analyze and troubleshoot SQL Server performance using wait statistics. You'll learn to identify precisely why your queries are running slowly. And you'll know how to measure the amount of time consumed by each bottleneck so you can focus attention on making the largest improvements first. This edition is updated to cover analysis of wait statistics current with SQL Server 2022. Whether you are new to wait statistics, or already familiar with them, this book provides a deeper understanding on how wait statistics are generated and what they mean for your SQL Server instance’s performance.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Zero Statistics Sampling What happens when I use this code on SQL Server 2022? CREATE STATISTICS stat_employee_empname ON dbo.Employee (EmpName) WITH SAMPLE 0 PERCENT; 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) Creating Aggregates What types of code can I include in the CREATE AGGREGATE statement? Answer: Only CLR functions Explanation: The CREATE AGGREGATE statement is used only with CLR functions defined in a .NET assembly. Ref: CREATE AGGREGATE - [ [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 - Development [Slicing status based on conditions]( - Hi, I have a table ShipperStatusHistory that tracks orders, Shipping status and relay points (for customers where to collect packages) . I want to flag the package That weren't collected by customers for each a relay point. To identify a left package (not collected by customer) the status need to be ("Dropoff" or "preparation") ***OR […] SQL Server 2016 - Administration [broker error]( - Hello, I install a broker under sql server 2016 and I have a message that I never recognized. In transmission_status : "24(The program issued an incorrect length command.)" The message of 25490 characters long has not been sent. Thank you in advance for any help. Administration - SQL Server 2014 [Suggestion on HA DR solution for transactional replication published database]( - Hi All, I am planning for HA DR solution for transactional replication published database. (Not for distribution DB). It is in SQL 2014 standard edition and having 4 subscriber for only one database, I am planning to have either log shipping or DB mirroring into another server. Can anyone suggest which is best to for […] SQL Server 2019 - Administration [Large number of Memory Ring buffers]( - Hi Friends, As part of collecting different stats from the SQL servers I have been asked to manage recently, I found the memory ring buffers on a server just crossed a million rows. Though I didnt get any complaints from the users, I would be glad if you can comment on this and indicate if […] SQL Server 2019 - Development [AI Tools]( - Not quite sure where to post this or how quite to ask as its early stage I am looking for two type of AI tool A tool to go through data and check accuracy/ confidence levels very simple example( millions of records ) a customer table with Company name , addresses, postcodes etc a separete […] [Openrowset & removing unwanted characters...]( - Folks I'm wondering can you help me here. I have inherited a system that populates a table from Excel using the OPENROWSET command. The table in question is called: tbl_raw_data and then the loaded data is moved on elsewhere... The excel speasheet can and does change column names and number of rows over time. The […] [Script all logins, permissions, roles]( - I found this older script by Greg Ryan in 2013 to script out all the logins, permissions, roles. however, i didn't see the role permissions. Please could some T-SQL guru help me to include the role permissions in this script as well. Truly appreciate your help. /**************************************************************** This Script Generates A script to Create all […] SQL Server 2008 - General [Reduction of installation time of sql server 2008 R2 Express]( - The installation of SQL Server 2008 R2 Express on a virtual machine takes around 12-15 minutes, whereas on a regular PC, it takes around 5-6 minutes. Are there any options to reduce the installation time ? The following approaches have been attempted: Via command line: SQLEXPR_x64_ENU /ACTION=Install /IACCEPTSQLSERVERLICENSETERMS /QS /FEATURES=SQLENGINE /INSTANCENAME=PMP /SQLSVCACCOUNT="NT AUTHORITY\SYSTEM" /AddCurrentUserAsSQLAdmin /SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS" […] SQL Azure - Administration [SQL MI - SQL Jobs failing with User Manager\ContainerAdministrator.]( - This is strange, any SQL job that sends out Emails via 'msdb.dbo.sp_send_dbmail ' are failing with this weird execution user. Executed as user: User Manager\ContainerAdministrator. Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050). The step failed. It doesn't matter if I change the SQL Owner to 'SA', to a AAD […] Strategies and Ideas [data security for a data warehouse]( - I'm working with someone whose client says "Yes, we have a data warehouse. But NO you can't be granted access to it." Technically, can you not create one or more schemas and add views to the schema ( and the other necessary required objects ) so that the outsiders are granted some but not full […] Integration Services [PowerQuery in SSIS... is it only in 2023?]( - First off, I'm terrible at SSIS... I'm trying to create a PowerQuery data source, and it's flat out not there in VS 2022 (yes, I have the rest of SSIS installed). is it only in 2023? is there a good rank beginner article on getting started with it somewhere? I saw one, but I wanted […] General [Review of DB Activity]( - Hi Folks,  I have been tasked with reviewing all the activity on a SQL Server 2000 - Version 8.00.2666 (SP4) I have monitored DB's before and via execution of Master DB stored procs, watching activity and so on, however never really when in to this depth of attempting to find out all the activity […] SQL Server 2022 - Administration [Logging SQLExceptions]( - We have a web front-end which I believe in some cases is throwing unhandled SQLExceptions, like Conversion errors, or Foreign key constraint errors, etc. I realize the best approach is probably for the developers to place their code in TRY/CATCH to handle these errors, but in the meantime, I was wondering if there is anything […] [Dropping a Service from Service Broker crashes server]( - I'm having an issue trying to drop a service. When I issue the drop command, the process runs for multiple hours and then the server dies with the following silent scream (any ideas would be appreciated) This does the same thing on 2 different 2022 CU10 SQL Servers on copies of the same database: 11/27/2023 […] SQL Server 2022 - Development [sp_dropserver - Bug/behavior change - Leaves transaction open after TRY...CATCH]( - Verified on latest CU 10 for SQL 2022. Confirmed works correctly on SQL 2019 (i.e. no open transaction). When it cannot drop server because it is used in replication, the transaction count goes from 0 to 1 after the CATCH. This causes stored procedures with this code to fail, since the transaction count is unexpected. […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -