SQL Editor IDE to use instead of Query Analyzer?

1

KHTan told me that QA won't install under Windows 10

OMG!

Yes, still QA here. I am far more productive in that than SSMS (also installed, and used for GUI tasks, but not for writing SQL code/Sprocs), but I saw an installer using SSMS yesterday and I was impressed with how he used it (as it is intended to be). He hardly wrote any code just did Point & Click in the Table tree to get columns lists, and Intellisense to get table/object names (of course he knew what it was going to offer him, so he was genuinely using it intelligently :slightly_smiling: to save him typing-time) . But all his code was full of swarf (square brackets, repeat of unambiguous qualifiers, etc.) that would seriously annoy me.

I use an sproc (with a very short name!) to get lists of columns (well ... pretty much anything) in the format that I want them and Cut & Paste which suits me well and is a habit that I would find very hard to break.

If I can't get QA to install I think my best bet would be to buy :astonished:!! the nearest equivalent that I can find. I currently (but not very often) toggle to a Programmers Editor for some RegEx type find & replace, and its brilliantly effective DIFF, which it would be nice to have integrated in the IDE, but I doubt, for me, that is SSMS.

The other thing that might work for me is a bigger screen. I dislike using QA on my laptop (1600 x 900) compared to my desktop (2x 1280 x 1024) because of a lack of window height on the laptop. My ideal window width is around 800px wide and as tall as possible, and in QA I can get a couple of those side-by-side and overlap them to jump between them. TAB'ed windows doesn't work for me, and having to open/close side panes I find annoying (although I expect there is an F-key that will do that, in SSMS, once learned)

I'm looking at upgrading PC (now too slow with 100 Chrome TABs open across half a dozen Windows) and will probably go for 2 x 27" 4K screens which I think? would be 3840 x 2160. Might put one of them Portrait? That will give me taller 800px wide IDE windows.

Things I do:

My first window opened is always JUNK_COMPARE.SQL. That is in a pane on the left, and I use it as a cut & paste dump. Lets say I go to close a window (it's probably been open for days ... !!!) and QA says "Save changes?" and I go "Hmmm ...". Chances are that I probably already edited that same file some more / differently in another window, so MAYBE the changes are stale. I Copy & Paste its contents to my JUNK window, do OPEN or SAVE AS on the original window (to get file list), if the filename is not immediately visible (directory is in LIST format, but MODIFY TIME sorted, so effectively gives me "Recently Worked" files - typically 12 files without scrolling) I modify the filename to append a "*" and then the Files Window just shows one file.

I have an APP installed (that probably won't work in Windows 10 either! it moaned when I installed it last time ...) that allows me to RightClick the file and get Long Name, Folder, Path [inc Name], UNC name to clipboard. I choose Path and then toggle to programmers edit and use DIFF between JUNK (which is always the first file, so will be remembered as the default) and paste my Path into 2nd file. The DIFF fires up and I either merge the changes, with the ones I did already, separately!!, or into the original file if I hadn't actually changed anything separately, or discard them.

I probably ought to change SOME habits though ... I use a Concatenate For Release approach to building a script of multiple SQL files, but its not an instant job (it involves some SOP steps as part of the Release to QA), so I could do with something lightweight for a Daily Build. I had to drag 100 or so files to QA yesterday to make sure "everything recently changed was run", and then manually run each one - that's a PAIN!.

Some files (i.e. SProcs and Triggers) still have, deliberate, ZZZZ comments that need attention and are designed to prevent a script just being run so I remember to do something about them before release! in the meantime they get commented out as "--xxzzzz" so the script executes, and the "--xx" will be taken out if the file gets saved, in that format, and then attempted to be put into Revision Control. I really should make (Google?!!) a BATch file that grabs "all files since XXX" and concatenates them AND changes any line that starts "zzzz" and runs them through SQLCMD

Probably some other productivity things I could lean from others ... :slightly_smiling:

2

I pretty much insist on this since what is unambiguous today may be ambiguous tomorrow. Also when looking at a query where unambiguous qualifiers are left off, I find it difficult to determine from which of the 7 joined tables a given column comes. So its also a sanity thing for me

The SSMS IDE does regex find/replace[quote="Kristen, post:1, topic:5334"]
will probably go for 2 x 27" 4K screens
[/quote]

Money to burn?

3

If you just don't like SSMS as an editor for SQL code - one option that I would recommend is to purchase SQL Assistant (http://www.softtreetech.com/isql.htm) and set it up to work with Notepad++.

This will give you intellisense for almost any database system you connect to (Oracle, MySQL, SQL Server, etc...). It will also give you automatic formatting of your code - based on your rules as well as the ability to compare code, check syntax, spelling, etc...

4

My (may-not-be-so) humble opinion:

  • Try SSMS again. They've broken it out from SQL Server development so that it's updated more frequently:

https://msdn.microsoft.com/en-us/library/ms174173.aspx
https://msdn.microsoft.com/library/mt238290.aspx

License fee is totally reasonable, and if you contact him he might help you out.

SQLPrompt is waaaaaayyyyyy better than Intellisense. Totally worth the money IMHO. Between that and ToolsPack, plus all the new stuff they've added, you might be surprised how nice SSMS can be. You might be able to customize them to skip square brackets etc.

Built-in for VS 2013 and higher, FINALLY has useful/sane designers and code generation. It's not just point and click.

Can't vouch for any other tools, but I no longer miss Query Analyzer thanks to the things I've mentioned here. Mladen's ToolsPack is an absolute godsend, BTW, it has saved my ass so many times I can't remember them all. I just throw money his way every year because he deserves it. :slight_smile:

5

Thanks all. By coincidence :slightly_smiling: I've been on site all week and forgot my laptop charger, so had to use SSMS on a spare PC in the client's office. I decided to take the opportunity to try to get more proficient on it ... I am sure that my knowledge of SSMS is limited, but my week-long experience definitely left me worse off than if I had been using QA - in terms of efficiency of my time - despite the fact that there are some useful productivity things in SSMS, compared to QA

Thanks, I'll take a look.

I had had a look at Mladen's SSMS ToolPak a while back, out of curiosity (colour coded pane for PRODUCTION and TEST connections would definitely be A Good Thing :slightly_smiling: ), so if I go down the SSMS route I'll definitely send him some pocket money ...

Our naming convention requires that a column name includes a 2-part mnemonic for the table (2-part because the table itself has a 2-part mnemonic prefix for "Project area" and "Table shortcode") therefore, in my case, I think that "becomes ambiguous tomorrow" is almost impossible.

Also means that we don't need any table aliases unless we self-join, as all column names in the DB are unique. (I know there are other schools of thought on naming conventions etc., so this is Just The Way That We Do It :slightly_smiling: )

I didn't think they were expensive ... :sunglasses: I could keep my existing pair of screens, but they are old and may well be nearing end-of-life anyway, plus the odd percentage point improvement in productivity would pay for them over two or three years. But I'm open to opinions on that.

Here's an example of me struggling to work, efficiently, in SSMS (compared to QA). I'd be interested to hear if people do something similar, but have a better way of working in SSMS.

I work with overlapping code windows, rather than TABs. I typically want to be able to position things so that I can easily toggle between 2 or 3 panes that I am working on - checking code in one, or cut & pasting from another etc. I found this particular erksome in SSMS (might be lack of experience though). For example: What's the best way to do this:

I open two files, drag them off the TAB bar and resize them 50:50 for the two sides of the screen. QA would have opened them at a "reasonable" size, SSMS opened them full size (as TABs), when I dragged them off the TAB bar they were still full size, so I had to resize them. If I dragged them to the 4-position-cross they were resized to half the screen, but covered the toolbar at the top and the status line at the bottom and were also "connected" so I couldn't just resize them further (i.e. to make the overlap)

I could only open a different file in an existing window if the code in the window had not changed. It is common for me to want to add a quick SELECT in the middle of the code for an SProc, to check some data, and then abandon that change (by loading a new file in that window). I couldn't figure out how to get SSMS to get me to confirm the abandonment of changes; if the file in the window had changed it just opened the new file in a new TAB (not a floating window, even though all my windows were floating) and I had to drag the TAB off the tab-bar and resize it again ... and then every few minutes wander around closing the plethora of windows that had opened, being careful not to close the ones that I still wanted lying around.

Equally if I have not changed the code in the current window I can't seem to open a new window - i.e. the existing window is reused. If I use "New Query" it clears the code in the current window, rather than opening a new window.

Also, if I have some Results in the current window and then open another file (reusing the window for the new file) the results are cleared. I definitely don't want that as I often want to look at another file by re-using the same window - for example, the results may be showing some DEBUG statement output which indicates that a sub-SProc was called, and that is showing goofy results, so I now want to load that other Sproc but I still need to be able to see the DEBUG output in the results pane).

6

Does anyone know of a SQL Editor which has an inbuilt syntax checker thingie and which is able to MOAN about things that a LINT type code-checker would alert to?

For example if I use an ALIAS without including " AS ", or I type VARCHAR or DECIMAL without the size/etc. parameters?

I would LOVE to have an editor that has that built in.

I've been looking at the manual for SQL Assistant and the code-generation and refactoring look excellent. Not sure how much "cost" there would be throwing away my existing tools for that purpose, and learning a new way of doing things. It would help if the new way gives me a 100% solution, rather than an 80% solution - I won't know that until I try it :slightly_smiling: but, after a brief scan of the DOCs it does look good.

7

I don't think it does that. It creates a new tab to the left of all existing ones. You can see the numeric suffix increasing.

I don't work with overlapping code windows (never even tried it out) but I'm quite used to SSMS as an IDE. Perfect? no. Adequate? more than. Plus you get gui support for tons of admin functions.

quote="Kristen, post:5, topic:5334"]
and then abandon that change
[/quote] why not Ctrl-Z?

8

not if your tables are conformant! then a column with the same data has the same name in all tables where it is found

9

For me to buy those locally would set me back at least $1500. Trying to think of how I would justify that to my wife....nope! Nothing comes to mind!

10

$350 or more?

11

Yeah, they are about that here. GBP 500 each IIRC, so $1,500 for the two sounds about right. This is for work, rather than for home per se (I work from home ...) so to pay for itself (assuming that the old monitors "would do", and will carry on working for the life of the new PC) and lets say they have a life of 200 weeks, then they only need to save $7.50 per week ... I would be surprised if more screen real-estate didn't achieve that for me.

Why bother? Often there may be several such mucking-abouts, I would have to keep using Control-Z until I have managed to remove all of them, in order that I have a no-changes-state to then open a file (But SSMS doesn't give me any indication, so I would have to check the status as to whether it was changed, or not, before opening the new file. SSMS will just create a new TAB if the existing window contains changes). I prefer the QA method of "Open new file", which will reuse the current window [i.e. I have to deliberate open a new window, when I want one] and says "Abandon changes?" if there are any changes which will be lost when the new file is loaded. It is not uncommon that I try to open a file in an existing window and there are changes - i.e. which I actually need to save, but had not pressed SAVE at the time I made them.

There is another situation where I go to open a new file, or close that window / all windows and I get an "Abandon changes" message. Perhaps I have no idea / am not sure what I changed. I do the following:

Copy & Paste whole contents to my No #1 window (which is always open with JUNK_COMPARE.SQL).

Use File Save As on the offending window. It offers me the original filename as a default. Append "*" and press enter so that the file list only shows that one file.

Use Right-Click and copy File Path (driver, path and name) to the clipboard (some gadget I installed years ago)

Toggle to my Editor. Select DIFF which will already have JUNK_COMPARE.SQL in "File 1" - from the previous Diff, natch :slightly_smiling: )

Paste the Path/Filename into File 2

Review any differences. I can selectively merge them into the original file, or if they are all acceptable I can revert to QA and just save the changed-file anyway.

Mine does this:

New Query - creates the first TAB. Following behaviour doesn't matter whether I drag that TAB off the tab bar, to make a floating window, or not. Shows as Query1.sql

Open a file

Do NOT modify the file

Press NEW QUERY again. Shows as Query2.sql, but the first window is nowhere to be found.

If, instead, I modify the code in the first window, then when I do New Query it opens a new, second, window (actually as a TAB behind my floating window)

I have "Reuse current window, if saved" set - which might be the reason, but I had assumed that was for "If I open a file and the current window does not need saving reuse it", not "If I click New Query throw away the current window, if not changed, and don't bother to make a new window"

12

Then just close that tab without saving

13

Just did that. After the last step I have three tabs: Q2, the file I opened and Q1.

Is that not what you get? (Wish we could easily, securely share screens)

14

Certainly SSMS doesn't work that way. Even if you never use a query tab, when you open a new file it gets a new tab. Not sure if that is a feature or a bug,

BTW does QA have source-control built-in? Can you build up a project (a la ssmssln) and commit it to a VCS? Just curious.

Oh another IDE I sometimes use is LinqPad. It's able to talk to Oracle and MySql natively as well as MSSQL

15

But I want to reuse the window. I have it carefully positioned just how I like it, with other windows alongside ... I just need it to, now, be showing the contents of a different file

No, I just have one "window". I suspect its my setting "Reuse current window, if saved" (=ON), but if so that seems rotten! a New Query is not the same as "Empty/Blank the current window" IMHO, not to mention that the QueryNNN number increments too ...

I started to take screen shots to show how it is here, but got bored of drawing boxes over the server name, file name, database name, MY name!! so I reverted to describing it instead. I can do the screenshots if anyone wants to see them.

I'm trying @jeffw8713 suggestion of SQL Assistant's Editor. Its much like QA, only not quite enough-like-it for me - might be configurable, but I haven't found the option yet (there aren't many about Window launching).

