What is difference between Table Variable and Temp Table

Feature	Table Variable	Temporary Table
Scope	Current batch	Current session, nested stored procedures. Global: all sessions.
Usage	UDFs, Stored Procedures, Triggers, Batches.	Stored Procedures, Triggers, Batches.
Creation	DECLARE statement only.	CREATE TABLE statement. SELECT INTO statement.
Indexes	Indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement.	Indexes can be added after the table has been created.
Constraints	PRIMARY KEY, UNIQUE, NULL, CHECK, but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY not allowed.	PRIMARY KEY, UNIQUE, NULL, CHECK. Can be part of the CREATE TABLE statement, or can be added after the table has been created. FOREIGN KEY not allowed.
Post-creation DDL	Statements are not allowed.	Statements are allowed.
Truncate table	Not allowed.	Allowed.
Rollbacks	Not affected (Data not rolled back).	Affected (Data is rolled back).

How to comma separator string use in Where clause in SQL

Using below function you can create table with valid value from comma separator string. And then INNER JOIN on your table.


CREATE FUNCTION [dbo].[fn_split] (
@value VARCHAR(MAX)
, @Delimeter CHAR(1)
)
RETURNS @SplitData TABLE (Data VARCHAR(50))
AS
BEGIN
DECLARE @XML XML

SELECT @XML = '' + REPLACE(@value, @Delimeter, '') + ''

INSERT INTO @SplitData
SELECT x.v.value('.', 'VARCHAR(50)') AS Data
FROM @XML.nodes('IDs/ID') x(v)

RETURN
END

Different type of WCF Binding

WCF supports nine types of bindings. 

Basic binding 

Offered by the BasicHttpBinding class, this is designed to expose a WCF service as a legacy ASMX web service, so that old clients can work with new services. When used by the client, this binding enables new WCF clients to work with old ASMX services. 

TCP binding 

Offered by the NetTcpBinding class, this uses TCP for cross-machine communication on the intranet. It supports a variety of features, including reliability, transactions, and security, and is optimized for WCF-to-WCF communication. As a result, it requires both the client and the service to use WCF. 


Peer network binding 

Offered by the NetPeerTcpBinding class, this uses peer networking as a transport. The peer network-enabled client and services all subscribe to the same grid and broadcast messages to it. 


IPC binding 

Offered by the NetNamedPipeBinding class, this uses named pipes as a transport for same-machine communication. It is the most secure binding since it cannot accept calls from outside the machine and it supports a variety of features similar to the TCP binding. 


Web Service (WS) binding 

Offered by the WSHttpBinding class, this uses HTTP or HTTPS for transport, and is designed to offer a variety of features such as reliability, transactions, and security over the Internet. 


Federated WS binding 

Offered by the WSFederationHttpBinding class, this is a specialization of the WS binding, offering support for federated security. 


Duplex WS binding 

Offered by the WSDualHttpBinding class, this is similar to the WS binding except it also supports bidirectional communication from the service to the client. 


MSMQ binding 

Offered by the NetMsmqBinding class, this uses MSMQ for transport and is designed to offer support for disconnected queued calls. 


MSMQ integration binding 
Offered by the MsmqIntegrationBinding class, this converts WCF messages to and from MSMQ messages, and is designed to interoperate with legacy MSMQ clients.