Yes, this is really too broad a subject for a forum, but here are a few thoughts....
I'm an old fogey who finds the many development and debugging aids for SQL Server wonderful but rather bewildering. They never quite give the information I want, at the right time, in the detail I want. Whereas, in certain circumstances, I'm a sucker for 'Visual-xxxxxx' IDEs, it is dangerous to rely on them for developing database applications. Gimme a nice plain Query Analyser!
Whenever I cut code, my uppermost thought is 'How can I test this to make sure it always does the right thing in every circumstance, How do I find out if and when it fails, or when it causes another component to fail?.'
When I'm developing database applications, I like to design them from the start to make them easy to debug. This often means putting in the modules that are required for the 'production' monitoring and maintenance of the system. It takes a lot more effort with very little to show for it, but I've never been tempted to leave all this for later on in the project and use the IDE approach with SQL Server. With a dynamic multi-user, multiprocess like this, the ability to fiddle about looking at variables is less important, when you're dealing with much broader factors such as indexing, locking, blocking, referential constraints, triggers, and so on. SQL Server has a number of ways of clever ways of alerting you to problems, if you want them.
I run all systems, even production ones, with a number of simple logs that will give me exactly the statistics I need on the running of the various parts of the system and flag up hotspots and likely performance issues. For example, I include the recording of the calling and exit of every stored procedure, along with the parameters, the spid that called it, time, user etc. This allows me to re-run serendipitous combinations of database procedures that cause problems.
I like to develop every routine in a test harness with checks for all sorts of errors, and the ability to rerun a series of events to investigate fully when things go wrong. Every stored procedure has a regression-test suite that allows me to rapidly alter a procedure and check that it still does what it is supposed to. I suppose I design stuff with the view, based on past experience, that things are going to go wrong, even in the most unexpected places, and it is very likely to be your own error that caused it!
When developing a routine, along with the obvious help that things like query execution plans give, I still use the old technique of 'Print-effing'. One can't effectively 'print-eff' to a console. I create a log in tempdb and write what ever I want to the log. By 'print-effing' I mean inserting records, at various points in the routine, into a log table to show things like the state of variables, table variables, or whatever. I have a ready-made template for creating the log, so it isn't a great trouble to do. You can then inspect the log to see what happens when you run your regression test on the routine. It never fails to astonish me how many bugs get trapped by using this sort of approach
Phil FactorSimple Talk