The Heap™ – Consultancy ©® - 2013-06-11 (page 1 of 2)

6:41 AM

@MarkStorey-Smith I might just be me going mad

I think the answer is: "no, use serializable isolation and two statements"

(I'm trying to prevent a race condition with another transaction comitting between the insert and the delete)

Morning all

@JackDouglas I don't understand what you want to delete.

And what is the PK of sale?

@ypercube sale_id :)

insert into sale(sale_at, product_id) values(sysdate, 1);
insert into sale(sale_at, product_id) values(sysdate, 1);

those two are gone in the result

they get 'split' into two other rows

it's sort of a MERGE, but I don't think merge can insert multiple rows per match?

It's not a very clear question :(

The Union query seems to be equivalent to: select sale_at, COALESCE(child_id,product_id ) from sale LEFT join split on(parent_id=product_id);

@JackDouglas So, in this example, you want to leave the 2 rows (with product_id=2 untouched) and the other 3 rows deleted and "split", so 6 new rows inserted in place of these 3?

ConcernedOfTunbridgeWells

7:03 AM

good morning

7:27 AM

Morning all

8:48 AM

SQL server deadlocks. Insta-fix with ROWLOCK and NOLOCK, eh?

0

A: How to avoid sql deadlock?

The way I resolved this issue at my workplace is by introducing ROWLOCK and NOLOCK. When you perform an insert or update on a particular table row, you add a ROWLOCK to it. This will prevent any other updates happening on the same row and the other update will wait until the row lock is released...

9:02 AM

That guy couldn't fix a sandwich.

@MarkStorey-Smith What's all this about reads being mostly 64KB?!? The world's gone mad.

9:23 AM

@gbn the guy was close to crying

@Marian yeah. saw that

18 hours ago, by gbn

@MarkStorey-Smith yes. The phantom mod does tend to act like a biblical burning bush at times

@PaulWhite It's how our mod rolls

Uh huh :-/

@Marian You made a good (and nice) comment on that

10:17 AM

@ypercube sorry, had to look after 6 children for an hour

@JackDouglas It's ok, I was away.

I'm still mentally frazzled so I might spout nonsense, but yes

@ypercube to that

I can do the insert and then the delete, but the problem is that another transaction might commit between the two, at least if I understand MVCC correctly

I should probably check that again

@JackDouglas which question? Oracle or PG?

@gbn oracle

does it have SSI?

Q: How to combine `delete` and `insert` operations in one statement?

10:30 AM

0

for the following data, I'd like to be able to delete some rows and insert others giving the result below. Is this possible with a single statement (eg with the merge statement)? create table product(product_id integer primary key); insert into product(product_id) values(1); insert into product(...

@gbn yes, alter session set isolation_level=serializable

it's actually not 'serializable' (a misnomer), but good enough I think

Like PG 9.1 SSI I mean

wiki.postgresql.org/wiki/SSI

so that a transactions will fail if the underlying data changes from another transaction

@gbn ah, no it hasn't

if you mean pg 9.2?

whereas classic MVCC allows the Marble problem

9.1 added it I think

Also en.wikipedia.org/wiki/…

oh yes, sorry

10:36 AM

Sybase/SQL Server type locking has this problem less because writers block readers

Oracle's 'serializable' seems to do what I want (it ignores any rows inserted/comitted by another transaction between the insert and delete in the main transaction (but neither transaction fails)

@gbn yes, you default to something stricter than MVCC?

@JackDouglas We have ANSI Serializable. SSI achieves the same by a different method

That Wikipedia article says:

Upshot: Oracle is shit for banking systems. Discuss

> Snapshot isolation is called "serializable" mode in Oracle[2][3][4] and PostgreSQL versions prior to 9.1,[5][6] which may cause confusion with the "real serializability" mode. There are arguments both for and against this decision; what is clear is that users must be aware of the distinction to avoid possible undesired anomalous behavior in their database system logic.

@gbn you can still 'lock table' on Oracle :P

10:40 AM

@JackDouglas That'll prevent skew alright

> Snapshot isolation has also been used[1] to critique the ANSI SQL-92 standard's definition of isolation levels, as it exhibits none of the "anomalies" that the SQL standard prohibited, yet is not serializable (the anomaly-free isolation level defined by ANSI).

Interesting stuff really.

@gbn I'm confused about the difference between Oracles serializable isolation and 'real' serializable modes

I can guess...

Oracles serializable isolation = PG pre 9.1 without SSI

"real" serializable = ANSI en.wikipedia.org/wiki/Isolation_(database_systems)#Serializable

Also MVCC = "no lock"

> When using non-lock based concurrency control, no locks are acquired; however, if the system detects a write collision among several concurrent transactions, only one of them is allowed to commit. See snapshot isolation for more details on this topic.

So what badness can you get under SI which is prevented by 'real'?

or is it just that you have to handle transactions bombing out

Snapshot isolation

In databases, and transaction processing (transaction management), snapshot isolation is a guarantee that all reads made in a transaction will see a consistent snapshot of the database (in practice it reads the last committed values that existed at the time it started), and the transaction itself will successfully commit only if no updates it has made conflict with any concurrent updates made since that snapshot. Snapshot isolation has been adopted by several major database management systems, such as SQL Anywhere, InterBase, Firebird, Oracle, PostgreSQL and Microsoft SQL Server (2005 and ...

ah

Write Skew

aka overdraft protection wiki.postgresql.org/wiki/SSI#Overdraft_Protection

ah, so we can get the same protection by using 'select for update' carefully. hmmmm

nice link!

I think I'd rather have SSI in Oracle too

10:48 AM

Does Oracle have MERGE?

but in my case, both transactions are doing writes, so I don't think there is any difference between SSI and 'serializable'

@gbn yes

that's what I was trying to figure if I could use

@JackDouglas As I understand there is. Txn1 and Txn2 start on same rows

Txn2 reads old state from MVCC because Txn1 got there first

Txn2 trashes Txn1 changes because of no SSI/ANSI Serializable

@PaulWhite We had a similar exchange a while back when he insisted that SQL Server takes page locks by default :/

Morning all

ConcernedOfTunbridgeWells

How do

@MarkStorey-Smith Morning

10:51 AM

@JackDouglas What SQL (or pseudo SQL) do you want to run? Not clear in the question

hello :)

insert into sale(sale_at, product_id) select sale_at, child_id from sale join split on parent_id=product_id;
delete from sale where product_id in (select parent_id from split);

MERGE then, no?

@gbn can merge insert multiple rows per match?

er... in SQL server yes

It does a row by row target vs source insert/update/delete (if specified ofc), don't need to do all

WHEN (these rows are) MATCHED .. UPDATE

WHEN (a source row) NOT MATCHED BY TARGET .. INSERT

WHEN (a target row) NOT MATCHED BY SOURCE.. DELETE

will try again :)

10:56 AM

@gbn thanks, hope he understood. Though I doubt he did that :).

Composable DML too. Does 'Orable allow that?

delete from sale where product_id in
    (
     insert into sale(sale_at, product_id)
     OUTPUT parent_id
     select sale_at, child_id from sale join split on parent_id=product_id
    ) X;

feck

@gbn hmmm, don't have the BY TARGET and BY SOURCE syntax

Lordy. docs.oracle.com/cd/B28359_01/server.111/b28286/…

it's an UPSERT

no DELETE

@gbn unfortunately not

Piece of shit

11:00 AM

pg does (writable CTEs)

@gbn now now

:)

he he

@gbn there is DELETE, but it is restricted too

oracle-base.com/articles/10g/…

what about my (eventually) composible DML idea?

1 min ago, by Jack Douglas

@gbn unfortunately not

that one?

just above "feck"

4 mins ago, by gbn

delete from sale where product_id in
    (
     insert into sale(sale_at, product_id)
     OUTPUT parent_id
     select sale_at, child_id from sale join split on parent_id=product_id
    ) X;

11:02 AM

unfortunately not :)

A: SQL Server concurrent transaction issue

2

Do it all in one with some composable DML? INSERT INTO order_item (item_no, order_id) SELECT X.item_no, @order_id FROM ( MERGE INTO items AS tgt USING (SELECT TOP (@whatever) item_no FROM items WHERE status = 'Unused' ) AS src ON tgt.item_no = src.item_...

but, hey, we've had windowing functions for about 10 years ;)

Since 2005 in SQL Server. Only 8

he he see last comment here: sqlblog.com/blogs/jamie_thomson/archive/2012/07/13/…

is composable DML always deterministic on SQL Server? How does it enforce that?

"this could be done in Oracle much easier than this.... :-/"

orly?

11:05 AM

@gbn docs.oracle.com/cd/B28359_01/appdev.111/b28424/…

fair enough

A: What are the advantages of sequences in SQL Server 2012?

am I the only one that thinks Tomtom is a bit of a tool?

0

Can someone elaborate on what this feature is useful for? Generating sequences, is that not obvious? Stuff like account numbers, order numbers, order numbers - that may not even be used as identity as in identity column.

@Marian his manner certainly doesn't come across very patient

@JackDouglas I'm not sure if he's here to help or to brag about his big dbs :).

@Marian Good command of English vernacular there :)

11:08 AM

@Marian both I think!

That question probably does need to be closed however

@gbn still learning :D.

@JackDouglas already voted, but Martin thinks it could deserve an answer.

Though I believe it would deserve a proper DBA.SE question, not that..kind of a SO question.

@Marian I think Martin is wrong in this case: it is too broad. Questios like "what can I use X for?" almost always are

@JackDouglas true.

@gbn Oracles Flashback stuff in general is amazing: dba.stackexchange.com/q/359/1396

@JackDouglas You may be selling, I aint buying...

11:14 AM

ha ha

MERGE is pants, plenty of other stuff is lacking, but there are lots of gems too :)

for the record I prefer pg :)

@Marian added my answer. bleeding obvious IMO

thedailywtf.com/Articles/Welcome-to-the-New-Order.aspx

@gbn yeah, saw it. Nice trick for the before trigger.

Mikael Eriksson

"You might want to grab a coffee for this bit." ..... ok, done, let's bring it on .... nope sorry, coffee did not help much.

@MikaelEriksson yeah, and he hates some of us. Not sure why. This guy is dangerous.

Geez I go for a 30 minute run and come back to 87 missed conversations. The heap is active this morning

Mikael Eriksson

11:25 AM

@Marian Not me!! I'm old.

@MikaelEriksson well, I know what a phone operator is. Saw on Discovery Channel. But this doesn't help me understand his blog :).

@bluefeet Run? Remember this: health is the slowest possible rate you can die

3

@gbn lol, yeah but I like to run.

Even if that means I will die slower.

11:54 AM

@bluefeet or do what I do, play four different sports on five different days of the week. No tedious running required. :)

@SimonRigharts what sports?

@bluefeet easports.com

:-)

@bluefeet floorball (think like ice hockey without the ice, skates, padding or hitting and you're fairly close), football (soccer), indoor football, ultimate frisbee and rock climbing (only indoor wall climbing so far, not brave enough for actual rock climbing yet)

@Marian Also that - NHL 13 and FIFA 12 ;) Not sure if I'm going to buy a PS4 at launch or not (definitely not buying the new Xbox, screw that)

@SimonRigharts you mean you're really getting out of the house for these sports? wow

@SimonRigharts wow, that is a lot of stuff. I just run, and go to a gym.

11:57 AM

@Marian Yup. I see sunlight and everything. :O

2

@SimonRigharts well I will definitely buy it sometimes near Christmas, if I can grab it. I hate XBox's live connection stuff. The PS3 I have now was brilliant for some games.

@bluefeet I used to go to the gym and do weights but now any night I'm not doing sport I'm like "praise be, I get a night off"

@SimonRigharts I'm jealous

@SimonRigharts you're my hero.

@MarkStorey-Smith Don't be, sunlight is dangerous. Vitamin D supplements are much safer ;)

11:59 AM

@SimonRigharts you sure you aren't smoking something? :)

@SimonRigharts I like your thinking!

@Marian Nah. At least I don't think I am. But if first year philosophy taught me anything it's that we can't be certain of anything

@Marian Yeah, the Xbox's connection requirements and used game policies are just draconian

@SimonRigharts well, then you'll die 100 yo. So you'll see SQL Server 2084. Will you switch to it till then?

@Marian I'll probably wait and see what games come out for PS4 first up

generally the release-day games don't match what I actually want

@SimonRigharts don't know about their used game policy, but the only thing I liked about the current XBOX was the camera stuff. That was indeed fine, not the shitty Eye thing from Sony. But games sucked hard.

12:04 PM

@Marian independent.co.uk/life-style/gadgets-and-tech/gaming/…

In short: for the new xbox, publishers can choose to charge what is essentially a transfer fee for game resale

be interesting to see if anyone has the stones to actually do it though

The new XBox in a nutshell:

2

3

@SimonRigharts that's shitty. And stupid. More power to torrents then :).

Q: Concatenating columns using CTE in SQL Server 2008

12:18 PM

I need to concatenate 100 columns and I am having performance issues...shocking

0

I have a table like NAME | TABLE_NAME ----------------- X1 | X001 X2 | X002 This table contains a name column which is nothing but description and a table_name column which is actually a table already present in the database. X001 Table has columns like X1_A, X1_B X002 Table has columns...

sql-server tsql sql-server-2008-r2 common-table-expression

Q: SQL inefficient queries, how to build one?

12:40 PM

if this question were any broader it would be an Adam Sandler movie

0

There are a lot of Questions and Answers on how to fine tune a query. They pretty much all assume you have some intelligence and have done your best to create a good query. My question is how do you optimize a query for inefficiency? What basic types of SQL query language will cause the most ...

sql performance code-efficiency code-inefficiency

@JNK :-) that is an interesting question, though.

a contest for the slowest query on a given data set.

@MarkStorey-Smith Ah, so the's not wrong; the knowledge is just a little dated.

@PaulWhite That's a polite perspective on it :)

@Marian Easy. WHILE 1 = 1 BEGIN DECLARE @i int END;

@MarkStorey-Smith A misspelled (misspelt?) one too!

@PaulWhite ok, ok... and a query that returns something significant for that data set :-).

12:54 PM

@Marian You beat me before I could type WHILE 1 = 1 SELECT 42 END;

@PaulWhite yeah, a row count could be significant ;). But you get the point :P.

@PaulWhite That should do a waitfor for ever in SQL Server. By design. Is there any Connect item for that yet?

@Marian The first one would one forever without producing anything significant. 42 is the most significant thing possible, so I hear.

42 (number)

42 (forty-two) is the natural number immediately following 41 and directly preceding 43. The number has received considerable attention in popular culture as a result of its central appearance in The Hitchhiker's Guide to the Galaxy as the "Answer to the Ultimate Question of Life, the Universe, and Everything". Mathematics * Forty-two is a pronic number and an abundant number; its prime factorization 2 · 3 · 7 makes it the second sphenic number and also the second of the form { 2 · 3 · r }. As with all sphenic numbers of this form, the ...

@PaulWhite yes, that's why I proposed it should do a Waitfor by design :-). Just watch the life go by.

I stopped reading at the word "Mathematics"

@Marian Oh I see. ha ha silly me lol

1:16 PM

@SimonRigharts Heh, the PS4 has already won the battle on the sharing games idea :-). techcrunch.com/2013/06/11/…

Yeah Xbox has had terrible publicity since they announced

@JNK well their marketing team sucks. Cause I'm sure it's not the devs that took the stupid decisions.

@JNK So I've seen. Though I have to say that the work on the kinect is quite impressive

yeah that is pretty cool

A: SQL - Counting from 2 or more Columns

> I swear sometimes I'm the only person on SO who cares to teach people proper programming.

0

I swear sometimes I'm the only person on SO who cares to teach people proper programming. The design in the OP is flawed. You should go for a 3NF design as per below (see http://en.wikipedia.org/wiki/Third_normal_form): /* Table for unique voters */ CREATE TABLE voters ( id INT UNSIGNED NO...

That user totally went after my initial answer when I wasn't even done editing.

1:24 PM

I was about to down-vote for your obnoxious statement that you are the only person on StackOverflow capable of guiding users to better "programming" even though the problem here is schema design. Thank you for removing it. — Aaron Bertrand 15 secs ago

First SELECT was broken, and now RAND. Whatever next.

@AaronBertrand I think the first comment was funnier even.

@StevenMoseley Guy, sometimes the schema is fixed, and users are stuck with it and can't just rebuild the entire application at the whim of some know-it-all on StackOverflow. — Aaron Bertrand 5 mins ago

I don't know of a place. Different people provide different help; if we were all robots doing the same thing, every question would have exactly one answer. I strongly recommend not going around down-voting people because their answers aren't as good as yours, or stating that you're the only person on StackOverflow that cares. It's pretty demeaning to everyone around you. — Aaron Bertrand 23 secs ago

1:46 PM

@JNK: I have a 2nd gbn here dba.stackexchange.com/questions/44231/…

Code Review bollixed me up yesterday

Any chance to merge please?

@AaronBertrand Has becoming a mod made you more mellow?

3

You can't always train a user on proper database design in response to a single question. You can't always teach someone to fish, and not everyone wants to learn how to fish. And you shouldn't lash out at others just because they're not as altruistic as you (and if you want to keep being that way, at least give them time to finish their answer before you start griping about it). — Aaron Bertrand 7 mins ago

@gbn a small gbn :-).

first answer and it's already going to be a great answer ;). Or whatever badge you get for 10 up-votes.

@gbn I'll ask. We can't merge ourselves any more.

@gbn I've sent an email asking for it

@JNK sorry, didn't see that was directed at you at first...

oh awesome

washes his hands of it

Q: Running two loops simultaneously to move a list to rows in two different columns of different lengths

2:01 PM

should this be migrated to SO?

0

eed help in below table updation using SQL in Ms-Access 2007 database. I've a table called table1 which has below entries: table1: -------------------------------------------------- | col1 | col2 | col3 | Col4 ------------------------------------------------- | A1 | ...

sql ms-access

Seems like they might have more luck with VBA eyes over there

Stack Exchange

2:17 PM

0

Q: Notifications about a new answer or comment can't works

today (one hour ago) I post a question History from SQL Agent Jobs (SQL Server 2005) all notification about new answer(s)/commnents were accesible only when I checked my question on Database Administrators site manually, not notified outside of Database Administrators during this time (question ...

support feature-request

2:30 PM

That is strange, I have always received notification to my global inbox from DBA.SE. — bluefeet 10 mins ago

^^ can someone respond to this comment to see if any notifications get sent?

@bluefeet i did and then it was deleted

why thank you @swasheck!

Why do I still check CNN.com?

what kind of totalitarian government does meta.dba run???

@Zane go to bbc.co.uk or aljazeera.com

2:33 PM

or dw.de

@swasheck no, it wasn't, it's still there.

@Marian mine keep blinking in and out

@swasheck It doesn't like you today.

Shawn Melton

dba.stackexchange.com/questions/25371/… Tempted to suggest this be closed, why in the world would you want to verify permissions this way?

@swasheck maybe it's because @FreshPrinceOfSO's runs? :)

2:37 PM

@Marian I'm open about my intestinal/bowel situation

Q: sql count record based on multipule condition from same table

please vtc, this is the exact same question that they previously asked

0

I have a table called products. ProductId| ProductName| ProductType| ProductSize 1 | a | yellow | 12 2 | b | green | 13 3 | c | yellow | 12 4 | d | yellow | 15 I want to get count of each product as a column at...

sql-server join count subquery where-clause

@bluefeet threw in a -1 too

@FreshPrinceOfSO I just hope you won't be. Stay closed, ffs :-).

@bluefeet and @Marian these are all plausible explanations

@Marian fixed fee service?

2:39 PM

@FreshPrinceOfSO yep

Awesome.

@MarkStorey-Smith i see you kicked the ant hill nicely ... dba.stackexchange.com/questions/43335/…

@BrentOzar is doing today a webex about DBAReactions :-).

@Marian link?

@AaronBertrand is doing a webex on using tools

2:47 PM

@swasheck link?

I can't attend either.

message is too long :)

brentozar.com -> see upcoming events

the first one is today's webex

gotomeeting.com/register/177069040

@swasheck All part of the service

@MarkStorey-Smith nice to see that there was no service interruption.

2:51 PM

@swasheck It's Kevin Kline actually

@swasheck ugh forgot it's blocked

@AaronBertrand well yeah ... I just forgot that @KevinEdwardKline is on SE now :)

@AaronBertrand I loved him in a fish called wanda

@JNK captain original :)

@swasheck Major Original, I got promoted!

2:55 PM

@JNK oh fine

3:08 PM

Got accepted for SQLSaturday Baton Rouge. Same talk as 2 years ago. But not in the crack of dawn slot - before lunch so hopefully a good alert crowd...

Topic?\

I got rejected for my PASS talk titled "Shit people do that pisses me off" not really sure why. :)

@Zane it just doesnt sounds quite right. it's technically correct but it just sounds grammatically awkward.

Too many F-bombs in the presentation apparently.

BBL meeting time.

@CadeRoux Grats! Interesting title for your talk ;)

@CadeRoux interesting.

you have a thing for turtles? :)

3:22 PM

@Zane Metadata - code generation and using extended properties for tagging database objects. Basically, table driven code generation, either for dynamic SQL or tooling up SQL through codegen. It's a beginning-intermediate talk - just working smarter, basically. There are lots of applications, but it basically touches on using extended properties instead of tables of tables if you want to make sure when tables or columns are dropped that the metadata doesn't hang around and cause problems.

@Marian Not really. It was just a combination of the layering and metadata idea and using tools to do the heavy lifting.

@CadeRoux Ah, I see. Interesting presentation anyway, saw the slide on Linkedin.

@CadeRoux Dang, I was hoping you were going to work LOGO into the talk

@billinkc Good idea.

3:38 PM

@billinkc LOGO?

Logo (programming language)

Logo is a multi-paradigm computer programming language used in education. It is an adaptation and dialect of the Lisp language. It was originally conceived and written as a functional programming language, and drove a mechanical turtle as an output device. It also has significant facilities for handling lists, files, I/O, and recursion. Today it is remembered mainly for its turtle graphics, though for tertiary level teaching it has been superseded by Scheme, and scripting languages. Logo was created in 1967 for educational use, more so for constructivist teaching, by Daniel G. Bobrow, W...

Eeegads there is an obscene amount of programing languages.

Even better

logosim.blogspot.com

LOGO was my first exposure to programming back in first grade

@Zane it was really good to play with as a 10 year old.

My first exposure to programming was SQL about two years ago...

3:40 PM

@Zane helloworldexample.net

@billinkc scratch that I learned some VB in high school

Just a few languages out there ;)

That way I could get a access database to do what I want.

@PaulWhite hiya

@FreshPrinceOfSO Hello!

3:44 PM

@billinkc No languages that start with a Z. I am disapoint.

I predict a Zane language in the (distant) future...

Zeno (programming language)

Zeno (after pre-Socratic Greek philosopher Zeno of Elea) is an imperative procedural programming language designed to be easy to learn and user friendly. Zeno is generic in the sense that it contains most of the essential elements used in other languages to develop real applications. The Zeno Interpreter was designed for use in Windows 95 and later Microsoft operating systems. The interpreter comes with built-in debugging tools, a source code text editor, and an on-line language reference. Zeno was created by Stephen R. Schmitt and is maintained by Abecedarical Systems. Example: Sieve o...

I always wanted a job programming in Oberon because it sounds cool

ZPL (programming language)

ZOPL

ZOPL is a programming language created by Geac Computer Corporation in the early 1970s for use on their mainframe computer systems used in libraries and banking institutions. It had similarities to C and Pascal. ZOPL stood for "Version Z, Our Programming Language". ZOPL is still in use at CGI Group (formerly known as RealTime Datapro), who ported it to VAX/VMS and Unix in the 1980s, and to Windows in 1998. It currently (2010) runs on Windows XP/2000/2003 and Red Hat Linux. The RTM (formerly ZUG) language compiler and runtime framework are written in ZOPL. Outside of CGI, ZOPL has not...

ZPL (short for Z-level Programming Language) is an array programming language designed to replace C and C++ programming languages in engineering and scientific applications. Because its design goal was to obtain cross-platform high performance, ZPL programs run fast on both sequential and parallel computers. Highly-parallel ZPL programs are simple and easy to write because it exclusively uses implicit parallelism. Originally called Orca C, ZPL was designed and implemented during 1993-1995 by the Orca Project of the Computer Science and Engineering Department at the University of Washingto...

@billinkc I've always wanted a job (janitor) at Google just so I can have google on my CV

3:54 PM

@billinkc SNOBOL sounds cool, too.

@Zane Start writing ZNOBOL and ZQL.

ZQL should be SQL with more awesome keywords

2

SELECT = GIMME

3

WHERE = BUTONLYIF

FROM = OUTTA

JOIN = HOOKEDTO

PIVOT = BLUEFEETED

3

@JNK a Zane Query Language :)

2

exactly

