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.

One thought on “Help, not all of my column is displaying in the output of the query!

  1. Bruce Bergenback says:

    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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,709 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: