SELECT ALL FIELDS , best method ??
-
In MS Access I oftern used SELECT * FROM
or SELECT
.* FROM
Are there any other ways to reference all fields in a table, apart from listing all the fields in the SELECT statement.
Is this ok to do in TSQL, where vwDups is a view to find duplicate records.
SELECT * FROM [vwDups]
IF @@ROWCOUNT > 0
BEGIN
RAISERROR 'Error : Dups are present !'
RETURN -1233
END
Please advise ?
- If all you want to do is get the count you might as well do aDECLARE @count INTSELECT@count = COUNT(*)FROM[vwDups]IF @count > 0BEGINRAISERROR 'Error : Dups are present !'
RETURN -1233
END
You might not notice the performance difference but this one would be faster since you are only getting the count rather than the entire record set.******************
Dinakar Nethi
Life is short. Enjoy it.
****************** -
I don't think there is anything wrong with Select * except that as columns may be added or removed from the table over time your result set may not be what you expected.
As mentioned, in your example using Count(*) would be a much better choice. It would be faster and use less resources.
-
Well, if you just want to send an alert if the view returns something, why not use this as a condition:
IF EXISTS (SELECT * FROM [vwDups])
That is, if you don't really need to count how many duplicities there are, just return an error if it is so, this should be the most efficient approach. Not only it does not return a rowset, it even does not bother to count returned records - just makes a check whether there is at least one record. As soon as one record is found, the statement is terminated. Also, it does not require declaring any variable to store the result. Of course, if you need the count to be stored and returned or otherwise used, then COUNT(*) is the right solution.
I tend to use EXISTS (or NOT EXISTS) when checking existence, and COUNT only if I really need to know how many matching records there are - partially because of performance, partially for the sake of clarity of the code. However, it is true that COUNT(*) does the same work and on small tables there hardly is any difference in resources used.
-
If you're wanting to delete dupes you could use a correlated subquery such as:
DELETE FROM dbo.tblScanDetail
WHERE
EXISTS
(
SELECT
ID, column1, column2, column3, column4
FROM
tbltable tbltableInner
WHERE
tbltable.column1 = tbltableInner.column1
tbltable.column2 = tbltableInner.column2
AND tbltable.column3 = tbltableInner.column3
AND tbltable.column4 = tbltableInner.column4
AND tbltableInner.ID > tbltable.ID
 
You need to have an identity column on the table (represented by ID).
-
Just a quick tip: IF EXISTS( SELECT TOP 1 * FROM [vwDups]) will be a bit quicker to check for existence...
I wasn't born stupid - I had to study.
-
Now, that's interesting, Farrell... I always assumed that there is no need to use TOP 1 in EXISTS clause, since the EXISTS itself requires only 1 matching record to return TRUE. Is there really any performance gain with the additional TOP 1? I never tested this, but I seem to recall that I read somewhere that it doesn't help
-
I don't think there is any advantage in specifying TOP 1, since EXISTS will stop evaluating anyway, after it has found one matching row.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply