The Heap™ – Consultancy ©®

James Lupolt

12:27 AM

@PaulWhite @Phrancis I also liked Kim Tripp's examples: sqlskills.com/blogs/kimberly/high-performance-procedures

Paul White

Sure, it's a natural optimization.

Phrancis

2:36 AM

@PaulWhite Yes, that would be a good way to put it, SQL coding "best practices"

Tom V

7:47 AM

morning

Paul White

morning

Tom V

8:02 AM

@PaulWhite do you ever sleep? :)

Paul White

zzzz

Andriy M

8:14 AM

Do I understand the term "term" in this question correctly as "one of the periods into which a year is divided at school, college, or university"?

0

Q: How can i enforce data integrity in this setup?

I have these tables: |-------------tblSchool -------------| | | | | tblSchoolYear tblTerm | + SchoolID | + SchoolID | | | ...

Or should I ask the OP to be sure?

Paul White

@AndriyM Yes.

Andriy M

@PaulWhite Thanks you.

Paul White

8:49 AM

I tried alot but am not getting any proper answer from my mind plz help — maira khan 4 hours ago

Somehow I am not convinced

Mikael Eriksson

9:05 AM

I would have a hard time with that assignment. What does even "non-clustered indexing" mean? Is it a euphemism for a heap?

ypercube

@MikaelEriksson some assignments in college/univ. are like that

I had an exam in analytic geometry in the univ. where we had to prove that certain equations would produce homocentric cycles.

After 2 hours, I managed to prove that it was a spiral.

I obviously failed the test

(3 hours, and 3 more proofs to do, I was too frustrated to even try)

Mikael Eriksson

@ypercube He he, you just made me feel like the first time a read a Paul White blog post. O-0.

I guess you were right and they were wrong right?

ypercube

Yes.

The excuse when I confronted the guy who had made the test, was like "hey, it's almost the same thing" or like that. More frustration.

Mikael Eriksson

@ypercube trying to figure out what a homocentric cycle is, no luck so far. Sounds like it kind of connects with it self on the "other" side where spirals hm... does not.

ypercube

Cycles with same center.

It may not even be a word, I'm "translating" from the grrek term

@MikaelEriksson concentric is the right term

Mikael Eriksson

9:22 AM

Ahhh, that was more google friendly :) Definitely not almost a spiral.

Perhaps if viewed from afar where the lines start to became blurry.

ypercube

Yeah, not the kind of answer you expect from a geometry professor.

0

Q: How do SQL Server perfom lock when using CROSS APPLY?

The best way I think I can ask my question is by starting with an example. Imagine these two queries : SELECT CAR.SerialNumber, TIRE.SerialNumber FROM dbo.tCar AS CAR INNER JOIN dbo.tTire AS TIRE ON ( TIRE.CarID = CAR.ID) WHERE CAR.Brand = 'Jaguar' and SELECT CAR.SerialNumber, TIRE.SerialNu...

sql-server-2008-r2

No idea how this can be answered

Mikael Eriksson

Yea I saw that. For the example given there will be no difference at all because the plan will be the same for both queries.

Mark Sinkinson

@ConcernedOfTunbridgeWells apparently I missed your drinkies invite as I was on holiday. I'm kind of around on the 28th, but on one of Paul Randal's training courses that week in London. In theory it should finish on time!

Tom V

@PaulWhite orchid textile is a large organisation = same prespam as yesterday

remember the NAA?

it's the same text

ypercube

@MarkSinkinson We were considering the 21st instead (or some of the previous days that week)

Paul White

9:30 AM

@TomV Yes I thought I'd seen it before. Hm, now where was that...

Tom V

the post where I asked is this spam? then i flagged it as NAA

then you said it was probably prespam nonsence

so maybe it wasn't spam yesterday but a question posted as an answer, or maybe it's prespam again

Paul White

Yes I do recall, I'm just trying to find the darn thing

Mark Sinkinson

@ypercube Fair enough. I think the 21st works for me

Tom V

dba.stackexchange.com/questions/43822/…

not sure if you can still see that with your diamond status

ypercube

@TomV of course he can. and al 20k+ users

Paul White

9:33 AM

@TomV Yes thanks very much for the help there :)

ypercube

So, it was probably not spam? They had just posted the homework question as an answer?

Tom V

@ypercube I guess

Paul White

Just wait until assignment #3 comes out :)

ypercube

10:17 AM

What is the benefit from flipping? — ypercube 2 mins ago

Anyone knows what's point of that part in Kejser's answer?

Mikael Eriksson

10:46 AM

@ypercube It will prevent the generated sequence number from being ever increasing and that will give you the same benefits with the sequence as you have with a random GUID in certain scenarios.

12

A: Indexing a PK GUID in SQL Server 2012

There is nothing wrong with GUID as keys and clusters in an OLTP system (unless you have a LOT of indexes on the table that suffer from the increased size of the cluster). As a matter of fact, they are much more scalable than IDENTITY columns. There is a widespread belief that GUID are a great p...

ypercube

@MikaelEriksson thnx. So it removes the congestion from one place (the end) all over the index.

Phrancis

11:07 AM

Monking

Anyone got time to look at a demo cursor? It times out on sqlfiddle and SEDE both, so I'm wondering if I'm doing something wrong. Haven't got a chance to test it on live SQL system... pastebin.com/bu52KcV6

First time doing a cursor, I followed MSDN directives to the letter, AFAIK

(I commented out the cursor logic to make sure it works, which it does without it)

Marian

you forgot to fetch data inside the cursor

Mikael Eriksson

@Phrancis Looks like you are missing a fetch next statement in the while loop

Marian

^^^

Mikael Eriksson

hehe

Marian

your while is always on :)

Mikael Eriksson

11:15 AM

and that would take a while

Phrancis

Ah, infinite loop eh?

Should while @@fetch_status = 0 be right after I open the cursor and before I fetch?

(instead of after fetch)

or wait... let me check MSDN again lol

Mikael Eriksson

declare - open - fetch - while - do stuff - fetch again - end while - close - deallocate

2

Phrancis

ahhhh

ypercube

Also have a look at Aaron's blog about cursor options: sqlperformance.com/2012/09/t-sql-queries/cursor-options

Marian

@yper, it's curser, not cursor :)

Phrancis

11:22 AM

Ah, much better :)

Paul White

Are we moving to SEDE in preference to SQLFiddle?

Phrancis

SEDE seems less buggy, at least to me

Paul White

Indeed. For SQL Server, anyway.

I was just wondering out loud.

Phrancis

OK. Just tried on fiddle, actually worked better lol. I just hate the GUI I think

Paul White

By the way, I can't help but mention that each cursor option has its place. As with everything, you need to learn the differences, and when each is optimal.

DYNAMIC cursors are best for some tasks, for example, though there is almost universal disdain for them.

Phrancis

11:27 AM

I hear cursors in general should be avoided when possible, that's about as far as I got; but this task I've been working on looks like it will require them, or some other form of loop

Paul White

Sometimes a cursor is what you need. They're not in the product just to hurt people for fun.

Phrancis

TTGTW, back in a bit

(@PaulWhite that's "Time To Go To Work")

Paul White

Thank you so much :)

Mikael Eriksson

@PaulWhite Just had a "Hanlon" moment there?

Paul White

@MikaelEriksson Ha ha, no. Cursors, even T-SQL cursors, have valid and optimal use cases. People can be dumb about using them, no doubt.

Mikael Eriksson

11:37 AM

I know. I do read your blog posts you know :)

Paul White

@MikaelEriksson You might have stopped! ;)

Besides I say stuff aimed at the world in general even when I @reply

I know it's annoying but I don't care.

Mikael Eriksson

@PaulWhite Not yet. Still waiting for the post on XQuery performance optimizations.

Michael Green

Hey Paul.

Paul White

Hey Michael.

Michael Green

How are you settling into your new bling?

And congratulations. I don't think we (the community) could have lost in that election, whetever the outcome.

Paul White

11:42 AM

It's actually pretty much as I expected. Fear of doing stuff wrong, feeling awkward being the newbie.

@MichaelGreen Thanks. And yes, I think that's true.

It is an exciting new challenge though. And that's not just an empty soundbite, either.

I debated long and hard with myself about the relative merits of the magic diamond.

I still think there are others who would have made an more natural an easier fit as new mod, but I'll give it my best.

Michael Green

Did you have a good idea of the extra tasks & tools that would come your way, should you win?

Paul White

@MichaelGreen Yes re:tasks and volume; not so much re: tools.

Also, I've never read so much FAQ and meta :)

@AndriyM Nice work.

Michael Green

Interesting. Thanks. Someday I'll systematically work my way through Meta. Just not enough hours to do everything, though.

Paul White

Isn't that the truth.

BTW I nearly stopped writing my answer to that apply/locks question when your answer appeared :)

Mikael Eriksson

11:58 AM

Any mods around?

What to do with cross posts?

0

Q: find columns with different values for different rows in same table

I have a requirement to find the columns (with values) from a table which have different value for different rows. For example, my table MARC has more than 200 fields with key as werks (plant). I have a reference plant and most of the entries in marc have values coming from the reference plant. S...

sql

-1

Q: find columns with different values for different rows in same table

I have a requirement to find the columns (with values) from a table which have different value for different rows. For e.g. My table MARC has more than 200 fields with key as werks (plant). I have a reference plant and most of the entries in marc have values coming from the reference plant. So us...

sql

Paul White

@MikaelEriksson Thanks. That's up next in my queue.

Michael Green

@Paul. Thanks. It was a bit difficult to know just how to pitch the answer. There's what was asked, what he probably meant, and what he actually needs to know. In the end I think we said much the same, only you had references and I had a six inch touch screen in one hand and a simpering child in the other!

Paul White

I was verbose, you were precise? ;) I went through the same thought process about the pitch. I think they're complimentary actually, hence didn't throw mine away.

Andriy M

Is this a more than 30-year old table that you cannot change now? — Andriy M 36 secs ago

@PaulWhite Thanks. Can't help it. You've been leading me by example all this week.

(What exactly do you mean, though?)

Paul White

Comment flag.

Andriy M

12:09 PM

Ah, that one, yes.

Paul White

@MikaelEriksson Fixed. In general, I prefer a flag (as other...) with any thoughts you have on which is the better home. It's usually easier for us to coordinate with the other site than go through migration, closing, an merging.

Mikael Eriksson

@PaulWhite Sure. "Flag as other" is a good answer to my question. Thanks.

Michael Green

12:25 PM

Good night everyone. Sleep well.

Paul White

@MichaelGreen Thanks for the chat, Michael.

ConcernedOfTunbridgeWells

1:38 PM

@MarkSinkinson Considering moving it a week forward to the 21st to dodge tube strikes. Will post something soon.

@PaulWhite Yeah, people confuse us for Australians all the time ...

Paul Vargas

2:38 PM

@MichaelGreen Huh?

I also received a notification in my inbox SE.

Marian

@PaulVargas heh, it was for the White

probably a chat fock-up

Paul Vargas

@Marian Ha! :) There is no problem. Nevertheless, I had entered the chat, I had not taken part.

ypercube

@PaulVargas When you have been in the chat room recently, you get notified when one mentions your name with @

like @Paul

Paul White

Oi! (I got pinged for that)

:)

ypercube

But there is an issue with names starting the same, like PaulWhite, PaulVargas

@PaulWhite I guess you both got pinged!

Paul White

2:44 PM

I always hit tab. Seems to work.

Marian

yeah, if you call @Paul .. they all come

:D

Paul White

@ypercube I just got pinged for the very last one by Mr Cube.

Paul Vargas

@Marian Humm... OK. :-P

Paul White

Oh hang on LOL I see what you mean.

As you were.

ypercube

yes. I was trying to exaplin to paulvargas why he got pinged.
Michael had used @ Paul (only) to ping you.

Paul Vargas

2:45 PM

@PaulWhite Are you a Java programmer?

Paul White

@PaulVargas No. Why do you ask?

ypercube

@PaulVargas It's good that our mod is not evil. You'd have to face consequences otherwise.

Paul Vargas

@PaulWhite I don't know.

But I'm a Java programmer.

Paul White

I don't think chat ping discriminates :)

Marian

here's the proof @Paul!

:P

Paul White

2:48 PM

Goodness me people!

I suppose I could just mute my volume and ignore you all ;)

Marian

sowwy :)

ypercube

@PaulWhite or freeze the room. And then keep pinging us while we can't talk ;)

Paul Vargas

@Marian You Talking To Me?

Marian

vision + skill + incentive + people - action plan == false start http://t.co/bihQpRSQ8g

Tweeted by auxesis on August 13, 2015 at 5:54 AM

Paul White

@ypercube Ha ha ha evilcube.

Marian

2:50 PM

talking to anyone who'd listen @PaulVargas :)

Paul Vargas

This has been very educational. Unfortunately, I have to work.

Paul White

This room was placed in timeout for 30 seconds; the topic of this room is "General on- and off-site discussion for dba.stackexchange.com"; - conversation should be limited to that topic.

Oh that's awesome.

Mark Sinkinson

That was tense...

Paul White

Wasn't it just?

Automatic official message and everything.

Andriy M

Very nice.

Paul White

2:53 PM

And yes very educational.

Resume silliness.

2

Marian

Phew. Thought we're forced to act professionally for the rest of our lives.

Paul White

Like we'd know how.

Mikael Eriksson

What are you doing. My phone started a count down on me.

And then I saw it was only the heap tab.

Paul White

@MikaelEriksson Timeout.

Mikael Eriksson

Ok

Paul White

2:55 PM

Wondered what it did.

We found out!

Mikael Eriksson

It was exiting.

Paul White

Our finest 30 seconds.

Mikael Eriksson

Nothing dumb was said

Paul White

Exactly.

Tim Stone

I missed an opportunity to say something during a room timeout?

Andriy M

2:58 PM

@TimStone You mean you'd be able to?

Paul White

@TimStone We could do it again, but it wouldn't be the same :(

Andriy M

Wouldn't be special

Paul White

Unless Tim has another awesome trick we've never seen...

Tim Stone

@AndriyM MOD ABUSE

Paul White

2 days ago, by Paul White

With great power comes a great lack of responsibility.

Mikael Eriksson

3:01 PM

There are the chat room mods and then the site mods and then super mods. Where does it end?

Paul White

Dev mods?

Mikael Eriksson

"It does not, there are mods all the way".'

Paul White

Mums of dev mods?

Tim Stone

@MikaelEriksson It's mods all the way down

Paul White

Ah yes Pratchett.

Mikael Eriksson

3:02 PM

+1 @TimStone and @PaulWhite

Tim Stone

Ah, you were one step ahead of me ;)

Mikael Eriksson

This was fun. I'm out.

Paul White

See ya

ypercube

it's a bit funny when a rather silly question results in 4 almost identical answers.

0

Q: Incorrect total using SUM of DATEDiff results

When i run a few DateDiff(SECOND, value1, value2) it returns a lists of values 93, 185, 1879 which totals 2157 sum with the calc and with SUM() However, when i use DateDiff(SECOND, firstValue1, lastValue2) returned 2476 A example of the problem is: declare @testTable table ( DateD float ...

sql-server

Oh, 5 now

Paul White

Equally fast guns in the West.

One died :(

Andriy M

3:15 PM

Collateral damage?

Paul White

@ypercube Six if you count the answer posted as a comment ;)

ypercube

@PaulWhite what comment?

Paul White

Not sure. Might have undergone catastrophic gravitational collapse under the weight.

Zane

I may have found the most useless peice of code ever written.

Paul White

@ypercube Yours :)

Zane

3:17 PM

CREATE TABLE #Status
	(
	[Arrow Status] VARCHAR(30) NOT NULL,
	[LPO Status] VARCHAR(30) NOT NULL
	)

	INSERT INTO #Status
		(
		[Arrow Status],
		[LPO Status]

		)
	VALUES	('In Arrow (Open Defi)', 'In LPO (Open Task)'),
			('Arrow Discrepancy (No Defi)', 'LPO Discrepancy (No Task)' )

	SELECT [Arrow Status],[LPO Status]
	FROM #Status WITH (NOLOCK)
	DROP TABLE #Status

That's standalone. It's all this does.......

Paul White

Super!

ypercube

Good they had NOLOCK

3

Zane

@ypercube Bwahahahaha! I'm so numb to nolock I didn't even notice it.

ypercube

in case someone else was trying to read from the table at the same time

Paul White

Aaron has ... you'll never guess

... a post about that

Kinda weird that sum & datediff question has no votes, up, down, close, nothing. Like we're all watching it, wondering what it will do next.

Zane

3:25 PM

I just sent the creator an email asking what the point of this was.

@PaulWhite haven't seen it.

Paul White

15 mins ago, by ypercube

0

Q: Incorrect total using SUM of DATEDiff results

When i run a few DateDiff(SECOND, value1, value2) it returns a lists of values 93, 185, 1879 which totals 2157 sum with the calc and with SUM() However, when i use DateDiff(SECOND, firstValue1, lastValue2) returned 2476 A example of the problem is: declare @testTable table ( DateD float ...

sql-server

The casing in the title bugs me.

It's like their shift key gave up after a while

ypercube

Would it be better with SUM, DATEDIFF or Sum, DateDiff ?

or Sum, Datediff?

Paul White

3:41 PM

They're all better :)

I've been sat here looking at it for ages. It's hypnotic.

"Why?"

Zane

3:59 PM

Gah. Nope. It gives me a headache just looking at it.

Paul White

Well that explains my headache at least.

ypercube

Awful

Zane

Did you not see that I removed mine.

Paul White

Funny

ypercube

5:30 PM

> put on hold as off-topic by bluefeet♦ 57 mins ago

and she says she is not a mod any more ... dba.stackexchange.com/questions/111086/…

Phrancis

Is it possible to explicitly specify fields when calling a proc/udf in SQL Server? I know in Postgres you can do something like select udf_search_name(first_name := 'John', last_name := 'Doe'); but what about TSQL?

ypercube

@Phrancis I think only with stored procedures, not with UDFs

Phrancis

Do you know what the syntax is like? Or what to look for on MSDN?

bluefeet

@ypercube Well I was never a mod on DBA

ypercube

Postgres has only one type: functions (and can be used as functions or procedures)

Phrancis

5:36 PM

Right

Paul White

I hesitate to say it but...

Aaron has a post about that

Well only sort of, but I couldn't pass it up.

Phrancis

Looks good enough, just what I was looking for

Paul White

Nah you were looking for this

@Phrancis @ypercube Named parameters work for procedures and scalar functions.

Phrancis

> A stored procedure can have as many as 2100 parameters, with each parameter having a name, data type, direction, and default value.

^^ HOLY CARP

Paul White

Yep. Doesn't say it's a good idea though.

Phrancis

5:41 PM

You guys are really awesome. I'm so glad I found this DBA site.

Paul White

Wait til you get the invoice at the end of the month.

3

Phrancis

lol

Paul White

Right I'm out of here for a bit. Have fun!

ypercube

5

Q: Which site is best for code reviews of queries?

Imagine a junior database developer / DBA that may not have an avenue to get their SQL code reviewed for best practices and such as they are learning the ropes. There is of course the Code Review Stack Exchange (CR.SE) site, which can and will review SQL if it is posted and falls within the site ...

discussion allowed-questions

Phrancis

5:57 PM

I wonder if CR people will have anything to say; guess this must indeed be a new one for DBA, no response after 24 hours

Zane

6:12 PM

@bluefeet now your powers are greater than ever!

Kin

8:39 PM

VTC please ... dba.stackexchange.com/q/111111/8783

Phrancis

9:45 PM

So, I've reached a point where I am kind of stuck...

ypercube

@Kin tempted to downvote the answer there

Phrancis

Is there a way to build the object path in the FROM clause dynamically, with the understanding that I've got 30-some [server : database] pairs I need to loop through for error checking?

Or some other way, whatever; but considering that the only things that would need to change during my cursor would be the @server and @database vars, as long as I can build the path from those?

ypercube

I don't see why you nee a cursor here.

Phrancis

I'm all ears

Though, the cursor is not what bugs me

ypercube

It seems you need a loop that iterates over a list of dbs and executes one statement or 2 per db.

Phrancis

9:54 PM

Yeah that's the idea

ypercube

So look for how to build dynamic SQL. Cirsors may br needed or not, not sure.