Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-01-17 : 10:00:04
|
| Horst writes "A select distinct using fields of data type sql_variant or uniqueidentifier is leading to performance problems (here: from 1 to 12 seconds): select distinct field1 from ... (shortened example)Distinct seems also to ignore casting/converting fields: select distinct cast(field1 as varchar); 12 sec.To cast/convert fields and pass them to an outer select has also no effect: select distinct f1 from (select cast(field1 as varchar) f1) ...; 12 sec.I found no description of this problems, only the general hint of performance problems using this data types and I'm not sure if there are good solutions for this problem???And I did'nt find anything to: (possible) data type changing from inner select to outer select (which maybe is a good solution to this or a hint that this is not impossible?).Thank you in advance,Horst.Windows 2000, SQL Server 2000, all actual service packs" |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-01-18 : 20:04:28
|
| Using DISTINCT with a uniqueidentifier is unnecessary. By definition a uniqueidentifier is... unique. So there will not be any duplicates for DISTINCT to remove. But that doesn't mean SQL Server won't scan the resultset just in case (I don't know if it will or not) because you put DISTINCT in there.Using sql_variant data types requires conversion of the data element to its base data type before comparisons can be made. Therefore there is a lot of converting being done when you include a DISTINCT clause.Is it really necessary for you to use a sql_variant?---------------------------------------------------------------------------------Infoneering: Information Technology solutions engineered to professional standards. |
 |
|
|
|
|
|
Performance problem using sql_variant or uniqueidentifier fields in select distinct