The Heap™ – Consultancy ©®

Chris Travers

01:23

I think I should stop posting answers on SO. Too many clueless people voting and commenting

3

Databases with large numbers of tables usually have them because they're being programmatically created. That is, planning isn't particularly relevant; tables are being used as the object level of granularity for naturally relational, but atomic (with in the domain) things. I'm thinking of things like result sets for batch processes that may have different shapes, where efficient access to recent data is far more important than dumping many billions of rows into a single overly-wide table. — Barry Kelly 24 mins ago

Ummm... I was talking about planning overhead in queries, which the database does.

Aaron Bertrand

02:44

Does Gordon go out of his way to format things terribly?

3

A: Create unique ID based on grouped UserId

Here is a method for getting the new value, assuming the userid is a string select (left(userid, 4)+right(100000 + row_number() over (partition by userid order by datefield), 4) ) You can also use this in an update statement, if you want to change the value...

swasheck

02:56

@AaronBertrand yes. he's big into Excel (amazon.com/dp/0470099518/?tag=stackoverfl08-20)

swasheck

04:07

frickin' finally

Chris Travers

04:39

swashheck, it is possible to get to 4096, but is really freakin' hard

Simon Righarts

I've seen a bot manage an 8192 before

Paul White

@swasheck Well done! My wife still hasn't cracked it. Driving her nuts. Ha ha ha ha ha

Simon Righarts

@PaulWhite Driving her nuts - more so than living with you already did? ;)

Paul White

It's a miracle she's not in a straitjacket

Simon Righarts

(also: Mike's going to be out here early May - what's your schedule like then?)

Paul White

04:46

I'll be around. Amanda's off to England for a month early May.

Mikael Eriksson

06:46

Is it just me that is having issues with syntax highlighting on dba?

It is the same color for string literals as there are for reserved keywords.

Here is the same code in SSMS

ypercube

09:32

@ChrisTravers I heard rumours about reaching 8192.

Chris Travers

@ypercube it ought to be possible with a significant bit of luck

ypercube

Yeah. With loads of luck, even 65536.

Chris Travers

Theoretically, you could do 2^16 with loads of luck

yeah

ypercube

or would it be 2^17 ?

you can get 2 or 4 as a random tile

Chris Travers

yeah, true

I have gotten as high as 4096

ypercube

09:41

have you played the 4D version?

2048 3D game

that's 3x3x3

and 2048 4D game which is 2x2x2x2

Chris Travers

10:18

Was I too harsh?

@BarryKelly, actually, anyone knowing anything about database performance (including anyone asking this question) will know what "planning overhead" is in this context. The improvements in the answer are solely useful for people approaching a question like this as a curiosity, lacking the background knowledge sufficient to make a working solution. — Chris Travers 2 mins ago

ypercube

No.

Do we need this question?

-1

Q: What is the market share of the major Database Management Systems out there?

From what I've heard oracle is the most dominant but I have no idea what percentage of market share it or any of the other Database Management Systems.

dbms

GrumpyPhil

10:54

@ypercube No. Clickety

ypercube

11:07

@GrumpyPhil Oh my. Look at the edit. It's equally vague now, if not more.

JNK

12:34

FYI the flaggers on DBA.SE are awesome

ypercube

Flug the flagging flaggers :)

ypercube

13:02

@JNK Can mods see how many flags a user has raised/been accepted?

JNK

@ypercube yeah I think so

You can see your own I think

ypercube

Yes, I can see mine.

JNK

but yes I can see them

Aaron Bertrand

@ypercube yes, we can see other users' flags

ypercube

Others' flag stats are exposed to as only when they suggest themselves for election.

JNK

13:04

we can see your whole flag history

@AaronBertrand those spam posts you just deleted, I would destroy the users

Aaron Bertrand

13:18

@JNK okey-done

Mikael Eriksson

Do you think the values in the running totals column is not exactly what you would expect them to be? — Mikael Eriksson 17 secs ago

Kermit

@JNK are you the NSA?

Lamak

@MikaelEriksson CHECKSUM(Amount)??

Mikael Eriksson

@Lamak :). I think that is funny.

Aaron Bertrand

This should not have 5 up-votes

4

A: Displaying the list of all tables in all database

You can use the sp_MSforeachdb procedure to do this create table #tablist(db sysname, tab sysname); exec sp_msforeachdb ' use [?]; insert into #tablist select db_name(),name from sys.tables;' select * from #tablist drop table #tablist

