Monday, November 10, 2014

SQL Server Difference FAQs-1

SQL Server Difference FAQs-1

  1. What are the Differences between TRUNCATE and Delete?
S.NoTruncateDelete
1Truncate is fasterDelete is comparatively slower
2Removes all rows from a tableCan remove specific rows with Where clause
3Is DDL CommandIs DML Command
4Resets identity of the tableDoes not reset identity of the table
5Removes the data by deallocating the data pages and logs the deallocation.Removes one row at a time and records an entry in the transaction log for each deleted row.
6Cannot be rolled backCan be rolled back
Remarks:TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. To remove the table definition in addition to its data, use the DROP TABLE statement.

  1. What are the differences between Primary key and Unique key?
S.NoPrimary KeyUnique Key
1Creates Clustered indexCreates Non-Clustered index
2Null values are not allowed.Allows only one null value.
3We can have only one Primary key in a table. We can have more than one unique key in a table. 
4Primary key can be made foreign key into another table. Unique key cannot be made foreign key into another table. 

  1. What are the Differences between Clustered Indexes and Non-Clustered Indexes?

S.NoClustered IndexesNon-Clustered Indexes
1It reorders the physical storage of records in the tableIt sorts and maintain a separate storage
2There can be only one Clustered index per tableWe can have 249 non-clustered indexes in a table
3The leaf nodes contain dataThe leaf node contains pointer to data
4
To create clustered index Sql server required more memory because the leaf pages in the tree structure will maintain actual data .
To create non-clustered index Sql server requires less memory because the leaf pages will contain pointers to actual data
5
By using clustered index retrieving data is more faster,when we compare with non-clustered index.
By using non-clustered index retrieving data is slower than clustered index.

  1. What are the differences between Stored Procedures and User Defined Functions?
S.NoStored ProceduresUser Defined Functions
1Stored Procedure cannot be used in a Select statementUser Defined Function can be used in a Select statement
2Stored procedure supports Deferred Name ResolutionUser Defined Function does not support Deferred Name Resolution
3Stored Procedures are generally used for performing Business LogicUser Defined Functions are generally used for Computations
4Stored Procedure need not return a valueUser Defined Functions should return a value
5Stored Procedures can return any datatypeUser Defined Functions cannot return Image
6Stored Procedures can accept more number of input parameters than User Defined Functions. Stored Procedures can have upto 21000 input parametersUser Defined Functions accept lesser number of input parameters than Stored Procedures. UDF can have upto 1023 input parameters
7Stored Procedures can use Temporary TablesTemporary Tables cannot be used in a User Defined Function
8Stored Procedures can execute Dynamic SQLUser Defined Functions cannot execute Dynamic SQL
9Stored Procedure supports error handlingUser Defined Function does not support error handling. RAISEERROR or @@ERROR are not allowed in UDFs
10Non-deterministic functions can be used in Stored Procedures.Non-deterministic functions cannot be used in User Defined Functions (UDFs). For example, GETDATE() cannot be used in User Defined Functions(UDFs)

Basic Difference

  1. Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).
  2. Functions can have only input parameters for it whereas Procedures can have input/output parameters .
  3. Functions can be called from Procedure whereas Procedures cannot be called from Function.

Advance Difference

  1. Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
  2. Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
  3. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
  4. The most important feature of stored procedures over function is to retention and reuse the execution plan while in case of function it will be compiled every time.
  5. Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.
  6. Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
  7. Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
  8. We can go for Transaction Management in Procedure whereas we can't go in Function.
  1. What are the differences between Where and Having clauses?
S.NoWhere clauseHaving clause
1It applies to individual rowsIt applies to a group as a whole
2It selects rows before groupingIt selects rows after grouping
3It cannot contain aggregate functionsIt can contain aggregate functions
4It can be used in select, delete ,insert etc.It is used only in select clause
The WHERE clause is evaluated before rows are grouped, and therefore is evaluated per row. The HAVING clause is evaluated after rows are grouped, and therefore is evaluated per group.
  1. What are the differences between Union and UnionAll?
S.NoUnionUnionAll
1This is used to eliminate duplicate rowsIt will not eliminate duplicate rows
2This selects only distinct rowsIt selects all the values
3It can be used to combine any number of queriesIt can be used to combine maximum of 2 queries
4It cannot contain aggregate functionsIt can contain aggregate functions
5Union is slower than UnionAllUnionAll is faster than Union
6
Output is in sorted order

Example :
SELECT Col
FROM @Table1
UNION
SELECT Col
FROM @Table2

Result:
1
2
3
5
Output is not in sorted order

Example :
SELECT Col
FROM @Table1
UNION ALL
SELECT Col
FROM @Table2

Result:
1
2
3
2
5

  1. What is the difference between normal Select statement and a Cursor?
S.NoSelect statementCursor
1Select statements are used for table-level processingCursors are used for row-level processing

8) Difference between Primary Key and Foreign Key
S.NoPrimary KeyForeign Key
1Primary key uniquely identify a record in the table. Foreign key is a field in the table that is primary key in another table. 
2Primary Key cannot accept null values. Foreign key can accept multiple null values. 
3By default, Primary key is clustered index and data in the database table is physically organized in the sequence of clustered index. While Foreign key is non-clustered index. 
4We can have only one Primary key in a table. We can have more than one foreign key in a table. 

No comments:

Post a Comment