« first day (2271 days earlier)      last day (2586 days later) » 
00:00 - 16:0016:00 - 00:00

12:22 AM
@ErikE take for instance this...
SELECT * FROM ( VALUES (1),(2) ) AS t(x) CROSS JOIN ( VALUES (3),(t.x) ) AS g(y);
Does that work in MS SQL without CROSS APPLY?
It shouldn't but I have no idea whether or not it does.
Also, yes in PostgreSQL we use CROSS APPLY TO run SRF that's reference an external part of the query, this can force the evaluation. For instance, this SELECT x FROM generate_series(1,3) AS t(x) CROSS JOIN LATERAL generate_series(1,t.x); will generate..
(6 rows)
@EvanCarroll I'm sorry, I have not seen your comments. Let me take a look.
12:39 AM
@EvanCarroll Of course that doesn't work without CROSS APPLY, but that's not what I said works. I said in the ON clause you can have an outer reference or refer to any table that has already been introduced.
What would it even look like if you couldn't reference other tables previously introduced in a WHERE clause or an ON clause?
@EvanCarroll It wouldn't make any sense! But that's why it was a learning process for me. Because of the practice of using CROSS APPLY with its cool outer references, I started thinking differently. That different thinking dawned on me one day as applicable to most of the other parts of the query. I started doing cool stuff in ON, WHERE, and SELECT clauses I had never done before.
Stuff with outer references.
Yea, I think the best way to think about it is to ignore that though and just ask, does this query need to be evaluated once per row, or once per query.
That's how I use LATERAL in my head.
@EvanCarroll No, I'm not referring to anything that mundane. I'm talking about radically different queries I'd never thought of before.
@EvanCarroll I guess I mean, that yes, I was already in once-per-row land. I wasn't even thinking of once-per-query land. But sometimes you can do the same thing in two different ways.
   dbo.SomeTableWithDuplicates t
       SELECT d.OtherId
       FROM dbo.SomeTableWithDuplicates d
       GROUP BY d.OtherId
       HAVING Count(*) >= 2
   ) d ON t.OtherId = d.OtherId;
This query can easily be rewritten as a row-by-row operation.
So your dividing question, while useful in many contexts, isn't always all that helpful (it often comes down to how much of the table you expect to query).
   dbo.SomeTableWithDuplicates t
      SELECT *
      FROM dbo.SomeTableWithDuplicates d
         t.Id <> d.Id
         AND t.OtherId = d.OtherId
There, it's rewritten to do the same job (and will work much faster if you have a condition on the t table that selects only a subset of rows).
And the mind-blowing version:
  dbo.SomeTableWithDuplicates t
      SELECT *
      FROM dbo.SomeTableWithDuplicates d
      WHERE t.OtherId = d.OtherId
      HAVING Count(*) >= 2
