7 Answers
The largest I ever personally wrote was a 16kb text file, which was many dozens of printed pages, and much personal misery.
Otoh, I was once at the Microsoft campus in Redmund in a customer lab (what would later become the SQLCAT group) where I was shown a berserk 3rd party report writing tool which would routinely create individual stored procedures over over 30mb in size. The largest I saw at that time was 38mb. The problem? The report writing tool could not write JOIN clauses and so used correlated subqueries for everything. The horror! The horror!
Otoh, I was once at the Microsoft campus in Redmund in a customer lab (what would later become the SQLCAT group) where I was shown a berserk 3rd party report writing tool which would routinely create individual stored procedures over over 30mb in size. The largest I saw at that time was 38mb. The problem? The report writing tool could not write JOIN clauses and so used correlated subqueries for everything. The horror! The horror!
I can't say for sure, but the one script that comes to mind, a stored procedure actually has a bit of everything, OpenXML, I think CTEs, and more.
At least 3 different DBAs have looked at and other than some minor changes (2 of us actually agreed on similar performance improvements, but the company never implemented them) it was just too much of a bear to maintain or debug. I'd say easily a couple hundred lines long.
Definitely not 8000. And not 25 pages.
At least 3 different DBAs have looked at and other than some minor changes (2 of us actually agreed on similar performance improvements, but the company never implemented them) it was just too much of a bear to maintain or debug. I'd say easily a couple hundred lines long.
Definitely not 8000. And not 25 pages.
Requirement can be complex and not the solution that's giving by an IT person. I personally like breaking things up with the help of CTEs or Stage tables or Temp Tables. To answer the question, I need to deal with someone's code which had 3000+ lines of code on a single SQL query. It's nightmare to debug and the person who wrote the query 20 years older than me...
