4

i have a table like this

tab1 

create table tab1(ID int identity(1,1), Type varchar(10),IsValued bit)

tab1:

ID Type IsValued
----------------
1   S   1
2   R   0
3   R   0
4   S   1
5   S   1
6   R   0
7   S   1

instead of inserting value into IsValued column i want to create one constraint(NOT TRIGGER) when Type ='S' ,IsValued should be inserted as 1 and when Type ='R' ,IsValued should be inserted as 0

like : IsValued = case when Type ='S' then 1 when Type ='R' then 0 end

How can i achieve this..

Justin
  • 9,634
  • 6
  • 35
  • 47
Ram Das
  • 348
  • 4
  • 15

1 Answers1

4

You want a computed column. For example:

CREATE TABLE tab1
(
 ID INT IDENTITY(1,1)
,[Type] VARCHAR(10)
,IsValued AS CASE [Type] WHEN 'S' THEN 1
                         WHEN 'R' THEN 0
             END
)

You can add to an existing table using the following syntax:

ALTER TABLE dbo.tab1 ADD IsValued AS CASE [Type] WHEN 'S' THEN 1
                                                 WHEN 'R' THEN 0
                                     END

You can make the column persisted by adding the keyword PERSISTED after the column creation. Persisting the column means that the field is stored on disk. When you insert or update a record, SQL server will work out the value at that point. If you don't, SQL Server will have to work it out each time you access the row. A good explanation can be found at SQL Server 2005 Computed Column Is Persisted

ALTER TABLE dbo.tab1 ADD IsValued AS CASE [Type] WHEN 'S' THEN 1
                                                 WHEN 'R' THEN 0
                                     END PERSISTED
Community
  • 1
  • 1
twoleggedhorse
  • 4,938
  • 4
  • 23
  • 38
  • that IsValued column added newly into that table using like: alter table tab1 add IsValued bit – Ram Das Mar 20 '13 at 11:03
  • @sharath I've updated my example specifically to your question – twoleggedhorse Mar 20 '13 at 11:08
  • @sharath Just a small note, you may wish to add the word PERSISTED after END . Explanation here http://stackoverflow.com/questions/916068/sql-server-2005-computed-column-is-persisted – twoleggedhorse Mar 21 '13 at 15:57
  • ok.. but what if that column is already present in that table. @twoleggedhorse getting error with this. ALTER TABLE dbo.tab1 ALTER COLUMN IsValued AS CASE [Type] WHEN 'S' THEN 1 WHEN 'R' THEN 0 END PERSISTED – Ram Das Mar 25 '13 at 09:05
  • @RamDas You can only alter columns with certain conditions: http://sqlserverplanet.com/ddl/alter-table-alter-column You will probably have to drop the column and then add it with the keyword. ALTER TABLE dbo.tab1 DROP COLUMN IsValued – twoleggedhorse Mar 25 '13 at 11:06