Knowing String Defaults in T-SQL–#SQLNewBlogger

For years I’ve assumed I knew the string defaults, but I realized that’s not right. This post looks at what I learned.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Declaring VARCHAR variables

I learned a couple things. First, this is invalid code:

The parenthesis aren’t needed, and cause an error. But if I declare just the word, I can add a string. The string in this code is more than 30 characters, which I’ve always assumed is the default length.

DECLARE @s VARCHAR;

SELECT @s = 'this is a test of a fairly long string'

SELECT @s

When I run this, however, I only get one character back.

Why is that? Well, the default length is on, according to the docs.

When is it 20? When we use CAST/CONVERT. In that case, it’s 30. Code from the docs shows this:

I’ve known this happens with CAST, but I didn’t realize the default length was 1. That’s interesting, and hopefully something no one lets slip into production when it would cause a problem.

A good lesson is to always declare your length, and don’t make that MAX if you don’t need it.

SQL New Blogger

This post took me about 10 minutes to write, once I realized the issue. I spent a few minutes grabbing links, as I’d had some of the code written once I was testing what I’d read.

You could do the same thing. Show some learning, show some code, show how you change things.