The Heap™ – Consultancy ©®

7:31 AM

I'm going to ask a question which had I asked it in the main site it would've been closed due to some reason. but there are sites which shows binary tricks / way of solving issues for many scenarios. Now leave aside the binary stuff , is there any site that shows nice queries or tricky queries or handy queries ? I know this is very vague. but this is the reason why I supplied the binary preview. to show the general idea.

Count events in an interval of time ( as discussed earlier here) - is a sample.

good morning

good morning

7:47 AM

@RoyiNamir This sounds like you are asking about some kind of knowledge repository. Can there be such a place?

I can paste here dozens of binary tricks sites.

of course it's all sums up to knowledge repository :)

I've heard of this thing called Stack Exchange network, maybe they have what you want ;)

Problematic . :-) Ir will take 2 sec for such question to be closed.

No, I didn't mean to ask a question there, but to search it for "nice queries or tricky queries".

Why do they need to be tricky, anyway?

Or what do you mean by that?

Everything is not tricky eventually.

Eventually I guess the word is smarter

one solution is smarter than the other.

For example. Mikael solution VS the other solution ( count events in SQL)

datediff from 0 was a smarter solution

which I can use to check ANY groups value which I want

Another example is instead of table numbers , A recursive CTE can be used.

Yes I know it's sounds strange. but I guess I'm after the pearls which an experienced DBA has gathered through the years.

-

7:53 AM

Perhaps SE is not good for searching for "cool stuff" in general, but it should be good enough for searching for a solution to a specific problem.

For example : your : "With Ties" solution which was marvless !

I know it's all there in the DOCS. but again , you know what I meant :-)

(count all x who has the same min/max values)

Thank you, but I'm still not sure what exactly are you looking for. Are you trying to create your own knowledge repository or something?

No. just to go straight to the "here , let me show you something cool - which you probably don't know"

Something like that ^

SE has accumulated a lot of cool solutions over time, I'm sure. Of course, they aren't tagged cool-stuff for anyone to easily find them if the person is looking for any cool stuff. So your question may be unanswerable at the moment, unless someone has created a repository specifically for clever SQL solutions (for any problem).

Maybe someone has a blog where they do appropriately tag articles with clever pieces of code. I haven't heard of such one.

Well. I'm glad to be in this chat :-) I'm sure I can find here anything I want

8:08 AM

You can find anything (or almost anything) you want at DBA.SE or SO too, just make sure you want something specific enough :)

Andriy , I have a question please , I know about RowRevision column which can be added to a table to check if row is changed.But what if I don't want to add such column ? is there any function in sql that takes all the columns values , create HASH and then I can store that hash and check again if it was changed ?( after update)

(Without specifying all columns names).

I only know that there are functions that have to do with creating hash values. I've looked them up a couple of times but never really used them.

something like : (psuedo) : Trigger instead of update : if HASH()==HASH(inserted.) =>> REJECT

Ok

There's CHECKSUM(), which accepts a * meaning "all columns".

I don't know if it's reliable enough to use in a trigger to check for actual changes.

8:35 AM

CHECKSUM isn't quite reliable enough

you can get clashes

HASHBYTES is safer

Also mentioned in the docs
"If one of the values in the expression list changes, the checksum of the list also generally changes. However, there is a small chance that the checksum will not change. For this reason, we do not recommend using CHECKSUM to detect whether values have changed, unless your application can tolerate occasionally missing a change. Consider using HashBytes instead. "

but HASHBYTES requires a list of column names I believe

Thanks.

@MarkSinkinson I did remember that CHECKSUM was not very reliable but forgot that it said so right there in the docs. Thanks for this reminder.

But there is a workaround which does allow *:

CREATE TRIGGER get_hash_value ON staging_table
FOR UPDATE, INSERT AS
UPDATE staging_table
SET sha1_hash = (SELECT hashbytes('sha1', (SELECT col1, col2, col3 FOR XML RAW)))
GO

SELECT col1, col2, col3 FOR XML RAW can be changed to select *

From here

Maybe not so much to * as to table_name.*.

Yes.

But I didn't understand why he said but not in the trigger. Why ?

He said : as each time you run it you would be getting a different value because the sha1_hash column would be different each time. ????

Oh he assume that he has a column which contain a hash column ..... so he basically will hash a (*+ old hash)

8:52 AM

Yes

If you only want to compare the hash values, not to store them, I think * would work for you.

-

There should be such hint/switch :-)

WITH NOSTALEUPDATES

or something like that

Will save many IO's

(but will increase CPU) :P

I got String or binary data would be truncated. when trying that method FOR XML RAW

ConcernedOfTunbridgeWells

Within a trigger?

sqlnotes.info/2012/01/16/generate-md5-value-from-big-data

sys.fn_repl_hash_binary to the rescue

(only md5)

9:23 AM

@swasheck I know the answer to this - build a custom process that shreds it into something that you can ETL. That's what I had to do.

XML - now you have two problems.

ConcernedOfTunbridgeWells

9:36 AM

We flatten our XMLs into comma delimited files and then ETL that way.

These ones were insurance policy documents with a complex, configurable-at-runtime structure that shred out into dozens of entities. Unfortunately the vendor's shredder didn't actually shred out the whole document so I had to build a secondary shredder with lots of custom xpath bits and bobs to get the other bits out that we needed.

"I know. We'll use XML. What could possibly go wrong?"

2

Cue runtime config change that converts the format of an identifier from a GUID to a concatenation of two GUIDs that no longer fits into the varchar (50) column.