SQL Server -- Stored Procedure vs UDF

Difference between Stored Procedure and User Defined Function (UDF)

UDF (User Defined Function)

• User Defined Functions (UDFs) can to executed in the SQL statements anywhere in the WHERE/HAVING/SELECT section.
• UDFs that return tables can be treated as another dataset. This can be used in JOINs with other tables.
• A UDF returns table variables.
• UDFs can't change the server environment or your operating system environment.
• Inline UDF’s can be though of as views that take parameters and can be used in JOINs and other  Rowset operations.
• UDF's doesn't support error handling. TRY - CATCH cannot be used in User defined functions.

Stored Procedure

• Stored procedures can not be used in WHERE/HAVING/SELECT section, they have to use EXECUTE or EXEC to run.
• SPROC can't return a table variable although it can create a table.
• SPROCs can change server or operating system environment variables by using the system store procedure. (eg: Change the values for job execution (enable or disable)
• Stored Procedure can have a function in it, while a function cannot have a Stored Procedure in it.

Difference between Primary Key and Unique Key in SQL Server

Difference between Primary key and Unique Key
(Primary key vs Unique key)

• Both primary key and unique enforce uniqueness of the column on which they are defined.

• But, by default, primary key creates a clustered index on the column, where as unique key creates a non- clustered index by default.

• Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.

• Also, there can only be one primary key, while there can be many Unique keys

Difference between Delete and Truncate in SQL Server

Delete vs Truncate
 
Delete

• Delete command removes the rows from a table based on the condition that we provide with a WHERE clause
• DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
• DELETE does not reset identity of the table. So, if you want to retain the identity counter, use DELETE.
• DELETE Activates Triggers.
• DELETE Can be Rolled back.
• DELETE is DML Command.

Truncate

• Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.
• TRUNCATE removes the data by de-allocating the data pages used to store the table’s data, and only the page de-allocations are recorded in the transaction log.
• TRUNCATE Resets identity of the table.
• Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
• TRUNCATE can not be Rolled back.
• Truncate is a DDL Command.
• You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.