The Heap™ – Consultancy ©®

Andriy M

04:57

@PaulWhite Too much honour, you mean? Yes, it is. :)

But seriously, you are expanding on a point I completely missed.

Paul White

I'll leave it as it is then. Cool.

15

A: Best way to write SQL Query that checks a column for non-NULL value or NULL

This pattern column = @argument OR (@argument IS NULL AND column IS NULL) can be replaced with EXISTS (SELECT column INTERSECT SELECT @argument) This will let you match a NULL with a NULL and will allow the engine to use an index on column efficiently. For an excellent in-depth analysis of ...

Colin 't Hart

08:42

G'day

Tom V

goeiemiddag

ConcernedOfTunbridgeWells

09:24

Morning all

@swasheck You're confusing it with the language people use to describe it.

2

dezso

12:21

afternoon

can one somehow figure out how accepts suggested edits?

this user adds tags, without (nearly) ever fixing anything else:

oNare, Santo Domingo, Dominican Republic

1.6k 1 2 20

Tom V

I've seen him before :)

he's after rep before anything else

many answers every day, a lot of them 0 score, very late, ...

ypercube

@dezso needs some advice - or flogging

Paul White

Badge hunting perhaps. Not a bad thing where the activity adds value, but I agree his contributions have been highly variable. I blame the reviewers ;)

Actually, quite often the author of the question accepts the edit.

dezso

that's also true

I gave advice in one of my rejection comments

I can imagine he didn't read it

Tom V

he's a student, probably doesn't have anything to do during vacation

:)

dezso

12:33

@TomV too much coffee? (see chat.stackexchange.com/transcript/message/22960288#22960288)

Tom V

@dezso that, and working with a PFE at the moment

dezso

@TomV Pfizer@NYSE?

dezso

12:56

kick this back to SO, please

0

Q: postgres could not connect to server: No such file or directory in ubuntu 15.04

when I run postgres server I am getting the following error psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"? I am using kubuntu latest version 15.04 and using pos...

postgresql

you have to start the database in order to connect to it

surprise

and my take on oNare's poor edit: dba.stackexchange.com/posts/107990/revisions

Tom V

@dezso premier field engineer

dezso

13:11

ah, it is something like a fireman, right?

Tom V

13:21

he's the thing you sometimes need to calm your customer down and give them a cozy feeling that their problem has been escalated to the top

dezso

but apparently it doesn't calm you down, right?

Tom V

the fact that he calms the customer down allows me to work on the problem at hand

which is solved by now

dezso

ah

that it sounds a win-win

Tom V

it is

it's a whole different level of quality than their support engineers

so the comment was more "working with a PFE" = "not really a laidback friday"

Marian

13:42

@TomV I know one, too

same opinion as yours, a brilliant gal

Colin 't Hart

14:19

@AaronBertrand I don't understand some of the syntax in your answer dba.stackexchange.com/a/108013/10361

1. Why do you have "column = func(column)" in the select clause?

In PostgreSQL that would return a boolean...

2. Why (and I've seen this elsewhere before) do you have a semicolon before the with?

bluefeet

@Colin'tHart the "column = ..." is a way to give an alias in SQL Server

Marian

@Colin'tHart CTEs have this funny syntax requirement

Colin 't Hart

WTF+

That's so silly...

Both requirements, but I was primarily reacting to the "a = expr" syntax

Marian

"with" keyword has multiple uses (hints, CTEs), this one helps differentiate for the CTE

@Colin'tHart it's Aaron's prefered way of aliasing columns

Colin 't Hart

I think it's a braindead design.

You can have two expressions in one query -- one in the select clause and one in the where clause -- having entirely different semantics.

Paul White

14:26

@Colin'tHart WITH requires the previous statement be terminated properly. Putting a semicolon before the WITH guards against that not being the case. Ok I suppose for demo code, not something I would do in production code.

Colin 't Hart

@PaulWhite Aha, That makes more sense to me.

Paul White

alias = expr reads nicer to me, you don't have to scan right to the ragged edge to see the exposed column name. By all means use expr AS alias if you prefer. Style thing.

Jon

anyone have any recommendations for SQL Server Training? Looking at the Microsoft training offered here: microsoft.com/learning/en-us/sql-training.aspx and wondering about the quality?

ypercube

@Marian It's not a requirement of CTEs really. But it may give syntax errors if the previous statement is not terminated with ; because of the similar WITH NOLOCK hints/syntax

Aaron Bertrand

@Colin'tHart Yes, exactly as Paul suggests (I even blog about it). I don't write code for PostgreSQL and don't really place portability above all else - I'll use ANSI standard when it isn't more convenient to do otherwise. I blogged about this here:

sqlblog.com/blogs/aaron_bertrand/archive/2012/01/23/…

@PaulWhite I do that even in production, to avoid someone pasting in some query or statement before the CTE and then complaining when it breaks

Marian

14:34

@ypercube indeed, lazy wording, it's as Paul said, previous statement needs to be terminated correctly with a ;

Paul White

@AaronBertrand Yes, fair enough.

I'm not that nice :)

Aaron Bertrand

@ypercube And you can never have too many semi-colons. This is valid: SELECT 1;;;;;;;;;;;SELECT 2;;;;SELECT 3;;;;;;;;

swasheck

looking at that makes my : hurt

Colin 't Hart

@AaronBertrand I will agree that it is nice to have the column aliases all lined up and visible -- not on the right. But I still don't like having what looks like expressions in the select clause. I take it SQL Server doesn't support booleans in the select clause like PostgreSQL does, where I can do select col1 = col2 as matches from ...

ypercube

@Colin'tHart SQL-Server does not even have a boolean type

Colin 't Hart

14:41

@ypercube Oh, poor things!

Aaron Bertrand

@Colin'tHart As Paul said, it's a style thing, but I have an objective reason for it. That specific concern is not a concern in SQL Server.

Colin 't Hart

I really don't understand DBMSes that don't have a boolean type. What are where clauses operating on?

Aaron Bertrand

@Colin'tHart I'll admit that not all of my conventions are popular, but I do have good, objective reasons for them.

Colin 't Hart

@AaronBertrand I agree completely with your arguments for using it.

Aaron Bertrand

@Colin'tHart SQL Server has a BIT type. And there are ways to use Boolean expressions for filters, joins, etc. You don't need a boolean type to perform boolean logic.

Just like you don't need a JSON type to store JSON.

Colin 't Hart

14:43

Especially writing code that is optimised (for want of a better word) for your particular DBMS of choice.

@AaronBertrand Still, it makes it a second class citizen.

Aaron Bertrand

@Colin'tHart I have been working with SQL Server for almost 20 years, and not once have I thought "Man, I wish we had a boolean type!" never mind "Wow, SQL Server sucks because it doesn't have a boolean type!"

Does PostgreSQL support parallelism yet?

ypercube

No. Not yet.

And it sucks.

dezso

i hear there is some wirk being done on it

Aaron Bertrand

Every DBMS has its strengths and weaknesses. I don't think you can get very far suggesting SQL Server is a second class citizen because it doesn't have a boolean type.

Colin 't Hart

@AaronBertrand I did think that when working with Oracle.

ypercube

14:46

@AaronBertrand I read it as "booleans are second class citizens in SQL-Server"

Colin 't Hart

@AaronBertrand I said that the boolean or whatever you use to emulate it was a second-class citizen.

Aaron Bertrand

@Colin'tHart ah, sorry, that was ambiguous at best :-)

Colin 't Hart

@AaronBertrand Strongly agree with the first sentence: every DBMS has its strengths and weaknesses.

I guess I'm spoiled now being used to PostgreSQL's rich types.

And we're getting parallelism soon :-D

ypercube

@Colin'tHart Yeah, like in a year or 2.

Colin 't Hart

Sorry to have to drop off just as the discussion is getting interesting, but it's weekend here. Cheers!

swasheck

15:13

@Colin'tHart postgres relies way to heavily on the OS (see: collation). that's both a strength and a weakness. i love postgres. make money with sql server.

dezso

@swasheck you mean spend money, right?

swasheck

@dezso i meant to say "i make money with sql server." it's what i do on a daily basis.

dezso

I see

swasheck

pitting RDBMS' against each other is stupid

mmarie

18:50

swasheck

sleep now in the fire

Jack Douglas

19:23

@swasheck unless one of them is MySQL

just kidding

@Colin'tHart presumably you used char(1) for years when working with Oracle?

swasheck

@JackDouglas it's very kind of you to consider MySQL an RDBMS

Jack Douglas

ha

poor old MySQL

hmmm, this looks a bit suss: db-engines.com/en/ranking

nice we get a plug though "We use the number of related questions and the number of interested users on the well-known IT-related Q&A sites Stack Overflow and DBA Stack Exchange. "

swasheck

> Number of mentions of the system on websites, measured as number of results in search engines queries. At the moment, we use Google and Bing for this measurement. In order to count only relevant results, we are searching for <system name> together with the term database, e.g. "Oracle" and "database".

@JackDouglas sounds like a legit metric ... thhhhhpppppttttt

Aaron Bertrand

So if more people complain more often about MySQL, that means it's ranked higher?

I agree, what a stupid waste of bytes

swasheck

@AaronBertrand yeah. so level of complexity increases rank. also, level of incompetence increases rank.

Jack Douglas

19:32

Trying to work out their angle. It's produced by a NoSQL consultancy

I'd guess they choose what to measure so MongoDB looks hot

seeing as that's the one climbing up the rankings

swasheck

what happens when you have devs noodling around in production? your sys.dm_db_missing_index_details reads like a comic book

Vérace

db-engines.com/en/ranking_definition - pretty lame. Those rankings are based on popularity - which puts one of the worst (technically) rdbms's out there (MySQL) - whereas a vastly superior one (Firebird) is way down the list. DB.SE does get a mention FWIW.

Jack Douglas

@Vérace I don't get why Oracle is top, it's not exactly trending on Twitter

swasheck

@JackDouglas other "metrics"

it sounds like an extremely complicated opinion poll

which reminds me ... they should read this houstonpress.com/arts/…

Jack Douglas

@swasheck not google search: 17M v 47M

ypercube

19:39

@JackDouglas Could be because some mysql docs and webpages are under oracle.com ?

Jack Douglas

dunno

@swasheck "Everyone knows water expands when it freezes. Do you know why it does that when literally nothing else in the world does? Nope, and neither does science" er, I think that might just be his opinion :p

2

A: Density of Solid States of Compounds

Start of an answer... hoping someone else will edit / comment / improve. The reason that water expands on freezing is that the crystalline state has a specific orientation of the molecules (through hydrogen bonds) that leaves a lot of space between them. So where most of the time the liquid is a...

"literally nothing else in the world does" except silicon, bismuth, antimony and gallium (and germanium)

swasheck

@JackDouglas yeah. i took issue with that because ... it's wrong. but then he discusses it a bit further down as due to the molecular shape.

@JackDouglas gemanium. isnt that the flower that gertie gave to ET? :)

Jack Douglas

@swasheck you mean where he says "Water expands when it freezes because of the shape of the molecule"?

that's just bonkers

what molecule is he talking about?

I think he might have meant to write "because of the structure of the crystal lattice"

but a crystal ain't a molecule

swasheck

@JackDouglas or something like "because of how the molecular composition of H2O affects is polar charging, thereby forcing the molecules into a crystal lattice such that it occupies more area"

Jack Douglas

I agree with his basic message - just because it's you 'opinion' doesn't stop it being wrong :)

