Difference b/w Stored Procedure and UDF

1. You can use SELECT statement with UDF, while either EXEC or Exec can be used with SP.
2. A UDF can return a Table variable, while with SP you can create a table but can't return a table variable.
3. You can write Update, Delete statements within the SP, but UDF does not allow this.
4. You can use UDF in a JOIN, but SP can not be used.
5. You can write your queries within the transaction in SP but UDF does not allow you to write the queries within the transactions.

Interview Preparation :)

GC.SupressFinalize:
It requests the GC not to call the Finalize method for the specified object. This method takes a single parameter: the pointer to an object. It sets a bit in the object header that GC checks when call the Finalizer method. When this flag is on, the runtime knows not to move this object's pointer to the f-reachable queue, preventing the object's Finalize method from being called. You can call this method within the Dispose method of IDisposable interface and prevent GC to call the Finalize method on the object that does not require it.
Syntax:
Public static void SupressFinalize (Object obj)
Note: If obj is null, it throws the NULL exception

GC.ReRegisterForFinalize:
This method is used to call the finalize method for the object again. This method takes a single parameter: the pointer to an object. When this method is called, it appends the address of the object to the end of the finalization queue. This method is usually used in case of resurrection, when the object’s finalized has been called once but the user wants to call the finalize method again.
Syntax:
Public static void ReRegisterForFinalize (Object obj)

Difference b/w Finalize and Dispose:
Both Finalize and Dispose methods are used by the GC to free the garbage objects and reclaim their memory, still there are some differences in both these methods:
Finalize is an implicit resource cleanup method, used by GC to collect the memory used by the managed as well as unmanaged resources. On the other hand, Dispose is an explicit resource cleanup method that is used by GC to reclaim the memory of both managed as well as unmanaged objects.
The exact time when the Finalize method will run is un-predictable, while the use of Dispose method is totally within the hands of the developer. When and where the developer wants to reclaim the memory of an object there he can use the Dispose method.
The use of Finalize method in the object’s type (i.e. class) is a performance hit as two GC runs are required to reclaim the memory of that object, whereas in calling Dispose there is no such performance hit as you can avoid the call to finalize method (if implemented) by using the GC.SupressFinalize.
The order in which the Finalize will be executed is not predictable. If a finalized object A has reference to another finalized object B, then it might be possible that Finalize for object B gets executed before the object A gets finalized, which may leads to unpredictable results. Since, the use of Dispose is totally within the hands of developer so no such problem exists with Dispose method.

Difference b/w Close and Dispose:
Both Close and Dispose methods are used by the GC to free the garbage objects and reclaim their memory. Close can be used to those objects which can be reopened and reused after they are closed, while Dispose is used to clean up those objects which are no longer required after they have disposed.