Link to home
Start Free TrialLog in
Avatar of CumulusGranitus
CumulusGranitusFlag for Germany

asked on

SQL Server Management Studio - Results Viewer - Number of Columns

Is there any way to increase the number of viewed columns from 655 to 900 (e.g.) ?
Avatar of Shaun Vermaak
Shaun Vermaak
Flag of Australia image

Any reason why you are not normalizing tables?
Avatar of CumulusGranitus

ASKER

It is normalized; but that was not the question.
Good luck
Try switching to "Results to text".  Not sure if it will work with that many columns but it's worth trying I guess...  I don't have such a table to try it out myself.
One thing I've learned from being on EE is that Expert comments often point to good considerations.

I've never seen a table with 900 columns, I'm not sure how that's normalized.  Or indexed, for that matter.  You seriously may want to take the issue as an indicator that the column count isn't normal and you might want to consider addressing that unless you're talking about some view returned by a sproc building columns out or something-- but even then, from a usage standpoint, is 900 columns at once useful vs just selecting the relevant columns?

I know it wasn't specifically what you were asking, but you've got resources who are willing to help improve some potential usage issues if you're interested in exploring that.
You are right when you say that this is not a normal condition. The 'normal' conditions work very well. But if it is allowed to create a table with 30000 columns in SQL Server 2019 why you may edit only a maximum of 500? The only question i had is, where (if so) you can change this value.
There may be some edge cases where columns are required (I can't think of any off the cuff) but it's probably assumed if that happens it's programmatic and receives programmatic updates.  I'm not aware of a way to extend that, and created a test table with 900 columns and can't find a method.

Also note, the column limit is 1024 on non-wide tables, to get to 30k you have to use sparse columns and there is a fixed row size so most of that data has to be NULL.  So I think the answer is that, it's not intended even though you technically can.  But as mentioned, performance has to be suffering as you can't effectively apply indexing to a schema like that.

See MSDN SQL Server limits:
https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-2017
In 20 years of SQL Server development I've only had one situation where I had a table of more than 400 columns, and that was when I had to build an SSIS package to suck in a SalesForce.com table of 1100+ columns that a non-database person built, heavily non-normalized, and then slice-dice it for use in a data warehouse.

The only way I could pull that off is to do two separate pumps, with the PK's and about 600 columns, and then create a SQL View to join the two to be one set again if they wanted to see that.

Page splits and speed of query execution must be really bad with that many columns.

Good luck.
...why you may edit only...

Woah wait, you're talking about the data editor in SSMS?!  That thing is a .NET control called DataGridViewer and by default it is limited to 655 columns.  So technically as a developer of SSMS you would be able to change this (although I do not see the benefit of editing that many columns, try to find ColumnXYA for instance, good luck with that lol).  But as a regular user of SSMS this is fixed and unchangeable.  Try using an UPDATE or INSERT statement instead...

Check out the link below for full details on how this works but that 655 is actually (2^16)/100.  Funny how things are still limited by 16 bits in times when terabytes fit on a piece of plastic of a couple of centimeters isn't it? ;)

Ref. FillWeight and binding (DataGridView)
^^  Ha.  Knew there had to be a technical answer somewhere.  That makes sense.
ok, i will ask Microsoft direct.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.