How to turn off IntelliSense in SQL Server Management Studio

IntelliSense in SQL Server Management Studio is beneficial to some, but irritating to others. For me, when I type out a line of simple code, IntelliSense will spam me with suggestions and actually cause my script to become gibberish if I ignore the suggestions. The suggestions will automatically be selected if I type too fast and don’t hit the escape key every time a seemingly meaningless IntelliSense option is selected. I will note that in SQL 2014 IntelliSense has become better, and I do use it now; however, while working in 2008R2 it was a major PIA and I always disabled it. Here is the way to turn it off, for good, without having to hit the IntelliSense Button in the SQL Editor Toolbar every time you load SQL.

IntelliSense Button in the SQL Editor Toolbar

Turn off IntelliSense

·         From SSMS go to the Tools Menu, select Options

·         Under Text Editor, Transact-SQL, select IntelliSense

·         If Enable IntelliSense is selected, clear the checkbox and hit OK.

Using INTERSECT and EXCEPT to compare records

I was asked for a simple way to compare two tables that should be identical and have the same schema. I provided the following scripts to show records that are identical (Intersect) and different (Except):


IF OBJECT_ID(N'dbo.test1') IS NOT NULL
    DROP TABLE test1
CREATE TABLE dbo.test1 (name VARCHAR(20), dob DATETIME)

IF OBJECT_ID(N'dbo.test2') IS NOT NULL
    DROP TABLE test2
CREATE TABLE dbo.test2 (name VARCHAR(20), dob DATETIME)

INSERT INTO test1 (name, dob)
        VALUES ('Fred','20010102')
INSERT INTO test1 (name, dob)
        VALUES ('Fred1','20010103')
INSERT INTO test1 (name, dob)
        VALUES ('Fred2','20010104')
INSERT INTO test1 (name, dob)
        VALUES ('Fred3','20010105')
INSERT INTO test1 (name, dob)
        VALUES ('Fred4','20010106')
INSERT INTO test1 (name, dob)
        VALUES ('Fred5','20010107')
INSERT INTO test2 (name, dob)
        VALUES ('Fred','20010102')
INSERT INTO test2 (name, dob)
        VALUES ('Fred2','20010104')
INSERT INTO test2 (name, dob)
        VALUES ('Fred3','20010105')
INSERT INTO test2 (name, dob)
        VALUES ('Fred7','20010106')
INSERT INTO test2 (name, dob)
        VALUES ('Fred5','20010107')
    
--exist and are the same in both tables test 1, not in test 2
SELECT name, dob
FROM dbo.test1
INTERSECT
SELECT name, dob
FROM dbo.test2

--exists in test 1, does not exist in test 2
SELECT name, dob
FROM dbo.test1
EXCEPT
SELECT name, dob
FROM dbo.test2

--exists in test 2, does not exist in test 1
SELECT name, dob
FROM dbo.test2
EXCEPT
SELECT name, dob
FROM dbo.test1


As you can see by running the script above, intersect shows values from the first query where there are identical record matches in the second query. Except shows values in the first query where there are No identical record matches in the second query. Additional benefit is that it also includes null values in the comparison, you do not get the same functionality with null values on joins (when you just enter the column names) from 2 tables (without adding specific null handling).