Time to read: ~ 2.5 minutes
Stopping you from running ALL THE CODE
Stopping you from running ALL THE CODE
Time to read: ~ 2.5 minutes
We’re aiming to have your “Learning” resolution covered!
Time to read: ~ 3 minutes
I’ll be the one trying not to avoid eye contact and wanting to talk to everyone!
Time to read: ~ 1 minute
What is coming up in this month, you ask?
You are asking, right?
Time to read: ~ 3.5 minutes
As SQL Server related as YouTube is…so depends on what you watch I guess
By the time this goes to post 24 Hours of PASS will have just finished (it was great!) and PASS ( twitter | blog ) will be busy converting all the recording of the sessions and putting them up so we can watch them again.
This made me realize how much resources are available up on YouTube (I use it a lot for learning but there is a difference between doing and realizing). Not just with 24 Hours of PASS but with the Local Groups, Virtual Groups, and even some individuals have started to use it as well (Robert Cain, Bert Wagner, Rob Sewell, etc.)
Here are a few of the YouTube shortcuts that I use while watching these videos:
Press a number to go to that percentage of the video…
1 = 10%,
2 = 20%,
3 = 30%,
Funnily enough, I found this by pressing the keys at the top of my keyboard, sequentially going up and getting to the end of the video. Pressing 8 brought me to 80%, 9 brought me to 90% and 0, thinking it would end the video, brought me back to the start again!
So 0 = 0%!
“Home” and “End” go to the start and end of the video respectively.
Pause the video…
“K” or “Spacebar”.
Say that you want more control though, and don’t want to go to a certain percentage of the video, but just skip a few sections…
Forwards and backwards….
“Left arrow” = Back 5 seconds,
“J” = Back 10 seconds…
“Right arrow” = Forward 5 seconds…
“L” = Forward 10 seconds.
Video too loud for you?
“Up arrow” = Increase volume,
“Down arrow” = Decrease volume.
Not enough time in the day?
Speed up the playback
“Shift” + “.”
Then when you’ve encountered it at full speed and if you’re like me, your Imposter-Syndrome kicks in and you start freaking out at how eloquently the presenters are speaking and how fluidly they can type, let’s slow it back down again.
Slow down the playback
“Shift” + “,”
Having trouble hearing?
Turn on Subtitles/Closed Captions
Subtitles too small? You can adjust them.
“+” = Increase the subtitles font
“-” = Decrease the subtitle font
That’s it! Check these out an up your YouTube watching game.
Also check out those links, there’s more informative videos coming every day!
I tried to explain it but I hope you can do it better.
I’m not sure if it’s a good sign or a bad sign if that is the message that greets you when you sign into a chat room. It conjures up a response somewhere along the lines of “…oh no” but I like helping out and the person who asked this is bright and passionate about SQL Server; just not fully experienced with it yet.
So, drinking my first (of many) coffee of the day, I asked him what was wrong with it.
I have two tables. 1 with values 1,2,3 & the other with values 1,2,3,4,5. When I use delete exists, it should just delete 1,2,3 but table1 is always empty.
Hmmm, not an unreasonable assumption I suppose so I asked him for his code.
DECLARE @t1 table (id1 int); DECLARE @t2 table (id2 int); INSERT INTO @t1 VALUES (1), (2), (3), (4), (5); INSERT INTO @t2 VALUES (1), (2), (3); DELETE FROM @t1 WHERE EXISTS ( SELECT * FROM @t1 AS d1 JOIN @t2 AS d2 ON d1.id1 = d2.id2 ); SELECT * FROM @t1;
That should return 4 and 5 but @t1 is empty! What’s wrong with it?
…what the problem is here, I knew what the problem was here. My question for you though is how would you explain it?
I’ll give you my go but you make your own. Here’s the basic of that conversation.
You’re deleting everything from @t1 if your exists returns any rows.
You’re not limiting it at all. You need to remove the second call to the table, the one in your EXISTS, and link it back.
DELETE FROM @t1 AS t1 WHERE EXISTS( SELECT * FROM @t2 AS t2 WHERE t1.id1 = t2.id2)
> Ok, but when it like DELETE FROM @t1 WHERE EXISTS(SELECT * FROM @t2) it should return 4 and 5 too because @t2 just has 1,2,3.
Nope, you’re saying delete from table1 if your exists (RETURNS ANYTHING AT ALL) because you’re not specifying a link back to the first table
> but SELECT * FROM @t2 returns 1,2,3 and @t1 has 1,2,3,4,5?
Yeah but EXISTS technically returns a TRUE or a FALSE. So you’re saying DELETE if TRUE, not DELETE if table1 = table2.
> ahhhhh! Ok I got’cha now
It seemed to work, for him at least but I don’t really think that is the best way to explain it.
I had to specify two things
My problem is the documentation on EXISTS says (abbreviated)…
Specifies a subquery to test for the existence of rows.
Returns TRUE if a subquery contains any rows.
…and I’m not sure if that is any better of an explanation.
What I am sure of though is, if I want to continue to help out, I’ll need to know these topics implicitly and be able to explain them properly.
Be careful! Run this piece of code, the results may not be what you think
SELECT * FROM ( VALUES ( 1), ( 2) ) AS x ( i ) WHERE EXISTS ( SELECT MAX(y.i) FROM ( VALUES ( 1) ) AS y ( i ) WHERE y.i = x.i );
Straight away I want to apologise for the Nicolas Cage memes!
I just got back from the Reading User Group and I’m still in that post “User Group Glow”, also known as “Long Day Lethargy”, or “Twelve Hour Tiredness”.
They are great though! A chance to talk to other people in the SQL Server community, – a slight reminder that even if you work alone, people are still experiencing some of the same problems that you are (apparently everyone has to deal with multiple nested views, who knew!) – a chance to hear presentations on different topics, and pizza if you’re lucky (we were).
I realised during the session that the two presentations given during the User Group had a connection with a small issue with a table change I had been given with a developer.
Here’s what did not happen to me so you can watch out for it.
Raul Gonzalez ( blog | twitter ) was first up with this presentation “Database Design Matters, Seriously”, showing us the chaos that can occur from not giving some serious thought into how you design your database.
His session is not yet up on his blog as I’m writing this but it will be soon so keep an eye out for that!
Now he had a lot of good points but, for brevity’s sake, the main chaos theory points here are what happens if you don’t take advantage of
FOREIGN KEY CONSTRAINTS, and not specifying a columns
NULLABILITY (yes, that’s a word!). SQL Server is a powerful program with many performance optimizations provided for you, but it’s not omniscient; it can only use the information that you give it!
His points on
NULLABILITY (I mean, I think it’s a word) tied in nicely with the next presentation…
David Morrison ( blog | twitter ) followed up with his presentation on “Query Plan Deep Dives” (I had seen this at SQL Bits, but it’s a great session so I had no problems watching it again) and, as an aside, through his presentation he showed us the compound effects that can happen from not specifying a columns
NULLABILITY (it’s got letters so it’s word-like…)
Now his slides and scripts are up on his blog and they do a great job of walking you through them so check them out and you’ll see the compound effects they create!
Here’s a little teaser…
-- now I want all people who's email isn't in the email table SELECT /*C.FirstName , C.LastName ,*/ C.EmailAddress FROM dbo.Contact AS C WHERE C.EmailAddress NOT IN (SELECT E.EmailAddress FROM dbo.Emails AS E) GO
Which brings us back around to consequences or as I like to put it “How I Pissed Off A Dev By Refusing A Simple Request”.
To be quite honest, it was a simple request. A requirement came in to expand a column datatype up to varchar(100), so one of devs wrote up a simple script and passed it onto the DBAs to check as part of the change control procedure.
ALTER TABLE tablename ALTER COLUMN columnname varchar(100)
And I said no.
“WHY???!!!“, you may shout at me (he certainly did), but I’m going to say to you what I said to him. “Give me a chance to explain before you take my head off, alright?”
While there is nothing wrong with the above code syntactically (is that a word?) but I couldn’t approve it since that column was originally
NOT NULL and the above script would have stripped the column of that attribute! Business requirements dictated that it should not allow NULLS, and hey, who are we to argue with that 😐
Double checking to see if the column is NULL or NOT NULL allowed me to see a problem with that code, one that many people would consider simple enough to just allow it through at a quick glance. Which could have opened up problems further down the line if it had run…
Thanks to the User Group, I now know that it could have a knock on effect with our query plans as well!
ALTER TABLE tablename ALTER COLUMN columnname varchar(100) NOT NULL
There, that’s better!
DBAs get a lot of stick sometime, the “Default Blame Acceptors” or the “Don’t Bother Asking” but a lot of the time, it’s not that we want to say no, it’s just that we have to take into consideration a thousand little things that could snowball into 1 giant problem.
With the rise of DevOps, check out the latest T-SQL Tuesday, DBAs have gone from going
to somewhere along the lines of
“Not this second, let me check it out and see what we can do”
If pressed further, we may rely on the good, old “it depends” though. Hey, clichés are there for a reason; they work!
It just goes to show that, like the IT profession, DBAs are constantly evolving.
Continuosly learning, checking out new helping technologies, and going to User Groups are going to help us to deal with it.
Just remember, in the end,
P.S. I should probably mention that the Nicolas Cage memes are because of this blog post by Nate Johnson ( blog ) that I enjoyed so much that I had to do something in response. I’m not normally this crazy, I swear!