April 2, 2015 at 4:00 pm
I need to find the ones ending in a decimal. So if you look at the list below, I need a script that will return the value 001. ,002., 004. ..
001
001.
002.
003
004
004.
April 2, 2015 at 6:08 pm
these must be text...
SELECT *
FROM MyTable
WHERE RIGHT(MyColumn,1)='.'
performance is going to be abysmal, because it's a trailing character, so you're going to get a table scan.
April 3, 2015 at 6:53 am
Depending on the usage, distribution and cardinality, there are few options to speed this up.
😎
One would be adding a calculated column and then index the column, this has little impact on the inserts but speeds up the query by aprox. factor of 10.
Another is to add a column and an insert trigger which sets a flag for the selected values, then add a filtered index on that column. This slows down the inserts by a factor of 2 but speeds up the query by a factor of aprox. 100-200.
April 7, 2015 at 8:45 am
I would use CHARINDEX
WITH myString AS (
SELECT '001' AS num
UNION ALL
SELECT '002' AS num
UNION ALL
SELECT '003' AS num
UNION ALL
SELECT '001.' AS num
UNION ALL
SELECT '004' AS num
UNION ALL
SELECT '002.' AS num)
SELECT * FROM myString
WHERE CHARINDEX('.',num,1)>0
April 7, 2015 at 8:58 am
BOR15K (4/7/2015)
I would use CHARINDEXWITH myString AS (
SELECT '001' AS num
UNION ALL
SELECT '002' AS num
UNION ALL
SELECT '003' AS num
UNION ALL
SELECT '001.' AS num
UNION ALL
SELECT '004' AS num
UNION ALL
SELECT '002.' AS num)
SELECT * FROM myString
WHERE CHARINDEX('.',num,1)>0
That will, under all circumstances result in a full scan, not much of an optimization in doing that;-), similar to a leading % with the LIKE operator.
😎
April 7, 2015 at 9:15 am
Eirikur Eiriksson (4/7/2015)
That will, under all circumstances result in a full scan, not much of an optimization in doing that;-), similar to a leading % with the LIKE operator.
😎
This depends what is the source I would think - even if there are millions of invoices, but the search is within lines of a specific invoice, with a proper range by Invoice Number I cannot think of any issue. Am I wrong?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply