understand when to use user defined functions in sql server tech-republic

Understand when to use user-defined functions in SQL Server | TechRepublic

http://www.techrepublic.com/blog/datacenter/understand-when-to-use-user-defined-functions-in-sql-server/171[08/29/2012 3:08:18 PM]

Blogs Downloads Newsletters Galleries Q&A Discussions News

Research Library

Home / Blogs / The Enterprise Cloud

The Enterprise Cloud

Understand when to use user-defined functions in SQLServerBy Tim ChapmanSeptember 3, 2007, 11:49 PM PDT

In the simplest terms, a user-defined function (UDF) in SQL Server is a programming constructthat accepts parameters, does work that typically makes use of the accepted parameters, andreturns a type of result. This article will cover two types of UDFs: table-valued and scalar-valued.(I will not be covering aggregate functions.)

Types of UDFsTable-valued functionsA table-valued UDF is a function that accepts parameters and returns the results in the form of atable. This type of function is special because it returns a table that you can query the results ofand join with other tables. In SQL Server 2005, field values from other tables may be passed intothe function during a join operation to return a record based result. To accomplish this, you mustuse SQL Server 2005s APPLY operator.

It can be difficult to know when it is appropriate to use a VIEW vs. when it is appropriate to use atable-valued UDF. VIEWs are a great tool for data abstraction, combining data, and logically usingsubsets of data. I like to use table-valued UDFs when I need to use one or more values fromdifferent tables in a join operation where some type of calculation needs to be done and anaggregation returned.

Scalar-valued functionsA scalar-valued UDF accepts parameters and, ultimately, returns a single, atomic value. There areseven reasons why these types of functions are different than stored procedures in the databaseengine.

You cannot modify data inside of a UDF.

A scalar-valued UDF returns only one value, where a stored procedure can have numerousOUTPUT parameters.

You can use scalar-valued UDFs as the default value for a column in a table.

Scalar-valued UDFs are an easy way to define constant values to use in your databaseenvironment.

You can pass field values as parameters into UDFs.

Follow this blog:

IT Management Development IT Support Data Center Networks Security

Log In Join TechRepublic FAQ Go Pro!ZDNet Asia SmartPlanet TechRepublic

http://ad.doubleclick.net/click;h=v8/3ce0/0/0/%2a/d;44306;0-0;0;74341537;31-1/1;0/0/0;;~sscs=%3fhttp://www.techrepublic.com/http://www.techrepublic.com/http://www.techrepublic.com/blogshttp://www.techrepublic.com/downloadshttp://www.techrepublic.com/newslettershttp://www.techrepublic.com/photoshttp://www.techrepublic.com/forum/questionshttp://www.techrepublic.com/forum/discussionshttp://www.techrepublic.com/newshttp://www.techrepublic.com/research-libraryhttp://www.techrepublic.com/http://www.techrepublic.com/blogshttp://www.techrepublic.com/blog/datacenterhttp://ad.doubleclick.net/click;h=v8/3ce0/0/0/%2a/j;44306;0-0;0;74341537;3823-300/100;0/0/0;;~sscs=%3fhttp://www.techrepublic.com/search?q=tim+chapmanhttp://articles.techrepublic.com.com/5100-9592-6108869.htmlhttp://www.techrepublic.com/blog/datacenter?mode=rsshttp://www.techrepublic.com/alerts/add?url=http%3A%2F%2Fwww.techrepublic.com%2Fblog%2Fdatacenter%3Fmode%3Drss&title=The+Enterprise+Cloud+on+TechRepublic&source=http%3A%2F%2Fwww.techrepublic.com%2Fblog%2Fdatacenter%2Funderstand-when-to-use-user-defined-functions-in-sql-server%2F171&frequency=weekly&rss_type=38http://www.techrepublic.com/members/login?regSrc=global-reghttp://www.techrepublic.com/members/join?regSrc=global-reghttp://www.techrepublic.com/faq/generalhttp://www.techrepublic.com/prohttp://www.zdnetasia.com/http://www.smartplanet.com/http://www.techrepublic.com/



