Working with various SQL Server options is always a great feeling. It allows you to customize your SQL Server experience and get the most out of it. In this blog post, we will discuss one such option that can be useful when working with query results.
Recently, I received an email from a junior explorer of SQL Server 2016 CTP who was having trouble getting the same output as shown in one of my previous blog posts. This surprised me, as I had tested the code and it was working fine for me. After some investigation, I realized that the issue was related to a specific SQL Server option – the maximum number of characters displayed in each column.
By default, SQL Server displays a maximum of 256 characters in each column of the query result. However, this can be changed to a higher value if needed. To change this option, follow these steps:
- Right click in the query editor and select Query Options.
- Under Results, select Text.
- Uncheck “Include column headers in the result set”.
- Change the maximum number of characters displayed to 8192.
- Click on “Execute” in the editor and choose “Results to Text” and click OK.
After making the above change, you will be able to get up to 8192 characters as part of the output to Text. This can be particularly useful when dealing with large text values or when you need to see the complete content of a column.
It’s worth noting that the maximum allowed value for this option is 8192. You can choose a different value based on your requirements.
Have you ever changed this option in your SQL Server environment? If so, what value did you use? I would love to hear about your experiences and any other SQL Server options that you find useful.
Thank you for reading!
Reference: Pinal Dave (https://blog.sqlauthority.com)