MS SQL Server Book of Wisdom

Below is a summary of what ensued for your amusement. Now, some of the statements are actually based on bad real life advice and many we just made up. Can you tell which is which?

Also please comment if you have got any good entries for the Book…..

• Why would i want my server to be enabled for awe!? I`m already in awe of my coding skills and i don't need a server to tell me i`m good.
• Set autoclose on, this means the disks get a rest when no-one is using your database.
• Unicode columns only hold unique values.
• Security adds an extra level of overhead to your applications, place all logins in the sysadmin role for maximum performance..
• Many people understand the more traditional SQL outer join syntax such as *= and =*. Use this as it will make your code easier for matured people to maintain.
• SQL server imprisons cached procedures incorrectly after 200 calls. Run dbcc freeproccache every 5 mins
• Ensure you create fragrant code, it makes parameter sniffing a more pleasant experience
• Always use optimiser hints, rememeber YOU know best.
• Full backup includes lots of old data that hasn't changed, differential backups will restore much quicker!
• Be green! Databases need space not spindles. Buy bigger disks and save on power consumption!
• People understand nested IF THEN statements much better than CASE - don't use it...
• dbcc dropcleanbuffers makes sure your data is clean and contains no corruptions. Use at least once a day...
• Use simple recovery, it's so simple to manage.
• Never ever comment in a piece of code, if you want idle chat go down the pub!
• Do not use SSAS. Cubes are bad as they are unable to roll down hills.
• Its best to run SQL Server from the command line, this way you can see what its doing when you log onto the server.
• Be green! Ensure you set your disks to be powered down when idle.
• Select * means a query will use all the CPUs... use this wherever possible.
• Short varable names are more efficient - never use @longVariableName where @a would do..
• Never backup your log as it removes the entries and you never know when you might need them.
• Fragmentation is the spawn of satan. Rebuild your indexes every half an hour for optimal performance
• Tempdb runs faster if you set it up on the root of your C Drive...
• Cursors rock, use them always. The SQL optimiser often makes bad choices when you use sets of data instead of row at a time
• Normalization can mean you have lots of little tables. Keep things simple by having one big table holding everything.
• Always run SQL trace by using the profiler GUI. Server-side traces just make things complex.
• The first step to recovering a suspect database is to detach it.
• If the database is not yellow in enterprise manager they can't be using it so save resources and delete it.
• Separate development servers are a waste of resource. Ensure all development and testing is done on production
• Sometimes truncate doesn't work properly, use delete instead. Delete is slower because it definitely deletes all rows.
• If you see blocking on a server, kill all spids involved immediately.
• “with tablockx” makes your query run faster. Always use it…
• Always set 'allow updates' to 1. This means you can update in the database, normally you can only insert or delete.
• Better to have many databases storing one table each, than one database holding many tables.
• NTFS compression is your databases best friend! Save space and compress all MDF & Log files.
• All commands are safe, undocumented simply means they forgot to add it!
• The DBA knows best, always ensure you configure recovery intervals and affinities.
• Don't waste valuable disk space when you don't need it. Size all your databases at 10MB and set autogrow to 1MB.
• SQL is more efficient if it's written in upper case.. never use lowercase.
• Nonclustered indexes slow down SQL Server, don't use them unless you are searching for strings in very large character columns.
• Views are better than tables - they are optimized to use less disk storage.
• Unicode columns only hold unique values.
• Statistics are evil! Turn off auto create & update
• Temporary tables don't need any space - use them all the time.
• Autoshrink is your friend.
• Your server will run faster with fewer users, try to restrict access as much as possible
• SQL runs faster when accessing data from disk. Ensure you never allocate more than 200mb
• Backups can be useful - make some when you have some spare time...
• SQL Server does not need stopping and restarting every day - but if you can do this, then do.