2

Even though I'm a beginner DB programmer I already noticed there are many large joins I find myself repeating over and over. I was thinking of just creating a table-valued function for commonly used joins, and allow a parameter to be passed into this function which will filter the results.

Eg. Instead of writing

SELECT * 
FROM T1 join T2 on T1.A = T2.A 
        join T3 on T2.B = T3.B
        join T4 on T3.C = T4.D
WHERE T1.D = '15' AND T2.D = '20' AND T3.C = '12'

I would write

SELECT * 
FROM dbo.SOME_FUNCTION(' T1.D = '15' AND T2.D = '20' AND T3.C = '12' ')

My question is simply this. Is this something I should be attempting to do? Or am I ridiculously lazy for wanting to do this?

Have been trying to research/write this function all day, and have had many/many problems doing so. I know eventually, I can/will make it work, just wondering if it is worth my time.

Dharman
  • 30,962
  • 25
  • 85
  • 135
user1329267
  • 19
  • 3
  • It's not a dumb question, you're trying to be efficient. The DRY principle has significant performance implications when applied to relational database programming, though, so be careful when applying it in that context (eg., don't use nested views (ie., views that refer to other views), multi-statement table valued functions don't perform as well as inline ones (see (http://stackoverflow.com/questions/2554333/multi-statement-table-valued-function-vs-inline-table-valued-function), don't run select queries in scalar functions, etc.). You have to balance performance with elegance – ssis_ssiSucks Apr 27 '12 at 21:48

2 Answers2

2

Table-valued functions are a nice way to avoid repetitive SELECT statements, but it isn't the only way to do so. I would put the statements in a view, because it can be indexed. You still can have your table-valued function, which selects records from the view using the parameters.

Imagine that you have the following 2 types of retrievals from your view:

1, SELECT ... WHERE Column1 = @p1

2, SELECT ... WHERE Column2 = @p2 AND Column3 = @p3

In this case you should add the matching two indices to the view

  • Column1

  • Column2 + Column3

Peter Aron Zentai
  • 11,482
  • 5
  • 41
  • 71
  • Ya, the problem is there WHERE/SELECT columns I use are usually different. Also the tables are massive(million + rows each), so doing a straight view would be intense. – user1329267 Apr 27 '12 at 20:46
  • @user1329267: Views couldn't care less how many rows there are... what aspect of creating a view would be intense? – Cᴏʀʏ Apr 27 '12 at 21:03
  • @user1329267: I would make a try:) All the rows included in your view will be indexed according to your index-preferences. – Peter Aron Zentai Apr 27 '12 at 21:43
0

I think maybe you're idea is sound but the approach is not quite ideal. There are probably two decent ways to go about this. The first would be to create a VIEW:

CREATE VIEW dbo.SOME_VIEW AS
(
    SELECT 
        -- [list out the columns here], do not use *
    FROM 
        T1 
    JOIN 
        T2 on T1.A = T2.A 
    JOIN 
        T3 on T2.B = T3.B
    JOIN 
        T4 on T3.C = T4.D
)

And with that, your query is:

SELECT * FROM SOME_VIEW
WHERE ...

The function approach could work, but I would change the function to accept three parameters , not the single string approach you showed in your example, so that you're doing this instead:

SELECT * FROM dbo.SOME_FUNCTION('15', '20', '12')

In the same vein, depending on how you're accessing the data, a stored procedure may be more appropriate:

EXEC dbo.spSomeStoredProcedure @T1D = '15', @T2D = '20', @T3C = '12'

EDIT: Both the table-valued function and the stored procedure should make use of the above view.

Cᴏʀʏ
  • 105,112
  • 20
  • 162
  • 194
  • Ya, the problem is there WHERE/SELECT columns I use are usually different. Also there ends up being about 25 columns once the join is done, thats why I'm looking at passing a string through(many problems occur returning and EXEC QUERY in an a function tho...) – user1329267 Apr 27 '12 at 20:49