In Memory Table Variables In SQL Server: Still Pretty Stupid (DBW 2024-09-14)

Database Weekly for September 14, 2024 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  AI Sings the Blues Quite a few of you aren't thrilled or enamored by GenAI (Generative Artificial Intelligence) with things like ChatGPT or CoPilot. Some of you love it or find it fascinating. My guess is many of you are like me and you're not quite sure how useful this is with a healthy dose of skepticism. No matter how you feel, I find [this piece on AI Blues]( fascinating to read. It talks about some of the problems with AI and how people are becoming less tolerant of the small errors that AIs make. I think people might be upset with some of the issues, but I also think these are types of things we might see in a colleague as well, and we might tolerate them for much longer than we think. The thing in the piece that really caught my eye is that training models to make less errors might make them less creative. I don't think they are very creative, but I do think part of the idea is that they might do something better than we could do, at least for those of us working in a space where we're a beginner to intermediate (or advanced beginner). In many cases, we'd expect better output from a model. The note about training not being often or in-depth enough as well is interesting. Perhaps some of the models ought to be trained separately, with some having a more specific focus, like learning Python and producing very few errors, or learning Python to produce interesting solutions to large scale problems even if there are some errors in the code. I could see similarly more tightly trained models for other areas, such as marketing email or legal issues. Maybe we need a lot of different models tailored for different jobs. Of course, if companies are struggling to generate revenue on AI already (and I think they are), then can they afford to train and retrain specific models focused for smaller audiences? Maybe, and maybe Moore's law will help bail out some AI experiments, but I'm not sure. For now, I continue to experiment, and I think skilled tech people ought to do the same. I don't think it will replace us, but I do think it can be a useful tool in many ways to help us become more productive. 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 [Forks in Flyway Database Development Work]( Database forking allows teams to multi-task, working on different strands of development in parallel. It also allows them to manage several 'variants' of a production database, such as for SaaS applications with client-specific schema requirements. This article explains how Flyway supports and simplifies database forking, via use of Flyway's locations, baseline migrations and by mapping Flyway projects to schemas. AI/Machine Learning/Cognitive Services [Unlocking the Power of AI in the Retail Sector]( From Purple Frog Systems In today’s fast-paced retail environment, businesses are constantly seeking innovative ways to stay ahead of the competition. One of the most transformative technologies in recent years has been Artificial... [A Guide to Storage for AI Workloads]( From IT Pro - Microsoft Windows Information, Solutions, Tools Learn how AI-optimized storage solutions can enhance performance, reliability, and cost-efficiency for AI workloads by providing the right mix of scalability, speed, and data flexibility. [Unlocking the Power of AI and ML to Improve Remote Patient Monitoring]( From IT Pro - Microsoft Windows Information, Solutions, Tools AI and ML are revolutionizing RPM by using predictive analytics and real-time data to improve patient care, optimize outcomes, and reduce costs. [The AI Blues]( From O'Reilly Radar - Insight A recent article in Computerworld argued that the output from generative AI systems, like GPT and Gemini, isn’t as good as it used to be. It isn’t the first... Administration of SQL Server [Msg 3023, Level 16, State 2]( Executing the following Database shrinkfile actibity and getting the error message use myDatabase DBCC SHRINKFILE (N'myDatabase_log' , 0, TRUNCATEONLY) [T-SQL Tuesday #178 – Recent Technical Issue You Resolved]( From Callihan Data Deepthi Goguri has our invitation for this month’s T-SQL Tuesday. Deepthi asks for stories on a recent technological problem we fixed. As always, to read the invite post, click... [Lesson Learned #508: Monitoring Wait Stats and Handling Large Data Set]( From Azure Database Support Blog Sometimes, we get asked how much data the client application is receiving from Azure SQL Database, along with the time spent and the number of rows returned. I would like... [T-SQL Tuesday 178 – Misleading data in Query Store]( From SQL Server Fast This post is for T-SQL Tuesday 178, hosted by Deepthi Goguri. She invites us to write about a recent technical issue we resolved. So let’s talk about the time... Azure SQL [CurrentBackupStorageRedundancy of my master database on Azure SQL Server]( From Azure Database Support Blog Symptoms: For your master database you might find different configuration such as Backup Storage Redundancy configured to be Local or Geo As this is not configurable property you might ask why... Azure SQL Managed Instance [Native JSON support now in preview in Azure SQL Managed Instance]( From Azure SQL Processing JSON data in Azure SQL Managed Instance just got more performant thanks to the new way JSON data is stored and handled. Now in preview for Azure SQL... Cloud - AWS [AWS Weekly Roundup: Amazon DynamoDB, AWS AppSync, Storage Browser for Amazon S3, and more (September 9, 2024)]( From AWS News Blog Last week, the latest AWS Heroes arrived! AWS Heroes are amazing technical experts who generously share their insights, best practices, and innovative solutions to help others. The AWS GenAI... Conferences, Classes, Events, and Webinars [PASS Summit late bird price bump - September]( The full program for the PASS Summit 2024 is now available! Explore the exciting lineup of sessions and meet the inspiring speakers who will be sharing their expertise this year. Discover the topics and experts and save your spot before the Standard pricing ends on September 18. [Join Me And @Kendra_Little At @PASSDataSummit For 2 Days Of SQL Server Performance Tuning Precons!]( From Erik Darling Data Last Year Kendra and I both taught solo precons, and got to talking about how much easier it is to manage large crowds when you have a little helper... [PASS Data Community Summit Prices Are About to Go Up!]( From Brent Ozar Unlimited Next Tuesday, pricing on the 3-day tickets for the PASS Data Community Summit goes up to $2,295. But if you register right now, plus use coupon code BRENTO24, it’s... Data Privacy, Compliance, and Governance [The Role of Data Security in Protecting Sensitive Information Across Verticals]( From Dataversity Data is the fuel that keeps the engine of any organization running efficiently. Its growing importance is becoming a frequent topic of conversation in boardrooms and strategy meetings. Companies... Data Science [An interesting result involving the Cauchy distribution]( From Statistical Odds & Ends If are independent variables and is a random vector independent of the ‘s with for all and , it is well-known that also has a distribution. It is also... [The location of ticks in statistical graphics]( From AllAnalytics Modern software for statistical graphics automatically handles many details and graph defaults, such as the range of the axes and the placement of tick marks. In the days of... Database Design, Theory and Development [Database Sharding: Strategies for Seamless Scaling and Performance Optimization]( Companies of all sizes and across industries are struggling to cope with an explosion of data never before seen in the short history of computing. As applications reach new levels of sophistication and become deeply interconnected, these companies find themselves increasingly overworked, overheated, and at their wits’ end, desperately trying to squeeze just a bit more performance and availability out of their aging database architectures. [Uniqueness, Keys, and Identity]( If you’ve ever had a traditional logic course, you’ll have run into “The Law of Identity” as the founding principle of all Western thought. It says that: “To be is to be something in particular; to be nothing in particular or anything in general, is to be nothing at all.” This usually gets expressed as “A = A” thanks to Ayn Rand’s Objectivism and credited to Aristotle. You want to dig a little deeper, however, it actually predates Aristotle’s laws of logic. [Design a really terrific Star Schema by interpreting an existing report]( Previously, I’ve written about tips for designing a really great report model from Agile user stories. Catch up on it here (Useful tips for designing a really great report model from Agile user stories). Now, I’d like to present a few ideas for designing a user-friendly, scalable and re-usable Star Schema by interpreting an existing report or report mock-ups. MDX/DAX [Context transition in DAX explained visually]( From Sqlbi A visual representation of the context transition in DAX makes it easier to understand how it works! Microsoft Fabric ( Azure Synapse Analytics, OneLake, ADLS, Data Science) [Purchasing Fabric Capacities as Reserved Instance]( From Data – Marc Last week, I had a situation in which a client wanted to purchase a reserved instance Fabric capacity. Me being me, I assumed it would be super straight forward... Performance Tuning SQL Server [In Memory Table Variables In SQL Server: Still Pretty Stupid]( From Erik Darling Data In Memory Table Variables In SQL Server: Still Pretty Stupid Thanks for watching! Going Further If this is the kind of SQL Server stuff you love learning about, you’ll... [Signs You Need Dynamic SQL In Your SQL Server Queries]( From Erik Darling Data Signs You Need Dynamic SQL In Your SQL Server Queries Thanks for watching! Going Further If this is the kind of SQL Server stuff you love learning about, you’ll... PostgreSQL [PostgreSQL Backups to Support Point In Time Recovery: Learning PostgreSQL with Grant]( The focus of your backup strategy should not be backups. Instead, you should be focused on restoring databases, because, after all, that’s what matters. Simply having a backup is fine. However, if you can’t restore your data from that backup, it doesn’t matter. On top of this is the ability to ensure you can restore your databases to a point in time, not simply to the last full backup. That ability requires a different approach to how you perform your backups. PowerPivot/PowerQuery/PowerBI [(Livestream Replay) The Cost of a Workaround in Power BI - with Injae Park]( From Havens Consulting DESCRIPTION From using SVGs or DENEB to create unique visuals, to page level security with RLS - workarounds are often necessary to overcome limitations and achieve desired functionalities in... [Migrate Power BI Dataflow Gen1 to Fabric Dataflow Gen2]( From RADACAD Unfortunately, there isn’t a migration tool to convert your Power BI dataflow (gen1) to Microsoft Fabric dataflow (gen2). If you have Fabric capacity licenses, it just makes sense to... [REFRAME in Power BI Direct Lake]( From RADACAD Power BI offers a new type of connection to Microsoft Fabric Lakehouse or Warehouse, called Direct Lake. The Direct Lake connection acts like DirectQuery and won’t need the data... Product Reviews and Articles [Forks in Flyway Database Development Work]( From Product learning – Redgate Software Database forking allows teams to multi-task, working on different strands of development in parallel. It also allows them to manage several 'variants' of a production database, such as for... [Redgate Clone Graduating Images]( From SQLServerCentral Blogs I wanted my own custom image in our internal Redgate Clone cluster, so I decided to make one. This is an overview of how this works. This is a... The... Product Upgrades and Releases [SQL Server Diagnostic Information Queries for September 2024]( From Glenn Berry Introduction These are my SQL Server Diagnostic Information Queries for September 2024, aka my DMV Diagnostic Queries. They allow you to get a very comprehensive view of the configuration... [Undercover Catalogue 0.4.5 Released – Database Module Bug Fix]( From SQLServerCentral Blogs Full documentation on the Undercover Catalogue can be found HERE We’ve spotted a bug in the Databases module where an unprintable ASCII character was being inserted at the beginning of... The... [Elastic pools for Azure SQL Database Hyperscale now Generally Available!]( From Azure SQL We are very pleased to announce General Availability (GA) for Azure SQL Database Hyperscale elastic pools (“Hyperscale elastic pools”).  Why use Hyperscale elastic pools?  Azure SQL Database is the preferred database technology... [Security Update for SQL Server 2019 RTM CU28]( From MS SQL Server Blog The Security Update for SQL Server 2019 RTM CU28 is now available for download at the Microsoft Download Center and Microsoft Update Catalog sites. This package cumulatively includes all... [Security Update for SQL Server 2016 SP3 GDR]( From MS SQL Server Blog The Security Update for SQL Server 2016 SP3 GDR is now available for download at the Microsoft Download Center and Microsoft Update Catalog sites. This package cumulatively includes all... [Security Update for SQL Server 2016 SP3 Azure Connect Feature Pack]( From MS SQL Server Blog The Security Update for SQL Server 2016 SP3 Azure Connect Feature Pack is now available for download at the Microsoft Download Center and Microsoft Update Catalog sites. This package... [Security Update for SQL Server 2017 RTM CU31]( From MS SQL Server Blog The Security Update for SQL Server 2017 RTM CU is now available for download at the Microsoft Download Center and Microsoft Update Catalog sites. This package cumulatively includes all... [Security Update for SQL Server 2017 RTM GDR]( From MS SQL Server Blog The Security Update for SQL Server 2017 RTM GDR is now available for download at the Microsoft Download Center and Microsoft Update Catalog sites. This package cumulatively includes all... [Security Update for SQL Server 2019 RTM GDR]( From MS SQL Server Blog The Security Update for SQL Server 2019 RTM GDR is now available for download at the Microsoft Download Center and Microsoft Update Catalog sites. This package cumulatively includes all... [Security Update for SQL Server 2022 RTM CU14]( From MS SQL Server Blog The Security Update for SQL Server 2022 RTM CU14 is now available for download at the Microsoft Download Center and Microsoft Update Catalog sites. This package cumulatively includes all... [Security Update for SQL Server 2022 RTM GDR]( From MS SQL Server Blog The Security Update for SQL Server 2022 RTM GDR is now available for download at the Microsoft Download Center and Microsoft Update Catalog sites. This package cumulatively includes all... [SDU Tools v24 is now available for download]( From The Bit Bucket Just a heads-up that v24 of SDU Tools went out to SDU Insiders last week. If you haven't used SDU Tools, they are just a large library of functions... Python [Why I’m Switching to Polars]( I recently decided to switch from Pandas to Polars for my Python projects that use dataframes. I came to this decision while taking a workshop on Polars last week: I found its syntax to be so intuitive that I couldn’t justify continuing to try to get “better” at Pandas, despite Pandas being the more established library. The fact that Polars is faster (it’s main selling point) was, surprisingly, not a factor in my decision. R Language [How to Use lapply() Function with Multiple Arguments in R]( R is a powerful programming language primarily used for statistical computing and data analysis. Among its many features, the lapply() function stands out as a versatile tool for simplifying code and reducing redundancy. Whether you’re working with lists, vectors, or data frames, understanding how to use lapply() effectively can greatly enhance your programming efficiency. For beginners, mastering lapply() is a crucial step in becoming proficient in R. [Please Version Data]( An important goal of our Win Vector LLC teaching offerings is to instill in engineers some familiarity with, and empathy for, how data is likely to be used for analytics and business. Having such engineers in your organization greatly increases the quality of the data later available to your analysts and data scientists. This in turn expands what is possible in prediction and forecasting, which can lead to significant revenue opportunities. SQL Server on Linux [0A000086 and command not found with latest mssql-tools for Linux]( In this post I cover the 0A000086 and “command not found” errors that you might encounter with the new version of SQL Server command-line tools, namely sqlcmd and bcp, for Linux in both VMs and containers. Security News and Issues [Microsoft Is Adding New Cryptography Algorithms]( From Schneier on Security Microsoft is updating SymCrypt, its core cryptographic library, with new quantum-secure algorithms. Microsoft’s details are here. From a news article: The first new algorithm Microsoft added to SymCrypt is called... [Australia Threatens to Force Companies to Break Encryption]( From Schneier on Security In 2018, Australia passed the Assistance and Access Act, which—among other things—gave the government the power to force companies to break their own encryption. The Assistance and Access Act includes... T-SQL and Query Languages [A Difference Between ISNULL And COALESCE You Might Care About In SQL Server]( From Erik Darling Data A Difference Between ISNULL And COALESCE You Might Care About In SQL Server Thanks for watching! Going Further If this is the kind of SQL Server stuff you love... [Making Temporal Databases Work. Part 3: Saving Data]( From Simple Talk The temporal database is a database that can keep information on time when the facts represented in the database were, are, or will be valid. We briefly described major... [Getting New and Old Columns to a Table]( From Sherpa of Data In my last post, we worked out how to change the data type of a field if it already existed and was found to need changing. This time, I... Tech News [Oracle Cloud Growth Could Cement Wall Street's Next AI Favorite]( From IT Pro - Microsoft Windows Information, Solutions, Tools Oracle's cloud infrastructure growth, driven by AI demand, positions the company for greater investor confidence and market value, despite its underdog status among cloud providers. [Tech Sector Layoffs 'Rival Worst Spikes' Over Time]( From IT Pro - Microsoft Windows Information, Solutions, Tools The surge of tech sector layoffs in the last two years could have ripple effects across the economy and investment market, according to RBC Capital Markets. The Lighter Side [CodeSOD: Unique Emails]( From Daily WTF Once upon a time, a company started a large React application. Like so many such projects, the requirements were vague and poorly understood, the management didn't have any sort... Vector Databases [Vector Databases – Part 4 – Vector Indexes]( In this post on Vector Databases, I’ll explore some of the commonly used indexing techniques available in Databases. I’ll also explore the Vector Indexes available in Oracle 23c. Be sure to check that section towards the end of the post, where I’ll also include links to other articles in this series. XML, XPATH and XQUERY [Working with XML data in SQL Server]( From MSSQL Tips Working with XML and T-SQL could be a challenge if you do not know how to access the XML data. In this article, we look at what you need...  [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -