Query Optimization

Question regarding performance.

If I have a query such as:

Select UserId, Firstname,lastname from members where country='can'

If I were to call this from an .net executable as straight SQL to the Database vs. encapsulating the command in a stored procedure and calling the procedure.

What would be the performance differences? Would their be any issues (outside of security) that would make me choose to place the call in a Procedure?

Thanks

It probably doesn't matter in this case. Executing the SELECT statement using a parameterized command object will provide same benefit as calling SP with slighly more overhead. RPC execution for SP calls provide better performance than sending the entire SQL text. The answer is that it depends on your needs. Changing SQL statements embedded in applications is often harder than modifyin a SP. You can also take a look at the whitepaper below for more information:

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

query not returning data!

Hi! I have a sql query in stored procedure:
SELECT Salutation + ' ' + FirstName + ' ' + LastName AS fullname

Ok, this returns a value if salutation is not null, but if the salutation is null it doesn't return any value, I was thinking if the saluation is null then I would atleast get the firstname and last name. Any help appreciated on this.

You can us ISNULL or COALESCE to take care the NULL value in your Salutation column before you combine it with firstname and lastname. For example:

SELECT ISNULL(Salutation,'') + ' ' + FirstName + ' ' + LastName AS fullname

Or

SELECT COALESCE(Salutation,'') + ' ' + FirstName + ' ' + LastName AS fullname

|||

To be precise, the query is returning a value. The value is NULL, which in SQL terms is unknown.

So, when you add (concatenate) a known value to an unknown one, what should the result be?

Unknown!

It's analogous to saying, I'm going to add $5 to the money in my pocket. How much money is in my pocket?

You don't know, because you don't know how much money is already in my pocket.

(You don't even know if there is $5 in my pocket - there might be a hole in it. )

You have to be very careful to account for NULL values in the database.

Nothing is less than, greater than, or equal to NULL. Even NULL!

|||

Thank you! It works great now. What does COALESCE mean?

Cheers!

|||

Thank you for the explanation! Make sense.