Differences....

Differences Between Funtion and Procedure

• Procedure can return zero or n values whereas function can return one value which is mandatory.
• Procedures can have input/output parameters for it whereas functions can have only input parameters.
• Procedure allows select as well as DML statement in it whereas function allows only select statement in it.
• functions can be called from Procedure whereas Procedures cannot be called from function.
• Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a function.
• We can go for transaction management in Procedure whereas we can't go in function.
• Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.
• UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored Procedures cannot be.
• UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
• Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

Differences Between Char , Varchar and Nvarchar

• Char datatype is used to store fixed length of characters. For example , if we declared char(10) it will allocates memory for 10 characters. Once we insert only 6 characters of word then only 6 characters of memory will be used and other 4 characters of memory will be wasted.
• Varchar means variable characters and it is used to store non-unicode characters. It will allocate the memory based on number characters inserted. For example, if we declared varchar(10) it will allocates memory of 0 characters at the time of declaration and Once we insert only 6 characters of word it will allocate memory for only 6 characters.
• Nvarchar datatype same as varchar datatype but only difference nvarchar is used to store Unicode characters and it allows you to store multiple languages in database. nvarchar datatype will take twice as much space to store extended set of characters as required by other languages.

Differences Between Primary Key and Unique Key

• Primary Key creates clustered Index by default , where as Unique Key Create NoN-Clustered Index
• Primary Key doesn't allow NULLs , where as Unique Key allows only one NULL value

Differences Between Delete ,Drop and Truncate

DELETE

• Delete is a DML Command
• We can use where clause to filter data, It deletes specified data if where condition exists
• DELETE statement is executed using a row lock, each row in the table is locked for deletion
• DELETE retain the identity
• Delete activates a trigger because the operation are logged individually
• Slower than truncate because, it keeps logs
• Rollback is possible

DROP

• DROP is a DDL Command
• Removes all rows and also the table definition, including indexes, triggers, grants, storage parameters
• No filter criteria allowed, removes all rows and No triggers fired
• DROP ANY privilege on a specific table cannot be granted to another user or role.
• Drop operation cannot be rolled back in any way as it is an auto committed , while statementDelete operation can be rolled back and it is not auto committed.

TRUNCATE

• TRUNCATE is a DDL command
• It Removes all the data
• TRUNCATE TABLE always locks the table and page but not each row
• If the table contains an identity column, the counter for that column is reset to the seed value that is defined for the column
• TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions
• Faster in performance wise, because it doesn't keep any logs
• Rollback is possible with TRANSACTION