Different Results With View Versus UDF

Hot Topic

Loading a column by name (via Variable, etc.)
Errors in the high-level relational engine
Error importing data from oracle database to an SQL database
Compatibility with sql 2000
"Package failed validation from the ExecutePackage task" error
The subscription to publication is Invalid
New to SQL server. Have 2005 having issues, cant load up sql managerment
Couple questions about partitioning and performance gain
Embedded code
Migrate Access 2002 to SQL Express 2005

SQL Server

SQL Server security connections not working.
limited number of connections to SQLMobile?
Data Flow execution hangs with no error
Implementing message queue in SQL Server 2000
Fail Execute Process Task based on batch file ERRORLEVEL?
SP3 & SP4 Not upgrading
cannot install integration services
Connecting in the Analysis Services using DSO and IIS
Broker:Corrupted message
Problem with concurrent insert and update commands

Different Results With View Versus UDF

I have two views, for various reasons I decided to wrap a Select * From View with two UDFs. When run on their own, the UDFs return the exact same resultset as their respective view (did a compare and stuff to make sure). However, when I join them together, what was once 130,000 records (when joining the views) skyrockets into millions of records, and the resulting resultset is filled with duplicates. What is going on

Answer this question

Different Results With View Versus UDF

  • Hannweber

    The exact same join with the exact same conditions using the views (which return an identical result set) returns the proper result.

  • Marius Mihalec

    Firstly I would use a stored procedure to return result sets, not UDF's. Secondly, it looks like you are getting a cartesian join, where every row in one table joins to many or every row in the other table. This is caused by incorrectly joining the two table (or views). This is illustrated by the duplicate rows you are getting. Check your joins, and make sure you join primary key to foreign key. To test the query, change the select list to a select count(*), and then run the query with just the first join condition. Then add each condition one by one. At some point the rowcount will explode. However, if the first join produces a massive row count, experiment with adding additional joins until you get the correct amount of records returned.

    HTH

    For more SQL tips, check out my blog:

  • Aaron Carlson

    that's weird, you could have been executing a cross join

  • mark6960

    You will have to post a repro script to demonstrate the problem. Otherwise it will be hard to guess what might be wrong. Your SELECT statement could be incorrect, the UDF could be wrong and so on.

  • Different Results With View Versus UDF
Answer this question