The Heap™ – Consultancy ©®

It also works with ROW_NUMBER() but I think it may give different results with different data

Depends if there are duplicate data in the table.

@ypercubeᵀᴹ I had that experience once where I used ROW_NUMBER() and then later it didn't work until I switched to DENSE_RANK(). That probably explains it.

@ypercubeᵀᴹ I'm proud of my "PREORDER BY", it's childish and immature but damnit I think it's awesome. :)

@ypercubeᵀᴹ I'd be really curious to see the execution plan on my answer to know if it is satisfied by a single sequential scan like Erwin's answer.

Would SQL Server do a single scan on this query?

The 2 dense_rank require different order

Also:

WITH cte AS (....)
SELECT ... FROM cte ;

and

SELECT ... FROM (...) AS cte ;

have different execution plans in Postgres. CTEs are materialized always

(except in very rare cases, like if they are not referenced anywhere later in the query)

12:40 AM

@ypercubeᵀᴹ, are you interested in adding a little bit to your answer here? dba.stackexchange.com/questions/166621/…

he mentioned using MERGE but MS says it's a bad idea

"Performance Tip: The conditional behavior described for the MERGE statement works best when the two tables have a complex mixture of matching characteristics. For example, inserting a row if it does not exist, or updating the row if it does match. When simply updating one table based on the rows of another table, improved performance and scalability can be achieved with basic INSERT, UPDATE, and DELETE statements."

source: msdn.microsoft.com/en-us/library/bb510625.aspx

1:21 AM

@ypercubeᵀᴹ Hmmm, I don't know... what I found was that SQL Server performed quite well using this technique on a table that had no indexes. I dimly recall seeing a single scan, but don't trust that memory.

@ypercubeᵀᴹ Do you think it's worth rewriting my query to avoid the CTE, then?

Cade Roux

2:25 AM

Hi peeps. I know I'm bad about being on SO. Anyone have any opinions on when Google Cloud Spanner would be a better choice than Azure SQL?

Looking for basic hosting for typical relational data that can be scaled quickly with web/mobile access. Lots of users, limited rows and tables.

Read-heavy. It's a mobile app for viewing schedule information and choosing events from a fixed inventory of shows on particular stages to personal schedules.

Cade Roux

2:38 AM

Normally, in the past, I would have spun up an AWS with SQL Server, but now I'm thinking pure cloud is probably something for this case where the DB is not going to have many of the special things I'm used to in SQL Server like Agent, SSIS etc.

3:16 AM

0

Q: Comments and psql?

Is there anyway at all, ever to change the behavior of comments in psql. Take the query below. Execute it. CREATE TABLE foo AS SELECT x AS id, -- x AS id2, x AS id3 FROM generate_series(1,50) AS x; Run that in psql. Then run \e. Now at least, for me what I see in my editor is the l...

yuckkk

this has to be a bug

I wonder if the people that downvote my self-answered questions do so because they legitimate assess them to be sub-par contributions to DBA.SE, or because they view them to be self-promotion or something.

3:36 AM

@ypercubeᵀᴹ Yes, a single scan, multiple sorts though. cc: @ErikE

@ypercubeᵀᴹ Is it just CTEs that are an optimization fence? Rewriting as a subquery/derived table would not constitute a fence in Postgres?

3:53 AM

@PaulWhite Thanks for the info!

@EvanCarroll No one has downvoted you on this question...

MDCCL

@PaulWhite Ha ha, I thought that this suggested edit was adding the extra or unnecessary indentations instead of actually removing them. Glad you accepted it. Cheers.

@MDCCL Ah thanks - I wondered why you had voted to reject it :)

Q: Where can I find a list of the extensions for PostgreSQL?

4:34 AM

@ErikE unrelated to that, i can see how that was confusing. Was thinking of this one though.

-3

Just wondering if there is an official list for extensions that are available for PostgreSQL?

postgresql postgresql-extensions

Questions asking for a list of things are often poorly received/closed.

Who knows why people vote the way they do though.

eh, asking well-defined questions about CORE RDBMS seems very on topic to me. "list" is just because that's the form the answer takes. It could be implicit. "Where do I find what extensions PostgreSQL ships with."

but sure, why people vote the way the do was the question behind the gripe

I assume it's because it was self-answered.

4:56 AM

@Evan When you say "PostgreSQL does not support 16,000+ columns on a table..." did you mean to type 1600?

No, it certainly doesn't support 16,000+. However, it probably doesn't support 1,600 either.

@EvanCarroll Well the link there says "Maximum Columns per Table 250 - 1600 depending on column types" so that's why I'm asking.

However, if you want to change it and be more specific feel free.

No I'm not that bothered. Just wondered.

nah, I have no idea how many of his column it supports, I probably could have been more specific I remember he was at 16,000 columns or something

4:59 AM

Yeah 16,382

5:32 AM

I updated the answer to clear it up

you ever see intarray @@ query_int

that's kind of cool

SELECT ARRAY[1,2,3] @@ '(7|(1&2&!9))';

6:00 AM

Ok!

Now to play with window functions

6:38 AM

damn I regret upvoting his answer dba.stackexchange.com/q/156372/2639

7:25 AM

@ErikE you could have both versions in your answer.

@PaulWhite Right, no. Only CTEs are optimization fence.

8:12 AM

Good morning

8:25 AM

@ErikE I really like your dense_rank answer. dbfiddle will give you the execution plan: dbfiddle.uk/…

Hallo everyone.

hrmm.

@ErikE it's a similar plan to the other solutions, but much more elegant imo. Erwin's function avoids the sorts entirely, you can't 'beat' that — though I'd imagine your solution would actually be faster.

I'm researching this now.

Fyi @JackDouglas =)

@EvanCarroll cool, I need to add plpgsql to my 8.4 instance :)

I think this will probably keep me up for another 2 hours. =(

8:35 AM

morning

Q: Did Malala Yousafzai ever say "socialism is the only answer"?

Hallo @AndyK

@AndyK, @McNets is better at waking up.

He beat you.

-5

I just saw this picture making the latest rounds today on Reddit’s /r/socialism. The picture has a quote attributed to Malala Yousafzai. The caption reads, I am convinced socialism is the only answer and I urge all comrades to take this struggle to a victorious conclusion. ...

quotes

@Evan McNets probably must not be sleeping at all ;)

Azure Window collapses

saw that

RIP nature thingy

nothing a couple of white boy scouts couldn't have accomplished on a winter vacation

nbcnews.com/news/us-news/…

Most epic excuse ever though.

After boasting online of the incident, the men told NBC News they had good intentions when they knocked the rock over, saying they were preventing it from hurting anyone.

8:53 AM

@EvanCarroll dbfiddle.uk/…

@ErikE ^^^^ I think Erwin wins on performance, the procedural overhead can't be as much as I thought

but your solution is still the most elegant :)

Thanks God, I didn't watch the match.

@ypercubeᵀᴹ that confused my brain; given there have been two major cloud problems recently I assumed you were talking about a third!

@ypercubeᵀᴹ where is @gbn in these sad times

@JackDouglas Certainly not my intention ;)

Evan ruined it ...

Did wha?

9:02 AM

my evil joke/misinformation

Bro Safari - Scumbag [FREE DL]

►

^ warning explicit and stuff.

@JackDouglas I wonder if the procedural method wins in a 9.6 with parallel workers.

9:22 AM

@EvanCarroll you can try parallel out: dbfiddle.uk/…

@EvanCarroll here's one I made earlier: dbfiddle.uk/…

I doubt that ordered window functions are parallel-enabled though.

I don't think they are

I was looking for docs on that.

@JackDouglas In which product? ;)

3

bwajajaja

Sorry I could not resist.

You got a star. Well played.

9:30 AM

@PaulWhite ha, the one that has only just gained rudimentary parallel querying

@PaulWhite is it even possible to parallelise ordered window functions (unless there is a partition clause)?

@JackDouglas I'm following the dev list. They are adding lots of parallel planning, for the next release.

@JackDouglas In which product? ;)

@PaulWhite greedy. No more stars for you ...

Dodi82

Hi guys

Just think PostgreSQL will within 6 months have parallel BRIN indexes, before SQL Server gets the first hint of them. =)

9:32 AM

Ha ha ha ha. I'm starting to see the silliness correlation.

@ypercubeᵀᴹ Worth a shot!

@PaulWhite that question was for the not-real dbms by the way.

The one you know the ins and outs so well

Objectively so.

And yeah I know I'm just in the mood to be silly rather than answering actual questions.

... =) Wait until I win the moderator election and I ban you all from this room.

/revolution complete

@EvanCarroll use case for BRIN? they are very-much a "not for general use" type of index

@EvanCarroll It would be well-deserved, no doubt.

Dodi82

9:36 AM

Can I ask a question?

@PaulWhite The winning or the banning?

@AndriyM yes

2016.foss4g-na.org/sites/default/files/slides/…

@Dodi82 You've already asked one. Go on, ask another.

Dodi82

Its is about ssrs dates parameters

