Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Friday, July 20, 2012 9:57 AM
Why DML operations are not allowed in functions in SQL Server?
Why this restriction is there.
It is just to make use of Deterministic and Non-Deterministic or any other difference exists.
Friday, July 20, 2012 10:30 AM ✅Answered
Only READ-ONLY database access in functions. If DML operations would be allowed in functions, then functions would be pretty similar to stored procedures. The way it is, a stored procedure can use a function, but not vice versa. Therefore, functions increase the "power" of stored procedures, queries and T-SQL scripts. UDF-s are also pitched as super-views.
User-defined functions were introduced in SQL Server 2000.
Quote from new features: "User-defined Functions
User-defined functions are similar to stored procedures and can be executed as such, or they can be executed in the same fashion as a system function. Like stored procedures, they accept zero or more input parameters, but can return a scalar data type such as int, decimal, varchar, or sql_variant in addition to the new table data type.
The defining structure of a user-defined function is similar to that of a stored procedure. The main differences are the requirement to specify the RETURNS value and to terminate the function with the RETURN statement.
A user-defined function that returns a table data type can declare an internal table variable and return that variable as its return value. These are known as rowset functions, and can be used where table or view expressions are allowed in Transact-SQL queries. This means a table returned by a user-defined function can be referenced in the from clause of a Transact-SQL statement, while stored procedures that return resultsets cannot.
User-defined functions that return a table can be a powerful alternative to views. While views are limited to a single select statement, user-defined functions can contain additional statements that allow more powerful logic than is possible in views."
http://msdn.microsoft.com/en-us/magazine/bb985066.aspx
Related forum discussion:
http://stackoverflow.com/questions/1179758/function-vs-stored-procedure-in-sql-server
Kalman Toth SQL SERVER 2012 & BI TRAINING
Friday, July 20, 2012 10:03 AM
Functions are not allowed to do any data manipulation like insert, update, and etc, and this is the rule! Use SPs instead and read this
Many Thanks & Best Regards, Hua Min
Friday, July 20, 2012 10:21 AM
Hi,
For reason, read Gail Shaw's comments in below post
http://www.sqlservercentral.com/Forums/Topic1180479-391-1.aspx
- Chintak (My Blog)
Friday, July 20, 2012 10:24 AM
DML changes the state of the database server which is not the actual motive behind creating a function. Functions are for replacing repeatative heavily used logic, where fetching of data is to be used.
regards
joon
Monday, July 23, 2012 5:48 AM
Thank you!
This sounds to be the best reply, I never read this difference in any earlier posts.