Learn SQL Server the hard way

[SQLDenis]

Since it's kind of slow here at tek-tips, let's have some fun
The best way to learn SQL server is to make a mistake on the production server
Here are 3 of mine

Script out a table to make a history table and forgetting to change the name in the drop statement.... ooops

Scripting out procedures and have drop dependent objects checked.... oops

Selecting an Update statement and hitting F5 and then realizing you didn't select the WHERE clause... oops

Needless to say once you do this on a production box and you have to go back as fast as possible you will never do these things again
Of course backup DB and restore in time to a minute before the mistake happens is a lifesaver

Now these are mine, not as bad as that DBA in Utah who truncated a table before checking that the new file was there and then they had to stop every single train in the state for 4 hours

Now these are mine, how about yours..don't be embarrassed , let's hear it

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[DotNetGnat]

i have gone thru this:

Selecting an Update statement and hitting F5 and then realizing you didn't select the WHERE clause... oops

for both update and delete statements...oops...

-DNG

 

[SQLDenis]

I guess it's a common one to overlook

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Most of my ugliest horror stories are trigger-related.

In 2003 we had legacy DB/app for road assistance calls. It was ugly 0NF crap, and people who made it often fixed one bug by introducing 2-3 another. So I took things in my hands and wrote something like this:

create trigger blah on RoadAccidents
for insert
as
	update RoadAccidents
	set YrTrackNo = ( select max(YrTrackNo) from RoadAccidents where YR = Year(GetDate() )
go

I wasn't worried about multirow inserts - but YrTrackNo was NOT NULL so it happily stopped entire application... guess when? .

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

Good one, vongrunt

triggers cause a lot of problem for people who use @@identity instead of SCOPE_IDENTITY
that's also a very common thing beside multirow stuff

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[yelworcm]

Scripting a re-org on an Oracle database, with a SQLPlus session on the production system to check the required table sizes.

Hmm. I should test this to see if it has any errors.

<run script>

...Hey, the drop is taking an awful long time. I didn't think there was THAT much data in dev......oh no.....

Bet you didn't know that you can't rollback a table drop in Oracle. ;-)

 

[SQLDenis]

Oh yeah
between Dev, Test and Prod servers there is alway a possibility to drop stuff

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Regarding code I posted, things were slightly more complicated - and without typos - but result was same. As well as lesson learned: half-baked triggers = bye-bye party time.

Let's continue... the following code looks innocent:

do something
while @@ROWCOUNT > 0
begin

do something
end

Then one day you add trivial statement at the end of BEGIN... END block ... so trivial that testing on dev server seems like total waste of time. This happened to me three times: first time, last time and never again .

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

or stuff like this

insert into table ......
select @Rowcount =@@Rowcount
if @@error <> 0 -- yeah right....

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

So know you know when they ask for people with 8 years SQL experience it's not the good stuff that matters but how many times they screwed it up in 8 years
The more mistake you made before the less chance of making them in the future
So by hiring someone with 8 years experience you will eliminate all those mistakes at your shop since the person already did them somewhere else and won't do them again (in theory)

just my theory


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

Here is another one I did
One of the developers created a YesNo table; (yes it was called that way.)
It had 2 rows see below
0 No
1 Yes

So I go on the production server (my first week on the job) I see this garbage and just drop it
5 minutes later the calls started to come in that the access app was broken etc etc etc
I guess it pays to have proper documentation, metadata or something explaining the use of this nonsense table
Needless to say this got converted into a proc and the table went into oblivion


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SajidAttar]

Hi Everybody!

I am also learning SQL Server the way you have learnt.

I have also executed Update and Delete statements without Where clause.

I learnt sp_MSForEachTable Procedures (with drop statement) from this Forum (with ignoring the warning of the person who had posted it) and learnt sp_MSForEachDB procedures with the experience from it .





Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell

 

[danvlas]

Well, one damn open transaction that denied everyone's access to a 'central' table really kicked me repeatedly several hours, when everybody was just shouting and yelling and I couldn't imagine who or what was responsible for that. Then I realized: one error message that waited for the OK button to be clicked in the client application...

So...never leave a transaction at user's mercy (especially dialog boxes or error mesages thrown before rolling back or commiting everything).



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[SQLSister]

I made some significant changes to DTS packages without backing up the msdb database afterwards. Then the hard drive crashed. Recreating changes you made a couple of months ago is not a fun thing.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[pwilson]

On a Sybase app I was creating a package that pulled some random numbers. so i did something like

 SELECT Rand(Column * (datepart(ms,getdate()))

Well on sybase the seed you pass in there is a global seed. There was a row with a zero causing the rand(0) = 0.

Since Sybase sets the seed to a global seed, all uses of rand() were returning zero. This caused a number of issues. I have yet to see any other environment that treats the rand seed as global.

 

[icrf]

set implicit_transactions on
CREATE TABLE ##Orders ...
INSERT INTO ##Orders ...
exec master.dbo.xp_cmdshell 'bcp ##Orders in ...'

Oops. That kind of code has made me wish that kill process feature worked a little better, but alas ...

- Andrew
Text::Highlight - A language-neutral syntax highlighting module in Perl
also on SourceForge including demo