September 13, 2018 at 12:36 pm
Over the years I've developed the habit of doing this in circumstances where I need to fetch a couple of parameters from a row in a table:
set @id = (select top 1 id from tbl_EmailQueue where IsSent = 0)
set @recipient = (select top 1 AddressRecipient from tbl_EmailQueue where id=@id)
set @deptCopy = (select top 1 AddressDepartment from tbl_EmailQueue where id=@id)
I don't know why, but this just seems wrong to me. It hasn't been a performance issue because I'm working with really tiny tables but on the whole I think there must be a better way to do this and I'll be durned if I know what that could be. Thoughts?
September 13, 2018 at 12:43 pm
thelenj - Thursday, September 13, 2018 12:36 PMOver the years I've developed the habit of doing this in circumstances where I need to fetch a couple of parameters from a row in a table:set @id = (select top 1 id from tbl_EmailQueue where IsSent = 0)
set @recipient = (select top 1 AddressRecipient from tbl_EmailQueue where id=@id)
set @deptCopy = (select top 1 AddressDepartment from tbl_EmailQueue where id=@id)I don't know why, but this just seems wrong to me. It hasn't been a performance issue because I'm working with really tiny tables but on the whole I think there must be a better way to do this and I'll be durned if I know what that could be. Thoughts?
The 2 set statements can be rewritten as a single select statement.set @recipient = (select top 1 AddressRecipient from tbl_EmailQueue where id=@id)
set @deptCopy = (select top 1 AddressDepartment from tbl_EmailQueue where id=@id)
SELECT TOP (1)
@recipient = AddressRecipient
, @deptCopy = AddressDepartment
FROM tbl_EmailQueue
WHERE id = @id;
September 13, 2018 at 12:50 pm
DesNorton - Thursday, September 13, 2018 12:43 PMthelenj - Thursday, September 13, 2018 12:36 PMOver the years I've developed the habit of doing this in circumstances where I need to fetch a couple of parameters from a row in a table:set @id = (select top 1 id from tbl_EmailQueue where IsSent = 0)
set @recipient = (select top 1 AddressRecipient from tbl_EmailQueue where id=@id)
set @deptCopy = (select top 1 AddressDepartment from tbl_EmailQueue where id=@id)I don't know why, but this just seems wrong to me. It hasn't been a performance issue because I'm working with really tiny tables but on the whole I think there must be a better way to do this and I'll be durned if I know what that could be. Thoughts?
The 2 set statements can be rewritten as a single select statement.
set @recipient = (select top 1 AddressRecipient from tbl_EmailQueue where id=@id)
set @deptCopy = (select top 1 AddressDepartment from tbl_EmailQueue where id=@id)
SELECT TOP (1)
@recipient = AddressRecipient
, @deptCopy = AddressDepartment
FROM tbl_EmailQueue
WHERE id = @id;
Excellent.
Would this work even better?
SELECT TOP (1)
@id = Id
,@recipient = AddressRecipient
, @deptCopy = AddressDepartment
FROM tbl_EmailQueue
WHERE IsSent=0
I need the id to update the record if it is processed. This helps. Thanks.
September 13, 2018 at 1:09 pm
SELECT TOP (1)
without an ORDER BY is a somewhat suspect practice.
If more than one row matches the WHERE clause, the result which is returned is likely to change from one execution to another, because SQL Server query results do not have a default order.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 13, 2018 at 1:10 pm
I need the id to update the record if it is processed. This helps. Thanks.
Can't you do this all in a single step?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 13, 2018 at 1:21 pm
Phil Parkin - Thursday, September 13, 2018 1:09 PMSELECT TOP (1)
without an ORDER BY is a somewhat suspect practice.If more than one row matches the WHERE clause, the result which is returned is likely to change from one execution to another, because SQL Server query results do not have a default order.
In this case I'm just sending an email if the record is found.
The order is unimportant - it is very low volume and will run from a SQL Job that checks for candidates every 5 minutes.
But yes, you're correct - TOP should have an order by clause but here TOP is just limiting the result to 1 record and that is its only function.
Is there a best practice for that I'm missing?
September 13, 2018 at 1:27 pm
thelenj - Thursday, September 13, 2018 12:50 PMDesNorton - Thursday, September 13, 2018 12:43 PMthelenj - Thursday, September 13, 2018 12:36 PMOver the years I've developed the habit of doing this in circumstances where I need to fetch a couple of parameters from a row in a table:set @id = (select top 1 id from tbl_EmailQueue where IsSent = 0)
set @recipient = (select top 1 AddressRecipient from tbl_EmailQueue where id=@id)
set @deptCopy = (select top 1 AddressDepartment from tbl_EmailQueue where id=@id)I don't know why, but this just seems wrong to me. It hasn't been a performance issue because I'm working with really tiny tables but on the whole I think there must be a better way to do this and I'll be durned if I know what that could be. Thoughts?
The 2 set statements can be rewritten as a single select statement.
set @recipient = (select top 1 AddressRecipient from tbl_EmailQueue where id=@id)
set @deptCopy = (select top 1 AddressDepartment from tbl_EmailQueue where id=@id)
SELECT TOP (1)
@recipient = AddressRecipient
, @deptCopy = AddressDepartment
FROM tbl_EmailQueue
WHERE id = @id;Excellent.
Would this work even better?
SELECT TOP (1)
@id = Id
,@recipient = AddressRecipient
, @deptCopy = AddressDepartment
FROM tbl_EmailQueue
WHERE IsSent=0I need the id to update the record if it is processed. This helps. Thanks.
My bad. I missed that the 2nd and 3rd queries used the result of the 1st query.
Yes. You can definitely do it as 1 query.
September 13, 2018 at 1:29 pm
thelenj - Thursday, September 13, 2018 1:21 PMPhil Parkin - Thursday, September 13, 2018 1:09 PMSELECT TOP (1)
without an ORDER BY is a somewhat suspect practice.If more than one row matches the WHERE clause, the result which is returned is likely to change from one execution to another, because SQL Server query results do not have a default order.
In this case I'm just sending an email if the record is found.
The order is unimportant - it is very low volume and will run from a SQL Job that checks for candidates every 5 minutes.
But yes, you're correct - TOP should have an order by clause but here TOP is just limiting the result to 1 record and that is its only function.
Is there a best practice for that I'm missing?
You could add an ORDER BY id. Assuming that id is just an incrementing integer value, you would be processing the mails in a FIFO method.
September 13, 2018 at 8:01 pm
One subtle thing to be careful of is the difference between these two styles if you don't get any rows as a result from the query. Using SET will result in the variables being changed to NULL, but doing it in a SELECT will leave the variables unchanged. If that's going to upset your code, make sure you reset the variables to NULL first.
September 14, 2018 at 5:47 am
andycadley - Thursday, September 13, 2018 8:01 PMOne subtle thing to be careful of is the difference between these two styles if you don't get any rows as a result from the query. Using SET will result in the variables being changed to NULL, but doing it in a SELECT will leave the variables unchanged. If that's going to upset your code, make sure you reset the variables to NULL first.
Ah, thank you for that heads up!
In my use case I only call the procedure every 5 minutes or so and I run it once if a record is found so there is no real difference; however, your point here would have been a difficult bug for me to figure out had this been put in a loop or something. (This is a very low volume temporary solution as the application will be replaced within the next year)
Nice.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply