What is deferred name resolution in SQL Server?



Let me explain deferred name resolution with an example. Consider the stored procedure shown below.


Create procedure spGetCustomers
as
Begin
Select * from Customers1
End

Customers1 table does not exist. When you execute the above SQL code, the stored procedure spGetCustomers will be successfully created without errors. But when you try to call or execute the stored procedure using Execute spGetCustomers, you will get a run time error stating Invalid object name 'Customers1'.


So, at the time of creating stored procedures, only the syntax of the sql code is checked. The objects used in the stored procedure are not checked for their existence. Only when we try to run the procedure, the existence of the objects is checked. So, the process of postponing, the checking of physical existence of the objects until runtime, is called as deffered name resolution in SQL server.


Functions in sql server does not support deferred name resolution. If you try to create an inline table valued function as shown below, we get an error stating Invalid object name 'Customers1' at the time of creation of the function itself.


Create function fnGetCustomers()
returns table
as
return Select * from Customers1


So, this proves that, stored procedures support deferred name resolution, where as functions does not. Infact, this is one of the major difference between functions and stored procedures in sql server. For all the differences between stored procedures and functions, please read this artilce.

7 comments:

  1. I want to Store multiple Records in database using Store procedure ....store procedure in such a way that it will use loop to perform operation ,,,,,,,that is i want to send email to the multiple user and at the same time all record should maintain in the database ,,,,please reply if any one know .....

    ReplyDelete
  2. This is string 'Manoj Sharma' (How to reverse word in sentence)
    How to write this string like Sharma Manoj

    ReplyDelete
    Replies
    1. Declare @str varchar(100)='Manoj Sharma'
      select substring(@str,charindex(' ',@str,1)+1,len(@str)-charindex(' ',@str,1)) +' ' + substring(@str,1,charindex(' ',@str,1)-1)

      Delete
  3. Deferred name resolution can only be used when you reference nonexistent table objects. All other objects must exist at the time the stored procedure is created. For example, when you reference an existing table in a stored procedure you cannot list nonexistent columns for that table.
    Please refer https://technet.microsoft.com/en-us/library/ms190686(v=sql.105).aspx for more information

    ReplyDelete
  4. I don't want my stored procedures to compile if a table it references does not exist. Is it possible?

    ReplyDelete
    Replies
    1. Yes ..you can achieve this with the help of schemabinding

      Delete
  5. Difference between Ms SQL and T-SQL

    ReplyDelete

If you are aware of any other sql server questions asked in an interview, please post them below. If you find anything missing or wrong, please feel free to correct by submitting the form below.

 
Disclaimer - Terms of use - Contact Us