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.

Community
  • 1
Jason
  • 683
  • 1
  • 12
  • 26
  • 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 [tag: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 '17 at 09:51
  • Separate the statements into batches -- by placing "GO" after each (unless you've changed the default). – Michael Green Mar 23 '17 at 01:39
  • @MichaelGreen, thanks for your reply! That didn't work for me. – Jason Mar 23 '17 at 02: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 '17 at 16:42
  • @JohnEisbrener, not working for me. Can you test the sample code and see whether that works for you? – Jason Mar 23 '17 at 16:43
  • Nope, not working here either after I name the index. The IntelliSense and Outlining last a few keywords more when you include the optional keyword, NONCLUSTERED, but after you specify the name of the index, it dies. Submit a Connect Item so MS has a proper bug report to develop against. – John Eisbrener Mar 23 '17 at 21:03
  • @JohnEisbrener , can you answer this question with your test results and I'll mark it as an answer with the bounty. – Jason Mar 24 '17 at 00:34

3 Answers3

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.

MarmiK
  • 269
  • 2
  • 15
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.

Anthony Genovese
  • 2,063
  • 3
  • 20
  • 34
1

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.

John Eisbrener
  • 9,427
  • 6
  • 29
  • 63