Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 664
  • Last Modified:

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

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
XGIS
Asked:
XGIS
  • 3
  • 3
1 Solution
 
Ryan McCauleyData and Analytics ManagerCommented:
I'm not sure exactly what you mean by your question. Can you provide a screenshot of what you're seeing?
0
 
XGISAuthor Commented:
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
 
Ryan McCauleyData and Analytics ManagerCommented:
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
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
XGISAuthor Commented:
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
 
Ryan McCauleyData and Analytics ManagerCommented:
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
 
XGISAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now