The Heap™ – Consultancy ©®

Evan Carroll

1:53 AM

0

A: Community Promotion Ads - 2017

ErikE

4:03 AM

I very much enjoyed Joe Obbish's answer: dba.stackexchange.com/a/164901/6236

Evan Carroll

4:26 AM

hrm...

a lot of words in that answer.

ErikE

@EvanCarroll That's the most salient thing that comes to mind when you look at it? The #1 important feature of that post?

Evan Carroll

Yes.

=(

I mean, the answer is either (a) because there is no index. Or.. alternatively, (b) because you're not using a real database.

I'm not sure how SQL Server answers are always so long, or what clustering has to do with it. Or, why we're calling "flow distinct" an operator when there is no operator, it's just a query plan. And, that optimization fence with the window function. That's fucking hideous. Baarrfffff.

SELECT DISTINCT TOP 10 LAG(VAL, 0) OVER (ORDER BY (SELECT NULL)) AS ID What the crap is going on there.

This kind of shit really makes my mind boggle how this is an issue. On PostgreSQL, CREATE TABLE foo AS SELECT md5(random()::text) FROM generate_series(1,10000000); That's 10 million md5s!!! Then we do CREATE INDEX ON foo(md5); to get a btree on the col. And, then EXPLAIN ANALYZE SELECT DISTINCT md5 FROM Foo limit 1;

Result..

                                                                  QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..0.64 rows=1 width=33) (actual time=0.017..0.017 rows=1 loops=1)
   ->  Unique  (cost=0.56..796657.45 rows=9782919 width=33) (actual time=0.016..0.016 rows=1 loops=1)
         ->  Index Only Scan using foo_md5_idx on foo  (cost=0.56..771657.45 rows=10000000 width=33) (actual time=0.015..0.015 rows=1 loops=1)

@ErikE Why is this so simple in PostgreSQL, and why does it require so many words in a SQL Server answer. Legit confused. You've got an index. You scan the index to find one value never touching the table.

Evan Carroll

4:59 AM

0

A: Why does my SELECT DISTINCT TOP N query scan the entire table?

PostgreSQL I'm not saying migration is an option, but for friendly comparison. CREATE TABLE foo AS SELECT md5(random()::text) FROM generate_series(1,10000000); CREATE INDEX ON foo(md5); EXPLAIN ANALYZE SELECT DISTINCT md5 FROM foo FETCH FIRST ROW ONLY; ...

ErikE

@EvanCarroll What I find funny is that your example "brain-dead simple query" doesn't even exhibit the characteristics that engaged the special case situation in SQL Server. You chose limit 1 on a table with more than 1 md5 in it. I'm not saying that such a query won't perform well, but in fact, your "equivalent query" was anything but!

Evan Carroll

Well, on a serious note, that's totally a fair point.

What kind of random data would suffice that point? How about the same size with trunc(random()*1000), and then I can select 20 distinct rows?

ErikE

@EvanCarroll How about you go read that thread and figure out what the special condition was that made SQL Server choose between two estimated-equal-cost operators, then you'll know what kind of random data to put in a query?

Joe Obbish

5:17 AM

@ErikE Glad you enjoyed it, thanks! I still have a bit to learn about giving good answers I think. Was difficult to figure out which details to include and what to leave out.

ErikE

@JoeObbish Well, your answer would be over the head of most random developers, but as someone who isn't fully up to speed on all the MSSQL query plan operators but is ready for that knowledge, I appreciated every step-by-step exploration of the issue, including all the different ways you attempted to force the execution plan you wanted. I learned some things.

@JoeObbish The length was appreciated, by those who have the wit to do so.

Joe Obbish

@ErikE I learned some things too

and now we have a postgreSQL "answer" too. how wonderful it is to see the community coming together

ErikE

@JoeObbish Hah! It's all unity and shared togetherness around here.

Paul White

6:17 AM

→ 16 messages moved to Trashcan

@ErikE @EvanCarroll We require everyone to be nice in chat. Make use of the chat mute options if you need to.

ArtOfCode

6:35 AM

and @ypercubeᵀᴹ, that room is private for a reason, so I can't give you access to it.

ypercubeᵀᴹ

@ArtOfCode OK. I thought it was a room we had access before. Must be some other room that messages go when room owners delete, different than when mods delete.

ArtOfCode

There are about twenty million trash rooms, that's the only private one

ErikE

@PaulWhite My apologies. I'll do better.

Andriy M

About twenty million trash rooms on Stack Exchange alone?

Paul White

@ypercubeᵀᴹ We have a choice. Sometimes messages just need to be moved to another room, sometimes they are better off in private trash where only mods and SE people have access.

@AndriyM About that :)

@ErikE Thank you :)

ypercubeᵀᴹ

6:48 AM

@AndriyM they recycle ;)

Andriy M

7:02 AM

As they are normal rooms, I guess you can move messages from one trash room to another (provided you have enough privileges). That would be a fun sport, probably.

ErikE

People at work use nullable bit columns where null and false are both false. It drives me crazy.

They also chose to violate 1NF by setting the Source of a row to where it came from plus "Historical" to indicate it shouldn't receive normal message processing (instead of putting that into a separate column).

So all code now has to be WHERE Source IN ('Xyz', 'XyzHistorical')

It drives me absolutely mental. Queries that could have been a seek get converted to a scan....

Though perhaps BETWEEN could help.

Paul White

7:20 AM

If something's not actually impossible/illegal, someone somewhere will do it.

People are never more creative than when coming up with ways to do stuff wrong.

2

dezso

9:10 AM

someone(s) seem to have serious problems (downvoting a free tool in the community ads post, huh?)

Andriy M

I think they are primarily downvoting the ad, not the tool.

@dezso If you are talking about PostgreSQL, I didn't downvote it but I don't think it needs advertising either, to be honest.

dezso

9:31 AM

@AndriyM no, I meant the Plan Explorer

I also don't think PostgreSQL needs such an ad - I am neutral in this sense, as it cannot hurt either

just it would change somewhat the platform indifferent look of the site

or whatever, but that's for sure

Dodi82

Hi everybody

Andriy M

@dezso That's it. I actually wouldn't mind promoting PostgreSQL. If it still needs any promotion (which I think it doesn't), it certainly deserves it. But I would only agree to promote it here along with promoting another (preferably equally decent) product.

And to make myself clear, I hold this position with regard to platforms but not with tools/utilities.

Paul White

9:54 AM

That white background box needs a border according to the instructions

For the elephant advert

ypercubeᵀᴹ

10:29 AM

@PaulWhite that ad needs dying

@EvanCarroll you could suggest a pg conference or the new pgAdmin4. An ad for the dbms itself is not what the Promotions is about

Paul White

@ypercubeᵀᴹ I can't immediately see why an advert for PG itself wouldn't be valid (though it may not get community support, that's a different issue). I do know the answer doesn't currently conform to the image requirements in the question.

Some might argue it was objectively not a real advert of course.

ypercubeᵀᴹ

@PaulWhite so an ad for mysql, an ad for sqlite, ...?

...would be ok? I don't know.

Paul White

@ypercubeᵀᴹ I guess so. Not an expert; just going by what's in the question body, as I interpret it. I wouldn't vote for a DBMS ad of any type.

Even SQL Server Express/LocalDB/whatever :)

ypercubeᵀᴹ

10:48 AM

and Call-me-Azure-today ;)

Jack Douglas

I like the pg ad and have upvoted it — I don't think there is anything wrong with us as a community promoting Postgres over MySQL for example (if we want to of course — I only get one vote)

@dezso I glad someone is downvoting — I'd do the same to MySQL and that would show the whole system is working the way it's intended :p

SQLite might get my vote too

Paul White

Nurse! He's out of bed again!

2

Jack Douglas

you don't like SQLite?

Paul White

We no longer explain jokes.

4

Jack Douglas

I think you are allowed to explain them briefly if you are so inclined :p

Feb 21 '16 at 20:12, by ConcernedOfTunbridgeWells

@PaulWhite I'm reminded of the first time I had an angioplasty in a posh hospital in Auckland (I still had health insurance then). They sent a rather attractive Filipina nurse called Josephine in to shave my goolies. I remember thinking: 'could be worse'

^^^ hope it's nothing to do with this but that's all a Heap search brings up

Paul White

10:55 AM

Hm. jebiyasi

2

ypercubeᵀᴹ

sounds like a pokemon

Paul White

No nothing to do with that. Though thanks for bringing back the visualization ;)

@ypercubeᵀᴹ It does!

@JackDouglas Don't mind me, I'm just in a daft mood.

Jack Douglas

No one minds you Paul :)

Andriy M

First he removes the tag, then we refuse to explain jokes to him, then he tries explaining them himself by offering us some of his gruesome guesses... where does it end?

Jack Douglas

11:19 AM

I only removed the tag because I didn't understand it

;P

Jack Douglas

11:36 AM

@Paul should I use "Server 2016 SP1 Express with Advanced Services" or just "Server 2016 SP1 Express" for DBFiddle? There only seems to be a download ink for the latter here: microsoft.com/en-gb/sql-server/sql-server-editions-express

Paul White

@JackDouglas Basic Express should be fine. The only potentially useful thing Advanced Services adds is fulltext search, and I wouldn't expect that in a fiddle thing.

Jack Douglas

thanks

Tom V

12:01 PM

Why? Because that's what it's designed to do! Seriously, think about it. You want 10 randomly-chosen values from one particular column. That's not what Select Distinct means. — Bucket123 18 mins ago

Andriy M

12:15 PM

@JackDouglas Fair enough, the explanation works for me, thank you ;)

hot2use

2:06 PM

Good afternoon.

Quick question: Do all editors lock files for editing on open? (Notepad.exe, wordpad.exe, ....)

Or put the other way: Which editor can look at the ERRORLOG file on a SQL Server without locking it?

Aaron Bertrand

Notepad should not lock the errorlog, I've done that many times

hot2use

@AaronBertrand I thought it was that way, but just wanted some verification. Would hate not having a DEADLOCK logged in the ERRORLOG because I had the file open.

@AaronBertrand Thanks for the feedback

Trying to circumvent Deadlocks on a SQL Server by tuning MAX_DOP, Cost Threshold and Index bloat, while including missing indexes to speed up SELECTS while trying not to kill the table INSERTS/UPDATE.

No chance of a code rewrite going to happen.

Found a query in the plan cache with a cost of 661. :-(

hot2use

2:36 PM

brentozar.com/pastetheplan/?id=rJTNxRFte

Tom V

@hot2use You could use that plan to scare off people aspiring to become a dba

"So what does a DBA do, it looks interesting and I'd like to become one?" "Oh, nothing special, we try to make sense out of these things"

hot2use

@TomV I'd like to laser etch it into a piece of steel and hit it round the head of one of the developers of the external company

Aaron Bertrand

There's only so much you can do with indexes etc. If people want to sabotage performance with bad code, at some point you have to fix the code.

3

bluefeet

@AaronBertrand but all code is performant!

Aaron Bertrand

My code is performanter

Tom V

2:39 PM

@hot2use Next time he comes around your office and says "there must be something wrong with SQL Server, it's slow"

Aaron Bertrand

I'm all about catering to DBAs using residential software, but I don't think we should be encouraging the use of that software or advertising to the exclusion of others. I don't know why the planner "cost you can see for a plan operator has always been based on what the optimizer estimated when the plan was compiled." But, certainly that's a unique flaw that can be solved with migration to a different platform which gets at the heart of my utilitarian suggestion. — Evan Carroll 3 mins ago

hot2use

@AaronBertrand whole-heartedly agree. It's just one of those companies, you know: "We aren't going to be changing anything soon. Sort out your hardware."

bluefeet

@AaronBertrand of course it is, you're brilliant :)

Aaron Bertrand

I'm not sure I understand why some people go so bloody far out of the way to be irritable and antagonistic.

@hot2use well, good luck with that. No offense of course, but unless you know magic...

Maybe switching to PostgreSQL will solve ALL of your problems.

hot2use

I was able to reduce deadlocks by 80% for one day, by reducing max_dop from 0 (on a 8 vcpu machine) to 6.

But then the users found out that things are running slightly better and now I'm back to 10 deadlocks a day.

I do have 146 indexes I could delete because they are never used

Aaron Bertrand

2:42 PM

6 is an odd choice (compared to, say, 4) but server-wide maxdop is like using a hammer to slice bread.

hot2use

That should speed up INSERTS/UPDATES slightly.

@AaronBertrand I'm desperate

I'm thinking of adding another 8 vcpus and setting max_dop to 8 again.

Aaron Bertrand

@hot2use Since with 4 you can at least have the chance of 2 concurrent processes not stepping on each other, but with 6 that chance is gone, I still don't think 6 is optimal.

I'd be desperate to move on if I had to try to play maxdop games to solve crap code.

hot2use

@AaronBertrand Ok. Can I read up on that?

dezso

@AaronBertrand I'll have to look into that favouriting plugin and change it to remove my favourites' posts altogether

hot2use

Is always in factors of 4?

Aaron Bertrand

2:45 PM

@hot2use It depends

hot2use

@AaronBertrand Not surprised.

Aaron Bertrand

But clearly you can see how two processes using 6 CPUs each can't share 8 CPUs nicely

hot2use

I was thinking of getting the low cost statements into the same cycle (quantum) while still allowing the parallel parts to continue.

Aaron Bertrand

Yes, but you need to consider (a) how many concurrent parallel plans do you want? (b) how many actually need to be parallel? (c) is your cost threshold for parallelism appropriate? Maybe a lot of plans are going parallel with little or no benefit.

hot2use

But the idea of having multiple processes running in parallel in parallel looks sexy.

Aaron Bertrand

2:48 PM

The default here is terrible, btw. It is stuck at a value that was appropriate for hardware in 1997

uh, ok. If you want advice let's stick to being serious ok? I have other things I could be doing.

hot2use

@AaronBertrand Sorry, was one of my responses inadequate?

I ran an analysis of the parallel bits in the plan cache and posted a screen shot here: chat.stackexchange.com/transcript/message/35576811#35576811

I have one massive 661 cost plan and some smaller ones.

Aaron Bertrand

"running in parallel in parallel looks sexy." just seemed like not a serious thing.

hot2use

Dead serious. Rephrased: Running multiple parallel parts in parallel seems like a good idea. But I think the problem is the big costly parallel processing statement which has run 257 times.

Aaron Bertrand

@hot2use So why not focus on tuning that one large plan instead of worrying about the others that have much lower costs and have been run a grand total of once or twice? Do you think there is some magic value of maxdop that will reduce that 661 cost to something manageable without tuning the code or the indexes it uses? I don't. I would set maxdop to 2, raise the cost threshold for parallelism, and then tune tune tune.

And again, I'm not saying parallelism is bad in general. But setting maxdop to 6 on an 8 CPU system does not seem wise to me. At that point it may as well be 8 or 0.

MAXDOP is used to either turn off parallelism altogether, or to better distribute parallel operations. Two processes using 6 CPUs each is not better distribution of resources.

Tom V

@hot2use Are you sure this one is involved in deadlocking?

hot2use

2:56 PM

@AaronBertrand ok. So having other low cost statements running at the same time as the costly is not a good workaround? And yes, recoding would be the best bet, but that is out of my hands.

@TomV I'll double-check

Aaron Bertrand

@hot2use If they end up going parallel, and using 6 threads, when they didn't benefit from parallelism, do you think that's helping the big expensive no-hope-to-tune query, or hurting it?

And if that big 661 cost query is being run concurrently by 2 or more users, do you think each user getting 6 cores is helping each other or hurting each other?

hot2use

@AaronBertrand hurting the big query (takes longer), but helping the smaller queries get past the big one. No?

Aaron Bertrand

@hot2use Not if they don't benefit (much) from parallelism (it doesn't speed up all queries, and sometimes en masse can have a negative impact on a workload as a whole). And not if that means they also trip on each other, too.

So in summary: I think 6 is an odd choice that I would never have tried, but play with it and see what works. Nobody here can possibly know where that magic balance is for you, except to repeat that none of this will substitute for fixing the code.

hot2use

@AaronBertrand I fully agree with you on that.

@TomV Yes. The table involved in the deadlocks is involved in this huge statement.

Tom V

@hot2use The table yes, but this statement?

hot2use

3:07 PM

@TomV No

Tom V

it's hard to tell since we can't see what the functions do, but I doubt that the fact that this query goes parallel causes more deadlocks and it would be better if this specific query used less parallelism

of course the statement could make the execution time of a transaction much worse if it takes very long

hot2use

Everything evolves around that one table register that logs all relevant modifications to other data.

Aaron Bertrand

There are plenty of other ways to optimize table access other than messing with maxdop, for what it's worth.

hot2use

FWIW : understood

Tom V

since you're looking to use a big hammer anyway, have you considered the isolation level of the database or lock escalation on that table if you really can't change any code?

Aaron Bertrand

3:10 PM

But I'm about out of time to turn this into a consulting channel. :-)

hot2use

Yes. The isolation level of 'snapshot isolation' was suggested

@AaronBertrand Your assistance and knowledge is very much appreciated, Aaron. Thanks.

Would put additional load on the already dead tempdb. 200+ms write delay

Tom V

@hot2use but could improve locking issues

and if you see lock escalation on that table propagating key to table locks you could get away with disabling escalation for that table

but it's hard to tell

ConcernedOfTunbridgeWells

@ypercubeᵀᴹ ^D100 100 moveto /Helvetica-Roman findfont 10 scalefont setfont 0 setgray (An elegant page description language from a more civilised age) show showpage

Tom V

You can polish a turd, but not without getting dirty

2

hot2use

@TomV I forgot about lock escalation. Will check. Thanks Tom.

hot2use

3:36 PM

@AaronBertrand ...and then it hit me. 6 can go both ways: 2 for the costly statement and 6 for the little statements. I'll put my Dunce hat on tomorrow.

Andy K

afternoon

Joe Obbish

if someone posts an answer that should be an edit because "he isn't able to edit the question" is the right thing to do to perform the edit for him or to try to help him edit it?

ypercubeᵀᴹ

@JackDouglas Pity. You might have used DBjeagl instead of DBfidlde! ;)

Jack Douglas

@JoeObbish do you mean if the OP self-answers? usually thats because accounts need merging

if that's the case I think it's best to do the edit and point them at the merge page: dba.stackexchange.com/help/merging-accounts

@ypercubeᵀᴹ that does sound like it might be a useful site. perhaps a jeagl chat room?

Joe Obbish

3:53 PM

@JackDouglas Not quite. See here: dba.stackexchange.com/questions/164738/…

Andriy M

@JoeObbish Perhaps they tried (and failed) to add that as a comment first. Quite possibly it just didn't occur to them to edit the question. Especially if that was meant as a response in your answer's comment thread.

Jack Douglas

wow lots of code

ErikE

4:29 PM

@AaronBertrand Thanks for your comments. I almost replied to that one's own post, to say approximately the same thing "this is only for one system so I can't see how it has broad community appeal."

Evan Carroll

HALLO EVERYONE. I RETURN.

and SO is down?

working for me

oh, my bad, sorry

bye

4:57 PM

lol, no chance of a problem here:

> Correction: PERSEUS must remain, the candidate for decommissioning is PURSEUS

Jack Douglas

@jcolebrand do you know how to configure IIS?

getting 405 Method Not Allowed when I try to POST, and I don't understand what Google is telling me

Evan Carroll

I'm out for a week. I just got the ban hammer for suggesting people migrate databases because SQL Server doesn't have EXPLAIN ANALYZE. Apparently, if you're going to butt heads with a self-proclaimed "Product Evangelist at SentryOne" you can't point out to how illogical that evangelism is. CAVEAT EMPTOR one of the moderators here is intending on using "community promotion ads" to promote a product for the company he works for, that other free and open source databases provide for free.

Aaron Bertrand

There are so many things wrong with your statements @Evan, I wouldn't even know where to start.

You did notice, for example, that the product I promote is free, yes?

Evan Carroll

You wouldn't want to start, you got me banned. You won. Just sit back and rejoice. It's a victory.

Aaron Bertrand

I did not get you banned. I had absolutely zero to do with that.

Max Vernon

5:04 PM

@EvanCarroll being childish just makes you look silly.

Evan Carroll

Vacation is good, see ya in a week dba.se.

Aaron Bertrand

In addition, SQL Server has free editions that are appropriate for a whole boatload of applications. And while our app is designed for Windows (which is, oh, roughly 100% of our market), CTPs of the next version of SQL Server currently do run on various Linux distros (I prefer Ubuntu, especially on Docker‌).

2

Max Vernon

@JackDouglas sweeet SQL Server vNext on Linux (Debian GNU/Linux 8 (jessie) - nice!

Aaron Bertrand

I will once again suggest that if you think I have done anything untoward here (and I will repeat, I did not "get you banned" - you did that, and a different moderator handled it, with zero input from me), e-mail team@stackexchange.com - they'll want facts though, and not baseless accusations.

Jack Douglas

@MaxVernon If I can get my head round IIS, the Linux version might be short-lived, but is still nice to know it works

Let's not discuss the matter in here

Max Vernon

5:14 PM

@JackDouglas yah, it's quite cool to be able to easily try it out. I have it installed locally, but it's always nice to have it handy

Jack Douglas

@Evan if you object to any moderator action you know the channels: Meta, email the team etc etc

Max Vernon

@EvanCarroll - I actually suggested that community ad, about 3 years ago or more. The product is free, and super helpful.

Aaron Bertrand

I have no idea what I am "dishing out and not taking" but whatever.

I will leave you to wallow in whatever you think I did or do that is so bad.

Max Vernon

@JackDouglas is there a reason this doesn't return a value? On my vNext on Linux it returns "3": dbfiddle.uk/…

Jack Douglas

@MaxVernon I can't figure that out

other SERVERPROPERTY items don't work either

swasheck

5:37 PM

@MaxVernon my favorite distro is Arch Linux ... however, no enterprise wishing to be taken seriously would run Arch

Tom V

5:54 PM

@EvanCarroll I flagged that comment and I suppose I wasn't the only one. I don't know ow which mod handled it

And I would be surprised that comment alone is the reason for a ban

You were at some point becoming an appreciated member but unfortunately being praised on a few occasions inspired you to start acting arrogant agaim

swasheck

i'm not even seeing the messages (block messages from this user everywhere) and it seems to me like the mods have been more-than-patient with this user.

THIS is why Clu went off the rails against users

Tom V

@JackDouglas what's the iis issue?

I'm on mobile now but if it's still an issue tomorrow let me know

ypercubeᵀᴹ

@swasheck Clu?

is that a pop reference?

swasheck

6:12 PM

@ypercubeᵀᴹ tron.wikia.com/wiki/Clu

onebox all the things

ypercubeᵀᴹ

ah Tron

Shame for not recognizing the name

swasheck

c'monson

ypercubeᵀᴹ

Tron

Tron (stylized as TRON) is a 1982 American science fiction action-adventure film written and directed by Steven Lisberger, based on a story by Lisberger and Bonnie MacBird and produced by Walt Disney Productions. The film stars Jeff Bridges as a computer programmer who is transported inside the software world of a mainframe computer where he interacts with programs in his attempt to escape, and Bruce Boxleitner, David Warner, Cindy Morgan, and Barnard Hughes star in supporting roles. Development of Tron began in 1976 when Lisberger became fascinated with the early video game Pong. He and producer...

fnally

swasheck

winnar

Philᵀᴹ

Winner, winner, chicken dinner

ypercubeᵀᴹ

6:18 PM

@JackDouglas perhaps they don't work in vNext edition?

select @@version; works

Aaron Bertrand

I don't think that's it. His app is probably having a hard time with SQL_VARIANT.

Philᵀᴹ

The Oracle version moans about variants if you do a: select * from all_procedures

swasheck

is a moan more, or less, dramatic than a complaint?

Philᵀᴹ

A moan is very English. It's like a complaint, but directed at anyone else apart from the person you need to be complaining to

4

McNets

Hi all, does anyone know why can't I use table variables with dbfiddle? dbfiddle.uk/…

Max Vernon

6:51 PM

@AaronBertrand it appears you're correct, sir.

dbfiddle.uk/…

interesting column alignment on the tinyint

Jack Douglas

@McNets I think it is because it is running each batch in a different transaction: dbfiddle.uk/…

I'm not sure what the default behaviour should be?

@MaxVernon it's the bigint that is wrong

McNets

@JackDouglas I don't know too much about dbfiddle, should I use one single batch?

Max Vernon

@JackDouglas true

Jack Douglas

@McNets I think perhaps DBFiddle should use a single transaction

not sure the best way to do that though

adding "BEGIN TRANSACTION" before the first batch doesn't seem to help

Andriy M

@McNets Variables (including table variables) must be declared in the same batch where they are used. At DBFiddle, each text box is parsed and executed as a separate batch, hence the error.

Jack Douglas

7:00 PM

@AndriyM ah, thanks for clarifying

Andriy M

My pleasure

McNets

@AndriyM will it work if I use a temp table instead of table variables?

Max Vernon

@McNets this works dbfiddle.uk/…

Jack Douglas

@TomV Thanks, the issue was I am stupid and didn't install Web-Asp-Net45 :S

McNets

@MaxVernon thanks

Max Vernon

7:02 PM

@McNets my pleasure

jcolebrand

7:37 PM

@JackDouglas Back

Still struggling with it?

Andriy M

@McNets Sorry, I assumed Max's example demonstrated the use of temporary tables, which made me think you got your answer, but actually he showed you how to use table variables correctly. To answer you question, if DBFiddle uses the same DB connection to run all the scripts specified in different textboxes, then you should be able to create a temporary table in one box and use it in another.

Jack Douglas

@jcolebrand thanks, no it's sorted :)

jcolebrand

Cool

Andriy M

@McNets Apparently it works.

Jack Douglas

@AndriyM no need to DROP btw — each connection gets it's own database which is dropped afterwards

Andriy M

7:49 PM

@JackDouglas I guessed as much, thanks for confirming.

McNets

@AndriyM thanks.

Andriy M

No worries

ypercubeᵀᴹ

8:12 PM

@MaxVernon and it works if you cast it to varchar dbfiddle.uk/…

ypercubeᵀᴹ

8:34 PM

I'm surprised that it has an upvote. — ypercubeᵀᴹ 23 secs ago

Too harsh?

Jack Douglas

9:14 PM

@ypercubeᵀᴹ I think so, a bit

Philᵀᴹ

9:45 PM

Fucking hell, some decent detailed answers there. Good to see!

ErikE

10:31 PM

> Please get full code for use in my program

-1

Q: How to set checked item in checkedlistbox from a TextBox with string seprated by comma - c#

I have problem to select item from string separated comma and check item in checkedlistbox How can I select item from string separated comma and then check in checkedlistbox? Preview My Program download My project from this file Download How can check in checkedlistbox with textbox and seprated...

(in a comment on the only, and horrible, answer)

> Download new File From Next Comment

rofl

Tom V

@ErikE I'm surprised (not really, it's SE) anybody bothered to answer

it's rep I suppose, or should I call that EXP

donwloading random files off of dropbox and see if it compiles, and then fix the code seems a lot of work for a couple of unicorn points though

jcolebrand

11:05 PM

Can I create temporary functions like I can create temporary stored procedures?

In Sql Server

I suppose I could just test it, but thought I would bug the room

"creation of temporary functions is not allowed" guess that answers that

ErikE

11:34 PM

@jcolebrand Yeah, I've tried, and it would be cool, but no. :(

@jcolebrand Temporary views could be useful, too

jcolebrand

Indeed

ErikE

@jcolebrand Temporary table types? :)

For TVPs

jcolebrand

You're gonna laugh at me for this one. I just wanna convert JSON to XML and I don't have privs to install functions on the server where I wanna run it

Fortunately I have a very rigid schema definition so I can do some string replaces

ErikE

@jcolebrand Argh. Use C# code. :)

jcolebrand

Trying to keep it in one SSMS window for easier running

Otherwise I'll have to

ErikE

11:36 PM

@jcolebrand Isn't some future version of SQL Server supposed to support JSON natively?

jcolebrand

Yah

I'm on like 2014 or something