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 NULL
,
[Email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[InternalNotes] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS
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 NULL
,
[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 NULL
,
[ZipPostalCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[InternalNotes] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
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 [IX_CustomeridDefaultShippingAddress] ON
[dbo].[Customer]([idDefaultShippingAddress]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO

CREATE INDEX [IX_CustomerDefaultAddress] ON
[dbo].[Customer]([idDefaultAddress]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_CustomeridPrincipalContact] ON
[dbo].[Customer]([idPrincipalContact]) WITH FILLFACTOR = 90 ON

Florian,

This is actually an issue just in the way SQL Server is trying to =
evaluate your WHERE clause expression. This behavior only happens when =
you try to evaluate a varchar field but use a number with no quotes. =
The query optimizer tried to make an implicit conversion of the =
ValueAttribute field in the original IN clause query to numeric rather =
then leaving it at varchar. I believe its easier for SQL Server to =
search for a number then it is to find a string thus the different query =
plans and different results. You can avoid this situation entirely by =
always ensuring that WHERE clause comparisons with char and varchar =
fields have quotes ' ' around them. This will force SQL Server to =
function without making the implicit conversion and is also a better =
coding practice allowing you to avoid this situation. Also, using the =
IN clause with subqueries can lead to table scans and other poor =
behavior. I would suggest using the EXISTS clause instead its a better =
practice and will on average perform better then an IN clause.

SELECT DISTINCT TOP 100000 Customer.id
FROM Customer =20
LEFT JOIN CustomerAddress CA ON Customer.idDefaultAddress =3D CA.id
WHERE EXISTS
(SELECT 1
FROM CustomerAttribute
LEFT OUTER JOIN CustomerAttributeDescription ON CustomerAttribute.id =
=3D CustomerAttributeDescription.idCustomerAttribute
WHERE idattribute =3D 13844=20
AND ValueAttribute =3D '6'
AND Customer.ID =3D CustomerAddress.idCustomer)

Now some friendly advice on your data model, it needs some real help.

1. Use of UniqueIdentifiers is a VERY BAD THING. This is covering up =
for poor data gathering. There is something that makes everyone of your =
customers unique and is not a UniqueIndentifier in SQL Server. This is =
HIGHLY proprietary and is a BAD practice. Go back and find another way =
to do this. This will cause you MAJOR issues down the road and its all =
over your tables (customers, salespeople, etc). Go back and remove this =
from your datamodel.

2. Your using a VARCHAR (30) to hold your credit card numbers. All the =
major bureaus require you to NOT store the credit card information in =
plain text but use some sort of algorithim to do it. Using the blowfish =
algorithm will take up 81 characters to hold the 16 digit credit card =
number therefore this is compeletely inadequate and if you plan on =
having people type in credit card numbers into your application you need =
to ensure that have a secure way of doing this.

3. Store the numbers for month and year of the your credit card =
expiration as INT and not as a VARCHAR. Also, put a constraint on both!

4. Get some consistency with your column conventions. In one place your =
holding the ProvinceState as an INT and in another your using a VARCHAR =
(50)!! You shouldn't need more then five at most and that's if your =
worldwide. If you are only doing business in North America two =
suffices.

5. I am sure one of your customers will be using the same bank as =
another. Split this info into its own table. This is a violation of =
3NF.

6. Do not use Timestamp, set your field to DATETIME and use a trigger to =
update the last time a field was modified.

7. An address has a type (home, work, billing, attorney,etc...), define =
the types of a addresses in the system in a table and use the customer =
field together with this to be your primary key. There is NO reason for =
an identity field to be in the CustomerAddress table.

8. Why are expanding these notes fields out to these sizes? Is there =
any reason you need this specifcally? You should be getting errors like =
this all the time:
Warning: The table 'CustomerAddress' has been created but its maximum =
row size (9109) exceeds the maximum number of bytes per row (8060). =
INSERT or UPDATE of a row in this table will fail if the resulting row =
length exceeds 8060 bytes.

9. Binding defaults and rules is a backwards compatible operation. Use =
constraints, triggers, and column defaults instead.

10. ID is a reserved word. Do not use it for the names of fields.

11. Try to avoid IDENTITY fields if at all possible.

Hopefully all this helps and gives you and your team something to think =
about. Just hope that Joe doesn't come in and put you through the =
ringer later on.

HTH,
Grant

[quoted text, click to view]