| Author |
Topic |
|
khalik
Constraint Violating Yak Guru
443 Posts |
 Posted - 2003-12-05 : 06:58:22
|
when we write SQL we have standards to follow... what are there... any link which talks all abt it....======================================Ask to your self before u ask someone |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-12-05 : 08:37:54
|
| This isn't a T-SQL standard, but a very important one. Don't have your applications log in as sa. Another one is don't store images in the database. Store a path to the image in the database. Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-12-05 : 08:47:25
|
| N O R M A L I Z A T I ON !!!!!!!!!!!!Other than the sa login, to me that's the #1 thing that must be fully understood before you create even your first table. Do a google search on it, I'll try to find a good link for you later today.- Jeff |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-12-05 : 09:10:37
|
| That's a pretty big subjectNormal Forms (as Jeff has pointed out)Attribute naming standardsCoding standards (never use SELECT *, ect)Version control standardsSecurity standards (as Michael pointed out, using roles, NT security, ect)Documnetation standards (Data dictionaries, business req docs, system life cycle...)Then there's always the developemnt with your hair on fire approach...Brett8-) |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-12-05 : 10:39:14
|
| Vyas --I tell you, I could not believe how much I agree with just about every word in your webpage. Very, very, nicely done; great examples!- Jeff |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-12-05 : 10:53:38
|
| Damn...Should be required reading when registering to join sqlteam...Brett8-) |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2003-12-05 : 11:15:08
|
I just had to post it.  quote:
Try to avoid server side cursors as much as possible. Always stick to 'set based approach' instead of a 'procedural approach' for accessing/manipulating data. Cursors can be easily avoided by SELECT statements in many cases. If a cursor is unavoidable, use a simpleWHILE loop instead, to loop through the table. I personally tested and concluded that a WHILE loop is faster than a cursor most of the times. But for a WHILE loop to replace a cursor you need a column (primary key or unique key) to identify each row uniquely and I personally believe every table must have a primary or unique key. Click here to see one of the many examples of using WHILE loop.
Vyas, Thanks for the link. Very Nice site!I Like the usage statistics on index.htm Maybe we can get Graz to provide something like this... |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2003-12-05 : 11:59:46
|
| Glad you guys liked that page. If Graz posts SQLTeam site stats, then my site stats will look too small ;-)--HTH,Vyashttp://vyaskn.tripod.com |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-05 : 12:31:15
|
| Besides that article, Vyas has a bunch of other interesting articles. They are well worth your time. I have learned so much by reading them.Vyas, I'm glad to see you posting here more often recently.Tara |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2003-12-05 : 12:42:29
|
quote:
Originally posted by tdugganVyas, I'm glad to see you posting here more often recently.
I'm just picking easy questions to answer, to reach your post count ;-)--HTH,Vyashttp://vyaskn.tripod.com |
|
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2003-12-05 : 12:59:22
|
quote:
Then there's always the developemnt with your hair on fire approach...Brett8-)
Oh You mean our standard operational procidure. I still have some doubts about remote storage of images that are updated regularly as they will not be part of the backups or restores.Anyone ever try creating a Image only Database?Say with tables with only two collums an Id field and the Image.Would this type of Deveopment bypass the performance hit as it is only called when actually displayed?This would also allow seperate control of the backups.JimUsers <> Logic |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2003-12-06 : 06:42:24
|
| thanks a lot guys.... VyasKN thanks for the link... most of all was saying abt normalization and all but i was asking standard when writing sql ..... shd be more than what listed...======================================Ask to your self before u ask someone |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-12-06 : 15:32:54
|
quote:
Oh You mean our standard operational procidure.
ROFLMAO!!! Thank you Jim, fortunately I just finished my sip of soda, otherwise it would've come out my nose. You're definitely not alone on that SOP.quote:
I still have some doubts about remote storage of images that are updated regularly as they will not be part of the backups or restores.
True, but at the same time, if the images are stored as files, even the most basic file backup software will only back up recently changed images. Obviously your company will be backing up other files besides the databases, so this is something you can add to it without any particular burden. And if you're talking about thousands of files where only a small percentage will change, this is a lot more efficient than database backups. Even a 50% change rate means there's still 50% you DON'T have to back up.quote:
This would also allow seperate control of the backups.
Not really, you're just tying them into a separate DATABASE backup. You also can't restore individual image rows in SQL Server, whereas you can easily restore an individual image file if you need to.The other thing to remember about image and text columns is that not all of their operations are logged, and edits made to an image column since the last full or differential database backup may not be restored. To me, this is a pretty serious argument against using the database for backing up image files...granted it's only due to the particular mechanism used by SQL Server, but since that's the database you're using, I don't see the benefit of changing the database backup procedures to serve the needs of image data that could be better stored elsewhere.quote:
Anyone ever try creating a Image only Database?Say with tables with only two collums an Id field and the Image.Would this type of Deveopment bypass the performance hit as it is only called when actually displayed?
Probably not. From the standpoint of simply selecting image data from a database, there is some evidence that shows it can be as fast as file access would be. The problem lies more in how the image is to be used by the client. If a web server is the ultimate transmission method, then storing it as a file will be better. A web server can cache a file and serve it from cache instead of having to read it from disk. And since most browsers have their own caches, the image can be pulled from the client's cache and not transmitted by the web server at allBut in order for the database to serve a web page, the data retrieval process has to be called, even if the data is cached by the database. Even with minor overhead, the image data itself cannot be as efficiently streamed to a web request. HTML uses an image tag which points to a file source, not a database or streaming source. Streaming it from a database would require it to either be written to a file (duh!) or use a custom program to render the image on the client (constructing an ASP page to grab the image still incurs database overhead) And client-side browser caching either wouldn't work at all, or would cache an image with no way of knowing it needed to be refreshed (and would therefore need to refresh the image each time it requested it) No matter how efficient your program might be, nothing will be faster than pulling an unmodified image from local browser cache. |
|
|
|
Wingenious
Starting Member
11 Posts |
Posted - 2003-12-06 : 17:50:45
|
| Here's a document that contains about 2.5 pages of suggested SQL coding standards (along with several other topics)...[url]http://www.dbaction.com/Architecture.pdf[/url] |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-12-06 : 21:19:06
|
| Note: there are some deviations.E.g., I hate indents in code texts. They are absolutely useless for me. |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-12-06 : 21:52:16
|
quote:
N O R M A L I Z A T I ON !!!!!!!!!!!!
Jeff, I can bet it will be a tough job for you to designa NOT normalized DB.In general, in order to manage to avoid normalizationsomeone has to have too quirk and "cunning" brains..(I think the best part of politicians has brains of this kind)So, your warning yell sounds like "Don't eat egg's shells!!".. |
|
|
|
DBRanger
Starting Member
6 Posts |
Posted - 2003-12-07 : 01:52:57
|
quote:
I hate indents in code texts. They are absolutely useless for me.
I'm very glad that I do not have to work with your code.Several of the earlier posts in this topic seem quite similar to many other topics. The posts are from self-proclaimed experts who simply lavish praise on each other rather than answer the question posed in the beginning. |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-12-07 : 05:55:06
|
| Hm.. moreover, before reading someone's code I usually firstlyget rid of all indentations in the text. Plus, I prefer not to letbosses to prostitute me. If they don't like my style I just saythem "Good bye!!". Let them hire a boy with an outstandingability for code text arty decoration.As to praises in this thread.. well it occures not often hereand all those, who were praised, really deserve it.And last but not least, you must admit that the subjectquestion doesn't fit the forums format. |
|
|
|
DBRanger
Starting Member
6 Posts |
Posted - 2003-12-07 : 06:22:23
|
quote:
As to praises in this thread.. well it occures not often here
Oh, please! I regularly visit several different SQL Server forums. The only thing more common than "experts" glorifying each other is "experts" demonstrating their extremely arrogant attitudes. Your post manages to do both. |
|
|
|
Previous Page&nsp;
Next Page
|
standards to follow