October 19, 2007 at 10:20 am
I have a SQL Server instance with tempdb and user db using Latin1_General_CI_AS collation.
In the user db I create a temp table like this:
Create Table #tmpResults1 (PKValue varchar(2000))
I have ROWCOUNT set to 100 to limit number of rows.
If I run the following select, I get a result set ordered as anticipated (0000000001, 0000000002, etc):
SelectDistinct RIGHT('0000000000'+CONVERT(NVARCHAR,ET."StockCode"),10)
From"user"."My_RESULTS" ET
Where(RIGHT('0000000000'+CONVERT(NVARCHAR,ET."StockCode"),10) > '')
And( 1=1 )
Order By RIGHT('0000000000'+CONVERT(NVARCHAR,ET."StockCode"),10) asc
StockCode in the table referenced by the "user"."My_RESULTS" view is an INT.
If I re-use the select above as part of an insert into the temp table, and then select out of the temp table, I get a completely different set of data returned (e.g. 0000004409, 0000004410, etc):
Insert
Into#tmpResults1
(
PKValue
)
SelectDistinct RIGHT('0000000000'+CONVERT(NVARCHAR,ET."StockCode"),10)
From"user"."My_RESULTS" ET
Where(RIGHT('0000000000'+CONVERT(NVARCHAR,ET."StockCode"),10) > '')
And( 1=1 )
Order By RIGHT('0000000000'+CONVERT(NVARCHAR,ET."StockCode"),10) asc
Select * From #tmpResults1
I have tried changing the CONVERT functions to convert to VARCHAR (to match the temp table field definition) rather than NVARCHAR, but this makes no difference.
If I run the same test on a different SQL Instance with the tempdb and user db having collation SQL_Latin1_General_CP1_CI_AS, then both the simple select and the select out of the temp table after insertion return the same correctly ordered result set.
Any ideas/thoughts/solutions would be greatly appreciated.
November 6, 2007 at 3:59 am
It appears this has something to do with the DISTINCT clause, as it works as expected with the DISTINCT clause removed, but continues to return incorrect data when the DICTINCT clause is included.
Anybody?
November 7, 2007 at 5:22 am
when you create the temp table, use the 'as collation' clause so collation exactly matches that of the user db.
HTH
george
---------------------------------------------------------------------
November 7, 2007 at 5:23 am
The ROWCOUNT option is ignored when you do a INSERT INTO ... SELECT statement (see BOL). It is honoured for your initial 'Select Distinct RIGHT('0000000000'...' and for your 'Select * From #tmpResults1'.
However, for the select from #tmpResults1 you are not specifying the order in which data should be returned, so you will get the first 100 rows found for the table. If some of the pages for the table have been written to disk (maybe the first few pages) and SQL can find 100 rows in what is still in memory it will give you those.
If you want to get the FIRST 100 rows from #tmpResults1 then you need to do 'Select * From #tmpResults1 order by PKValue'
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 8, 2008 at 10:04 am
George, can you provide an example?
August 8, 2008 at 3:47 pm
e (8/8/2008)
George, can you provide an example?
yes sorry remembered syntax wrong, see BOL for create table, use the collate clause for the column definitions to specify the collation you want to use, and match the temp table to the collation used in application db table.
---------------------------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply