Can you guess the output of this query without actually executing it?
EXEC('select ''''''''')
Answers
1 Incorrect syntax near select ''''''''') 2 '''' 3 '' 4 '
Can you guess the output of this query without actually executing it?
EXEC('select ''''''''')
Answers
1 Incorrect syntax near select ''''''''') 2 '''' 3 '' 4 '
Please note that this post is now available at https://exploresql.com/2017/08/22/usage-of-cte-and-maxrecursion-option-in-view/
IN clause can be used as a JOIN to get data by matching the column values from other table. Although this is much similar to INNER JOIN, be careful about the usage of IN
Let us create the following data sets
use tempdb create table customer_master ( cust_id int, cust_name varchar(100) ); insert into customer_master(cust_id,cust_name) select 1,'Sankar' union all select 2,'Madhivanan' union all select 3,'Gopal'; create table premium_customers ( cust_id int ); insert into premium_customers(cust_id) select 1 union all select 2
Now to get all customers who are in premium_customers table, you can use this
select * from customer_master where cust_id in ( select cust_id from premium_customers )
which returns the following result
cust_id cust_name ----------- ----------- 1 Sankar 2 Madhivanan
Now, let us re name cust_id of premium_customers into customer_id
exec sp_rename 'premium_customers.cust_id','customer_id','COLUMN'
Now execute the same query
select * from customer_master where cust_id in ( select cust_id from premium_customers )
The result is
cust_id cust_name ----------- ------------- 1 Sankar 2 Madhivanan 3 Gopal
It returns ALL ROWS from customer_master table although there is no column named cust_id in premium_customers. Because it is a column in customer_master table, it is matched with customer_master table and gives all rows
How do we avoid this?
Always use table alias and qualify the columns using table alias
Executing the following code
select t1.* from customer_master as t1 where t1.cust_id in ( select t2.cust_id from premium_customers as t2 )
now throws an error
Msg 207, Level 16, State 1, Line 39 Invalid column name 'cust_id'.
So it is always a better practice to use table alias and qualify the columns with it.
A view is very useful in giving out only the required/restricted information to the users. However see what happens when you create a view with “SELECT * from table” definition.
Let us create this simple table
create table emp_master ( emp_id int, emp_name varchar(100), joined_date date )
Create a view based on this table using “SELECT *”
Create view emp_master_view as select * from emp_master;
Now see the column information of this view using sp_columns system procedure
EXEC sp_columns 'emp_master_view'
The result is shown in the below image
Now let us add a new column in the emp_master table
alter table emp_master add address varchar(200)
Now see the column information of view using sp_columns system procedure
EXEC sp_columns 'emp_master_view'
The result is shown in the below image. As you see the address column is not yet updated in the view.
So what is the proper way to update the column information of the view?
Use sp_refreshview system rocedure
EXEC sp_refreshview emp_master_view
After executing the sp_refreshview procedure, let us see the column information
EXEC sp_columns 'emp_master_view'
As you see in the following result, new column is added in the view
Points to consider when creating a view
1 Do not use *. Explicitly type out the column names in the view definition. When new columns are required, alter the view to add them
2 Use schemabinding option when creating view which will prevent the usuage of *