SQL Server changes my SQL - why?

[Edward]

Here's my carefully hand-crafted SQL:

SELECT *
FROM tblMailbookQueryISPRO
WHERE ((fldClosed = -1)
AND (fldCSNo LIKE N'%PSH%')
AND (fldReceivedDate >= CONVERT(DATETIME,'2003-04-01 00:00:00', 102))
AND (fldReceivedDate <= CONVERT(DATETIME,'2003-04-30 23:59:59', 102))
AND (fldAction = -1)
AND ((fldActionBy1ID = '{ca8e0b3c-dd6e-40a6-8282-6a3ed5ce55e3}') OR
(fldActionBy2ID = '{ca8e0b3c-dd6e-40a6-8282-6a3ed5ce55e3}')))

I paste it into the SQL pane of my Data in Table window and here's the
pig's breakfast that SQL Server makes when I click "Verify SQL"

SELECT *
FROM tblMailbookQueryISPRO
WHERE (fldClosed = - 1)
AND (fldCSNo LIKE N'%PSH%')
AND (fldReceivedDate >= CONVERT(DATETIME, '2003-04-01 00:00:00', 102))
AND (fldReceivedDate <= CONVERT(DATETIME, '2003-04-30 23:59:59', 102))
AND (fldAction = - 1)
AND (fldActionBy1ID = '{ca8e0b3c-dd6e-40a6-8282-6a3ed5ce55e3}')
OR (fldClosed = - 1) AND (fldCSNo LIKE N'%PSH%')
AND (fldReceivedDate >= CONVERT(DATETIME, '2003-04-01 00:00:00', 102))
AND (fldReceivedDate <= CONVERT(DATETIME, '2003-04-30 23:59:59', 102))
AND (fldAction = - 1)
AND (fldActionBy2ID = '{ca8e0b3c-dd6e-40a6-8282-6a3ed5ce55e3}')

Now, what's all that about?

TIA

Edward

TABLE DEFINITION:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblMailbookQueryISPRO]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblMailbookQueryISPRO]
GO

CREATE TABLE [dbo].[tblMailbookQueryISPRO] (
[fldCSID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[fldReceivedDate] [smalldatetime] NULL ,
[fldAction] [smallint] NULL ,
[fldUserID] [uniqueidentifier] NULL ,
[fldAddressee] [nvarchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[fldSubject] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[fldActionBy1ID] [uniqueidentifier] NULL ,
[fldActionBy2ID] [uniqueidentifier] NULL ,
[fldRemarks] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[fldCustomerReference] [nvarchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[fldCSNo] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldEnquiryID] [uniqueidentifier] NULL ,
[fldDocID] [int] NULL ,
[fldAttachedfile] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[fldFileAttached] [smallint] NULL ,
[fldRecTime] [smalldatetime] NULL ,
[fldCompanyID] [uniqueidentifier] NULL ,
[fldContactID] [uniqueidentifier] NULL ,
[fldSortNo] [int] NULL ,
[fldUser] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldEnq_Type] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[fldTempContactID] [int] NULL ,
[fldTempCompanyID] [int] NULL ,
[fldTempOldCSID] [int] NULL ,
[fldContact] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[fldCustomer] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[fldAircraftID] [uniqueidentifier] NULL ,
[fldSystemID] [uniqueidentifier] NULL ,
[fldPartNo] [nvarchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[fldCategoryID] [uniqueidentifier] NULL ,
[fldDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldClosedDate] [smalldatetime] NULL ,
[fldRespSiteID] [uniqueidentifier] NULL ,
[fldReceivedTime] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[fldEndTime] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[fldClosed] [smallint] NULL ,
[fldEnquiryDate] [smalldatetime] NULL ,
[fldISPRO] [smallint] NULL ,
[fldAircraft] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[fldSystem] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[fldComplaint_Category] [nvarchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[fldDocumentID] [uniqueidentifier] NULL ,
[fldComplaint] [smallint] NULL ,
[fldSerialNumber] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[fldISPRONo] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[fldDate] [smalldatetime] NULL ,
[fldRevision] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[fldRevisionDate] [smalldatetime] NULL ,
[fldTitle] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[fldActionRequired] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[fldStatus] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[fldOriginator] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[fldOriginatorID] [uniqueidentifier] NULL ,
[fldSRP_TFU] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[fldSRP_TFUDate] [smalldatetime] NULL ,
[fldCostToFix] [money] NULL ,
[fldCostNotToFix] [money] NULL ,
[fldActionTaken] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldOldCSID] [uniqueidentifier] NULL ,
[fldKnowledgeBase] [smallint] NULL ,
[fldKBSubject] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldKBDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[fldKBFileAttached] [smallint] NULL ,
[fldKBAttachedFile] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[fldKBSolution] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldKBPublished] [smalldatetime] NULL ,
[fldKBKeyPhrase] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[fldQueryVisibleToCustomer] [smallint] NULL ,
[fldLastUpdateActionBy1] [smalldatetime] NULL ,
[fldLastUpdateActionBy2] [smalldatetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

[hansje]

I suddenly realized you were not working in QA, and I don't understand
what you've been doing. If you tried creating a view, I pasted the query
in the SQL pane, and nothing was changed.
Were are you trying to do this?

Tnx,

Hans Brouwer

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[hansje]

This is strange indeed.
I created the table in PUBS on a server with sp3 installed. Did NOT fill
the table with data. Pasted your query into QA, clicked verify query
and...
nothing was changed.

This happens everytime you do this?
Have you tried this on another server? Another database?

Tnx,

Hans Brouwer

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Edward]

hansje <ha*****@anonymous.com> wrote in message news:<3f*********************@news.frii.net>...

I suddenly realized you were not working in QA, and I don't understand
what you've been doing. If you tried creating a view, I pasted the query
in the SQL pane, and nothing was changed.
Were are you trying to do this?

Tnx,

Hans Brouwer

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

No, not QA.

Open EM, select the server node, get down to the database, select the
table tblMailbookQueryISPRO, right-click and go to Open Table ->,
Query.

HTH

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk

[Simon Hayes]

te********@hotmail.com (Edward) wrote in message news:<25**************************@posting.google. com>...

Here's my carefully hand-crafted SQL:

SELECT *
FROM tblMailbookQueryISPRO
WHERE ((fldClosed = -1)
AND (fldCSNo LIKE N'%PSH%')
AND (fldReceivedDate >= CONVERT(DATETIME,'2003-04-01 00:00:00', 102))
AND (fldReceivedDate <= CONVERT(DATETIME,'2003-04-30 23:59:59', 102))
AND (fldAction = -1)
AND ((fldActionBy1ID = '{ca8e0b3c-dd6e-40a6-8282-6a3ed5ce55e3}') OR
(fldActionBy2ID = '{ca8e0b3c-dd6e-40a6-8282-6a3ed5ce55e3}')))

I paste it into the SQL pane of my Data in Table window and here's the
pig's breakfast that SQL Server makes when I click "Verify SQL"

<snip>

Enterprise Manager is not a good tool for SQL development - if you're
doing something by hand, you should really be using Query Analyzer,
which is a lot more powerful and reliable.

I suspect that EM's parser is confused by your parentheses, most of
which appear to be superfluous (although I may be wrong - I looked
very quickly). Your query looks like it could probably be more clearly
written like this:

SELECT *
FROM
dbo.tblMailbookQueryISPRO
WHERE
fldClosed = -1
AND fldCSNo LIKE N'%PSH%'
AND fldReceivedDate >= '20030401'
AND fldReceivedDate < '20030501'
AND fldAction = -1
AND (fldActionBy1ID = '{ca8e0b3c-dd6e-40a6-8282-6a3ed5ce55e3}' OR
fldActionBy2ID = '{ca8e0b3c-dd6e-40a6-8282-6a3ed5ce55e3}')

Notice the date format as well - YYYYMMDD will work consistently with
all client/server settings which affect dates. Since the column is
already smalldatetime, the values will be converted implicitly for
you. You might be better off using datetime instead - 2079 isn't so
far away if you have to deal with transactions involving long-term
loans or leases.

Simon

[Daniel Morgan]

Simon Hayes wrote:

te********@hotmail.com (Edward) wrote in message news:<25**************************@posting.google. com>...

Here's my carefully hand-crafted SQL:

SELECT *
FROM tblMailbookQueryISPRO
WHERE ((fldClosed = -1)
AND (fldCSNo LIKE N'%PSH%')
AND (fldReceivedDate >= CONVERT(DATETIME,'2003-04-01 00:00:00', 102))
AND (fldReceivedDate <= CONVERT(DATETIME,'2003-04-30 23:59:59', 102))
AND (fldAction = -1)
AND ((fldActionBy1ID = '{ca8e0b3c-dd6e-40a6-8282-6a3ed5ce55e3}') OR
(fldActionBy2ID = '{ca8e0b3c-dd6e-40a6-8282-6a3ed5ce55e3}')))

I paste it into the SQL pane of my Data in Table window and here's the
pig's breakfast that SQL Server makes when I click "Verify SQL"

<snip>

Enterprise Manager is not a good tool for SQL development - if you're
doing something by hand, you should really be using Query Analyzer,
which is a lot more powerful and reliable.

I suspect that EM's parser is confused by your parentheses, most of
which appear to be superfluous (although I may be wrong - I looked
very quickly). Your query looks like it could probably be more clearly
written like this:

SELECT *
FROM
dbo.tblMailbookQueryISPRO
WHERE
fldClosed = -1
AND fldCSNo LIKE N'%PSH%'
AND fldReceivedDate >= '20030401'
AND fldReceivedDate < '20030501'
AND fldAction = -1
AND (fldActionBy1ID = '{ca8e0b3c-dd6e-40a6-8282-6a3ed5ce55e3}' OR
fldActionBy2ID = '{ca8e0b3c-dd6e-40a6-8282-6a3ed5ce55e3}')

Notice the date format as well - YYYYMMDD will work consistently with
all client/server settings which affect dates. Since the column is
already smalldatetime, the values will be converted implicitly for
you. You might be better off using datetime instead - 2079 isn't so
far away if you have to deal with transactions involving long-term
loans or leases.

Simon

But so typical of Microsoft. Code something that rewrites what the user
intended without
having the civility to ask. I wonder when my buddies up the street will
learn that it isn't nice
to do things for people without asking permission first.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

[Edward]

Daniel Morgan <da******@x.washington.edu> wrote in message news:<1067612838.13871@yasure>...

Simon Hayes wrote: <snip?

Enterprise Manager is not a good tool for SQL development - if you're
doing something by hand, you should really be using Query Analyzer,
which is a lot more powerful and reliable.
But EM *is* quite handy. You can select tables, make joins by
dragging fields from one table to another, see the relations in a nice
graphical way, and ... gulp ... I come from an Access developer
background and the Query Builder in Access was pretty good (and
clearly the template for EM's)!

I suspect that EM's parser is confused by your parentheses, most of
which appear to be superfluous (although I may be wrong - I looked
very quickly). Your query looks like it could probably be more clearly
written like this:
Ah, trying to start a Holy War, eh? Seriously, I parenthesise things
because that's how I write my code. So that it is unambiguous to
someone (else) looking at it. Sure, if you just have a string of
clauses joined by ANDs it doesn't really matter, but when you factor
in a mixture of ORs and ANDs you'll be glad of the parentheses and,
what's important, so will I when I come to maintain your code.
<snip>
Notice the date format as well - YYYYMMDD will work consistently with
all client/server settings which affect dates.
I work with the format returned from the application - too much hard
work to go in there and change the output.
Since the column is
already smalldatetime, the values will be converted implicitly for
you. You might be better off using datetime instead - 2079 isn't so
far away if you have to deal with transactions involving long-term
loans or leases.

I don't, but thanks for the warning. With a bit of luck I'll be
retired by 2079 and sunning myself on Mars.
But so typical of Microsoft. Code something that rewrites what the user
intended without
having the civility to ask. I wonder when my buddies up the street will
learn that it isn't nice
to do things for people without asking permission first.

This is JUST what bugged me about it. Microsoft didn't even have the
civility to inform me that it thought my code was crap, and that their
way was better. Only trouble is, having looked at quite a deal of
their sample code, I wouldn't bet on theirs being better than mine in
all circumstances. In any case, why not let us go to hell in our own
handbaskets?

But thanks to all who took the trouble to reply. It's just one of
those things, I guess.

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk

[Greg D. Moore \(Strider\)]

But so typical of Microsoft. Code something that rewrites what the user intended without having the civility to ask. I wonder when my buddies up the street will learn that it isn't nice to do things for people without asking permission first.

You do realize almost any fairly complex query you may submit will be most
likely be completely rewritten by the optimizer before being executed?
--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

[Greg D. Moore \(Strider\)]

"Edward" <te********@hotmail.com> wrote in message
news:25**************************@posting.google.c om...

Daniel Morgan <da******@x.washington.edu> wrote in message news:<1067612838.13871@yasure>...

Simon Hayes wrote:

<snip?

Enterprise Manager is not a good tool for SQL development - if you're
doing something by hand, you should really be using Query Analyzer,
which is a lot more powerful and reliable.
But EM *is* quite handy. You can select tables, make joins by
dragging fields from one table to another, see the relations in a nice
graphical way, and ... gulp ... I come from an Access developer
background and the Query Builder in Access was pretty good (and
clearly the template for EM's)!

Yes and no. One is an application. The other is a database engine with
some GUI stuff thrown on top of it.

EM IS handy, no doubt about it, but keep in mind it's masking a LOT of
stuff.

Ah, trying to start a Holy War, eh? Seriously, I parenthesise things
because that's how I write my code. So that it is unambiguous to
someone (else) looking at it. Sure, if you just have a string of
clauses joined by ANDs it doesn't really matter, but when you factor
in a mixture of ORs and ANDs you'll be glad of the parentheses and,
what's important, so will I when I come to maintain your code.
I tend to agree, for human readability, parenthesis are very nice. But
keep in mind, the optimizer will strip off and re-arrange things as it see
fits (keeping the truth values the same of course.)
I don't, but thanks for the warning. With a bit of luck I'll be
retired by 2079 and sunning myself on Mars.

Don't we all. Of course didn't all the folks in the 50s and 60s coding two
digit years. :-)
But so typical of Microsoft. Code something that rewrites what the user
intended without
having the civility to ask. I wonder when my buddies up the street will
learn that it isn't nice
to do things for people without asking permission first.

This is JUST what bugged me about it. Microsoft didn't even have the
civility to inform me that it thought my code was crap, and that their
way was better. Only trouble is, having looked at quite a deal of
their sample code, I wouldn't bet on theirs being better than mine in
all circumstances. In any case, why not let us go to hell in our own
handbaskets?

I suspect (and this was just a quick pass looking at the two queries) that
somehow EM is rewriting it to present a slightly more optimized version to
the SQL Engine. As far as I could tell, truth-value-wise, they were the
same.

In any case, I would follow other folks recommendations, use Query Analyzer
or other tools to write your queries.


But thanks to all who took the trouble to reply. It's just one of
those things, I guess.

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk