SQL Server TSQL Debugging - Massive View - Any Tips?

Review the joins by commenting them out, one by one, to see if you can isolate to hopefully one join. Then review the data in that situation that is causing the issue to figure out how to deal with it.

Yes that's what I do. Also, I do a select * first so I can see if I can see what data elements are different in the two rows, that helps me find which table is most likely to be the problem. – HLGEM Oct 18 '10 at 18:01 +1 The OP has stated that all 11 of the joins are crucial to the dependancies within the query - a similar effect can be achieved by changing inner joins to outer joins in such circumstances.

– Mark Bannister Oct 19 '10 at 11:47 @Mark Bannister: Those JOINs could be changed to EXISTS or IN, depending on the need which would solve the issue of duplicates. – OMG Ponies Oct 19 '10 at 15:26 Absolutely - that would be a better way of doing it, but it would probably be quicker to convert an inner to an outer join, in the first instance. – Mark Bannister Oct 19 '10 at 15:42.

Use some visual tools with big queries; surprisingly ms access is not bad for such tasks, you can have a visual representation of all joins and conditions abstract and isolate parts by saving and reusing ms access query objects easily and without modifying the schema.

Couple tricks... Finding duplicate keys... Any query can be turned into a subquery and selected from. Select idcolumns,count(1) from (put your entire query here as a subquery, 90+ lines is fine) group by idcolumns having count(1) > 1 This one in particular will show if you have any duplicate rows for your 'idcolumns'. If you're having problems with 'split rows', changing around the 'idcolumns' will allow you to see where these records are being split.

You can also include a where clause so you're only looking at a small sample set from your original query...it's easier to identify a single record and work with it through this process (as Brad stated in his comment). If this is an error in a GUI somewhere...isolate and replicate the error first. This seems more like a view in your case so it might not be valid here, but if you can't isolate and repeat an error, you cannot troubleshoot it.

Refer to OMGPonies post...remove all the tables in the joins and slowly introduce tables one by one. With each join you put back in, run the count query above and watch for changes. I'm a bit of a drawer...I do recommend finding a white board and drawing out each relation...don't go overboard, but the visual reference helps me alot.

Colour code it so it's easy to view (I do tables in black, table name in red, primary key in blue, FK's in Green, and connect the tables with this weird purply coloured marker I stole...er...permanently borrowed...from my last job). And lastly...front to end mapping. Alot of the time these old queries were written when business requirements were different.

Over time with employee churn, the start and end goal gets lost along the way and the process becomes more important than the goal. Identification of what your users are using this for can be as important of step as actually fixing this view.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.