Table Designing

Question

Table Designing

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 1

I agree with everything Sean said. I'll add to it by questioning the nonclustered indexes that you want to add. Why are you adding those. Do you know you need them or are you just adding an index on foreign key constraints? I wouldn't suggest adding nonclustered indexes without understanding how the queries are being built and whether or not you'll need an index. The constraints themselves won't act as indexes, but if SQL Server knows it can only ever get one row in a join due to an enforced constraint, it's much more likely to do intelligent things in the plan. You may still need indexes on those columns, but, again, I wouldn't put them in there until you prove that you need them and then you may need to make them a covering indexes, depending on the queries needed. So, no, I don't agree with recommendations 2 & 3 without a lot more information.

Also, I don't see a natural constraint? Based on all the other columns in the table, is it OK if they're all exactly the same, numerous times? If so, your keys and constraints are fine (although your data is going to be messy). If not, you should either consider going with a natural key, or, you need to add a constraint to enforce the natural key.

Finally, as for picking a clustered index, what is the most common access path to the data in this table? Frequently, that's the primary key. But not always. I'd define that immediately. You only get one clustered index, so it's the most important part of setting up a table.

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 2

Thanks Sean,

The table name is put just for name sake and will be changed.

CreatedBy

ModifiedBy are referring to other tables where they are PKEY and data type GUID.

Do i need to make any other changes??

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Ratheesh.K.Nair (7/8/2014)
Thanks Sean,
The table name is put just for name sake and will be changed.

No problem there.

CreatedBy
ModifiedBy are referring to other tables where they are PKEY and data type GUID.

This is exactly what I was saying. These seem to be a FK to other tables with similar guids as a clustered key issue. Also, please consider my comments about why this might be an issue.

If you allow a user to be deleted (and you have established a FK relationship to this table) SQL will not allow the delete of the user row. This could be a big problem. Then consider what happens when you don't establish a foreign key. You are left with a totally useless guid in a column that would tell you who created/modified the row, except that you can't tell anymore because the user was deleted. For me these types of auditing columns are an acceptable place to denormalize your data. I would recommend putting in the user name instead of a foreign key.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 4

While I'm not a fan of GUIDs as clustered keys, I've seen it in a production system and it wasn't the end of the world. We did have to deal with some excessive fragmentation issues and the obvious problems of fewer rows stored per page were there. Performance was reduced. It's absolutely not a choice I would make, but if you're already neck deep into that style of design, I wouldn't recommend a complete rewrite and redesign, but you should sure as heck go into it with your eyes really wide open so you're not surprised by some of the issues that are bound to come up.

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 5

Thanks Grant for the reply.

Yes there exists foreign key between table Muthal and ConsumableType.

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 6

Grant Fritchey (7/8/2014)
While I'm not a fan of GUIDs as clustered keys, I've seen it in a production system and it wasn't the end of the world. We did have to deal with some excessive fragmentation issues and the obvious problems of fewer rows stored per page were there. Performance was reduced. It's absolutely not a choice I would make, but if you're already neck deep into that style of design, I wouldn't recommend a complete rewrite and redesign, but you should sure as heck go into it with your eyes really wide open so you're not surprised by some of the issues that are bound to come up.

Those tables were already in place and they dont have any plans to change at this moment.

I got the request for this table designing and with help you all experts I gave my recommendation. Avoided GUID because its not sequential and takes 16bytes space.