Have you tried the option to send results to a new tab (which you can then float if you like)?
Didn't know about that (and problem loads more such things too ...). Sounds like that might be helpful.
Sadly I'm not getting very far with SQL Analyser's Editor. Its got loads of things that would help productivity, but its MDI is defined as "documents", not "windows", so no way to reuse an existing window for something else and so on.
Have you tried using Visual Studio as an alternative? Granted it's not much use for admin. I think it is a good SQL IDE though.
Not got that installed (not sure I own it as part of anything I have received recently). Presumably not just a freebie download?
It is a freebie. You can get VS 2015 Community Edition then add SSDT (data tools) to it.
Great, I'll give it a go, thanks for that.
I'd like to discuss "how I work" more in case anyone can point me at a better way of doing things. Can't think of a way to do that,l short of making a YouTube about how I work and getting a whole load of filthy "Why the F would you do that" type comments from the YouTube community at large [VBG]
I'd also have to make a whole folder of non-sensitive-filenames, and some non-sensitive-code that I could display without upsetting any slients, so not entirely trivial as a mini-project.
I'm thinking that my way of working may be something I've evolved over decades, because of the way the QA did stuff, and maybe there is a far better alternative that everyone else is doing in SSMS (or VS)
I'm also curious whether KHTan's work-style has evolved to look exactly the same as mine 
I mentioned Redgate before, someone followed up with a question, here's how I'd reply:
It's certainly worth checking out, and you can purchase individual components rather than the entire suite.
Thanks Rob.
My initial issue is trying to get a working environment / IDE that I am comfortable with.
I have used SSMS a bit, when on client site or Remote Connected to a server etc. and I've always wound up with lots of TABs and found it a chore to close them (i.e. working out which ones I'm still needing [but have not necessaryily changed anything], and which ones are now redundant)
Part of that is the full-path style that SSMS appears to adopt (compared to QA) - which means that pop-up lists of WINDOWS don't show me enough - its like trying to manage a rocket-ship through a letter-box!
beyond that, I have some imagination that there will be lots of Tools that will help productivity ... but right now I'm really struggling with the amount of time it seems to be taking me just to work, in a practical sense, with only half-a-dozen-files.
I am guessing that either this is not a problem for other people (they live with it, and have never considered how "expensive" it is), or they just don't work like that (in which case this Old Dog could definitely learn a New Trick)
Here's how my working day looks
Background: I have each SProc, Trigger and View in its own file.
I don't RightClick & Edit an SProc in SSMS because a) I prefer Files for Version Control and Rollout (to QA and PROD) and b) at the top of each file is an EXEC statement which "logs" that the script was run (date/time, name, and "Version")
At the bottom of each file is a comment with a Test Rig - e.g. some Unit Tests, and also a bunch of SELECT TOP 10 for the various tables, and JOINs, that enable me to have a quick look at some data, or to modify those "Sandpit Selects" to change the WHERE to find some "rows of interest"
Right, so now I want to do some work:
I open the file containing an SProc that I want to work on. At some point (either because I'm trying to fix a new problem, or because I've just added a new feature) I run the Unit Test and I get some output in the Results Pane.
I have a @Debug parameter on all SProcs. If I set that to 1 then I get a whole load of extra SELECTs outputting debug info interspersed with the actual output (in the results pane). Each of those Debug statements has a column name matching the SProc Name and a value of "DEBUG(1)" with a [not enforced] unique ID
I might get a runtime error too, of course.
Now I want to fix the runtime error, or make the darn thing work PROPERLY!!
That often causes me to want to:
Open a different, child, SProc and look at that. Currently I do that in the same Window, and retain the existing Results (which enables me to find the point in the code where I want to make a change).
Instead of a child Sproc I might want to open the source code for a VIEW that is being used. I might open that in a different Window, but I tend not to (because I then have to close that window at some point). The original SProc's filename is in my file history, so i can easily do Alt-F, 2 to get back to the earlier file. I realise that I lose "position" in the original file when I do that, but my files are not huge, and as all my DEBUG outputs are uniquely labelled I can easily do a Find "DEBUG(7)" to jump to that location in the code
I am also very likely to run my Swiss Army Knife [as KHTan calls it ] Sproc - that lets me get a list of columns, formatted to my liking, or the source code of a View to examine, or the parameters of an SProc, or the Indexes on a table that contain a Column (if I am doing some performance tuning)
So I might well run a Unit Test, a SELECT TOP 10 ... to see what the data looks like, my SProc to get Column / Index / whatever info
And I may well open This File and That File, in the current window, whilst sorting out the Feature / Bug that I am working on.
Do other people do something similar? or do you do that in a different way?
How do you cope with millions of tabs - or does that not happen for you?
How do you "see" the results from a Unit Test whilst you are working in the various related files / windows?
I gave SQL Assistant a good go, but I was trying to use their editor - however I failed to take @jeffw8713 advice and hook it up to Notepad++, maybe that would have given me the editing environment that I want (I've heard of Notepad++ but never used it). I spent a lot of time trying to customise SQL Editor to the way I want to work, and to mimic Query Analyzer, and in the end I gave up, Their Support folk / Forum were very helpful, but their definition of MDI is to open each document in a new window (now I come to think of it perhaps QA is the only APP I use where I use "New Window" and "File Open" to control which-file-in-which-window, all my other APPs do File-Open into a new window (except for the option in SSMS to reuse a window if saved, which means you can't force a File Open to be in a different window ... so ... I couldn't figure out how to do DESIGN on two tables, side by side, for example as the second DESIGN reused the first window ...)
But the rich editing features in SQL Assistant caused me to realise that there might be much higher productivity tools out there, and I'm definitely up for that 
@khtan you might like to have a look at (freebie) Atlantis SQL Everywhere which is QA-like. AFAIK it won't do New Window / File Open though. But it does have several useful features
Group Databases by Function, as well as by Server
Several useful Intellisense type features
Run SQL Statement / script on multiple databases - e.g. for a rollout
Data Edit in Results window (without overhead of SSMS)
Record / Playback (/ save) keyboard macro
RegEx find & replace
So I browsed with a wider net:
Jetbrains DataGrip GBP69.00 - 149.00
Object navigator / designer. A feature that might appeal to PROs is that you can design a table with a grid (column name / type / Default / IS (NOT) NULL etc). and simultaneously the CREATE (or ALTER) syntax is displayed in a separate pane. Navigate to location in Object Tree from RightClick on an object within code or using a global FIND.
Modify data in results pane with with auto-commit or all changes in a transaction with rollback option. Filter results pane (without having to modify a WHERE clause). Also lookup related data via any defined FKey (and in opposite direction). FIND will highlight all matching text in resultset
Intellisense: complete JOIN using FKey definition (maybe that exists in SSMS?). Other auto-complete stuff seemed same as other editors.
rename-within-block - I was taken with this in SQL Assistant - select a block of code, change a variable / column names and it highlights and changes throughout the code block. Also a refactoring rename across all code.
Not sure how useful it would be ?! but if you use a column name that doesn't exist in a SELECT you get the option to modify the table. Bit like the COME FROM statement I always wanted back when I programmed in Basic!
Code formatting ... its second nature to me to indent and style my code just as I like it, as I write it, but the DEMO suggests just writing the code left-aligned and then using Format once written to sort it out. Not sure I could unlearn my style-habit!
Smart-Select - select word, sub-statement, and continue pressing to select wider areas of "blocks" of code.
Multiple cursors - several editors have this, not sure I would ever use it. Example given is 4 lines of address all with VARCHAR(30) which needs changing to VARCHAR(40), put cursors on all the 3s and then type the multi-change to 4s. I reckon, given that my code would be vertically aligned, doing DEL, 4, LEFT, DOWN repeatedly would be as quick as positioning multiple cursors and, for a bigger change, FIND-REPEAT and PASTE does as good a job ... but I haven't tried it in anger. I hate things that make changes outside the displayed code as there are always side-effects, using FIND_REPEAT and PASTE allows me to see & decide if the Paste would screw things up ...
Compare two resultsets. CSV file "edit as table". Version control integration.
Connects to a range of different DBs
I also had a look at Aqua Data Studio which seems to have some of those features, but not all, and I couldn't see any advantage,
I thought that Apex SQL had an IDE editor, but if they have I can't find it on their site. They do appear to have a freebie Intellisense plugin that looks to have some nice features - perhaps similar to Redgate's SQL Prompt?
http://www.apexsql.com/sql_tools_complete.aspx
However, the one that I am most excited about is Cardett Associates Advanced Query Tool US$ 180 / $360 (extended version)
It has all/most of the items above and appears to have a lot more besides.
Results Window allows individual rows (i.e. with complex/wide/multi-line columns) to be viewed in a popup with a vertical Column name / Value grid. Resulsets can be displayed with expand/collapse grouping.
In addition to inline edit it can generate a script of changes, and has options to Alert if many rows affected, or to require confirmation of the number of rows affected before commit. Might save me from having to type BEGIN TRANS / ROLLBACK around every test statement!
Graphical Query Builder - I would have thought I didn't need this, since the days of Query by Example ... but it has the ability to show (distinct) values found in columns and so on, which could be helpful and save some adhoc SELECT DISTINCT ... show-and-tell statements, which must be less efficient ...
Export Data in lots of format which, together with a very comprehensive Charting ability, may mean that adhoc data provision to users (or even users building their own) might work well. Also a bulk data loader - don't know how that would compare with BCP / SSIS etc. but I'll be interested to have a look. I have a fair amount of Day One one-off data loads migrating from old systems that don't warrant complex systems that might be good candidates for Cheap & Cheerful.
In addition to Data Compare there is also Object Compare - perhaps similar to Redgate's Database DIFF tool.
They have a 30-day trial, so I'll give it a go this week and report back
Tried that. Doesn't like it for whatever reason i can't remember now 
1 Like
Been using them for a month or more now. Very pleased with them, they replaced a pair of 1280 x 1024. I had them Landscape to start with, side by side (desk is big, but barely big enough!!) ... it was like watching a tennis match and would probably have given me neck ache. Also, any site which did not limit line-length-width was impossible to read, and any site that did meant I wasted most of the width anyway ... except for Excel spreadsheets and SSMS Data Grids ...
So I turned them both Portrait.That gives me a width of 1440 and a height of 2560, and basically the way I am now working is using four windows - top and bottom of each screen, generally with the windows overlapping - i.e. each window is, say, 2/3rd of the screen height. Editor tools on the left, APP on the right. I use the windows at the top for less commonly used stuff (more head-rotate to view the top, most comfortable eye-line is slightly downwards, so towards the bottom of the screens) - on my Right screen I have Dev version of APP at bottom and Test / Production version at the top.
I have found that the width is plenty for most situations, but if I need a wide display (Excel / SSMS Data Grid) I can just drag the window across both screens (just tried Excel at default column width and I can get 44x Columns A:AR visible)
No idea if that needed 4K flavour of screens, or not. I was worried that, with my ageing eyes (glasses required now for reading) that the font would be too small, but it actually looks fine to me (I was already wearing my reading glasses when working at the screen)
1 Like
thanks! I'll try it