Solved

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

Posted on 2014-04-04
6
461 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

762 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now