Exploring Time Travel and Row Level Concurrency: Data Engineering with Fabric (2024-10-02)

SQLServerCentral Newsletter for October 2, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Exploring Time Travel and Row Level Concurrency: Data Engineering with Fabric]( - [Microsoft Fabric and PySpark: Coding tricks to improve your solutions]( - [Casino night is back at PASS Summit 2024!]( - [From the SQL Server Central Blogs - Microsoft Purview GA menu’s]( - [The Definitive Guide to Azure Data Engineering: Modern ELT, DevOps, and Analytics on the Azure Cloud Platform]( Question of the Day - [Memory-Optimized Tables Memory]( The Voice of the DBA  Cleaning Up Your Database How many of you have objects in your database that aren't being used? What about something in a schema with a _old in the name? Or _2 or _3 or _delete? There is a lot of old, deprecated stuff I see in production databases. In fact, I've been somewhat amazed as I work with clients that many of the scripts we can build from a database with SQL Compare won't actually execute on an empty database because the script is full of broken code. I also find plenty of DBAs that want to clean things up, but they don't. Sometimes they're afraid they'll break something, which is certainly possible. Sometimes they can never find the time. Often they might ask a manager, who usually says this isn't important and don't bother. Is it worth it to clean up your databases? [Brent says no]( for old code. I say maybe for tables and code. For a lot of code, Brent is right, your boss doesn't care and it doesn't necessarily help you. After all, it's in production now, and if it's being used, you're going to just create problems with a DROP. Where is the business value for removing old code (assuming it isn't being used)? What benefits do your clients get? Not you being happier there are less objects, but what is the business benefit. That's the key. Is there a business benefit. What I'd say is that if you have broken code, it needs to be removed. Because this does impact your software development process, especially when trying to match lower environments. For broken stuff, save the code in your VCS (you do version control database code, right?) and then delete this stuff from prod. It's broken. Or fix it. For tables, I would want to get rid of old tables as well. Why? Well, this is real costs in storage and potential reading of old data. If we moved data to table_old and someone decided they needed to read this for a report at the time, they might still be reading old data. I'd first rename these objects as object_delete_date with the date being a month away. Then I'd set a reminder for that date. On that date, bcp out the data, then drop the table. Period. Two other things. First, make sure you know how to recreate the table (see the VCS comment above) and bcp in the data. Two, this is low-priority work. If you want to clean the database, know this is a long term, baby step process that will take months or years, and may never end. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Exploring Time Travel and Row Level Concurrency: Data Engineering with Fabric]( John Miner from SQLServerCentral Learn how updates and concurrency can work in Fabric with Delta Parquet data. [External Article]( [Microsoft Fabric and PySpark: Coding tricks to improve your solutions]( Additional Articles from SimpleTalk PySpark has some unconventional syntaxes which provide power to the development process, making it easier. [Technical Article]( [Casino night is back at PASS Summit 2024!]( Additional Articles from PASS Back by popular demand, casino night from SQL Server Central will return to PASS Summit this year. Casino night will take place on November 5 after the welcome reception. Tickets will be allocated at random via a lottery. [Blog Post]( From the SQL Server Central Blogs - [Microsoft Purview GA menu’s]( James Serra from James Serra's Blog The new data governance features in Microsoft Purview are now being made generally available as they are gradually rolled out across various regions. You can view the deployment schedule... [The Definitive Guide to Azure Data Engineering: Modern ELT, DevOps, and Analytics on the Azure Cloud Platform]( [The Definitive Guide to Azure Data Engineering: Modern ELT, DevOps, and Analytics on the Azure Cloud Platform]( Site Owners from SQLServerCentral Build efficient and scalable batch and real-time data ingestion pipelines, DevOps continuous integration and deployment pipelines, and advanced analytics solutions on the Azure Data Platform. This book teaches you to design and implement robust data engineering solutions using Data Factory, Databricks, Synapse Analytics, Snowflake, Azure SQL database, Stream Analytics, Cosmos database, and Data Lake Storage Gen2.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Memory-Optimized Tables Memory Memory-Optimized tables should fit in memory to work efficiently. Aside from the data size, how much overhead is required for each row? 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) Migration Assistant Issues If I run the Data Migration Assistant to check for an upgrade from SQL Server 2008 to SQL Server 2022, which categories of issues are pointed out as potential items that can affect an upgrade? (choose 3) Answer: Behavior Changes, Breaking Changes, Deprecated Features Explanation: The issues pointed out by the Data Migration Assistant are in these categories: - Breaking changes - Behavior changes - Deprecated features There are new feature recommendations in the security area, but these are not pointed out as issues. Ref: Overview of the Data Migration Assistant - [ [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 [Error 28054 related to master key root cause and solution required]( - I am getting the following error in SQL Server Error Log randomly on few days. I want to know the root cause for this error so that we can decide on applicable solution for this particular error. Service Broker needs to access the master key in the database. Error code:32. The master key has to […] [How to attach Database created in higher version to lower version of SQL Server]( - I have attempted to attach a database created in a higher version of SQL Server to a lower version, but I encountered errors. I also tried the backup and restore method, which resulted in errors as well. According to the following link, I explored several options but was unsuccessful: 1. Generate Scripts Wizard in […] SQL Server 2016 - Administration [application not connecting to secondary]( -  Hi the application which has the connection string of the listener configured connects to node S01 but when failed over to S02 it does not connect there are no applicable error in the SQL logs and windows logs please let me know   Development - SQL Server 2014 [Query to return all data that has same site, section and date]( - I have a query Select Id, WOCode, Site, Section, CompletionDate from work orders where type = 'BRE'. I want to be able to return list of all WOCode that has same Site,Section and CompletionDate. The date field will be date only with no time SQL Server 2019 - Administration [sql monitoring tool]( - Has anybody had any experience with Nagios as a SQLServer monitoring tool. What were the pros and cons you had faced while using it. Please advise. [Failed restore database]( - Hi All , I have issue restoring small database . SQL version : SQL 2014 In the server , At first I took a backup of database ( bak file ) and compressed it . Then I copied ( standard copy ) to my local laptop from server , after that I copied back to […] SQL Server 2019 - Development [max and min dates]( - Hi I am trying to get the first and last days for the following where date_next_compare is less than 28 but having more than that would mean the max date for that range. Sorry if isn't clear. I currently have this:  I want to see two rows like below. I tried doing min and […] [Pivot Mistake]( - Hi all, I have this query where I make a pivot to extract the data segmented by Hours: select ‘Amount’ as Amount, [0] ,[1] ,[2] ,[2] ,[3] ,[4] ,[5] ,[6] ,[7] ,[8] ,[9] ,[10] ,[11] ,[12] ,[13] ,[14] ,[15] ,[16] ,[17] ,[18] ,[19] ,[20] ,[21] ,[22] ,[23] from (select sum(efeneg) ‘Amount’,left(hormsg,2) ‘Time’,left(hormsg,2) ‘Time’,left(hormsg,2) ‘Time’. from […] [open query via linked server to db2 is showing terrible performance]( - Hi this post is mostly about cross server concepts vs a query being sent to and run on the target server and then the data simply coming back over the network. but any feedback is appreciated. we have a sql server linked server thru which the db2 erp query (view) shown below is sent to […] General [What is Azure Data Studio or GitHub asking me?]( - I have an Azure Data Studio question (or maybe it's a GitHub question, I'm not sure), but I couldn't find a forum here to ask it. So, please excuse my asking it here. I am busy writing a T-SQL script, using Azure Data Studio. I committed my changes locally, then I tried pushing them to […] [How do data visualization consultants overcome the challenge of integrating?]( - Integrating multiple data sources is challenging due to differences in data formats, structures, and quality. Data visualization consultants must first establish data connections and ensure consistent data formatting. This often involves using ETL (Extract, Transform, Load) processes to clean and combine data. They must also handle data refreshes and ensure that integrated data is up-to-date. […] SQL Server 2022 - Administration [SQLExternalMonitoring what is it in sp_who2 result]( - If I run sp_who2, one of item in the list is showing using program SQLExternalMonitoring , what is it? it uses quite a bit cpu and ram. 70 sleeping NT AUTHORITY\SYSTEM SNOCSMASQP01 . master AWAITING COMMAND 170846212 57458731 09/26 10:31:41 SQLExternalMonitoring 70 0  Thanks, [Linked Server to SAP HANA database.]( - Greetings all. It's been a very long time, but I'm back. Does anyone have experience with setting up a linked server to a SAP HANA database? I have hanaclient-2.8.20-windows-x64 installed and a DSN created using the HDBODBC driver that comes with the client install. I am able to use the DSN to connect with generic […] [SQL job & SSIS package excution]( - Hi SQL experts, We have installed SQL Server with 2022 and using this as SSIS server. SSIS DB is the only one database on this server. We deployed a SSIS package, which is scheduled to run every 1 hr through SQL agent job. The job is failing sometimes & running successfully other times. The job […] SQL Server 2022 - Development [SQL Query Optimizer Supporting Parameterized Query and WHERE Clause Optimization]( - I am looking for a SQL query optimization tool that can enhance my query execution times. I have already tried EverSQL and SQL Tuning, but they are not meeting my requirements for the following reasons: 1. Parameterized Queries: Both tools seem to struggle with parameterized queries and do not optimize them correctly. 2. WHERE Clause […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -