FUNCTION vs.WHERE condition - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

FUNCTION vs.WHERE condition

Author

Topic

XK3V1N
Starting Member

3 Posts

Posted - 2014-02-05 : 15:09:06

I am starting to look into T-SQL functions.

What are some of the advantages of using functions instead of where conditions? See example below.

---------------------------------------------------------------------
-- 1 . Function
CREATE FUNCTION Production.GetTopProducts (@productid AS INT, @supplierid AS INT) RETURNS TABLE
AS
RETURN
SELECT
[productid]
,[productname]
,[supplierid]
,[categoryid]
,[unitprice]
,[discontinued]
FROM [TSQL2012].[Production].[Products]
WHERE [productid] = @productid
AND [supplierid] = @supplierid;

SELECT * FROM Production.GetTopProducts(1,1) AS P;

---------------------------------------------------------------------
2. -- WHERE
SELECT
[productid]
,[productname]
,[supplierid]
,[categoryid]
,[unitprice]
,[discontinued]
FROM [TSQL2012].[Production].[Products]
WHERE productid = 1
AND supplierid = 1;

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-02-05 : 15:19:24

About the only thing that you gain are encapsulation and abstraction. In general, they hinder performance. So, test, test and more testing..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-02-05 : 15:29:24

Performance degradation can be significant. We avoid functions wherever possible.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources