Recursive CTE calculations (SQLServerCentral 11/19/2018)

Klicken Sie hier, um Sqlservercentral.com zu abonnieren

Laden...

6 jahre vor

Html
Text

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.

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.

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

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.

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 protected]. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the [email protected].

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.

Teilen auf

Laden...

Weitere Newsletter von Sqlservercentral.com

A Trigger... just not that kind Sqlservercentral.com
Vor 59 Minuten
Configuring Maintenance Plans in SQL Server (20 24-02-21) Sqlservercentral.com
Gestern um 08:17
How to Resolve SQL Server Database Stuck in Rec overy Pending State (2025-02-19) Sqlservercentral.com
Letzten Mittwoch um 08:13

Weitere Newsletter von Sqlservercentral.com

Laden...

Recursive CTE calculations (SQLServerCentral 11/19/2018)

A community of more than 1,600,000 database professionals and growing

Featured Contents

The Voice of the DBA

Break Problems Down

The Voice of the DBA Podcast

Featured Contents

Recursive CTE calculations

What we learnt at PASS Summit Webinar

What Is SQLPSX?

From the SQLServerCentral Blogs - Ethics in Data Science and Artificial Intelligence

From the SQLServerCentral Blogs - A New Video - Create a DB Executor Role

Question of the Day

Today's Question (by Steve Jones):

Think you know the answer? Click here, and find out if you are right.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

Database Pros Who Need Your Help

SQL Server 2017 : SQL Server 2017 - Development

SQL Server 2016 : SQL Server 2016 - Development and T-SQL

SQL Server 2014 : Administration - SQL Server 2014

SQL Server 2014 : Development - SQL Server 2014

SQL Server 2012 : SQL 2012 - General

Database Design : Disaster Recovery

SQL Server 2005 : SQL Server 2005 Integration Services

Teilen auf

Weitere Newsletter von Sqlservercentral.com

Verwandte Newsletter