Help, not all of my column is displaying in the output of the query!1
February 22, 2022 by Kenneth Fisher
I’ll be honest, I don’t remember if I’ve written about this before but I couldn’t find it, so here we go.
Have you ever run a query and not all of your output is displaying?
CREATE TABLE #test (col1 varchar(100)); INSERT INTO #test VALUES ('1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30'); SELECT * FROM #test; DROP TABLE #test;
Or text output:
If you guessed there is a setting for this you’re right! Under Tools -> Options.
I’m showing the default values for the number of characters to output. I.e. 256 in text and 65535 in grid. Obviously I reduced this for my demo (down to 30 characters for each, which is the minimum). In general though I recommend increasing them to the maximum which is 2097152 characters in both text and grid output. That way if you want less you can handle that in the query itself. I.e. SELECT LEFT(Col1,30). To make this easy I just put 999999999 and it auto reduces to the maximum.
And the last thing I’ll mention here is that if for some reason you need to change these (or any number of other) settings for a specific query window you can go to the Query menu or right click in the query pane and then go to Query Options.
Category: Microsoft SQL Server, Settings, SQLServerPedia Syndication, SSMS | Tags: microsoft sql server, Settings, SSMS
One thought on “Help, not all of my column is displaying in the output of the query!”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Thank you for sharing the wealth Kenneth. I knew that this value could be changed but somehow I hadn’t run across this limit of 2097152 characters in my SQL Server journey.