SQL Formatter

Our DBA writes some mean SQL code, I can write some SQL that works :).  It’s not my strongest suit but certainly not my weakest so I handle most of my sql statements myself.  However, when I need his help I cringe at sending him my sql code because he freaks when he sees it.  To me SQL is a string variable, I use to not care how pretty the formatting is, what tab setting to use, when to carriage return a FROM and so on.  Well, he cares and makes sure I know about it.  I totally understand why but for me it was too time consuming to make sure I format something that in the end is a string variable…. Until I found SQLinForm.  I am now a religious sql formatter, because not only does SQLinForm does it for me but it also creates c#, vb.net, php, and whole slew of other string variables.  My SQL has never been prettier and I can finally ship sql to my dba.

Written by Tim on July 9th, 2007 with 1 comment.
Read more articles on database and tools.

Stored Procedures vs Ad Hoc SQL

Stored procedures debate from my point of view:

Pros of using Stored Procedures

• Cleaner code.  Thousand lines of logic and sql statements can be traded in for hundreds.
• Push more work to someone else besides developer.  If you have a dedicated DB guy, this can really help in your timing. 
• Put much more logic into Stored Procedure then Ad Hoc SQL
• Faster. Ok, maybe a little bit, but with processing power and RAM does this really matter anymore?
• Permissions are centralized and encompassed.
• Trips to the database server can be reduced.

Cons of using Stored Procedures

• Much harder to debug.  No UI debugger, stepping thru the code. 
• Harder to tell were errors lie. Does this lie at the stored procedure or my code calling it?  Plus the error codes returned by SQL are cryptic - “Error 512 was called” - Oh yeah, good old error 512.
• Parameter list can be really long, which takes away from the cleaner code.
• “Stored procedures also will open up a maintenance problem. The reason for this is that they form an API by themselves.” This is the main point that causes me to stay away from Stored Procedures.

I’m not entirely sure which side of the fence I lie.  Right now I’m more comfortable with not using stored procedures because of the maintenance factor.  I really think LINQ is going to make stored procedures deprecated.

.NET specific - Here is a nice line of code to keep around when working with stored procedures that I end up using when getting the the dreaded “Parameters do not match” error.  You can put this on your datasource Inserting Event.

Written by Tim on June 1st, 2007 with 1 comment.
Read more articles on asp.net and database.

Navicat Review

I do a good amount of work in MySQL so that I need a good database admin tool.  I come from a Microsoft Enterprise Manager/Management Studio background so much of this review compares to that as a benchmark.

The Good

• Import/Export Data feature rocks.  Much easier to use then DTS for simple imports & exports & supports multiple formats (db, dbf, txt, html, xls, wk1, wq1, xml, mdb, ODBC)
• Dump as SQL.  Will write out your table to .sql file with the full inserts.  Microsoft so needs this.
• Maintain Tables.  Easily able to
• Backups.  Straight forward step by step.
• Saved Queries.  Navicat saves then within the application at the database level not a sql file, this makes finding queries much faster.
• Scheduled Job.  Again straight forward step by step
• Manage Users.  They make it as simple as MySQL can be (which isn’t that simple sense you have to create a user multiple times to have access from localhost and from anywhere)
• Data Transfers.  Their version of DTS, not as robust but gets the job done.

The Bad

• Query Windows.  This is my biggest complaint of Navicat and almost a show stopper for me. Tabbed windows are great BUT having multiple tabs within tabs causes my eye to lose focus of which tab I need.  Not good for quick tabbing, I have to think before clicking which slows me down. I much prefer how Management Studio has tabs for queries but shows results at the bottom of that window.

• Query Builder.  I do most of my SQL by hand so this doesn’t effect me to much but Navicat’s query builder uses the dreaded Grave `instead of the Quote ‘.  So if you use their query builder to get a sql statement started, maintaining it could be a nightmare.
• Designer. Seems clunky and implemented as a second thought.  Big improvements here.

Overall Navicat is the most feature rich MySQL tool on the market (I’ve tried ~4 so far). Pricing is at $139, well within reason. Besides a reworking of the query window, Navicat is a finely tuned.  2 TechToolBlog thumbs up.

Written by Tim on April 26th, 2007 with 5 comments.
Read more articles on database.

Scripting a MS SQL Database

One thing I love about Navicat is the ability to give me the raw SQL in a backup, something that Microsoft Management Studio lacks big time. I found this great Code Project article/application that does this perfectly.

http://www.codeproject.com/dotnet/ScriptDatabase.asp

Written by Tim on January 12th, 2007 with no comments.
Read more articles on database.