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
7	Constraints are predefined business rules in which all the organizations follow this constraints without any modification.	Trigger is a user defined business rule for which user is responsible for logic for business rule
8	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 NOT NULL,PRIMARY KEY,FOREIGN KEY,UNIQUE KEY and CHECK	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
4	Usage of CAST: USE Sample GO SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice FROM Production.Product WHERE CAST(ListPrice AS int) LIKE '3%'; GO	Usage of CONVERT: USE Sample GO SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice FROM Production.Product WHERE CAST(int, ListPrice) LIKE '3%'; GO

3.Difference between CUBE and ROLLUP

S.No	CUBE	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