SQL Server Difference FAQs-8

1.Difference between Constraints and Triggers
S.No Constraints Triggers
1 Once we define some constraint in a table they will be stored along with table definition It will be stored as separate object
2 Constraints will do memory location to table comparison. Triggers will do table to table comparison.For this triggers will use magic tables(inserted,deleted).
3 In the order of precedence first Constraints will be fired In the order of precedence only after Constraints is fired,then only Triggers will be fired
4 Performance wise Constraints will not give best performance because memory location to table comparison is slower than table to table comparison.
Performance wise triggers will give best performance because table to table comparison is faster than memory
location to table comparison.
5 Constraints cannot start a chain reaction as like triggers - for instance each delete, update action etc. can trigger off another function
Triggers are used to carry out tasks which cant be done using constraints.
For eg:-A change in the "sal" column of a table should change the "tax" column in another table.This cant be done using constraints.It has to be done using triggers.Thats where the importance of triggers lie.
6 Constraint is used for column Trigger is used for table
Constraints are predefined business rules in which all the
organizations follow this constraints without any
Trigger is a user defined business rule for which user is responsible for logic for business rule
Constraints are used to maintain the integrity and atomicity of database .In other words it can be said they are used to prevent invalid data entry . the main 5 constraints are
Triggers are basically stored procedures which automatically fired when any insert,update or delete is issued on table

2.Difference between Cast and Convert in SQL Server

S.No Cast Convert
1 Cast is ANSII Standard Convert is Specific to SQL SERVER
2 Cast cannot be used for Formatting Purposes. Convert can be used for Formatting Purposes.For example Select convert (varchar, datetime, 101)
3 Cast cannot convert a datetime to specific format Convert can be used to convert a datetime to specific format
Usage of CAST:

USE Sample
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS int) LIKE '3%';
Usage of CONVERT:

USE Sample
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CAST(int, ListPrice) LIKE '3%';

3.Difference between CUBE and ROLLUP

1 It is an additional switch to GROUP BY clause. It can be applied to all aggregation functions to return cross tabular result sets. It is an extension to GROUP BY clause. It’s used to extract statistical and summarized information from result sets. It creates groupings and then applies aggregation functions on them.
2 Produces all possible combinations of subtotals specified in GROUP BY clause and a Grand Total. Produces only some possible subtotal combinations

No comments:

Post a Comment