question

sathishkumar avatar image
sathishkumar asked

Explain about Function

How to create a function? when its good to use in sql?
functions
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
This is for SQL 2008R2, but the syntax hasn't really changed for the last few editions of sql server: [CREATE FUNCTION][1] (MSDN can be really useful) A function is useful in T-SQL in the same respect as just about any other language - abstraction, code re-use and refactoring instantly spring to mind. It is one of the basic programming ideas that gets hammered home from the first moment you really try to learn a programming language. [1]: http://msdn.microsoft.com/en-us/library/ms186755.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered
+1 to William, but it's worth noting that Scalar Functions and Table-Valued functions often lead to degradation in performance. Scalar functions do so because they are interpreted on each run, and not in-lined to the main query plan. And Table-Valued functions can cause issues because their row count is estimated as 1 row, so can cause issues with inappropriate plans being chosen. Using inline table functions is fine, and they can be used in place of scalar functions by using CROSS APPLY.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
Most definitely Matt (+1)
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
I don't mind at all pounding home the idea that the multi-statement table valued functions look quite attractive, but they are very, very dangerous. Performance for these is based on the use of table variables which means they have no statistics so they're inclusion in execution plans is based on returning a single row. As long as they do only return one or a few rows they might not be too bad, but when they start returning hundreds or thousands of rows performance degrades seriously and quickly.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.