Advise on column type

Hello,

In my web application I am listing steps of action:
STEP ACTION
10
10.1
10.2
10.9
10.10
10.20
30
30.1
etc

How should I set up my columns in my database? I tryed decimal but the problem is that 10.1 and 10.10 is in theory the same while in my case it is absolutely not. I also tryed nvarchar but does not sort correctly.
Should I create 2 int column, one for the main step and another one for the sub-step (one would contain the part before the "." and the other would contain the part after the ".")?

Thanks

you could use the 2 column approach, or you could still use varchar.

to get a numeric sort on string data, you need to carefully control the character positions

10 becomes a string 10.00
10.1 becomes 10.01 <-- notice the leading zero after the decimal

then when your sort the string data, you'd get a correct sort.

10.00
10.01
10.02
10.09
10.10
10.20
30.00
30.01

note: if your data might ever go to three places after the decimal, then you have to account for it now i.e.
10.000
10.001
10.010
10.100

So the question back to you is...will it be simpler for you to manage two columns and put the data back together as its sorted/presented, or will it be simpler for you to control the formatting of the string input?

PS: you could also still use the decimal format as long as you control the positions of each character value...

|||Thanks for the answer.
After carefully reviewing your suggestion, I think it would be easier for me to create 2 columns.
Now, would it be more efficient to put back the data together with my sql request ( do a cast of each int column into varchar and add the character "." between) or to format the string input in my code(C#) ?

Thanks|||

If you perform the string concatenation in Sql, then anyplace you pull the data, it will be formatted as you require.
If you perform the string concatenation in code (C#), then you'd need to repeat the "fixup" anytime you use the data.

In terms of raw cpu, i doubt that there is any statistically significant difference between the 2 choices - so i would choose the method you are most comfortable with.

|||

I finally decided to go with the varchar column.
However, I can not find any rule to automatically transform a 10.1 into a 10.01 or a 10.10 into a 10.1

Would you have a sample on how to do the transformation?

Thanks