OMO: SQL query tools

[Brettus]

Hopefully a quick one, I've implemented an ERP system at work that uses SQL as the back end and I've got a bit of experience in writing some reports. The company that sold us the ERP installed Visual Studio Data Tools and that loads up so I can write and deploy SSRS reports which are cool but the actual query writing at present is done in MS graphical query, it isn't very feature rich (missing UNDO much to my recurring chagrin!) I resort to copying the query out to notepad just in case I break it. Very messy.


There MUST be a better software solution out there. I know that tools aren't always ideal as they aren't as flexible or powerful as just coding it but I'm not quite at "matrix" level just yet. Most of what I'm wanting is simple enough stuff, just it gets a bit messy and on one of them the parser gave up trying to sort it out for me which didn't help. I like the table map view and things but is there a better tool out there I could be using for getting the query done?


Thanks org!

[PyroUK]

That's a bit too technical for my knowledge, but I used to use MS SQL Server Management Studio at my old place.

Did what I needed it to do, although you might not be able to get it free perhaps they do a trial version so you can see if it's fit for purpose?

[LiquidFlux]

I'd suggest going through the list of SQL related tools on alternativeto.net (http://alternativeto.net).

[phi-dan]

For creating formatted reports at work we use Crystal Reports. No need to know the full syntax, you just drag and drop fields onto the report, but you can also build complex queries, link tables, ref external data, etc.
One of the developers has a thing for Logicity (but she also hosts dashboards off nested shares on her PC instead of the web cluster / SharePoint server / data farm- go figure)

[Heorot]

Sorry can't help. I last used QMF on an IBM mainframe with DB2 about 12 years ago.

[keith_d]

I've used Crystal Reports in the past to build a flexible reporting system. It's very good but...

a) you have to understand the database structure well enough to build a 'universe' which includes all the key fields and the relationship between them

b) it used to be quite expensive

[Heorot]

Talk about stating the bleedin' obvious. Without knowing which fields have keys, any query will be horrendously inefficient.

I was a systems analyst and relational database designer for 20 years.

[Haircut]

You mentioned SSRS so I am going to assume then you mean Microsoft SQL server is the back end, to be honest the Microsoft Client tools are really the best bet as they are built for the job and can be downloaded freely and include a useful help function if you install it.

Writing SQL is generally clunky graphical tools just gloss over that and in doing so hide important functionality (IMO), forget the GUI jump in with both feet and learn the required syntax's for the basics (Select statement, from statement, where clause, inner & outer joins) and you will be much the wiser and better off long term.

[keith_d]

Talk about stating the bleedin' obvious. Without knowing which fields have keys, any query will be horrendously inefficient.

I was a systems analyst and relational database designer for 20 years.

And I was supposed to deduce this using telepathy??

[kaivalagi]

You mentioned SSRS so I am going to assume then you mean Microsoft SQL server is the back end, to be honest the Microsoft Client tools are really the best bet as they are built for the job and can be downloaded freely and include a useful help function if you install it.

Writing SQL is generally clunky graphical tools just gloss over that and in doing so hide important functionality (IMO), forget the GUI jump in with both feet and learn the required syntax's for the basics (Select statement, from statement, where clause, inner & outer joins) and you will be much the wiser and better off long term.

+1

If dealing with MSSQL server the Microsoft tools are about the best there is, just requires understanding basic SQL syntax

From version 2008 onwards there's intellisense build in (via Ctrl-Space) to provide cues for object and operator names etc, but installing something like SQL Complete (http://www.devart.com/dbforge/sql/sqlcomplete/) (they have a free version) will work a little better.

Why not start a little SQL query help thread :)

Also, there are decent sql tutorials out there to get you moving, but I'd say if you want to create performant sql you'll need to maybe do a 4 day course to get to the grips with it properly (depends on how well you can learn from a book or not). If you can understand indexing, joins etc and can utilise CTE (http://msdn.microsoft.com/en-gb/library/ms175972.aspx), temp tables and table variables then you're well on your way.

[Heorot]

Keith_d, I apologise in advance if the following appears condescending, it's meant to be helpful.

if you want to write efficient SQL it is essential that you understand how relational databases work. A relational database contains many sets of related data that are linked by keys. In order to get the individual elements within the sets, you need to know which data elements are held in which sets. You should then look for the keys within those sets and use them to join them. As kaivalagi says, if you are going to use SQL more than occasionally, then you should go on a short course to learn the basics.

[kaivalagi]

Brettus, I'm more than happy to answer any questions you might have off the back of self learning, I'm sure a few others here would be too :)

Best not to use graphical tools for building queries unless you already understand the basics and can interpret what they are producing and tune as necessary

Like Heorot says if you can understand the basic premise of tables/views/sets of data and their keys/indexes/relationships you can create some half decent flat queries of the wanted columns from that data...

If you get into more complex stuff like aggregations within sets of data that then form new relationships to other data etc etc then you need to get to grips with more technical solutions that utilise CTE (http://msdn.microsoft.com/en-us/library/ms175972.aspx)/temp tables/table variables etc or even step towards using SSIS (http://msdn.microsoft.com/en-us/library/ms169917.aspx) for churning through big amounts of data and handling it's transformation as a pre-requisite for reporting data etc , all this needs a little experience really though...

Let us know if we can help! To start with have a look for "MS SQL Client Tools" and download and install the correct version on your desktop for your SQL server version

[Brettus]

Cool! thanks for all the replies guys. Sorry for lack of reply, I've been reading every response (honest!)

I think my current problem is I'm not sure what my problem is. I like the visual design so I can quickly link the fields and it also helps me to lay it out (I resort to whiteboarding my queries first too) I'll see how I get on and when I've got an example of a problem (other than the clunky and CTRL-Z lacking query editor) I'll let you know.

[kaivalagi]

The visual design GUI for sql reporting will help you see the data and relationships but as you've seen it is limited. To me it sounds like you ought to use it to form a basic query and then play with it in the sql client tools until it runs right. In the query analyser there are options to see the actual execution plan etc for a query so you can see where a lot of time is being spend on operations, this can help you identity where indexes are lacking etc. There are a lot of other tools that can help too, worth getting familiar with the client tools and what they can help you with for sure!

Have a play and get back with any questions, it will be worth it to go down this road...getting a query wrong can cause table scans etc which have the knock on effect of blocking tasks which cause unwanted delays and or timeout exceptions within the database application etc

[Heorot]

Going back to keys. One thing to be aware of is that the key of a set (usually called a table) might comprise more than one element. To get a direct access, you will need to access the table using all the elements. That's why kaivalagi's suggestion that you look at the execution plan will save you hours of frustration. Some tools will identify where there is a non-indexed join.

[kaivalagi]

Some tools will identify where there is a non-indexed join.

Yep, in MSSQL that's normally going to result in a table scan in the execution plan taking up a high percentage of the overall execution, very easy to see.

There is also a tuning wizard that can be utilised to highlight indexing worth messing with...it's within the profiler tool and just requires logging of traced activity of the database first, e.g. http://www.datasprings.com/resources/dnn-tutorials/artmid/535/articleid/53/sql-server-2008-profiler-and-tuning-wizard?AspxAutoDetectCookieSupport=1

There's loads of good stuff to get your teeth into Brettus, a geeks dream come true lol!