It would be very handy if there was a way when you open a server in Object Explorer that it pops up with notes about that server. For instance, there is a job we have on EVERY server in our environment except one random server that no one hardly thinks about. But if that job is there, then it messes up things. It would be great if when anyone on our team who has redgate sql prompt opens that server they get a reminder that we add on there that says some special note (in this case “DO NOT ADD AP Job!!!”). It could even be stored in like the extended properties for the server and SQL Prompt just reads that first when it’s opened. An optional feature on top of that, would be anytime you open that server as a new query window. I’d say optional, because some people may not want to see that everytime. But that would help as well when people want to see notes, but they never use the GUI and just write out all their code.

https://www.brentozar.com/archive/2011/06/fixing-sql-server-management-studios-tab-text/

Brent Ozar wrote this tip back in 2011 and I cant believe i've been struggling with the horrible SSMS tabs every day for my whole career. would be great if SQL prompt has a button to change these settings for you or defaults these options when you install it.

the main reason being that they are buried away and if I wasnt aware of them then i'm sure many others aren't.

at the end of every day i have about 10 tabs open and i just close the whole lot because it's too hard and to go through them all quickly and i usually have the kids shouting at me for their dinner :)

When joining two tables, you today get the option to join on known references or identical column names.

What I would like to see is an extra join option in the suggestions, if both the columns are NULLable:

SELECT * FROM table1
INNER JOIN table2 ON (table2.ref1ID = table1.ref1ID OR (table2.ref1ID IS NULL AND table1.ref1ID IS NULL)

The above example might seem like rubbish at first! But sometimes when you have composite keys, one of the columns in the key might very well be a NULLable value.

So a more valid example would be:

SELECT * FROM table1
INNER JOIN table2 ON table2.comp1 = table1.comp1 AND (table2.ref1ID = table1.ref1ID OR (table2.ref1ID IS NULL AND table1.ref1ID IS NULL)

There's an obscure coding bug that I've seen multiple times in my 20 years of working with SQL Server, and it can be particularly difficult to spot as it doesn't cause a syntax error, yet the havoc it wreaks can be widespread. Consider the following scenario with two tables; they are similar but have differing column names. Now say I want to insert records from StagingTable into FinalTable, but only if they don't already exist there. Yes, there are other ways to handle this like Merge, Left Outer Join etc., but this is still commonly used and you will no doubt see it in the wild.

DROP TABLE IF EXISTS dbo.StagingTable;
CREATE TABLE dbo.StagingTable (Col1 INT, Col2 INT);
INSERT dbo.StagingTable (Col1, Col2)
VALUES (1,1), (2,2), (3,3), (4,4);

DROP TABLE IF EXISTS dbo.FinalTable;
CREATE TABLE dbo.FinalTable (Int1 INT, Int2 INT);
INSERT dbo.FinalTable (Int1, Int2)
VALUES (1,1), (2,2), (3,3);

--Insert new rows from stagingtable into FinalTable if rows don't already exists there.
--In this scenario, we should insert 1 row with values (4,4)
INSERT dbo.FinalTable (Int1, Int2)
SELECT Col1, Col2
FROM dbo.StagingTable stg
WHERE NOT EXISTS (SELECT * FROM dbo.FinalTable WHERE Col1 = stg.Col1)

--FinalTable still only has three rows, and (4,4) isn't among them.
SELECT * FROM dbo.FinalTable

Now, before reading on, run the script above and see if you can figure out why the new row isn't being added.

In the NOT EXISTS subquery (anti-semi-join), the coder assumed that Col1 exists in FinalTable, but that isn't the case. One might expect this would be cause for a syntax error, but in reality, Col1 DOES exist in the scope of the overall statement, so SQL Server assumes you know what you are doing and executes the query without error, but also without inserting any new data. It's the same as a subquery having a predicate of WHERE 1=1 which of course isn't the desired behavior. The simplest way to avoid this is to ensure that all columns are qualified with table name or alias. When qualified, SQL quickly notices that something is wrong, and points out the error either at parse-time or run-time.

My suggestion is for SQL Prompt to detect subqueries and call them out IF they use unqualified columns. I'm not suggesting a call-out of missing qualifications in ALL queries as it isn't always required, but by checking subqueries, it might go a long way to preventing this in the future.

The simple fix to the insert statement above is of course the following:

INSERT dbo.FinalTable (Int1, Int2)
SELECT Col1, Col2
FROM dbo.StagingTable stg
WHERE NOT EXISTS (SELECT * FROM dbo.FinalTable ft WHERE ft.Int1 = stg.Col1)

It would be very convenient when going through legacy scripts to be able to hit a shortcut combination to switch the order of an equality statement, for example in a JOIN clause

For example, with the cursor on "A.ID = User.AccountID":

SELECT U.Username
FROM Account AS A
INNER JOIN Users AS U ON A.ID = U.AccountID AND U.Active = 1
WHERE A.ID = @AccountID AND U.ID = @UserID

Becomes
SELECT U.Username
FROM Account AS A
INNER JOIN Users AS U ON U.AccountID = A.ID AND U.Active = 1 --note, only clause where cursor is is switched.
WHERE A.ID = @AccountID AND U.ID = @UserID

This would then compliment the casing/qualifying/wildcards/semicolons shortcuts you already have which massively speed up sorting out old queries.