Blog

Hopefully, when you make an typing error while writing a SQL statement, you get the red curly lines under the offending part of the query showing you where the error is. If you execute the following statement, you’ll get an error message:

However, some typos doesn’t create an syntax error. Instead it alters the meaning of the t-sql statement in an perhaps unwanted way. Check out the following example.

Let’s say we have a table with three columns: customer name, weight in kilograms and age. There are two rows in the table. Joe weights 85 kg’s and is 42 years old. Ben weights 140 kg’s and is 38:

USE tempdb;

CREATE TABLE T1 (CUST_NAME VARCHAR(10), WEIGHT_KG INT, AGE INT);

INSERT T1 VALUES ('JOE',85,42),('BEN',140,38);

Next, run this select statement:

SELECT
	CUST_NAME,
	WEIGHT_KG
	AGE
FROM
	T1

This returns…

CUST_NAME   AGE
----------  -----------
JOE         85
BEN         140

Wait a minute! Something is wrong here… Is Ben really 140 years old?

Did you spot the “error” in the select statement? Maybe you did, but with more columns and with less obvious data it gets a lot harder.

What the result shows is actually CUST_NAME and WEIGHT_KG. Not AGE, altough it says so. But WEIGHT has the header AGE. AGE isn’t even returned by the query. By now I guess you have spotted the missing comma after WEIGHT_KG, causing this behaviour. SQL Server interprets the statement as “WEIGHT as AGE”, that is, with an column alias name. The “as” keyword is optional however, making this particular typing error easy to make.

This can lead to some really nasty bugs in your database. Imagine the accidental missing comma in the statement “PaidAmount UnPaidAmount” for instance. “All” that happens is that a column changes its name to another columns name, and that you’ll have one column less in the result set.

To avoid the error, qualify the table (or table alias) the column belong to. If you do that, you’ll get an error:

SELECT
	T1.CUST_NAME,
	T1.WEIGHT_KG
	T1.AGE
FROM
	T1

This is becasue dots (.) are not allowed in alias names.