OMO: SQL query tools

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?

I'd suggest going through the list of SQL related tools on 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]

Originally Posted by 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.

And I was supposed to deduce this using telepathy??

[kaivalagi]

Originally Posted by 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.

+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 (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, temp tables and table variables then you're well on your way.