Query optimizer question

Hi,
we have a very strange behavior of SQL Server that we try to understand.
We have the following Select statement:
SELECT DISTINCT TOP 100000 Customer.id
FROM Customer LEFT JOIN CustomerAddress CA ON
Customer.idDefaultAddress=CA.id
WHERE 1=1
and Customer.id IN (
select idCustomer
from CustomerAttribute left JOIN CustomerAttributeDescription ON
CustomerAttribute.id = CustomerAttributeDescription.idCustomerAttribute
where idattribute = 13844 AND ValueAttribute = 6
)
When we run this select we get the desired results from the database.
However if we run only the Select that is in the Where clause, that is:
select idCustomer
from CustomerAttribute left JOIN CustomerAttributeDescription ON
CustomerAttribute.id = CustomerAttributeDescription.idCustomerAttribute
where idattribute = 13844 AND ValueAttribute = 6
we get the following error:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'DCDB' to a column of data
type int.
which is somehow normal as the column ValueAttribute is a varchar column
.
What I do not understand is why in the first situation it works.
Obviously looking at the execution plan you can see that it is made
different in the 2 situation but it's SQL that decides upon what
execution plan it should chose.
Does anyone have any explanation for this behavior or could you point me
to some documentation regarding the way the Query Optimizer works?
Thanks,
Florian
Here is the tables structure: CREATE TABLE [dbo].[Customer] (
[id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[idDefaultShippingAddress] [int] NULL ,
[idDefaultAddress] [int] NULL ,
[idPrincipalContact] [uniqueidentifier] NULL ,
[idSalesPerson] [uniqueidentifier] NULL ,
[idCustomerParent] [uniqueidentifier] NULL ,
[Code] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Phone] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Fax] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[WebSite] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL
,
[Email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NUL
L ,
[InternalNotes] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_A
S
NOT NULL ,
[DotNetMessenger] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_
AS
NOT NULL ,
[EntCreationDate] [datetime] NULL ,
[NumberEmployee] [int] NOT NULL ,
[Status] [udtStatus] NOT NULL ,
[BankName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[BankCity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL
,
[BankProvinceState] [int] NULL ,
[BankAccountNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI
_AS
NOT NULL ,
[BankRoutingNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI
_AS
NOT NULL ,
[CreditCardType] [int] NULL ,
[CreditCardNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
AS
NOT NULL ,
[CreditCardExpMonth] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CreditCardExpYear] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI
_AS
NULL ,
[idDefaultLanguage] [int] NOT NULL ,
[tsUpdate] [timestamp] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CustomerAddress] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[idCustomer] [uniqueidentifier] NOT NULL ,
[idTaxCode] [int] NOT NULL ,
[idCurrency] [int] NOT NULL ,
[idTimeZone] [int] NOT NULL ,
[Address1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Address2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ProvinceState] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
NULL ,
[Country] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL
,
[ZipPostalCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
NULL ,
[InternalNotes] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_A
S
NOT NULL ,
[Description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT
NULL ,
[ShippingInstruction] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Status] [udtStatus] NOT NULL ,
[ISO3166GEO] [int] NULL ,
[tsUpdate] [timestamp] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CustomerAttribute] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[idCustomer] [uniqueidentifier] NOT NULL ,
[idAttribute] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CustomerAttributeDescription] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[idCustomerAttribute] [int] NOT NULL ,
[idCulture] [int] NULL ,
[ValueAttribute] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_
AS
NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Customer] WITH NOCHECK ADD
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerAddress] WITH NOCHECK ADD
CONSTRAINT [PK_AddressCustomer] PRIMARY KEY CLUSTERED
(
[id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerAttribute] WITH NOCHECK ADD
CONSTRAINT [PK_CustomerAttribute] PRIMARY KEY CLUSTERED
(
[id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerAttributeDescription] WITH NOCHECK ADD
CONSTRAINT [PK_CustomerAttributeDescription] PRIMARY KEY CLUSTERED
(
[id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Customer] WITH NOCHECK ADD
CONSTRAINT [DF_Customer_id] DEFAULT (newid()) FOR [id],
CONSTRAINT [DF_Customer_Name] DEFAULT ('') FOR [Name],
CONSTRAINT [DF_Customer_Phone] DEFAULT ('') FOR [Phone],
CONSTRAINT [DF_Customer_Fax] DEFAULT ('') FOR [Fax],
CONSTRAINT [DF_Customer_WebSite] DEFAULT ('') FOR [WebSite],
CONSTRAINT [DF_Customer_Email] DEFAULT ('') FOR [Email],
CONSTRAINT [DF_Customer_Notes] DEFAULT ('') FOR [InternalNotes],
CONSTRAINT [DF_Customer_DotNetMessenger] DEFAULT ('') FOR
[DotNetMessenger],
CONSTRAINT [DF_Customer_NumberEmployee] DEFAULT (0) FOR
[NumberEmployee],
CONSTRAINT [DF_Customer_BankName] DEFAULT ('') FOR [BankName],
CONSTRAINT [DF_Customer_BankCity] DEFAULT ('') FOR [BankCity],
CONSTRAINT [DF_Customer_BankAccountNumber] DEFAULT ('') FOR
[BankAccountNumber],
CONSTRAINT [DF_Customer_BankRoutingNumber] DEFAULT ('') FOR
[BankRoutingNumber],
CONSTRAINT [DF_Customer_CreditCardNumber] DEFAULT ('') FOR
[CreditCardNumber],
CONSTRAINT [DF_Customer_idDefaultLanguage] DEFAULT (1) FOR
[idDefaultLanguage]
GO
CREATE INDEX & #91;IX_CustomeridDefaultShippingAddress]
ON
[dbo].[Customer]([idDefaultShippingAddress]) WITH FILLFACTOR =
90 ON
[PRIMARY]
GO
CREATE INDEX [IX_CustomerDefaultAddress] ON
[dbo].[Customer]([idDefaultAddress]) WITH FILLFACTOR = 90 ON &#
91;PRIMARY]
GO
CREATE INDEX [IX_CustomeridPrincipalContact] ON
[dbo].[Customer]([idPrincipalContact]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE UNIQUE INDEX [IX_Customer_Code] ON [dbo].[Customer](
1;Code])
WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_Customer] ON [dbo].[Customer]([id]) WITH
FILLFACTOR
= 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerAddress] WITH NOCHECK ADD
CONSTRAINT [DF_CustomerAddress_Address1] DEFAULT ('') FOR [Address1]
,
CONSTRAINT [DF_AddressCustomer_Address2] DEFAULT ('') FOR [Address2]
,
CONSTRAINT [DF_CustomerAddress_City] DEFAULT ('') FOR [City],
CONSTRAINT [DF_CustomerAddress_ProvinceState] DEFAULT ('') FOR
[ProvinceState],
CONSTRAINT [DF_CustomerAddress_Country] DEFAULT ('') FOR [Country],
CONSTRAINT [DF_CustomerAddress_ZipPostalCode] DEFAULT ('') FOR
[ZipPostalCode],
CONSTRAINT [DF_CustomerAddress_InternalNotes] DEFAULT ('') FOR
[InternalNotes],
CONSTRAINT [DF_CustomerAddress_Description] DEFAULT ('') FOR
[Description],
CONSTRAINT & #91;DF_CustomerAddress_ShippingInstructi
on] DEFAULT ('') FOR
[ShippingInstruction]
GO
CREATE INDEX [IX_CustomerAddressidCustomer] ON
[dbo].[CustomerAddress]([idCustomer]) WITH FILLFACTOR = 90 ON &
#91;PRIMARY]
GO
CREATE INDEX [IX_CustomerAddressTaxCode] ON
[dbo].[CustomerAddress]([idTaxCode]) WITH FILLFACTOR = 90 ON &#
91;PRIMARY]
GO
CREATE INDEX [I_CustomerAddress] ON
[dbo].[CustomerAddress]([Address1], [Address2], [City])
WITH FILLFACTOR
= 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerAttribute] WITH NOCHECK ADD
CONSTRAINT [U_CustomerAttribute] UNIQUE NONCLUSTERED
(
[idCustomer],
[idAttribute]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerAttributeDescription] WITH NOCHECK ADD
CONSTRAINT & #91;DF_CustomerAttributeDescription_Valu
eAttribute] DEFAULT
('') FOR [ValueAttribute]
GO
setuser
GO
EXEC sp_bindrule N'[dbo].[udtUserStatusRule]', N'[Customer].[
;Status]'
GO
EXEC sp_bindefault N'[dbo].[udtStatusDefault]', N'[Customer].
1;Status]'
GO
setuser
GO
setuser
GO
EXEC sp_bindrule N'[dbo].[udtUserStatusRule]',
N'[CustomerAddress].[Status]'
GO
EXEC sp_bindefault N'[dbo].[udtStatusDefault]',
N'[CustomerAddress].[Status]'
GO
setuser
GO
ALTER TABLE [dbo].[Customer] ADD
CONSTRAINT [FK_Customer_Culture] FOREIGN KEY
(
[idDefaultLanguage]
) REFERENCES [dbo].[Culture] (
[id]
),
CONSTRAINT [FK_Customer_CustomerAddress2] FOREIGN KEY
(
[idDefaultShippingAddress]
) REFERENCES [dbo].[CustomerAddress] (
[id]
),
CONSTRAINT [FK_Customer_CustomerAddress3] FOREIGN KEY
(
[idDefaultAddress]
) REFERENCES [dbo].[CustomerAddress] (
[id]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CustomerAttribute] ADD
CONSTRAINT [FK_CustomerAttribute_Attribute] FOREIGN KEY
(
[idAttribute]
) REFERENCES [dbo].[Attribute] (
[id]
),
CONSTRAINT [FK_CustomerAttribute_Customer] FOREIGN KEY
(
[idCustomer]
) REFERENCES [dbo].[Customer] (
[id]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CustomerAttributeDescription] ADD
CONSTRAINT & #91;FK_CustomerAttributeDescription_Cult
ure] FOREIGN KEY
(
[idCulture]
) REFERENCES [dbo].[Culture] (
[id]
),
CONSTRAINT & #91;FK_CustomerAttributeDescription_Cust
omerAttribute] FOREIGN
KEY
(
[idCustomerAttribute]
) REFERENCES [dbo].[CustomerAttribute] (
[id]
) ON DELETE CASCADE
GOOn Mon, 05 Apr 2004 21:46:46 -0500, Florian Ion wrote:

>We have the following Select statement:
>SELECT DISTINCT TOP 100000 Customer.id
>FROM Customer LEFT JOIN CustomerAddress CA ON
>Customer.idDefaultAddress=CA.id
>WHERE 1=1
> and Customer.id IN (
> select idCustomer
> from CustomerAttribute left JOIN CustomerAttributeDescription ON
>CustomerAttribute.id = CustomerAttributeDescription.idCustomerAttribute
> where idattribute = 13844 AND ValueAttribute = 6
> )
Why are you using left join in the subquery? The test for
ValueAttribute = '6' (what you should use for varchar data - or use
CAST (ValueAttribute AS int) if you're not sure the '6' is formatted
consistently) will discard any rows that the left join included, so
you might just as well change it to an inner join. Good chance that
this will improve perfoprmance on this query!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)