Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 The Yak Corral
 Developer Presentation

Author  Topic 

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2005-01-05 : 13:31:21
I'm starting to work on a presentation for developers about SQL Server. I'm tentatively titling it "Everything I wish a SQL Developer knew". I'm looking for a little help picking out topics.

Assuming you're a DBA what things do you wish all your developers knew? What things are you glad they know?

Assuming you're a developer what things have you learned that you wish you'd known all along?

These can be high-level architectural things (stored procedures are good) down to the very detailed (prefix tables names with the owner).



===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-01-05 : 13:39:32
Right off the top - the most useful technique I have learned (and yes I learned it here on SQLTeam!) was the usage of a tally table. A very powerful technique I have used many time since becoming aware of it.

But I think just visiting this site reading the posts and working through many problems has made me a better SQL (read. set-based thinker) developer. So my general advise, FWIW is - practice, practice, practice solving various kinds of problems.
Go to Top of Page

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-01-05 : 13:55:01
Simple query optimization stuff. How to read the execution plan and how to determine the # of reads a query causes.

Joins
ANSI Joins vs WHERE clause joins

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-01-05 : 15:05:34
Graz, it sounds like you're talking about a presentation to application (i.e. .NET, Java, etc.) developers who are more or less rookies in SQL Server? I agree with ehorn that a Tally table is very handy, but I think that would be more advanced than your target audience, although it might make for a cool whizbang "Bet you never thought of this" type of demo.

I wish more developers understood there is a difference between iterative (cursor) and set-based processes. I find most developer think iteratively because they have to for their applications and then try to transliterate that to SQL with awful results (as we have seen here on the site when we get cursor questions).

A couple other ideas: Concept of Transactions, ability to issue SQL calls directly from the application (e.g. calling an INSERT stored procedure with parameters rather than doing Recordset.AddNew), and a little about locking or lack thereof. Just because they are displaying data on a screen does not mean that that record is locked and someone else may update it which may lead to stomping on each others' changes. And conversely, things they may do which will cause a lock when they don't mean to.

---------------------------------------------------------------------------------
Infoneering: Information Technology solutions engineered to professional standards.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-05 : 15:11:04
I wish developers, the kind that Mark describes, would use Query Analyzer to build their queries rather than Enterprise Manager. They usually use the GUI tool to do their joins, then copy that into Query Analyzer to do the rest. If they were more comfortable with writing queries, I wouldn't have to format their queries just to read them since EM formats them bad.

Tara
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-05 : 15:57:20
Not to use SELECT * and use ANSI JOIN syntax.
Normalization 101 .
How security works on SQL server.

Just a quicklist fttomh.

rockmoose
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-05 : 16:00:50
Why Bill?

You expecting any developers at PASS?

I go for an entire section on how Cursors could be replaced 99% of the time..

Or is that 5 9's?



Brett

8-)
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-01-05 : 17:03:35
**Looks at current project**

Oh yeah, DON'T use "intID" as the PK of EVERY TABLE in your database!

**Looks at last project**

Don't use index, user, group and log as table names!

**cries**


Damian
Go to Top of Page

jhermiz

3564 Posts

Posted - 2005-01-05 : 17:09:26
Graz,

Coming from more of a programming background I would love to understand security more, its rather strange to me, everytime I think I understand it something goes haywire. An intro on who / what / when /etc...sa, dbo, etc... just some good practices for creating security in a database types of rights, differences between system administrator, public, dbo...all that nutty stuff.

Stuff drives me bonkers!

A new beat on the web -- http://www.web-impulse.com
Go to Top of Page

jhermiz

3564 Posts

Posted - 2005-01-05 : 17:10:34
To add onto tara's suggestion...

This is probably why, just got this from a developer today:


CREATE PROCEDURE MassInsert
as
--first grab from ECN table
--SELECT ECN.UniqueID, ECN.ECNID FROM ECN ORDER BY ECN.UniqueID
SELECT ECNID, MAX(UniqueID) AS EXPR1
from dbo.ECN
GROUP BY ECNID

--then insert it
INSERT INTO Results (Results.EXPR1,Results.ECNID )
SELECT ECN.UniqueID,
ECN.ECNID
FROM ECN
GO



A new beat on the web -- http://www.web-impulse.com
Go to Top of Page

Sitka
Aged Yak Warrior

571 Posts

Posted - 2005-01-05 : 17:23:02
Argh, post messed up.

I wish someone would start an Official XML Rant Thread.
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-01-05 : 18:04:18
quote:
Originally posted by Merkin
**Looks at last project**

