Sql Server 2000 Enterprise Versus Standard Edition
-
Here is a sample query that works on SQL Server 2000 Standard Edition (SP3), but fails on SQL Server 2000 Enterprise Edition (SP3).
SELECT tb_contact_id, tb_contact_email FROM tbEmailContacts WITH (NOLOCK) WHERE tb_contact_ssn= 000002903
Here is SQL for the table definition
CREATE TABLE [dbo].[tbEmailContacts] ( [tb_contact_id] [int] IDENTITY (1,1) NOT NULL ,[tb_contact_ssn] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,[tb_contact_email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[tb_contact_modified_user] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[tb_contact_modified_date] [smalldatetime] NULL ) ON [PRIMARY]
In the Enterprise Edition, executing the query from SQL Query Analyzer returns the message:
Server: Msg 245, Level 16, State 1, Line 1 Syntax error converting the varchar value 'ï¼'ï¼-ï¼(tm)' to a column of data type int.
Is there a way we can get Standard Edition to behave like Enterprise Edition in this scenario (i.e., quotes should be required in the query)?
Please share your thoughts and expertise.
Thanks in advance.
-
Is it possible that you have some bad data in the enterprise edition server??
I don't know what this is (ï¼'ï¼-ï¼(tm)), but it surely doesn't seen like a ssn to me.
On a last note, when you write something like this :
WHERE tb_contact_ssn = 000002903
000002903 is treated as an integer, therefore tb_contact_ssn is converted to int before being compared. This means 2 things, if the column is not numeric, you get an error message like the one you're getting, 2 an index SCAN is used because a convert must be made. If you were to use where = '000002903', you should be getting an index seek (assuming that this column is indexed of course).
-
I don't think it is a difference between standard and enterprise, but probably in the various SET options that are enabled or disabled. Remi is right though, if you are filtering on a char/varchar column, give it a char/varchar to compare against...
But.. I would've thought that SQL would convert 000002903 to '000002903' and then perform the query, rather than converting every row? This also makes more sense because there is no guarantee that SQL could convert the string in the row to an int to do an integer compare.
-
I'd never assume that SQL will convert the way you'd expect. I believe that a conversion to integer would be seen by the execution engine as 'simpler' than converting to varchar, so the int would be picked.
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
-
I would hope so... but how would you explain the error then??
It's either corrupted data or conversion error.
-
if object_id('Tempdb..#temp') > 0
DROP TABLE #temp
GO
Create table #temp
(
id int not null identity (1,1) primary key clustered,
SSN CHAR(9) not null
)
GO
Insert into #temp (ssn) values ('123456789')
Insert into #temp (ssn) values ('987654321')
Insert into #temp (ssn) values ('324365789')
Insert into #temp (ssn) values ('887766554')
Insert into #temp (ssn) values ('ConvertEr')
GO
SET SHOWPLAN_TEXT ON
GO
Select * from #temp where ssn = '123456789'
--1 row(s) affected
-- |--Clustered Index Scan(OBJECT
[tempdb].[dbo].[#temp___450].[PK__#temp__1E9768F7]), WHERE[#temp].[SSN]='123456789'))GO
Select * from #temp where ssn = 123456789
--error
-- |--Clustered Index Scan(OBJECT
[tempdb].[dbo].[#temp___450].[PK__#temp__1E9768F7]), WHEREConvert([#temp].[SSN])=123456789))GO
SET SHOWPLAN_TEXT OFF
GO
drop table #temp
GO
-
Very true Remi - hmm. Learn something every day
Still think it's not the smartest, but it is the way it is... I guess I've never really queried a varchar column with an int value so have not previously run into this issue.I reckon you have data corruption, or at least you have a funny serial number (that odd one you provided) which cannot convert to an it. Query the table using quotes around your serial number and try again.
-
Is there a reason that this number is kept as a (var)char(9)?
I know that there must be some ssn that start with a 0, but it's still easy to correct that presentation stuff at the client. Wouldn't it be better to keep it as an int and have only 4 bytes to check vs 9?
-
BTW I agree that it would be faster to convert the right part of the expression to the column's type. But if I remember correctly, the server has the obligation to send you the data you request. Needless to say that '012345678' is just not the same as 12345678. This is probabely the reason of the conversion done that way.
-
Couple questions after reading all these:
1. What set options are you using?
2. Do you have the same data in both copies?
3. Have you ran a DBCC CHECKDB to look for corruption?
4. What kind of indexes do you have on the table? Can you post the CREATE TABLE and CREATE INDEX statement?
Other than that, you should probably consider using a CHAR(9) for the SSN field, with a front-end validation of being all numeric. It's a recorded standard, if you are interested in knowing about it:
http://www.ssa.gov/employer/ssnvs_handbk.htm
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA. -
Good Day,
I want to thank you all for the excellent responses. They have all helped me a great deal. This is by far the best Sql Server bulletin board in the known universe. And one that's still free which is fast becoming a rarity these days. It seems, all the other bulletin boards want money and give less valuable information.
It looks like the first response (Remi) was closest to what I needed.
After further investigation, here's what I found:
It turns out that in our production database, there were a couple of records that had non-numeric values in the social security number field. I think one was entered incorrectly.
All of the responses were helpful though and I will be exploring the various Set options that were mentioned.
Thanks again for the excellent responses. You people are the greatest !!!!
Sincerely
-
HTH.
If you want to make sure that the data remains numeric, you want ask you developper to validate the data before the insert.
And then add a check constraint SSN like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply