Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
just.net
Starting Member
24 Posts |
 Posted - 2014-12-02 : 08:22:03
|
| Would you use char (8) or nvarchar (8) to keep code/string like 05486440 ?How to maintain currency, decimal or money ?Should I make small tables just for holding categories, and then use foreign-key to the ID of these categories?tables name - singular or plural?Is the order of the tables in the join really matter? |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-12-02 : 08:45:59
|
| 1. I'd use int or bigint, not a character type2. http://msdn.microsoft.com/en-CA/library/ms179882.aspx3. it depends on what you will put into those small tables4. It's a matter of taste, but sometimes plural reads better5. The optimizer can usually work things out, assuming your statistics are up to date. |
 |
|
|
just.net
Starting Member
24 Posts |
Posted - 2014-12-02 : 09:09:01
|
| 1. I'd use int or bigint, not a character type int is great but the value such as 0 at the beginning can be lost.2. http://msdn.microsoft.com/en-CA/library/ms179882.aspxThanks.3. it depends on what you will put into those small tablesExample:Table A = Orders (id, date... statusID)Table B = OrderStatuses (statusID, statusName)Table A => foreign-key to Table B4. It's a matter of taste, but sometimes plural reads betterI think so too.5. The optimizer can usually work things out, assuming your statistics are up to date.Thanks. |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-12-02 : 09:42:36
|
| 1. well, leading zero's are not "lost"; they're irrelevant. If you want to display them later, add them back in then. Meanwhile, you save column space.3. I'd tend to do it that way, to allow for updating statusName separately and to keep the size of TableA reasonable. Also to consider: Is there a status code that corresponds to the status name? e.g. some folks might use:A -> ActiveC -> CompleteO -> Openetc.in that case, you may want to carry a status code in TableA instead of statusId, add a statusCode column to tableB and make that the PK. |
|
|
|
just.net
Starting Member
24 Posts |
Posted - 2014-12-03 : 01:30:29
|
| Your answers are very good for me, thank you.except answer 1, because what if the values are:000047860023455600000035this way the zeroes are relevant and significant.so which one is better?char (8) , nvarchar (8) , nchar(8) |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-12-03 : 08:54:03
|
The leading zeros for values stored in your table are not significant. If they need to be displayed, you can add a computed columncreate table my table (code int, displaycode as right('00000000' + cast(code as varchar(8)), 8) ) Then always select displaycode but insert code, e.g.insert into mytable (code) values (123456)select displaycode from mytable |
|
|
|
just.net
Starting Member
24 Posts |
Posted - 2014-12-04 : 05:13:07
|
| Thank you! |
|
|
|
|
|
|
|
|
Best Practices