SQL Server Difference FAQs-1

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

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.

2. 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.

3. 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.

4. 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)

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.

5. 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

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.

6. 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

7. 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.