GIMME ColumnA OUTTA MyTable A HOOKEDTO MyOtherTable B ON A.cola = B.cola BUTONLYIF SomeDate > '1/1/1900'

7

Alright I am down with this language.

4:03 PM

AS = CALLME
ON = ?

Oh good one, @ypercube

I don't have a better one for ON

GIMME BIGGIE(ColumnA), SMALLIE(ColumnB), ColumnC OUTTA MyTable SMUSH BY ColumnC;

6

@ypercube if the key word was CALLME all of my columns would be aliased as Ishmael

^^^^ winnar

@billinkc wins

4:04 PM

@billinkc ROFL!

SMUSH BY is awesome

@billinkc Oberon is also the name of a Micro Brewery.

@Zane or "maybe"

@swasheck i was thinking it...

RAISERROR should be WTF

4:10 PM

@swasheck When I googled "Call me" to assure correct spelling of Ishmael I was annoyed that maybe was the top suggested.

@JNK ZOMGWTF

@swasheck That's THROW in SS2012

BEGIN TRY = HOPETHISWORKS BEGIN CATCH = FRACK

bill I think you should write this language

@JNK I'm a big fan of BUTONLYIF

I think it's time to open a microsoft ticket suggesting these changes.

4:13 PM

There is a certain appeal to documenting ZQL

If only to make it a preprocessor thing that does the substitutions when it's run. I wonder if I could pervert sqlcmd mode to make this happen

One of my projects never really started: PREZQL - Presentation Query Language for cross management of queries and presentations. Generate your PowerPoint from your SQL and be able to demo queries and return to the presentation.

Sounds perverse. I approve

@CadeRoux Sounds like vi capabilities

@ypercube now that's perverse

ConcernedOfTunbridgeWells

ESC: q!

4:27 PM

@CadeRoux Abso-effing-lutely - The system has a perfectly good data dictionary and metadata updates tend to fall in the cracks. I do quite a bit of stuff that generates code off the back of markup in the system data dictionary. Another argument I've had to have far too many times - "No, we are not going to deploy and maintain a 17 table configuration database for the ETL framework."

Q: After 5 years of working, SQL Server 2k5 Table now locking without any changes made to DB

3

I have a table: OrderDetails. For 5 years, only the odd Deadlock has occured. If it happened, it happened on indexes which was on OrderDetails table Now all of a sudden, deadlocks are everywhere - happens very minute. We have not made any changes to the application or database - the indexes reb...

sql-server-2005 deadlock

"the odd Deadlock has occured" :-)

4:59 PM

@Marian Deadlocks can be rough to troubleshoot kind of fun in a masacistic sort of way.

it's very rewarding if you can figure it out

5:15 PM

For android users: blog.everything.me/2013/04/25/congratulations-its-a-beta

@FreshPrinceOfSO I tried it a while ago

@Lamak and what happened

@FreshPrinceOfSO mostly....meh

@Lamak my manager just demo'd it to me.. really bad ash

@FreshPrinceOfSO bad ash?

5:24 PM

ass

@FreshPrinceOfSO oh, right. I wasn't too impressed. Still have the app, though, is a little faster now

6:27 PM

We have a new account like @sweden in New Orleans called @BeingNOLA in its second week: twitter.com/BeingNOLA - I am lined up for week of July 7. There are already three parody accounts for the neighboring suburbs: @BeingMetairie, @BeingLakeview, @BeingKenner.

6:47 PM

@PaulWhite Flicked through the slide pack from IE2 this afternoon. One of the call-outs in the IO module is "Misconception that SQL Server always reads extents". Funny as I daresay Paul is the person Denny is referring to in his comment :)

2

7:13 PM

Gave my thoughts on the new Mac Pro: madelinebertrand.com/2013/06/11/objections-to-the-new-mac-pro

7:23 PM

@JNK thnx for reminding the link to that blog post.

@AaronBertrand Cool machine.

@AaronBertrand agree with "And if you can’t justify the price of the machine vs. what power it will give you, don’t buy it." :) so i'm out

not that the price won't reflect the power of the machine...just that I probably don't need as much power as it provides (atm)

Excel question. Their REPLACE function sucks. Is there a more .NET type of replace that changes all of the values into the new thing?

Nevermind, it's called substitute

@billinkc Change ALL THE VALUES