Verifying Trees (2023-01-20)

SQLServerCentral Newsletter for January 20, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Verifying Trees]( - [SQL Server point in time recovery]( - [From the SQL Server Central Blogs - New year, new role, and new book]( - [From the SQL Server Central Blogs - Creating a PostgreSQL Docker Container with a Volume on Windows]( - [Pro Encryption in SQL Server 2022: Provide the Highest Level of Protection for Your Data]( Question of the Day - [More ANSI_PADDING Tables]( The Voice of the DBA  Daily Coping Tip Take a different route today and see what you notice I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. Minimize Typing It couldn't have happened to a worse set of people, and I'm glad it did, but I'll still take a positive lesson out of this. A [hacker sent a typo in a command to a botnet]( and lost control. That's kind of funny, and I'm glad it happened. The less botnets, the better, IMHO. The coding in this software didn't have good error handling, which is a lesson in and of itself. Overall it seems many developers do a good job of error handling, but I still encounter more pieces of software that allow problematic input than I'd like. While we don't have great error handling in T-SQL, you can make some checks, and you should. That's not the big lesson for me. The bigger lesson is that we ought to do less typing in much of our daily work. The last decade has had me work often with companies looking to implement DevOps software pipelines and driving automation wherever possible. We want to limit the chances humans can make mistakes, which means we want to limit their typing. Or clicking, as is the case in much of today's software. Instead, we want to ensure all our code or commands are reviewed by someone, they are submitted to an automated pipeline, and they are validated or practiced on some system ahead of production execution. We ought to do this for no other reason than we want to ensure we have an audit trail, but preventing typos is good as well. I don't know if you can completely get away from typing, but we can reduce the number of human error mistakes if we include some static code analysis (including for commands), some peer review, some sort of unit testing, and pre-production deployment. A lot of mistakes I find are fairly simple ones. Common human error that occurs because we're busy, we're stressed, we're moving too fast, or we just miss something. Use the computer for one of its strengths. Tediously checking the simple things that humans do wrong. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Verifying Trees]( Glen Cooper from SQLServerCentral In this article, Glen Cooper provides code that allows you to process a tree, or undirected graph of data. Lots of hierarchies fall into this category and the ability to process them is valuable in many reporting situations. [External Article]( [SQL Server point in time recovery]( Additional Articles from MSSQLTips.com In this article, we look at how to do a point in time restore using TSQL and SSMS to recover a database to a particular point in time. [Blog Post]( From the SQL Server Central Blogs - [New year, new role, and new book]( James Serra from James Serra's Blog Happy new year to everyone! As I enter my 9th year at Microsoft, I have switched roles, and am now an Industry Advisor in Federal Civilian, helping our Federal... [Blog Post]( From the SQL Server Central Blogs - [Creating a PostgreSQL Docker Container with a Volume on Windows]( Steve Jones - SSC Editor from The Voice of the DBA This post looks at how to set up a PostgreSQL container on Windows using Docker for Windows. I’ve seen a few posts, but I had to cobble together some... [Pro Encryption in SQL Server 2022]( [Pro Encryption in SQL Server 2022: Provide the Highest Level of Protection for Your Data]( Additional Articles from SQLServerCentral This in-depth look at the encryption tools available in SQL Server shows you how to protect data by encrypting it at rest with Transparent Data Encryption (TDE) and in transit with Transport Level Security (TLS). You will know how to add the highest levels of protection for sensitive data using Always Encrypted to encrypt data also in memory and be protected even from users with the highest levels of access to the database. The book demonstrates actions you can take today to start protecting your data without changing any code in your applications, and the steps you can subsequently take to modify your applications to support implementing a gold standard in data protection.   Question of the Day Today's question (by Steve Jones - SSC Editor):  More ANSI_PADDING Tables I run this code to create a table and insert rows. What are the results that I see from the query? SET ANSI_PADDING OFF GO CREATE TABLE dbo.ANSIPADDINGOFF(charcol CHAR(20), varcharcol VARCHAR(20)) GO INSERT dbo.ANSIPADDINGOFF (charcol, varcharcol) VALUES ('c test2','v test2 ') GO SELECT '[' + a.charcol + ']', '[' + a.varcharcol + ']' FROM dbo.ANSIPADDINGOFF AS a GO Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by tomaz.kastrun) Rounding in Python What is the value of variable val3 (print command) in this Python code? import math as m val1 = round(m.sqrt(650.25),-1) val2 = str(val1) val3 = val2[-3] print(val3)  Answer: 0 Explanation: By decomposing the first step (calculating val1) we calculate the square root of 650.25, which is 25.5. The round function in Python works as Bankers rounding, making the 25.5 rounded to 26. For the reference, 24.5 would be rounded to 24. The bankers rounding works that numbers x.5 (e.g.: 1.5, 2.5, 3.5, 5.5, etc) are rounded to the nearest rounded to nearest even number. Since we are rounding with value -1, the result is not 26.0, but 30.0. Here is an example, how does the digit parameter works. When negative, it will round to whole numbers, when positive, it will round the decimal numbers. print(round(543.5, -1)) # Result: 540.0 print(round(543.5, -2)) # Result: 500.0 print(round(543.5553, 2)) # Result: 543.56 Converting to val2 is changing the number into string, and the result is still 30.0. The variable val3 is getting the 2nd (second) character from the end. The value in the string for position -2 is 0. number : 3 0 . 0 position:-3 -2 -1 0   [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 - Administration [Troubles with in memory OLTP performance degradation]( - I started with a new company recently and they use in memory OLTP which is something I haven't used before so I'm excited to try a new technology.  I've been there about a month now and I'm reading/watching everything I can on the feature but they're seeing some strange behavior that I could use some […] SQL Server 2016 - Administration [Job History says running, but Job Activity says idle]( - Hello experts, I'm hoping to get help with a strange issue. I got a request to stop a long-running SQL Agent job. The client correctly sees that the job history says the job is running. But when I checked the Job Activity Monitor, it says the job is idle and last succeeded at such-and-such a […] SQL 2012 - General [60 Second Rolling Avg With Different Starting Points]( - Hi, I have a table with columns: jobId, timeStamp (for duration per jobId) and tempData (see attachment). I have created a query that calculates 60 second rolling average by jobId. (you can see it at the end of the post) Where I am struggling is adding two additional rolling averages. Both of them would be […] SQL Server 2019 - Administration [SQL Server (Express) installation issue]( - Please be gentle.. newbie here ! I'm creating an install program (Using Advanced Installer, but I don't think that's relevant) to install SQL Express. I'm trying to do this as a quiet install with no user intervention. It's failing with a request for a password on the SQL Server Browser. I cant see how to […] [SQL Server 2019 or 2022]( - Hi Experts, We are planning to upgrade our SQL Servers from 2014 to higher version.Which is the best suitable upgrade 2019 or 2022, please advise.   Regards SQL Server 2019 - Development [Existing scalar function within a trigger is not found]( - Calling an existing scalar function within a trigger which is not found within the trigger (Msg 4121, Level 16, State 1) The error is the following: Msg 4121, Level 16, State 1, Procedure Sales.uTu_Customer, Line 12 [Batch Start Line 44] Cannot find either column "Hashing" or the user-defined function or aggregate "Hashing.CreateJsonInputForSha256SalesCustomer", or the name […] [Column name with text and column name]( - Hello all. I hope everyone's ok I have this query: SELECT cont, type, month01, month02, month03... FROM acum Where year=2022 I'd like that for each month it shows a text and the year, like Jan-2022, Feb-2022 and so on. Something like SELECT cont, type, month01 AS CONCAT( 'Jan-',year) , month02 AS CONCAT( 'Feb-',year) ... FROM […] [Stored procedure for top 1 from multiple tables and update 1 table in 1 query]( - I have two tables in this format below; Table Transfer Id Stage Amount TransactionNumber bnumber Table User Id Bnumber FirstName FirstLastName My aim is to select top 1 from these tables based on criteria and then update that record in the Transfer table, all in one query. I have struggled and came up with the […] General Cloud Computing Questions [Suggest cloud provider for SQL]( - I am a MERN stack developer and we need to work with SQL for a project. Can anyone suggest a good FREE cloud SQL server having low storage with which my Node backend can connect using an URI (Like Atlas for MongoDB). Our backend developer is left in the dark with this sudden and urgent […] Azure Data Factory [Data factory question]( - Upon my adventures of Azure data factory and all the wonders it can do I have stumbled upon a issue that Microsoft support has no answer as of yet. Long story short, I am trying to ingest data from a storage blob in another tenant. That customer will only allow us to connect to that […] Reporting Services [Behaviour of a link in a report in ReportViewer]( - Hi. I have an issue with a report that is about 10 years old. The report is running on an ASP.net application that is running off IIS and SQL Server 2017 on a Windows Server 2018 Server machine. The Asp.net application has a Report viewer control to view reports. There are links in the report […] SSRS 2014 [Out of memory (error code)]( - hi I have a problem. I have created a report which shows the data from table in SQL Server (just a "select * from where catid in (..)). There is a parameter where I can choose if it should show MAIN categories, OTHER categories or ALL categories - and depend on choose - the list […] [Out of memory (error code)]( - hi I have a problem. I have created a report which shows the data from table in SQL Server (just a "select * from where catid in (..)). There is a parameter where I can choose if it should show MAIN categories, OTHER categories or ALL categories - and depend on choose - the list […] SQL Server 2005 Integration Services [SSIS Connection Manager OLEDB Issue]( - Hi Team, this is first time I am posting question on forum, I am able to connect to DB using SSMS and windows authentication, however when I am trying to connect in SSIS for new OLEDB connection it seems to give me error as below. Test Connection failed because of an error in initializing provider. […] SQL Server 2022 - Development [Distance between multiple locations]( - What would be the best approach when establishing the distance between points. By that I mean I have lets say 2 fixed Building locations and 8 moving Cranes(Container lifting Crane). I have position locations every minute of the cranes, for all 8 of them. I can establish the distance with the Haversine formula in an […]   [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. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -