7.11.2012

SQL Server Difference FAQs-1

  1. What are the Differences between TRUNCATE and Delete?
S.No
Truncate
Delete
1
Truncate is faster
Delete is comparatively slower
2
Removes all rows from a table
Can remove specific rows with Where clause
3
Is DDL Command
Is DML Command
4
Resets identity of the table
Does not reset identity of the table
5
Removes 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.
6
Cannot be rolled back
Can be rolled back

  1. What are the differences between Primary key and Unique key?
S.No
Primary Key
Unique Key
1
Creates Clustered index
Creates Non-Clustered index
2
Null values are not allowed.
Allows only one null value.
3
We can have only one Primary key in a table.
We can have more than one unique key in a table.
4
Primary 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.No
Clustered Indexes
Non-Clustered Indexes
1
It reorders the physical storage of records in the table
It sorts and maintain a separate storage
2
There can be only one Clustered index per table
We can have 249 non-clustered indexes in a table
3
The leaf nodes contain data
The 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.No
Stored Procedures
User Defined Functions
1
Stored Procedure cannot be used in a Select statement
User Defined Function can be used in a Select statement
2
Stored procedure supports Deferred Name Resolution
User Defined Function does not support Deferred Name Resolution
3
Stored Procedures are generally used for performing Business Logic
User Defined Functions are generally used for Computations
4
Stored Procedure need not return a value
User Defined Functions should return a value
5
Stored Procedures can return any datatype
User Defined Functions cannot return Image
6
Stored Procedures can accept more number of input parameters than User Defined Functions. Stored Procedures can have upto 21000 input parameters
User Defined Functions accept lesser number of input parameters than Stored Procedures. UDF can have upto 1023 input parameters
7
Stored Procedures can use Temporary Tables
Temporary Tables cannot be used in a User Defined Function
8
Stored Procedures can execute Dynamic SQL
User Defined Functions cannot execute Dynamic SQL
9
Stored Procedure supports error handling
User Defined Function does not support error handling. RAISEERROR or @@ERROR are not allowed in UDFs
10
Non-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)

  1. What are the differences between Where and Having clauses?
S.No
Where clause
Having clause
1
It applies to individual rows
It applies to a group as a whole
2
It selects rows before grouping
It selects rows after grouping
3
It cannot contain aggregate functions
It can contain aggregate functions
4
It can be used in select, delete ,insert etc.
It is used only in select clause

  1. What are the differences between Union and UnionAll?
S.No
Union
UnionAll
1
This is used to eliminate duplicate rows
It will not eliminate duplicate rows
2
This selects only distinct rows
It selects all the values
3
It can be used to combine any number of queries
It can be used to combine maximum of 2 queries
4
It cannot contain aggregate functions
It can contain aggregate functions
5
Union is slower than UnionAll
UnionAll 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.No
Select statement
Cursor
1
Select statements are used for table-level processing
Cursors are used for row-level processing

8) Difference between Primary Key and Foreign Key
S.No
Primary Key
Foreign Key
1
Primary key uniquely identify a record in the table.
Foreign key is a field in the table that is primary key in another table.
2
Primary Key cannot accept null values.
Foreign key can accept multiple null values.
3
By 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.
4
We can have only one Primary key in a table.
We can have more than one foreign key in a table.

6 comments: