« first day (2252 days earlier)      last day (2633 days later) » 

Evan Carroll
Evan Carroll
5:32 AM
Is DBA.SE down for anyone else?
weird back up
 
 
3 hours later…
Jack Douglas
Jack Douglas
8:07 AM
→ 3 messages moved to Trash
→ 7 messages moved to Trash
 
 
3 hours later…
Martin Smith
Martin Smith
10:52 AM
@JoeObbish But if you wrap the one without a FROM clause in a MAX it then seems to only be evaluated once. 
WITH cte (VALUE_1) AS
(
SELECT MAX(dbo.EXPENSIVE_1(0)) t
)
SELECT n
FROM X_100 -- 100 integers from 1 - 100
INNER JOIN cte ON n >= cte.VALUE_1;
 
Paul White
Paul White
11:17 AM
@MartinSmith @JoeObbish That's effectively the same thing. Any aggregate will do, even the ANY aggregate (ha!) introduced by specifying DISTINCT on a constant value projected over 1...n rows. You can do tricks with e.g. @@DBTS or RAND(seed) as well.
Expensive scalars are just a nightmare because the cost model has no support for them.
Fun to play with though, for sure
 
Martin Smith
Martin Smith
11:46 AM
@PaulWhite I remember @@Dbts being used in your article here sqlblog.com/blogs/paul_white/archive/2012/09/05/…
 
Paul White
Paul White
Yep.
 
 
3 hours later…
Paul White
Paul White
2:26 PM
Aggregates are kinda cool for that. Remember the CASE MIN(1/0) thing?
 
Jack Douglas
Jack Douglas
3:18 PM
 
McNets
McNets
3:32 PM
Hi
 
Joe Obbish
Joe Obbish
4:30 PM
@PaulWhite I think I understand what's going on here but want to be sure. With SELECT DISTINCT dbo.EXPENSIVE_1(0) the optimizer is able to eliminate the DISTINCT because it knows there will just be one row. That allows the compute scalar operator to be deferred until the nested loop which is bad.
However, if I adding the dummy table introduces uncertainty. It could have 0 rows, so it makes sense to do the stream aggregate before the nested loop join, as the query could finish without having to do the loop join at all
and to do that stream aggregate requires the function to be evaluated
The MAX technique leads to a stream aggregate as well, which accomplishes the same thing, but IMO for a different reason
In theory, I see no reason why a future version of SQL Server couldn't reduce MAX(constant) to just constant, which would result in it not working
(I am being a bit loose/imprecise in the above in why the query optimizer does one thing or the other)
@JackDouglas Absolutely, at least in the platforms that I work with (SQL Server and Oracle)
in some cases I'd rather have single threaded read ahead reads in SQL Server than a parallel scan, for example
 
 
2 hours later…
ypercubeᵀᴹ
ypercubeᵀᴹ
6:40 PM
47
Q: Is there a field length that is too short to allow harmful SQL injection?

James JenkinsI was reading about SQL injection and saw this, which got me thinking: input fields as small as possible to reduce the likelihood of a hacker being able to squeeze SQL code into the field without it being truncated (which usually leads to a T-SQL syntax error). Source: Microsoft SQL Server ...

sql-injection
 
 
2 hours later…
Paul White
Paul White
8:17 PM
@JoeObbish That seems mostly right. I'd just add that the optimizer cannot see the constant inside the function, just that it returns one row. Also, a scalar aggregate returns NULL on an empty input, not no rows.
 
Royi Namir
Royi Namir
9:01 PM
I've been wondering , when I query SQL Server , on which format data is transmitted back ? I assume it's binary ( and not XML) but what about columns ? how do it trasmit that John and Paul and Ringo are under the "Name" column ?
does it uses an int as an index to the "Name" column?
I mean looking at JSON : 
{name:"royi", age:38} , {name:"John" , age:40"}
name ( and age ) appears here for each row
and it's waisty
I know I can put WIRESHARK , but just out of curiosity , i'm interested how it's sent back
And particular structure ?
 
Evan Carroll
Evan Carroll
9:48 PM
dba
 
McNets
McNets
9:59 PM
 
Paul White
Paul White
Tabular Data Stream
Tabular Data Stream (TDS) is an application layer protocol, used to transfer data between a database server and a client. It was initially designed and developed by Sybase Inc. for their Sybase SQL Server relational database engine in 1984, and later by Microsoft in Microsoft SQL Server. == Background == During the early development of Sybase SQL Server, the developers at Sybase perceived the lack of a commonly accepted application-level protocol to transfer data between a database server and its client. In order to encourage the use of its products, Sybase came up with a solution through the use...
 

« first day (2252 days earlier)      last day (2633 days later) »