But its got some WELL COOL tools :slightly_smiling: for example:

Highlight a block of code, a little icon "Sync editing block" appears which, when clicked, highlights the block (instead of the Selection) and then if you edit, e.g. a variable name all the matching occurrences within that block change (in real time, as you type, in case you want to see the "impact")

Don't know, but if it does I don't use it. I use Tortoise (which talks to SVN in my case). I do all that as a separate exercise for any local changes "when I'm ready". I'd prefer a distribute revision control system, but I haven't found the time to install one (or more correctly, to set up a test environment so that I can prove, to my own satisfaction, that I can migrate the whole of my SVN repository, including all historical changes, into the new Beast). I would defintiely like to be able to check-in changes I make on a laptop, on a client site, without having to have them become part of the "main built" / Trunk (which, as I understand it, is what I would get from a distributed system)

I should just employ a Summer Student, this summer, to get a bunch of things like that sorted out ...

16

well, at the end of the day, I guess trying to get a new ide to work like an old one (instead of learning to use the new one with its features) is an exercise in frustration. i regularly switch between Visual Studio and SSMS, which, though somewhat alike, are different enough with their features that sometimes VS is the natural choice and sometimes SSMS. Both support Alt-Select which I love. VS has the sort of refactoring tools that you speak of -- they're actually quite good, I think.

The built-in source control is pretty important to me, FWIW VS has an SVN plugin (Ankh SVN is one) that is great if you don't use TFS or GIT.I guess for me VS is a natural choice since my projects frequently combine C#/VB code as well as SSIS and SSRS. For those 3, VS is really the only choice.

I do like Visual Studio database projects. Not perfect but definitely a decent (and free) choice.

17

I thought i was the last dinosour, looks like i am not :grin:

Long before moving to SSMS, i have tried several other alternatives but none of those I really like it and may replaces the good old QA (having used it for over 10 years). When my Windows 10 laptop came, i have no choice but to use SSMS.

Same here. I have my own version of "swiss army knife" :slightly_smiling:

With the laptop screen resolution is getting higher, font getting smaller, age increasing, eyesight decreasing, I would love to have that. Too bad, i move around a lot, it is not possible to carry that with me wherever i go :pensive:

I tried it many years ago. At that time, SSMS was slow and adding the ToolsPack on it is making it worst. I have been using SSMS for few weeks now, overall SSMS seems much faster now, I will definately give the ToolsPack a try.

Very true. the new tool does has much more new feature to get the work done. I guess it is time for this old dog to learn new trick. :slightly_smiling:

18

Yes, high time I changed. Looks like I'm one step behind KHTan, so I'll turn out the lights as I go!

Given that I have to change then I am going to look around for the best of breed tool - this old dog wants to learn the best new-trick in town!. I very rarely have to use something other than my Desktop PC, and including my Lapytop in second place I almost never have to use SSMS (or TOAD or somesuch!!) on a client site, so I am fortunate in being able to choose a tool-of-choice and can favour "maximum similarity" and "maximum productivity gain" in that choice. The two factors may be mutually exclusive though!

I'm going to have to look that one up. I should probably Google for "Top Tricks for SSMS" too