I am made two parameter (Year - Month) in order to show reports for sales based on these parameters. I made them dynamic by specifying values to them (year: 1995-1996-1997) and (Month: Jan to Dec). Then used filters in the Tablix to connect datafields to the parameters. Every time I choose the year with the month I get an error in my report!

Error Type: Cannot compare data type systems.string and system.Object[].please check the data type returned by the filter expression for dates

9:38 AM

@JackDouglas Thank you.

@AndriyM The banning! (but Jack answered better)

@PaulWhite Thank you too.

@JackDouglas ^ Parallel batch mode window aggregate with no PARTITION BY

@EvanCarroll GIS is niche (good niche, but still niche). From the conclusion: "GiST = 20x faster than BRIN".

It's like big data is niche. If you're working with tables with indexes that don't fit in ram, it's no longer niche it's the only option.

GIST indexes are gigantic.

9:42 AM

@PaulWhite clever — the 'gather' bit must be smart enough to use the high/low values from each parallel 'bit'

@EvanCarroll yup, brin indexes are teeny weeny. they would work a whole lot better if there was a sensible way to achieve clustering on postgres iirc

@JackDouglas Actually the gather is dumb. The magic is all in lock-free shared structures.

Similar to the way modern parallel batch hash ops share a single hash table.

Greek to me I'm afraid :)

You're thinking of @ypercubeᵀᴹ

Does he identify as Greek?

Well that's a good question. I should hate to offend anyone.

9:47 AM

Do other Greeks identify @ypercubeᵀᴹ as Greek?

That's deep.

yesterday, by Lamak

silliness is back!

:p

I should connect to a Paradox db, do you know if VStudio still support it?

Anything can connect to Paradox. Also, nothing can connect to Paradox.

3

@PaulWhite good point

I should recover my old C++ Builder

9:56 AM

@PaulWhite I've seen exactly that in postgres-dev list (parallel hash join)

@ypercubeᵀᴹ Old SQL Server used to parallelize hash using a separate hash table per thread, either distributing rows between threads, or broadcasting all rows to all threads. New parallel hash uses a single shared hash table that all threads cooperate to build (using lock-free CPU primitives) and probe.

And batch mode means 64-900 rows are processed at once, instead of strictly iteratively (row-by-row per operator).

10:21 AM

@EvanCarroll yes, dbfiddle.uk/…

@EvanCarroll have a look at this though: dbfiddle.uk/…

BRIN is useless unless the clustering on the underlying table correlates with the index order

10:37 AM

Trying very hard to refrain from commenting. Because having an easily accessible and updated model of your database is not useful ...

@EvanCarroll I thought BRIN didn't update it's bounds downwards when the underlying data changed — turns out that is wrong: dbfiddle.uk/…

11:25 AM

@ypercubeᵀᴹ work related?

@dezso yeah

1:37 PM

23ºC / 74ºF winter has gone?

@McNets whre's that? here definitely not :)

Close to Barcelone

@McNets please send some of that to Berlin

the weather is stuck between winter and spring

@dezso my pleasure, I don't like hot temp.

1:52 PM

23°C is hot?

well, that means that we will reach more than 30º sonner

sooner

hot2use

@McNets sunner :-)

which would be the mix of: sooner - sun - summer

2:21 PM

@hot2use good point ;)

3:28 PM

Today marks two years since I started working with SQL Server :)
I quickly found this site, and I’m glad I did, since I didn’t have anyone to teach me (though there was a friend who advised me to add NOLOCK to everything).
I learned more than just answers from folks here – I learned how to think, analyze, and investigate. I have a rewarding career now, my life is tangibly better, and it’s because of people like y’all. Thank you!

9

same here @Forrest

@Forrest Don't you mean "former friend"?

@Forrest I thought you were just a witty guy who was fun to be round, but you are also so appreciative. That's doubly fun.

Hmm, that looked a bit patronising. I didn't mean it! I meant that in the most appreciative way too.

3:44 PM

@JoeObbish Ha! No, I just make sure to investigate anything he tells me now :)

friends don't let friends NOLOCK!

8

@AndriyM Thanks :)

I have a table ACTIVITY_RECORDS with id (BIGINT UNIQUE), start_date (DATETIME) and activity (BIGINT)

I have another table USERS with id (BIGINT UNIQUE), name (VARCHAR)

I want to INSERT IGNORE in ACTIVITY_RECORDS using a name

I want to obtain the id from the USERS table using the name then use that id to insert a record into the ACTIVITY_RECORDS table

where do I start?

4:07 PM

Hello !

got a quick sqlserver question for yall

what happens when you fill a sqlserver database rows file

what is a "sqlserver database rows file"?

@YashasSamaga - you may want to look at msdn.microsoft.com/en-us/library/ms365303.aspx

@MaxVernon I need it for php

tell me the keywords I will need to learn about

and I will figure out how to write the query myself

I don't know where to start

unfortunately, php is not my area of expertise.

perhaps ask the question on Stack Overflow

well, SQL query is query

irrespective of whether I am using PHP or not

So you are are asking about how to write a query?

4:22 PM

@MaxVernon the .mdf file

@A_V so what is your question? Are you wondering what happens if the file is completely full?

@MaxVernon I have been writing SQL queries for 6 years but I never got any training. I just know the basics. UPDATE, SELECT, INSERT, DELETE, etc.

Q: What happens when a file size limit is reached?

0

So I have a database for which I have configured the ROWS (.mdf) database file with autogrowth ON and set to max size to 10Gb. After a while, I get an alert that my tempdb log has filled a drive and I figure that my database's size is stuck on the limit at 9990mb or something, unable to extend a...

sql-server sql-server-2008-r2

I currently have two queries but I wish to combine them

my .mdf file got full

at the same time my tempdb grew like crazy

4:23 PM

Query 1: SELECT id FROM users WHERE name='blabla'

Query 2: INSERT IGNORE INTO activity_records(id, start_date, activity) VALUES(the_id_i_got_from_query1, CURDATE(), activity_number)

I want to combine them

perhaps you want INSERT INTO activity_records SELECT id, CURDATE(), activity_number FROM USERS WHERE name = 'blabla';

that looks really weird

@YashasSamaga Yes but it is correct.

SELECT id, CURDATE(), activity_number

I am having confusion there

I have never seen such a SELECT before

Look for: INSERT .. SELECT

(although we don't know where that activity_number came from. It's not in the table columns)

4:29 PM

it is a number

@ypercubeᵀᴹ yep

I tried to avoid using some random number :p

replace it with the number, and the query should work

yea I know; I thought it giving a name instead of using specifiers would be nicer

the actual query has %d and %e stuff

bye folks

4:33 PM

@AndyK Bye

4:47 PM

^^ how to increase trust in your monitoring

that's a quite pertinent graph

Picasso monitoring software?

2

@Forrest I am unable to comment on that

@dezso it seems my network infrastructure.

Commuting, bye

6:50 PM

SET LANGUAGE FRENCH;

SELECT
   *,
   Problem = CASE WHEN Month(d.DateValue) = 3 THEN '' ELSE 'X' END
FROM
   (
      SELECT 'yyyyMMdd HH:mm:ss.fff', Convert(datetime, '20170307 23:22:21.020')
      UNION ALL SELECT 'yyyy-MM-dd HH:mm:ss.fff', Convert(datetime, '2017-03-07 23:22:21.020')
      UNION ALL SELECT 'yyyy-MM-ddTHH:mm:ss.fff', Convert(datetime, '2017-03-07T23:22:21.020')
      UNION ALL SELECT 'yyyy-MM-dd', Convert(datetime, '2017-03-07')
      UNION ALL SELECT 'yyyy/MM/dd', Convert(datetime, '2017/03/07')

Three safe date literal formats I've found in SQL Server.

yyyyMMdd HH:mm:ss.fff
yyyy-MM-ddTHH:mm:ss.fff
yyyyMMdd

@ErikE is that a question, or are you just making an observation?

@ErikE Are you sure about yyyyMMdd HH:mm:ss.fff?

@ErikE, @AaronBertrand can clarify I guess

In his (Bad habits to kick : mis-handling date / range queries), he states:
> the only truly safe formats for date/time literals in SQL Server, at least for DATETIME and SMALLDATETIME, are:

YYYYMMDD
YYYY-MM-DDThh:mm:ss[.nnn]

But in his (What do BETWEEN and the devil have in common?), he uses your first one (yyyyMMdd HH:mm:ss.fff), so I don't know.

7:23 PM

it's okay to post an answer for a version of SQL Server further ahead than the questioner's version, right?

you just need to be clear that it might not be immediately useful

@JoeObbish I've run across those kinds of answers and found them helpful.

On SO, sometimes answers appears before questions

I was so excited to see TRANSLATE

but realized it wasn't available yet :(

I think I'll install vNext in my lab after I get back from vacation next week. There's a lot of stuff I want to try in there.

Lamak

@JoeObbish as long as you also post a code that works with the questioner's version

7:37 PM

@MaxVernon It's an observation standing ready to be corrected if it's wrong.

@ypercubeᵀᴹ I'm pretty sure, I just tested it with the above script.

@ypercubeᵀᴹ But I only have access to SQL Server 2012, not 2014 or later.

YYYYMMDD has always been safe for me (2000 and on) both with and without the time part.

@dezso Clearly something was time traveling. The graph's got to be accurate!

so anyone with a gold tag-badge with postgresql on Stack Overflow can one-click migrate PostgreSQL questions to dba.se?

Do I understand that right?

@ErikE see sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/… - but essentially, the last two you list are the recommended ones to use.

@EvanCarroll the gold hammer allows you to close any question tagged with your gold badge. However, you cannot add the tag to a question, then close it with immediate effect.

7:53 PM

Sure, but does close include migration?

sorry, no, it's just duplicates locally.

the silver allows you dupe-hammer right?

the gold allows you to close-hammer?

as far as I'm aware only the gold badge allows you to close with a single vote. And then only close-as-duplicate. meta.stackexchange.com/tags/dupehammer/info

Stinking Badges

►

Can we rename pgadmin to pgadmin-3 and ban pgadmin?

8:11 PM

@Lamak Oh. I added a note, no time to do more than that for now

Q: PgAdmin Tags should be redone

0

pgadmin vs pgadmin-3 There are two seperate products PgAdmin 3. PgAdmin 4. PgAdmin doesn't currently mean anything until PgAdmin 3 dies off. As you may know, many of us from the pgAdmin team have been hard at work on pgAdmin 4 for some time now. pgAdmin 4 is a complete rewrite of pgAdmin...

discussion tagging tag-synonyms retag-request

do we have a procedure for nuking a question that is clearly stupid with a bad chosen answer that doesn't make any sense? dba.stackexchange.com/questions/120890/…

I need more upvotes so I can do more downvotes.

This site is currently in read-only mode;

wtf.

yea, jsut noticed this.

8:28 PM

now

9:49 PM

Can someone confirm that in SQL Server, a "non-logged operation" such as a bulk copy really is a "minimally logged" operation, in the sense that there is still a t-log entry created, just with far less data in it than if one were to perform the inserts one at a time?

@ErikE msdn.microsoft.com/en-us/library/ms190422.aspx

@ypercubeᵀᴹ Even in old versions like SQL 97?

I'm trying to convince an older guy who remembers that in SQL Server 7 non-logged is literally not logged, that it's really always been minimally logged, but it's not working.

I don't know. I don't even know what SQL 97 is

A: What is the better way to handle a different types of measurement in grocery store items database

0

Using postgresql-unit I'm big on types. I would use an extension for this. If you're not in the US and working with Imperial Units, take a look at Christoph Berg's brainchild postgresql-unit. First build the extension sudo apt-get install bison flex build-essentials libpq-dev git clone "https:/...

@ypercubeᵀᴹ Sorry, I said that wrong. SQL Server 7.0.

10:02 PM

@ErikE I don't know what recovery models were available then, but I can't understand how SQL Server could recover with literally 0 logging.

If the co-worker can't understand that, I'd say just follow. He says zero-logging, you know he is really talking about minimal logging ;)

@ypercubeᵀᴹ Yes, exactly.

Is it ethical to ask people here to help me close a duplicate on SO (about databases)?

Unless, you know, he is right and 7.0 did not log at all. But then it would not be able to recover after a failure during the import. I don't know how plausible this is

@ErikE Yes, sure.

Q: Alternate/candidate key in ADO.NET data model

2

I need to have an entity that has two separate candidate keys where one of them is the primary key, which is a surrogate identity key, and another one that is an alternate key representing the real unique attribute. I need to have them both separately in my entity. Please note that I am not refer...

.net database entity-framework ado.net

We do it all the time. Mostly for migrating but closing is no different.

I VTC'ed this older post as a duplicate of a newer one since the newer one is much better worded and has a more accurate (up to date) answer.

It's really sad when I go searching the internet for the answer to a question, and find that I answered it on stackoverflow just 37 days ago.

10:10 PM

I don't know if closing as duplicate is the best option though.

Or adding another answer, not sure.

None looks ideal anyway

I was tempted to paste my answer as another answer, but identical answers will flag a moderator and create problems.

Q: How to count how many times a value appear in a row

10:23 PM

-1

please i'm working on a project, i'm kind of hanged. Please how can I COUNT NUMBER OF SAME VALUE in a ROW.. Not counting number of rows in a table but number of value in a row. eg. 1 2 2 2 2 2 2 I want to know number of times that "2" appears in the row.

count

This ^^^ deserved a vote to close.