The Heap™ – Consultancy ©® - 2013-06-03 (page 2 of 2)

« first day (881 days earlier) ← previous day next day → last day (4285 days later) »

Zane

21:22

What could possible be the point of select Distinct blah, blah1 blah2 from (Select Distinct blah, blah1, blah2, from (select distinct Longer query)))

This query litterally takes the same 4 values and distinct's them all 4 times...

bluefeet

@Zane someone didn't understand sql

Zane

Apparently he though DISTINCT = good

billinkc

They want to make sure the values are really distinct. You just don't understand. Sometimes, MS SQL Server gives the wrong answers. I've had very senior DBAs look at the queries and tell me so

Zane

Technically speaking what he is doing is even more silly now that I've looked harder.

Select Distinct a.blah, a.blah1, a.blah2 from ( select Blah, Blah1, Blah2 from foo) Left Join Nopoint as B On a.blah = B.blah

If you are going to left join to something grab no columns and then distinct the results what was the damn point....

FreshPrinceOfSO

Well you see @Zane, the point is horses.

Zane

21:37

@FreshPrinceOfSO you dump those babies into a temp table and then use a cursor to go through them one at a time and you've got yourself a query.

Aaron Bertrand

Don't forget to use NOLOCK or performance will suck

FreshPrinceOfSO

How about an XML path?

Zane

@AaronBertrand there is nothing in this database that doesn't use NOLOCK...

Aaron Bertrand

Perfect, should never be any performance problems then

Simon Righarts

@billinkc Funny, though, when you ask for a repro they can't find one but they're sure they had one a long time ago in a galaxy far far away

ypercube

21:40

@AaronBertrand We had a question, with a few NOLOCK, while you were in the cold.

Q: Splitting SQL query with many joins into smaller ones helps?

We need to do some reporting every night on our SQL Server 2008 R2. Calculating the reports takes several hours. In order to shorten the time we precalculate a table. This table is created based on JOINining 12 quite big (tens of milions row) tables. The calculation of this aggregation table to...

Zane

@AaronBertrand exactly. It's like magic.

Simon Righarts

@billinkc I improved a developer's query performance 1000% (10s to 1s) by rewriting it to avoid DISTINCT and re-arranging the joins so it did index lookups instead of a table scan (gak)

Aaron Bertrand

Uh, what the heck is WITH ROWCOMMITTED?

A word of warning - WITH (NOLOCK) Is evil - can result in bad data coming back. I suggest trying WITH (ROWCOMMITTED). — TomTom yesterday

Mark Storey-Smith

@AaronBertrand 'orrible (Oracle)

Simon Righarts

It's black magic I tell ya, sp_helpconstraints and SET SHOWPLAN ON are evil, dark, DBA-only tools

Aaron Bertrand

21:42

I already had a "discussion" with TomTom today so if anyone else wants to comment on that please do

Mark Storey-Smith

Which IIRC is where TomTom usually pops up

Simon Righarts

only to be used in the blackest of nights after sacrificing a goat. Or something

I honestly think that some developers think that

Mark Storey-Smith

I honestly think some developers think

Some

Simon Righarts

@MarkStorey-Smith Well, blindly parroting what you've been told doesn't really qualify as thinking in my book ;)

SQLFox

Speaking of that question--is it really appropriate to start your answer with "Large tables? YOUR data is small. MY data is big."?

Mark Storey-Smith

21:44

@SQLFox "Mine's bigger than yours" talk basically

Simon Righarts

@SQLFox Probably not. Saying "your data is actually relatively small" is fine, but byte-dickwaving isn't really constructive

Mark Storey-Smith

@SimonRigharts Put the original back! That's a new heap catchphrase if ever I saw one :)

Simon Righarts

@MarkStorey-Smith Done. I was like "um should I say that?"

@MarkStorey-Smith Best hope google doesn't start trawling chat.se, if it does I could be in a small spot of bother

Mark Storey-Smith

@SimonRigharts It's a phrase I'll be using frequently from this day forth and it's only right you should be credited appropriately

Simon Righarts

@MarkStorey-Smith Haha. I was trying to find a succinct way to phrase it

Aaron Bertrand

21:50

This doesn't make any sense. This works fine: DECLARE @d DATETIME2(7) = '2012-04-05T12:34:56.7777'; SELECT CONVERT(DATETIME, @d); - so I suspect your underlying value is actually not a DATETIME2 type, or gets converted somewhere along the way through a query, view, etc. Since there is no reason for SQL Server to convert the value to and from a string during this process. Can you post a repro on SQLFiddle? — Aaron Bertrand 6 secs ago

Can't stand self-answers that actually can't possibly be the real answer to the question. The answer is probably "my bad; the column was actually varchar and had bad data in it."

PS that is this guy. Fun fun.

-2

Q: Is converting everything to varbinary the correct way to compare character data?

I was using a MIN function to compare character data in a column that allowed nulls, with disastrous results. ;-) Here's a much simplified example that shows the same kind of thing: Determine the number of rows in sys.indexes: select count(*) from sys.indexes; Run this SQL: select count(nam...

sql-server collation coalesce

Zane

@AaronBertrand he probably has a date of "Ted" or something.

select count(coalesce(name,char(0x7e))),
substring(cast(min(coalesce(name,char(0x7e))) as varbinary),1,1)
from sys.indexes;

Best query ever.

Simon Righarts

22:15

@MarkStorey-Smith I prefer "A Heap of sarcasm", myself ;)

Mark Storey-Smith

@SimonRigharts Well, we haven't had a change of room title since (I think) @PaulWhite popped in and prompted the change from TheClusteredIndex. Change is good as a something or other so I'm told...

Aaron Bertrand

How about the partitioned writable columnstore

Mark Storey-Smith

@AaronBertrand Fantasy talk