Does that mean that every time you SAVE it is checked in? or does it only checkin when you say so? (in which case I suppose, unlike me, you don't need to have a Local Copy of all source code, you can just get files from Repository as you need to work on them?). My approach is based on years of travelling with laptop before mobile internet was available / on aeroplanes, and local copy of source meant I could work anywhere, and then CheckIn when I was next connected. As a self-employed person it also means that all my source code is on multiple devices, which gives me a little extra insurance.

Not something I have to grapple with. I have an IIS "engine", it just takes the output from SQL Resultsets and "renders" them on web pages. It uses Content Management templates to format / substittute resulset columns into HTML, and is able to take "hints" from the ResultSet. For example, if a Column Name starts "GUID" it will use the GUID value to find a snippet of template in the CMS and substitute that instead of the column. Thus I can do:

SELECT CASE WHEN MyColumn =1
            THEN @ItemTemplateGUID
            ELSE @TotalTemplateGUID
            END as [_GUID_Template],
       MyColumn1,
       MyColumn2,
       ...

and the Engine will use the appropriate CMS Template which is then populated with columns from the remainder of the resultset. (A template is specified for the whole resultset, so this is only used if there is a need for an Either/Or section within the output's template)

Consequently I write SQL 99.999% of my time, and modify the Engine [application code] only when I have a brainwave that it can be improved. So a dedicated SQL tool will be better for me than a choice made on the need for a composite IDE

Perhaps we should swap notes in another thread?

My laptop is quite old, but at the time I chose it based on a) largest screen available on a laptop, and b) proper keyboard with no compromises on F-keys and a proper Numbers Pad. It does have some goofy arrangement for PageUp/Down though, which catches me out every time ... still ... years after I started using it!

Of my two teenagers one is iPad and the other Surface. The screen resolution of the Surface is impressive, she uses it for two A4 pages side-by-side although, as you say, that works for her young eyes! I think I might consider that, for its fantastic portability, as my next laptop replacement.

19

I could do with learning how folk work in SSMS for the following situation (assuming that you do HAVE this situation!)

Open SProc1.SQL Modify some code / save / DROP/CREATE or ALTER.

Need to run some TEST code. 1) How do you do that? I have a /* UNIT TEST */ comment at the bottom of the code with an EXEC statement (within BEGIN TRANS / ROLLBACK) so I highlight and run that. That still works fine in SSMS - highlight the UNIT TEST code and EXEC it, but maybe there are better ways?)

Then I find that there is a problem in a different SProc, called from the first. So I open SProc2.SQL. For me that is in the first/same Window. The results are not cleared (SSMS Clears the results when I set it up to reuse a Window on File Open :frowning: for me a New TAB would have the same problem that I then can't see the results)

  1. I think this is my key problem, how I then "locate" that second window / TAB in SSMS (when there are several open, not just a couple :slightly_smiling: )

Then I want to just type SELECT ... to check something, or I want to use my Swiss Army Knife SProc (as KHTan called it :slight_smile: ) to generate some code to use in my Sproc. I probably do that inline, cut & paste the code I need to replace the EXEC sp_ArmyKnife command, but I might also want to use a Scratch Pad window. That could, of course, be another TAB but in SSMS I find it very difficult to find that TAB when I have, say, 10 or so TABs open. In QA its one of the three windows that I have in Restored-state and thus overlapping so I can click on a bit-that-is-sticking-out (the other, not-currently-in-use 7 windows would be Minimised until needed). My JUNK and ScratchPad windows are always Windows 1 & 2 so I can also use the Window Menu to toggle to them - Alt-W,1 or Alt-W,2. My main Project Window (the thing I am supposed to be working on today!!) is always Windows-3.

Seems to me in SSMS that TABs grow like Topsy, and I have to spend time figuring out which ones I can clean up from time to time, and using Control-TAB to select windows rarely seems to work for me - the Active Files right-hand-half of the pop-up shows so little of the filename whereas in QA using Alt-W to display the window list uses the whole width of the screen, if it needs to, to fit the filename in. PLUS it has keyboard shortcuts 1,2,3 ... next to each one which saves scrolling / mouse-picking.

If I could find a solution to the "Toggle to the right TAB efficiently" issue, that might solve the issue that is holding me back in SSMS

20

When I say so