SQL Server Consulting, Education, and Training
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
All this month I’m going to be giving readers (watchers? stalkers?) access to my Starting SQL video course.
Enjoy!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
A while back I blogged about how ISNUMERIC can give you some wacky results.
Working with a client… Well, it’s hard to know based on my publishing schedule. But they were using ISNUMERIC all over the place, and getting the same wacky results, and even errors.
Here’s a short setup:
SELECT
x.n
INTO #t
FROM
(
SELECT '$'AS n
UNION ALL
SELECT ','AS n
UNION ALL
SELECT '.'AS n
UNION ALL
SELECT ',1,'AS n
UNION ALL
SELECT '-'AS n
UNION ALL
SELECT '+'AS n
UNION ALL
SELECT CHAR(9)AS n
UNION ALL
SELECT CHAR(10)AS n
UNION ALL
SELECT CHAR(11)AS n
UNION ALL
SELECT CHAR(12)AS n
UNION ALL
SELECT CHAR(13)AS n
) AS x;
We’re definitely not dumping anything in that table that’s a number.
Let’s see what happens when we run this query:
SELECT
t.n,
i = ISNUMERIC(t.n),
t = TRY_CONVERT(bigint, t.n),
c = CASE
WHEN t.n NOT LIKE '%[^0-9]%'
THEN 1
ELSE 0
END
FROM #t AS t
The first column is what each of the next three columns are evaluating:
You can see ISNUMERIC stink it up every time. TRY_CONVERT mostly does okay, but I’m not sure how I feel about + and – being converted to zero.
That case expression is the only thing that seems reliable.
Of course, integers are stodgy. Stiff. Maybe we need something a little more floaty.
SELECT
t.n,
i = ISNUMERIC(t.n),
t = TRY_CONVERT(float, t.n),
c = CASE
WHEN t.n NOT LIKE '%[^0-9]%'
THEN 1
ELSE 0
END
FROM #t AS t
What happens?
Well, ISNUMERIC still stinks, but at least TRY_CONVERT does better.
No one should be using ISNUMERIC, period. Depending on your query needs, you can certainly use TRY_CONVERT in most situations, assuming you’re on SQL Server 2016+.
For everyone else, and even maybe people on 2016+, that CASE expression works really well for rooting out things that aren’t numbers.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
How you do date math in a where clause matters, because wrapping a column in any sort of expression can really hurt your query performance.
The thing is that most rewrites are pretty simple, as long as there aren’t two columns fed into it.
For example, there’s not much the optimizer could do about this:
WHERE DATEDIFF(DAY, u.CreationDate, u.LastAccessDate) > 1
But that’s okay, because if you do that you deserve exactly what you get.
Computed columns exist for a reason. Use them.
Where things get a bit easier is for simpler use cases where constant folding and expression matching can be used to flip predicates around a little bit.
It’s just a little bit of pattern recognition, which the optimizer already does to make trees and apply rules, etc.
CREATE INDEX p ON dbo.Posts(CreationDate);
There’s a huge difference between these two query plans:
SELECT
c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE DATEADD(MONTH, -1, p.CreationDate) >= '20191101'
GO
SELECT
c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE p.CreationDate >= DATEADD(MONTH, 1, '20191101');
GO
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.