Table structure

[fragglerock]

I have been asked to comment on a table structure that a contractor wants to use the structure is as follows

Columns must be listed in the following order:
Primary Keys
NUMBER, not null (shortest to longest)
DATE, not null
VARCHAR, not null (shortest to longest)
NUMBER, nullable (shortest to longest)
DATE, nullable
VARCHAR, nullable (shortest to longest)

This ordering will minimise row chaining, where rows are split across multiple disk blocks. When this occurs, queries take more time to execute because multiple disk blocks need to be read instead of just one.

This sounds like rubbish to me, or if it is true the performance difference for general tables is going to be minuscule, and the order would best reflect internal table structure (ie all bits of an address next to each other, "house keeping" (dateCreated etc) at the end). I always understood that order does not matter, and making things easy for the human reader is sensible.

Comments?

 

[PWise]

wheter this is rubbish or not dont know but you can create a view with the fields listed any way you want

 

[Crowley16]

erm, you are using SQL Server right?

I believe row chaining is something found on Oracle. I've certainly not heard about anything like that in sql server.

even if it does exist, the performance differences would not be noticeable. The contractor is most likely bringing this up to make himself look better or get more work...

--------------------
Procrastinate Now!

 

[SQLSister]

I've never seen anything like that in SQL Server. And you have to think about maintainability. Once you have created the table, the only way you can maintain that structure when adding fields is to recreate the table with a new name, move the data, drop the old table and rename the first. Clearly this is unacceptable in a production table that has a lot of records as it will lock the tables out completely inthe process and kill performance. Since the structure is not maintainable over time, I would not use it. (Incidentally you can do this in SQL Server 200 in Enterprise Manager but those the actual steps it takes and doing so just to maintain some artifical order will kill performance on your server which is why you should never ever make table structure changes using Enterprise Manager.)

In SQL Server what will make a big difference is having too many fields in a table.

If your total field size (excluding text fields) adds up to more that 8064 bytes than you should split into separate tables even if you don't have a performance problem because that is the maximum row size that SQL Server can store. You can go along for years until you havea record that is too big and then blam your whole application is broken because you can't save a correct record.

"NOTHING is more important in a database than integrity." ESquared

 

[fragglerock]

Thanks very much.

The whole document made me go but I thought perhaps I had just missed a whole chunk of my education

It is no doubt a highly classified document, and me posting it on the Internet would be frowned upon

How does this grab you? I am not sure I understand it
"• Create alias names for all table names in the FROM clause and use the alias throughout the SQL statement. Use the first three letters of the table. In case of repetitive aliases, add underscores and the first letter after each underscore (e.g. employee_general_categ becomes emp_g_c). If the aliases are still repetitive, add the second letter after each underscore (e.g. employee_general_categ becomes emp_ge_ca). "

does it mean

SELECT name
FROM names as nam

?

Note use of "_categ" where before it says don't use abbreviations

How do people think about_underscores? I PreferCamelOrPascal is there a LAW about this?

Thanks for your thoughts!

 

[SQLSister]

That is just an attempt to use a consistent naming structure and seems fine (althought I prefer shorter aliases myself, I rarely have one over 4 characters long). Personally I prefer CamelCase although underscores are OK. The most important thing is to use one or the other consistently and to use the same field name when referenced in other tables as much as possible. That way you avoid having to remember if it is person_id, PersonID or People_id in a table when you are writing queries. Believe me when it comes to maintaining a database, consistency in naming conventions can save a lot of time. Also I see no need for an alias unless you have a join in the statement. But what the naming convention is really is less important than having one and sticking to it. Whatever it is your developers will become used to it and use it automatically after a little experience querying your database.



"NOTHING is more important in a database than integrity." ESquared