Differences between Stored Procedures and Functions

• Procedure can return zero or n values whereas function can return one value which is mandatory.
• Procedures can have input/output parameters for it whereas functions can have only input parameters.
• Procedure allows select as well as DML statement in it whereas function allows only select statement in it.
• Functions can be called from procedure whereas procedures cannot be called from function.
• Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in afunction.
• We can go for transaction management in procedure whereas we can't go in function.
• Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.

• UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
• UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
• Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowsetoperations.

Webservices VS WCF

Web Services limitation :-

The major limitation with web services is that the communication can happen over HTTP only.
A second limitation with web services is that it provides singlex
communication and there is no way to have half duplex or full duplex
communication using web services.

They work in an stateless fashion over HTTP and are hosted inside a web server like IIS


Windows Communication Foundation (WCF):-

Windows Communication Foundation (WCF) comes to the rescue when we find ourselves
not able to achieve what we want to achieve using web services, i.e., other
 protocols support and even duplex communication. With WCF, we can define our
service once and then configure it in such a way that it can be used via HTTP, TCP,
 IPC, and even Message Queues. We can consume Web Services using server side scripts
 (ASP.NET), JavaScript Object Notations (JSON), and even REST
(Representational State Transfer


Address: Like a webservice, a WCF service also provides a URI which can be used by
         clients to get to the WCF service. This URI is called as the Address of
         the WCF service. This will solve the first problem of "where to locate the
         WCF service?" for us.

Binding: Once we are able to locate the WCF service, we should think about how to
         communicate with the service (protocol wise). The binding is what defines
         how the WCF service handles the communication. It could also define other
         communication parameters like message encoding, etc. This will solve the
         second problem of "how to communicate with the WCF service?" for us.

Contract: Now the only question we are left up with is about the functionalities
          that a WCF service provides. Contract is what defines the public data and
          interfaces that WCF service provides to the clients.


WCF bindings: System-provided WCF bindings are used to specify the transport protocols, encoding, and security details required for clients and services to communicate with each other

Let us see more detailed on predefined binding

BasicHttpBinding

• It is suitable for communicating with ASP.NET Web services (ASMX)-based services that comfort with WS-Basic Profile conformant Web services.
• This binding uses HTTP as the transport and text/XML as the default message encoding.
• Security is disabled by default
• This binding does not support WS-* functionalities like WS- Addressing, WS-Security, WS-ReliableMessaging
• It is fairly weak on interoperability.

WSHttpBinding

• Defines a secure, reliable, interoperable binding suitable for non-duplex service contracts.
• It offers lot more functionality in the area of interoperability.
• It supports WS-* functionality and distributed transactions with reliable and secure sessions using SOAP security.
• It uses HTTP and HTTPS transport for communication.
• Reliable sessions are disabled by default.

WSDualHttpBinding

This binding is same as that of WSHttpBinding, except it supports duplex service. Duplex service is a service which uses duplex message pattern, which allows service to communicate with client via callback.

In WSDualHttpBinding reliable sessions are enabled by default. It also supports communication via SOAP intermediaries.

WSFederationHttpBinding

This binding support federated security. It helps implementing federation which is the ability to flow and share identities across multiple enterprises or trust domains for authentication and authorization. It supports WS-Federation protocol.

NetTcpBinding

This binding provides secure and reliable binding environment for .Net to .Net cross machine communication. By default it creates communication stack using WS-ReliableMessaging protocol for reliability, TCP for message delivery and windows security for message and authentication at run time. It uses TCP protocol and provides support for security, transaction and reliability.

NetNamedPipeBinding

This binding provides secure and reliable binding environment for on-machine cross process communication. It uses NamedPipe protocol and provides full support for SOAP security, transaction and reliability. By default it creates communication stack with WS-ReliableMessaging for reliability, transport security for transfer security, named pipes for message delivery and binary encoding.

NetMsmqBinding

• This binding provides secure and reliable queued communication for cross-machine environment.
• Queuing is provided by using MSMQ as transport.
• It enables for disconnected operations, failure isolation and load leveling

NetPeerTcpBinding

• This binding provides secure binding for peer-to-peer environment and network applications.
• It uses TCP protocol for communication
• It provides full support for SOAP security, transaction and reliability.