So here, the JSON and
STRING_SPLITmethods took about 10 seconds each, while the Numbers table, CLR, and XML approaches took less than a second. Perplexed, I investigated the waits, and sure enough, the four methods on the left incurred significant
LATCH_EXwaits (about 25 seconds) not seen in the other three, and there were no other significant waits to speak of.
And since the latch waits were greater than total duration, it gave me a clue that this had to do with parallelism (this particular machine has 4 cores). So I generated test code again, changing just one line to see what would happen without parallelism:
There’s a lot going on in that post, so I recommend checking it out.
Adventureworks seems just fine to me. Only four instances of procedures calling procedures. I looked at the database I work with most. Hundreds of procedures (representing 15% of the procedures) call other procedures. On the other end of the spectrum is Stackoverflow. I understand that they don’t use stored procedures at all.
Check out the comments for more notes.
Someone posted to #sqlhelp on Twitter, asking the following: “Wondered if anyone could enlighten me as to why ISNUMERIC(‘7d8’) returns 1?”
SELECT ISNUMERIC('7d8')returns a 1.
Great answer and explanation, and his advice to use TRY_CONVERT() for 2012 and up is spot-on.
What the what?? I literally JUST ran a query exactly like this, but without the join. I haven’t mixed aggregate and non-aggregate columns in the query without a GROUP BY…the only aggregate is in the subquery, and it’s all by its little lonesome!
It’s funny what one little letter can do to you.
This works for tables, stored procedures, views etc, but there are a few things to be careful about. It doesn’t change the code behind code based objects so you need to modify that as well. And of course any time you use sp_rename you’ll get the warning:
As Kenneth notes, this does not change any underlying code, so renaming columns can potentially break code.
Now aside from the odd occasion when you actually need it for it’s simple purpose it’s a rather interesting way to get a rolling count. Basically you can use it to get a list back of 1,2,3,…n-1,0 where n is your divisor.
There are a few great use cases for modulo within SQL Server. One not mentioned is building test data. You can easily build a uniformly distributed set of randomized numeric values within a particular range using modulo math.
Essentially, the problem is that a poor estimate can be made not simply when
SYSUTCDATETIME()) appears, as Erland originally reported, but when any
datetime2expression is involved in the predicate (and perhaps only when
DATEADD()is also used). And it can go both ways – if we swap
<=, the estimate becomes the whole table, so it seems that the optimizer is looking at the
SYSDATETIME()value as a constant, and completely ignoring any operations like
DATEADD()that are performed against it.
Paul shared that the workaround is simply to use a
datetimeequivalent when calculating the date, before converting it to the proper data type. In this case, we can swap out
SYSUTCDATETIME()and change it to
I suppose switching to GETUTCDATE isn’t too much of a loss, but it looks like (according to Paul White in the second linked Connect item) this appears to have been fixed in SQL Server 2014.
The CROSS APPLY and the old-school solutions are by far the best choice for dense indexes, i.e. when the first column has a low degree of uniqueness. The old-school solution is only that fast because the optimizer short-circuits the query plan.
LEAD() and the old school strategy are best for selective indexes, i.e. when the first column is highly unique.
There’s a nice set of options available so if one doesn’t work well with your particular data set, try out some of the others and see if they work for you.
Really. Every single time. It started off kind of funny. Scalar functions in queries: no parallelism. Scalar functions in computed columns: no parallelism, even if you’re not selecting the computed column. Every time I think of a place where someone could stick a scalar function into some SQL, it ends up killing parallelism. Now it’s just sad.
This is (hopefully. HOPEFULLY.) a less common scenario, since uh… I know most of you aren’t actually using any constraints. So there’s that! Developer laziness might be a saving grace here. But if you read the title, you know what’s coming. Here’s a quick example.
Yeah, UDFs in check constraints is a pretty bad idea most of the time.
But why? I mean the table gets created either way and since the default is dbothere is no real reason to name it.
Actually no. The default is not in fact dbo. It frequently is dbo but by no means always. The default schema is part of your USER information. Specifically theDEFAULT_SCHEMA option. Well, unless you are a sysadmin. Then it actually does always default to dbo.
Schemas are a very powerful grouping mechanism, and they’ve been around long enough that if you aren’t taking full advantage of them, you really should.