Not as efficient, but in some cases this style of query is useful (aggregate within EXISTS clause).
Don't you need a group by in that?
12:50 AM
@EvanCarroll Nope. Try it. :) Mind-blowing, I said.
I can't try it, I don't have sample data. I don't think PostgreSQL you can run aggregates against un-grouped rows unless they just consist of a primary key.
I think what's it's doing there is an implicit GROUP BY with all of the columns
Which is something like what MySQL does too
@EvanCarroll Incorrect
SELECT * FROM ( VALUES (1,2) )  AS t(x,y) HAVING count(*) > 1;
That doesn't work in Microsoft SQL.
I'm not sure how your exist statement works with HAVING(*) if the above does not work.
@EvanCarroll Stick that in an EXISTS clause
SELECT 1 WHERE EXISTS ( SELECT * FROM ( VALUES (1,2) ) AS t(x,y) HAVING count(*) > 1 );
ERROR: column "t.x" must appear in the GROUP BY clause or be used in an aggregate function
Doesn't work in PostgreSQL
12:54 AM
need an outer reference to implicitly group by
@EvanCarroll No, no outer reference needed, works in SQL Server. The * is irrelevant.
SELECT * FROM ( VALUES (1) ) AS g(x) WHERE EXISTS ( SELECT * FROM ( VALUES (1,2) ) AS t(x,y) WHERE g.x = t.x HAVING count(*) > 1 );
ERROR: column "t.x" must appear in the GROUP BY clause or be used in an aggregate function
@EvanCarroll Must not work in PostGreSQL (it pays attention to the * when it should ignore it)
I don't agree that. I think this is just non-standard syntax that Microsoft implemented. But, I could be wrong.
@EvanCarroll I don't agree. It makes perfect sense to me.
@EvanCarroll I'm sorry to disturb you but FILTER requires a window definition and I'm not able to figure it out now. 1:56 AM I'm going to sleep. I'll try again tomorrow. Nite all
12:57 AM
I'll look at it in a sec @McNets, good night.
@ErikE it's likely an implementation detail. Microsoft is grouping under the hood and rather than denying you the ability to use that non-standard syntax, they expose it misleading others into thinking it works.
@ErikE I don't think (a) that EXISTS requires a GROUP BY implicit or otherwise, or that (b) the syntax is supported by the spec.
That said, I could be totally and utterly wrong.
Let me turn it into a question. ;)
@ErikE why is this returning x and not 1? dbfiddle.uk/…
@EvanCarroll Dangit I was trying dbfiddle.com and dbfiddle.net and just could NOT find that thing! Finally.
@EvanCarroll It doesn't return anything. There's a single column, x, with no rows.
wow. that's a horrible display.
I was on the phone so couldn't explain much. I'll explain now.
1:05 AM
@JackDouglas feature request! ^^ row counts
@EvanCarroll Be sure to lodge your complaints with the author!
@EvanCarroll The trick with EXISTS is that the SELECT list is irrelevant. DBMSes are actually supposed to ignore (or at least, can ignore) the SELECT list inside an EXISTS clause.
Additionally, a correlated subquery is implicitly grouped by the correlation keys
PostgreSQL is ignoring the select list.
@EvanCarroll But it may be parsing it and then tossing it later.
no, it's not
If you do CROSS APPLY (SELECT Max(Val) FROM SomeTable t WHERE a.Id = t.Id) (notice the outer reference to table t) you do NOT need a GROUP BY because it is implicitly grouping by the correlation key, a.Id.
1:07 AM
It's just invalid syntax because you can't do HAVING without GROUP BY
you do not need a group by because you do not have a HAVING
What I noticed in my own queries is that this implicit grouping lets you dump the GROUP BY clause, if the GROUP BY expression exactly matches the correlation key.
in your exists query, you have a HAVING. So you need a group by.
Well, whether it's peculiar to SQL Server or not, you don't need a GROUP BY because a correlated subquery is implicitly grouped :)
That may be true, but that's a violation of standard. MySQL does that kind of stuff too.
Let me cook up an example in dbfiddle
@EvanCarroll Show me the standard on EXISTS clauses that says this, and I'll believe you. :)
@EvanCarroll You can put a GROUP BY if you want to, but you don't need to.
1:09 AM
See, not ever EXISTS should result in a grouping to begin with, what if there is enough rows to pay for the aggregation and the planner wants to take a different route? It's exposing an implementation detail which could be optimized away.
HAVING is just syntax sugar anyway.
@EvanCarroll dbfiddle.uk/…
I got it working now
I'm writing a simplified question right now
Turns out you don't even need the WHERE clause. It still accepts the HAVING in an EXISTS without the GROUP BY.
I blow people's minds all the time with a GROUP BY in an EXISTS clause that starts SELECT * ... :)
It's a horrible idea to ever do this. It's no faster than the explicit GROUP BY.
1:17 AM
@EvanCarroll I never did it because of speed. :)
@PaulWhite I'm actually quite curious why HAVING works in SQL Server inside an EXISTS clause without a GROUP BY. I know that logically, a correlated subquery is implicitly grouped by the correlation keys, and I can see how the engine could project a correlated subquery to a non-correlated one with a filter or seek of some kind (meaning it is probably free to go collect all the subquery's results using a more efficient whole-table aggregate operation before it knows all the input rows).
But I'm curious how it works when there are no correlation keys.
It has an implicit GROUP BY every column
just like MySQL
in my experience HAVING can make queries worse
Often that's true.
which is unfortunate
don't really get why
1:20 AM
But SQL is supposed to be declarative, so all this talk of one syntax being faster than another is ... odd.
A logical query specification is exactly that.
Physical implementations have performance characteristics.
ohhhhh I see what you're saying..
CE seem different in a bad way
haven't spent too much time trying to figure it out
it's the only connect item I've ever submitted though
no one voted for me! :(
@JoeObbish I've submitted several, actually. Most of them got shot down.
I asked for INTERSECT ALL and EXCEPT ALL and was told no. Boohoo!
@JoeObbish Link?
@JoeObbish Well late filters are often hard to estimate. Lots of stuff has happened before them.
Ha! I also asked for DISUNION
1:26 AM
You obviously need to ease up on the recreational drugs!
I've submitted 8 feedback items
will find later. busy killing zombies
6 Won't Fix, 1 Fixed, and 1 By Design
or being killed. remains to be seen
This one really ticked me off when they answered "won't fix": connect.microsoft.com/SQLServer/feedback/details/668183/…
1:29 AM
@EvanCarroll Also: dbfiddle.uk/…
I think this is a bug in PostgreSQL at this point.
we'll find out.
@PaulWhite Ha! There it is! Same behavior as SQL Server! Awesome.
@ErikE I used to get upset by Won't Fix until someone at MS pointed out to be how the system works, and what Won't Fix actually means.
It's confirmed
=( talking to a developer now about it.
@ErikE++ # You get a point.
@EvanCarroll Actually, Evan, PostGre's difference is that it DOES expand the * to all the columns, because Paul's most recent fiddle post fails if you change the 1 to *.
1:31 AM
Thanks for that query @PaulWhite I was running in circles before I saw that.
@ErikE that's been in the question I posted for a while. ;)
@ErikE Yes I tried * first and was surprised it didn't work, so I changed it to a constant.
Or in any case, it's improperly evaluating the query since in an EXISTS clause, the SELECT list is meaningless.
@EvanCarroll Yeah sorry I was reading chat on my phone and didn't fancy trying to put a fiddle together until I got to a real computer.
@PaulWhite I heard that in Oracle, the myth about SELECT 1 being faster was so prevalent that they had to change their engine's code because SELECT * was actually the best-practice, working version they'd coded for, and SELECT 1 actually caused the engine to do something different and worse.
Aggregate binding is an absolute nightmare so I have sympathy with them (PG not Oracle).
@ErikE Interesting.
1:35 AM
20:33 < RhodiumToad> the one problem is that in pg you can do  exists(select func() from ...   where the func() is an SRF that might return 0 rows
Oh PostgreSQL. you are amazing.
that's terrifying, fyi. @ErikE if you thought this was mind blowing.. imagine a function in the select list that that returns 0 rows.
even if the row exists.. DENIED.
not enough existence.
@EvanCarroll I don't understand.
@PaulWhite What does Won't Fix actually mean? Just Won't Fix in the current version?
In PostgreSQL we can make functions that return 0, or more rows. So a function can return a table and the like.
@EvanCarroll You can have table-returning functions in SQL Server, too.
The reason why this optimization isn't yet in PostgreSQL is because of that, if the function returns 0 rows, the exists test fails.
So what do you if you have a table-returning function in an EXISTS test that returns 0-rows?
@ErikE It can mean a huge variety of things. The most practical interpretation is "thanks! we may or may not fix this, in fact we might already have fixed it. Or not."
One might expect a zero-row SRF to be coerced to NULL in that situation
Chris Date explodes
1:38 AM
@EvanCarroll Ah, well, I presume this means that PG has some kind of "table object" that functions like a scalar?
Not like a scalar, but yes. You can think of an SRF as a table-object of sort.
@EvanCarroll What does SRF mean?
Set Returning Function
@EvanCarroll Yeah, like, a special data type... an array of objects, kind of, which isn't actually a row. More like fields than rows/columns
1:40 AM
@EvanCarroll And an SRF doesn't need to go in the FROM clause? One can just SELECT SRF() on its own?
In SQL Server, you would need to SELECT <project list> FROM SRF AS alias;
@PaulWhite As a derived table in the FROM clause you mean, right?
Unless it returns 0 or 1 rows that are 1 column wide and can be implicitly converted to a scalar... ?
In PostgreSQL, you can for instance, say, `SELECT 1, srf()`. Let's assume the SRF returns three rows, 1, 2, 3. That'll return

If the SRF returns 0 rows. that'll return no rows, Because you can't cross join 1 row, with 0 rows. an SRF in a SELECT-list is essentially a cross-join-lateral.
@EvanCarroll That's kind of cool though weird to me.
@ErikE In the documentation it's just referred to as a <table_source>.
@PaulWhite you can write it on its own. SELECT srf() is perfectly valid.
So is TABLE srf()
1:42 AM
What do you get with SELECT srf1(), srf2() and they return a different number of rows?
The Cartesian product.
I guess that's just a cross join.
Try it, generate_series() is an src.
You may remember this getting posted by Adam on twitter a while back: SELECT *
FROM ( VALUES ( 1), ( 2) ) AS g ( x )
FROM ( VALUES ( 1) ) AS t ( x )
WHERE g.x = t.x );
Having that in the SELECT clause is weird!
1:43 AM
SELECT generate_series(1,5), generate_series(1,2);
@sp_BlitzErik Yes I do. Rob Farley has a great example too.
@ErikE you use it a lot in PostGIS.
@ErikE take for instance, a function that decomposes a line to individual points... You could use
`SELECT ST_DumpPoints(l) FROM tbl WHERE id=1`

or you could say

`SELECT * FROM tbl CROSS JOIN LATERAL ST_DumpPoints(tbl.l) WHERE id = 1;`
That's just the extension of the fact that aggregates return a single row with `NULL` when they have no input rows: `SELECT Max(x)
FROM (SELECT 1 WHERE 1 = 0) z (x)`
@ErikE Scalar aggregates.
Y U No make code blocks in my last chat post!?!?!
@PaulWhite As opposed to what kind of aggregates?
1:47 AM
@ErikE Vector aggregates.
    VALUES ( 1), ( 2)
) AS g ( x )
    SELECT MAX(t.x)
    FROM ( VALUES ( 1) ) AS t ( x )
    WHERE g.x = t.x
    GROUP BY () -- Vector agg
@PaulWhite Okay I admit I don't understand that.
@PaulWhite WUT!?!?
@PaulWhite Learn something new every day! I have never seen GROUP BY () before.
@sp_BlitzErik If you didn't see it, it was: (explanation here)
SELECT 'No Rows'
@PaulWhite INSERT dbo.Brain (Content) VALUES ('Complete nonsense')
This is the stuff that keeps parser/binder writers awake at night.
@EvanCarroll That's still a bug though, right? Surely PG knows if there's an SRF like that present, or at least implement a plan that accounts for it correctly within the exists test.
@PaulWhite I'm not sure if they think it's a bug.
1:53 AM
@EvanCarroll Ok.
It's a violation of the spec though, so they'll likely be moved to view it that way. dba.stackexchange.com/a/168049/2639
@PaulWhite I read your post but have to read it again a few times to digest it. Interesting stuff! Thanks!
@EvanCarroll Markdown formatting is a pain sometimes eh.
no joke seriously
where is the button for auto-escaping html/xml in a block quote in that stupid editor.
Yep. You can &lt; it yourself but ugh.
2:00 AM
I prefer block quote gui-button to just be less stupid. What's the chances I want to block-quote something and have it rendered as if the block quotes had HTML in it.
But, perhaps it's style.
Chalk one up to the zombies.
@EvanCarroll Oddly enough, it is sufficient to replace only the < with a &lt;
> >If the &lt;select list> “*” is simply contained in a &lt;table subquery> that is immediately contained in an &lt;exists predicate>, then the &lt;select list> is equivalent to a &lt;value expression> that is an arbitrary &lt;literal>.
Renders as:
I have no idea what's going on in here
Adam's query doesn't seem weird to me. I might have a problem...
"installation progress... 100.0%"
somehow the significant digit makes it worse
still stuck on that screen
2:28 AM
@JoeObbish You submitted that bug on my birthday or Rob Farley's, depending on whether 11/7 means July or November.
it was November
Farley then.
After I win the next moderator election, to get opper in the channel too?
@EvanCarroll Opper?
2:42 AM
Still not with you, sorry.
After I win the next moderator election, to get moderator privileges in the channel too?
Yes. On all chat.SE sites. Not chat.SO or chat.mSE.
that's going to be great.
I can throw my own messages in the trash.
You young folks and your modern vernacular!
+o is like 1982
/mode +o <nick>
2:44 AM
Yep. Modern :)
Nah it's just not something I encountered at the time.
not an irc user?
Never really got into it, no.
Not missing much.
It's pretty horrible.
The only cool thing about it that you can't do here -- and this is pretty horrible too, is have bots.
I miss irc bots.
Some chat rooms have bots. We only have one - Master Database for meta questions.
For instance, in #PostgreSQL, if someone asks a question about Window Functions, I can do ??window-functions [>user], or the like and it'll send them the docs.
we can also book mark quirks and the like.
2:48 AM
Oh actually there is another one here, the translate bot.
translate fr: if someone asks a question about Window Functions I can send them a link.
(from English) Si quelqu'un pose une question sur les fonctions de la fenêtre, je peux leur envoyer un lien.
> les fonctions de la fenêtre
4 hours later…
6:49 AM
7:13 AM
7:44 AM
@PaulWhite and the define: bot? Or is that not a bot, just a function of all the rooms?
@ypercubeᵀᴹ Yeah that's the third one in the list of one :-/
8:19 AM
Oh a translate bot, didn't know we had that
translate fr: goeiemorgen
mods only
does it translate from English only or from any language?
translate: goeiemorgen
(from Dutch) good morning
8:34 AM
translate: καλημέρα
@ypercubeᵀᴹ the 'fr' before was the target language code
(confirms I'm not a mod)
translate fr:bon dia
Interjection: καλημέρα • (kaliméra)
  1. good morning, hello, good day
Noun: καλημέρα • (kaliméra) f (indeclinable)
  1. good morning, hello...
8:37 AM
kaliméra nice, do you use accents too? @ypercubeᵀᴹ
@McNets Yes. But not so many now: en.wikipedia.org/wiki/Greek_diacritics
We started with 0 diacritics (in Ancient Greek), moved to a lot (from about 3rd century BC - AD until the 20th century). Then we had 2 steps of removing them. There is now only a single accent and the diaeresis used.
@EvanCarroll I think TomTom will get more votes
@ypercubeᵀᴹ I see, we use: à é è í ï ò ú ü
8:55 AM
Oh, you are not talking about Spanish?
Nope, I'm talking about Català, Spanish has only graves accents: á é í ó ú and ü
9:25 AM
@ErikE ha! Now you became my next victim. It's PostgreSQL or Postgres (if really necessary, PG for short), there is no product called 'Postgre' or 'PostGre'
spider in its net
I should make a fork and call it PostGreeceQL
PostGr for short
@ypercubeᵀᴹ would be very δημοφιλής, I guess
How about MyperSQL ?
@McNets And ñ
á é í ó ú ö ü ő ű
9:40 AM
@AndriyM This is not an accent, it is an specific letter of Spanish alphabet.
if you were wondering
@McNets Oh I see, very well, thanks for that.
In Català there is: ç Ç (sounds like: SS)
@McNets It is considered a different letter, ok.
But it's still an n with an accent, visually
9:46 AM
@McNets what's the difference from S ?
@McNets Those are using acute accents rather than grave accents actually (except for the ü, of course).
it is a phonetic difference
@McNets is the sound like the s in "see" or sh in "she"? or ch in "chin"?
@ypercubeᵀᴹ yes it is similar
Similar to all three?
9:49 AM
depending on the next vowel
'si' 'se' has a sound similar to 'see', however when you use: 'ssi' 'sse' the s sound is "stronger"
From reading the wikipedia it seems that c C is pronounced like k except when it is followed by i or e (where it is pronounced like s) and in some more exceptions (where it is written as ç Ç and pronounced like s)
Is that correct, @McNets?
'i' and 'e' are 'weak vowels'
adding to my above listing, that's also a case of different letters and not accents
Hungarian accents are easy
So "caço" and "caces" would be pronounced "ka-so" and "ka-ses"
@dezso Spanish accents too
Catalan (/ˈkætəlæn/; autonym: català [kətəˈla] or [kataˈla]) is a Romance language derived from Vulgar Latin and named after the medieval Principality of Catalonia, in northeastern modern Spain and adjoining parts of France. It is the national and only official language of Andorra, and a co-official language of the Spanish autonomous communities of Catalonia, the Balearic Islands, and Valencia (where the language is known as Valencian, and there exist regional standards). It also has semi-official status in the commune of Alghero, situated on the northwestern coast of the island of Sardinia (Italy...
9:59 AM
Verb: translate (third-person singular simple present translates, present participle translating, simple past and past participle translated)
  1. (transitive) To change text (as of a book, document, movie) from one language to another.
  2. 2002, Matt Cyr, Something to Teach Me: Journal of an American in the Mountains of Haiti, Educa Vision, Inc., ISBN 1584321385, 25:
  3. His English is still in its beginning stages, like my Creole, but he was able to translate some Creole songs that he's written into English—not the best English, but English nonetheless.
  4. (intransitive) To change text from one language to another; to have a translation into another language.
  5. 2004, Ted Jones, The French Riviera: A Literary Guide for Travellers, Tauris Parke Paperbacks (2007), ISBN 9781845114558, chapter 3, 58:
(23 more not shown…)
Noun: translate (plural translates)
  1. (analysis, in Euclidean spaces) A set of points obtained by adding a given fixed vector to each point of a given set.
this one, too
Yeah the cube mentioned that one.
@PaulWhite I stopped reading the transcript prematurely, it seems
There's a lot of it today.
I missed some 457 messages
Ok my bad, I go jump of a bridge — Frks 8 mins ago
Well I'm sure that won't be necessary.
10:06 AM
@dezso I usually care a lot about names, spelling, acronyms, correctness, and so on. For some reason I don't feel so excited about getting PocedGree correct.
@PaulWhite I think he may be the OP, with unregistered accounts
The "answer" suggests to me that it's the OP too
@ypercubeᵀᴹ Yes. I left a comment.
We'll never know, if he fulfills his promise (jump).
3 am here and can't sleep. Sigh.
10:10 AM
@ErikE I recommend booze, drugs & prostitutes. Sleep well!
Oh, hang on, this isn't Grand Theft Auto, it's real life
@ErikE try to spell ogresql correctly a hundred times
I use to see:
INSERT INTO ErrorsTable (...) VALUES (@ErrNum);

Cannot be used directly?

@ypercubeᵀᴹ Which would be a shame.
10:19 AM
@PaulWhite If it were SQL Server, the query would still be wrong, as there is firstly, no m alias; and secondly no use of m allowed after pivoting.
Looks like the booze and drugs are kicking in.
Who's Ana?
This is a fun game.
@PaulWhite damn mobile web chat bugs make it impossible!!!
It's certainly a challenge!
I can't see what I'm typing while the keyboard is up
I find positioning the cursor equally entertaining.
10:24 AM
@PaulWhite My mistake for taking Drews and bugs instead.
Ha ha ha ha ha ha ha
Yeah I was working on fixing Ana but exceeded edit time.
I took pity.
@PaulWhite Ooh, you fixed my subjunctive, too!
The new chat is sneaky: It's so much better than the old one, it makes you think it's fully functional.
The half hour delay in posting my earthquake message yesterday was mostly about fighting my phone and mobile chat.
10:29 AM
If they just let you scroll past the bottom of the chat log it would be workable as you could manually keep up.
Like infinite scrolling past the bottom by adding line feeds or some kind vertical space.
Wish me luck struggling to sleep. I'm sick and can't breathe well, too.
Good luck!
11:19 AM
@EvanCarroll row counts are now shown, as a by-product of using 'datatables'. I think it's an improvement but not sure, so would appreciate feedback.
what is happening to you @PaulWhite?
@JackDouglas Well it's not like I wrote some MySQL!
thin end of the wedge I think
Maybe. I shall attempt to be more aware of the dangers :)
11:29 AM
you'll thank me one day for saving you from ||
@JackDouglas The concatenation operator? Parallelism?
you see, you already recognize it as "The concatenation operator", not just "A concatenation operator". It may be too late for you.
I learned it from Oracle 8 :)
Plus, it is the SQL Standard concat operator, right?
Anyway, don't flatter yourself. If anything, I am succumbing to Evan's propaganda powers of persuasion.
Yeah right.
Is "Yeah right" directed at yourself??
Evan may have done more damage than he intended
12:07 PM
@ypercubeᵀᴹ The Comic DBMS
12:20 PM
listening to the Star Wars soundtrack. It's funny how much you can see in your minds eye just from the music.
@TomV starred. Some cliparts are missing, though.
12:55 PM
@TomV is that comic sans?
Good morning
@McNets Yes I find it more readable
@TomV as you should
This question got me wondering
Q: Dedicated database for each user?

AlluziionI'm working on what will be quite a large web application that will handle business-sensitive data. I'm trying to maintain a very high-level of security but I'm not expert with databases. Eventually we'll bring in a database expert but for the time being during early development I'll be developin...

Would it make sense to do that in SQLSERVER ?
Let's say your application goes viral and you have millions of users
can you have millions of databases ? sounds dumb as hell to me
@A_V for millions perhaps. For a few hundreds (or thousands) might be good
1:10 PM
Impossible to answer without details about the application
or the RDBMS
The #1 reason I'm thinking you'd want to do this is so no SQL injection or poorly written code can retrieve rows of data that a specific user shouldn't have access to
but both of these issues can be somewhat fixed
@TomV indeed, in the specific case of the question it's hard to say
it's kind of like discussing about a specific use case without actually having a case
@A_V Each choice has different advantages and disadvantages
managing a table with millions of rows of user data seems easier than managing a million databases
with 2-3 rows of data each
anyways, scalability isn't the only thing to think about
1:27 PM
SO is an example of a system with millions of users and it uses SQL Server. As far as I'm aware, it's not allocating one database per user.
Yeah but let's say we were talking about a database of people medical records including all their bank account numbers and PINs
SHOULD it be designed differently
as far as I understand it
if you allow your customers to write their own SQL queries then you need to be much more careful
@PaulWhite that's coming, just you wait
but if not then I don't see how it's different from other databases?
@bluefeet Now I'm scared :)
1:35 PM
Deal of the Day March 24: Half off C# in Depth, Fourth Edition. Use code dotd032417au at http://bit.ly/2mTRMZy
@PaulVargas but if you read that, you wont need jon skeet to google stuff for you.
1 hour later…
3:05 PM
Hi @EvanCarroll, it works now. dba.stackexchange.com/a/168008/110455
You could probably secure massive multi-tenant fairly effectively with schemas, but honestly, this is database security layering 101. You can secure your perimeter and even let them write fairly arbitrary code by just thinking about what objects you grant them access to and which rights. Inline TVF or views that restrict the surface area of what individual users can request.
3:20 PM
@Wilson Very interesting question indeed, big topics. I have posted my take on it.
3:31 PM
Never ending history again, stackoverflow.com/questions/43001133/…
@McNets Very cool. I find that syntax substantially cleaner. =)
@EvanCarroll I don't know if GROUP BY can be avoided, and get it using a partition.
can't get around GROUP BY if you need a count
3:57 PM
A: Multiple Databases Vs Single Database with logically partitioned data

ErikEYou'll wish you had used separate databases: If you ever want to grant permissions to the databases themselves to clients or superusers. If you ever want to restore just one client's database without affecting the data of the others. If there are regulatory concerns governing your data and data...

00:00 - 16:0016:00 - 00:00

« first day (2271 days earlier)      last day (2586 days later) »