Recursive CTE calculations (SQLServerCentral 11/19/2018)

[SQLServerCentral - www.sqlservercentral.com]( A community of more than 1,600,000 database professionals and growing Featured Contents - [Recursive CTE calculations]( - [What we learnt at PASS Summit Webinar]( - [What Is SQLPSX?]( - [Ethics in Data Science and Artificial Intelligence]( (From the SQLServerCentral Blogs) - [A New Video - Create a DB Executor Role]( (From the SQLServerCentral Blogs) The Voice of the DBA Break Problems Down Recently I was pouring over a few questions in the forums and noticed something that I would think is obvious, but apparently it isn't so for other people. A poster was attempting to run a query across multiple databases and needed help structuring code to work in the context of each database. The poster was going back and forth a few times with others that were attempting to help them, struggling to get suggestions implemented into their code. That's typical, as someone that needs an answer often doesn't understand the solution and is learning. In this case, I found that the original poster was losing some of their troubleshooting skills in the process. Years ago I read a piece on [Simple Talk]( from Kathi Kellenberger. She is now the current editor of the site, but at that time she was just a SQL Server MVP and expert that was looking to help others learn more. The article was called [Solving Complex T-SQL Problems, Step by Step](, and it stuck with me. There wasn't any knowledge that I didn't know, but it was an organized explanation of the Kathi's steps that I found refreshing. This was a nice reminder to me to not move too fast, but ensure that I really understand what the problem is and work in stages. I've certainly been guilty at times of moving too fast and glossing over some details. That might be fine in some cases, but if I produce code that's doing that, likely I (or someone else) is going to be rewriting code later. That's not what we want to do, especially if this is a logical error. I might understand some less than optimal code from a performance perspective, but there shouldn't be logical errors. In this case, the poster had incorporated some changes into code and kept running the entire script, getting bad results. They even started to pinpoint a few areas where data wasn't correctly being returned, but couldn't solve the issue. This was a somewhat complex script, and it could be easy to miss some mistakes. This was also a dynamic SQL script, which often means that we have to extrapolate from our code to understand what the engine will actually execute. There's often a simple solution to help you break the problem down: use PRINT. I've done this for most of my career, in many languages. Even today, I sometimes use this in PoSh to ensure that I'm not missing something being done. Watch windows and other debugging tools are great, but they sometimes are more difficult to understand when there are long strings of data. Print often simplifies the process. No matter what tools, languages, problems, etc. you are working on, breaking things down is often the best way to tackle a complex task. You might be surprised how much clarity this can bring to a tough problem. Even if you need to ask for help, having a list of things you've tried, and some data on the results is very much appreciated by those you ask. That way they know what you've tried and what to recommend. Steve Jones from [SQLServerCentral.com]( Join the debate, and [respond to today's editorial on the forums]( --------------------------------------------------------------- The Voice of the DBA Podcast Listen to the [MP3 Audio]( ( 3.9MB) podcast or subscribe to the feed at [iTunes]( and [Libsyn](. [feed]( The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. ADVERTISEMENT [RGUni]( Redgate University Self-paced online training courses with easy to follow classes on getting started, exploring advanced features, and making the most of Redgate products. Learn the fundamentals, best practices, and top tips from the experts - Redgate’s Microsoft Data Platform MVPs and engineers. [Start Learning]( [SQL Change Automation]( CI/CD for your SQL Server database Feeling the pain of managing and deploying database changes manually? Redgate SQL Change Automation completes your database delivery process by building, testing, and deploying the database changes you and your team check into version control. [Try it free]( Featured Contents  [] [Recursive CTE calculations]( Huub Loete from [SQLServerCentral.com]() Reusing the previous value of a recursive CTE to construct the new value [More »]( ---------------------------------------------------------------  [] [What we learnt at PASS Summit Webinar]( PASS President Grant Fritchey will be joined by fellow Microsoft MVPs Kendra Little and Steve Jones to discuss their highlights and learnings from PASS Summit 2018, the largest Microsoft Data Platform conference.[More »]( ---------------------------------------------------------------  [] [What Is SQLPSX?]( Additional Articles from [SimpleTalk]() SQLPSX is a useful library you can add to PowerShell to automate many DBA tasks. In this article, Laerte Junior demonstrates several useful commands and shows you how to create a SQL Agent job using this library to run daily DBA checks.[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Ethics in Data Science and Artificial Intelligence]( Brian Kelley from [SQLServerCentral Blogs]( Microsoft redid its Data Science Track not too long ago and they added several courses and new progress points. One...[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [A New Video - Create a DB Executor Role]( David Postlethwaite from [SQLServerCentral Blogs]( A long long time ago, I wrote a blog post showing how to create db_executor role in SQL Server. There...[More »]( Question of the Day Today's Question (by Steve Jones): Which of these functions are always deterministic? (choose 2) Think you know the answer? [Click here](, and find out if you are right. --------------------------------------------------------------- We keep track of your score to give you bragging rights against your peers. This question is worth 1 point in this category: deterministic. We'd love to give you credit for your own question and answer. To submit a QOTD, simply log in to the [Contribution Center](. ADVERTISEMENT [Pro Power BI Desktop]( This book shows how to deliver eye-catching Business Intelligence with Microsoft Power BI Desktop. You can now take data from virtually any source and use it to produce stunning dashboards and compelling reports that will seize your audience’s attention. Slice and dice the data with remarkable ease then add metrics and KPIs to project the insights that create your competitive advantage. Yesterday's Question of the Day Yesterday's Question (by Steve Jones): I have the Sales database that I am restoring. I have restored a full backup and ten log files. Each of these was restored using the WITH NORECOVERY option. I have run out of log files. How do I get the database online for users to access? Answer: RESTORE DATABASE WITH RECOVERY Explanation: If you do not have any other files to restore, you can use the RESTORE DATABASE or RESTORE LOG commands without any options other than WITH RECOVERY to open the database for client use. Ref: RESTORE - [click here]( --------------------------------------------------------------- [» 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]( : [SQL Server 2017 - Development]( [Incremental Table Copy]( - Hi, I have 3 huge table in excess of 100 GB each which need two be copied between two SQL 2017... [How to study the layout of a database?]( - Hello friends, Suppose you download a sample database, whether it be AdventureWorks, Northwind, or whatever other database. What steps do you... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Development and T-SQL]( [SQL Server Execute Permission for SSRS login]( - Hi, Recently when the application team did a SSRS Report deployment and the report was not working because the SSRS login... [Execute a Stored Procedure against every row insert in Table]( - I have around 200 tables and we have insert in each table with in each seconds. so there are around 90... --------------------------------------------------------------- [SQL Server 2014]( : [Administration - SQL Server 2014]( [Splitting data files caused performance issue]( - Hi All, We have a database about 500 GB , we thought of splitting the data files on different physical hard disks... --------------------------------------------------------------- [SQL Server 2014]( : [Development - SQL Server 2014]( [Joining tables using OR condition]( - I am told that i need to join Select * from Table A Left Join Table B on A.part OR A.part2 = B.Part Can i do... [Creating a Stored Procedure with Loop and If statement into a new table]( - Hi Guys,  Can anybody help? --------------------------------------------------------------- [SQL Server 2012]( : [SQL 2012 - General]( [UTS????????????Q/?457202606UTS????UTS?????UTS?????UTS?????University of Technology Sydney]( - UTS????????????Q/?457202606UTS????UTS?????UTS?????UTS?????University of Technology Sydney????????????Q/??457202606??????????,???????????,????????.????,????,???,Diploma,????,????(????) ?????----------------??????????????-----------????? ???????????: ?????,???????????……???????? ????????,??????; ?????????????????; ???????,????; ??????????,???????; ????????????? [ANU??????????Q/?457202606ANU????ANU?????ANU?????ANU????Australian National University]( - ANU??????????Q/?457202606ANU????ANU?????ANU?????ANU????Australian National University????????????Q/??457202606??????????,???????????,????????.????,????,???,Diploma,????,????(????) ?????----------------??????????????-----------????? ???????????: ?????,???????????……???????? ????????,??????; ?????????????????; ???????,????; ??????????,???????; ????????????? ? [MQU???????????Q/?457202606MQU????MQU?????MQU?????MQU?????Quarie University]( - MQU???????????Q/?457202606MQU????MQU?????MQU?????MQU?????Quarie University????????????Q/??457202606??????????,???????????,????????.????,????,???,Diploma,????,????(????) ?????----------------??????????????-----------????? ???????????: ?????,???????????……???????? ????????,??????; ?????????????????; ???????,????; ??????????,???????; ????????????? ???????????? [UQ???????????Q/?457202606UQ????UQ?????UQ?????UQ?????University of Queensland]( - UQ???????????Q/?457202606UQ????UQ?????UQ?????UQ?????University of Queensland????????????Q/??457202606??????????,???????????,????????.????,????,???,Diploma,????,????(????) ?????----------------??????????????-----------????? ???????????: ?????,???????????……???????? ????????,??????; ?????????????????; ???????,????; ??????????,???????; ????????????? ?????????? [UM???????????Q/?457202606UM????UM?????UM?????UM?????University of Melbourne]( - UM???????????Q/?457202606UM????UM?????UM?????UM?????University of Melbourne????????????Q/??457202606??????????,???????????,????????.????,????,???,Diploma,????,????(????) ?????----------------??????????????-----------????? ???????????: ?????,???????????……???????? ????????,??????; ?????????????????; ???????,????; ??????????,???????; ????????????? ????? [UNSW?????????????Q/?457202606UNSW????UNSW?????UNSW?????UNSW?????University of New South Wales]( - UNSW?????????????Q/?457202606UNSW????UNSW?????UNSW?????UNSW?????University of New South Wales????????????Q/??457202606??????????,???????????,????????.????,????,???,Diploma,????,????(????)  ?????----------------??????????????-----------????? ???????????: ?????,???????????……???????? ????????,??????; ?????????????????; ???????,????; ??????????,???????; [USYD??????????Q/?457202606DUSYD????USYD?????USYD?????USYD?????University of Sydney]( - USYD??????????Q/?457202606DUSYD????USYD?????USYD?????USYD?????University of Sydney ????????????Q/??457202606??????????,???????????,????????.????,????,???,Diploma,????,????(????) ?????----------------??????????????-----------????? ???????????: ?????,???????????……???????? ????????,??????; ?????????????????; ???????,????; ??????????,???????; ????????????? [UOW???????????Q/?457202606UOW????UOW?????UOW?????UOW????? University of Wollongong]( - UOW???????????Q/?457202606UOW????UOW?????UOW?????UOW????? University of Wollongong????????????Q/??457202606??????????,???????????,????????.????,????,???,Diploma,????,????(????) ?????----------------??????????????-----------????? ???????????: ?????,???????????……???????? ????????,??????; ?????????????????; ???????,????; ??????????,???????; ????????????? [Connect Adventureworks DB to Visual Studio 2012]( - Hi, I've tried various ways of connecting the Adventureworks DB to Visual Studio 2012. Can anyone provide a step by step on... --------------------------------------------------------------- [Database Design]( : [Disaster Recovery]( [Backup strategy| Azure VM snapshots VS SQL server backups for ready only databases - Which is the best option?]( - I am designing a backup strategy for one of the new applications. We have a Azure SQL server VM (IaaS)... --------------------------------------------------------------- [SQL Server 2005]( : [SQL Server 2005 Integration Services]( [Problem using expression in SSIS]( - Hi, Below is the expression i am trying to make work : substring ("Archive_Full_20120731200002",1, (FINDSTRING( "Archive_Full_20120731200002","Full_", 1)-2)) Whe I use the exact string(Archive_Full_20120731200002)... 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](. Feel free to forward this to any colleagues that you think might be interested. If you have received this email from a colleague, you can register to receive it [here](. --------------------------------------------------------------- This transmission is ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com