SQL Server Performance Forum – Threads Archive

How will performance differ from these 2 queries

The table is test_emp
———–
emp_id char(12),emp_name char(50) indexed on emp_id the emp_id will have the format abc-ind-0001, abc-ind-0002 … here I want to take the maximum of emp_id. Query 1. select max(substring(emp_id,8,4)) from test_emp Query 2. select substring(emp_id,8,4) id into #temp from test_emp select max(id) from #temp In these 2 quries, which will be fast if there are half million rows ? What type of scan ( index/table) will each query use ?
Best Regards Jayaprakash Chandrasekaran

---

Use query analyser to compare the execution plans of both queries. I imagine the first will use a table scan or clustered index scan.
The second will use a table scan since there are no indexes on your newly created temp table.

---

you really don’t want to insert 0.5M rows into a temp table when a simple query yields the results

---

SQL Server is set-based. The less sets you process, and the smaller the sets, the faster it will execute. Also, you should never use INTO #table. This will lock up tempdb and cause big problems for everyone. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

]]>