How Can I Replace “No column name” With A Word In SQL Server?

Words: 398
Time to read: ~ 2 minutes
tl;dr : Give it an alias! ( select ‘0’ AS [Zero]; )

---

I don’t do a lot of Search Engine Optimization (SEO) stuff on this blog; mainly because I started it as a way to self document issues in case I forget them and also because I figure if I write about a topic, it will force me to learn it to a more comprehensive level than normal, so it’s mainly a selfish blog (different from these guys).

I try and stay away from click-bait titles as well but I’m a cheesy guy so they can slip in there from time to time 🙂

That being said this blog is hosted on WordPress and when you log in it gives you a whole load of quick data measurements, and if there’s one thing that Data Platform people love, it’s data measurements.

One of these is “Search Terms” and normally it’s great for blog ideas on what people want to see…

However, from time to time actual results come through and those are searches that we can action e.g.

Quick Answer:

Give it an alias:

SELECT 0 AS [Zero];

Why?

Have you checked out Kenneth Fisher’s ( Blog | Twitter ) recent blog post about creating an empty table from a SELECT statement but without the identity? It’s a good post and I especially like the fact that he was able to figure it out himself.

How does that relate to this? Well (spoiler alert here) he used UNION ALL to remove the identity property of the table when inserting into a new table.

I like to think of it as using UNION ALL to confuse SQL Server, stopping it from guaranteeing that the column has an identity property anymore.

I also like to think of that’s why SQL Server puts “No column name” onto custom columns and expressions that we create: because we’ve confused SQL Server as to what the column is now.

Now I know that Oracle has a different method and puts the expression into the column name…

…but I’m not a fan. If the expression gets sufficiently big, then the column name is just going to get annoyingly wide:

Besides, in the end, it’s easier to just give it an alias. Probably make it easier to figure out what the column is in the end too: