The Heap™ – Consultancy ©®

McNets

8:09 AM

Morning

Andriy M

Morning

ypercubeᵀᴹ

8:57 AM

morning

Mathematics

Morning all jeeks

Mathematics

9:13 AM

why would anyone do same inner join twice, but only difference is that second inner join uses table aliases

ypercubeᵀᴹ

9:41 AM

@Mathematics is the join based on the same columns? (the ON clause)

Mathematics

9:54 AM

@ypercubeᵀᴹ yes

SELECT DISTINCT othercolumns,
                Table1Alias.columna
FROM   maintable
       INNER JOIN secondarytable
               ON maintable.id1 = secondarytable.a_id1
       INNER JOIN table1
               ON secondarytable.id2 = table1.id3
       INNER JOIN table1 Table1Alias
               ON secondarytable.id2 = Table1Alias.id3
       INNER JOIN thirdtable
               ON table1.id4 = thirdtable.id5
       INNER JOIN fourthtable
               ON thirdtable.id6 = fourthtable.id7
       INNER JOIN fivetable

Jack Douglas

@Mathematics only one of the joins is filtered with AND table1.type = '0' — is table1.id3 unique in table1?

ypercubeᵀᴹ

@Mathematics What @JackDouglas noticed and asked above. If table1 (id3) is not UNIQUE, then the double-join produces something like a cross join. The additional restriction (only in one of the two joins) means that the end result will have every row of the starting table (secondarytable here) connected to all its related rows in table1 with the restriction that at least one of them matches the WHERE condition.

I would prefer to write that (the join with the WHERE) as an EXISTS subquery.

It would have the benefit of not introducing another cross join, which may be happening with your query if table1 (type, id3) is not unique.

The DISTINCT in the query may well be there to get rid of that.

Mathematics

10:10 AM

I am trying to understand your comments, in thinking mode

ypercubeᵀᴹ

Understanding poorly written complex queries is not an easy task ;)

Mathematics

@ypercubeᵀᴹ indeed

ypercubeᵀᴹ

@Mathematics I see you posted a question, great. Please add the columns in the `SELECT`` or at least the tables appearing there. Also the tables' PKs would help as well.

Mathematics

@ypercubeᵀᴹ done just before we started discussing it here :)

1

A: What's the reason for using double innner join in this sql statement

Looking at the where clause, the row being pointed to by table1 requires the column type to = '0' and the row being pointed to by table1alias requires the column columna to = 'conn'. Perhaps there are multiple rows on table1 for the same id3?

@JackDouglas table1.id3 values are NOT unique

McNets

Is there a way that I could delete my own posts here?

ypercubeᵀᴹ

10:23 AM

@McNets in the chat? Now you can ;)

Feeds

ypercubeᵀᴹ has added McNets to the list of this room's owners.

McNets

10:43 AM

@ypercubeᵀᴹ Thank you!

→ 1 message moved to Trash

ypercubeᵀᴹ

Boom!

Feeds

Paul White has removed McNets from the list of this room's owners.

Paul White

We don't really need any more room owners at this point. Being a room owner is more about supervising a room than giving individuals the ability to move posts or see deleted messages etc.

Tom V

Only one user with the waffles hat

I'll try to get that one to get my third secret hat on this site

Tom V

11:40 AM

3

Q: Does Ubuntu 18.04 show filenames containing emojis in colour?

I've recently transferred some files made on Mac OS, and containing emojis in their filename, to my Ubuntu 16.04 LTS machine. The emojis show up in Nautilus, albeit monochromatic. Will I see them in full color if I switch to 18.04 LTS?

Nothing surprises me anymore

Andriy M

1:48 PM

I'm voting to close this question as off-topic because it has also been posted (and already answered) over at Stack Overflow. Please don't cross-post. — Andriy M 13 secs ago

Peter Vandivier

2:30 PM

@TomV has science gone too far?

Zane

@PeterVandivier yes.

Tom V

@PeterVandivier Humanity finally succeeded in implementing unicode pretty much everywhere and now we are screwing things up again by using emoticons in filenames

2

MyDocument▯▯▯▯.pdf <---- after receiving a file with emoticons in the filename through postfix or similar

Zane

3:16 PM

It really seems like a nightmare to include. If I have to pull some datafile and some buisness user names it with a GD emoji my brain will probably boil with rage.

Also any output files I generate for them will be as many poop emojis as I can fit.

Peter Vandivier

> Your engineers were so concerned with whether they 💪🏻, they never stopped to ask if they 🙅🏻‍♂️

Tom V

☝

I already failed at using emojis from windows ^^

McNets

3:37 PM

MyDocument.😌💰☢️

@TomV Use your phone

Tom V

@McNets Apparently Windows key + ; opens an emoji picker in windows

but that's what entered the text based finger pointing up instead of the emoji I wanted

McNets

❤👍🙌🐱‍👓

@TomV BTW I can't get this new hat, apparently you must change your hat after 00:00 UTC

ypercubeᵀᴹ

We are only 23 hats behind Code Review. Closing in!

Tom V

@McNets I don't think wear six hats and change hats on 6 days is linked

ypercubeᵀᴹ

I got the James Bond one

Tom V

3:48 PM

I mean you don't need to switch to a new hat everyday

I'm pretty sure I wore the same hat twice

McNets

@TomV No, it means you must wear 6 different hats on 6 different UTC days. I think there is a post on Meta about it

Tom V

@ypercubeᵀᴹ Everybody could get waffles but I'm afraid Paul will end up in the nut house if we do that

ypercubeᵀᴹ

@TomV what's the waffles trigger?

Tom V

> Post a total of 15 comments on a question and/or its answers

ypercubeᵀᴹ

ah. Weird that I haven't got that then!

Tom V

3:52 PM

@ypercubeᵀᴹ on a single question or the answers to that question

ypercubeᵀᴹ

@TomV aha! and before a certain mod moves them to chat, I guess! ;)

Tom V

Unfortunately he never sleeps

jadarnel27

@Zane glad you liked my 90's movie reference :P

McNets

@jadarnel27 Id is a PK and a single equality operator, isn't enough to get an auto-parameterized query?

jadarnel27

@McNets Sorry, I don't understand your question.

Zane

4:06 PM

@TomV I think the max comments I've ever had in a post is lik 5 so I'm way off. My comments would get pretty irrelivant in a hurry.

McNets

@jadarnel27 sqlservergeeks.com/auto-parameterization-sql-server

In the above example BusinessEntityID is the primary key of Employee table so it is guaranteed to be unique. In addition query predicate using an equality operator so at maximum there will be only one record. By considering all these, query optimizer decides it is safe to auto-parameterize this plan using a Clustered Index Seek operator

jadarnel27

@McNets I think I see what you're saying. The query does get auto-param'd (as described in that article) if I leave off the subquery. I specifically don't want it to be auto-param'd in this case, which is why I added the subquery.

McNets

@jadarnel27 ok, then I've misunderstood your comment.

jadarnel27

@McNets Gotcha. I was writing quickly, so maybe wasn't completely clear =)

ypercubeᵀᴹ

5:04 PM

This should be a separate question by the way, not an answer here. Voting to delete. — ypercubeᵀᴹ 40 secs ago

Paul Vargas

Hello, @ypercubeᵀᴹ. -- What do you think about create an index for a timestamp column?

The table is used for logs.

ypercubeᵀᴹ

@PaulVargas if there are queries that would be improved, why not?

Paul Vargas

This is for SQL Server 2014.

ypercubeᵀᴹ

Ah, I thought you were asking for Postgres.

Is it a datetime?

Because timestamp is some weird old type in SQL Server (under deprecation now I think): docs.microsoft.com/en-us/sql/t-sql/data-types/…

Paul Vargas

Yup, datetime.

ypercubeᵀᴹ

5:18 PM

OK. Well, it all depends on the queries you run and ow fast you need them to be

Besides the common secondar yindex, there is also the option of using the timestamp as the CLUSTERED key.

Paul Vargas

I was thinking about to add a integer column with the date in format yyyyMMdd and create an index.

^^^ @ypercubeᵀᴹ Do you think that that would be a good idea?

ypercubeᵀᴹ

No. Why would you do that?

How will it be better than a simple index on the (ts) column? Or an index on (CAST ts AS date)?

Zane

5:45 PM

@PaulVargas yeah I highly recommend not doing that. Always just use the appropriate date type. What you're suggesting will make any sort of date logic performed on that table a nightmare with no decernable benefit.

Changing date columns into int is a path that is full of despair and regret.

2

Paul Vargas

@Zane Thanks for the warning.

Zane

6:06 PM

No problem

lol wat?

0

Q: Shrinking MS SQL 2008 Database to move into MS SQL Express

I need to shrink a popular CRM database with SSMS to move it onto a new server. I am going from MS SQL 2008 Standard to SQL Server Express 2014. Database is in Simple Recovery Model. It needs to be under the 10 gig limit. There's 39% space to erase but shrink does not work. I've done this many ti...

shrink

"Needed to get it under 10 gigs so we deleted millions of rows." What kind of application is this?

Taryn

one where they don't care about data

McNets

@Zane 100000's, hundreds of thousands, it could be 300,000

A CRM usually holds historical information about customers (phone calls, visits, actions, ...), I suppose he/she is deleting the oldest records. Or maybe splitting it into geographical zones.

Tom V

6:59 PM

Our government... Aaaand it's gone

Zane

lol

Tom V

Took longer than I expected

ypercubeᵀᴹ

7:54 PM

You predicted it, a few days ago.

McNets

8:18 PM

@TomV not the first time, isn't it?

Lamak

@TomV what comes now?

Tom V

8:37 PM

We're used to it yes

They go into something called 'ongoing affairs' where they can't decide much until the next election

Last time we were without government we had record growth since the politicians couldn't do anything silly

2

Lamak

lol

Tom V

We pretty much get on with our lives, they bicker in front of a camera and nothing really changes

Zane

0

Q: Data Warehouse Instrumentation and Metadata - Recording Business Logic Decisions

On the back end of an ETL, the admin usually records details like the job run ID, the number of records exported to distant system, the number of records deleted from distant system, etc. The question is, what is a good way of recording the business logic decision made? For example, if Toy Co...

data-warehouse etl metadata architecture business-intelligence

/sigh been getting a log of these lately.

Tom V

Except trying to pretend they govern they can go into full campaign mode

Politicians are pretty much in campaign mode al' the time anyway but they can stop pretending now

Maybe we'll get intermediate elections but I don't think so since the planned election is next year already

They couldn't form a new coalition in time anyway

McNets

8:54 PM

Well, our politicians are jailed by now...

Tom V

The funny thing is that the politicians and media are acting like it's something important but a lot of the public thinks 'whatever'

@McNets one of your politician's should ve stayed here instead of entering Germany

McNets

@TomV yes, it's true

Tom V

Puigdemont knew the risk. He was safe here with the nationalist party in the majority

Jack Douglas

9:13 PM

theregister.co.uk/2018/12/18/sqlite_vulnerability

oops, think I need to shut down db<>fiddle's SQLite instances for the time being

2

Paul White

11:41 PM

@TomV OMG

2

Transcript for

The Heap™ – Consultancy ©®