Missing comma and Column alias

When you write SELECT statement and use column alias always use AS followed by Alias name. If you miss out a comma between column names,  you will get different result sets with different column names

Consider the following set of code

create table #employee_master(emp_id int, emp_name varchar(100), dob varchar(12))
insert into #employee_master
select 1,'Sankar','09/19/1976' union all
select 2,'Kamal','02/11/1968' union all
select 3,'Rajesh','22/29/1200'
Select emp_id, emp_name dob from #employee_master

Note that there are three columns in the SELECT statement and there is a missing comma between emp_name and dob. If you execute the above code, the result is

emp_id         dob
----------- -------------
1              Sankar
2              Kamal
3              Rajesh

If you look at the result , there are only two columns and under the column dob the names are displayed. Because in the SELECT statement emp_name dob is actually considered as emp_name as dob and dob becomes the alias name for emp_name.

You should be aware of this. If your SELECT statement has many column names always make sure there is no missing comma. If there is, make sure if it is a proper alias name and use AS between column names

Table variable and it’s storage in tempdb

You know that temporary tables are stored in tempdb database although accessible to the current session only. Within a current session, it exists until you drop it.

Did you know table variable is also stored in tempdb database till the execution completes?

Consider the following code

DECLARE @NUMBERS TABLE(NUM INT)
SELECT * FROM TEMPDB.INFORMATION_SCHEMA.TABLES
SELECT TABLE_CATALOG ,TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM TEMPDB.INFORMATION_SCHEMA.COLUMNS

When you execute all the above, you can get some information about the table variables in the INFORMATION_SCHEMA views

It returns the following


-- Result1
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE
--------------- -------------- ----------- -----------
tempdb dbo #AE4E2F08 BASE TABLE

— Result2
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME
————– ————– ———– ———–
tempdb dbo #B036777A NUM

If you look at the table name it starts with # like temporary variable but it is of hexadecimal number.

If you execute just the following

 

SELECT * FROM TEMPDB.INFORMATION_SCHEMA.TABLES
SELECT TABLE_CATALOG ,TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM TEMPDB.INFORMATION_SCHEMA.COLUMNS

You will not get the above results. It should be run simultaneously with table variable creation part. So table variables are stored in tempdb during the execution

SET vs SELECT

Pinal Dave, Blogger and Speaker, posted about  Interview Question of the Week #044 – What is the difference of performance between SELECT and SET?

Here are some more examples on the difference

1 SET can assign a single value to a variable

DECLARE @I INT
SET @I=10
SELECT @I

SELECT can assign a value to a variable and will SELECT and display it too

DECLARE @I INT
SELECT @I=10
SELECT @I as result

2 If SET uses a query to assign a value to a variable and there is no result set returned from a query, variable will be assigned a NULL value

DECLARE @I INT
SET @I=10
SELECT @I as result1
SET @I=(SELECT 1 WHERE 1=0)
SELECT @I as result2

If you execute the above code, it returns two result sets. Result1 is 10 and Result2 is NULL. As SELECT 1 WHERE 1=0 does not return any value

If you use SELECT in the above example result2 is still 10. It is because when there is no result set returned, SELECT retains the old value of variable.

DECLARE @I INT
SET @I=10
SELECT @I as result1
SELECT @I = 1 WHERE 1=0
SELECT @I as result2

Both SELECTs return 10 as result.

If you want to assign a value to a variable, always use SET