The Heap™ – Consultancy ©®

Paul White

5:24 AM

@TomV Ok. Ping me when you're next around.

I want to try something, but we both need to be here at the same time.

Replacement memory arrived today so I now have 16GB!

Andriy M

5:37 AM

Colours have changed back.

Paul White

80

Q: Revert new syntax highlighting

This morning I've noticed that the syntax highlighting has been completely redone. This post will cover C# in particular, but I'm sure there will be similar issues among other languages. Here's how it looks now: Here's how it looks in Visual Studio 2015 with the default theme: While in ge...

Paul White

5:54 AM

Another great design answer:

2

1

A: Normalizing a table with a field that generally uniquely identifies a row, but is sometimes null

1. Assuming that sku and item_number will always hold unique values I consider that you have come up to the answer by yourself, since an item_number is an optional attribute (or column at the implementation phase), i.e., it does not apply to all the product occurrences, therefore, from a logical...

Tom V

7:04 AM

@PaulWhite Belgium represents

Paul White

@TomV Ok. I'm going to reopen the question quickly. Please VtC as a duplicate of your suggestion.

Thanks.

Tom V

your last link wasn't my suggestion but the original :)

ah crap no

I messed up

need coffee

sorry

Paul White

@TomV Muppet. I took care of it anyway.

@TomV Closed as a duplicate of both.

Tom V

@PaulWhite I'm sincerely sorry

Paul White

How's that for a compromise?

Tom V

7:11 AM

Best of both worlds

Paul White

@TomV You have no idea how many times I got the links the wrong way around as well, so no worries.

I wasn't absolutely sure the multi-dupe close would work with mod/gold VtC involved.

But it did, so that's good to know.

Tom V

That was my whole idea. I didn't mess up but I wanted you to test the feature

Paul White

I think I should just stop trying to follow this conversation and just be happy with the outcome.

I think I see what you mean though :)

Marian

7:37 AM

@ypercubeᵀᴹ they drew some attention, indeed :D.

ypercubeᵀᴹ

@Marian -6

Marian

@ypercubeᵀᴹ you rebel!

Paul White

8:15 AM

-8

Mark Sinkinson

10:11 AM

Has anyone ever turned the Parameterize Queries option on in Azure?

Gives a really cool 'This is how awesome it will be' graph

But as it's a preview, is there anyone with evidence it does/does not do at it states?

Paul White

10:47 AM

@MarkSinkinson Azure SQL Database? No. Is it just forced parameterization with a UI and a more user-friendly name? I'd approach it just as I would the box option, if so.

I don't always make major configuration changes based on an Azure Wizard, but...

Mark Sinkinson

@PaulWhite Yeah, Azure SQL Database. I can't seem to find documentation on it

Turned it on in a Test environment though, so I guess we'll see what happens!

I was probably a little harsh...

Paul White

@MarkSinkinson Sounds exciting. Do report back on what you find. Does SQL Database even expose a forced param setting? I used to have a SQLDB instance, but I seem to have dropped it at some stage because it frustrated me and I became bored with it.

@MarkSinkinson Oh ha ha. That's good. I hadn't noticed the typo.

Mark Sinkinson

The Query Store advisor in Azure is basically suggesting this would improve performance based on the existing load on the database. It believes 'Non-parameterized queries are causing performance issues'.
I don't do too much work with Azure SQL DB though, so haven't really dug deep.

Paul White

11:26 AM

What's the use of (price > -1 OR price <> 999999.99) condition which appears twice? Seems useless. — ypercubeᵀᴹ 9 mins ago

@ypercubeᵀᴹ You can never be sure the database will notice a predicate.

ypercubeᵀᴹ

The default is -1 on that column weird values for a price.

Paul White

@ypercubeᵀᴹ I'll take everything you have at $-1

Andriy M

I wonder if they realise that the condition will always evaluate to true (unless price is null, of course).

ypercubeᵀᴹ

@AndriyM Spock mentioned this in his answer

Andriy M

@ypercubeᵀᴹ Oh, indeed.

Marian

12:02 PM

@MarkSinkinson ha, nice!

why are those millions of backticks necessary?! :/ don't they have a decent programming env?

Max Vernon

Is this true ( the two answers bit)?

dba.stackexchange.com/questions/136235/…

Michael Green

Which two answers?

ypercubeᵀᴹ

@MaxVernon I'd say it's not bad to have 2 answers if they are different enough and not just variations and especially if they are quite long.

Paul White

12:26 PM

@MichaelGreen 10K needed because the answer is deleted. Context above.

@MaxVernon What @ypercubeᵀᴹ said.

Michael Green

@PaulWhite Thanks.

@ypercubeᵀᴹ Agreed. Different approach => different answer.

Paul White

67

Q: What is the official etiquette on answering a question twice?

When is it okay to post two answers to one question?

discussion questions answers etiquette multiple-answers

Ooo an undelete vote. Don't see them often.

ypercubeᵀᴹ

in Discussion on answer by Ziggy Crueltyfree Zeitgeister: Group daily schedule into [Start date; End date] intervals with the list of week days, 2 mins ago, by ypercubeᵀᴹ

@VladimirBaranov for the record, I disagree with your comment in the (now deleted) other Ziggy's answer. If the answers are different enough (different approach to the issue) and quite long (like this case), it should be the answerer's choice whether to have them as one or 2 answers. Nothing wrong I see personally with 2 answers in such cases. Some advantages is that others could upvote one approach and downvote the other. And with a bounty, you may want to give it to one of the approaches. It will be clearer to future readers which one you chose.

Paul White

My own view is that it should be fairly unusual to see multiple answers by the same author, but this does seem like one of those times where it would be appropriate.

ypercubeᵀᴹ

@PaulWhite Yes, I agree, It's not common practice but the situation seems appropriate.

Max Vernon

12:51 PM

@ypercubeᵀᴹ that's what I was thinking too. I don't like two answers in one since you cannot upvote the correct or best answer.

Paul White

To meta!

Max Vernon

You can imagine my incredulity when, just now, I saw tables named dbo.DTA_* in one of our databases. Database Tuning Advisor, really???

@PaulWhite two, now.

@PaulWhite are you writing that question up, or do you want me to ?

morning, Mikael!

or, good afternoon, as it ~~were~~ is in Europe!

Paul White

@MaxVernon No. I am busy writing an answer.

Mikael Eriksson

@MaxVernon Sure we are in Europe and it is afternoon :)

Max Vernon

:-)

Paul White

12:58 PM

Anyone can ask on meta if they want to gauge the local community's opinion on this topic.

Mikael Eriksson

@PaulWhite I think a comment of mine was deleted and since you are the number one comment-deleter perhaps you know something about it. I guess it was because of the bad code but I'm not sure.

On the "Group daily schedule into" Q.

Paul White

@MikaelEriksson No it was part of an obsolete chain now that GP has posted a full answer. I assumed you would have the pastebin link saved. Can get it for you if not.

Mikael Eriksson

Oh no. I just wanted to know if I offended or did something wrong. I guess not then. The code was incomplete at best.

Paul White

@MikaelEriksson Not at all. Its moment had simply passed.

Mikael Eriksson

I understand, thanks.

Paul White

1:02 PM

Gee what is it with me and apostrophes recently!

Shaneis

People need to experience the joy that is apostrophes??

Tom V

More trace flags for the win

8 set globally (although I admit most look harmless)

There's even a 7.0 flag

Master Database

1:18 PM

1

Q: Are two answers by a single person on one question A Good Thing™?

I know there is an "official" StackExchange meta-post already concerning this question, however I think it would be suitable to have our own post that reflects our position. This really excellent question has provoked a number of excellent answers, one of which was self-deleted after the OP req...

discussion

A_V

1:34 PM

Hello

Paul White

1:47 PM

Hi

Max Vernon

If I create tables and stored procs in MSDB does that affect warranty?

Paul White

Technically, I think so, yes. But people do it.

-1

A: Need help improving sql query performance

ORDER BY in the sub query is what's making it slow. ORDER BY makes query slow since you need to order (sort) all the objects in the query results. It is in order of N Square if you don't have an index, and N log N if you do. And you don't even need to order by all of it. What you need is onl...

Substantially edited by the author following the downvote and comment feedback.

Marian

2:19 PM

@PaulWhite nice, and that turns a d-v into an u-v

A_V

2:42 PM

can someone briefly describe me what the full-text search service is for ?

in sqlserver 2000

I know that it's a service that searches full text but not much further beyond that

Paul White

@A_V Did you read through the documentation?

A_V

most definitely not, I just came out of school and I'm currently reading on oracle docs. Planning to get through sql server once I'm done

Mark Sinkinson

@A_V out of interest, why on earth are you still using SQL Server 2000?

A_V

still, I must debug some sqlservers in the mean time

@MarkSinkinson excellent question, to which there is no answer other than "at some point we will upgrade"

@PaulWhite oh well that's fairly short, expected some 20-page thing

Paul White

@A_V It's the top-level page, expand the bar on the left :)

A_V

2:48 PM

that applies to all other versions, right ?

Paul White

@A_V No.

A_V

of course it doesn't why did I even ask, okay that's more like it when I expand the bar.

Paul White

No worries. Seems a bit unfair to ask someone just out of school, with no experience of FTS to 'debug" it.

A_V

well, I sure can try !

Paul White

Ah the enthusiasm of youth :)

3

Tom V

3:09 PM

@PaulWhite thanks for the heads up

@PaulWhite especially SQL 2000

Paul White

Yep

swasheck

3:47 PM

must've been some bird

Tom V

It took them that long to scrape off Santa?

swasheck

:)

Master Database

4:12 PM

0

Q: Data Analyst Questions

From what I've seen, this site seems to be all about data, but there's a stigmata about asking questions not directly related to administration of those databases. As an analyst, is this a good place to ask my questions about issues I'm having interacting with and analyzing my database, or shoul...

discussion

Zane

4:30 PM

Dev wants to know why his package doesn't work..... Sends me a query that doesn't even parse....

bluefeet

there are so many inappropriate things to say about his non-working package :O

6

swasheck

don't pull the thang out, unless you plan to bang

that was the first thing that popped into my mind

outkast

Tom V

give me just a minute while I fuck the world

Zane

4:47 PM

@bluefeet This is why SSIS is the greatest Microsoft product.

Package jokes are the whole reason I'm in this industry.

bluefeet

:P

ypercubeᵀᴹ

5:30 PM

Quoting is so complicated in (dynamic) SQL.

If only we could use Python syntax.

Zane

5:41 PM

What's the Python Syntax?

I don't even dabble in actual programming.

Max Vernon

@ypercubeᵀᴹ no kidding. Trying to explain it is also complicated!

like why does this work:

SELECT ASCII('''')

but not

SELECT ASCII(''')

Tom V

@ypercubeᵀᴹ Anrything becomes complicated in dynamic SQL

Max Vernon

@TomV apparently including spelling "anything" ;-)

Tom V

@MaxVernon oops I edited everything into anything and failed

Max Vernon

@TomV "Anrything" is now the new "everything and anything" :-)

Paul White

5:55 PM

The Curse and Blessings of Dynamic SQL

Max Vernon

^^^ the definitive volume on that subject!

wBob

Erland is speaking at SQLBits!

Paul White

Yes he finally starting speaking.

Zane

That webpage hurts my eyes.

Paul White

@Zane That's why you stay dumb :)

Max Vernon

6:02 PM

I love that he opens with this nugget: "Understanding dynamic SQL itself is not difficult. Au contraire, it's rather too easy to use. Understanding the fine details, though, takes a little longer time"

just a little longer time

Zane

I didn't say I wasn't going to read it. I'm just going to have to print it out or something.

Max Vernon

print it out so you can say you read it.

Tom V

@MaxVernon An advice I'll follow

Max Vernon

@TomV print it out in 6pt text, then tack it to your cubicle wall so you can point developers at it when they ask you why their dynamic SQL CRUD proc no longer works.

Tom V

I'm glad I don't live in a cubicle and I don't have to deal with dynamic SQL

3

I deal with Dynamics parentheses

Paul White

6:07 PM

@Zane Ok sorry I couldn't help myself there.

Zane

I thought it was a good bit.

Paul White

@TomV You had me at "Dynamics"

Tom V

@PaulWhite why aren't I surpised :p

Max Vernon

6:54 PM

@mod - how many bogus questions requiring a trigger are we gonna see today? At least 3!

Someone trying to game the system?

ConcernedOfTunbridgeWells

Come now, Dynamics's database schema isn't all that bad.
ZOMG! The GUIDS! The goggles don't work. Nooo!!! The GUIDS!!!
ia ia Cthulhu Ftagn...

5

Max Vernon

Zee goggles, zay do nuzzing!

ConcernedOfTunbridgeWells

Ph'nglui Mglw'nafh Cthulhu R'lyeh wgah'nagl fhtagn.

ia cthulhu r'lyeh fhtagn

ia ia Cthulhu Ftagn

Which reminds me of an oldie but a goodie ...

4427

A: RegEx match open tags except XHTML self-contained tags

You can't parse [X]HTML with regex. Because HTML can't be parsed by regex. Regex is not a tool that can be used to correctly parse HTML. As I have answered in HTML-and-regex questions here so many times before, the use of regex will not allow you to consume HTML. Regular expressions are a tool th...

QOTD: Even Jon Skeet cannot parse HTML using regular expressions.

Max Vernon

7:13 PM

@ConcernedOfTunbridgeWells ain't that the truth.

ypercubeᵀᴹ

@ConcernedOfTunbridgeWells Haha. Firefox crashed while visiting that page!

Paul White

7:25 PM

@MaxVernon On it.

@MaxVernon May be part of a wider problem

If you're seeing this on CS or CS Theory, I believe those sites have been subject to a coordinated attack by students from an Indian university. They have been posting terrible questions that are immediately answered by other students (usually using plagiarized content) and then they are voting in a tight ring, inflating posts by 8 or more votes. They just tried to pull this on Stack Overflow, and they'll find they no longer have accounts there. New accounts from that university should now be blocked from posting for a while. I've informed the other moderators. — Brad Larson 15 mins ago

Max Vernon

User123 and User567 ? .

yah, just seeing that too

@PaulWhite the one case where downvotiing as much as you like makes no difference at all

Paul White

@MaxVernon Oh but it does. Various system controls react to it. That's why there isn't a bigger problem. DV is the best.

ypercubeᵀᴹ

@Won't, have a look at the situation on TCS right now. — Kaveh 3 mins ago

Max Vernon

@PaulWhite oh good. You mods get all the juicy bits!

:-)

would those questions get deleted, or?

so we should flag as low quality too I guess

"Aaaannnnddddd, they're gone!"

Paul White

7:43 PM

Nuked from orbit with maximum prejudice.

Max Vernon

South Park - "...and it's gone"

►

Paul White

I cannot stress how important votes on questions and answers are, both up and down. Question downvotes are free.

5

Well perhaps I can after all.

Max Vernon

@PaulWhite it's good to see the system working

Paul White

@MaxVernon It is nowhere near as glamorous as you think.

Max Vernon

I don't doubt it.

Andriy M

7:56 PM

Weird, just got this in the First Posts queue: dba.stackexchange.com/review/first-posts/69231

Zane

CNN just did the funniest thing I've ever seen.

They had someone having people pick who they were going to vote for in Indiana by having them put jelly beans in jars.

Max Vernon

@PaulWhite - I think these are part of the problematic questions as well... I've flagged as VLQ

0

Q: Multiple User Access over a common item using transactions

I have a question about transactions. Can multiple transactions access the same data item at the same time ? For example, if I have to book a train reservation ticket, and there is only 1 seat available, will 2 people simultaneously be able to access the same seat for bookings at the same tim...

transaction

-2

Q: How to create a view for tables

I am new to DBMS subject. I have two tables Ticket and Customer Ticket tid varchar(6) - **primary key** cid varchar(6) - **foreign key** tPrice number Customer cid varchar(6) - primary key cName varchar(6) cAge number How can i create a view to know the ticket details with customer age...

sql oracle view

Zane

They were discussing the outcome after counting the beans and followed out with "Now none of this is official"

@MaxVernon I don't get it why are they doing this?

swasheck

here's a question for the room

nevermind

i thought through it

Zane

Oh.

swasheck

8:06 PM

basically ... how would you batch an insert instead of doing one insert at a time

using a certain, unnamed etl tool that starts with "Informatica"

but the "batch" size on an etl update is essentially a commit size

Max Vernon

8:24 PM

@Zane they're gaming for unicorn points.

Paul White

8:51 PM

-10

Q: What is OOOOP And how ti works

What is OOPWhat is OOPWhat is OOPWhat is OOPWhat is OOPWhat is OOPWhat is OOPWhat is OOPWhat is OOPWhat is OOPWhat is OOPWhat is OOPWhat is OOPWhat is OOPWhat is OOPWhat is OOPWhat is OOPWhat is OOPWhat is OOPWhat is OOPWhat is OOPWhat is OOPWhat is OOPWhat is OOPWhat is OOPWhat is OOPWhat is OOP...

sql-server

How did that not get flagged?

James Lupolt

9:07 PM

So in his most-recent podcast, Ozar denies that he is @TrumpDBA

Paul White

That's exactly what Trump would say.

NReilingh

So I wrote what I thought was a pretty interesting question on the codereview site but it seemed to get approximately zero views. I think it might be better-received here, but I'd think cross-posting would be frowned upon. Any thoughts?

0

Q: Database stored procedure with a "preview mode"

A fairly common pattern in the database application I work with is a need to create a stored procedure for a report or utility that has a "preview mode". When such a procedure does updates, this parameter indicates that the results of the action should be returned, but the procedure should not ac...

sql design-patterns sql-server t-sql

Paul White

9:32 PM

@NReilingh You could cross-post if you customize it sufficiently, but posting duplicates on multiple sites at once is a definite no-no. Thanks for asking. I'll have a read now.

@NReilingh That seems entirely on-topic here. You could flag it for a CR mod to migrate. They may not, but you can ask. Say you checked with me if you like.

NReilingh

Thanks Paul!

Paul White

No worries.

Max Vernon

@PaulWhite I didn't see it ;-)

Paul White

@MaxVernon Turns out it did get flagged 4 times, but the flags raised only resulted in a close. And one solitary delete vote.

Max Vernon

Cray cray

Paul White

9:36 PM

It stuck out as I was checking recent posts for stuff from the problem users mentioned earlier. It's unusual to find one that hasn't already been nuked. Hence my surprise.

Max Vernon

Thank god for the Community Mod!

I mean I'm pretty sure you do actually sleep sometimes!

Paul White

Yes Community would have deleted it eventually.

Oh I see. Ha.

I was heading to sleep when I got your @mod message. Things escalated quickly.

wBob

@NReilingh I would set the NULL / NOT NULL property on the temp table. I don't tend to see duplication in procs as that much of an issue, but the savepoint thing seems a bit over-complicated, and possibly "deadlocky" to coin a phrase, INSERT/UPDATE/DELETE requiring higher locking levels than plain SELECTs. Maybe : )

@NReilingh One option might be to do SELECT ... INTO first into a temp table, then if it's preview mode, just select from that table, if not, complete the INSERT|UPDATE|DELETE with a join to the temp table on the primary key. No duplication of code, but some possible duplicate of effort on the dbengine's part; still you've just warmed the cache : )

Paul White

There should be a special badge for answering a question before it is posted :)

wBob

I could of course be talking nonsense and undoubtedly will be corrected!

@PaulWhite Eejit badge

2

Paul White

9:49 PM

The repless badge?

wBob

Heh heh, I could always copy-n-paste if the OP likes it; I'm in preview mode : )

Paul White

Aha! Excellent!

wBob

I wondering if a UPDLOCK is required in my suggested pattern, might be just as "deadlocky" without?

Paul White

I can't say. Too busy dealing with janitorial duties right now to even look at it properly.

I hope CR do send it over - it seems interesting.

wBob

@NReilingh Consider setting the column to a primary key if possible on both the temp table and table variable, or at least the NULL/NOT NULL. Let us know if it gets migrated. thanks.

Paul White

10:52 PM

2

Q: Database stored procedure with a "preview mode"

A fairly common pattern in the database application I work with is a need to create a stored procedure for a report or utility that has a "preview mode". When such a procedure does updates, this parameter indicates that the results of the action should be returned, but the procedure should not ac...

sql sql-server t-sql

@wBob ^ Has arrived from CR ^

Transcript for

The Heap™ – Consultancy ©®