Sql Temporary tables

What are the 2 types of Temporary Tables in SQL Server?
1. Local Temporary Tables
2. Global Temporary Tables


What is the difference between Local and Global Temporary Tables?
Local Temporary Tables:
1. Prefixed with a single pound sign (#).
2. Local temporary tables are visible to that session of SQL Server which has created it.
3. Local temporary tables are automatically dropped, when the session that created the temporary tables is closed.


Global Temporary Tables:
1. Prefixed with two pound signs (##).
2. Global temporary tables are visible to all the SQL server sessions.
3. Global temporary tables are also automatically dropped, when the session that created the temporary tables is closed.


Can you create foreign key constraints on temporary tables?
No


Do you have to manually delete temporary tables?
No, temporary tables are automatically dropped, when the session that created the temporary tables is closed. But if you maintain a persistent connection or if connection pooling is enabled, then it is better to explicitly drop the temporary tables you have created.
However, It is generally considered a good coding practice to explicitly drop every temporary table you create.
In which database, the temporary tables get created?
TEMPDB database.


How can I check for the existence of a temporary table?

Method Overloading in WCF

[ServiceContract]
public interface IGetEmployee
{
    [OperationContract(Name = "GetEmployeeDetailsById")]
    List GetEmployeeDetails(int id);

    [OperationContract(Name = "GetEmployeeDetailsByName")]
    List GetEmployeeDetails(String name);
}

     we can achive the method overloading through the name attribute of the operation contract set the alias name for the method .method name will appear based on the proxy class generation If you use the proxy class that is automatically generated by svcutility.exe, the alias method names will be used. However, you can manually edit the generated proxy class to achieve the appearance of overloaded methods on the client as well.This can be accomplished by applying the same attributes to the methods defined in the interface that is used by the proxy class.

NVL2 function

The NVL2 function takes three arguments: NVL2 ( arg1, arg2, arg3 ) NVL2 returns arg3 if arg1 is NULL, and arg2 if arg1 is not NULL. The NVL function allows you to perform some value substitution for NULL values while the NVL2 function allows you to implement an IF..THEN...ELSE construct based on the nullity of data. For SQL Server we can uses the CASE statement. CASE WHEN arg1 IS NOT NULL THEN arg2 ELSE arg3 END this CASE statement will return arg3 if arg1 is NULL, and arg2 if arg1 is not NULL

Difference between BasicHttpBinding and WsHttpBinding

If we want to summarize in one sentence, the difference between WsHttpBinding and BasicHttpBinding is that WsHttpBinding supports WS-* specification. WS-* specifications are nothing but standards to extend web service capabilities.

Below is a detailed comparison table between both the entities from security, compatibility, reliability and SOAP version perspective.

Criteria	BasicHttpBinding	WsHttpBinding
Security support	This supports the old ASMX style, i.e. WS-BasicProfile 1.1.	This exposes web services using WS-* specifications.
Compatibility	This is aimed for clients who do not have .NET 3.0 installed and it supports wider ranges of clients. Many of the clients like Windows 2000 still do not run .NET 3.0. So older version of .NET can consume this service.	As its built using WS-* specifications, it does not support wider ranges of client and it cannot be consumed by older .NET version less than 3 version.
Soap version	SOAP 1.1	SOAP 1.2 and WS-Addressing specification.
Reliable messaging	Not supported. In other words, if a client fires two or three calls you really do not know if they will return back in the same order.	Supported as it supports WS-* specifications.
Default security options	By default, there is no security provided for messages when the client calls happen. In other words, data is sent as plain text.	As WsHttBinding supports WS-*, it has WS-Security enabled by default. So the data is not sent in plain text.
Security options	None Windows – default authentication Basic Certificate	None Transport Message Transport with message credentials

One of the biggest differences you must have noticed is the security aspect. By default, BasicHttpBinding sends data in plain text while WsHttpBinding sends it in encrypted and secured manner.

when to create interfaces

I got the thread from MSDN about when to create interfaces.really it will helpful to understand the need of the creating interface. some points from the thread.

• Interfaces are better suited to situations in which your applications require many possibly unrelated object types to provide certain functionality.

• Interfaces are more flexible than base classes because you can define a single implementation that can implement multiple interfaces.

• Interfaces are better in situations in which you do not have to inherit implementation from a base class.

• Interfaces are useful when you cannot use class inheritance. For example, structures cannot inherit from classes, but they can implement interfaces.

MSDN:when to create interfaces

Search in Datagridview

                 This is a sample for search datagridview for example.  The user will  type the employee number into the textbox and click on the search button.  then we want the focus to move to the row with the employee number .

private void SelectGridRow(int id)
{
 foreach (DataGridViewRow row in dataGridView1.Rows)
 {
  //assume col 0 as emp column
  if (dataGridView1.Rows[row.Index].Cells[0].Value.Equals(id))
  {
   dataGridView1.Rows[row.Index].Selected = true;
   dataGridView1.Rows[row.Index].DefaultCellStyle.BackColor = Color.Coral;
   dataGridView1.FirstDisplayedScrollingRowIndex = row.Index;
   return;
  }

 }
} 

Transactions

    Transactions provide a way to group a set of actions or operations into a single indivisible unit of execution. A transaction is a collection of operations with the following properties:

• Atomicity. This ensures that either all of the updates completed under a specific transaction are committed and made durable or they are all aborted and rolled back to their previous state.
  
  
• Consistency. This guarantees that the changes made under a transaction represent a transformation from one consistent state to another. For example, a transaction that transfers money from a checking account to a savings account does not change the amount of money in the overall bank account.
  
  
• Isolation. This prevents a transaction from observing uncommitted changes belonging to other concurrent transactions. Isolation provides an abstraction of concurrency while ensuring one transaction cannot have an unexpected impact on the execution of another transaction.
  
  
• Durability. This means that once committed, updates to managed resources (such as a database record) will be persistent in the face of failures.