Two queries - different output

Question

Hi Everyone !

The following queries force me to deep into the things, what I have missed in T-SQL optimizer engine.

Why the following two queries return me different row count ?

-------------------------------------------------------------------------------

1) select distinct o.object_id, o.name as ObjectName, o.type, p.name as ParamName
    from sys.objects o
    inner join sys.parameters p on o.object_id = p.object_id
    where (o.type = 'P' or o.type = 'V' or o.type = 'FN' or o.type = 'TF')
    and o.name = 'GetMaterialDayTotals'
    order by o.type desc, o.name
------------------------------------------------------------------------------------------
2) select distinct o.object_id, o.name as ObjectName, o.type
    from sys.objects o
    inner join sys.parameters p on o.object_id = p.object_id
    where (o.type = 'P' or o.type = 'V' or o.type = 'FN' or o.type = 'TF')
    and o.name = 'GetMaterialDayTotals'
    order by o.type desc, o.name
-----------------------------------------------------------------------------------

1) 940686549 GetMaterialDayTotals P @DateFrom
    940686549 GetMaterialDayTotals P @DateTo

2) 940686549 GetMaterialDayTotals P

================================================================

Any way I have inner join in both cases, the difference only in ParamName selection.

Thanks in Advance.

---

KH

Answer 1

@Karen:  Hi Karen, please repost this question in a NEW post, then come back and edit this entry and add the hyperlink to your newly posted question.  That lets this post remain focused on its original topic, and lets you have your very own post that is focused just on your topic.

Answer 2

In your first query you have an extra field which will be unique for the same types, so your first query should return more rows than the second.

Here is a quick test I ran on AdventureWorks database:

select distinct o.object_id, o.name as ObjectName, o.type--, p.name as ParamName
    from sys.objects o 
    inner join sys.parameters p on o.object_id = p.object_id
    where (o.type = 'P' or o.type = 'V' or o.type = 'FN' or o.type = 'TF')
    and o.name = 'uspGetBillOfMaterials'
    order by o.type desc, o.name

First query returned 2 rows (2 different parameters) and the second only 1.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 3

1) select distinct o.object_id, o.name as ObjectName, o.type, p.name as ParamName

------------------------------------------------------------------------------------------
2) select distinct o.object_id, o.name as ObjectName, o.type
-----------------------------------------------------------------------------------

For the first query, DISTINCT will be applied to the combination of the values returned by the columns - o.object_id, o.name as ObjectName, o.type,p.name. Where as, the second one will consider only the following columns to get DISTINCT values o.object_id, o.name as ObjectName, o.type.

Hence, you are getting different output becasue - 

 940686549 GetMaterialDayTotals P @DateFrom
 940686549 GetMaterialDayTotals P @DateTo

are different combination of values.

---

-Vinay Pugalia
If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
Web : Inkey Solutions
Blog : My Blog
Email : Vinay Pugalia

Answer 4

The following queries force me to deep into the things, what I have missed in T-SQL optimizer engine.

Why the following two queries return me different row count ?

This isn't related to optimization but rather the way the queries are formulated.  The DISTINCT in the first query is superflous because the each row is uniquely identified by the selected columns; you'll get the same results with or without DISTINCT.  The effect of the query is to return all of the objects with at least one parameter along with the parameter names.

The DISTINCT in the second query is needed to eliminate duplicate rows for objects that have multiple parameters.  The row counts are different because objects have multiple parameters.  The effect of the second query is to return all of the objects with at least one parameter without parameter names.  The only time both queries will return the same rowcount is when objects have a single parameter. 

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 5

Any way I have inner join in both cases, the difference only in ParamName selection.

---

KH

And it's the difference.

---

Serg