When I've created a table filled it with data then select distinct data from it, it takes hours and is still running up to 17 hours.
Removing the distinct returns all the rows. Creating the same table in the master or MSDB database and running the select distinct statement it returns in a second.
I can't see any apparent differences in the databases in terms or collation or compatibility.
The script I'm using is below:
if exists (select 1 from sys.tables where name = 'temp_table')
drop table temp_table
CREATE TABLE temp_table (data(100) null)
-- Populating the temp_table takes a few seconds
FROM myTable with (nolock)
-- Takes 17 hours and is still running
select distinct data from temp_table
Any ideas why?
I've check disk I/O, CPU and memory and they all seem fine.