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
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.