Writing SQL Server code with AI using GitHub Copilot (2024-02-12)

SQLServerCentral Newsletter for February 12, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Writing SQL Server code with AI using GitHub Copilot]( - [Planning a Database Testing Strategy for Flyway]( - [From the SQL Server Central Blogs - Understanding and Using xp_sqlagent_enum_jobs in SQL Server]( - [From the SQL Server Central Blogs - The Docker debug command]( - [Pro T-SQL 2022: Toward Speed, Scalability, and Standardization for SQL Server Developers]( Question of the Day - [An Updatable View]( The Voice of the DBA  Un-Stable Teams I've always valued having a team of people I know and can work with. While I haven't had a lot of long-term jobs before Redgate, I have had a few positions that lasted more than a year and appreciated working with the same group for a long time. We might gain or lose a person, but overall, the structure of the team was the same day after day. This was a comfortable atmosphere, and I liked knowing who I was working with each day. At Redgate, we have had some stable teams of people, but in our engineering area, we move teams around. There is [an annual re-teaming]( each December/January where engineers can choose to leave their team and ask to transfer to another one. They get to put in their top three choices (or remain on their team), and we do a good job of trying to match up everyone's preferences. The number and charter of teams do change a bit each year, so engineers get visibility into the structure we're planning before they mark a preference. It seems from our internal reports that we match up 99% of engineers with their first or second choices (first choice is in the high 80s). Recently our [head of engineering talked about the dangers of a long-term stable team vs. making changes](. Chris Smith noted that 25-33% of engineers look to move, which is my impression. I get to know who works on different products (or parts of them), and while I often know year-to-year who can help with which issues, I'm also surprised by the changes at times. I'm also disappointed sometimes because some very talented engineers will leave a team to move to another. That can worry me as I know how much knowledge is leaving. At the same time, as Chris pointed out, sometimes very talented people will leave an organization because they feel stuck in one job. There also is value in cognitive diversity, which is something I look for. We need to get along, but I like working with people that think differently than I do. I haven't seen this need to move as much in operations teams as in development teams. Maybe the nature of the person who supports systems is different than those who build them, but I do find long-term system administrators sometimes struggle with change and improvements. I'm not fond of change for change's sake, and I want to justify new technologies/protocols/etc. However, I also think as technology changes, we ought to objectively evaluate if it's better. Not all change is, but some certainly improve the environment. Do you prefer a stable team or do you like to change periodically? A lot of companies move people around, sometimes on a schedule, to ensure they can grow and bring new ideas and perspectives to other teams. Does your employer do this? Do you like the idea or would you prefer not to re-team periodically? Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Writing SQL Server code with AI using GitHub Copilot]( Daniel Calbimonte from SQLServerCentral Introduction In this article, we will see how to work with GitHub Copilot. GitHub copilot is an interesting plug-in that generates code using AI. The technology is from Microsoft and OpenAI. We will show how to install Copilot, and how to generate SQL Server code (T-SQL) using Copilot. First, we will answer some FAQs about Copilot […] [External Article]( [Planning a Database Testing Strategy for Flyway]( Additional Articles from SimpleTalk With Flyway, you can adopt a test-driven development strategy that will allow you to test and evaluate databases, and database objects, at every phase of the database development lifecycle. The further down the delivery pipeline that bugs appear, the more costly in time and resources they are to fix. This approach will allow you to catch many of them before the database change even gets committed to version control, making a continuous delivery process much easier to adopt and sustain. [Blog Post]( From the SQL Server Central Blogs - [Understanding and Using xp_sqlagent_enum_jobs in SQL Server]( SQLPals from Mission: SQL Homeostasis Understanding and Using xp_sqlagent_enum_jobs in SQL Server What is xp_sqlagent_enum_jobs? xp_sqlagent_enum_jobs is an undocumented, extended stored procedure in Microsoft SQL Server. It is used to provide information about the jobs that are... [Blog Post]( From the SQL Server Central Blogs - [The Docker debug command]( dbafromthecold@gmail.com from The DBA Who Came In From The Cold In the latest version of Docker Desktop a new command has been included call docker debug. Now this is only available with a Pro Docker licence but it’s an... [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):  An Updatable View I have this view, based on a table where CityNameID is an identity field. CREATE VIEW dbo.City AS SELECT cn.CityNameID, cn.CityName FROM dbo.CityName AS cn GO I want to make it updatable to run this code: INSERT City (CityName) VALUES ('Elbert') What do I need to do?  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) Error Severity 10 I have code that raises an error. RAISERROR ('My error', 10, 1); When the calling program receives the error, what severity is returned to the client that called this code? Answer: 0 Explanation: For compatibility reasons, errors with severity 10 are converted to 0 before returning to the client application. Ref: - Database Engine error severities - [ - Raiserror - [ [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 2016 - Administration [Long running open temp_db transaction.Age in seconds = 3456 secs]( - Version : Microsoft SQL Server 2016 (SP2-CU17-GDR) (KB5014351) - Microsoft Corporation Enterprise Edition. We keep receiving these alerts. Are there any parameters which can be "turned off" to suppress these messages ? [Import sql objects definiton into individual file]( - Hi, I'm trying to put the initial object definition into Azure Devops. My sql servers are on prem 2016. How can I script all the objects definition( table, views, stored procs, function) with if exists drop on the top in to their own individual file so that I can push them to Azure devops. This […] SQL Server 2019 - Administration [Server Audit Specifications, Audit Action Type -- SELECT UPDATE INSERT DELETE]( - I'm trying to capture an audit of ANYONE logging into a database on my SQL 2019 Enterprise instance. When setting up my 'Server Audit Specifications' the 'Audit Action Type' drop down is not showing options to select SELECT UPDATE INSERT DELETE as presented in online tutorials. I am both LOCAL ADMIN and SQL sysadmin on […] [add a group in local security policy]( - i want to add a group in multiple local security policy like 'Adjust memory quotas for a process', 'Lock pages in memory' , 'Log on as a service', how can it be achieved as a script? please share ideas. [Veeam backups and Tlog maintenance]( - Was testing with VEEAM to do SQL backups... Using app aware, the set up indicates the tlog would be truncated. But after testing I found the tlog content was not being truncated after backup. Does anyone else use VEEAM with App Aware with SQL Server and how to you mange the size of your tlogs? SQL Server 2019 - Development [xp_dirtree]( - Hello. I'm using sys.xp_dirtree to get a list of files in a folder. exec master.sys.xp_dirtree '\\myServer\Users\Documents\BulkInsert\', 1, 1; This was working fine whilst the number of files was approximately 3000. Now the number of the files has double but xp_dirtree is only returned the circa 3000 files. I changed the statement to :- exec master.sys.xp_dirtree […] [Table reference issue]( - We've been tasked for updating a system for a client on a more modern approach. Currently they do their business in access forms. The problem is when I try to reference the table I added to their existing table, I get the following error ,' columns in table do not match an existing primary key […] SQL Azure - Administration [Azure SQL Limit on external connections]( - Hello All, I have a use case to setup a Azure SQL DB for analytics and report developers. Planning for 20 people that will be connecting via SSMS/Azure Data studio/ PBI , excel etc. Reading the documentation there appears to be a very strong limit on external connections (which I assume the above are?) Is […] Reporting Services 2008/R2 Administration [update data source on all SSRS reports]( - I have many reports that are using a shared data source. The data source looks like this and it is deployed to a certain location on the reports server. I crossed out some sensitive information. But hopefully you can see what this is. Our Windows admin is about to delete this account and he has […] Integration Services [Execution of a Job Skipping Tasks When Ran from Agent]( - Hello, I'm running VS SSDT 2017. I made a package in SSIS that I want for the SQL Server Agent to be able to execute as part of a scheduled job in the Job Activity Monitor in SSMS. The problem that I'm having is that when I execute the package from the activity monitor, a […] [Filtering result set with a value selected from another Sql Server]( - Hi, I have a very slow view at one of the customer's database. I could speed up the select if I could filter the data with Value_Date. The "problem" is that there are two Sql Servers in this equation and I don't yet know how to use variables / expressions to accomplish this... I know […] [VS Professional 22 (17.8.6) installed - I can\'t create a SSIS ETL Project]( - Hi, I used to work a lot with MS SSIS ETL but I have been away 5 years now - working with Sql Server, DB2 and Informatica PowerCenter ETL Now I have an assignment with a customer who wants me to convert their PowerCenter ETL to SSIS due to company strategic policy. I installed Visual […] MySQL [Synchronous vs. Asynchronous Replication]( - Hi All, I want to deploy both Synchronous vs. Asynchronous replication in mysql nodes at the same time, basically I need to setup a 3 mysql nodes as a cluster on AWS instances (Synchronous replication) to sync between each other and one mysql node in my on premise server with Asynchronous replication to work as […] SQL Server 2022 - Administration [Data Architecture Options]( - We have a requirement to have 0 downtime during Migrations, more like Active- Active. What options do we have on Microsoft SQL Server side ? What restrictions do we have on Azure MI Business Critical? basically scripts are applied , every week as a part of Product Release and we need 0 down time, any […] SQL Server 2022 - Development [Migrating from SAP SQL Anywhere to SQL Server 2022 Standard]( - Good day We are in the progress of migrating SAP SQL Anywhere to SQL Server 2022 Standard. However, we have encountered numerous cases where the stored procedures pass a calculation as a parameter, e.g. Declare @Par INT = 10, @ReturnValue INT = NULL; /* a lot of calculations and variable assignment happening in this block*/ […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -