SQL Server Difference FAQs-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.
- 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.
- 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 4To 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 5By 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.
- 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)
- 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.
- 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 6Output is in sorted orderExample :SELECT ColFROM @Table1UNIONSELECT ColFROM @Table2Result:1235Output is not in sorted orderExample :SELECT ColFROM @Table1UNION ALLSELECT ColFROM @Table2Result:12325
- 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.