如何维护大型 t-sql 过程
我即将继承一组大型且复杂的存储过程，这些存储过程每月对非常大的数据集进行处理。
我们正在调试它们，以便它们与用 VB6 编写的原始流程相匹配。 他们决定用 t-sql 重新编写它们的原因是因为 vb 过程需要几天的时间，而这个新过程需要几个小时。
所有这些都很好，但是我怎样才能使这些现在大量的 t-sql 代码（1.5k+ 行）甚至可以远程读取/维护。
任何使 t-sql 不再令人头疼的经验都是非常受欢迎的。
I'm about to inherit a set of large and complex set of stored procedures that do monthly processing on very large sets of data.
We are in the process of debugging them so they match the original process which was written in VB6. The reason they decided to re write them in t-sql is because the vb process takes days and this new process takes hours.
All this is fine, but how can I make these now massive chunks of t-sql code(1.5k+ lines) even remotely readable / maintainable.
Any experience making t-sql not much of head ache is very welcome.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
ApexSQLScript 是一个很棒的工具，用于编写整个数据库的脚本 - 然后您可以将其签入源代码管理并管理更改。
我还发现，一致地记录存储过程可以让您使用 sys.sql_modules 中源代码的数据提取有关它们的信息 - 您可以使用标签或其他任何方式来帮助记录子系统。
另外，使用模式（甚至多个数据库）——这确实有助于将数据库划分为逻辑单元并指出体系结构问题。
就大型代码而言，我最近发现 SQL2005 CTE 功能在管理具有大量嵌套查询（甚至不是递归）的代码时非常有用。 可以声明和构建 CTE，然后在最终语句中使用，而不是管理一堆嵌套和缩进。 这也有助于重构，因为删除冗余的嵌套查询和列似乎更容易。
存储过程和 UDF 对于管理大型代码库和消除暗角至关重要。 我还没有发现视图非常有帮助，因为它们不可参数化（如果结果集很小，在这些情况下可以使用 UDF）。
ApexSQLScript is a great tool for scripting out an entire database - you can then check that into source control and manage changes.
I've also found that documenting the sprocs consistently lets you pull out information about them using the data about the source code in sys.sql_modules - you can use tags or whatever to help document subsystems.
Also, use Schemas (or even multiple databases) - this will really help divide up your database into logical units and point out architectural issues.
As far as large code, I've recently found the SQL2005 CTE feature to be very useful in managing code with lots of nested queries (not even recursive). Instead of managing a bunch of nesting and indentation, CTEs can be declared and built up and then used in the final statement. This also helps in refactoring as it seems a lot easier to remove redundant nested queries and columns.
Stored Procs and UDFs are vital for managing a large code base and eliminating dark corners. I have not found views to be terribly helpful because they are not parameterizable (UDFs can be used in these cases if the result sets are small).
肯定要从重新格式化代码开始，尤其是缩进。
然后将SQL模块化。 将块拉出到更小的、描述性命名的过程和函数中，并放在它们自己的独立文件中。 我发现仅此一点就可以很好地提高我对大型 SQL 文件的理解。
Definately start by reformatting the code, especially indentations.
Then modularise the SQL. Pull out chunks into smaller, descriptively named procedures and functions in their own stand alone files. This alone I find works very well with improving my understanding of large SQL files.
首先，创建一个充满 .sql 文件的目录并在那里维护它们。 将这组 .sql 文件添加到修订控制系统。 SVN 运行良好。 有一个工具可以将这些加载到您的数据库中，覆盖任何现有的。
拥有一个测试数据库和基线报告，显示每月处理的输出应该是什么样子。 您的测试还应该采用版本控制下的 .sql 文件的形式。
您现在可以根据需要重构您的过程，然后运行测试以确认正确的功能。
First, create a directory full of .sql files and maintain them there. Add this set of .sql files to a revision control system. SVN works well. Have a tool that loads these into your database, overwriting any existing ones.
Have a testing database, and baseline reports showing what the output of the monthly processing should look like. Your tests should also be in the form of .sql files under version control.
You can now refactor your procs as much as you like, and run your tests afterward to confirm correct function.
对于格式化/漂亮的 SQL，我已经成功使用 http://www.sqlinform.com/ - 您可以试用免费的在线版本，也可以使用桌面版本。
For formatting/pretty-fying SQL, I've had success with http://www.sqlinform.com/ - free online version you can try out, and a desktop version available too.
尝试尽可能模块化 SQL，并进行一组测试，使您能够在需要时维护、重构和添加功能。 我曾经有幸继承了一个总共 5000 行的存储过程，但我仍然会做噩梦。 项目结束后，我打印了存储过程，以在这个过程中破坏 X 树。 在我们公司的一个每周站立会议上，我把它首尾相连，它延伸到了建筑物的整个长度。 将此作为如何不编写和维护存储过程的示例。
Try to modularise the SQL as much as possible and have a set of tests which will enable you to maintain, refactor and add features when needed. I once had the pleasure of inheriting a Stored Proc that totalled 5000 lines and I still have nightmares about it. Once the project was over I printed out the stored proc for a laugh destorying X trees in the process. During one of our companies weekly stand up sessions I laid it out end to end and it streched the entire length of the building. Ised this as an example of how not to write and maintain stored procedures.
您可以做的一件事是使用自动化脚本来存储对源代码控制的所有更改，以便您可以查看对过程的更改（使用先前版本和当前版本的差异）
One thing that you can do is have an automated script to store all changes to source control so that you can review changes to the procedures (using a diff on the previous and current versions)
它绝对不是免费的，但为了使您的 T-SQL 格式保持一致，Redgate Software 的 SQL 提示符非常方便。 只要你的过程的语法是正确的，几次击键（Ctrl+K，Y）将立即重新格式化它。 这些选项使您可以很好地控制 SQL 的格式。
It's definitely not free, but for keeping your T-SQL formatted in a consistent way, Redgate Software's SQL Prompt is very handy. As long as your proc's syntax is correct, a couple of keystrokes (Ctrl+K,Y) will reformat it all instantly. The options give you a lot of control over how your SQL is formatted.