CODEWITHSUNDEEP
sqlsql-server
user3991213
user3991213

Reputation:

Create a column with an if condition in SQL server

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

Upvotes: 1

Views: 2803

Answers (2)

radar
radar

Reputation: 13425

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 )

Upvotes: 1

voam
voam

Reputation: 1016

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.

Upvotes: 1

Related Questions