June 20, 2011 at 12:13 pm
Hi ,
I wrote a procedure to populate a table called Regiontable.
when I select a field called state and region
it gives me wiered result.When I executed the below Query
I got the results which was perfect.
select distinct region ,state from regiontable
Eastern MA
Eastern NY
Eastern RI
Eastern GA
Eastern Unknown
Eastern NH
After 20 secs when I executed the same query I got
EASTERN MA
Eastern NY
Eastern RI
EASTERN GA
Eastern Unknown
Eastern NH
Again after few secs I got from same query the below results
EASTERN MA
Eastern NY
Eastern RI
Eastern GA
Eastern Unknown
EASTERNNH
So every time I execute the query the region is changing from lower case to upper case which is scewing up my report.I am confused and not able to figure it out what is wrong.Can some one help me.Any help would be appreciated.Thank you
June 20, 2011 at 12:26 pm
Hi,
It will be important for you to show us the stored procedure so we can see how this table is being populated. Is it possible that this SP is inserting data between each execution of your query? That is the only thing that can be giving these results.
Thanks,
Jared
Jared
CE - Microsoft
June 20, 2011 at 4:51 pm
Yo,
With only that information you have given me I am gonna put money on the select DISTINCT is the issue - SQL normally works top to bottom hence the sequential but leaving it to chance is bad design.
Your issue (On that information) is how ever you input that city field you allowed free text. text and TEXT mean the same thing to SQL but not to other things so your correct in thinking they could stuff up your report.
My main suggestion is to run a script across your data something like
UPDATE city
set city_name = 'Upper or lowercase which ever you want"
where city_name = 'The old one'
A good database design would have the city value in a lookup table and reference that from the customer table precisely to avoid issues like this, if this is a PROD database there is little point to reinventing the wheel in order to get the structure correct. Doing something as simple as adding a look up list to your front end... I am going to go ahead and assume the front end is... vb.net for instance.
With that you can create a look up list in the database to populate a value field... The less typing you give them!! The less they will stuff up!!
Good luck with it...
Caz
June 20, 2011 at 5:00 pm
C a z (6/20/2011)
With only that information you have given me I am gonna put money on the select DISTINCT is the issue - SQL normally works top to bottom hence the sequential but leaving it to chance is bad design.Your issue (On that information) is how ever you input that city field you allowed free text.
Caz is quite correct here, though the second component involved here is collation. It's case insensitive, which means the sort picks up whichever one comes up first, and does a 'top 1' for each 'group'.
Might want to do a data cleanup if this is an issue. There's a number of proper capitilization scripts out there that will deal with cities with multiple words and the like.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 20, 2011 at 5:12 pm
I want to make note here that collation may not the issue. Collation allows you to filter on case, not insert and return it.
Collated ex: SELECT * FROM TABLE WHERE COLUMN = 'STRING' This will NOT return rows where column is 'String', 'string', or 'strinG'
Not collated ex: SELECT * FROM TABLE WHERE COLUMN = 'STRING' This WILL return rows where column is 'String', 'string', or 'strinG'
That being said, the other responses are correct in that a select distinct will grab the first row that it finds. The results you have there tell me that your table is indexed and that the index is not on either column being returned. Therefore, the table reorganizes upon insert and returns different results every time a row is inserted that may change the order of data in the table. I would clean your data and then create a reference table (as mentioned earlier) that will keep the data consistent. OR you can script to return the data in the format you want either in the report itself, or the query.
Example: select distinct UPPER(SUBSTRING(region,1,1)) + LOWER(SUBSTRING(region,2,LEN(region))) ,UPPER(state) AS State from regiontable
Thanks,
Jared
Jared
CE - Microsoft
June 20, 2011 at 5:17 pm
jared-709193 (6/20/2011)
I want to make note here that collation may not the issue. Collation allows you to filter on case, not insert and return it.
Incorrect, please test the following script:
CREATE TABLE #abc (SomeChar VARCHAR(20) COLLATE SQL_LATIN1_GENERAL_CP437_CS_AS)
CREATE TABLE #def (SomeChar VARCHAR(20) COLLATE SQL_LATIN1_GENERAL_CP437_CI_AS)
INSERT INTO #abc VALUES ( 'string')
INSERT INTO #abc VALUES ( 'String')
INSERT INTO #abc VALUES ( 'strinG')
INSERT INTO #def VALUES ( 'string')
INSERT INTO #def VALUES ( 'String')
INSERT INTO #def VALUES ( 'strinG')
select distinct SomeChar FROM #abc
select distinct SomeChar FROM #def
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 20, 2011 at 5:24 pm
Craig wins it...
There is an issue in parsing upper and lower case and collation.
Regardless... sitting down with a database plan cures all!!
Caz
June 20, 2011 at 5:27 pm
I'm sorry, maybe I was not clear... The default collation for SQL Server is not case-sensitive. I believe we are saying the same thing 🙂
Thanks,
Jared
Jared
CE - Microsoft
June 20, 2011 at 5:36 pm
jared-709193 (6/20/2011)
I'm sorry, maybe I was not clear... The default collation for SQL Server is not case-sensitive. I believe we are saying the same thing 🙂Thanks,
Jared
Nope, definately not clear. You were saying something quite different though when you declared that the collation only affected the where clause.
Also, default collation for SQL Server can be set during installation. If a CS collation is set at that time, everything is set that way... including tbl object names which can drive me batty when I'm trying to type code and there are tables: tblA, tbla, and TBLa.
However, if you don't play with the switches during installation, you're correct, it's set to a CI collation... usually.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 20, 2011 at 5:40 pm
Caz, (not trying to be arrogant, but to help you completely understand) please keep in mind that it is not a "parsing" issue. It is a data/design issue. In fact, there is no "parsing" occurring on case. However, as I mentioned earlier new data is getting ordered above old data giving you inconsistent results.
I had this issue a couple years ago and thought I could share the example:
CREATE TABLE Ref_Region (RegionId INT, RegionName VARCHAR(100), StateAbbrev CHAR(2))
--insert data into it and place any constraints or identity_insert if you wish
I also had a manager column in this table. Then you can just place the RegionID in your other table and reference it. Of course, this means that you have to offer a drop down type list for users to choose from where the data set will be from this reference table.
Thanks,
Jared
Jared
CE - Microsoft
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply