How to replace 0 and 1 in SQL Server 2012

Question

I have a rugby database + player table. In the player table I have performance and I want to represent the performance as

0 = low
1 = medium
2 = high

I don't know what datatype the column should be. And what is the formula or function to do that?

Please help

Why do you want to store numbers instead of plain text? It can't be to save space; how many rugby players can you possibly be storing data for? — Bohemian, Jan 29 '17 at 10:01

Gurwinder Singh · Answer 1 · 2017-01-29T09:51:10.703

5

You can define your column like this:

performance tinyint not null check (performance in (0, 1, 2))

tinyint takes only 1 byte for a value and values can range from 0 to 255.

If you store the values as 1 - Low, 2 - Medium, 3 - High and are using SQL server 2012+, then you can simply use CHOOSE function to convert the value to text when select like this:

select choose(performance,'Low','Medium','High')
. . .

If you really want to store as 0,1,2, use :

select choose(performance+1,'Low','Medium','High')
. . .

If you are using a lower version of SQL server, you can use CASE like this:

case performance 
    when 0 then 'Low'
    when 1 then 'Medium'
    when 2 then 'High'
end

edited Jan 29 '17 at 09:51

answered Jan 29 '17 at 06:51

Gurwinder Singh

38,557
6
51
76

I would add that a tinyint can hold values between 0 and 255. – Zohar Peled Jan 29 '17 at 07:19
@Zohar - I thought it was clear from 1 byte. Updated though. Many thanks. – Gurwinder Singh Jan 29 '17 at 07:21
This approach won't scale well should the number of peformances become large, because then such state would need to maintained in queries themselves, possibly many of them. – Tim Biegeleisen Jan 29 '17 at 07:21
@Tim to me, it looks like a enumerated type. – Gurwinder Singh Jan 29 '17 at 07:23
2

@TimBiegeleisen While your comment is correct, I find it highly unlikely that performance levels would ever reach the 226 values. – Zohar Peled Jan 29 '17 at 07:25
1

you need to do: choose(performance+1,'Low','Medium','High'), then choose function is 1-base https://msdn.microsoft.com/en-us/library/hh213019.aspx – inon Jan 29 '17 at 08:57
@inon - my example assumes values as 1,2,3 if you read the answer completely. Its pretty unnecessary to add 1 every time (though no one can stop you from doing it) – Gurwinder Singh Jan 29 '17 at 09:04

Tim Biegeleisen · Answer 2 · 2017-01-29T07:00:34.500

There are a number of ways you can handle this. One way would be to represent the performance using an int column, which would take on values 0, 1, 2, .... To get the labels for those peformances, you could create a separate table which would map those numbers to descriptive strings, e.g.

id | text
0  | low
1  | medium
2  | high

You would then join to this table whenever you needed the full text description. Note that this is probably the only option which will scale as the number of performance types starts to get large.

If you don't want a separate table, you could also use a CASE expression to generate labels when querying, e.g.

CASE WHEN id = 0 THEN 'low'
     WHEN id = 1 THEN 'medium'
     WHEN id = 1 THEN 'high'
END

score 1 · Answer 3 · answered Jan 29 '17 at 06:55

1

1- column datatype should b int. 2- where you send the date check the performance first like:- if(performance = low) perVar = 0 send into database

answered Jan 29 '17 at 06:55

Saghir Ahmed

7
3

score 1 · Answer 4 · answered Jan 29 '17 at 12:12

I would use a TINYINT datatype in the performance table to conserve space, then use a FOREIGN KEY CONSTRAINT from a second table which holds the descriptions. The constraint would force the entry of 0, 1, 2 in the performance table while providing a normalized solution that could grow to include additional perforamnce metrics.

How to replace 0 and 1 in SQL Server 2012

4 Answers4