Unleashing SQL Sorcery: Increasing Performance and Complexity with GitHub Copilot (2023-08-19 DBW)

Database Weekly for August 19, 2023 Problems displaying this newsletter? [View online](. [Database Weekly]( The Complete Weekly Roundup of SQL Server News by SQLServerCentral.com Hand-picked content to sharpen your professional edge Editorial  Using AI with Data Tasks The hot new technology of the year is AI. Between ChatGPT, Copilot, and generative AI, it seems that this is invading the world of computing at an incredible rate. Whether this becomes really useful and valuable or not is something that we will see over time. There have been plenty of trends in this area in the past that haven't become as ubiquitous as the hype would lead you to believe. I've done [some light experiments]( with AI on my blog. To date, I haven't found this to be that useful, other than a few cases where I basically used an AI to search the web for me. Rather than read a bunch of SSC or Stack Overflow results, the AI summarized things. Somewhat. I definitely had to test and verify the code more than I feel I've done with code posted in a forum. Of course, I do less experimenting because the AI results were a little more targeted to what I needed, rather than my cobbling knowledge and partial solutions together. I'm also not sure which I prefer. I would like to use AI for data work, and there is [an article]( talks about some of the ways that we've used AI in the past. Data profiling has made sense, and I can see value here. For data security, I'm not sure how helpful AIs have been. I've looked at some products, and I don't know that I think any of them do a great job of identifying data. They do make it easy for whoever is assigned the task by doing some of the work, but they aren't a panacea. They make mistakes, just like humans do. I do think data observation and looking for anomalies is a place where AI can really shine, but that's not the data work that many of us do. It matters, but for most of us, this isn't something we deal with. The future of AI was more interesting. The idea of data homogenization, taking data from different sources, and fitting it to a data model is interesting. Of course, the AI can't make too many mistakes, or the time correcting might overwhelm the time saved. I think we see that now with humans who we ask to ETL data. If they aren't good at it, or make lots of mistakes, those of us overseeing them might just do the entire job ourselves. I know that AIs are still new and immature, and while there is a lot of potential, they feel like junior staffers now, needing more handholding and micromanaging than I like to do. Perhaps they will change our careers and the way we work, but I don't know how quickly, or even how deeply. Already I find lots of companies putting restrictions on what their employees can do with AI, which makes me think this might be more a targeted, niche technology more than a general, use-it-everywhere solution. Steve Jones - SSC Editor [Join the debate, and respond to the editorial on the forums](  The Weekly News All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit. Vendors/3rd Party Products [Managing Test Data for Database Development]( In coming up with a strategy for managing test data, first you need to understand what the many test requirements are likely to be for the particular database you are developing. Next, you need to create all the different categories of test data required to suite test requirements and, finally, work out how to manage it in a way that allows a test-driven approach to development. Read more in this latest article from Phil Factor AI/Machine Learning/Cognitive Services [Basic Terminologies Large Language Models]( From frankdenneman.nl Many organizations are in the process of deploying... [Video: An Intro to MLflow and Azure ML]( From 36 Chambers – The Legendary Journeys THE VIDEO THE SYNOPSIS In this video, we look at t... [Unleashing SQL Sorcery: Increasing Performance and Complexity with GitHub Copilot]( From Azure SQL Blog 2  So, you're ready to journey further into... [AI for Data Management: An Old Idea with New Potential]( From IT Pro - Microsoft Windows Information, Solutions, Tools Organizations should go beyond just using proven methods for leveraging AI to streamline data management by taking advantage of novel techniques that are just now emerging. [Generative AI vs. AI]( From Past News - RSS Feeds Discover how AI in general differs from generative AI and how they combine to improve decision making, accuracy and results. The post Generative AI vs. AI appeared first on eWEEK. [Detecting “Violations of Social Norms” in Text with AI]( From Schneier on Security Researchers are trying to use AI to detect “social norms violations.” Feels a little sketchy right now, but this is the sort of thing that AIs will get better... [Think AI Has Reshaped Cloud? Wait Until You See What's Next.]( From IT Pro - Microsoft Windows Information, Solutions, Tools AI is transforming not only what services are available to cloud customers but how the cloud operates. Administration of SQL Server [Lesson Learned #418: Understanding SQL Events: Attention Signal and User Error Message]( From Azure Database Support Blog Several days ago, I got a service request where we had a conversation about the differences about SQL Server Events: Attention Signal and User Error Message. In the realm of... [Why You Shouldn’t Start Stored Procedures with sp_]( From Callihan Data Everyone has an opinion on naming stored procedures. One opinion that can be agreed upon is that starting a stored procedure with “sp_” is not the way to go.... [Make SQL Server Database Restores Easy With sp_DatabaseRestore]( From StraightPath Solutions SQL Blog If you’ve read the Straight Path blog for any period of time, you’ve probably noticed more than a few posts about database backups. You may have also noticed we’re... [Deploying Resource Governor Using Online Scripts]( From Michael J. Swart When I deploy database changes, I like my scripts to be quick, non-blocking, rerunnable and resumable. I’ve discovered that: Turning on Resource Governor is quick and online Turning off... [SQL SERVER – Unique Indexes on Nullable Columns]( From Journey to SQL Authority with Pinal Dave As the server allows only one null value in a column if a unique constraint is applied. Let us learn about Unique Indexes on Nullable Columns. First appeared on SQL... [Finding a History of Database Snapshots, Taken and Restored]( From SQL Undercover This is a question that’s come up twice this morning, firstly where can we find a history of database snapshots and secondly where can we find a history of... Azure Databricks, Spark and Snowflake [Improving Performance with Adaptive Query Execution in Apache Spark 3.0]( From Hadoop in the Real World Apache Spark, the popular distributed computing framework, has been widely adopted for processing large-scale data. With the release of Apache Spark 3.0, a groundbreaking feature called […] The post Improving... Azure SQL Managed Instance [Your max log rate on SQL Managed Instance Business Critical is now doubled]( From Azure SQL We are excited to announce that the transaction log write throughput (referred to as "log rate") limit is now doubled for the Azure SQL Managed Instances Business Critical with... [Scenarios with private endpoints to Azure SQL Managed Instance]( From Azure SQL Last week we announced the general availability (GA) of private endpoints for Azure SQL Managed Instance. Today, we bring you examples of private endpoints in practical scenarios, starting from... [Tips & Tricks #5: Unable to login to Azure SQL Managed Instance using AAD Integrated]( From Azure Database Support Blog Issue: Trying to login to Azure SQL Managed Instance (MI) from SQL Server Management Studio (SSMS) using AAD-Integrated keeps getting the below error. However, the user is able to connect... Career, Employment, and Certifications [Almost Half of Americans See Automation Replacing Their Jobs]( From IT Pro - Microsoft Windows Information, Solutions, Tools Close to half of Americans say automation could easily replace their jobs, according to an American Staffing Association survey. Cloud - Azure [Managing Data Costs on Azure]( From Dataversity As more businesses migrate their operations and da... Conferences, Classes, Events, and Webinars [Standard Sponsor Rates End August 30!]( Secure your Sponsorship package before the rates go up. PASS Data Community Summit 2023 is where the database community meets every year to make life-long connections with peers, network with industry leaders, and advance their careers. With over 170 technical sessions, and a great line-up of sponsors, this is your time to secure your space before August 30 and get a preferred booth location. [PostgreSQL 101: The ten PostgreSQL extensions you need to know about]( Extensions are a unique and powerful capability in PostgreSQL, a feature that other relational databases do not provide. Taking advantage of the extension framework in PostgreSQL is a true advantage to using the platform. In this webinar, find out how PostgreSQL extensions can help you find solutions to some of your common problems. Register now and to join on August 22nd [Meet the Summit 2023 Sponsors]( Preparations for Summit 2023 are fully underway! We thought what better way to introduce you to some of this year’s sponsors than sit down with them and find out what they have in store for you in November… DMO/SMO/Powershell [How to check if powershell is running as administrator with elevated privileges]( From SQLServerCentral Blogs How to check if powershell is running as administr... [Remotely Copy Files To Multiple Destinations Using PowerShell]( From SQLServerCentral Blogs Remotely Copy Files To Multiple Destinations Using... Data Visualisation [a wordy slide makeover]( From Storytelling with Data We enjoy sharing our makeovers with readers, both as inspiration for you and to synthesize and document our learnings. We’ve even created a page highlighting some of our past... Data Warehousing [How To Identify The Grain]( From Jeff Taylor Creating a data warehouse can be challenging. Identifying the grain is one of the most important parts of having a quality data... Database Design, Theory and Development [Data Types - Does size really matter? - nvarchar vs. varchar]( From Jeff Taylor How many of us have created a new table and used t... MDX/DAX [Differences between GROUPBY and SUMMARIZE]( From Sqlbi GROUPBY and SUMMARIZE seem similar, but they diffe... Microsoft Fabric ( Azure Synapse Analytics, OneLake, ADLS, Data Science) [What's in a name? Naming your Fabric artifacts]( Does it really matter what you call your artifacts when you create them in a Fabric Workspace? [Using Azure DevOps Analytics views and Microsoft Fabric to create Sprint review reports]( From Kevin Chant Reading Time: 8 minutes In this post I want to cover using Azure DevOps Analytics views and Microsoft Fabric to create Sprint review dashboards. I consider this post... [Discover the Microsoft Fabric Data Wrangler]( From Simple Talk The Data Wrangler is as interesting as hidden inside Microsoft Fabric. It’s not easy to find and activate it, but it is worth the trouble. Before digging into the... [What are Capacities in Microsoft Fabric?]( From MSSQL Tips In this article, we look at what capacities are available for Microsoft Fabric and a breakdown of the cost structure for these services. Oracle/PostgreSQL/MySQL/other RDBMS [When PostgreSQL Parameter Tuning is not the Answer]( In this blog, we will segue from discussing PostgreSQL system parameters best practices to other ways of performance tuning. Moreover, we will demonstrate that essential database performance tuning goes beyond choosing the appropriate parameters settings. Performance Tuning SQL Server [Query Tuning Essentials: How Key Lookups Can Hurt SQL Server Query Performance Demo]( From Erik Darling Data Away Days It’s August, and that means one thing:... [Query Tuning Essentials: How Local Variables Hurt SQL Server Query Performance Demo]( From Erik Darling Data Away Days It’s August, and that means one thing: Family Vacation. I’m taking this month off from regular blogging, and posting some of my paid beginner training content for... [Query Tuning Essentials: How Key Lookups Can Hurt SQL Server Query Performance]( From Erik Darling Data Away Days It’s August, and that means one thing: Family Vacation. I’m taking this month off from regular blogging, and posting some of my paid beginner training content for... [Query Tuning Essentials: How Table Variables Hurt SQL Server Query Performance]( From Erik Darling Data Away Days It’s August, and that means one thing: Family Vacation. I’m taking this month off from regular blogging, and posting some of my paid beginner training content for... PowerPivot/PowerQuery/PowerBI [(Livestream Replay) Leveling Up Power Query - Beyond the U/I (with Chandeep Chhabra)]( From Havens Consulting DESCRIPTION Learning how to solve advanced problems using the M language which are beyond the scope of Power Query User Interface. Discover the beauty of the mash-up (m) language,... [5 Pro Tips for Getting Started with Power Query]( From Havens Consulting Video by: Reid HavensLearn about five useful tips ... [How to Build a Custom Tornado Chart in Power BI Desktop]( From MSSQL Tips Learn about tornado charts and how to build a torn... [Is Data Wrangler the Power Query of Python?]( From Guy in a Cube Have you tried Data Wrangler within Microsoft Fabr... [Power BI Query Usage and Performance Series – Part 1 – Enabling Log Analytics, generating the KQL queries]( From FourMoo As with most of my blog posts it involves a client from a customer where I am consulting, which I think will help others. The requirement was to analyse... [Enabling Power BI Paginated Report Drill!]( From Guy in a Cube Power BI paginated reports support drillthrough! Patrick walks you through how to configure the reports within Report Builder to get them working. Drillthrough reports in a paginated report (Power... [Forget Python! Building Models in Power Query]( From Adatis Programming languages such as Python and MATLAB ca... [Create a calendar view with the matrix visual in Power BI]( From MARTIN'S BLOG  [Can You Develop For Power BI Using Only Your Browser?]( From Chris Webb's BI Blog Why the day you can do all your Power BI development work in the browser is coming soon [Transforming Power BI Customization: “WireFrames” in PowerBI.Tips Theme Generator]( From PowerBI Tips Introducing the New PowerBI.Tips WireFrames Feature In the dynamic landscape of data visualization, staying ahead of the curve is crucial. That’s why we’re thrilled to introduce the latest game-changing... Product Upgrades and Releases [Cumulative Update #22 for SQL Server 2019 RTM]( From MS SQL Server Blog The 22nd cumulative update release for SQL Server ... [General availability: Terraform support for Azure Cosmos DB for PostgreSQL]( From Azure Updates Now you can use Terraform for unified inventory and management operations for Azure Cosmos DB for PostgreSQL clusters. [General Availability: Incremental snapshots for Premium SSD v2 Disk and Ultra Disk Storage]( From Azure Updates Incremental snapshots for Premium SSD v2 and Ultra Disk Storage with instant restore capability are now generally available (GA) R Language [A Gentle Introduction to Vectors in R]( R is a language for programming with data. Unlike many other languages, the primitive data types in R are not scalars but vectors. Therefore, understanding how to deal with vectors is crucial to programming or reading the R code. In this post, you will learn about various vector operations in R. [Best Practices for Data Cleaning and Preprocessing]( As data scientists, we often find ourselves immersed in a vast sea of data, trying to extract valuable insights and hidden patterns. However, before we embark on the journey of data analysis and modeling, we must first navigate the crucial steps of data cleaning and preprocessing. In this blog post, we will explore the significance of data cleaning and preprocessing in data science workflows and provide practical tips and techniques to handle missing data, outliers, and data inconsistencies effectively. [Mastering Grouped Counting in R: A Comprehensive Guide]( As data-driven decision-making becomes more critical in various fields, the ability to extract valuable insights from datasets has never been more important. One common task is to calculate counts by group, which can shed light on trends and patterns within your data. In this guide, we’ll explore three different approaches to achieve this using the powerful R programming language. [Little useless-useful R functions – Goldbach’s conjecture and Sieve of Sundaram]( From TomazTsql This is fun It is also O(MAX) complexity. But... SQL [The GROUP BY Clause]( When you’re learning SQL DML, the most complicated clause is typically the GROUP BY. It’s a fairly simple grouping based on values from the FROM clause in a SELECT statement. Security News and Issues [Bots Are Better than Humans at Solving CAPTCHAs]( From Schneier on Security Interesting research: “An Empirical Study & ... T-SQL and Query Languages [SQL SERVER – Banker’s Rounding]( From Journey to SQL Authority with Pinal Dave Also known as unbiased rounding or round half to e... [The SELECT INTO Temp Table Easy Guide: How to Manage Temporary Data Without Dismay      Â]( From SQL Server – {coding}Sight Data processing may involve several steps. And it can be fun! But the fun ends when you access some joined tables again and again. So, when you look at... [Fun With KQL Windowing Functions – Row_Rank_Dense]( From Arcane Code Fun With KQL Windowing Functions - Row_Rank_Dense Virtualization and Containers/Kubernetes [3 Things Your FinOps Strategy for Controlling Kubernetes Costs Requires]( From IT Pro - Microsoft Windows Information, Solutions, Tools People, processes, and technology are the crucial factors that determine an organization's ability to curtail unnecessary spending within their Kubernetes deployments.  [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. Note: This is not the SQLServerCentral.com daily newsletter list, and unsubscribing to this newsletter will not stop you receiving the SQL Server Central daily newsletters. If you want to be removed from that list, you can follow the instructions on the daily newsletter. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -