Alex has done some pioneering work on the testing of stored procedures over four years, and has learned a great deal in the process. In this article, he spells out the lessons learned from a wealth of experience in Unit Testing T-SQL.… Read more
Why don't we emphasize the huge advantages of parameterized queries over ad-hoc queries in SQL Server? There is a severe impact on resources and performance from repeatedly using similar ad-hoc queries, instead of reusing the existing query plans. David Berry shows how you can measure this impact, and springs a surprise or two in the process
… Read more
For those times when you absolutely, positively got to perform a cross tab query in SQL, Keith Fletcher's T-SQL stored procedure will allow you to do it "on the fly". You can add it to your database and start cross tabbing immediately, without any further setup or changes to your SQL code. Check it out, and then take the cross tab challenge. If you can compile a cross tab report that displays the order value by customer, by quarter, using the stored procedure, you may win a much-coveted prize!… Read more
Cursors and iterations are both renowned for slowing down Transact SQL code, but sometimes seem unavoidable. In this workbench, Robyn Page and Phil Factor demonstrate some set-based techniques for string manipulation and time interval-based reporting, which use helper tables rather than the dreaded cursor.… Read more
Never a man to walk away from a challenge, Phil Factor set himself the task of automating the production of Word reports from SQL Server, armed only with OLE automation and a couple of stored procedures.… Read more
If database design is done right, then the development, deployment and subsequent performance in production will give little trouble. A well-designed database 'just works'. There are a small number of mistakes in database design that causes subsequent misery to developers, managewrs, and DBAs alike. Here are the ten worst mistakes
… Read more
William Brewer investigates an unsung hero in SQL Server, extended properties, and shows how to use them to create self-documenting SQL Server databases.… Read more
Nigel Rivett demonstrates some core techniques for extracting SQL Server data into CSV files, focussing on a stored procedure approach that gives complete control of the format of the extracted data.… Read more
A seemingly never-ending battle in online database forums involves the question of whether or not database application development should involve the use of stored procedures.… Read more
Like a Phoenix, the dynamic SQL versus canned procedures and user functions argument has resurfaced on the SQL newsgroups. Many of the proponents of the dynamic argument are web or Access developers, or developers of some other front end. Arthur takes another look at the argument.… Read more
There have been a number of discussions about whether to use SPs or not, often shedding more heat than light, Doug Reilly takes a look at the debate.… Read more
