February 26, 2002 at 5:33 am
I have found that many bits of wisedom exist when it comes to programming. Thus I have, in my many years, developed 3 rules of thumb I follow that still bite me everyday.
1) It can almost always be done better.
2) Placement is everything.
3) Also double check 1 and 2
Ex.
1)
SELECT * FROM TABLEX
when all you need is COL1 and COL2 is better
SELECT COL1, COL2 FROM TABLEX
2)
Always remember when using subqueries that any where clauses directly related to the subquery run better inside that subquery rather than outside.
SELECT BLAH FROM TABLEA
INNER JOIN
(SELECT DISTINCT [NAME], [PHONE] FROM TABLEB) AS TABLEB
ON
TABLEA.[NAME] = TABLEB.[NAME]
WHERE
TABLEB.[NAME] IN ('JOHN', 'JAMES', 'ADAM')
Should be written
SELECT BLAH FROM TABLEA
INNER JOIN
(SELECT DISTINCT [NAME], [PHONE] FROM TABLEB
WHERE
TABLEB.[NAME] IN ('JOHN', 'JAMES', 'ADAM')) AS TABLEB
ON
TABLEA.[NAME] = TABLEB.[NAME]
Query 2 will perform much better than the first (especially when TABLEB is large).
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
February 26, 2002 at 5:34 am
Sorry, hit post in error before I said.
What rules of thumb or words of wisdom help you as a programmer?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
February 26, 2002 at 7:42 am
A short list:
K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 26, 2002 at 9:34 am
remember to put that WHERE clause in the DELETE ... 🙂
had a few scary ones with that
February 26, 2002 at 11:39 am
Always try to write it as well as you know how the first time - odds are no one will ever look at your code until it breaks.
Good code is elegant.
Code explicitly to stop as many exceptions as you can, don't rely on the error handler as a pseudo-event driven programming model.
Andy
February 27, 2002 at 3:06 pm
Always remember that programming is a service organization function. If you make software your customers do not use, you will not be a programmer long.
KISS is real (Keep it simple stupid)
If builders built buildings the way programmers write programs, the first wookpecker that came along would destroy civilization.
An expert is one who knows more and more about less and less until he knows absolutely nothing
and of course.....
Work smarder and not harder and be careful of yor spelling
William H. Hoover
Louisville, Ky
William H. Hoover
Louisville, Ky
sweeper_bill@yahoo.com
February 27, 2002 at 4:47 pm
1. Always search for a SET oriented approach.
2. Smack developers who present you cursor solutions.
3. Have someone else look over your code if possible as a sanity check. This includes asking for opinions if you are unsure of something.
4. Don't combine developers, #3, and T-SQL
5. Adhere to standards, even if they are your own.
6. Force developers to adhere to your standards.
7. Choose a simple solution, but think about the exceptions and border cases.
8. Test. Can't say this enough. As soon as you think your tiny change will only affect one area, you will change something or break something that brings management to your desk.
9. Enjoy life. There will always be more work. Always be bugs. Always be enhancements. Remember that and take time away from work to enjoy your family and life.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 13, 2002 at 5:29 am
Standards always seem to be an issue, so define a standard and stick to it...
Oh yes, and learn to avoid cursors with a passion!!
Deon
April 4, 2002 at 5:58 am
Better yet:
Put a BEGIN TRAN before, (specially if it is late late..), triple check the resultset, pray, then COMMIT, and then, by instict, erase the COMMIT of your QA.
... of course forget and go on, there is nothing you can do anymore..
quote:
remember to put that WHERE clause in the DELETE ... 🙂had a few scary ones with that
April 4, 2002 at 6:04 am
Totally agree on 1. and 2.
Use a bat if necessary
🙂
quote:
1. Always search for a SET oriented approach.2. Smack developers who present you cursor solutions.
3. Have someone else look over your code if possible as a sanity check. This includes asking for opinions if you are unsure of something.
4. Don't combine developers, #3, and T-SQL
5. Adhere to standards, even if they are your own.
6. Force developers to adhere to your standards.
7. Choose a simple solution, but think about the exceptions and border cases.
8. Test. Can't say this enough. As soon as you think your tiny change will only affect one area, you will change something or break something that brings management to your desk.
9. Enjoy life. There will always be more work. Always be bugs. Always be enhancements. Remember that and take time away from work to enjoy your family and life.
Steve Jones
April 4, 2002 at 1:44 pm
Always run your code first in development enviroment
April 9, 2002 at 10:16 am
-- #
-- ### Add as many comments as you can to all your T-SQL
-- ### Ensuring that a reader of your code knows
-- ### Exactly what it is the following logical
-- ### set of instructions is expected to do.
-- #
DELETE sysusers
-- #
-- ### Then check that the above statement has completed
-- ### Successfully!
-- #
SELECT @l_Row_Count = @@ROWCOUNT
IF @l_Row_Count = 0
BEGIN
-- #
-- ### Operation Successful
-- #
PRINT 'PHEW!'
END
ELSE
BEGIN
-- #
-- ### Operation Unsuccessful
-- #
PRINT 'OOOOPS!'
END
-- #
-- ### End of Sillyness
-- #
April 11, 2002 at 8:41 am
Check the data. Does the result set make sense? Are there duplicates?
April 12, 2002 at 8:53 am
Admit to your mistakes....
I once deleted a whole database (OOPPPSSS). The vendor had created a file and not documented it (oopppsss), the folder it was in was also used for database files and finally got full. The database locked up. I went through files to see what I could get rid of. This was on a UNIX box...I am familiar with Windows. SO, when I double-clicked (to open) a file....I had forgotten that *.sh files are the same as *.exe and ran their delete database script. NO "OOOOPPPS, I didn't mean that" was built in, the commits were built in, so there was NO undo/rollback option. Luckily, for me at least, we had all the data else where. Eventually, there was enough of this stuff from the vendor and we said goodbye and went with a MS SQL Server option.
But the point is....after it happened, I admitted to my mistake so that we could figure out what happened and how to solve the issue. Not admitting to it would have compounded the problem.
April 15, 2002 at 7:29 am
Anytime a programming dilama seems impossible to be solved, I remind myself "There has to be a way to solve it". And I found a way each time.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply