« first day (1200 days earlier)   

3:13 AM
Hi DBAs, Code Review moderator here. Would you accept this question for migration?
Q: Making a new column and setting default values equal to the COUNT of a certain condition in SQL?

user40128I have 2 tables. (table1, table2) Now, I would like to do something along the lines of: ALTER TABLE table1 ADD counter INT DEFAULT (Select COUNT(table2.car_type) FROM table2 WHERE (table1.car_type = table2.car_type)); That's the closest syntax I can come up with, but just by looking at it I kn...

2 hours later…
5:31 AM
@200_success It would certainly be on topic on SO. Advanced SQL problems are officially on topic here too, but that one doesn't seem to require advanced skills. But then, I'm not an Oracle person, don't really know Oracle's capabilities well enough.
5:49 AM
Thanks for your opinion.
6:07 AM
@200_success Second opinion. I think it would (also) be on-topic here, though it is not a stellar question by any means.
7 hours later…
12:43 PM
Anybody in here interested in baseball because that was hands down the funniest game of baseball I've ever seen.
12:57 PM
@AaronBertrand isn't in yet is he?
1:12 PM
Not a big rounders fan, myself
Ohhh snap OP took my calendar table advice!
A: Select query for report with known number of rows

ZaneAlright what you want to do is ge yourself a Calendar Table these are incredibly useful and every database should have one. On the off chance that that link goes down here is the script included. /** Create Date Dimension Table **/ /* Create First numbers table for key generation */ CREATE TABLE...

So glad he's doing that instead of that stupid union.
Q: SQL Variable Declaration Error

SeánI am able to connect to a SQL Server 2012 instance using (Oracle) SQL developer. I a trying to create some dynamic SQL to query the database. However, when I run the SQL statement: DECLARE @SQLquery as NVARCHAR(MAX) = '' then I get the following error message: Error starting at line : 2 ...

Anybody have any experience with tape backup and robotic pickers and such? I got a old ML6000 dumped in my lap and don't even know the terminology well enough to ask a well formed question.
@rfusca I've seen one once...
1:27 PM
if by 'seen' you mean ' I know tons about it' - then perfect ;)
@rfusca I know my last company used them a LOT but I never got involved
Yeah my dad showed my the tape room at bring your son to work day when I was like 8 so I'm pretty much an expert.
lol great
the VP is like "surely you just stick the tapes in and 'let it do its thing'"
Anyone able to decipher what this lady is talking about?
Q: Handling Early Arriving Facts or Inferred Members in SSIS

user3549004I have been working on an SSIS solution which would handle the early arriving facts into a dimension with no luck so far. Supposedly, table Loyalty Scheme is related to Partition table and Company table. In the transaction, loyalty scheme ID is NULL with Partition 1 and Company 1 then i'd have to...

1:51 PM
code dump --- fix it

user3545953The following tables were created in SQL server, and the corresponding values were inserted. CREATE TABLE Client (ClientID VARCHAR(4) PRIMARY KEY NOT NULL CHECK (ClientID LIKE 'C%'), FirstName VARCHAR (15) NOT NULL, LastName VARCHAR (15) NOT NULL, DateOfRegistration DATE NOT NULL, ProblemDes...

Please think about it, read the man page if necessary, and ask if you still have a specific question afterwards. — Ed Morton 12 mins ago
Not having an explanation is exactly giving a man a fish. In this example your code snippet is the fish. You gave it to him. Hence your answer was flagged. As previously stated I declined the flag and suggested you elaborate. If you don't want to elaborate fine I don't care it's just a suggestion to improve your answer. — Zane 20 secs ago
I don't think he understands how that metaphor works.
2:18 PM
I'm going to punch that guy in the head.
Don't put user objects in master!JNK 1 min ago
@JNK why not all the cool kids are doing it.
I think they were confused about why their trigger didn't get run
But you're getting this error because the trigger never gets to process. It doesn't get to process because your statement is violating a FK constraint, which is checked BEFORE trigger execution. Your triggers are for AFTER UPDATE which means the update has to process. — JNK 1 min ago
Woah why are you converting the date to VARCHAR??? — Zane 6 mins ago
I'm thinking this guy has over complicated his problem
@PowerUser yeah but you don't do your WHERE clause this way?! — Zane 31 secs ago
AHHHH! Store dates as DATE dammit!
2:45 PM
why do it one step when you can do it in 12?
I can't believe how often these come up. It's insane. It's not like they gave the DATE datatype some obscure name. I just don't get it.
People confuse FORMATTING with the DATA
So they want to store it as YYYYMMDD or whatever
That way when they do a SELECT * it looks "right"
@bluefeet One reopen vote and one delete vote, nice. That's pluralism all right.
@AndriyM not very surprised by that :)
Oh @bluefeet
Q: Not able to formulate query to combine different row values in single row using pivot table

ubaid ashraf masoodyBelow is the actual table In the table above: 1) FEID is the examination ID which remains same for one exam, like ist semester examination of particular class. So it will remain same for all rows in above table as it consists of data of single exam always. 2) To store result of single stude...

2:53 PM
@Zane tl; dr;
3:08 PM
@bluefeet Fair enough.
@MikeFal worst drill I've seen in ages.
@Zane Eh?
@MikeFal Wild game.
What drill are you referring to?
They stopped chasing and collapsed around the net.
I figured you were referencing the game. :)
Yeah, they were playing a prevent defense with 2 goals up, which makes sense. But the Avs have to much scoring potential.
3:11 PM
You have to keep chasing the puck in a 6 on 5 situation. Force them to move where they don't want to. Towards the last few second they all just crowded around the GoalTender which is not helpful.
Hell of a play stopping that dump from going in.
That's probably my biggest fear.....the Avs like to play with fire.
My biggest fear is the psychological effect of giving up that game. The Wild are an emotional team they need to get back on that horse and take a home game if they want to win.
3:35 PM
Hmm I can't get to review without it throwing an error?
@bluefeet know of any issues with that?
@Zane huh?
what the error?
I can't review anything. The button goes to an error screen.
Wonderful. Our "CTO" apparently just asked for write access to a prod database and got it granted. This won't end well.
@MikeFal /sigh
@Zane I'm guessing there is an issue. I can't get in either
3:38 PM
@MikeFal better get some auditing set up stat
Backups are current.
get some CDC going or something
I'm having the same problem except instead of a cute cat I have Derpy looking guy pointing at a keyboard. — Zane 6 secs ago
Q: SQL: delete all data from a table and all related tables

liv aI have a students tables & scores, classes I want to delete all data related to students who finished their studies before 2012 - so from the students table its delete from students where graduate year < 2012 but how do i remove all the records from the scores table? I can do a select to all id'...

If you don't know the answer should you be doing it?
Working now. The queue is packed.
4:35 PM
Super quiet in here for a Friday.
Good Friday.
Holiday for a lot of people.
5:03 PM
@MikeFal wall street's closed so i'm at home
@MikeFal you're on 2012, right?
heh. well, you can spin up an XE for ddl mods and you can also watch all statements by his login :)
without bothering with CDC
5:29 PM
Dear lord on this good friday grant me the strenght to deal with my co workers many misconceptions about sql server.
@Zane i liked it when i told a dev that he didnt blow out tempdb which caused locks ... instead, he was trying to take write locks out on the same objects
he escalated to management level because i wouldnt just make tempdb bigger
@swasheck lol
I don't care how you do it just get me more TempDB!
@Zane same dev said that sql server doesnt have mvcc which makes his job so much harder
@Zane insane game last night, eh?
@swasheck I'm not familiar with it but looking at the wiki
@swasheck Yeah I was telling Mike that I knew we were doomed in that last minute once the Wild stopped chasing and started collapsing around the net.
@Zane basically it's snapshot isolation (not exactly, but close enough)
5:37 PM
@swasheck You know. I think the difference between us and people like that is we are aware that we don't know everything about SQL Server and constantly look to learn more. Instead of just getting an idea in your head and running with it forever.
That play to save that empty net goal was amazing.
@Zane that's what separates me from that dev. i'm so ignorant about so many things ... but i like learning so i balance my ignorance
johnson's really coming around (finally)
nothing says good friday like homebrewing a saison ... amiright?
So I don't get why teams switch to the protect the lead mentality. It doesn't make sense to me. What you were doing was working so why switch?
@Zane more opportunity for mistakes
5:53 PM
You have more to lose when you are winning
I don't like it. What you were doing was clearly working keep being aggressive.
i'm generally inclined to agree with you, @Zane ... it seems rarely work well
Q: Left Join giving wrong result

HiteshI am having 3 tables Products, Order, Order_Placed Products---------- Order---------------- Order_Placed ________________________________________________________ prod_id | ------- | order_id | ---------- | prod_id |order_id| 1000----------------2001-------------------1000|2001 1...

Q: is there any constraint on repeatition of record in sql

Ayyan AlviI have column of name username which have record of usernames and every user name is repeating more than one is there any query or something like trough which i can display every user name maximum one time

Ohhh come on!
6:17 PM
@Zane look at that dudes other questions
"How can I have a column increase by one automatically?"
I imagine people like that having a panic attack when they forget how to breathe.
@Zane notice who answered both of those awful questions
6:36 PM
@bluefeet He lives entirely off of the lowest of the hanging fruit.
@bluefeet this is ridiculous!
A: how to apply auto increment query on specific column

user3384956i also have the same problem thanks bro for asking that question

@JNK What a useless turd.
@Zane oops its gone :)
@bluefeet :)
6:51 PM
@JNK: FYI, I've just effectively invalidated your comment by editing the answer.
Emergency question, any way to reset AWS RDS MySQL privileges for main user?
Tried to connect this morning, main admin account has mainly N for privileges and thanks to AWS there is no root access
@AndriyM thanks for the heads up
@rodling sorry no idea
I think most people here have limited knowledge of AWS and even more limited MySQL so you are looking at a pretty tiny intersection of the 2 in a venn diagram
I use AWS but we have a full server not just DB access
7:08 PM
@rodling Did you alter the permissions on your RDS admin account? Is there some reason you need to reset perms?
7:48 PM
@MikeFal i didnt do anything, i woke up to flush No across the privileges
I'm out!
Have a good easter if you celebrate that sort of thing.
see ya zane
8:46 PM
Something for dba?
Q: In SQL Server, should I force a LOOP JOIN in the following case?

JohnnyMTypically, I recommend against using join hints for all the standard reasons. Recently, however, I have found a pattern where I almost always find a forced loop join to perform better. In fact, I am beginning to use and recommend it so much that I wanted to get a second opinion to make sure I a...

Sure why not.
That was fast. It is good to have a mod around @bluefeet :)
@MikaelEriksson sometimes
9:17 PM
@JNK He removed my qualifier. And lost an upvote.
how dare he question @PaulWhite
@PaulWhite . . . Most databases don't support the syntax SELECT COUNT(*) WHERE 1 = 0. — Gordon Linoff 50 secs ago
Gordon is in a literal frame of mind today it seems.
@PaulWhite today? :)
He can keep my up-vote because he answered the question.
Besides, I feel sorry for people on SO.
Ah, I see he edited the comment. Everyone's happy again.
Hm. What's wrong with the in-lining there?
> @PaulWhite . . . Most databases don't support the syntax SELECT COUNT(*) WHERE 1 = 0. But I get your point and edited the answer appropriately. – Gordon Linoff 13 mins ago
9:35 PM
@PaulWhite it looks like it was deleted
Oh. Fair enough.
@AndriyM The qualifier is back. I think he understands now.
@PaulWhite Yup, saw that.
Still slightly confusing. He first says just "never", as in "absolutely never". Maybe he thinks people rarely use COUNT(*) without GROUP BY.
"Slightly confusing" is a win on SO.

« first day (1200 days earlier)