JNK

13:30

@AaronBertrand There have been some changes to how they track spammers

so destroying the account weights some algorithm

Aaron Bertrand

Good to know, thanks. I've got a few mod newsletters in my "to read" folder

Kermit

@AaronBertrand i wanna see

Aaron Bertrand

@Kermit you probably don't, pretty boring stuff on first glance

(Well, aside from the stuff specifically about you :-))

Kermit

@AaronBertrand is there a section for banned members

ha

i'm glad i have my own section

JNK

It actually is pretty boring

"Moderator announcements now come to your inbox!"

bluefeet

14:09

when you give an alias do you use AS or = or nothing?

JNK

@bluefeet @AaronBertrand will tell you to use 'Alias' = <something>

Aaron Bertrand

@bluefeet =

JNK

for me it depends on the use case

Aaron Bertrand

@bluefeet sqlblog.com/blogs/aaron_bertrand/archive/2012/01/23/…

The community is divided, though.

Just like commas at the beginning or end

JNK

Like if it's an INSERT...SELECT I will likely use AS since in that case I think the definition is more important but the alias is a reference

bluefeet

14:13

ok thanks

JNK

I think the most important thing is to be consistent in that statement

Aaron Bertrand

@JNK see even there I still use alias = because (a) it's consistent with all of my other usage and (b) I still want to line up the destination columns and source columns - I am going to match on the alias more likely than the definition (especially if it is a complex expression, constant, etc)

Not trying to change your mind, of course, just pointing out that it is very much a preference

JNK

Nothing makes me more bonkers than seeing SELECT Cola = X, Y as 'Colb', 'Colc' = Z, Q 'Cold'

Aaron Bertrand

Oh and please don't use single quotes around alias names

That really drives me bonkers - it's deprecated and makes the alias look like a string literal

ypercube

14:26

@AaronBertrand Funny how only SQL-Server and MySQL allow this

Kermit

would this be an accurate way to get the size of the database?

7

Q: Select SQL Server database size

how can i query my sql server to only get the size of database? I used this : use "MY_DB" exec sp_spaceused I got this : database_name database_size unallocated space My_DB 17899.13 MB 5309.39 MB It returns me several column that i don't need, maybe there is a trick to sele...

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

Aaron Bertrand

I think the answer sorting algorithm should be revisited. Right now if two answers have the same score (one +3 and the other +5/-2), they are considered "equal" and will sort randomly. I think the one with more down-votes should be sorted lower, even if it has the same overall score.

Lamak

@AaronBertrand I agree with the algorithm been revisited. But I'm sure it will come to a discussion between: "why the one with more downvotes should be listed last?, it has more upvotes so it should be listed first"

Aaron Bertrand

Yeah probably true. I think those arguments though are fueled by the people who just don't want to see anything change, ever

Lamak

You could think of the algorithm to sort answers being with "most rep obtained first" or something like that (which I don't necessarily agree with), just thinking what people that would prefer sorting by more upvotes would say

@AaronBertrand I agree with that

Aaron Bertrand

14:39

Perhaps it should be slightly different - same score answers should be listed first by those with 0 down-votes, then randomly among all of those with the same score but 1+ down-votes.

My problem is obviously like in this question where a bad answer was posted, and 5 people lemming up-voted it before anyone bothered pointing out how bad it is.

Lamak

sounds good to me. But won't to meta

Aaron Bertrand

I would not use the PowerShell method myself, but given a choice between PowerShell and sp_msForeachdb, I guess I would choose PowerShell.

It's sad that meta is so predictable that I don't even want to entertain the idea there.

ypercube

@AaronBertrand Problem solved. 5-3 now.

Aaron Bertrand

@ypercube ta

JNK

powershell / smo is hella useful though

During our replication debacle last week I was able to do a quick and dirty script to compare every index on our old database to our new database to make sure we had them all (which we didn't)

The DBA™ had a 6000 line script he was reading through to do the same thing

Aaron Bertrand

14:53

@JNK I would use SQL Compare for that - 0 lines of code for me to write

JNK

we used SQL compare

or The DBA™ did

I was just brought in as cleanup at the end

Aaron Bertrand

Well if he didn't do it right, shrug he probably wouldn't do PowerShell right either.

JNK

other issue he had with SQL Compare was it was showing stuff like constraints we had turned off the in secondary

no he wouldn't

FOR SURE

Aaron Bertrand

I'm not saying PowerShell isn't useful. Just wouldn't be my go-to.

JNK

It's one of those things that's easy to use for a lot if you are comfortable in it but I know what you mean

Aaron Bertrand

15:00

I grew up using T-SQL and DDL to solve most problems in SQL Server. PowerShell is re-learning, and in most cases it isn't any more efficient to write or execute even when you are comfortable in the language. I think a lot of problems are solved using PowerShell to prove it can be done, rather than because it's better than methods that existed before PowerShell came along.

JNK

The biggest benefit I have experienced regarding PS in SQL is being able to operate on multiple servers and simplifying interaction between the database and file system

being able to use .NET libraries is nice too

Aaron Bertrand

Yes, for file system stuff, sure. For operating on multiple servers, I used to use tools like SQLFarms Combine for that (I think this was bought by CA and no longer exists). Multi-script from Red Gate does the same. My pain points with PowerShell in this regard is that loading the modules can be tedious and not all properties / features are covered. So in cases where I have to tell PowerShell to execute some T-SQL or DDL, I feel like I'm shoving my head up my ass to find my face

Ultimately almost everything PowerShell is going to do to my SQL Server is something I could have told it directly. There are a few exceptions of course, but I find that most PowerShell advocates want to change everything to use PowerShell, whether it makes sense or not.

It is not always the best tool in the toolbox.

Zane

Looks like @MikeFal's PowahShell sense was tingling.

Mike Fal

@AaronBertrand As I powershell advocate, I agree it's not the best tool in the toolbox. No, just call it serendipitous timing. :)

The thing is, Powershell gives you a tool outside of SQL server to manage SQL server. I usually find that it's best used when having to manipulate the filesystem in conjunction with SQL Server, since SQL Server sucks at interacting with the filesystem.

Cleaning up old backups, for example. I'd much rather do that in powershell as it is cleaner and more consistent. The tools for SQL Server to do that directly usually break or create security holes.

Zane

Oh snap I forgot all about this question and it earned me some sort of badge last night.

12

Q: ISO Week vs SQL Server Week

Okay so I have a report that does a this week vs last week comparison and our customer noticed that their data was "funky". Upon further investigation we found it was not doing weeks correctly according to the ISO standards. I ran this script as a test case. SET DATEFIRST 1 SELECT DATEPART(WEEK,...

sql-server sql-server-2008

JNK

15:14

@MikeFal same

Or for testing stuff, like that lucene jibba jabba I was doing this week

Mike Fal

Yeah

JNK

which BTW I loaded 1million rows of a long comments field in and it works just as fast as it did against 7k rows of names

Mike Fal

Load and retrieval?

JNK

the load took a bit

but it's stored in the file system

the retrieval was instantaneous

I had it populating an autocomplete box of like 100 items and it happens as fast as I can type

Mike Fal

Wow, nice

That's webscale for ya' ;)

JNK

15:24

or something

I'm not planning to use it for data STORAGE but it's nice for stuff like that

from what I have read Lucene has issues scaling out which is when people use like Elasticsearch

Zane

stackoverflow.com/questions/22692223/…

JNK

@Zane you have a talent for finding those

Zane

@JNK useless questions?

JNK

@Zane confused ones

Zane

@JNK Oh yeah.

0

Q: Recursive SQL UDF for removing non-alpha-numeric characters

So I'm trying to create my first recursive udf (using MS SQL) to strip anything that's not letters and numbers from a string. This was inspired by this post (Replace with wildcard, in SQL) CREATE FUNCTION uf_RemoveNonAlphaNumericChar( @p_CharIndex int, @p_V...

sql sql-server recursive-query

How about that guy?

@AaronBertrand would probably like that question.

Andriy M

15:46

@MikaelEriksson or whoever is aware: Is there a difference between (./text())[1], as in this answer, and .[1]? I know that particular query works equally well with .[1], I just don't know if the two expressions are completely interchangeable.

Aaron Bertrand

15:59

@Zane ugh

Zane

Hahaha!

Aaron Bertrand

This kind of question is much less boring

1

Q: GETDATE() uniqueness when used on INSERT

Recently I was reading this blog post: http://blogs.msdn.com/b/sqlazure/archive/2010/05/05/10007304.aspx which contains this section: Picking a Clustered Index There are a couple of strategies for picking your clustered indexes; one of the easiest and best is to add another column o...

sql-server index insert

Zane

@AaronBertrand I was all ready to write an answer from my experience at wand where they were trying to do just that and it bit us in the ass because the precision wasn't enough. Then I saw your answer and decided I'd go lay by my dish.

Aaron Bertrand

Do you mean relying on the uniquifier bit you in the ass, or not using the uniquifier? What was the specific issue?

Were you trying to identify individual rows by timestamp alone?

Zane

Using just the GETDATE() for uniqueness.

Aaron Bertrand

16:14

Hopefully they are not trying to identify a single row based on time - they seem to acknowledge that the same value will be "okay" because of the uniquifier.

Lamak

@Zane but it's not necessarily unique

it's just that a clustered index will use an uniquier when that happens

Zane

Essentially digital menu boards would call home and tag a timestamp I don't remember the exact structure but they were getting rounded up/down to be the same time and was causing them some grief.

It was then I explained they needed to go by company/store/menuboard/callhometime to accurately track

Forgive me if I'm missing some details this was 2 years ago by now. Also that database system has so many flaws it's hard to really recall all the fires I put out.

Aaron Bertrand

I've seen people write C# apps that say INSERT ... SELECT GETDATE() and then try to find that row by saying SELECT ... WHERE col = GETDATE() facepalm

Lamak

lol

Zane

@AaronBertrand hahaha

ypercube

16:20

amazing ...

Zane

@AaronBertrand Did you say "You don't really know what you're doing do you? You're just playing around with your hands aren't ya?"

Mikael Eriksson

@AndriyM Yes, there is a difference. If you have an account on SQLServerCentral you can have a go at this QOTD. sqlservercentral.com/questions/XML/106182

When you specify the text() node that is what you get. If you just specify a node like . you will get all text values from that node and that nodes children. Mixed content XML like that is rare but SQL Server has to take care of that when querying for a value and the query plan is more complicated if you only specify a node instead of the text() node. So it can be different in result and it can be different in performance.

declare @XML xml = '
<root>
  <item>1987</item>
  <item>1654</item>
  <item>1321</item>
</root>'

select @XML.value('root[1]', 'varchar(20)')

Result: 198716541321

select @XML.value('(root/text())[1]', 'varchar(20)')

Result: NULL

And check out the difference in query plan for these two...

select @XML.value('(root/item/text())[1]', 'int')
select @XML.value('(root/item)[1]', 'int')

Zane

Does anyone think it would be of any value to build a presentation on common problems found on SO? Probably not right? People who do this sort of shit aren't really going to user group meetings.

Mikael Eriksson

I think this is as much XML The Heap can handle for one day.

Aaron Bertrand

16:39

@Zane that's basically what my bad habits presentation is based on

Almost every slide I say "I see people on SO doing this..."

Zane

For a while the trend was old join syntax. The current trend of questions I keep seeing is I want to format my dates like X when I insert them.

Heaps of questions where the answer is keep it in the presentation layer.

2

Q: Three Node Cluster that Allows For Two Failures

We are creating a three node "active/active/active" cluster with SQL 2008 R2 (not cutting edge, but that is what the application supports) and think it should be able to maintain quorum with two physical nodes down. What is the best way to configure this? My manager has been pushing for a witne...

sql-server-2008-r2 clustering

Andriy M

@MikaelEriksson Ah, I understand now, thank you very much!

Zane

It appears he was a little Overactive in his question..... okay I'll leave.

2

Q: Inserting a 30 million row table from a linked server

I have a large table that needs to be updated on a nightly basis from a linked server. Initially I had simply truncated the table and inserted all of the records but this was taking too long. I'm now using a merge statement to copy over only the records that need updating. However this appears...

sql-server t-sql linked-server

SSIS candidate methinks.

Kermit

Whistleblower says UNC put athletes in classes that never met and required only one final paper. This one got an A-. http://t.co/HShyr6ivGm

Tweeted by BryanAGraham on March 26, 2014 at 10:16 PM

Story

Zane

17:01

@Kermit The entire college system is messed up beyond belief. The states give more tax money to the schools every year. They rake in tons of money from athletics and donations. They don't pay adjunct professors anything and tuition is a cost rising higher than any other industry price(including healthcare) in the country. Where does all this money go?

Kermit

@Zane this country is broke

Zane

@Kermit Not going down that road. However I'm curious if anyone's read any sort of report or news story explaining how this is possible.

Aaron Bertrand

Yep, that sounds simple and easy to maintain

Well, we have one root SP that then calls 3 primary processing sub-SPs. The 3 sub-SPs all do similar things so those similar functions are broken out in to sub-sub-SPs. Combining everything back in to 1 SP would result in a nightmarishly huge SP that would be impossible to work with. I will try out what you've suggested, thanks! — Caynadian 4 mins ago

Zane

lol

JNK

@Kermit as a graduate of the UNC system I can totally believe this

Zane

17:12

0

A: Inserting a 30 million row table from a linked server

MERGE doesn't seem to be what you want here. You're only inserting new rows with a CREATION_DATE from the past 24 hours, right? SET NOCOUNT ON; DECLARE @Yesterday DATE = DATEADD(DAY, -1, GETDATE()); INSERT dbo.ANNOUNCEMENTS(column list) SELECT column list FROM [LINKEDSERVER].[DATABASE].[dbo]...

billinkc

threadless.com/product/5765/Beaker_Squared/tab,guys

Aaron Bertrand

I love this:

billinkc

3

Aaron Bertrand

> backing up is not an option

Because you're not taking backups already?

Zane

I may be wrong on this but if he's doing a high volume of inserts wouldn't SSIS/lookup on key/bulk insert be faster?

@billinkc That's pretty great.

Aaron Bertrand

17:14

@Zane maybe, my reading is that the table is 30 million rows, not the insert

Zane

I'm asking him the insert volume now.

I'm tempted to build him a tutorial but all those screenshots take time. I can't beleive I don't have one on the ready...

Aaron Bertrand

Worth testing maybe - given the same table structure, at what point does SSIS outperform a straight insert? My guess would be pretty high.

+ dev time, maybe SSIS isn't installed, etc

Zane

It depends on how he's set up too. Sometimes that look-up can be killed by network settings.

Aaron Bertrand

You may or may not be amazed how many customer systems I come across where SSIS is simply not available anywhere (bare bones installs).

bluefeet

@billinkc I'm gonna have to buy that

Zane

17:19

Yeah I suppose my experience is a lot different. People don't usually hire an SSIS developer if they aren't set up for that. :)

Aaron Bertrand

@Zane logical

On the sites, though, they're not asking SSIS developers for help. :-)

Well, at least not directly / explicitly.

So I tend to err on the side of "no SSIS expertise in-house; SSIS not even installed"

Zane

OP said he was going to work on an SSIS package so I assume he has it. However since his first solution was a linked server merge I fell he may not come up with a very effective package.

Aaron Bertrand

The big problem here is it seems he is comparing 30MM rows to 30MM rows (no filters on either side)

Filtering at the source for only the past 24 hours will likely have a dramatic impact, even if he keeps the merge.

Zane

Yup. My assumption is the package he makes will be two OLESources that select all 30MM on both sides. Use SSIS MERGE and the conditional split when not found.

JNK

he may wanna consider putting the logic on the source machine too

make a TVF or something that he can call

Zane

17:25

@AaronBertrand I'll have to test that out once I can finally get my server built.

Aaron Bertrand

I'm sure there are plenty of ways to further optimize. But a filter on the source will have the most dramatic impact IMHO.

Zane

Most definitely. That's pretty sizeable chunk to try to compare for change.

Zane

18:05

Ugh I feel absolutely miserable.

Kermit

@Zane have a vodka tonic

bluefeet

@Zane still sick?

Zane

@bluefeet I don't know I just feel tired and miserable. My insomnia probably has too many stacks at the moment.

I think it's more and more likely that this kid hasn't a clue as to what he's doing.

I have other tables with the same problem but don't have an updated date field. It seems like I cannot apply the answer to these columns: [STUDENT_ID] ,[SECTION_ID] ,[STUSEC_START_DATE] ,[STUSEC_END_DATE] ,[TERM_CODE] ,[ROOM_NUMBER] ,[BEGIN_PERIOD] ,[END_PERIOD] ,[SCHEDULE_NAME] ,[SCHOOL_NUMBER] ,[SCHOOL_NAME] ,[SASI_COURSE_CODE] ,[COURSE_TITLE] ,[CREDIT_HOURS] ,[INSTRUCT_LANGUAGE_CODE] ,[INSTRUCT_TYPE_CODE] ,[TEACHER_NAME] ,[TERM_CODE_SORT] ,[DPSID] ,[SCHOOL_YEAR] ,[SCHEDULE_STRUCTURE] ,[SECTION_NUMBER] — Geoff Dawdy 4 mins ago

ypercube

@Zane Kid, age 36.

Zane

@ypercube I didn't do my research on him.

JNK

18:19

@ypercube technical experience wise he appears fairly immature

Zane

18:30

0

Q: MSSQL JOIN the two tables below using WHERE

How can I JOIN the two tables below using WHERE ? I tried the following but I don't think that I'm doing it correctly: $query = mssql_query('SELECT T_EQ.[ID],T_ASSIGNED.[ID] FROM [TestTABLE].[dbo].[T_ASSIGNED] WHERE [T_ASSIGNED].[A_T_N]="' . $contact_lastname .'" JOIN T_EQ ON T_ASSIGNED.[ID] = ...

php sql

Yikes

If SO has taught me anything it's that all PHP websites are vulnerable to SQL Injection.

JNK

and created by people who wouldn't know a JOIN if it bit them in the ass

Mike Fal

I just hit an interview candidate in the face with the RTO/RPO question

Aaron Bertrand

@Zane PS did you listen to my RunAs Radio interview yesterday? Or just a coincidence?

Zane

@AaronBertrand No but I will now.

Aaron Bertrand

@Zane wasn't a shameless plug, just that Richard and I talked specifically about the storing dates as strings issue.

Zane

18:42

@AaronBertrand Correction wasn't an entirely shameless plug :)

The date thing comes from some knob who seemed to think he needed to have a date in SSIS in a certain format.

Holy geez you did this before Intersection.

@MikeFal You're doing interview at the new gig already?

Mike Fal

@Zane Well, I am the only DBA on site, it's a little more convenient to ferret out tech stuff before running him up to the next level.

Otherwise, they'd have to schedule someone from NZ and that gets tricky

Zane

0

Q: Why am I getting inconsistent case statement result based on whether value from rand function is stored in variable

I am writing a loop to build up and insert random strings with 5 characters. In testing which approach is fasted for building up these string I have run into inconsistent results that hopefully someone can explain to me. The three approaches I am comparing are setting up a table variable with al...

sql-server sql-server-2005

Woooah!

@MikeFal Makes sense.

Aaron Bertrand

@Zane VTC dupe

10

Q: How does this CASE expression reach the ELSE clause?

I need to load some test data into the Channel field on my Account table. The Channel can be one of 10 different values, so I thought I'd randomly assign the Channel one of the values using a CASE expression along with ABS(CHECKSUM(NewId())) % 10 like so: SELECT id, name, Channel =...

sql sql-server-2008

Zane

19:02

@MikeFal Did he sink or swim?

Mike Fal

@Zane For a mid level? He kept his head above water, but I wouldn't ask him to swim the English Channel.

Zane

Well enough to be a viable candidate?

Mike Fal

The only thing he could tell me about clustered indexes was you can only have 1 and it's usually the PK. When I asked what the diff types of replication there were, he told me "synchronous and asynchronous"

Zane

Also what was the exact question?

bluefeet

@MikeFal I would have stumbled on the replication question - I'd never had to work with it before

Aaron Bertrand

19:05

Replication is stupid anyway

Mike Fal

When I asked him if he could tell me what his favorite new feature in SQL 2012 was, he couldn't think of one.

Zane

My last phone interview I ended up rambling on for like 15 minutes about clustered indexes.

bluefeet

@MikeFal lead/lag

Zane

@MikeFal That's not fair I've been asking you guys that for about 3 weeks and haven't got an answer.

Mike Fal

Yeah, I'm not a fan. I've not done to much, but they use it here and so I expect at least to be able to say snapshot or transactional.

@Zane SQL On Server Core

Zane

19:06

@AaronBertrand ??

Aaron Bertrand

@Zane !!

Mike Fal

>.<

Aaron Bertrand

Rather than replication, I would rather do these things, in no certain order:

mirroring
AGs
log shipping
poke my eyes out

3

@MikeFal that was possible before, just wasn't officially supported

Zane

@AaronBertrand I have very limited experience with replication. Wand used it connect to connect to their client sites. It seemed to work okay. Unless the distribution server crapped out.

@AaronBertrand lol

Aaron Bertrand

@Zane yeah riding a bike to work works okay too until you get a flat tire or creamed by a bus

Mike Fal

19:09

@AaronBertrand Picky picky. :)

Aaron Bertrand

Well I am the pedantic beaker

Mike Fal

Yeah, I have yet to find out how and why they use replication here. I just know it's there.

bluefeet

@AaronBertrand how about bi-directional replication?

Mike Fal

But it's always struck me as overcomplicated.

Aaron Bertrand

@bluefeet no thank you, that just seems like a nightmare to me

bluefeet

19:11

@AaronBertrand we are currently trying to remove it

Simon Righarts

@MikeFal Oh ho ho. This is going to be fun. :)

Zane

They used it to pull transactions from the client to the home server and push DDL changes down to the client.

Mike Fal

@SimonRigharts When is it not fun? :D

Zane

0

Q: How to SELECT DISTINCT on 1 key column when querying multiple columns

I am working in SQL Server 2008 R2. I have a table in which 1 "record" is actually several records due to one of its fields having different values. This table has a lot of fields itself. All of the fields for a given "record" will have the same value, except for the one field that has various...

sql-server

@AaronBertrand record field field field record.

Aaron Bertrand

Please don't, my brain will explode

Zane

19:23

lol that question has more fields than Iowa. Ba Dum Tish.

Mike Fal

column row row column

Zane

I'll be here all night folks.

Mike Fal

@Zane Fortunately, the rest of us won't.

Aaron Bertrand

try the veal

Zane

19:37

0

Q: SQLserver query result output to Hive without worrying about columns names

I have a pretty complex SQL server query to return a report. I want to move the result table to Hive. I know that SQOOP has free-form query function built in. However, it clearly says that "The facility of using free-form query in the current version of Sqoop is limited to simple queries...

sql-server hive sqoop

Remember that time today when someone asked a bad question on SO. Man that was a hoot.

Mike Fal

@Zane youtube.com/watch?v=nCjsWpM9zFU

Zane

@MikeFal Oh my...

Mike Fal

I'm unsure how I feel about this

It's Michael Bay, which means "suck" about 80% of the time.

Zane

What happened to the puppet suits. That first movie looks great to this day because they did such a great job on those suits.

This may just be because it's the American trailer but it looks like it takes it self super seriously.

JNK

@MikeFal I think it means SUCK!!!

because nothing is subtle

Zane

19:50

Ninja Turtles is supposed to be fun.

Mike Fal

I want a Usagi Yojimbo movie

Zane

@MikeFal Never read that before. However, since it hasn't already been a movie or tv show it's unlikely to become a movie.

Mike Fal

Oh, you should read UY. It's pretty damn awesome.

Aaron Bertrand

Ugh. Why do people still choose compact edition?

I'm using SQL Server Compact Edition which doesn't support triggers. — Breems 6 mins ago

Zane

@MikeFal A name that is an acronym for words overblown and generic.

Mike Fal

19:56

@Zane Psh....

Zane

I was referring the Michael Bay not to you.

Mike Fal

Ah

Zane

You and your overblown powershell scripts :)

Mike Fal

Remove-Item @zane

Zane

20:18

Oh look it worked.

JNK

get-content 'The Heap' | ? {$_.GetType() -eq 'user' -and $_.Name -ne 'Zane'} | %{$_.MakeRoomOwner()}

I told my web devs to stop using inline sql in their pages today and they said OK, which was surprising

GrumpyPhil

20:40

Not been on a killing spree for ages. Gathers weapons

billinkc

21:01

I feel so dirty

TRY_CONVERT(datetimeoffset(0), CONCAT(STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(T2.POSTPONED_TIMESTAMP, 9, 0, 'T'), 12, 0, ':'), 15, 0, ':'), 18, 0, '.'), 7, 0, '-'), 5, 0, '-'), RIGHT(SYSDATETIMEOFFSET(), 6))) AS [PostponedDateTime]

Aaron Bertrand

Oh gross

billinkc

Source system provides date time as ye big string in local timezone and I get to deal with internationalness

And yes, I have a big disclaimer in the code stating since we don't know have a DST thing, it's going to be wrong twice a year

Zane

It's going to be incredibly weird to do a road trip without smoking.

GrumpyPhil

Makes TO_DATE and TO_TIMESTAMP seem sane

Mike Fal

If I start saying "zed", I hold @SimonRigharts responsible.

billinkc

21:11

zed is dead, baby

Transcript for

The Heap™ – Consultancy ©®