Add a little more love to your love/hate relationship with Intellisense
Update: Shortcut for enabling Intellisense changes in SSMS 17.2
Since SQL Server 2012, the default shortcut to enable/disable Intellisense has been CTRL + Q, CTRL +I. (“Control Quacky Intellisense”)
In SSMS 17.2, this changes to CTRL + B, CTRL +I. (“Control Baffling Intellisense).
Update: Intellisense now works with Azure SQL Database
If you run SQL Server Management Studio version 17.2 or higher, you can use Intellisense with Azure SQL Database. (This was released in August 2017.)
Transcript
Intellisense: I have a love/hate relationship with it.
You have to be careful with it– and I do disable it, especially on really large scripts– and I’ll tell ya why. But I do like using it sometimes.
You can easily turn Intellisense on and off
There is a button to do this, right up here, it’s this button with the highlighting around it. And it says, hey Ctrl + Q followed by Ctrl + I will toggle Intellisense on and off. Whenever you see a comma like this in the middle of a shortcut command that is what’s known as a chord. This is our first shortcut chord, so we’re going to turn Intellisense off, it’s on right now.
I’m going to hit Ctrl + Q. And notice here in the bottom of the screen, it lets me know that there’s something interesting going on. It says, hey you pressed the beginning of a chord, a lot of our chords start in Ctrl + Q.
The second part of this one is Ctrl + I. And I think of this as “Control Quacky Intellisense.” I’m going to go ahead and do Ctrl + I. That’s the second part of the chord.
The little highlight on this icon went off when I did that – you can use the button. I like using the keyboard and I like doing that chord.
I’m going to turn it back on, I just did Ctrl + Q and now with Ctrl + I, Intellisense is back on for my session.
Intellisense will correct capitalization/case within a session
It will correct case, so I have capital M and capital A. Let’s say I type an ’s', and it says, I’m going to suggest the master database.
Well that’s great, let’s go for it. I’m going to hit Tab, to auto-complete the name, and it actually does correct my capitalization. This is one of things I love about Intellisense. It’s great with things like tricky capitalization and things that are hard to get right.
If my script is run on someone else’s case-sensitive instance, I certainly want it to run. Which is why I make my test instances case-sensitive.
So that’s really cool.
I can also arrow down through my Intellisense suggestions
Let’s say I want sys.databases_files, this says sys.dat, I’m going to type ‘a’, and now I’ve got a list of suggestions.
The Down and Up arrows let me to navigate between them. I’m going to hit Tab to select database_files, and then go ahead and run my query to make sure it works, and it does. I’m going to do Ctrl + Z to Undo that and leave my script in the “ready to play” mode, so you can play along, too.
One of the most confusing things though about Intellisense is Completion mode versus Suggestion mode
It’s actually fairly easy once you know what to look for, to tell the difference between these two.
What you’re going to look for is whether or not a suggestion is fully highlighted or if it is just outlined.
Suggestion mode
Here we are back at ‘sys.dat’. Let’s say, I want to type the name of something that doesn’t exist, but there are things that are named similarly. And maybe, it’s something that Intellisense just doesn’t know about.
Maybe it’s in a different database context, maybe it’s something I want to create in the future. Who knows what it is, but let’s say I want to type sys.database, just one word. This doesn’t exist, there is no sys.database, but I want to type it in.
Well, okay, sys.dat, I type ‘a’, I am currently in Suggestion mode. See how data_spaces is? Just has an outline? Suggestion mode is showing me suggestions but it isn’t committing me to them. And in Suggestion mode I can type sys.database and then hit Space Bar.
I had suggestions but it didn’t force me into them, I like Suggestion mode a lot. But you may default to, or actually be in, Completion mode, which acts differently. And I personally don’t like it as much.
Completion mode
We can toggle between Suggestion mode and Completion mode with Ctrl + Alt + Space Bar. So I have to do Ctrl, I have to do Alt and I have to do Space Bar. There is a menu option for this too, but in shortcuts it’s Ctrl + Alt + Space Bar. And in Suggestion mode, now, sorry– in Completion mode, this is Completion mode– I have a blue box, not just an outline.
Now that I’m in Completion mode, I’m going to type sys.database, this thing that doesn’t exist. Since that is highlighted, something’s always highlighted in Completion mode, if I hit Space, ah, it completed it for me.
Sure, I can undo it, with Ctrl + Z, but if you’re in a pattern where you are running into this a lot and Intellisense keeps completing something you don’t want it to complete, you might be happier in Suggestion mode.
Those frustrating underline squiggles and refreshing Intellisense cache
Sometimes I get frustrated with it because all of these red underlined squiggles, right? Like, oh, it just makes everything look so wrong. Well, this looks wrong for a reason.
I can refresh my cache all I want – and you can use Ctrl + Shift + R, to tell Intellisense, hey go refresh your knowledge of what the objects are – But the thing is right here, I have a ‘use master’. Intellisense understands that, and what it’s doing is it’s looking for this table, and it says, hey that’s not in the master database. It is totally right to have that underlined in red. It’s also not in the BabbyNames database. This table is in the WideWorldImporters database.
If I tell it the correct database context, Bam! Behind the scenes, after I changed the database context, a separate thread for Intellisense went out and refreshed and said, hey that IS there, I see that object, yeah that table does have those columns. I didn’t even have to hit Ctrl + Shift + R to refresh the cache, it just did it automatically. Sometimes it doesn’t do it as fast as you want, and that’s fair, this is a background process. We don’t want our Intellisense thread having top priority, right?
I’m going to switch this back to the other database and I’ll hit Ctrl + Shift + R, I think it may have beat me to it and said, hey that doesn’t exist there.
Intellisense uses a second thread, so things get weird if you use the DAC
One of the weird things about this separate thread for Intellisense though is, it can look kind of funny and it can mislead you at very critical times. It’s good to know that that separate thread doesn’t always work. There’s a special session in Management Studio – not in Management Studio, there’s a special session in SQL Server called the Dedicated Admin Connection. A lot of people call this the DAC.
And the term DAC means multiple things in SQL Server, but the one it mainly means is the Dedicated Admin Connection. SQL Server keeps memory available and a little bit of CPU so that if your system is under high pressure, it’s like the reserved entry where you can get in and you have a few reserved resources so that you can see what is going on in the SQL Server. Even if everything else is under a ton of memory pressure and there isn’t much CPU available, you can run a few queries on the Dedicated Admin Connection. Only Admins can use it, only one thread can use it at a time.
You only want to use this periodically, and you don’t want to, you know, hog the DAC. But, you also want to know that there is this weird error that can show up when you connect to it which is a false alarm.
What I’m going to do is connect to the DAC. I’m going to hit Alt, to go up to that menu at the top, and I’m going to hit Q, to go to the Query Menu. And I’m going to type C to expand Connections, and now H to Change Connection.
This is suggesting that I connect to my instance, which is named FASTER01. But I want to connect to the DAC so I’m going to type, admin and then colon. I want to connect to the dedicated admin session on FASTER01.
When I click Connect, hey, the first thing I see is a big, ole, error message that says I couldn’t connect to the DAC. This makes it look like this failed and that maybe somebody else is using it. Oh no! what’s going on? I hope, I hope I can get in!
Well, before you freak out, look down at the bottom of your screen, because it looks like, after all, I am connected to the DAC… and I am.
My session connected, and then Intellisense tried to connect right after me.
This error is actually for the Intellisense thread. I can prove with this query that looks at sys.dm_exec_sessions, as well as sys.endpoints that the Session ID connected to the Dedicated Admin Connection is Session ID 69, and in fact, my Session ID is 69.
It’s just that Intellisense couldn’t connect after me. And Intellisense isn’t going to work in my session while I’m connected to the DAC, either. Whether or not that little button for it is highlighted, it just doesn’t have a thread.
Intellisense may hog CPU on the computer running SSMS
I have had some cases– and I’m going to go ahead and hit Alt, and change out of this while I describe it – I have had some cases where Management Studio seemed to be driving up my CPU. I would often notice this by hearing the fans on my laptop kick on.
I look and I say, who’s using all the CPU? And I see that it’s Management Studio. In those cases, in Management Studio I was often working on a really large script. When I toggled Intellisense off – which you can do with Ctrl + Q and then Ctrl + I – shortly after I did that, my CPU usage dropped way down.
Because of situations like that, when I’m editing large scripts, I prefer to not have Intellisense on.
And really I always prefer if I’m editing a large script to not be doing it against production right? So you got to be careful out there, it is a great tool but use the force wisely. And know that it’s not currently available in every situation.
You can’t use it right now against Azure SQL Database, but you can vote on this Connect item if you think you should be able to. <– Update, this now works with SSMS 17.2 and higher.
And I hope it’s coming, because I do think that Intellisense is a valuable tool. Especially if you’re like me, and you don’t like to capitalize everything yourself.