SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2005 Best Practices


SQL Server 2005 Best Practices

Author
Message
NandaKishore
NandaKishore
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/nKishore/sqlserver2005bestpractices.asp
Chris Hedgate
Chris Hedgate
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3131 Visits: 7
What do you mean by "Avoid code that assumes all metadata is publicly visible"? As of SQL Server 2005 Microsoft is now recommending usage of the (documented) catalog views and dynamic management views for accessing metadata.

And regarding the list of documented columns that return NULL or 0, well all of those columns are part of catalog views that exist only for backwards compatibility with the old system tables. For instance, instead of using sysindexes (which is actually called sys.sysindexes to be exact) you should use sys.indexes.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54648 Visits: 32776

Well written article, but a couple of points.

It doesn't really get into Best Practices much at all. With the exception of some SQL 2000 best practices that will prevent an upgrade, there's almost nothing here in terms of the list of "do this", "don't do that" and "this would be a good guideline to follow."

Also, you talk about database mirroring, yet, according to the documentation provided, this is not currently supported by MS so this can hardly be considered a "Best Practice."



----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
TDuffy
TDuffy
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1578 Visits: 57

How can you state "Usage of DBCC commands is not supported in SQL Server 2005"? This is just not true.

Some DBCC commands are deprecated (e.g. DBCC PINTABLE), but such a blanket statement is false.

Terry





PBirch
PBirch
SSC Eights!
SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)

Group: General Forum Members
Points: 941 Visits: 35
I agree. I have been using DBCC commands in 2005 for months with few issues. That is an odd phrase.

This article, will ok, was not a best practices. It was more of a "new stuff" article.

The author did not mention that the semicolon is a statement terminator used in t-sql. Using 7 and 2000 you could write sql statements for pages and never use a semicolon. In 2005, using the semicolon is still not mandatory; it is ANSI-compliant.

You will notice that the code from Microsoft uses it and there are a few gotchas. For example, if you use the CTE(which was not mentioned in the article) in a batch of statements, the statement before the CTE definition must have a semicolon.

Quand on parle du loup, on en voit la queue
Ian Yates
Ian Yates
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2480 Visits: 445

And where is the whitepaper?
"Please refer to this white paper for a better understanding of partitions in SQL server 2005."

Sounds like some cut + paste?





DCPeterson
DCPeterson
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2061 Visits: 432
I think what Nanda meant was that the meta data is now secured along with access to the objects themselves, so you can't assume that any given user has access to any particular piece of meta data. At least that's how I read it.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek



*****************/
Chris Hedgate
Chris Hedgate
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3131 Visits: 7
Yes, that might be what was meant. Not very clear though..

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/
stevehindmarsh
stevehindmarsh
SSC Eights!
SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)

Group: General Forum Members
Points: 922 Visits: 584

Hmmm - not really a best practices list - more a new features description.

Would a best practice be 'turn off CLR enabled' sp_configure option until ordered to turn on by senior management '





Chris Hedgate
Chris Hedgate
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3131 Visits: 7
Well, first of all you do not need to, CLR is disabled by default. But enabling it does not really do a lot unless anyone starts to use it. Being told by senior management to enable it would make me very disheartened, since that is not something they should concern themselves with. But I do think that there are many compelling cases for enabling and using it, even though I am in general quite reluctant of it.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search