0

In SSMS 2008R2 you can navigate to a Database > Views > Find a specific view that you have an index on and unfurl the Index(es)

In SSMS 2014 (possibly other modern versions) you cannot. When you get to the View and unfurl, the selection of object types below it does not include Indexes - thus you cannot see the Indexes via the GUI. Of course I can still use TSQL to view the index, but this is not as easy to demonstrate to colleagues as via the GUI or for them to do themselves.

Does anyone know why this happens or if there is a way to allow the Indexes on Views to be displayed (I believe you cannot see the Indexes on any object within System Tables either, but that may be related to the same issue)? I'm guessing there is a setting in SSMS, but I haven't been able to find/answer via the net so far.

Steps to reproduce:

  1. Create a view (don't forget "WITH SCHEMABINDING AS" option)
  2. Create an Index against that view
  3. Navigate to your view within SSMS 2014
  4. When you unfurl that view (i.e. click the cross), there is not a "folder" for Indexes (I've just noticed, there isn't one for Columns either)

If you have access to SSMS from 2008R2 you will find that you can navigate to the Indexes on a View and also display the columns list.

Any ideas?

Improve this question
9
  • 2
    At first I was going to comment that regular Views don't have indexes, but it seems further down in your post you are indeed referring to indexed Views. The fact that you don't see a node for the Columns in a View is a problem too, and sounds like either a permissions issue with your account or an actual application bug with your version of SSMS. On that note, you should be using the latest version of SSMS, regardless of what version your SQL Server instance is.
    – J.D.
    Jun 2, 2021 at 14:56
  • 1
    It appears on version 15.0.18131
    – McNets
    Jun 2, 2021 at 15:05
  • 1
    The latest version is 18.9 so you are quite behind. Note that this is irrespective of which version of SQL Server you have (which is also behind)
    – Charlieface
    Jun 2, 2021 at 20:53
  • Thanks for your suggestions, unfortunately this is a Financial Services SQL Server setup and there is a lot of red tape (and fear) around upgrading core services. It's not as simple as "lets get onto the latest version" and though I agree with the sentiment and simplicity of "you should be using the latest version of SSMS", that's not a simple step here. However, as you have confirmed by no other responders being in the same situation as me, I'm guessing it's this version of SSMS, so I shall start the process of getting this behemoth to start the approval procedure. Thank you all.
    – MHSQLDBA
    Jun 7, 2021 at 13:58
  • @J.D. Pretty sure it's the actual version of SSMS - I'm sysadmin and can see the same Indexes on the view(s) in question on the same server via SSMS 2008R2
    – MHSQLDBA
    Jun 7, 2021 at 14:00

1 Answer 1

Reset to default
1

As J.D. suggested in the comments, try with the latest version of SSMS. I evaluated with 18.8 using the below:

USE tempdb
GO

CREATE VIEW dbo._IndexView WITH SCHEMABINDING

AS

SELECT 
1 AS One 
GO


CREATE UNIQUE CLUSTERED INDEX One ON dbo._IndexView (One)

--DROP VIEW dbo._IndexView

enter image description here

Improve this answer
2
  • Thanks for putting the time in and providing clear evidence!
    – MHSQLDBA
    Jun 7, 2021 at 14:01
  • Just for anyone reading this in the future - the version (of SSMS) that I describe in the original question was: 12.0.2000.8 - if you're experiencing the same behaviour that I was, upgrade from that version. (Currently 12.0.6433.1 definitely confirmed working if you have to stick with SQL 2014)
    – MHSQLDBA
    Jul 1, 2021 at 11:28

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.