It is a general practice to ask practical oriented questions that help interviewer to understand the ability of candidates. These practical oriented questions can be answered easily on the base of experience. And off-course interviewer want to check your working capability instead of theory. Though there is still a challenge to analyse the ability of candidates in minutes of time.
Lets see few sample questions that are common for experienced developers.
- Can we create a View on a table which is not exist in database ? (in MS SQL)
- When will you use View and when Stored Procedure?
- Can we disclose only 10 WCF functions out of 15 to a particular customer or user ? (in WCF)
- Suppose we want to charge customer based on no of calls made to our WCF function, so is it possible to do so in WCF?
In, this post, I will cover the first question only.
Can we create a View without a base table / view ? (View on a table which is not exist in database)
Answer: No, MS SQL strictly check existence of dependent objects. Only Stored procedures are allowed to be created with non-existing objects.
Oracle give you a CREATE FORCE VIEW or ALTER FORCE VIEW command for this. When MS SQL 2005 was launched, at that time most of Oracle users were switching to on it, demanded the same feature. MS SQL allow you to do this in stored procedure and not directly as Oracle does. In Oracle, we can create a view using FORCE VIEW with Create command. Its force the creation of View on a base table which is not exist at all. Refer : Is there any equivalent of Oracle’s Create Force View in MS SQL MS SQL strictly check the existence of objects you refer while creating a new object except Stored procedures. Even if you are referring a foreign key that is not exist, MS SQL will not allow you and prompt error immediately. So there is no questions of creation of stand alone view that is going to refer non-exist object!!! So, be noted that MS SQL require each dependent object while creating new object and some exception is given to Stored Procedures. Refer : Stored procedure can be created with non-existing objects
Oracle :
So, the next question is what will be the output of SELECT query on this view? Well, it will give you an error as the base table is not exist. Each object details are managed into system tables with their valid / invalid state. If you force the creation of a View without base table/view, it will be created but marked as Invalid status.
At later stage, when the required table is ready (created), we need to revalidated(compile) our view that is still marked as Invalid state in user_objects. To recompile so that Status of our object(View) can be updated in user_objects, we need to run following commands:
ALTER VIEW MyForcefullyCreatedViewName COMPILE;
This will compile again and now it will find the required table on which our view is being created and status will be marked as VALID.
Now, you might be thinking that why Microsoft SQL Stored procedure has special privilege over other objects? What is the use of it? There may be many reasons for it. Let me simplify it with an ordinary example. Considering an enterprise level projects, where many professionals are involved with different modules. There may be chances that an expected table or object is not created by a person/team and it is suppose to be used in another object by another person/team. Or lets say, there is some complex query and some senior member is still working on it, instead of waiting, you can carry on your work by referring that object in your object! In this scenario it is very useful to create an object that is based on another object which is not present in database.