Hi,I am using SQL Server 2005 (9.0.1399). When I use some non-deterministic system functions in the where clause, the execution plan looks not good to me. Here is a example:First, create a test table use tempdb; go if object_id('test') is not null drop table test; go create table test (UserName varchar(256), a varchar(100)); go create unique clustered index cidx_uname on test(UserName) go insert into test values('Tom', 'test') insert into test values('Jerry', 'test') insert into test values('dbo', 'test') insert into test values('Marry', 'test') insert into test values('John', 'test') gothen look at the execution plans of the following two queries:select * from test where username = user_name()select * from test where username = convert(varchar(256),user_name())
The plan for the first query is a clustered index scan, whereas the plan for the second query is a clustered index seek. When the tables are large and queries are more complex, the difference is quite significant.I tried some other system functions. I found object_name() has the same behavior and getdate() is OK.Could someone tell me why the query plans are different? Is there any side effect with the second query?Thanks
non-deterministic system functions in where clause
Posted - 2009-04-01 : 12:55:41