his facts are very shaky though

swasheck

19:50

yeah. thereby proving his own point ;)

very complex trolling on his part

Jack Douglas

@swasheck ha ha yes

the very best trolling is when you don't realise you are doing it

I'm looking at you Mr Trump

swasheck

i think he knows

Jack Douglas

Clinton then?

swasheck

all of them

Jack Douglas

is there no innocence anywhere in politics any more

ah well

ypercube

20:18

oh oh: krebsonsecurity.com/2015/07/…

Vérace

20:34

@JackDouglas I didn't realise that MySQL was trending either.

Kin

21:39

Can someone help with this question (dba.stackexchange.com/questions/108060/…) . I think that its a dupe .. and the maked as dupe already has an answer.

ypercube

@Kin so what's the problem?

Kin

OP is arguing that its not a dupe .. but @PaulWhite has provided an explanation that answers the question

ypercube

You want to undo the "mark as duplicate"?

Kin

I still feel its a dupe .. just want someone to look at it if its a true dupe ..

Aaron Bertrand

Yes, it is a dupe

Same problem, same resolution, the source columns have to be exact matches for type/length

Kin

21:46

thanks @AaronBertrand .. I wanted to have some agree .. (just dont want to close questions as dupe since I can) .. thanks again !

Aaron Bertrand

@ypercube sorry, my initial reply was not what I intended to end up with

@ypercube the "issue" is he had to add a space to the first column in order for the data lengths to match, and believes this is caused by something other than a data length issue... — Aaron Bertrand ♦ 3 mins ago

HEY ROLANDO!!!!!!

RolandoMySQLDBA

Hey @AaronBertrand

Aaron Bertrand

Gotta go hit some volleyballs, ciao folks

ypercube

hi Rolando!

RolandoMySQLDBA

Hey @ypercube.

Gotta quick question.

Anyone knows a SQL Server DBA looking for a job in NYC ?

ypercube

21:54

@AaronBertrand Yes, thnx, deleted my comment.

RolandoMySQLDBA

If you do, send me email. I gotta run.

Have a good weekend, everyone

ypercube

... and Rolando rolls away ...

Kin

Have a good weekend all ..

ypercube

23:17

I fail to see the reason for the bounty

0

Q: Can I combine the results from multiple columns into a single column without UNION?

I have a table with several columns which I want to SELECT: SELECT his_name , her_name, other_name FROM foo; Bu, I instead I want to combine the results all into a single column. As an example, I can do this with UNION ALL as SELECT her_name AS name FROM foo UNION ALL SELECT his_name AS name...

mysql sql

Paul White

23:58

@ypercube oNare is on a badge mission.

Transcript for

The Heap™ – Consultancy ©®