3

I use SQL Server 2016 Developer Edition on Windows 10 64 bit. All the databases are local on my laptop.

I have a SQL script file, and Intellisense doesn't work on it. I have enabled Intellisense and tried various fixes suggested in this post:

IntelliSense not working but it is enabled

None of these fixes work for me; however, Intellisense works on all the other files. The only special thing of this file is its length: it has more than 600 lines of code.

It seems that the problem is caused by a command creating columnstore index. Here is a sample code:

SELECT TOP 100 *
INTO #test1
FROM tab1

CREATE CLUSTERED COLUMNSTORE INDEX idx ON #test1

SELECT TOP 100 *
INTO #test2
FROM tab1

I found that Intellisense stopped working after inserting the block of code in the middle! The outlining will also disappear after inserting the middle block of code.

Once I remove the middle block of code, the outlining and Intellisense will work again.

Can anyone help me test this sample case?

--

I tried to add semicolon an GO to separate code, but neither of them works for me. Now it seems that the only feasible solution is commenting that middle block out.

Improve this question
7
  • I tried the script with Ola Hallengren's [dbo].[IndexOptimize] script which is 1363 lines in length and I didn't encounter any problems with SQL Server's intellisense. Could you open up Ola's Index Optimize script in your SSMS to verify that it is indeed an issue with your code and/or SSMS?
    – John K. N.
    Mar 14, 2017 at 9:51
  • Separate the statements into batches -- by placing "GO" after each (unless you've changed the default).
    – Michael Green
    Mar 23, 2017 at 1:39
  • @MichaelGreen, thanks for your reply! That didn't work for me.
    – Jason
    Mar 23, 2017 at 2:14
  • CTRL + SHIFT + R will refresh the intellisense cache. See if that does it for you? (This option can be found under Edit -> IntelliSense -> Refresh Local Cache)
    – John Eisbrener
    Mar 23, 2017 at 16:42
  • @JohnEisbrener, not working for me. Can you test the sample code and see whether that works for you?
    – Jason
    Mar 23, 2017 at 16:43

3 Answers 3

Reset to default
1
+50

After some testing I am also running into the same issue with SSMS 2016 (ver. 13.0.16104.4). I was able to keep Outlining and IntelliSense functionality working for a few more words than you're reporting by including the optional word NONCLUSTERED, but after I named the index, both the IntelliSense and Outlining features stopped working.

I suggest you fill out a Microsoft Connect item so the MS team has a proper bug submission ticket to work against.

Post the link after the fact and I'll up-vote it as well.

Improve this answer
1

In case it's already enabled, press Ctrl + Shift + R. That will update Intellisense with used database.

Sometimes, if the used database is not frequent, that does not work instantly in SSMS, but the above will update Intellisense.

In case of long script related issue, go to Tools>Options>. In there in on the left side, search for Text Editor>Transact-SQL>IntelliSense.

There is an item called Maximum Script Size. It may be 100KB`. In the drop down, change it to 5MB at least, or to Unlimited per your preference. After setting this, you need to close SSMS and start it again. Some older versions required a restart of the system. That should work.

Improve this answer
0
1

Add a semicolon after the middle block of code. I have noticed that sometimes it gets "stuck" trying to process something. Providing it with a clear break in a command seems to let it pick up again on the next query.

Improve this answer
1
  • Thanks, for your reply! Adding a semicolon won't work for me.
    – Jason
    Mar 23, 2017 at 2:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.