Best SQL server question and answers on differences

Few question and answers on differences in SQL SERVER

Here I have posted some concepts in SQL server which I got from another source 

1.    

                           Differences between Functions and Procedures?

             

Procedures	Functions
Procedures can be used for performing business logic.	Function can be used for computations.
A procedure need not to return a value.	A function must return a value.
It can return more than one value.	Function can return only one value.
Return values using out parameters.	Returns value using Return expression.
Procedure can’t be called in SELECT statement.	Function can be called in SELECT statement.
They can use Temporary tables.	They cannot be used Temporary tables.
These can support Error Handling.	Raise error,@@ERROR are not allowed.
PRINT and EXEC statements can be written in Procedures.	PRINT and EXEC statements cannot be written in Functions.

     Differences between Temporary Table and Table Variable?

Temporary Table	Table Variable
It is created in TempDB.	It is created in the memory.
You cannot pass TT as parameter to Functions and Stored Procedures.	You can pass TV as parameter to Functions and Stored Procedures.
A TT can have indexes.	A TV can have only a primary index.
A TT can involve in Transactions, Logging (or) Locking.	A TV cannot involve in these. This makes TV faster than a TT.

     Differences between Triggers and Stored Procedures?

Procedures	Triggers
Procedures called explicitly.	Triggers called implicitly.
Procedures can be created without a table.	Triggers can’t be created without a table.
Procedures accept parameters.	Triggers won’t accept parameters.
Procedures return a value.	Triggers won’t return a value.

       Differences between Clustered index and Non clustered index?

Clustered Index	Non clustered Index
Clustered index will alter the physical representation of rows in a table.	This index will not alter the physical representation of rows in a table.
A table can have only One clustered index.	A table can have more non clustered indexes. [In 2005-249, In 2008-999]
It always arranges the data of a table in sorted order.	Data of a table is not arranged in order.
Data pages and Index pages are stored at same level.	Data pages and Index pages are stored at different levels.

  Differences between Star Schema and Snow Flake Schema?  

Star Schema	Snow Flake Schema
Here Dimensions connect to Fact table and resembles like a star.	Just like a star schema but having sub dimensions.
De normalized Data.	Normalized Data.
Less Joins.	More Joins.
More Memory.	Less Memory.
It is Easy to understand.	Complex to understand.
Indexes are more.	Indexes are less.
Performance high.	Performance low.

         Differences between Table and View?

Table	View
Table contains data.	View contains no data only stored queries.
Tables are limited (2 Billion).	No limit for views.
DML operations on any data.	DML operations on related data.
Tables can be associated to any trigger.	Views can be associated with only Instead of Trigger.

     Differences between Simple View and Complex View?

Simple View	Complex View
Simple view is based on one table.	Complex view is based on one (or) more tables.
Simple views can update. [update means allows DML operations]	Complex views  cannot update

         Differences between View and Synonym?

View	Synonym
View is a subset of table.	Synonym is a mirror of table.
View can be based on one (or) more tables.	Synonym is always based on one table.

         Differences between View and Index View?

View	Index View
When view is created the query is stored in DB.	When Index View is created the query result stored in DB.
A View doesn’t contain any data.	Index view contains data.

      Differences between UNION and JOIN?

UNION	JOIN
Accumulates the Data	Relates the Data
Two Dissimilar structures can’t be combined with UNION operator.	Two Dissimilar structures can be Joined.
It combines the result of two Select statements.	It combines the columns from 2 or more tables with joining condition.

     Differences between WHERE and HAVING clauses?

WHERE	HAVING
It is used to filter the data before Grouped.	It is used to filter the data after Grouped.
It can be used in SELECT, INSERT, UPDATE, DELETE statements.	It can be used in only SELECT statement.
Aggregation functions are not used in Where clause.	Aggregation functions are used in Having clause.

   Differences between DELETE and TRUNCATE?

DELETE	TRUNCATE
It is a DML command.	It is a DDL command.
It supports to delete the rows on conditional basis (where clause).	No conditional deletion.
It will not reset the Identity Column.	It will reset the Identity Column.
It is Logged Action.	It is not Logged Action.
It does not de allocate Data pages (memory).	It will de allocate Data pages.
In this Restoring is possible.	In this Restoring is not possible.
Slow in Execution, since it consult log file to store each deleted row.	Fast in Execution, since it does not consult log file.