SQL Server Interview Questions on 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?

Basic SQL Server Interview Questions

Explain DML, DDL, DCL and TCL statements with examples?
DML: DML stands for Data Manipulation Language. DML is used to retrieve, store, modify, delete, insert and update data in database.
Examples of DML statements: SELECT, UPDATE, INSERT, DELETE statements.

DDL: DDL stands for Data Definition Language. DDL is used to create and modify the structure of database objects.

Examples: CREATE, ALTER, DROP statements.

DCL: DCL stands for Data Control Language. DCL is used to create roles, grant and revoke permissions, establish referential integrity etc.
Examples: GRANT, REVOKE statements

TCL: TCL stands for Transactional Control Language. TCL is used to manage transactions within a database.
Examples: COMMIT, ROLLBACK statements



What is the difference between Drop, Delete and Truncate statements in SQL Server?
Drop, Delete and Truncate - All operations can be rolled back.

All the statements (Delete, Truncate and Drop) are logged operations, but the amount of information that is logged varies. Delete statement logs an entry in the transaction log for each deleted row, where as Truncate Table logs only the Page deallocations.Hence, truncate is a little faster than Delete.

You can have a where clause in Delete statement where as Truncate statement cannot have a where clause. Truncate will delete all the rows in a Table, but the structure of the table remains. Drop would delete all the rows including the structure of the Table.


Please refer to the screen shot below for the differences summary snapshot between Drop, Delete and Truncate statements in SQL Server.


What is Cascading referential integrity constraint?
Cascading referential integrity constraints allow you to define the actions Microsoft SQL Server should take when a user attempts to delete or update a key to which an existing foreign keys point.

You can instruct SQL Server to do the following:

1. No Action: This is the default behaviour. No Action specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, an error is raised and the DELETE or UPDATE is rolled back.

2. Cascade: Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted or updated.

3. Set NULL: Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are set to NULL.

4. Set Default: Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are set to default values.

DIfference between primary key and unique key in SQL Server?
1. A table can have only one primary key. On the other hand a table can have more than one unique key.
2. Primary key column does not accept any null values, where as a unique key column accept one null value.

SQL Server Interview Questions

Here i will explain all possible question in SQL ...

Basic SQL Server Interview Questions
SQL Server Interview Questions on Temporary Tables
SQL Server Interview Questions on Indexes - Part 1
SQL Server Interview Questions on Indexes - Part 2
What is the difference between a Temporary Table and a Table Variable
What is the use of COALESCE in SQL Server
SQL Server Interview Questions on triggers


Difference between User Defined Function and Stored Procedure
SQL Server Interview Questions on Views - Part 1
SQL Server Interview Questions on Views - Part 2
Explain Inner Join with an example
Explain Left Outer Join with an example
Explain Right Outer Join with an example
Explain Full Outer Join with an example
Explain Self Join with an example
What is the difference between Index Scan and Index Seek
What are the advantages of using stored procedures?
What are the different ways to replace NULL values in SQL Server?
SQL Server interview questions on string manipulation functions
Write a Stored Procedure that takes column name as a parameter and returns the result sorted by the column that is passed
What is deferred name resolution in SQL Server? 

WCF In Dot Net

Introduction of WCF:  WCF stands for Windows Communication Foundation and is part of .NET 3.0. WCF is Microsoft platform for building distributed and interoperable applications.

What is a distributed application or connected systems?

Distributed application, is an application where parts of it run on 2 or more computers.

Why distributed applications is used?

1. An enterprise application may need to use the services provided by other enterprises. For example an ecommerce application may be using Paypal service for payments.
2. For better scalability. An enterprise web application may have Presentation tier, Business tier, and Data Access tier, and each tier may be running on a different machine.

What is an interoperable application?
An application that can communicate with any other application that is built on any platform and using any programming language is called as an interoperable application. Web services are interoperable, where as .NET remoting services are not.

Web services can communicate with any application built on any platform, where as a .NET remoting service can be consumed only by a .net application.

What technology choices did we have before WCF to build distributed applications?
Enterprise Services
Dot Net Remoting
Web Services

Why should we use WCF?
We have 2 clients and we need to implement a service a for them. 
1. The first client is using a Java application to interact with our service, so for interoperability this client wants messages to be in XML format and the protocol to beHTTP.
2. The second client uses .NET, so for better performance this client wants messages formatted in binary over TCP protocol.

Without WCF
1.To satisfy the first client requirement we end up implementing an ASMX web service, and 

2. To satisfy the second client requirement we end up implementing a remoting service 

These are 2 different technologies, and have complete different programming models.So the developers have to learn different technologies. 

So to unify and bring all these technologies under one roof Microsoft has come up with a single programming model that is called as WCF - Windows Communication Foundation.

With WCF,
You implement one service and we can configure as many end points as want to support all the client needs. To support the above 2 client requirements, we would configure 2 end points. In the endpoint configuration we can specify the protocols and message formats that we want to use.