You can nest scalar function calls. This means that you can pass a call to a scalar-valuedfunction to another function or stored procedure.

You can use the results from scalar-valued UDFs as criteria in a WHERE statement. Althoughyou can do it, this is typically not a good idea. (Later in the article, Ill explain why I try to avoidthis common practice.)

There are two types of scalar-valued UDFs: deterministic and non-deterministic. Recognizing thedeterminism of the functions that are created is important. An example of the importance is thecreation of indexed views. One of the many restrictions of creating an index on a view is that theview definition cannot use a non-deterministic function.

DeterministicA deterministic UDF always returns the same result with the same set of input parameters. Someexamples of deterministic functions are the system functions MONTH(), YEAR(), and ISNULL().

Non-deterministicA non-deterministic UDF is can potentially return a different value each time it is called with thesame set of input parameters. Some examples of non-deterministic functions are the systemfunctions GETDATE(), NEWID(), and @@CONNECTIONS.

Two examples of UDFsBefore presenting the examples, I will set up my SalesHistory table and load data into it:

IF OBJECT_ID('SalesHistory')>0 DROP TABLE SalesHistory;

CREATE TABLE [dbo].[SalesHistory] ( [SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, [Product] [varchar](10) NULL, [SaleDate] [datetime] NULL, [SalePrice] [money] NULL )

DECLARE @i SMALLINT SET @i = 1

WHILE (@i



SELECT @Sales = SUM(SalePrice) FROM SalesHistory WHERE Product = @Product AND SaleDate BETWEEN @BeginDate AND @EndDate

RETURN(@Sales) END

The script below calls the UDF created in the above script. Note: The schema the functionbelongs to must be used in the call. In this case, the function belongs to the dbo schema.

SELECT dbo.udf_GetProductSales('PoolTable', '1/1/1990', '1/1/2000')

I usually discourage using scalar-valued UDFs in a WHERE criteria statement because, for everyrecord considered in the query, the scalar-valued function will be called. This means that afunction used in the WHERE criteria will cause a scan of the values being searched, which isgoing to be slower than if an index is able to be used. (I will provide more details on this conceptin a future article.)

Although the use of a correlated sub-query is sometimes confusing and complicated, the use ofthem can help solve some of the more challenging query problems. While using these specialqueries is useful, they only return one column of data. You can use the upgraded table-valuedUDFs in SQL Server 2005 to overcome this shortcoming. Ill show you how to use the APPLYoperator to accept column values from a table and return a table-result of correlated values.

CREATE FUNCTION dbo.udf_GetProductSalesTable ( @Product VARCHAR(10), @SaleID INT ) RETURNS @SalesTable TABLE ( SalesTotal MONEY, SalesCount INT )

BEGIN INSERT INTO @SalesTable(SalesTotal, SalesCount) SELECT SUM(SalePrice), COUNT(SaleID) FROM SalesHistory WHERE Product = @Product AND SaleID



7Comments

Add Your Opinion

See All CommentsMy Contacts

Get IT Tips, news, and reviews delivered directly to your inbox by subscribing to TechRepublics freenewsletters.

About Tim Chapman

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, andhas more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 andSQL Server 2005. If you would like to contact Tim, please e-mail him at [email protected].

Get SQL tips in your inboxTechRepublics free SQL Server newsletter, delivered each Tuesday, contains hands-on tips thatwill help you become more adept with this powerful relational database management system.Automatically subscribe today!

Join the conversation!

Full Bio Contact

Talking intelligently aboutvirtualization

Defragment your WindowsServer 2003 hard drive on aset schedule

Follow via:

Staff Picks Top Rated Most Recent

Can you please give some more details on when it is more appropriate to use a Viewvs a table-valued UDF? I have a few table-valued UDFs created that now I feelmaybe should have been views, but I... Read Whole Comment +

View in thread

UDF or Viewpixelwiz 15th Jul 2011

0Votes

So you are saying that if I use the a Scalar UDF ona variable or parameter in a whereclause, it will not use an index? For example: Select product_id, product_name fromproducts where... Read Whole Comment +

View in thread

Scalar UDF in Where Clauseunclebiguns@... 13th Sep 2007

0Votes

Thanks for the article on UDFs. I also like to use UDFs when designing queries. It

RE: Understand when to use user-defined functions inSQL Serveralaniane@... 7th Sep 2007

0Votes

http://www.techrepublic.com/forum/discussions/102-236103http://www.techrepublic.com/newslettershttp://www.techrepublic.com/newslettershttp://techrepublic.com.com/5213-6257-0.html?id=4492495&redirectTo=%2f1320-22-20.htmlmailto:[email protected]://nl.com.com/MiniFormHandler?brand=techrepublic&list_id=e046http://www.techrepublic.com/blog/datacenter/talking-intelligently-about-virtualization/166http://www.techrepublic.com/blog/datacenter/talking-intelligently-about-virtualization/166http://www.techrepublic.com/blog/datacenter/talking-intelligently-about-virtualization/166http://www.techrepublic.com/blog/datacenter/defragment-your-windows-server-2003-hard-drive-on-a-set-schedule/169http://www.techrepublic.com/blog/datacenter/defragment-your-windows-server-2003-hard-drive-on-a-set-schedule/169http://www.techrepublic.com/blog/datacenter/defragment-your-windows-server-2003-hard-drive-on-a-set-schedule/169http://www.techrepublic.com/blog/datacenter/defragment-your-windows-server-2003-hard-drive-on-a-set-schedule/169http://www.techrepublic.com/forum/discussions/102-236103/rsshttp://www.techrepublic.com/alerts/add?url=http%3A%2F%2Fwww.techrepublic.com%2Fforum%2Fdiscussions%2F102-236103%2Frss&title=Discussion+on+Understand+when+to+use+user-defined+functions+in+SQL+Server+on+TechRepublic&source=http%3A%2F%2Fwww.techrepublic.com%2Fblog%2Fdatacenter%2Funderstand-when-to-use-user-defined-functions-in-sql-server%2F171&frequency=weekly&rss_type=42http://www.techrepublic.com/forum/discussions/102-236103-3472057http://www.techrepublic.com/members/profile/6860153http://www.techrepublic.com/util/votehttp://www.techrepublic.com/util/votehttp://www.techrepublic.com/forum/discussions/102-236103-2319001http://www.techrepublic.com/members/profile/3527785http://www.techrepublic.com/util/votehttp://www.techrepublic.com/util/votehttp://www.techrepublic.com/members/profile/4704529http://www.techrepublic.com/util/votehttp://www.techrepublic.com/util/vote



Join Login

See all comments

Join the TechRepublic Community and join the conversation! Signing-up isfree and quick, Do it now, we want to hear your opinion.

makes it easier to break complex queries into smaller chunks for debuggingpurposes. Later on, I will replace the... Read Whole Comment +

View in thread

http://www.techrepublic.com/forum/discussions/102-236103http://www.techrepublic.com/members/join?regSrc=disc-starthttp://www.techrepublic.com/members/join?regSrc=disc-starthttp://www.techrepublic.com/members/login?regSrc=disc-starthttp://www.techrepublic.com/members/login?regSrc=disc-starthttp://www.techrepublic.com/forum/discussions/102-236103http://www.techrepublic.com/forum/discussions/102-236103-2316162

techrepublic.comUnderstand when to use user-defined functions in SQL Server | TechRepublic

lvbnMtaW4tc3FsLXNlcnZlci8xNzEA: form1: q: button3:

understand when to use user defined functions in sql server tech-republic

Documents