Is it efficient to use SQL functions on the left-hand side of SELECT commands?

I'm designing a C# program using .NET and Windows Forms. At one point I need to open a form that contains a DataGridView object and a TextBox. The DataGridView object is to be fed with the input on the TextBox and show real-time matches from the database.

To accomplish this, I've been using the simple SQL Query that 开发者_开发技巧follows:

SqlCommand cmd = "SELECT name,document_no FROM clients WHERE LEFT(name, " + textboxname.Text.Len + ") = '" textboxname.Text + "'";

It works for now, while my database contains about a dozen of rows, but is it efficient for large-sized (2k+ entries) databases?

---

The approach you're taking won't be efficient - at least, not if there's an index on the name column (if there is no such index then expect poor performance whatever you do). Typically, any function called in a WHERE clause where one or more of the parameters are columns is going to defeat any attempts of the query optimizer to use indexes on those columns.

Much better is:

SELECT name,document_no FROM clients WHERE name LIKE 'yourtextboxtexthere%'

...which will have a good chance of using the index on name.

---

2k+ entries, yeah, you are probably OK. 2M+ entries and I'd say that you are not OK. the LEFT function probably won't use an index. Consider using the "WHERE name LIKE " + txtboxname.Text + "%" instead.

problems to consider.

1. Sql injection, you should either sanitize the txtboxname.Text or use parameterized queries.
2. Indexes usually are used with the LIKE statement as long as the '%' is at the begining or the end of the statement and not both (ala '%sometext%')