SQL Server corruption, boxplots, and more in Database Weekly (2022-02-26)

Database Weekly for February 26, 2022 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  Another look at table variables Becoming overly enthusiastic about a new SQL Server feature can backfire if you don’t do some testing. One example is the table variable introduced with SQL Server 2000. At the time, there was a myth that table variables would always perform better than temp tables with the reasoning that, by definition, variables are stored in memory. It was common knowledge that temp tables are stored in tempdb, but many assumed that a variable would not be. It was easily proven that [table variables are stored in tempdb]( just like temp tables. I consulted at a couple of shops where developers had fully embraced the use of table variables to store intermediate results in stored procedures. At one customer, table variables were so prevalent that there must have been an entire project devoted to replacing temp tables with table variables in stored procedures regardless of how many rows involved. (There are other, better uses for table variables such as table-valued parameters and use in table-valued functions.) Why are there some performance issues with table variables? While certain indexes can be included in the table variable definition due to primary keys and unique constraints, there are no distribution statistics on table variables. The optimizer estimates that all table variables have a cardinality of one row which could negatively affect the plan, especially when thousands of rows are involved. Even though indexes could be in place, they don’t matter with just one estimated row. In 2014, Microsoft added the ability to create [memory-optimized table variables](. Finally, table variables could truly live in memory, but to do so requires some work on the SQL Server instance. There must be a filegroup in place for MEMORY_OPTIMIZED_DATA, and you must create a user-defined table type for each possible table variable schema. Assuming you have those prerequisites in place, you just need to declare a variable of the type and then populate it with rows. It’s easy if you have the types defined, but that’s going to take some planning! Finally, in 2019, Microsoft took care of the cardinality issue as part of the [Intelligent Query Processing]( features. This new feature is called “table variable deferred compilation.” Basically, the optimizer adjusts the plan based on the actual table variable row counts. There are still no statistics, but it’s more likely that it will choose a more appropriate plan based on the actual number of rows. Table variables were enthusiastically embraced by lots of folks as the answer to many performance problems. The reality was quite different. Luckily, Microsoft has improved this feature over time, and it’s worth taking another look.  Kathi Kellenberger [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 [Flyway Branching Walkthrough]( We'll step through the process of using Flyway Teams to support database branching and merging, where the team split the development effort into isolated, task-based branches, and each branch has its own development database. [Flyway Database Drift and How it Happens]( Flyway's approach to database migrations is based on strict versioning, but there is a limit to what a single process can do to prevent 'drift'. This article explains how drift can happen, and why you also need source control and external processes that log changes, to prevent it. [Implementing a Database Versioning System]( Database versioning brings more control to team-based database development and helps avoid many of the errors that often creep into database delivery. This article explains all the requirements of a semantic versioning system for databases, from specifying the format of the version numbers, to deciding where to store them and how to process and compare them. [Why you don’t just buy tools to make DevOps happen]( Matt Gordan is next in the hot seat. We discuss how Matt keeps the lights on while overseeing many data related projects and how his team know the more they are plugged into projects, the better support the can offer. [What’s new in SQL Monitor? Tagging and Current Activity Page (minor release v12.1)]( We've released a minor version of SQL Monitor, v12.1, that includes two exciting new features: ‘Tagging’ and a ‘Current Activity’ page. In a nutshell: tags allow you to record aspects of a server so that you have information at your fingertips, and the Current Activity page directly queries the monitored server when you open it to get the most up-to-date information possible. It’s all part of our commitment to making SQL Monitor the best monitoring tool out there! Administration of SQL Server [A guide to restore a TDE database backup to another SQL Server Instance]( From SQLServer-DBA.Com Restoring a TDE enabled backup is straightforward but requires some certificate preparation steps [DBCC CHECKDB: Look for SQL Server Corruption]( From StraightPath Solutions SQL Blog A monster’s worse fear is being found. – Riche... [PREEMPTIVE_OS_FILEOPS Waits and Filestream Restores]( From The SQL Herald (Joey D'Antoni) We had a case over the weekend where our automated... [Meme Week: SET DEADLOCK_PRIORITY HIGH]( From Brent Ozar Unlimited Hey, this seems harmless. Seriously, don’t SET DEADLOCK_PRIORITY HIGH. Even setting it to low can be a bad idea. I’ve heard people say, “Well, I’ve got a big process... [Recipe 2: Writing Extended Events to a table]( From SQLServerCentral Blogs Welcome to the second recipe of this Extended Even... [Migrate tables with a foreign key to memory-optimized tables of SQL database]( From SQLShack In this article, we are going to learn how we can migrate the tables with a foreign key to memory-optimized tables of the SQL database. In my previous article,... [Impact on TDE encryption in progress on SQL Server restart]( From SQLServer-DBA.Com Monitoring SQL Server encryption when there is a SQL Server service restart [SQL Server script to rebuild all indexes for all tables and all databases]( From MSSQL Tips In this tip we look at a simple script you can use to rebuild all SQL Server indexes for all specified databases. [How to Read Log File in SQL Server using TSQL]( From MSSQL Tips In this article we look at how to query and read the SQL Server log files using TSQL to quickly find specific information and return the data as a... [Run same command on all SQL Server databases without cursors]( From MSSQL Tips In this article we cover how to run the same command against all SQL Server databases using sp_MSforeachdb along with several examples of how to use it. [Identify SQL Server TCP IP port being used]( From MSSQL Tips In this article we look at 3 different ways to find the TCP IP port that SQL Server is using for an instance. [Fastest way to Delete Large Number of Records in SQL Server]( From MSSQL Tips Performance Considerations | Large Tables | Delete Operations - In this article we look at different ways to most effectively delete large amounts of data from a SQL Server... Azure DevOps [Check if File Exists Before Deploying SQL Script to Azure SQL Managed Instance in Azure Release Pipelines]( From SQLServerCentral Blogs I have been in Azure DevOps pipelines a lot recent... Azure SQL [Export Azure SQL Database | Advanced Scenarios]( From Azure Database Support Blog Export Azure SQL Database is a commo... Computing in the Cloud (Azure, Google, AWS) [Logging Into an AWS RDS Instance]( From Callihan Data We recently took a look at creating a MySQL database with AWS RDS. Today, let’s go through the steps of logging in. Creating Our Connection We’ll use MySQL Workbench... Conferences, Classes, Events, and Webinars [SQL Monitor: Monitor your performance and availability]( Managing your entire SQL Server estate, on premises, in the cloud or a hybrid, with instant problem diagnosis, intelligent and customizable alerting has never been more vital. Discover how Redgate’s SQL Monitor enables all this from a single pane of glass. [Can Continuous Compliance Automation transform data protection in your organization?]( Redgate's Security Lead, Rob Chipperfield, will be joined by a panel of peers to discuss this mindset change of Continuous Compliance Automation, and the impact it can have on the daily operations, and business demands of your organization. DMO/SMO/Powershell [Copy to Multiple Destinations with PowerShell]( From The lonely Administrator In honor of today, 2/22/2022, I thought I’d shar... Data Mining / Data Analysis [what is a boxplot?]( From Storytelling with Data A boxplot—sometimes called a box and whisker plot... ... Database Design, Theory and Development [Primary Keys in SQL Server]( From SQL Server – {coding}Sight Across all major RDBMS products, Primary Key in SQ... ETL/SSIS/Azure Data Factory/Biml [Azure-SSIS Supports SSIS 2017 Only]( From AndyLeonard.blog() With the recent public preview release of Synapse-... MDX/DAX [SIN, COS, ASIN, ACOS, TAN, ATAN, COT, ACOT – DAX Guide]( From Sqlbi SIN: Returns the sine of the given angle. https://... Oracle/PostgreSQL/MySQL/other RDBMS [SQL Variables for Queries and Stored Procedures in SQL Server, Oracle and PostgreSQL]( From MSSQL Tips Learn about the differences in SQL Server, Oracle and PostgreSQL on how to use variables for ad hoc queries and stored procedures. [The effects of NULL with NOT IN on Oracle transformations]( From Simple Talk IN and NOT IN subqueries are frequently used in Or... Performance Tuning SQL Server [SQL Query Performance Tuning Tips]( From MSSQL Tips In this article we look at things you should know to help tune and optimize your SQL Server queries. [Understanding the SQL Server NOLOCK hint]( From MSSQL Tips In this tip we look at the SQL Server NOLOCK hint and how it impacts query results, both good and bad. PowerPivot/PowerQuery/PowerBI [TSQL Table-Valued Functions And Dynamic M Parameters In Power BI DirectQuery]( From Chris Webb's BI Blog My favourite – and it seems many other people’... [Create a Sub-Column from a single field in Power BI]( From MSSQL Tips In this article we look at creating a Power BI gro... [Power BI Embedded: Stress Testing & Capacity Planning]( From SQLServerCentral Blogs When deciding if Power BI is the correct reporting... Python [How to Define and Call Functions in Python]( From MSSQL Tips In this article we look at how to create Python function and various aspects of creation and use to simplify coding. SQL Server Security and Auditing [The denial-of-service attack is coming from inside the house]( From Born SQL A short post this week. While I was helping some friends recently, we experienced a curious thing where as soon as an application was started up, it was immediately... T-SQL [Doing Something with the REPLACE function]( From Sherpa of Data Most of us have used the REPLACE function several ... [The basic T-SQL SELECT statement]( From Simple Talk The SQL language is used across many relational da... [SQL Queries Tutorial]( From MSSQL Tips If you are just getting started with writing SQL Server queries take a look at this article for examples to select, insert, update and delete data. [Getting The Top Value Per Group In SQL Server: Row Number vs. Cross Apply]( From Erik Darling Data Introductions There are many ways to express queries in SQL. How different rewrites perform will largely be a function of: You not doing anything ridiculous Queries having good indexes in place The optimizer... [Generate a parameter list for all SQL Server stored procedures and functions]( From MSSQL Tips In this tip we look at a simple query you can use to get a list of parameters for all stored procedures and functions in a database. [SQL LIKE Statement]( From MSSQL Tips Learn how to use the SQL LIKE operator to search for various string patterns within SQL Server data. This covers several examples of how the SQL LIKE statement can... [Migrating Time Series Data to SQL Server from Yahoo Finance and Stooq.com]( From MSSQL Tips Learn how to use time series data in SQL Server by using financial security data from Yahoo Finance and Stooq.com. [Various Meanings of SQL’s PARTITION BY Syntax]( From Java, SQL and jOOQ. For SQL beginners, there’s a bit of an esoteric syntax named PARTITION BY, which appears all over the place in SQL. It always has a similar meaning, though in... [SQL Server Stored Procedure Input Parameter, Output Parameter and Return Value]( From MSSQL Tips In this tip we look at how to write SQL Server stored procedures to handle input parameters, output parameters and return codes. Tools for Dev (SSMS, ADS, VS, etc.) [Azure Data Studio Intellisense]( From Scary DBA (Grant Fritchey) I recently saw a question about the Azure Data Stu...  [RSS Feed]([Twitter]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -