The Heap™ – Consultancy ©® - 2014-10-02 (page 1 of 2)

Aaron Bertrand

3:56 AM

You need to better define your requirements and explain why the most obvious solutions are not possible. Why can't you create a linked server on server 1? Why can't you use OPENQUERY? Why can't you create a view? It's frustrating for people to offer all kinds of suggestions and for you to just shoot them all down with no explanation. Maybe the explanation will help people come up with a different solution. As it stands, it's not fair to say "how do I do x?" and then respond with "nope, can't do that." "nope, can't do that." "nope, can't do that." — Aaron Bertrand ♦ 29 mins ago

^----- pin the answer on the problem

Max Vernon

@AaronBertrand great comment, just what I was thinking.

Chris Travers

4:58 AM

wondering if there is anything more painful than loading bulk data out of a NoSQL solution like Couch.....

Paul White

9:49 AM

Anyone understand the opening sentence of this answer?

0

A: When you type an SQL query in interpreter, what does happen?

It is not particularly fair for you to ask a question that you don't know the answer to. However the four key stages (in SQL Server) and other database engines are: Parsing (creates a parse tree) Binding (generates algebrized tree) Query Optimization (generates and selects good enough query plan...

ypercube

@PaulWhite It baffled me, too. I guess they want to say something about the question being an interview one but I can't get really what the point is.

Paul White

Glad it's not just me.

Mikael Eriksson

11:13 AM

@PaulWhite @ypercube Works fine for me. Q: "Actully this question is asked in an interview from me." A: "It is not particularly fair for you to ask a question that you don't know the answer to."

ypercube

@MikaelEriksson Ah, that explains it.

But I doubt that the OP had asked the question. I'd bet on being asked. and language barriers.

Paul White

12:14 PM

Very creative. I do think the OP was the interviewee though.

Aaron Bertrand

12:37 PM

100 million rows, no key. Struggling for an analogy.

0

Q: How to split one big table that has 100 million data to multiple tables?

I have one database in Sql server 2012 and in that there is one table that has more than 100 million data and I wanted to split that table to multiple similar table with 1 million data in each table. What is the best way to achieve this and faster. I have tried paging but its take too much time...

Kermit

@AaronBertrand i got nothing

Aaron Bertrand

"Go find my car in the (massive) parking lot. It's the black one."

Kermit

I like it

JNK

"On the beach, it's the seashell near the water."

bluefeet

I think I'm going to open a pivot consulting service.

Aaron Bertrand

12:52 PM

Your tagline could be "we pivot till we're blue in the feet"

9

bluefeet

@AaronBertrand that's perfect

Kermit

@bluefeet "Bluefeet Pivot LLC"

Aaron Bertrand

Wow, fun conversation here

-5

Q: Why create a temporary table in a stored procedure?

Recently I got the following question: In a stored procedure, you need to create a temporary table. When it is efficient to create an index on that temporary table? Then my question was... why would we want to create a temporary table in a stored procedure? Thanks for your help!!

sql sql-server database tsql

@LittleBobbyTables Not getting into the argument about temp table vs. table variable (lots of misconceptions shown here; people should read this in full). But just FYI you can put a primary key (clustered) on a table variable in any version of SQL Server, so it's not entirely true that you can't put indexes on them. And in SQL Server 2014 you can define additional indexes inline, too. — Aaron Bertrand 23 secs ago

Kermit

@AaronBertrand Much fun.

JNK

@AaronBertrand in 2014 do table variables still have no stats?

Aaron Bertrand

1:07 PM

@JNK there's a trace flag to help with some of that: sqlperformance.com/2014/06/t-sql-queries/…

groan

deprecated or not still better than Access — Steven Wood 3 mins ago

It's stranglin' time

This is why it was a COMMENT and not an ANSWER. Since it seems like you are just starting out in this project because you haven't even made a single successful connection yet, and since a lot of people don't know that Compact is deprecated and shouldn't be used, I thought it might be helpful to bring it up. If you don't find it helpful, well, tough beans. The next reader might. Sorry for trying to help you but I'm going to leave my comments here because other readers might be a bit more appreciative. — Aaron Bertrand 13 secs ago

Kermit

@AaronBertrand sounds like my excitement from yesterday

Aaron Bertrand

Seriously, these are the types of people that are going to drive SO into the ground and turn it into MSDN forums.

Kermit

What can you do

Just have more kids...

Teach them to not go on SO

JNK

1:26 PM

good grief

Aaron Bertrand

PS this is a great post by Paul

sqlperformance.com/2014/10/t-sql-queries/…

JNK

I saw that

bluefeet

another satisfied pivot customer

Holy sh** dude! You are soo amazing. Thats it what i was talking about. I tried the think with the MAX() but didnt SUM() every record before the MAX(). Excellent, thank you very much for your patience and time reading my cryptic post. Im wondering how can i solve the issue with the NULL values in c_Meaning. Once there is a column with c_Meaning_1 i want that for each record there is the value "Wareneingang:" and so on.. — JollyPopper 4 mins ago

Aaron Bertrand

@AaronBertrand wud u mind following me please? need to dm you, tku.

Tweeted by sqlmal on October 2, 2014 at 12:47 AM

@AaronBertrand @kekline recommended I seek ur opinion on something..do u mind following me to dm u? tku much.

Tweeted by sqlmal on October 2, 2014 at 1:33 PM

JNK

seems legit

Aaron Bertrand

1:35 PM

I told him to send me an e-mail because that conversation on twitter would be f'ing alphabet soup

JNK

wt d u mn?

Aaron Bertrand

is hlpfl tku

JNK

I try to envision people who type like that as English aristocrats with tea and crumpets.

It makes it much more bearable and amusing.

Aaron Bertrand

You are far more kind than I am

bluefeet

@AaronBertrand I don't think I'd even respond to that tweet.

Aaron Bertrand

1:36 PM

@bluefeet I didn't to the first one, but apparently Kevin set me up for this

billinkc

Mal is female

And DNF Indian

Aaron Bertrand

DNF?

billinkc

dot not feather

Aaron Bertrand

oh brother

billinkc

I think she runs the Atlanta SSUG.

ypercube

1:45 PM

@billinkc How is that (Mal = female)?

billinkc

Malathi (lots more letters in there)

ypercube

@billinkc Ah, OK. I guess that's her: beyondrelational.com/modules/2/blogs/71/…

billinkc

Yes

shudders

Aaron Bertrand

2:01 PM

The world sucks. Ebola, enterovirus... what's next? Can I live in a bubble?

Kermit

@AaronBertrand don't forget about ISIS

Aaron Bertrand

I'm not too worried about ISIS beheading me

The other two, though, are legitimate concerns (as my daughter plays at a day care with 30 other kids)

Kermit

"Microsoft MVP beheaded"

billinkc

I assume none of y'all work with Power Query

Kermit

wats dat

billinkc

2:06 PM

Exactly

Aaron Bertrand

Nope

I like Dairy Queen though, is that close?

billinkc

Power Query is an add on for Excel 2010/2013 that uses a functional language to provide a safer scripting thing

I'll take a cookie dough blizzard please

Lamak

@billinkc I used it once

and that was all

Kermit

@AaronBertrand no way, me too

Aaron Bertrand

GO BACK TO STACK OVERFLOW

0

Q: how get startDate -1 in sql script?

I'm trying to get the start_date -1 to update my database record select start_date -1 from mytable

sql-server sql

billinkc

2:11 PM

VtC Tip of the iceburg

Aaron Bertrand

Dumb question - why FILE_IDEX instead of FILE_ID or FILE_INDEX?

technet.microsoft.com/en-us/library/ms186275(v=sql.105).aspx

Kermit

@AaronBertrand because not FILE_INDX

Aaron Bertrand

Is this like RAISERROR (raise roar), where someone made a typo when they first entered it into the code, and they decided fixing it would be too much hassle?

billinkc

backwards compatibility mang

Aaron Bertrand

Someone created that code long before there was anything to go backwards to

billinkc

2:16 PM

There's no compensation for fixing things. It's all about what new functionality you ad

Aaron Bertrand

@Zane no longer has a trade mark / service mark on mega man

0

Q: How do I choose which row will appear (field value) when I use "group by"?

I have a select that should show only rows that p02_nrcelular field are repeated (like if there is more than one row with same value) but using group by to show only one of each. The problem is, when they are repeated, teh p02_fgorigem field has different values of 0 and 1 in each row. The value...

sql-server tsql

ypercube

@AaronBertrand I guess EX stands for "Expression"?

"File ID Expression"

billinkc

We should just refer to everyone that uses the Mega Man logo as Zane

Aaron Bertrand

> Returns the file identification (ID) number

ypercube

Similar to DATABASEPROPERTYEX

Aaron Bertrand

2:19 PM

That EX is for extended. DATABASEPROPERTY had the base properties, DATABASEPROPERTYEX has a bunch of additional ones.

(Why they needed a second function, I have no idea.)

Also interesting that FILE_ID is deprecated, but FILE_IDEX is not. I think they got that decision backwards.

technet.microsoft.com/en-us/library/ms182063(v=sql.105).aspx

ypercube

Could it be a deprecation process? (Add a new FUNCTIONEX, deprecate FUNCTION in future version, ...)?

Aaron Bertrand

Yeah maybe. The concept of overloading is lost on them (except they did it with RAISERROR, funny enough, they deprecated some forms of it...)

They didn't have to make a RAISERROREX

billinkc

because we have throw

Oh and Windows 10 installs just fine on Hyper-V. I know, you're all shocked

Aaron Bertrand

2:34 PM

Yeah works fine in VMWare Workstation and VMWare Fusion, too. With Parallels you have to do a little tweaking and also suffer compromises.

Kermit

fancy

billinkc

I don't get the compromised start menu

Aaron Bertrand

Half old-style, half live tiles

And it doesn't take over your screen

Also look at this - actual quick access to things without having to find metro's G-spot

billinkc

Maybe it's because I never got the "live tiles" but I don't know why I'd want big fat tiles when I have little icons doing the same thing

ConcernedOfTunbridgeWells

@AaronBertrand I do like my shells to do what I tell them, and preferably without side effects.

Aaron Bertrand

2:40 PM

billinkc

They buried control panel even further from us

JNK

@billinkc same

Aaron Bertrand

Not really - see above screen shot. Just right-click the start button. I did not customize anything there.

JNK

I am hating the start menu in win 8.1

billinkc

I was unaware of that option. I had to dig down through things to find the classic Control Panel icon

Aaron Bertrand

2:42 PM

Also shell has some new options, like finally supporting Ctrl+V for once (instead of putting ^V there, how annoying was that)

billinkc

I was elated that they made the defaults like QuickInsert the actual default

Aaron Bertrand

So far Windows 10 is actually tolerable. I haven't wanted to throw my computer out the window yet, which is a stark contrast to every time I touch Windows 8

Also I think it's hilarious they had to name it Windows 10 because of all the code out there that parses "Windows 9%" for 95/98.

(Honestly I don't think they give a sh*t about that - I think it's another catch-up gimmick, like Word jumping to v6 to catch up with WordPerfect.)

Lamak

3:07 PM

Ah, wordPerfect, the memories

ypercube

@Lamak IBM says goodbye to Lotus 1-2-3 today

swasheck

@AaronBertrand do you have a source for that?

billinkc

Someone posted it to redit

swasheck

blocked

billinkc

swasheck

3:16 PM

thanks

Aaron Bertrand

engadget.com/2014/10/01/windows-10-9-naming-rumor

SE CDN seems to be down (making the site terribly slow and unresponsive). Anyone else?

swasheck

thankyoubothverymuch

i'd probably be gone from here if i wasnt paid so well

Aaron Bertrand

3:38 PM

It seems a little ridiculously screwed down over there.

Shawn Melton

I don't have an iPhone but this would be fun to do to anyone that does

►

The GIF image for messaging is a pretty good.

swasheck

@AaronBertrand yeah. we're not going to win8 or win9/10

Aaron Bertrand

@ShawnMelton I've done the wallpaper one on desktops on April 1

Shawn Melton

Yeah the desktop one is always a good one

swasheck

i just change the mouse orientation and the screen with ctrl+alt (up|down|left|right)

Aaron Bertrand

3:53 PM

@swasheck Oh I can understand enterprise standards on operating systems, and that those move like glaciers. But blocking you from imgur and reddit and just about every other useful site on the internet is pretty stupid.

swasheck

@AaronBertrand it is pretty stupid. i had to beg to have sqlperformance.com and the sqlsentry.tv site unblocked.

it's very "old south" here ... if it's foreign then it's suspect

Aaron Bertrand

I can't imagine being responsible for a whitelist for Internet domains. Not because it would be hard for me to come up with a list of things I want you to be able to see, but because I'd spend the rest of my career handling exceptions.

swasheck

i guess it's all in what you value. we value fear, stupidity, and wasted salary

Shawn Melton

4:09 PM

I can't understand why local library here that I will go to and work sometimes (work from home here) will let me get to stackoverflow.com but not dba.se

Aaron Bertrand

4:28 PM

Suspect it's similar to wells - they keep a good list, not a naughty list

billinkc

This user has me shaking my head very much dba.stackexchange.com/users/47194/shiningstar

Kermit

4:48 PM

@billinkc their questions are 5 stars

swasheck

i think it's a crock that users can't drop into negative numbers for rep

billinkc

5 stars out of a googol

Aaron Bertrand

out of 22 lakhs

billinkc

can this terribad question get 33 lakhs of likes?

Aaron Bertrand

@James I don't know what on earth that is but you should delete it. — Aaron Bertrand 23 secs ago

billinkc

4:58 PM

@AaronBertrand Did that apply to their SQL statement or something else?

swasheck

yay!!!!! more territorial pissings in meta

42

Q: Can we talk about the voting culture here on Meta?

I'd like to start with saying that I emailed @Shog9 back in August with the text below, because I'm not a particularly good writer, and I was hoping he'd be able to write up a nice post here. A couple weeks ago I learned that he's quite a busy person, so I'm posting myself now. That being said......

discussion voting meta

Aaron Bertrand

@billinkc the comment - I didn't even realize that was the OP.

billinkc

I respectfully disagree with Damien's comment there. A clean address was vital to our clients when I worked at the marketing firm. It allowed us to improve our deduplication efforts and lead to lower mailing costs

Aaron Bertrand

@billinkc that doesn't make the problem easy to solve, especially for someone who doesn't even know how REPLACE() works

ypercube

But he only said that this is a rare case. He could be right on that.
And that it is not easy to clean the address database. Which I guess you agree.

billinkc

5:13 PM

I guess I was fixated on the "rare case" part

Aaron Bertrand

So Microsoft put on a private Lync call on Windows 10 yesterday. Lync, unfortunately, has an inherent limit of 250 people, so lots of people were turned away.

250 people, max. Even if you're Microsoft.

billinkc

But we have downloads so as long as you don't want to interactively ask questions, it's fine

swasheck

250 people is the limit for Lync conf calls?

is that the takeaway?

Aaron Bertrand

Seems like it, though not sure if voice only is different from video

billinkc

That or they're dumb in trying to dogfood their own product when it's woefully inadequate to handle the traffic

traffic/volume/whatever

Aaron Bertrand

5:21 PM

That could be it too. At least the recording doesn't have Mandarin over-dubbing

swasheck

we use lync here. we love it so much that we're evaluating polycom and cisco

Lamak

> I tryed everything

somehow I doubt that

-2

Q: Sql Server Manager Error

Can somebody resolve this query for me please. CREATE TABLE[dbo].[Kupci] ( UserIduniqueidentifierFOREIGN KEYREFERENCESaspnet_Users (UsersId) NOT NULL PRIMARY KEY, Ime NVARCHAR (50) NOT NULL, Prezime NVARCHAR (50) NOT NULL, Telefon NVARCHAR(50)NOT NULL, Email NVARCHAR(50)NOT N...

sql-server database

swasheck

today, i am in a bad mood

ergo

what about ritual, vegetable sacrifice? did you try that? — swasheck 22 secs ago

@billinkc ... i thought that size didnt matter

billinkc

It doesn't. As long as you're satisfied, that's all that matters

Kermit

Who's abe?

> put on hold as off-topic by Abe Miessler, Lamak, Aaron Bertrand, Kermit, swasheck 14 secs ago

Aaron Bertrand

5:24 PM

Lurker

Kermit

dem lurkers

swasheck

@Kermit at least he's honest

I'd give trouble shooting on your own a shot before you come here. This is a pretty basic error, and if you look at some example create table scripts I'm betting you will see the problem very quickly. If not, come back here, tell us what you tried and where you are stuck. Voting to close for now. — Abe Miessler 6 mins ago

billinkc

Huh, I'm sure this isn't a bad data issue at all. Contract entered into force 2014-04-09 but it was terminated for new business (basically a newer version) on 2008-06-01

swasheck

@billinkc you should use mongo for that

billinkc

I miss the Too Minor edit reject reason

stackoverflow.com/posts/26166033/revisions

Seems my mood is a bit foul as well

Dem points

Just got a down vote on one of my SO answers that has over 100 upvotes. Really asshole? my answer is downvote worthy?

Tweeted by vongillern on October 2, 2014 at 5:31 PM

Aaron Bertrand

5:34 PM

Too bad tweets can't be down-voted.

Kermit

Honest Abe

Aaron Bertrand

@vongillern Entitled much? They're 2 whole magic unicorn points. Relax and go have a drink - away from the computer.

Tweeted by AaronBertrand on October 2, 2014 at 5:42 PM in reply to vongillern

Kermit

wtf

swasheck

i feel like i missed something

Kermit

no idea why that was all deleted

bluefeet

5:46 PM

@swasheck that's how I felt last week

when y'all discussed the PASS stuff. I came back and everything was removed

Aaron Bertrand

so, cleaning up

@bluefeet sorry, same idea, can't leave that stuff sitting around in the clear

Kermit

@AaronBertrand definitely don't want the heap to get their heads chopped off

swasheck

@bluefeet that's because we were talking about real people. this is clearly not a person

bluefeet

@AaronBertrand no problem. I just read the deleted stuff

swasheck

your wish is my command

Aaron Bertrand

5:48 PM

YES!!!!!

swasheck

... but you should learn to spell. that's not the proper english use of the letter, "q"

Aaron Bertrand

Whoops

swasheck

is that one of his products?

Aaron Bertrand

No this is from a Microsoft slide deck

swasheck

oh

Aaron Bertrand

5:54 PM

There are other typos on the same deck, too

swasheck

maybe they just really like Ginger

billinkc

We have reimagined the dictionary

Shawn Melton

6:16 PM

I don't mess with SASS beyond installing it but saw that client wants to install an instance of SQL Server with SASS, but have a database on it with just procedural code. To populate SASS cubes they would need to talk to an database instance that has data to pulls the data into SASS cubes right? Is that data strictly stored in the SASS database and not within database engine database?

billinkc

SSAS is going to store its own copy of the data in its internal storage system. But yes, when it goes to process the cube, it needs to be able to talk to the source database

I think I parsed your question correctly ;)

Shawn Melton

Yeah that is what I was looking for and what I thought from reading a bit.

Funny part they won't this on a data warehouse server that houses a DW for medical data...I see a security issue alone with just having prod and dev on the same server.

the processing of cubes with SASS would seem to be something that would greatly affect performance wouldn't it?

James

@ShawnMelton yes

billinkc

When you go to process a cube, it beats the every loving sh*t out of the database

It runs distinct queries to ensure the keys you defined really are unique, pulls back all the data in the tables/named queries you've defined, etc

Aaron Bertrand

Yes they should probably read the data off a secondary on a different machine (restored backup, log shipped copy, etc)

billinkc

6:20 PM

It can be quite the site to behold

James

@ShawnMelton more if the source database and ssas are on the same server. but the read queries will still be pretty demanding on the source database even if they're on separate servers.

@AaronBertrand's idea about using a read-only copy as the source is good

Aaron Bertrand

Shawn Melton

Yeah, I don't even know why they would want this all on one box

Aaron Bertrand

^--- Canadian

James

Most of the cube processing I've looked at involes lots of giant sorts that tend to spill to TempDB

We have some pretty bad SSAS cubes at work. It makes me sad.

The dimensions have string keys. As I understand it, that means we have to remove data and do a full process every week to avoid running into this limit: francescodechirico.wordpress.com/2012/07/04/…

Mark Storey-Smith

6:26 PM

@James Nasty!

Shawn Melton

What is generally used to view the processed data in SASS cubes?

James

@ShawnMelton Mostly Excel I think. The cube browser in SSMS if you're a DBA.

I talked to a company that had some .NET web apps that used cubes as data sources, but haven't got the impression that this is a common use case.

Shawn Melton

SSRS is not common use then?

billinkc

It is, well can be

Depends on whether they build reports out using SSRS or just have them build their own via Excel

Shawn Melton

Ah.

billinkc

6:33 PM

Or both, some click paths are common but there's always the one person who only knows what they want when they see it

Aaron Bertrand

groan

0

Q: How To Remove Space From Middle Of String

Trying to get rid of a space. Current Results: P O BOX 140 Desired Results: PO BOX 140 --This script did not work update table set column= replace(column, 'P O', 'PO') from table

sql sql-server

JNK

"A: With an application layer."

Aaron Bertrand

Is this guy really going to walk through every single use case he comes across and post a bad question about it?

James

@AaronBertrand Only if people keep answering, I guess.

Aaron Bertrand

@James easily solved

billinkc

7:10 PM

Resetting the comment timer is obnoxious

swasheck

7:55 PM

first time ever seeing these query plan operators

JNK

@swasheck what's that from?

Transcript for

The Heap™ – Consultancy ©®