Pages

Friday, 24 April 2009

Formatting SQL is harder than you think..

I've had a couple of pieces of feedback about the T-SQL Tidy site. One is that the hosting is a bit slow and that the format of the SQL produced is not as good as the SQL generated from SQL Server.

The T-SQL generated by SQL Server when you right click on a table and choose 'Script table as' is much better than the format of the T-SQL after it has been parsed by the formatting engine.

This is true, I don't think it would be possible to format the T-SQL any better than the way SQL Server is doing it. This is because SQL Server understands what it is scripting, the definition of each object (table's and column's) are stored inside SQL Server. T-SQL Tidy has no knowledge of this, it reformats the SQL outside the context of the SQL Server.

I was trying to explain why the formatting engine behind this site is not as good as formatting by hand to a friend of mine. He asked why don't you just insert line breaks after commas? I explained that although an early version of the engine did just that, but its not that simple. Commas are used for separating parameters in internal functions and stored procedures, how do you know when a comma is separating fields or separating parameters in a function?

The answer is you cant. (Okay – you could, but not practical)

So I have added a new option to the site, “Insert New Lines after Commas” - so if you are formatting very simple T-SQL and you want new lines after each field, just enable this tick box.

The hosting is provided by ASPSpider.com, which is a free hosting site, as it is free I cant complain. It the speed becomes a big issue or it the site becomes too popular, it may need a new home but until then I am happy with ASPSpider.com.