« first day (2234 days earlier)      last day (2635 days later) » 

Paul White
Paul White
12:53 AM
@MaxVernon I have made a different edit that I feel respects the original author a little more (they did not ask about 9.6, how could they). If the 9.6 aspect needs more clarity, a sensitive edit to the accepted answer might be appropriate. I also unprotected the question, since it has not attracted "me too" or spam answers.
@ypercubeᵀᴹ If the PG base is anything like SQL Server's, most users will not be on the latest version at any point in time, so the accepted answer is arguably the most correct one (on average). Not explaining that at length before anyone asks :)
 
ypercubeᵀᴹ
ypercubeᵀᴹ
@PaulWhite I'm not sure about similarities with SQL Server. Truth is that upgrading major version is a bit of a pain.
So, unless one has strict policies and tested procedures in place, they will be reluctant to upgrade.
 
 
1 hour later…
Michael Green
Michael Green
2:08 AM
Is MDEV supposed to be a temple table or something ? — Jesse 3 hours ago
A temple table -- would that be an ALTER?
4
 
Joe Obbish
Joe Obbish
2:50 AM
I've got a SQL Server question that I'm considering asking, but I'm not sure if it's a good format for the site. Anyone wiling to give feedback?
 
Max Vernon
Max Vernon
3:11 AM
@PaulWhite seems like an excellent edit to me!
 
Max Vernon
Max Vernon
3:24 AM
@JoeObbish shoot
 
 
3 hours later…
Joe Obbish
Joe Obbish
6:05 AM
@MaxVernon I'd like to ask for a complete list of when a join is not eligible to be implemented as a hash join
I already know some of the conditions, but not all
but how could anyone know that they have the complete list?
might be a shopping list question?
 
 
2 hours later…
Martin Smith
Martin Smith
7:43 AM
@JoeObbish The only condition I can think of is that it needs an equi join. Are there other things that would prevent a hash join?
 
Martin Smith
Martin Smith
8:00 AM
@JamesLupolt Yep I'm going for the full four days. Weds and Thur have signed up for "Loading And Transforming Data In Power BI And Power Query" and "Data Science with SQL Server Tools" training days.
 
 
2 hours later…
Andy K
Andy K
9:38 AM
morning
 
dezso
dezso
good morning
 
hot2use
hot2use
9:50 AM
and a good morning to you too
 
Paul White
Paul White
10:42 AM
Morning
 
ConcernedOfTunbridgeWells
ConcernedOfTunbridgeWells
From the good-luck-with-that department ...
 
ypercubeᵀᴹ
ypercubeᵀᴹ
morning
 
ConcernedOfTunbridgeWells
ConcernedOfTunbridgeWells
Morning
 
hot2use
hot2use
@ConcernedOfTunbridgeWells We are also not allowed to drink during working hours, but lunch-time is not a working hour.
So either Lloyds will have to start paying for the lunch break or "will have to take their new regulation and stick it somewhere where the sun never shines."
 
dezso
dezso
@ConcernedOfTunbridgeWells It’s a British thing that we commiserate and celebrate with alcohol apparently, I've been living in Britain for my whole life
 
McNets
McNets
11:02 AM
Hi, can be considered one day a good day if, after a esxi update, the NAS for backups & replicas becomes frozen?
 
Michael Green
Michael Green
Better than "yes, the SAN's completely wiped. .. ooooh! You meant thaaat one over there" conversation. True story.
 
ConcernedOfTunbridgeWells
ConcernedOfTunbridgeWells
11:57 AM
@dezso Yes, you should find it reassuring that much of the critical infrastructure you depend on is insured through deals negotiated over liquid lunches in pubs in EC2 or EC3.
I have a hypothesis that the Lloyd's Target Operating Model programme is doomed from the start because in order to make improvements to the process they will have to find a way to automate the liquid lunch.
 
Max Vernon
Max Vernon
12:55 PM
@JoeObbish perhaps if there were more to the story than just a definitive list. For instance why do you want to know?
Admittedly, it would probably be closed as "opinion based" unless one could find an authoritative document.
 
 
1 hour later…
Tom V
Tom V
2:01 PM
@MaxVernon I use that word at least once a day
@Dodi82 No, you need to look at named sets instead of calculated members or, what would be my choice, implement them in your dsv query or datawarehouse and create a real dimension attribute to use in your hierarchy
Pardon my French @EvanCarroll @ypercubeᵀᴹ @MaxVernon
 
Joe Obbish
Joe Obbish
2:29 PM
@MartinSmith yeah, I'm aware of that one
 
ypercubeᵀᴹ
ypercubeᵀᴹ
@TomV French? You mean the SSAS / SSIS, whatever this language you are talking is?
 
Joe Obbish
Joe Obbish
the equality condition needs to apply to all rows so certain types of OR logic can fail
I've also seen queries with FOR XML PATH fail to use a hash join, but I didn't look closely enough to see if that was just the equi join restriction
historically there are some other ones, but they all appear to be fixed as of SQL Server 2016
@MaxVernon if I asked for examples of query patterns that would no longer be opinion based
and it's not like there would be 100 possible answers so it seems like a reasonable question to ask
but I suspect that would be closed too
I want to know because I'm trying to identity types of queries that won't perform well when the large tables in them are converted to CCIs
 
Max Vernon
Max Vernon
@JoeObbish I think the "opinion based" would be around the list being complete. Although if you made the central point of the question around conversion of tables to clustered columnstore indexes, then I think your question may be well received. At this point, I'd recommend trying it.
You've certainly peaked my curiosity
 
Joe Obbish
Joe Obbish
@MaxVernon Would it be better to ask for examples instead of a complete list?\
 
Max Vernon
Max Vernon
@JoeObbish excellent idea. The word "list" tends to get bad treatment, for some reason.
 
Dodi82
Dodi82
2:40 PM
@TomV Hi Tom and thank you for your reply. I sounds as a big turn I need to take for this approach! Creating the territory dimension may lead to snow flake schema which I am trying to avoid! It would be really useful if the SSAS allow when you can hierarchies to be more dynamic which you can group attributes
 
Joe Obbish
Joe Obbish
@MaxVernon would you recommend posting a self answer that contains the examples that I already know about?
 
Dodi82
Dodi82
@TomV By the way, I reached the level of the analysis in my project! Something I wouldn't really imagine to achieve in the past months. But, it did take good efforts and time going through different resources
It worth the hard work though
 
Max Vernon
Max Vernon
@JoeObbish Sure, that way people won't waste their time on things you already know about.
 
Joe Obbish
Joe Obbish
@MaxVernon Thanks for your feedback. I've seen self answers sometimes not well received on the site. I'll post it in a bit after doing more research
 
Tom V
Tom V
@Dodi82 Creating a "Member" calculated or not is a new item in the "attribute", if you want grouping you need a new level in your hierarchy or a named set
a named set can be created using MDX, a new attribute needs to be created from a source
 
ypercubeᵀᴹ
ypercubeᵀᴹ
2:45 PM
@TomV SSIS, SSAS, MDX - they are all Greek to me.
 
Tom V
Tom V
not sure how you do your "dynamic" grouping, or expect SSAS to know about geography and or any other dimension you throw at it, so you will need to define the grouping yourself
you'll probably hard code it anyway if it's not in your data
 
Dodi82
Dodi82
@TomV My idea is to group the countries into three territories and then use these group into the SSRS
 
Tom V
Tom V
and I don't see how it would lead to a snowflake, just convert the table in your DSV to a named query and either join to a table where you have your territories, so you still have a star schema, normalize it in your data warehouse or hard code it with a CASE WHEN THEN ELSE END as territory in your named query
 
Max Vernon
Max Vernon
@JoeObbish sure. I think the self-answers that are not well-received tend to be seen as just being a way of showing off, or looking just for rep. I'm sure this type of question/answer won't be seen as belonging to either of those categories since it is (a) technically complex, and (b) you'll identify in your question and self-answer that the list you provide is not considered complete.
 
Dodi82
Dodi82
I though creating a dimension may lead to a snow flake!
 
Tom V
Tom V
2:50 PM
@Dodi82 No, adding a table to a dsv could
 
Dodi82
Dodi82
@TomV Got it now thank you :-)
 
Tom V
Tom V
@Dodi82 but adding a new dimension instead of a hierarchy level using a new attribute would prevent you using it in a hierarchy, and would prevent you to design attribute relations hence SSAS would be able to create a bitmap index
 
Max Vernon
Max Vernon
ouch.
0
Q: SQL Server detected a logical consistency-based I/O error: incorrect pageid on RESTORE

Henrik Staun PoulsenThe restore of our database to new server hardware failed with a page error. Message SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 49:8125916; actual 49:29097436). It occurred during a read of page (49:8125916) in database ID 7 at offset 0x00000f7fbb8000 ...

sql-server restore sql-server-2016 checkdb
 
Tom V
Tom V
so all your filters could possibly be very slow because a nonemptycrossjoin would need to check all cells to see if they are actually empty instead of using an index
 
Forrest
Forrest
@MaxVernon Yeah, I was looking at that one. Makes me think they weren't keeping up with CHECKDB.
 
Max Vernon
Max Vernon
3:01 PM
@Forrest yah, for sure. As a database gets bigger, it becomes more important not less important to run dbcc regularly, because the chance of single page corruption is progressively larger. Also, as you know, its a restore plan, not a backup plan, that is important.
 
Dodi82
Dodi82
@TomV I think I will keep it as its because I can't afford the filters to be slow or deprive me from using it as a hierarchy!
 
Tom V
Tom V
@Dodi82 Then don't create a new dimension, but create a new attribute inside your existing dimension
add it to the hierarchy
and define appropriate attribute relationships
it's simple as that really
 
Dodi82
Dodi82
You right, but I used the Country attribute within a hierarchy. Is it fine if I rename it as you said and then use it again in another hierarchy?
 
Tom V
Tom V
@Dodi82 you were talking about territories such as South-America
aka the grouping of countries
 
Dodi82
Dodi82
@TomV Yes, using the country attribute, rename it to territory and add it as a new hierarchy. The territory attribute contains the data from the country attribute but classified as Europe, North and South Americas
 
Tom V
Tom V
3:32 PM
@Dodi82 Yeah, whatever :)
 
Dodi82
Dodi82
@TomV Oh Ok :-0
 
 
1 hour later…
Max Vernon
Max Vernon
4:48 PM
Based on the facts that eventually emerged in the question, should I delete my answer here?
6
A: Insert up to 10 missing products into a related product discounts table

Max VernonThis might do what you want: SELECT 'INSERT INTO products_discounts (product_id, discount_amount, discount_description) VALUES (''' + CONVERT(varchar(30), p.product_id) + ''', 10, ''Top 10 Product'') ' FROM products AS p LEFT OUTER JOIN products_discounts AS pd ON p.product_id = pd.product_I...

@AaronBertrand ^^^^^
 
Jack Douglas
Jack Douglas
5:34 PM
@Philᵀᴹ what do you think of my new toy? dbfiddle.uk/…
 
wBob
wBob
6:12 PM
@MartinSmith I should be at the "Data Science ..." session, doing Text Mining the day before. Will say hello!
 
 
2 hours later…
ypercubeᵀᴹ
ypercubeᵀᴹ
7:47 PM
Quick note that OUTER APPLYS are horrible for performance and should only be used if there isn't an alternative set based approach. — Jonathan Fite 1 hour ago
@PaulWhite @MartinSmith ^^^ I would think this is not accurate as an absolute statement but please advise.
 
Joe Obbish
Joe Obbish
@ypercubeᵀᴹ not accurate as an absolute statement
I have taken queries that used LEFT OUTER JOIN and switched them to OUTER APPLY and experienced worse performance
I can
't prove this, but it seems like SQL Server is more likely to use a nested loop join with APPLY
it is possible for the query optimizer to implement APPLY not as a nested loop join though
my personal rule is to use APPLY if I'm comfortable with a nested loop join for that part
 
William Mariager
William Mariager
8:24 PM
On a scale of 1-10 how bad would it be to do away with my web service layer and instead just let my application connect to the database directly with a user that only has certain rights/views/procedures?
I imagine procedures can ensure that rows are only inserted in a proper way. And the views will be read-only.
Obviously it would put a lot of pressure to make sure the accounts have only the access they need and nothing more.
Okay, I've been reading a bit and it seems there's a few issues with it. One is less control and another is a tighter coupling which I don't like, so I think I'll stick with my web service layer.
 
Tom V
Tom V
8:42 PM
@WilliamMariager are you aiming for API stability? i.e. you could change the db layer & stored procedure parameters if you want as long as the service calls remain the same etc
are the calls your own or 3rd party is what I mean (not necessarily currently but maybe in the future)
 
William Mariager
William Mariager
The calls are my own, but in the future they might be 3rd party sure.
 
Tom V
Tom V
@WilliamMariager I had the same discussion today about direct db access via API
it depends on what you agree on with your caller application/partners I suppose
but a layer of abstraction could be a good thing or a bad thing anyway you look at it, direct db access would be faster, API abstraction and stability could be a good feature, if the API really is stable
 
William Mariager
William Mariager
The reason I was curious was because I feel like I'm making quite the web API to support everything I want efficiently. I basically have a product database of sorts. Lets say I want to find all products in Finland. The Web API allows me to fetch all products or a product by id. Now I need to add a method to fetch products by country code. And this keeps happening.
 
Tom V
Tom V
Haha, lol that's exactly the thing I was facing
 
William Mariager
William Mariager
At the moment the API is in flux, since I'm still developing and don't have any 3rd party access yet, but it's coming soon, so I kinda need to decide what I want to support.
 
Tom V
Tom V
8:47 PM
We now need to call the api to see "does this order, or its lines, have an attached scanned document"
 
William Mariager
William Mariager
Hah, what a coincidence. :P
 
Tom V
Tom V
while we would like "can you return a list of all the sales orders that have documents"
that would require a (possibly breaking) api change that needs to go through a lot of testing before it can make a released build on the "vendor" (another team) side
a direct query in the db would just solve that in an hour or 2 but have the limitation of them not changing the db instead of just making sure the API works in the same way between minor version
 
William Mariager
William Mariager
At least I'm lucky I don't have anything big depending on it yet. But I'd really like to avoid problems in the future. :P
Yeah, my DB is possibly in even more flux than my web API, so I think web API is the best for now.
 
Tom V
Tom V
It depends as usually
 
William Mariager
William Mariager
Everything is just put together to meet a deadline. Lots of things behind the scenes that can be improved once I can start focusing on that instead.
 
ypercubeᵀᴹ
ypercubeᵀᴹ
9:02 PM
3
Q: Effect on execution plans when a table variable has a primary key

sam.bishopHaving read a great deal about the differences between temporary tables and table variables in SQL Server, I am experimenting with switching from mostly using temporary tables to mostly using table variables. (They seem to be a better fit for the types of queries I usually work with.) In these ...

sql-server optimization
 
Joe Obbish
Joe Obbish
9:13 PM
@ypercubeᵀᴹ Looks like he's about to edit his question to change it
 
Evan Carroll
Evan Carroll
9:40 PM
IT IS I, EVAN THE GREAT
 
Max Vernon
Max Vernon
10:05 PM
@EvanCarroll at this point, why not just go with "KING EVAN".
 
Evan Carroll
Evan Carroll
Because kings are rivaled by other kings.
Evan is rivaled by no one.
1
Q: Most efficient way to solve the subset sum problem in PostgreSQL

Evan CarrollI'm looking now at the subset sum problem... inspired from this post. It's an interesting problem.. Here we create two tables foo with 10,000 rows (id,rand) between 1..1,000,000 bar with 10 rows (id,rand) between 1..100,000,000 Source code to generate them is as follows. CREATE TEMP TABLE f...

postgresql sum
ANSWER IT FOR GREAT GLORY.
 
ypercubeᵀᴹ
ypercubeᵀᴹ
10:41 PM
@EvanCarroll your q has:
> Now the goal is to find whatever combinations of bar sum closest to the row in foo without going over.
shouldn't it be the other way around?
(combinations of foo sum closets to a value in bar)?
 
Evan Carroll
Evan Carroll
I think, through researching this I can answer it a few ways
I'm working on the CTE version onw
Yea, that's a typo. Good catch.
I really want a set type in Pg.
 
ypercubeᵀᴹ
ypercubeᵀᴹ
@EvanCarroll you need to reflect the change in the example, after "Ideally .."
 
Evan Carroll
Evan Carroll
Done & thanks.
 

« first day (2234 days earlier)      last day (2635 days later) »