10 questions to ask yourself before optimizing your database

Assuming that your database doesn't need the ability to store special characters, and in particular the character sets defined by the Unicode standard, do you systematically replace nchar with char and so on, across the various column types to which this applies?

Never had to use nchar, certainly wouldn't use it by default, unless I was always operating with special string data. It takes up twice as much space and takes twice as much processing and I/O.

Do you use smalldatetime rather than datetime? If you're unclear about the difference between the two data types, here's a quick overview: The smalldatetime data type can store dates from January 1, 1900 through June 6, 2079, which is sufficient for most business applications. The datetime data type extends this range from January 1, 1753 to December 31, 9999. Do you really need this range?

More interested in seconds than going past 2079, smalldatetime only goes up to minutes.

Do you routinely use the bit data type rather than smallint? If so, do you know the cost? It may not affect your application, but the cost is that you can index a smallint but not a bit.
SQL Server 2000 permits NULLs in bit columns, and this is the default behavior. This may or may not be desirable.

I'd be very worried if an index on a bit field was going to have a significant impact on optimisation. Only two, three if you count null branches anyway. Cluttered and messy if you had to do it. As for allowing null, that can be very useful, if you don't need not yet set, then make it not null and potentiallyb provide a default constraint.

How do you treat NULLs in character columns? The default is to permit NULLs, but a smarter approach is to deny NULLs and permit zero-length strings.
Bit misleading this. In a Char or nchar column null will take up as much space and I/O as a none null value. In varchar/nvarchar this is not a consideration. Personally I don't use char/nchar unless it was say always a non null three letter code or some such. They are a right PIA and can catch you out on a regular basis.
'Billy' 'Billy' for instance. You can stare at that sort of thing for hours before you realise what's going on. Billy in a Char(6) column does not = a Varchar(6) of Billy as the Char version will be packed to it's length.

Note Char/nchar for an index column, sorting and comparison is faster than varchar/nvarchar.


Do you consider the maximum row size of a table? Do you consider this size versus the data page size (8060 bytes)?

Only if I have to. I have an intense dislike of wide tables anyway.

Do you make use of Standard Columns (go to Tools | Options | Standard Columns)?

Never heard of it

Do your tables contain a TimeStamp column? If not, do you know the circumstances when this column might be useful?

a Time stamp changes on each update, so you can check the current stamp against the last one you read and see if the data has changed since you read it. If you don't need this functionality don't go near them.

Does each of your tables have a clustered index? Do you know when a table should have one and when it is unprofitable?
When, nearly always. Unprofitable, when there is no default order that records should be read in (or written) and or when the index key is subject to multiple changes giving a large overhead maintaining the index.

Do you name your stored procedures using the prefix sp_? Do you realize the performance hit that this incurs?
No I don't, as I want to differentiate mine from those you get from Bill. The performance hit is the procedure will always be looked up in master FIRST. If you are foolish enough to create an sp in a user database with the same name as the one in master, it must be fully qualified to be executed.

HTH.

I agree that the questions are great, but it looks like arthur started creating answers to the first couple of the questions and then in haste to make deadline or something forgot to answer the last question.

Any possibility of doing a followup and answering your own questions

where is the Standard Columns?

Thanks