The Heap™ – Consultancy ©®

8:58 AM

I think this deserves more attention:

1

A: How often will a FOR EACH STATEMENT trigger execute if the operation is caused by an FK constraint with UPDATE CASCADE?

Foreign key constraints are currently implemented with special internal triggers. All of them are run FOR EACH ROW. Note that these are implementation details that can change, so don't rely on it. But the basics have not changed over the last couple of major versions, so major changes are unlike...

ConcernedOfTunbridgeWells

10:11 AM

@swasheck I don't even have rock hard abs. More of a one-pack.

2

JNK

12:54 PM

@ConcernedOfTunbridgeWells We call that a pony keg

Royi Namir

1:14 PM

Hello. I'm having trouble getting rows in my full text search query. Is it possible to get help ? thank you

Lamak

@JNK Hey, you're back

Royi Namir

with a new icon i believe

I remember a red dot or something

JNK

@Lamak trying to be

Been busy with actual work stuff recently

Lamak

@RoyiNamir man, that was ages ago

JNK

@KrisGruttemeyer same here.

Lamak

1:20 PM

@JNK you should stop that bad habit

JNK

yeah that was a very long time ago

Royi Namir

at least I still have memory

:-)

Kris Gruttemeyer

@JNK She's a beast, really. Also, Brent O training was pretty awesome, you said you went a while back right?

billinkc

Gimmie da codez stackoverflow.com/q/30483663/181965

JNK

@KrisGruttemeyer yeah I went to what I think was the first one

in Atlanta in like 2012 or 2013

Lamak

1:27 PM

oh, the giant deformed head is back

4

Kris Gruttemeyer

@JNK Very nice, learned ALOT. Definitely worth it.

Lamak

@RoyiNamir it seems to me that you are using an english dictionary for your fulltext index?

maybe that's the issue. But then again, I'm an ignorant about it

Royi Namir

@Lamak How can I check it ? as you can see - even english word wasn't found

'"aerSwy*"'

Lamak

@RoyiNamir aerSwy doesn't seem like an english word for me

you might be confusing unicode-non unicode strings with "english"

Royi Namir

it is hashed word. is it matter ?

so love is stored as evol or something like that

Lamak

1:32 PM

@RoyiNamir well, my understanding is that full text is based on real words, that's why it uses a dictionary

Royi Namir

oh....

Lamak

> Full-text search supports almost 50 diverse languages, such as English, Spanish, Chinese, Japanese, Arabic, Bengali, and Hindi

Royi Namir

Im searching for a name with eng meaning.....( if it's true im f* stupid)

Lamak

so, no point on using full text on hashed words

> Full-text queries perform linguistic searches against text data in full-text indexes by operating on words and phrases based on rules of a particular language such as English or Japanese. Full-text queries can include simple words and phrases or multiple forms of a word or phras

bluefeet

morning or afternoon where ever you are. Better @ConcernedOfTunbridgeWells?

Lamak

1:39 PM

good morning

Kris Gruttemeyer

@ConcernedOfTunbridgeWells Well played, worthy adversary

ConcernedOfTunbridgeWells

Morning - afternoon really.

@bluefeet London, Gracechurch Street to be precise.

In the Square mile, where there are more pubs than insurance companies - just.

Royi Namir

@Lamak nor is an english word but still : i.imgur.com/v33L6eN.png

ypercube

@RoyiNamir probably a "stop word"

Words like "a", "the" are deliberately excluded from full-text results

Royi Namir

but the list is empty :

?

found guy

checking...

nope , still not working for guy

Lamak

1:46 PM

@RoyiNamir why aren't you using CONTAINS(vcn.*,'guy')?

the '"guy*" means that the word has guy as a prefix

Royi Namir

still doesn't work : i.imgur.com/ZfvDmHl.png

ypercube

SELECT *
FROM sys.fulltext_system_stopwords
WHERE stopword LIKE 'guy%' OR stopword LIKE 'way%' ;

to check.

Royi Namir

ypercube

Yep, 1033 is the English I think.

I can't explain why the 'guy' doesn't work.

Lamak

it is

ypercube

1:52 PM

Oh it is. So, everything is fine then.

Royi Namir

what's fine ? i dont understand

guy is a valid word.

can you please explain ?

Lamak

frankly, even if "guy" is a word, your whole scenario is unsuitable for full text search

Royi Namir

I dont understand how come everything is fine then , what am i missing ?

Lamak

@RoyiNamir he meant that you are using the english dictionary. Also, you really really need to do some research first. I mean, it's clear that you (as me too) just don't know enough about full text search on SQL Server to start implementing it

Royi Namir

Ive been working on if for the last 8 hours. so I did a little research 9 as much as you can do in 8 hours). you can see it from my question is SO. also , in production environemnt - it does work. but when I created the FTS in my local computer - this is where I got stuck

Thanks for help BTW

Lamak

2:01 PM

@RoyiNamir I am talking having already read your SO question. The fact that it's a well asked question (I upvoted it), doesn't mean that it shows research about full text index. There's the fact that you didn't know it works with a dictionary, that the CONTAINS query you were using is meant for prefixes, etc. I don't mean this as an attack or anything like that

ypercube

Perhaps the full text index hasn't updated with the new row (that has the 'guy') ?

Royi Namir

yes I know thank you :-)

ypercube

@RoyiNamir Nothing, I misundestood.

Royi Namir

IN PRODUCTION IT IS WORKING

ypercube

Full text indexes are updated/refreshed in the background.

Lamak

2:03 PM

@RoyiNamir maybe they have the right dictionary

ypercube

For example, if you first create a full text index on a big table, how much time does it need? Can you check with a big table in your test database?

Royi Namir

ok - How can I reset all my dictionaries ?

ConcernedOfTunbridgeWells

As a corollary, there are 189 pubs in the city of London (the Square Mile).
http://www.citypubs.co.uk/publist.html

ypercube

@RoyiNamir I'm not sure.

You could also check the properties of the full-text index, to see if it's fully populated (or sql-server is still working on it.)

msdn.microsoft.com/en-GB/library/cc879321.aspx

Kris Gruttemeyer

4:20 PM

Anyone else have to fight those Severity 020, connection pool reset errors?

Zane

No sir.

swasheck

@RoyiNamir uhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh ... i'm not sure how any of this is working since you're not specifying unicode strings (though that may be implied in the FTS, but i dont think so)

Zane

5:15 PM

Before it goes to prod it goes through Plan Explorer.

This is my mantra.

Kris Gruttemeyer

5:41 PM

Rightfully so

bluefeet

when do you start your new gig @Zane?

Zane

5:53 PM

Technically 2 weeks from now is when I get paid but I won't be transitioned until they can get a new ETL person in. That team is backlogged as hell and completely understaffed.

I got promoted another guy quit. 2 people are on a large project and 1 guy is on paternaty leave so all in all theres one guy left to handle requests.

Zane

6:11 PM

@bluefeet how about you?

James Lupolt

Anyone else downloading CTP2?

bluefeet

@Zane June 15th

Zane

Nice.

Kris Gruttemeyer

6:53 PM

@JamesLupolt Doesn't look like us common folk can download it until tomorrow

swasheck

ugh. stupid wordpress plugins

cant handle code

Lamak

@swasheck like your devs

swasheck

heh

Lamak

you should start calling them "wordpress plugins"

would be nice

swasheck

i dont think that they'd get it

Lamak

6:59 PM

that's the point

swasheck

not for me

i want them to know that i think that they're idiots

Lamak

ah, so no passive aggressiveness

(I have the feeling that I used too many double letters there)

Andriy M

just the right number

billinkc

but in the wrong order

Lamak

@billinkc how is it written?

Andriy M

7:06 PM

yeah, the order is always hard to get right, they are all so alike

@Lamak it's written "passive aggressiveness", and you wrote "passive aggressiveness" (the double letters were in the wrong order).

billinkc

I just like to make you question your mastery of the English language

Lamak

@AndriyM damn, I don't see the difference

billinkc

swasheck

you wouldnt

billinkc

Which is labeled "hatersgonnahatehatehate.png"

Lamak

7:09 PM

@billinkc so glad you are back

billinkc

Followed by InstantKarma.png

Andriy M

@Lamak we were just pulling your leg, sorry.

swasheck

@billinkc it's gonna getchya

click debug ... just to see what happens

Lamak

7:22 PM

@AndriyM I thought @billinkc was doing it, but didn't expect it from you ;-), that was a good one

Andriy M

@Lamak Technically, I merely explained what he said a little more in detail, but that still makes me a participant in the joke, of course. Especially since I probably confused you more than Bill did.

Lamak

@AndriyM It's not @billinkc that confuses me, it's the fact that he exists

billinkc

I bet you got all sorts of hot and bothered at the prospect of me pulling your leg

Lamak

eeewwww

Lamak

7:52 PM

why, just why is it so hard for some people to explain themselves?

0

Q: SQL server 2005... sites hacked, when doing google search it redirects

i'm in serious need of assistance. I have MS SQL Server 2005. When i pull up a site in the browser, let's say, www.abc123.com, it shows the site just fine. When i do a search in google for abc123 it shows me the site's link/url. If i click on it from there, then i'm redirected to a totally differ...

billinkc

Restore to a backup and hope that your backup predates your site getting pwned — billinkc 20 secs ago

Lamak

basically

billinkc

He types as he knows there won't be a backup

Or call google

Lamak

backup doesn't predate issue :-\ — Damien 54 secs ago

billinkc

> Hire a professional because you're clearly in over your head

Yeesh, maybe it's naptime

Andriy M

8:12 PM

stackoverflow.com/questions/30491793/…

And the question is ~~tagged sql sql-server~~ removed

ypercube

stackoverflow.com/questions/30491793/…

> Thanks guys for all your help... turns out it was a file that was causing the issue. Thanks for being helpful! – Damien 3 mins ago

Andriy M

Ah, turns out my link was wrong. That was the comment I meant to link to. The link didn't onebox, so I didn't notice.

Lamak

@ypercube jeez

ypercube

@AndriyM yeah, deleted questions (and comments on them) do not onebox

Your link was fine.

Andriy M

@ypercube It was to the question itself, and I meant to link directly to that comment.

Doesn't matter now.

Kin

8:24 PM

Anyone encountered this error while installing SQL Server 2016 CTP2 ?

billinkc

You either need to unselect Polybase or install the java runtime engine

@Kin and enjoy patching every hour

swasheck

Looks like you're trying to run Java. Have you patched yet? Have you patched yet? Have you patched yet? Have you patched yet? Have you patched yet? Have you patched yet? Have you patched yet? Have you patched yet? Have you patched yet? Have you patched yet? Have you patched yet? Have you patched yet? Have you patched yet? Have you patched yet? Have you patched yet? Have you patched yet?

Andriy M

How about now? How about now? ...

Kin

@billinkc thanks .. and good spot :-)

moving on

Paul White

An error encountered only by people who select to "install everything".

swasheck

8:30 PM

polybase is for hipsters

billinkc

Happens to the best of us...

Paul White

Personally I'm not planning to test Polybase/Hadoop/whatever, so I didn't install it.

swasheck

@billinkc ... of whom you're not a representative sample

3

@PaulWhite no? i was looking forward to some SQLKiwi PolyBase optimizer deep dives

Paul White

No chance. Too much other good stuff to play with.

swasheck

ah. too amped for StretchDB i take it

Paul White

8:33 PM

Not talking about features until CTP 2 is available for public download, just in case.

swasheck

odd. does this not work for everyone?

microsoft.com/en-us/evalcenter/evaluate-sql-server-2016?i=1

Kin

@PaulWhite indeed ... but I unchecked it .. and it moved on ..

Aaron Bertrand

You know, because I haven't provided enough information already

Can you please also give an example of how to accomplish this without a trigger? — Jyrkka 1 min ago

Paul White

2 hours ago, by Kris Gruttemeyer

@JamesLupolt Doesn't look like us common folk can download it until tomorrow

swasheck

@KrisGruttemeyer does this link work for you? microsoft.com/en-us/evalcenter/evaluate-sql-server-2016?i=1

@PaulWhite but he's from upstate new york so he has an excuse

Paul White

8:36 PM

No doubt.

swasheck

@AaronBertrand the primary takeaway from this is that you get an upvote on your answer from me

Aaron Bertrand

Well, thanks, but I'm more just dumbfounded by the demand for spoon-feeding

Paul White

Documentation for SQL 16 CTP 2 seems to be available.

Aaron Bertrand

@PaulWhite yes, and I have noted (in limited observation so far) that it is very thorough for documentation accompanying a CTP.

Paul White

Indeed!

Updateable nonclustered columnstore, non-parallel batch mode, live query plans, batch mode sort...lots of engine stuff for CTP 2.

Aaron Bertrand

8:44 PM

parallel insert / select!

swasheck

@AaronBertrand they're building better idiots

@PaulWhite WTF? live query plans?

yay

Paul White

@swasheck Live Query Statistics

Aaron Bertrand

@swasheck brentozar.com/archive/2015/05/…

swasheck

be still, my beating heart

i feel sad that we've just upgraded to 2014

buyer's remorse

Aaron Bertrand

You can build your own - the view is based on a DMV that was added and gets populated in 2014. :-)

Thanks @AndriyM

Paul White

8:50 PM

@AaronBertrand Yes and it's very typical V1 MSFT

swasheck

@AaronBertrand oh that one. yeah. that also gave me a sadface as well. it'd be really nice to have a live query statistics on anything that's currently running and not just something that's running with statistics

Aaron Bertrand

@swasheck it's ah-spensive

swasheck

indeed

> This page has more animated GIFs than a Geocities page from 1995. That said, this is an exciting feature that keeps SSMS at the top of the client tools across all DBMS platforms.

Andriy M

@AaronBertrand Seems like another one who can't "chew".

swasheck

verdammtes nach mal

pulled the trigger too soon

> Load-balancing of read-intent connection requests is now supported across a set of read-only replicas. The previous behavior always directed connections to the first available read-only replica in the routing list. For more information, see Configure load-balancing across read-only replicas.

Paul White

8:57 PM

Also, hooray for new memory grant, threads & DOP information on sys.dm_exec_query_stats

swasheck

@PaulWhite pardon my ignorance ... so the big difference over against sys.dm_exec_query_memory_grants is going to be historical information on memory grants?

Paul White

Yes.

Crikey Operational Analytics is huge too. Synchronized columnstore and in-memory rowstore.

Did Microsoft hire 10,000 extra developers or something?

swasheck

that's insane

Aaron Bertrand

At least 12

swasheck

let's see how things shake out in reality, though

Aaron Bertrand

9:09 PM

Still can't figure out how to use dbo. to reference a table, though.

swasheck

you can run both workloads on the same table. unfortunately, there's a bug that completely drops the columnstore when you attempt an update

Aaron Bertrand

shhhh that doesn't exist

swasheck

uhhhhhh ... didnt think that it did. i was trying to come up with something silly

like, "you can't write data to the columnstore index"

Aaron Bertrand

I haven't played with it. You find a lot more bugs than I do.

Paul White

I suppose they would always be able to add new features quickly to the relatively new codebase that is columnstore and Hekaton, but it's still impressive.

swasheck

9:12 PM

ok. i'm heading out. we're having a 2008->2014 celebration at a bar ... drinks are on "the man" so i've gotta take advantage

Aaron Bertrand

It is. I really am more impressed about the state of the docs, though.

It's never been a strong point, never mind during CTP stages.

Paul White

That is truly amazing, I agree. Much better than previous experiences.

swasheck

@PaulWhite impressive, to say the least. but as with any new feature, it should come with its own portion of comedy

Paul White

There's bound to be something.

Paul White

9:32 PM

Ooo! New MIN_GRANT_PERCENT and MAX_GRANT_PERCENT query hints.

Aaron Bertrand

9:51 PM

Wasn't my down-vote, but might I offer a couple of suggestions: (1) You suggested NOLOCK and explained about two things that are important to you, but failed to warn any reader about much more important problems - hint: deadlocks & replication are not your biggest concerns (2) Being new to this site, please review this post and discard the expectation that you will always (or maybe ever) get any reasoning behind up-votes or down-votes. — Aaron Bertrand ♦ 2 mins ago

@PaulWhite whoa I hadn't even heard about that one - silly TAP saving some of the good news for the peons

(I'm finding that two things are increasing on our site: (1) SO-type questions and lots of spoon-feeding requests and (2) whining about down-votes.)

Paul White

@AaronBertrand I always check for OPTION (PARALLEL_PLAN) just in case :)

Aaron Bertrand

Ha ha

Kris Gruttemeyer

11:12 PM

@swasheck That one works. Being from upstate NY clearly means that I shouldn't be allowed to Internet by myself. Haha

swasheck

11:28 PM

I remember my first time using the internet.

ConcernedOfTunbridgeWells

@swasheck ... reading usenet on a Wyse terminal off a Vax in 1990. And we liked it!

None of your fancy world wide web. We had email, news and FTP and we used to *dream* of gopher. Uphill both ways.
Now get off my lawn.

Actually, it might have even been a VT-220.

Transcript for

The Heap™ – Consultancy ©®