Don't use index, user, group and log as table names!

**cries**


Damian

Ooh OOh OOH! On that note, my (least) favorite is naming a field: Date. Oh yeah! THAT is descriptive and informative. Not to mention reserved.

---------------------------------------------------------------------------------
Infoneering: Information Technology solutions engineered to professional standards.
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-01-05 : 18:13:36
Well this guy is right into his Hungarian notation, so it would be :

dateDate

I'm not kidding...



Damian
Go to Top of Page

jhermiz

3564 Posts

Posted - 2005-01-05 : 18:55:15
quote:
Originally posted by Merkin

Well this guy is right into his Hungarian notation, so it would be :

dateDate

I'm not kidding...



Damian



You mean:

dateDate is bad ...

Changes all code, at least he didnt call it Date on its own.

Hungarian notation, I use something like that in asp:

Drop Down List: ddl
List box: lst
Grid: dg
etc...

Maybe his is "Drunken" hungarian notation =)


A new beat on the web -- http://www.web-impulse.com
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-01-05 : 18:58:07
I do use it in ASP.NET to name controls like that too. I try to avoid it in other places. It still creeps in when I'm doing classic asp and vb6, old habits die hard

But I hate it in C# and especially in SQL.



Damian
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-01-06 : 00:33:09
Graz, maybe give them a SProc template as a "take away".

Something that has proper error handling examples in it, and SET NOCOUNT and all that gear.

We put SET XACT_ABORT ON in ours so that they go tits-up the moment an (i.e. untrapped ) error occurs.

And SET ARITHABORT ON for tose pesky indexes views ...

And a sample BEGIN TRANS + COMMIT / ROLLBACK block for Update Sprocs

And something about the limitations of BLOBs - developers shouldn't just expect to use a BLOB column with impunity.

For the one-man-band Developer-cum-DBA I'd add something about Database maintenance. Why FULL RECOVERY creates huge LDF files - 'coz there will be no backups, right?!

And actually how a good backup strategy is a life saver. We run our development databases with higher levels of logging (FULL where production uses SIMPLE, backup every 10 minutes where Production uses Hourly) because we KNOW we are going to screw up and need to "roll back".

Even running the poxy MS maint. wizzard and setting it to BACKUP ALL USER DATABSES would be a fair place to start.

Kristen
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-01-06 : 03:17:18
1. how to optimize their codes

2. how to read execution plans

3. not name sp starting with sp_

4. explore the possibility that something must have gone wrong with the application or network side before blaming the server aka DBA

5. that security/restrictions are for the good of the company and not
to make their life miserable

6. not to use accounts for specific applications to another application

7. not to use enterprise manager for queries (wish they'd use the query analyzer - am thinking of removing it from them if they won't use it)

8. not to include specific formats in sprocs! leave the formatting in the client side please...

9. how does does a server change as years pass by, hope they don't compare the server 10 years ago still with 5 million data and now which has 50 million

10. normalization, it surprises me that there are still some programmers who are happy with 30 columns then with 15 null fields, than separate them into more scalable structures.

11. any advise coming from the DBA doesn't mean, he's boasting or taking in control but the advise is for improvement

now i'm ranting... ha ha ha, i'll stop there


--------------------
keeping it simple...
Go to Top of Page

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2005-01-06 : 05:04:35
Errorhandling: @@Error and @@RowCount are your FRIENDS!

Templates - .tql , useful for repetitive queries.

Naming standards - it isn't the standard that is important, but consistent implementation, and documentation. Having said that, maybe a comment that it is a bad idea to use spaces ' ' and reserved words as names (imho).

Foreign Keys - why RI is your friend (aka "how did that data get there?").



*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-01-06 : 05:45:00
how to use Profiler...wisely.

and for the small-shop(s)(??)
how metrics and database planning are worth the 5 minutes it takes to fill in the numbers (table sizes, sla levels, expected trans per second, etc)
a quick guide to hardware planning....or "RAID for Dummies".
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-06 : 09:09:27
quote:
Originally posted by Sitka

Argh, post messed up.

I wish someone would start an Official XML Rant Thread.



Wish Granted

http://weblogs.sqlteam.com/brettk/archive/2004/09/01/1999.aspx



EDIT: Oh, and how about the benefits of buying AND reading about book on the subject, or at the very least learning to use Google.

And if they don't know where to start, how about a bio on Edgar.



Brett

8-)
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2005-01-06 : 11:26:03
Wow! Don't stop. Any other ideas?

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page
    Next Page

- Advertisement -