Solved

How to display ALL or SPECIFIC SPARSE fields when EDITING in SSMS 2012?

Posted on 2014-04-04
6
506 Views
Last Modified: 2014-04-15
When using tables with sparse fields SSMS decides what fields it wants to display to you when editing from object explorer.

Are there any SSMS 2012 options other than standard sql inserts?
0
Comment
Question by:XGIS
  • 3
  • 3
6 Comments
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 39983966
I'm not sure exactly what you mean by your question. Can you provide a screenshot of what you're seeing?
0
 
LVL 7

Author Comment

by:XGIS
ID: 39986170
Hello Ryan,
Please note that when you select EDIT in SSMS 2012 it simply does not display all the the fields of the table so you can edit values. eg if my table has 20 fields only 6 display.  It seems to be random in which field it selects also. Usually the Identity field a GUID and some that have data.  I thought it was null fields only but this does not seem to be the case. It will show geography and geometry fields even though they are all null, but certain INT fields it just ignores. I am working remote and have no SSMS setup yet.
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 39986340
That's really odd, and not something I've ever seen happen before. When you say "it seems to be random", do you mean that you can't tell why it shows certain fields and not others, but it's always consistent, or do you mean that it seems to randomly pick fields to show you each time? Whenever you do get SSMS set up, both a screenshot and a copy of the table DDL would be helpful here.

If it's the first (consistent, but not understood), it could be related to permissions on the columns that you can't see. I checked in a SQL 2012 server I have handy and I can't edit the table if I don't have permissions on all the columns, but it could be what you're seeing:

http://www.mssqltips.com/sqlservertip/2124/filtering-sql-server-columns-using-column-level-permissions/

If you execute the following statement (from the database that contains this table), do you see all the columns in the table?

select * from sys.columns
 where object_id = object_id('YourTableName')

Open in new window


Also, what's your permission level in the database - are you a regular user, a datareader, DBO, or something else?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 7

Author Comment

by:XGIS
ID: 39998333
I am back in the office now.. attached  is an example in edit mode for an Admin.
This is SSMS 2012 SQL Express (Microsoft SQL Server Management Studio      11.0.3128.0)
Maybe there is too much efficiency...   but some of the fields are default and not nullable like normal tables and DBs... I doubt it is an error... but it is definitely not a feature either
SparseEDIT-SA.PNG
SysColumns.PNG
TableCatalog.PNG
0
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 500 total points
ID: 39999346
Just to confirm - is "WA" the name of your table? I see you using the string when searching, but the table you give as an example is named something different. I just want to make sure I'm clear on what I'm seeing.

However, that said, I actually made some columns in a test table SPARSE and I'm seeing exactly the same thing you are - those columns don't appear either when I script a SELECT statement in SSMS or when I select "Edit Top 200 Rows" for the table. This doesn't change whether I have data in those columns are not, and if I do a "SELECT * FROM MyTable", I see all columns without an issue.

I did a bit more digging, and it appears this is by design - since a table can have 30,000 sparse columns in it, they've chosen to exclude them from these statements regardless of how many there actually are. There was a Connect bug opened to fix it (http://connect.microsoft.com/SQLServer/feedback/details/776900/ssms-2012-doest-open-sparse-column-to-edit-data), but it was closed as not meeting the bar for resolution, but shares that MSFT will consider fixing it in a subsequent release (I'm using SSMS 2014 and it still occurs).

That said, there's a bit of a work-around - when you have a view on top of the table, the view displays all columns, even the sparse ones. You can create views for all tables with a sparse column using the following script:

http://sqlsoundings.blogspot.com/2011/08/select-top-1000-rows-doesnt-show-sparse.html

A bit annoying, I agree, but I understand why the design decision was made, and hopefully the work-around is sufficient. Also, unless you have a case for SPARSE or space in your environment is at a real premium, you may want to consider removing SPARSE from these columns - it will take a bit more space on disk, but it will resolve the display issue for you as well.
0
 
LVL 7

Author Closing Comment

by:XGIS
ID: 40002597
Hello Ryan.. excellent response.. it is an unfortunate reality that there must be a trade-off... I compare it to MVC Razor where the developer now loses any capability of visual GUI development.   The database name was actually 'WA'... which I filtered using that schema object query.    Sparse was my answer to my development migration to having unused columns in a generic column type and name scenario. The only thing that changes is my table names and the number of fields.  The objective here is to use the same CRUD infrastructure and simply change the number and type of fields based on table requirements.  As a solution to the issue itself I am using Asp.NET maker for rapid generation and editing. Thankyou Again..
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question