I ran a couple of quick tests against a fully indexed table with 1.3 million rows, and it does appear that for your example charindex is quicker.
Like: 5 seconds (avg over 10 runs)
Charindex: 4 seconds (avg over 10 runs)
However, for searches on beginning of data, like outperformed patindex...
I have a ridiculously large database at work, with a multitude of tables, and had the same problem. However, using a select count(*) from tablename wasn't terribly efficient, so I ran sp_spaceused instead, which is fine in most cases:
CREATE TABLE #spaceused
(name varchar(20), rows int...
If you want the search to return any values of subid if you pass in NULL, then:
SELECT * FROM tbl
WHERE MainID = @MainID
AND SubID = ISNULL(@SubID, SubID)
If you want the search to return only those matching NULL, then you could try:
SELECT * FROM tbl
WHERE MainID = @MainID
AND ISNULL(SubID...
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.