1

i am not sure if i could use conditional statement while creating new columns.

Code:

create table Employees(
Emp_ID int primary key identity (1,1),
Hours_worked int,
Rate int default '')

/*Now here in default value i want to set different rates depending upon hours worked. like if hour worked is greater than 8 then rate is 300, if hours worked is less than 8 hour the rate is 200.) How to write this as a Default value in sql server 2008.

My second question is:

Why i get error if i write like this,

create table tbl_1(
col_1 varchar(max) unique
)

The error is 

Column 'col_1' in table 'tbl_1' is of a type that is invalid for use as a key column in an index.
Msg 1750, Level 16, State 0, Line 1

Regards Waqar

Improve this question
14
  • Your first question. You don't. A default is a default value for a column. It is not conditional. Your second question, why would you think you should use unique on a varchar(max)? That doesn't make any sense. You could use unique if the varchar has a limit but anything more than a few characters is ridiculous anyway. Commented Oct 2, 2014 at 19:14
  • ok i get it. but is there any way i could add condition to the rates.yes it is ridiculous but what's the reason behind this error? Commented Oct 2, 2014 at 19:16
  • 3
    The reason is that an index size is 900 bytes and a varchar(max) can take a lot more than that. Commented Oct 2, 2014 at 19:17
  • 1
    Is there any way i could add condition to the rates, You could make the column a computed column and not allow a user variance... stackoverflow.com/questions/13014108/… Commented Oct 2, 2014 at 19:20
  • 1
    @DourHighArch I am betting its done so that its one column that doesn't need to be added to any insert/update and computed automatically. Commented Oct 2, 2014 at 19:21

2 Answers 2

Reset to default
1

you can use COMPUTED Column, http://msdn.microsoft.com/en-us/library/ms191250.aspx

create table Employees(
Emp_ID int primary key identity (1,1),
Hours_worked int,
Rate   as (case when Hours_worked > 8 then 300  else 200 end) persisted )
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

The default value cannot refer to any other column names. So the "default" value of Rate won't know the value of Hours_worked. You could handle it with a trigger or whatever is doing the actual inserting could contain this logic.

http://msdn.microsoft.com/en-us/library/ms173565(v=sql.100).aspx

You cannot but a UNIQUE constraint on a VARCHAR(MAX) field.

Improve this answer

1 Comment

@rajesh answered this perfectly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.