CODEWITHSUNDEEP
sqlsql-serverselectsubquery
fredzyadi
fredzyadi

Reputation: 85

How do I use variables in a select query?

I have this following select query that uses a scalar function to get full name. I want to eliminate the redundancy by using variable but so far there is no success. My query is

select 
 a.Id, 
 a.UserName, 
 getFullName(a.UserName),
 a.CreateTime
from DataTable;

I don't want to retrieve 'a.User' two times. I would prefer if I can save a.User in a variable and then pass it to the function hence improving the efficiency.

Currently the work around I came up with is as following

select 
 Id, 
 UserName, 
 getFullName(UserName), 
 CreateTime
from (select a.Id, a.UserName, a.CreateTime from DataTable) temp

This solves the performance issue but adds the overhead to write same select two time. Any other suggestions would be great.

DataTable looks like this

+----+----------+------------+
| Id | UserName | CreateTime |
+----+----------+------------+
|  1 | ab       | 10:00      |
|  2 | cd       | 11:00      |
|  3 | ef       | 12:00      |
+----+----------+------------+

Here is the NamesTable used to get the full names

+----------+----------+
| UserName | FullName |
+----------+----------+
| ab       | Aa BB    |
| cd       | Cc Dd    |
| ef       | Ee Ff    |
+----------+----------+

Here is the function that gets the full name

Create function [dbo].[getFullName](@user varchar(150)) returns varchar(500)
as
begin
    declare @Result varchar(500);

  select @Result = FullName from dbo.NamesTable where UserName =  @user;
  return @Result;
end;

Upvotes: 3

Views: 159

Answers (3)

TheGameiswar
TheGameiswar

Reputation: 28900

Scalar UDF will execute for every row,but not defintely the way you think.below is sample demo and execution plan which proves the same..

create table testid
(
id int,
name varchar(20)
)


insert into testid
select n,'abc'
from numbers
where n<=1000000

create index nci_get on dbo.testid(id,name)

select id,name,dbo.getusername(id) from dbo.testid where id>4

below is the execution plan for above query enter image description here

Decoding above plan: Index seek outputs id,name
Then compute scalar tries to calculate new rows from existing row values.in this case expr1003 which is our function

Index seek cost is 97%,compute scalar cost is 3% and as you might be aware index seek is not an operator which goes to table to get data.so hopefully this clears your question

Upvotes: 2

Tom H
Tom H

Reputation: 47464

As D Stanley says, you're trying to solve some problem that doesn't exist. I would further add that you shouldn't be using the function at all. SQL is meant to perform set-based operations. When you use a function like that you're now making it perform the same function over and over again for every row - a horrible practice. Instead, just JOIN in the other table (a set-based operation) and let SQL do what it does best:

SELECT
    DT.Id,
    DT.UserName,
    NT.fullname,
    DT.CreateTime
FROM
    DataTable DT
INNER JOIN NamesTable NT ON NT.username = DT.username;

Also, DataTable and NamesTable are terrible names for tables. Of course they're tables, so there's no need to put "table" on the end of the name. Further, of course the first one holds "data", it's a database. Your table names should be descriptive. What exactly does DataTable hold?

If you're going to be doing SQL development in the future then I strongly suggest that you read several introductory books on the subject and watch as many tutorial videos as you can find.

Upvotes: 3

D Stanley
D Stanley

Reputation: 152556

You're solving a problem that doesn't exist. You seem to think that 

select 
 a.Id, 
 a.UserName, 
 getFullName(a.UserName),
 a.CreateTime
from DataTable;

Has some relatively expensive process behind it to get UserName that is happening twice. In reality, once the record is located, getting the UserName value is an virtually instant process since it will probably be stored in a "variable" by the SQL engine behind the scenes. You should have little to no performance difference between that query and 

select 
 a.Id, 
 getFullName(a.UserName),
 a.CreateTime
from DataTable;

The scalar function itself may have a performance issue, but it's not because you are "pulling" the UserName value "twice".

A better method would be to join to the other table:

select 
 a.Id, 
 a.UserName, 
 b.FullName,
 a.CreateTime
from DataTable a
LEFT JOIN dbo.NamesTable b
  ON a.UserName = b.UserName

Upvotes: 4

Related Questions