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. |
|
|
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.JoinsANSI Joins vs WHERE clause joinsMichael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
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. |
|
|
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 |
|
|
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 |
|
|
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?Brett8-) |
|
|
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 |
|
|
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 |
|
|
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 MassInsertas--first grab from ECN table--SELECT ECN.UniqueID, ECN.ECNID FROM ECN ORDER BY ECN.UniqueIDSELECT ECNID, MAX(UniqueID) AS EXPR1 from dbo.ECNGROUP BY ECNID--then insert itINSERT INTO Results (Results.EXPR1,Results.ECNID )SELECT ECN.UniqueID, ECN.ECNIDFROM ECNGO A new beat on the web -- http://www.web-impulse.com |
|
|
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. |
|
|
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. |
|
|
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 :dateDateI'm not kidding...Damian |
|
|
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 :dateDateI'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: ddlList box: lstGrid: dgetc...Maybe his is "Drunken" hungarian notation =)A new beat on the web -- http://www.web-impulse.com |
|
|
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 |
|
|
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 SprocsAnd 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 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-01-06 : 03:17:18
|
1. how to optimize their codes2. how to read execution plans3. 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 DBA5. that security/restrictions are for the good of the company and not to make their life miserable6. not to use accounts for specific applications to another application7. 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 million10. 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 improvementnow i'm ranting... ha ha ha, i'll stop there--------------------keeping it simple... |
|
|
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! |
|
|
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". |
|
|
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 Grantedhttp://weblogs.sqlteam.com/brettk/archive/2004/09/01/1999.aspxEDIT: 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.Brett8-) |
|
|
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. |
|
|
Next Page
|