SSMS How do I open a new Query Window using Same Connection Instance of current Window

Question

Coming from Oracle I have discovered that SSMS connects slightly different than Oracle's SQL Developer. Oracle you click ONE connection and then all tabs you open then use that Connection. SSMS creates a completely new Connect/Instance for each window/tab I open. I don't want this. TWO pet peeves come to mind with this behavior I am trying to get around.

1. View affect of Script before COMMIT in another Window

I want to be able to run a script in one window... and in another Window have the ability to Query the Non-Committed data to simply view the results... Currently I have to use the SAME Window I ran the script in order to simply VIEW the affect. (Commit/Rollback buttons come to mind - Oracle I miss you sometimes).

2. View data stored in #TempTable from another Window

I am creating a #TempTable using a script... but want to query it's data from another Window. Currently when I open another window (Unlike Oracle's SQL Developer) it opens the window using a new connection/instance. How do I query the data in #TempTable from another window rather than the window I ran the script in?

The above is just simply for organizational reasons... it's SUCH a pain to run a script... and then from that same tab have to then write in other code when I don't want to edit/add any other code to the page that contains the script I ran.

If I'm doing something wrong or missing some Point please let me know.

Answer 1

View affect of Script before COMMIT in another Window

You can use Table Hints such as WITH (NOLOCK) or WITH (READUNCOMMITTED) to perform dirty reads of uncommitted data between two windows in SSMS.

View data stored in #TempTable from another Window

You can use global temporary tables (##Table instead of #Table) to query temporary tables across sessions. Note that you must create the temporary table as a global temp table to begin with.

The above is just simply for organizational reasons... it's SUCH a pain to run a script... and then from that same tab have to then write in other code when I don't want to edit/add any other code to the page that contains the script I ran.

If I'm doing something wrong or missing some Point please let me know.

A simple way is to write your entire script, including the queries to check/test your data changes, and actually highlight the individual portions you want to run and execute them.

Take the below code sample. I want to insert two records into a table, validate the result and then commit if I am happy or rollback if not:

BEGIN TRANSACTION
INSERT INTO TableA (ID, Value)
VALUES (1, 'Value 1'),
 (2, 'Value 2')
SELECT * FROM TableA
COMMIT
ROLLBACK

I would put the entire code into a single window, highlight lines 1-5 and hit Execute/F5. The data is inserted but not committed, so I then highlight line 7 and hit Execute/F5 and verify the results. If I am happy, I highlight line 9 and hit Execute/F5 to commit the changes or if I want to rollback I highlight line 11 and hit Execute/F5.

Also, note that if IMPLICIT_TRANSACTIONS is OFF (default) then your session is basically using 'autocommit' which means without an explicit BEGIN TRANSACTION, the INSERT statement would commit automatically.

• Code Novice: Thank you for your detailed answer. It may not be the answer I hoped for but it is the correct answer. What you described is how I've been working within SSMS. Oracle's DB Client (SQL Developer) allowed you to separate SQL on a single page using semi-colons - No need to have to highlight text. Just place cursor and run and it would run ONLY the code where the cursor was... and of course the very nice added benefit of just being able to keep my code organized across tabs... the tabs in SSMS are long and very rarely fit on one screen so I find myself trying to find the tab I was using.
• HandyD: You could take a look at Azure Data Studio. I've only used it a little as I still mainly work in SSMS or VS so I can't comment on whether it will solve your particular issues, but it may provide a UI you're more comfortable with/is more suitable for your use cases.

Answer 2

If I'm doing something wrong or missing some Point please let me know.

You're not missing anything. SSMS always has a separate session per tab.

it's SUCH a pain to run a script... and then from that same tab have to then write in other code when I don't want to edit/add any other code to the page that contains the script I ran.

In SSMS the Execute command (F5,Ctrl-e), always runs the highlighted text in the query window, if there is any.

And so the normal way people use SSMS query windows here is to highlight sections of code to run, often separating the main script from the test/debug bits by using multi-line comments.

For instance when developing a stored procedure, then query window might look like:

create or alter procedure SomeProc @param int
as
/*
 declare @p int = 12
 exec SomeProc @p
*/
begin
 ...
end

Then run the whole script to recreate the proc, and highlight the test harness lines and run to test it.

• Code Novice: I'm curious if ALL clients behave in this manner? For example... I have been looking into DataGrip to use rather than SSMS. A friend of mine swears by it. Do you have any experience with other clients? jetbrains.com/datagrip
• Code Novice: I have also learned that I can view uncommitted script affects from another Window if I use the WITH (nolock) table hint. So if I were to update the Client Table... I can view the results prior to COMMIT by doing this... SELECT * FROM Client WITH (nolock). With the understanding that this potentially leads to 'dirty reads' I can at least use this in some testing scenarios and I have already found it useful. From what I know Oracle has no such keyword.
• David Spillett: @CodeNovice - to see uncommitted changes for the entire session in a tab, I would use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED instead of adding NOLOCK hints or similar in every statement. Standard warning (which you are no doubt aware of as it is pretty obvious, but it is worth restating just in case): remember not to completely rely on results read this way, as active transactions may end up being rolled back instead of committed.