The Heap™ – Consultancy ©® - 2017-09-26 (page 1 of 2)

that is cool

sounds like a lot of XML work

yeh

github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/pull/…

I can't believe you used a RIGHT JOIN

can I patch it to use a LEFT JOIN?

i'll kill you dead if you break anything

I expect that from RIGHT JOIN users

4:05 AM

it was one of those things where i left it just to have the shorter cte query on top

heh

one day I'm going to do a blog post where doing three right joins gives the same results as a left join

GET IT?

preferably after the revolution

i'll need a reason to purge you

I'll have to figure out how foreign keys work first though

"sometimes"

4:25 AM

alright, that's enough for tonight

A: How to shrink big database files faster than dbcc command?

5:52 AM

Morning

Any mods in town?

I put a flag up on this answer, because I thought it wasn't really an answer, just a slight rant on the verge of the "be nice" policy.

0

The answer above is the horrible caricature of this Paul Randal's article: Why you should not shrink your data files In his article Paul suggests: The method I like to recommend is as follows: Create a new filegroup Move all affected tables and indexes into the new filegroup using ...

@hot2use Always.

@PaulWhite :-)

I know its an edge case, because the author has one sentence with a possible solution.

@hot2use I empathise, and yes it is an edge case, but there's just enough of an answer to make it NNAA. Other flags might well be accepted (and indeed, have been).

I have no idea why people have upvoted that answer, but there you go.

NNAA?

Not Not An Answer

5:57 AM

Ah ok. Thanks.

Would a nice comment suffice to poke the author in the right direction?

Yes.

Q: Stop SQL Server from ordering results?

Will ponder the idea. And yes, I was amazed he got 2 upvotes too.

What happened with this question:

0

I'm teaching a class in which I want to demonstrate to my students that queries are not guaranteed to return results in a specific order without an explicit ORDER BY. I've observed that SQL Server seems to return ordered results even when ORDER BY is not present in the query. This is a problem be...

I would suggest making the answer to the question asked a more prominent part of this answer. You are welcome to briefly critique another answer as well, but that should not form 95% of your answer. If you could word things a little more constructively, that would be appreciated as well. Thank you. — Paul White ♦ 13 secs ago

@hot2use A lot.

Cool :-)

Essentially, I believe some of our more awesome users combined to turn a potential train wreck into something with lasting value to the site.

6:01 AM

I read the transcript from back to when I left and was amazed at the amount of up and down votes and the changing of votes.

Anything to prevent Brent earning a Reversal badge :)

I'm kidding.

haha

Yes, a lot of effort went into that post by @ypercubeᵀᴹ and yourself

A totally different question now.

Well hopefully not different but certainly better

I was thinking different in the context of the way the question presents itself to the site's visitors

Ah gotcha OK cool

6:06 AM

Thanks Paul for the follow up. :-)

And I hope I'm not pestering the mods too much with my occasional flags?

Thump me if I am.

Of course not. We're bored most of the time and need more flags to justify our existence.

Seriously dba.se is a very well-behaved site with a pretty low flag volume.

@PaulWhite generic Avatar?

8 hours ago, by Paul White

@ypercubeᵀᴹ Yes I lost a bet with Joe.

7:00 AM

Morning

7:51 AM

1 day w/o smoke

@McNets How is it going? Or should we ask your Current Life Partner and/or colleagues?

@gbn Well by now, a little bit nervous

CLP has gone at beach this weeak

8:07 AM

@McNets Sensible move...

@gbn yes

Well, good luck.. to your CLP. he he

Morning

ConcernedOfTunbridgeWells

8:29 AM

morning

Morning all

@McNets good luck with that

@Marian thank you!!

make it two!

then three.. and so on

9:01 AM

@Marian yeah, that's the way

@McNets the only way I found it worked for me :)

last time I counted it by days, this time I'm counting it by hours

10:28 AM

@McNets You probably smell nicer already. When smokers smell dirty to you, you've cracked it

@McNets Good luck.

5 years, 21 days here

About 24 years here

Q: SQL Server: is there a default SELECT order?

@gbn Great.

The teacher really wants to know it now.

1

I need to clear up what I mean by order here. I am simply asking about what order the data is fetched in. If I simply SELECT * FROM table; in SQL Server without an ORDER BY clause, I find that the results always come in the same order. That certainly is not the case with some other database serv...

sql-server select

Yea I was looking for a dupe target but can't seem to find any quickly

> I have not tried with very large tables

10:43 AM

@sp_BlitzErik I see that often: "I have huge tables with a million rows"

@sp_BlitzErik NATRQ?

@gbn someday they'll learn

@PaulWhite not answering the right question

which, i guess they attempted to

I would think so.

@sp_BlitzErik We all start somewhere. My first "real" BI database I worked on had daily partitions bigger than the entire data quantity involved in my first SQL Server role

Admittedly, it was SQL Server 2008-ish vs SQL Server 6.5 in 1998. I tend to avoid BI work if possible

sql-server select group-by order-by

3

Q: SQL Server: Order of Rows

We all know that a simple statement such as: SELECT * FROM stuff; should not produce an ordered result. However, when I try to demonstrate this, it always comes out in primary key order. Moreover a statement such as: SELECT thing,whatever FROM stuff GROUP BY thing,whatever; always appears ...

There is a pattern here

10:49 AM

@gbn yeah, at some point i was impressed that i had a table of 20 some odd million phone numbers (no not call registry)

Isn't his new question essentially a rewording of what Joe and 'cube reworded his previous question into?

all of his questions seem to be rewording the same question

@sp_BlitzErik Odd isn't it

Wait, that last question linked by Paul is from the last year? Wow

so accessing data, sql server can do an unordered scan, an ordered scan, a backwards scan, an allocation order scan

what else?

or i guess that should read an ordered scan forwards or backwards

10:54 AM

A columnstore scan, an in-memory table scan...

DO BE REAL, PAUL

Well in theory

;)

@AndriyM Indeed.

11:23 AM

@hot2use thak you, last time I left it for 6 years

@McNets ooh quitting cigarettes?

why do you think it is that microsoft has TSQLUserDefinedFunctionsNotParallelizable as a non parallel plan reason, but scalar functions only ever seem to generate CouldNotGenerateValidParallelPlan?

don't think i've ever practically seen anything other than that or MaxDOPSetToOne

Philᵀᴹ

blog.timescale.com/…

@sp_BlitzErik random guess: different kinds of UDF. Inline TVF (macro), multi-statement TVF (resultset back). Scalar (single value), CLR (clearly not parallel capable). With and without data access for all

@hot2use I've just closed it as duplicate

@gbn they have one for clr, too: blogs.msdn.microsoft.com/psssql/2013/08/28/…

itvfs don't inhibit parallelism the way others do

at least not as a construct

11:34 AM

I was editing, yes

They are substitution macros

yeah, no real magic powers, but at least less harmful than alternatives

@gbn CLR scalar functions are parallelizable.

really?

ah well, that's me talking bolllocks again

I tend to avoid in my code unless there is a clear benefit or I know they'll be trivial (eg DBA code that queries small metadata tables)

Yeah CLR scalar functions can go parallel unless they access data.

sqlblog.com/blogs/hugo_kornelis/archive/2012/06/29/…

11:50 AM

@sp_BlitzErik parallel scan?

@ypercubeᵀᴹ spoken like a true man from multiple dimensions

@sp_BlitzErik Let's hope he's more star than snowflake

@gbn i've been assured that none of @ypercubeᵀᴹ's dimensions are a safe space

2

@sp_BlitzErik @ypercubeᵀᴹ do you consider your dimensions to be junk or degenerate? Or merely shrunken?

@gbn I consider them all real ;)

12:05 PM

@gbn my dimensions are tip top, thanks

Is there such a thing as "round dimensions"? That was a big lunch

you'll find out soon enough

@PaulWhite you look so different today. More geometrical

@bluefeet one of us!

@sp_BlitzErik what's the difference between an unordered scan and an allocation order scan?

@sp_BlitzErik yep, if possible...

12:14 PM

@JoeObbish your mom

@bluefeet Lost a bet with Joe.

@sp_BlitzErik that was a serious question

@JoeObbish An unordered scan (Ordered: False) is allowed to not return rows in any particular order. An allocation-order scan is driven by IAM pages.

well at the storage level an unordered scan will either be allocation-order or ordered, right?

I think what tripped me up was the mixing of storage level operations and plan operators

back much later

12:32 PM

@JoeObbish For Ordered:False, the storage engine can decide at runtime on any convenient access method, it might be an ordered b-tree access, an unordered access of any type, or an allocation-ordered scan.

The main distinction between Ordered:True and Ordered:False is the guarantees required by the optimizer for correct operation. Not all the details are exposed in plans.

For example, prefetching could work differently for an ordered vs unordered btree access.

sql kiwi or sql wiki?

Unordered need not involve an allocation-order scan.

@sp_BlitzErik Hard to tell from the avatar :(

how long was the bet for? 30 days?

A month.

-_-

which month?

12:36 PM

An interval of a month.

so, until oct 1?

You're new to intervals eh

apparently a good way to upset your wife is to compressed air blast a summer worth of dust from a box fan not into the garbage

@PaulWhite no, i just don't know if you mean 30 days or four weeks or a specific month which may have more or less days

I can see that working yes.

@sp_BlitzErik I guess DATEADD(MONTH, 1, start_dt)

Probably not a month on Mercury either.

i dunno man, you maybe should have gone with four weeks

SELECT DATEADD(MONTH, 1, GETDATE());
SELECT DATEADD(DAY, 30, GETDATE());
SELECT DATEADD(WEEK, 4, GETDATE());

12:41 PM

Sure, but I expected to win

Otherwise I'd have chosen NANOSECOND

Have to say I'm disappointed with the colour.

can't you choose a different one?

I believe it is autogenerated from the email address.

uch

UCH

i was going to change mine in solidarity but i don't wanna look like i'm in an easter gang

A: How is the default user avatar generated?

Oh maybe it's a hash of the last IP address.

Ah, it's both:

112

They're called Identicons. If you do not upload your own image, then Stack Exchange uses Gravatar and specifies Identicons as the default image: Gravatar implementor's guide How the URL is constructed, with: When you're not registered (when Stack Exchange does not know your email address), t...

probably one of the most infuriating things about ssms is that hitting ctrl+b clears the clipboard

12:47 PM

That is so annoying.

As does accidentally copying/cutting a blank line. And yes there's a setting for that (I think).

But SSMS has many ways to be annoying.

it only ever seems to happen when you're in a groove or copied something from way up in a script and scrolled nearly to the end

so like you hardly even notice

>;(

op had the sql-server tag on this and i got excited

0

Q: CPU Spiking 300% 200% 150%

I'm running a database which is randomly spiking to 300% to 150%. It's Linode 8 GB RAM, 4CPU package >> Run with '--help' for additional options and output filtering [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 10.1.23-MariaDB-1~xenial [OK] Op...

mysql

my joy was smashed to bits

hug

what a day to get the sweet and tender clone

Q: Know what tables each user is accessing

1:13 PM

0

I have a database with a lot of users, all of them with full read privileges on all the tables but not all the users are reading all the tables. I want to have each user having as little privileges as they need. Each user belongs to a different large legacy application for which auditing the code...

audit

just use the <appropriate mechanism> for <your platform>

<duh>

2:43 PM

The answer in the linked duplicate addresses the question. — ypercubeᵀᴹ 3 hours ago

The question has been re-opened and closed again, only as a duplicate of a different question

@ypercubeᵀᴹ ^^^ FYI

The answer to that one probably addresses the closed question anyway

2:58 PM

@AndriyM thnx

3:47 PM

there should be a badge for if your reputation is ever equal to your favorite trace flag

billinkc

How does one get a -1 reputation?

anger jon skeet

Is there a TF -1?

billinkc

I thought dbcc traceon(-1) shows all the existing trace flags

<-- also not a dba

Optional last parameter -1 that means "apply globally" not just "this connection"

3:55 PM

Oh somebody's ban ended

ms or tt or other?

Ms. Not that he's back but I checked his profile to see if a ban resulted in -1 rep

afaik it just bumps you back to 1

it's not listed as a constraint in the sede, but maybe @bluefeet or @jcolebrand can tell us if there's a constraint or some other magic to prevent it from going below 1.

jcolebrand

I was pretty sure it took you to only 1, never to -1

Who's currently banned and I'll look at it?

what's the question?

4:06 PM

@jcolebrand you could just ban someone and look at it

@Lamak are you volunteering?

nono, just giving him some ideas

@billinkc you can't. 1 is the lowest

jcolebrand

I thought so @bluefeet

4:34 PM

Anyone going to Postgres meeting tomorrow?

meetup.com/London-PostgreSQL-Meetup-Group/events/243330194

@JackDouglas ^^

OH! @ypercubeᵀᴹ -- are you going to sqlbits? i think (at least) me and brent will be there in february

if not, perhaps one of your dimensions will be around to fancy a pint

as they say

@sp_BlitzErik I don't know. I haven't been in a SQL Server meeting / congress before.

Is it in London?

LONDON
O
N
D
O
N

Q: Disable cross-database access

Great. In any case, I can come for beers on Saturday ;)

wBob

5:35 PM

Given this old question on SO on disabling cross-database queries in SQL Server, is it possible now, considering SQL Server 2017 etc ?

0

Lets say User1 has access to both Db1 and Db2. Where the DB1 and Db2 reside in the same SQL Server box. Now with the following syntax this user can access tables from other database: SELECT * FROM [database].[schema].[table] I have a security requirement that I am working on that should r...

sql-server

sure, just use azure

:)

wait, no, they added that

aws rds doesn't

5:54 PM

@sp_BlitzErik you guys should've combined it with SQL Saturday Holland and dataminds in Belgium for a European road trip.

Consider it in the future, Belgian beer is on me

do they have precons?

because about the only way to make road trips make sense financially is to do them

@ypercubeᵀᴹ I'd like to go but I don't have time — do you have a favourite pg10 feature you are hoping will get some airtime?

@sp_BlitzErik SQL Saturday has precons yes

@sp_BlitzErik you guys should come to Chile sometime

i'll run it by brent to see if it's doable

5:56 PM

but SQL Saturday is always planned the weekend before dataminds, which I believe covers a lot of expenses if you look at the speaker list and they have precons too

Aaron was here 2 or 3 years ago and is here again this year

datamindsconnect.be/overview/speaker

sqlsaturday.com/666/Sessions/Schedule.aspx

lemme guess... bad habits

?

Of course but I think the combination of both events can be doable together with sqlbits

oh cool, buck woody is going to dataminds

With both events a few days apart, and both having precons it might be worth it

just sayin...

I don't know the financial aspect of it

me either, i'm dumb

6:00 PM

I remember Aaron saying it was mostly on an advertising budget a few years ago and not much money was made, so I'm not sure

@TomV the speakers should go dressed as the antichrist to that one

I could attend in a red devils soccer outfit

Except im too shy to actually do that

i would wear a lamak mask

@PaulWhite yeah, I knew that

6:11 PM

Have we had the new header for long?

I guess that I didn't know that the storage engine has so many options

@JackDouglas if by "we" you mean "mods"

apparently I'm responsible for the SSC question of the day

were there any other questions in the running?

I didn't actually write it

"inspired" is the right word I guess

oh snap

MS connect feedback

I'm nervous to click

it's like scratching the final number on a lotto ticket

6:15 PM

i bet there's a job offer in there

you can be sunil jr.

@sp_BlitzErik I'm sure they wouldn't give us anything special they haven't given you :p

or are we beta testers as well now?

I won the connect lottery!

"We confirm this is a problem in Microsoft SQL Server, and we will make an appropriate fix with upcoming regular updates."

@sp_BlitzErik I can sell you one

@JoeObbish how many votes did it get?

@Lamak i'm gonna make a real one

that sounds wrong somehow

6:19 PM

@sp_BlitzErik 10

@JackDouglas what header?

@JoeObbish disappointing. it was tweeted.

by who?

el brento

oh, wow

thought he had like a billion followers

to be fair, logging into connect is a big ask

6:22 PM

a day long ask, sometimes

indeed

alright, 1300 points to go before pass

Q: New top bar is coming to the Stack Exchange network

@JackDouglas ah, so this?

51

Updated timeline: Done changes rolled out on meta.se and to Mods on all sites on Monday (9/18) Assuming no significant issues are discovered we will roll out to all sites within the next couple weeks I'm the new product manager for the DAG team. I’m excited to announce that the...

discussion featured user-interface top-bar navigation

@sp_BlitzErik how many days?

billinkc

33 ish

6:27 PM

@Lamak about five weeks, but realistically i only have until around the 21st because that's when i start traveling for other stuff

@JackDouglas mods got it before everyone else

@Lamak ah yes, that. I guess you will get it soon, not sure why mods go first

but @bluefeet probably knows :)

@JackDouglas because you are special

@JackDouglas because when it was first announced the TL gave a lot of feedback on it, so we made the decision to roll it out to the mods first to work out any bugs and/or make changes that would work better for you special folks. :)

@sp_BlitzErik well, good luck

6:31 PM

I have special needs

@bluefeet very kind, I like the review queue dropdown, that really jumps out as 'a good thing'

@Lamak i don't need luck when i have the good judgment of a highly ethical community.

that's true