The Heap™ – Consultancy ©®

Michael Green

2:07 AM

@JNK "Is anyone here NOT named Tom" - Alice maybe? Also Bob.

Max Vernon

4:29 AM

Apparently Untappd thinks I've had 128 distinct varieties.

Never had either of the ones above, though.

Andriy M

6:26 AM

@Zane CAST(wk.RealWeekNumber AS VARCHAR(1))?

@MichaelGreen Perhaps what @JNK meant was along the lines of "Is everyone here NOT named Tom".

Phil

7:37 AM

Mornin'

Lots of roads closed this morning. Right nightmare getting to work!

dezso

9:15 AM

@Phil don't you do home office?

Phil

@dezso Nope, ~22 mile commute

Michael Green

9:54 AM

@AndriyM Yes, I'm fairly sure that's what he meant; but I just couldn't help myself.

Phil

12:26 PM

0

Q: sql server 2008 r2, How do you avoid divide by zero error in the below table?

How do you avoid divide by zero error in the below table? CREATE TABLE [dbo].[TblDivision] ( [Numerator] int NOT NULL, [Denominator] int NOT NULL, [Result] AS (Numerator/ Denominator) ) GO Insert into (Numerator, Denominator) TblDivision values (3,0) Go

I do wonder what m409's answer actually adds. Pointless

Paul White

FGITW

TheAkhemist

12:51 PM

When setting a columns data type and max length, is nvarchar 0 indexed, e.g. if i set column x as a nvarchar(2), would that mean 3 characters are allowed to be inserted into that column?

YperSillyCubeᵀᴹ

@TheAkhemist No. Up to 2 characters will be allowed.

TheAkhemist

@YperSillyCubeᵀᴹ thank you very much

YperSillyCubeᵀᴹ

@TheAkhemist It would be weird to allow 3, wouldn't it?

Even in languages where arrays are 0-indexed, like C, an array of int[5] has 5 int elements.

(indexed from 0 to 4, but still 5.)

Was this suspension automatic, due to the 2 downvoted and closed questions?

dba.stackexchange.com/users/83118/anand?tab=topactivity

Phil

1:07 PM

@YperSillyCubeᵀᴹ A bit unfair, really. Had 1 alright question. Just needed a light slap, not a full-on killshot

Paul White

@YperSillyCubeᵀᴹ There are other deleted questions.

I'm not going to discuss details though, like whether it was automatic or manual.

dezso

@PaulWhite is it a secret? ;)

Paul White

@Phil BTW you voted to close the good question ;)

@dezso No, it's just not fair to discuss suspensions when the suspendee can't talk.

Phil

@PaulWhite I said it was "alright", not good :P

Paul White

@Phil Ha ha ok then.

Phil

1:14 PM

I'd VtC it again

Paul White

@Phil Yeah it was pretty borderline, rescued by a very good answer.

YperSillyCubeᵀᴹ

@PaulWhite OK, I remember this one: dba.stackexchange.com/posts/124924/revisions

Paul White

Indeed.

Phil

@YperSillyCubeᵀᴹ Pretty scary that financial data is involved

Paul White

Often seems to be financial/healthcare

dezso

1:25 PM

@PaulWhite true thing

Paul White

@AaronBertrand MAXRECURSION 0 allows you to exceed 32767.

Paul White

1:38 PM

0

Q: Is Nvarchar 0 indexed

Is the max length configuration for nvarchar 0 indexed, e.g. If i declare column x as type nvarchar and set the max length to (2) (X nvarchar(2)) does that mean that the column takes 3 nvarchars? or only 2?

sql-server database-design

@YperSillyCubeᵀᴹ ^^^ FYI

YperSillyCubeᵀᴹ

1:51 PM

@PaulWhite thanx.

Does this paragraph in msdn: nchar and nvarchar, make sense to you?

> nchar [ ( n ) ]

Fixed-length Unicode string data. n defines the string length and must be a value from 1 through 4,000. **The storage size is two times n bytes.** When the collation code page uses double-byte characters, **the storage size is still n bytes.** Depending on the string, the storage size of n bytes can be less than the value specified for n. The ISO synonyms for nchar are national char and national character..

Is the storage size still n bytes or still 2*n bytes?

Aaron Bertrand

@PaulWhite doh, of course, thanks, updated.

Only other complaint I've seen so far is "too many code samples" - IMHO basic does not have to mean short

It's not like the code samples are long or complex

Paul White

@AaronBertrand From people who can't count ;)

Aaron Bertrand

Indeed

bluefeet

@AaronBertrand do you need a tl;dr;?

Paul White

@YperSillyCubeᵀᴹ I think they may be hinting at Unicode compression there.

bluefeet

2:00 PM

for the instant gratification folks

Aaron Bertrand

@bluefeet no, this is exactly not the kind of post for instant gratification folks.

Paul White

@YperSillyCubeᵀᴹ But generally, the storage limit for n(var)char is 2*n bytes.

Aaron Bertrand

What would a TL;DR even say? "CTEs are cool, you should use them, but be careful"?

Paul White

@YperSillyCubeᵀᴹ Which can result in counter-intuitive behaviours when characters than need more than 2 bytes (supplementary characters) are involved.

bluefeet

@AaronBertrand of course

Paul White

2:02 PM

TL;DR go read Pinal, then come back when you want to learn something.

6

Aaron Bertrand

(For those a little lost, this is the post we're discussing.)

Paul White

@YperSillyCubeᵀᴹ For example:

DECLARE @T AS table (ch nvarchar(2) COLLATE Latin1_General_100_CI_AI_SC);
INSERT @T (ch) VALUES (N'𠜎𠜱');

> Msg 8152, Level 16, State 13 // String or binary data would be truncated.

So nvarchar(2) is not necessarily large enough to store two characters.

Aaron Bertrand

Unicode is hard. Srutzky has all the answers though.

Paul White

He seems to like collations, yes.

James Anderson

@AaronBertrand Having recently started blogging myself I'm interested to know how many hours you would put into a blog post of that complexity and length?

Aaron Bertrand

2:10 PM

@JamesAnderson that was a couple of hours, more spent on the organization than the writing itself.

James Anderson

@AaronBertrand I expect the organization process was much longer.

Aaron Bertrand

@JamesAnderson It was in the back of my mind for a week but I literally started writing it Monday afternoon and had some colleagues reviewing it yesterday

James Anderson

That's good going. It took me about 3 hours to discover, screen shot, and write the post about the tempDB tab in the 2016 installer. thedatabaseavenger.com/2016/01/…

Andriy M

@AaronBertrand …and can only be used for the duration of a single SELECT, UPDATE, INSERT, or DELETE statement… – you forgot about MERGE.

Or perhaps you chose to forget about MERGE :)

Aaron Bertrand

2:26 PM

Yeah, that was intentional. Whenever I see a customer using MERGE I try to talk them out of it.

So the last thing I'm going to do is promote its use in any way.

And to be honest, a CTE doesn't make a whole lot of sense for MERGE, since you have to put a select from the CTE inside USING() anyway.

Andriy M

Can't you do just USING cte?

Perhaps I misremembered this

Paul White

@AndriyM Yes.

Max Vernon

@AaronBertrand thanks, I was starting to feel like the nerd sitting in the corner of the party.

Andriy M

@PaulWhite Indeed, just verified that myself.

Paul White

@AndriyM I still wouldn't use a CTE with MERGE though. Even in the small number of cases where I would use MERGE at all.

It's too easy to out-smart oneself with the positioning of predicates.

Aaron Bertrand

2:40 PM

@AndriyM Oh yes you can, sorry.

In any case, it's still not something I'll promote or encourage

Andriy M

I think I used it as the target once, but I've since read that that usage is buggy or something.

I used it to limit the target row set. I think I once also posted an answer on SO about that (guilty).

Max Vernon

Is this correct, from a technical standpoint? dba.stackexchange.com/questions/125279/…

I believe it is, else I wouldn't have posted it; but good lord, I've been wrong before.

Phil

Awesome day so far. Managed to hire an Apprentice Phil. God help him :D

Max Vernon

@Phil The PFY™

Phil

Does that make me the BPFH?

Max Vernon

2:47 PM

yes, it does!

Paul White

@MaxVernon Seems correct to me. Belongs in the answer though ;)

Max Vernon

@PaulWhite ok, ok. lol. Thanks for looking!

Paul White

You just know he's looking for a workaround to make the problem go away instead of fixing the root cause.

Max Vernon

@PaulWhite no doubt. Some things are hard, I guess. In this case, it's not that hard to punch the developers in the face.

Paul White

:)

billinkc

2:49 PM

TIL - tertiary weights - blogs.msdn.com/b/qingsongyao/archive/2009/11/14/… Seems in SQL Server you might want to order by case sensitivity yet have comparisons be insensitive - we gotta collation for that!

Andriy M

Might be a little harder to face the consequences

But might also spare some punching in the future.

Max Vernon

I suppose I shouldn't propose punching anyone. It's not nice. Plus, I used to be a developer.

@billinkc collations are a lot more interesting than I thought they were before I started reading sruzky's answers...

seems there isn't anything a collation can't do; am I right Aaron?

Paul White

@billinkc I find it incredible the US default for installation is still SQL_Latin1_General_CP1_CI_AS. That's one character away from the 'problematic' SQL_Latin1_General_CP1_CI_AI.

That's probably because Americans insist on sorting per gallon-fortnight-acre or something.

Max Vernon

@PaulWhite I prefer to sort by olympic sized swimming pools, thanks very much. :)

not that I'm American

Paul White

3:05 PM

@MaxVernon No? But you live in the US?

Canadian?

dezso

@MaxVernon who could have benefitted from some punching?

ducks

Max Vernon

@PaulWhite yes. Saying we're American is like saying you're Australian.

@dezso lol

Paul White

@MaxVernon I remembered you were Canadian, but thought you lived in the US, and might have traded nationality at some stage.

Max Vernon

surely you're not confusing me with the fearless @AaronBertrand ?!

Paul White

@MaxVernon I certainly apologise for any unwanted inference there.

Max Vernon

3:09 PM

its no problem - I'm just kidding around

dezso

@MaxVernon why, isn't he one?

Paul White

@MaxVernon No I thought you were "similar" (for certain values of similar).

dezso

were's my trollface hat

James Lupolt

@PaulWhite 1000s of hours have been wasted because of this default : (

Max Vernon

@PaulWhite I was all excited there for a moment.

Paul White

3:10 PM

@MaxVernon Remain excited. Just choose a suitable value for similar.

James Lupolt

I'm not really sure why it was ever that way. Back-compat is the only justification now I guess

Paul White

@JamesLupolt Indeed.

@JamesLupolt So I'm told, yes.

Max Vernon

@JamesLupolt you're saying the default should be the accent insensitive version, presumably?

James Lupolt

@MaxVernon Mostly I don't understand why the default for US locales is a SQL collation, and UK is a Windows collation

Paul White

And why the US needs to sort single byte data differently from Unicode.

> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

Max Vernon

3:15 PM

collations could be a course unto itself.

Paul White

Using Latin1_General_CI_AS is much more sane.

billinkc

cuz we only store it as 'Murican

Max Vernon

(rant) dammit I hate that Microsoft decided to add "social" to msdn.microsoft.com. Makes it so much more difficult to search just the msdn.microsoft.com subdomain

billinkc

Rule #1, never use their search

Max Vernon

I don't - I use DuckDuckGo!!!

searching for "Latin1_General_CI_AS site:msdn.microsoft.com" returns a million "social" results; the real result is buried in 8th position.

James Lupolt

3:18 PM

annoying

though you can still do Latin1_General_CI_AS site:msdn.microsoft.com -site:social.msdn.microsoft.com?

Paul White

Yes I was about to suggest the same

Tedious though I agree

Max Vernon

@JamesLupolt yes. But, more typing for no real valid reason, when they could have just used social.microsoft.com and put all the terrible junk over there instead.

billinkc

@MaxVernon next time use altavista

swasheck

lycos ftmfw

also

Max Vernon

@billinkc hey I used to use that until it sunk to bottom of the junk pile when Yahoo bought it.

Paul White

3:21 PM

Jeeves knows

swasheck

could be a spoiler so i'm deleting)

Paul White

:26638450 That had better not be a spoiler for a movie I haven't seen yet

Max Vernon

@swasheck still pretty funny

Paul White

Damn you Seth! :)

swasheck

curse you perry the platypus

Paul Vargas

3:25 PM

Hi, @swasheck How are you? -- By the way, have you updated your database of Greek texts?

swasheck

@PaulVargas i'm well. no i havent.

Paul Vargas

@swasheck OK.

swasheck

@PaulWhite thanks for your answer. i'm struggling to force a plan with a filter but you've given me some things to examine

Paul White

3:41 PM

@swasheck FAST 1 should be pretty reliable (for that particular case).

The real point is it is possible. The trouble is such a plan doesn't always look cheapest to the optimizer, though it often will be in practice.

swasheck

yes. and i'm glad to know that it's possible. seems like a lot of knob-turning to get to a satisfactory plan. wonder how stable and realistic it'd be in a real-life production environment.

Paul White

@swasheck Also, apply SP1 at least, preferably come right up to date with SP1 CU4.

@swasheck It varies, of course. There are similar issues with table partitioning, which just hides much the same basic implementation under the covers.

swasheck

@PaulWhite we get horrible partition elimination because our developers refuse to include the partitioning key in any of their queries.

@PaulWhite good point

does anyone have a good and valid link to 2014 sp1? i can't seem to find it anywhere. all roads lead to microsoft.com/library/errorpages/smarterror.aspx?aspxerrorpath=/…

Paul White

3:58 PM

@swasheck microsoft.com/en-us/download/details.aspx?id=46694 ?

swasheck

SMARTERROR

i can get this far

microsoft.com/en-us/download/details.aspx?id=46694

choose x64 ... next ...

Paul White

Works for me.

swasheck

you're more awesomer

Paul White

@swasheck Try microsoft.com/en-us/download/… ?

Might not work, it's the "If your download does not start after 30 seconds, Click here" link I got.

swasheck

odd. wasnt working with chrome. internet exploder work like charmp

Paul White

4:06 PM

I'm using Chrome. Clearly your install is haunted.

swasheck

so many corporate extensions and add-ons to make things better and safer for us are probably plaguing the process

i'm pleased to announce that firefox works as well

(for now. until security overruns that too)

Paul White

Lemon-soaked corporate extensions.

swasheck

with a touch of black pepper

Paul White

HHGTTG reference.

swasheck

sorry. i've not read it

perhaps i should

i've been looking for something to read

tea leaves got boring

Paul White

4:18 PM

@swasheck Is pastebin blocked for you?

swasheck

@PaulWhite yes.

it's why i use gist

Paul White

@swasheck OK. If it helps, there's a showplan.xml here that you can use with a USE PLAN hint if you need to.

swasheck

@PaulWhite thanks. i appreciate that. i'm just evaluating different suggestions for managing large tables and this strategy is the biggest headache, so far

so far, it's crappy plan but still faster than partition elimination

Paul White

@swasheck Not following. Faster than partition elimination with table partitioning?

swasheck

@PaulWhite yeah. sorry. the partitioned view with the crappy plan is still faster (on this ONE query) than a query that gets the benefit of partition elimination on a really really real partitioned table

Phil

4:31 PM

Not doing as well for upvotes as I imagined. Mind you, weekends skew. Will run it again on Friday night to see where we are. data.stackexchange.com/dba/query/edit/420398

(I did sum(1) to anger you all)

Paul White

@Phil But the data isn't refreshed daily.

select YEAR(CreationDate), sum(1)
from Votes
where VoteTypeId = 2
and MONTH(CreationDate)=12
and DAY(CreationDate) between 1 and 6
group by YEAR(CreationDate)
order by YEAR(CreationDate)

For example.

Phil

Ah

YperSillyCubeᵀᴹ

4:49 PM

That question that @Martin answered is very weird.

dezso

@YperSillyCubeᵀᴹ where?

YperSillyCubeᵀᴹ

1

Q: SQL Query is getting progressively slower

We are experiencing a very weird problem when making consecutive query calls. Not sure what info is important, so I will give as much info as I can. We have a webserver and database server through Rackspace. They are running Windows Server and Sql Server 2008 r2 express version for the databas...

sql-server sql-server-2008 sql-server-2008-r2

swasheck

@YperSillyCubeᵀᴹ they're not feeding it enough.

Paul White

@YperSillyCubeᵀᴹ I don't see an answer by Martin?

YperSillyCubeᵀᴹ

Oh, it's another Smith...

swasheck

4:58 PM

> Today one of my clients called and asked for my perspective on Rey's identity. I provided this information and am now sending him a (zeroed) invoice for 30 minutes of Star Wars consulting.

nerd

@YperSillyCubeᵀᴹ and the first name started with "M"

Paul White

@YperSillyCubeᵀᴹ Silly Cube.

YperSillyCubeᵀᴹ

@swasheck that was that tricked me.

swasheck

need to update your bayesian inference filter

Max Vernon

5:11 PM

interesting subtlety around CREATE SCHEMA in that you can execute all kinds of DDL statements between the CREATE SCHEMA and the ;, and the order for most of the statements doesn't matter.

msdn.microsoft.com/en-us/library/ms189462.aspx#Anchor_4

CREATE SCHEMA Sprockets AUTHORIZATION Annik
    CREATE TABLE NineProngs (source int, cost int, partnumber int)
    GRANT SELECT ON SCHEMA::Sprockets TO Mandar
    DENY SELECT ON SCHEMA::Sprockets TO Prasanna;

^^^^ that is one atomic statement.

swasheck

@MaxVernon so it uses fission

Max Vernon

@swasheck lol

swasheck

usa today had a brilliant post today, detailing the difference between h-bombs and a-bombs

Max Vernon

just in case you feel like doing some consulting for free:

0

Q: Social (Classmate like) database schema appreciation

This is a database schema I propose for a networking website like the Classmate web site. Do you think the database has all the tables needed for such a project? What would you rather do? The database has 3 parts: the payment part is surrounded by the black color box, the social part by the blue...

database-design

YperSillyCubeᵀᴹ

0

Q: Duplicate keys error after switching to MySQL Galera

I recently switched from a single MySQL installation to MySQL Galera, running on 3 nodes addressed via HAProxy. Ever since switching I do get randomly "duplicate key" errors reported which theoretically should not be possible as I do check if the dataset is already available. e.g.: MySQL Error ...

mysql galera

Notice the 2nd edit.

James Lupolt

5:24 PM

@swasheck Seems irresponsible to put that out there. What if someone uses that image to make an h-bomb?

swasheck

right?

Paul White

5:39 PM

@YperSillyCubeᵀᴹ Priceless.

Max Vernon

@YperSillyCubeᵀᴹ oh man. Just like "press any key"

Paul White

@MaxVernon We do have users that would tackle that, particularly when combined with the details given in his previous question.

Max Vernon

@PaulWhite in my mind that doesn't make it a useful question. That is unless you were going to make a ClassMates website.

perhaps I'm wrong.

billinkc

6:03 PM

I made a perpetual motion device out of an hbomb that then goes nucular

Mike Fal

Yeah? Well I also made a perpetual motion device. It's called your mom.

2

Max Vernon

6:22 PM

@MikeFal and I laughed!

Paul White

@MaxVernon Well that's true of just about any database design question, but we still allow them. Some get really good answers that are good general learning tools as well. It's quite hard to judge in advance.

Max Vernon

6:37 PM

@PaulWhite fair enough. I'll defer to your excellent judgement, and simply not look at it :-)

close-vote retracted!

and the question has been up-voted, just to keep in line with the suggestion by @Phil to upvote more often.

Tom V

7:41 PM

Hi all

What a day

huh, wtf

I VtC a duplicate on this

3

A: SSRS Blocking Processes

Unfortunately there is not much you can do about this, this behaviour is by design. The problem manifests itself when user sessions time out because the report is taking too much time. You can try to improve the reports, or configure the session timeout to be a bit longer than the longest running...

then I thought WTF that's my own answer how could I vtc that and retracted my vote

and the Q and A are both a duplicate of this

3

Q: SSRS Blocking Processes

We have a busy SSRS Reporting server (SQL Server 2008 R2) on which we see frequent blocking between the below processes: [dbo].[CheckSessionLock] [dbo].[WriteLockSession] We are aware that some reports are running unacceptably slow. What I need to know is whether the blocking in the ReportSe...

sql-server performance ssrs blocking

@PaulWhite a merge?

KASQLDBA has some additional info I guess

confused...

Tom V

8:20 PM

off course I meant this

2

Q: SQL Reporting Services (SSRS) - Blocked process on ReportServer, SP CheckSessionLock

We frequently see blocking on our SSRS box which houses both front-end Report Manager ReportingService and the SQL database engine with ReportServer & ReportServerTempDB Catalog databases Blocking session: Database name: ReportServer Application name: Report Server Host name: SSRS01 ...

sql-server ssrs blocking

Shouldn't be around here after a 14 hour work day I suppose

billinkc

9:30 PM

Oh @zane ...

It's time to Rock. Wearable Mega Man Helmet: http://j.mp/1mH01rS https://t.co/BJYK1rPKJ5

Tweeted by thinkgeek on January 6, 2016 at 9:30 PM

Phil

10:29 PM

@MaxVernon the upvote Police are watching!

Geoff Patterson

Anybody know if TF 4199 ever fixes intra-query parallelism deadlocks?

(I don't have permission to use QUERYTRACEON on the server to check)

And it's not exactly easy to quickly repro that situation, which I think I've read is essentially always a bug within SQL Server, on another machine

Max Vernon

@GeoffPatterson sounds like an interesting question.

@Phil "Every breath you take, every vote you make, I'll be watching you"

MDCCL

It's a bit late but, happy 2016 everyone :)

Geoff Patterson

12

Q: SQL Server: deadlocked on lock communication buffer resources

what could be possible reason for this deadlock type? (not deadlock in general) lock communication buffer resources is this indicated system is low in memory and buffers count ran out of limit? Detailed Error: Transaction (Process ID 59) was deadlocked on lock communication buffer resources wi...

sql-server-2008

@MaxVernon That's the most relevant question, and using MAXDOP 1 or even a "OPTION (CONCAT UNION, HASH UNION)" hint to avoid the problematic MERGE UNION works fine in my case

I was mostly just curious if 4199 is another potential solution, I'll try to get access and follow up on that

MDCCL

@PaulWhite Hi, Paul. Sorry for the late reply, I have been away for the last few weeks. I'll take a look at it and see if I can contribute something particularly substantial. As of now, I think that ConcernedOfTunbridgeWells has provided a very good answer, what do you think?

6

Q: Is it appropriate to ask for tips on data architecture here?

I'm working on a personal project that will require a database to track religions, gods, mythologies, and sacred texts. I've been struggling with what would be best practices for design and layout of some of the pieces and wondered if this was an appropriate place to ask data architecture questio...

discussion

MDCCL

11:08 PM

Could the following question be considered a duplicate:

3

Q: Versioning data in a database

I have a database to hold data about projects. Projects have lots of data associated with them that is segmented into other tables such as project_contacts and project_images. Those are one-to-many relations where 1 project can have many contacts and many images. However, I need to be able to...

postgresql database-design

Just like this one:

0

Q: Database schema for storing edits in the content

I want to store all the edits of a specific table. Multiple fields can be modified and for different users. I want to store all changes, taking in count that the number of fields that can be edited can change over the time.

mysql database-design schema

Transcript for

The Heap™ – Consultancy ©®