SQLTeam.com | Weblogs | Forums

Sql Server re-writes computed column definition, I want my script back

SQL Server 2008 R2

I will post data and schema in the post below.

I have added a computed column as

alter table events2 add company_code as
case
when substring(id,5,2) in ('48','36','30','34','17','48','45','21','22') then substring(id,5,2)
when substring(id,5,2) = '18' then '17'
when substring(id,5,2) = '65' then '17'
when substring(id,5,2) = '80' then '08'
when substring(id,3,2)='16' then '16'
when substring(id,3,2)='80' then '08'
when substring(id,5,3) in ('012','013','014') then '01'
end
Sql re-writes as
(case when substring([id],(5),(2))='22' OR substring([id],(5),(2))='21' OR substring([id],(5),(2))='45' OR substring([id],(5),(2))='48' OR substring([id],(5),(2))='17' OR substring([id],(5),(2))='34' OR substring([id],(5),(2))='30' OR substring([id],(5),(2))='36' OR substring([id],(5),(2))='48' then substring([id],(5),(2)) when substring([id],(5),(2))='18' then '17' when substring([id],(5),(2))='65' then '17' when substring([id],(5),(2))='80' then '08' when substring([id],(3),(2))='16' then '16' when substring([id],(3),(2))='80' then '08' when substring([id],(5),(3))='014' OR substring([id],(5),(3))='013' OR substring([id],(5),(3))='012' then '01' end)

What I want
Give me back my original script.
I don't want sql server to stop re-writing his way, I want my script which I understands better. I can modify my script and then can alter the definition as below

alter table events2 drop column company_code
alter table events2 add ............

off-course sql server may re-writes afterward.

...
CREATE TABLE [dbo].[Events2](
[DateTime] [datetime] NOT NULL,
[ID] varchar NOT NULL,
[Machine] [int] NOT NULL,
[Status] varchar NOT NULL,
CONSTRAINT [PK_Events2] PRIMARY KEY CLUSTERED
(
[DateTime] ASC,
[ID] ASC,
[Machine] ASC,
[Status] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
go
...

...
alter table events2 add company_code as
case
when substring(id,5,2) in ('48','36','30','34','17','48','45','21','22') then substring(id,5,2)
when substring(id,5,2) = '18' then '17'
when substring(id,5,2) = '65' then '17'
when substring(id,5,2) = '80' then '08'
when substring(id,3,2)='16' then '16'
when substring(id,3,2)='80' then '08'
when substring(id,5,3) in ('012','013','014') then '01'
end
go
...

...
select definition
from sys.computed_columns c
where object_name(object_id)='events2'
go
...

...
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 1 2020 3:00:00:000PM','0000451312',101,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 1 2020 7:00:00:000PM','0000451312',101,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 2 2020 10:00:00:000PM','0000452956',101,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 3 2020 6:47:47:000AM','0000012985',3,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 3 2020 6:10:20:000PM','0000480018',48,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 4 2020 7:00:00:000AM','0000451312',101,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 4 2020 8:27:10:000AM','0000480035',48,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 4 2020 9:04:21:000AM','0000300010',30,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 4 2020 10:08:25:000AM','0000361096',1,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 4 2020 5:58:06:000PM','0000344273',14,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 4 2020 6:04:32:000PM','0000360248',3,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 5 2020 8:29:35:000AM','0000480027',48,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 5 2020 8:59:29:000AM','0000300014',30,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 5 2020 9:08:23:000AM','0000344211',14,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 5 2020 7:00:00:000PM','0000451316',101,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 5 2020 10:00:00:000PM','0000451313',101,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 5 2020 10:00:09:000PM','0000360114',3,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 6 2020 7:57:18:000AM','0016009044',1,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 6 2020 8:52:22:000AM','0000300012',30,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 6 2020 9:00:55:000AM','0000344478',15,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 6 2020 3:47:30:000PM','0000210421',59,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 6 2020 4:18:25:000PM','0000480035',48,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 6 2020 6:14:14:000PM','0000013307',3,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 7 2020 7:00:00:000AM','0000451312',101,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 7 2020 9:07:56:000AM','0000300004',30,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 7 2020 9:08:15:000AM','0000300013',30,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 7 2020 9:08:33:000AM','0000344581',14,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 7 2020 5:57:25:000PM','0000177319',16,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 7 2020 5:58:24:000PM','0000342380',14,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 8 2020 7:00:00:000AM','0000451312',101,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 8 2020 7:00:00:000AM','0000451313',101,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 8 2020 8:08:15:000AM','0000220003',59,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 8 2020 8:38:05:000AM','0000210380',59,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 8 2020 8:52:15:000AM','0016009056',1,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 8 2020 8:57:00:000AM','0000480036',48,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 8 2020 9:11:52:000AM','0000803929',50,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 8 2020 10:11:15:000PM','0000014148',3,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 10 2020 8:00:00:000AM','0000014221',3,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 10 2020 8:11:03:000AM','0000360308',3,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 10 2020 8:48:09:000AM','0000300009',30,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 10 2020 8:57:58:000AM','0016009051',1,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 10 2020 4:55:00:000PM','0000480043',48,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 10 2020 5:00:43:000PM','0000210415',59,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 10 2020 6:03:42:000PM','0000175477',16,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 10 2020 6:11:49:000PM','0000300010',1,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 11 2020 9:01:48:000AM','0000342472',14,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 11 2020 9:01:59:000AM','0000300008',30,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 11 2020 6:07:00:000PM','0000360808',1,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 11 2020 10:00:00:000PM','0000451313',101,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 12 2020 8:51:20:000AM','0000803929',50,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 12 2020 9:15:56:000AM','0000361023',1,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 12 2020 9:41:28:000AM','0000220002',59,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 12 2020 5:30:42:000PM','0000178297',16,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 12 2020 6:01:14:000PM','0000182064',16,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 12 2020 6:05:06:000PM','0000341905',14,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 12 2020 7:02:45:000PM','0000361101',6,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 13 2020 7:00:00:000AM','0000451312',101,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 13 2020 9:02:11:000AM','0000344232',14,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 13 2020 9:15:45:000AM','0000803648',50,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 13 2020 9:18:29:000AM','0000803573',50,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 13 2020 3:00:00:000PM','0000452956',101,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 13 2020 6:08:09:000PM','0000803648',50,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 13 2020 6:17:51:000PM','0000803573',50,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 13 2020 6:29:08:000PM','0000300011',1,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 13 2020 6:32:07:000PM','0000480026',48,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 13 2020 6:34:54:000PM','0016009044',1,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 13 2020 10:00:00:000PM','0000451313',101,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 15 2020 8:31:38:000AM','0000300011',1,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 15 2020 8:56:48:000AM','0000480035',48,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 15 2020 9:12:23:000AM','0000181226',16,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 15 2020 3:00:00:000PM','0000452956',101,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 15 2020 3:21:38:000PM','0000803973',50,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 16 2020 7:00:00:000AM','0000451315',101,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 16 2020 2:32:15:000PM','0000344458',14,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 17 2020 8:45:58:000AM','0000344503',14,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 17 2020 8:51:45:000AM','0000343280',14,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 17 2020 9:03:00:000AM','0000480026',48,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 17 2020 9:56:09:000PM','0000013887',3,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 17 2020 10:04:18:000PM','0000013984',16,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 18 2020 8:58:45:000AM','0000300009',1,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 18 2020 2:45:01:000PM','0000014203',3,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 18 2020 6:03:14:000PM','0000360527',3,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 18 2020 10:04:40:000PM','0000014004',3,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 19 2020 8:42:19:000AM','0000480053',48,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 19 2020 6:12:12:000PM','0016009045',1,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 19 2020 7:22:17:000PM','0000480003',48,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 19 2020 8:01:28:000PM','0016009089',1,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 19 2020 8:10:56:000PM','0000341979',16,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 20 2020 12:01:00:000AM','0000360481',6,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 20 2020 8:43:13:000AM','0000013920',3,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 20 2020 9:03:54:000AM','0016009087',1,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 20 2020 6:09:15:000PM','0000300008',30,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 20 2020 8:00:36:000PM','0000360952',3,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 20 2020 10:00:00:000PM','0000451314',101,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 21 2020 6:04:55:000PM','0000480006',48,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 21 2020 8:03:47:000PM','0000360110',3,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 21 2020 10:00:00:000PM','0000013909',3,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 22 2020 1:59:57:000PM','0000360114',3,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 22 2020 2:49:43:000PM','0000480011',48,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 22 2020 6:00:44:000PM','0016009078',1,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 22 2020 10:00:00:000PM','0000451313',101,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 24 2020 8:19:34:000AM','0000177111',16,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 24 2020 8:28:44:000AM','0016009079',1,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 24 2020 8:30:47:000AM','0000182252',16,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 24 2020 11:15:53:000AM','0000801921',50,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 24 2020 3:00:00:000PM','0000452956',101,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 24 2020 4:54:20:000PM','0000480004',48,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 25 2020 7:00:00:000AM','0000451316',101,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 25 2020 2:50:21:000PM','0000300009',1,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 25 2020 4:05:00:000PM','0000360445',3,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 25 2020 10:00:00:000PM','0000451314',101,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 26 2020 8:32:29:000AM','0000176799',16,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 26 2020 8:44:42:000AM','0000803929',50,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 26 2020 8:58:58:000AM','0000210414',59,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 26 2020 9:00:35:000AM','0000300009',1,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 26 2020 6:01:21:000PM','0000480047',48,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 26 2020 6:06:02:000PM','0000181741',16,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 27 2020 5:05:51:000AM','0000182383',16,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 27 2020 8:24:34:000AM','0000220003',59,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 27 2020 8:39:52:000AM','0000480013',48,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 27 2020 1:00:00:000PM','0000180349',16,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 27 2020 3:23:40:000PM','0000014075',3,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 28 2020 9:20:00:000AM','0000803335',50,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 28 2020 6:00:12:000PM','0000300011',30,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 31 2020 8:20:22:000AM','0000220003',59,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 31 2020 9:13:09:000AM','0000180732',16,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 31 2020 9:15:32:000AM','0000360248',3,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 31 2020 5:42:18:000PM','0000220036',59,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 31 2020 6:28:36:000PM','0000480026',48,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 1 2020 9:03:40:000AM','0000220007',59,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 1 2020 9:11:24:000AM','0000803979',1,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 1 2020 9:11:31:000AM','0000300004',1,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 1 2020 10:47:09:000AM','0000361117',3,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 1 2020 6:01:34:000PM','0000342525',14,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 1 2020 8:10:27:000PM','0000013846',3,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 2 2020 8:12:14:000AM','0000360545',3,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 2 2020 9:01:56:000AM','0016009069',1,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 2 2020 9:27:27:000AM','0000210415',59,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 2 2020 4:05:11:000PM','0000013909',3,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 2 2020 6:09:50:000PM','0000220003',59,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 2 2020 6:12:28:000PM','0000803979',1,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 3 2020 9:07:45:000AM','0000480015',48,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 3 2020 9:08:14:000AM','0000013922',3,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 3 2020 5:34:35:000PM','0000210414',59,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 3 2020 6:01:08:000PM','0016009064',1,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 3 2020 6:25:49:000PM','0000480006',48,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 3 2020 8:00:41:000PM','0016009059',1,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 3 2020 11:58:40:000PM','0000182334',16,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 4 2020 8:41:08:000AM','0016009168',16,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 4 2020 8:58:57:000AM','0000801160',50,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 4 2020 3:22:18:000PM','0000803982',50,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 4 2020 5:30:10:000PM','0000210414',59,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 4 2020 5:52:44:000PM','0000179197',16,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 5 2020 8:28:57:000AM','0000361102',1,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 5 2020 8:32:41:000AM','0000210417',59,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 5 2020 8:45:31:000AM','0000344353',14,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 5 2020 9:05:59:000AM','0000300004',1,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 5 2020 9:06:02:000AM','0000300008',1,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 5 2020 9:12:06:000AM','0000480003',48,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 5 2020 9:13:36:000AM','0000342614',14,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 5 2020 2:41:42:000PM','0000013906',3,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 5 2020 3:11:31:000PM','0016009049',1,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 5 2020 4:37:37:000PM','0016009044',1,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 5 2020 5:04:17:000PM','0000341969',14,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 7 2020 9:00:35:000AM','0000300009',1,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 7 2020 9:06:58:000AM','0000344238',14,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 7 2020 9:15:35:000AM','0000210421',59,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 7 2020 9:19:10:000AM','0000210415',59,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 7 2020 9:21:00:000AM','0016009229',1,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 7 2020 6:08:21:000PM','0000343046',16,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 7 2020 7:27:24:000PM','0016009044',1,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 8 2020 8:49:31:000AM','0016009038',1,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 8 2020 8:49:56:000AM','0016009072',1,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 8 2020 8:51:48:000AM','0000803979',1,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 8 2020 9:10:12:000AM','0000361106',1,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 8 2020 9:24:09:000AM','0000344229',1,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 9 2020 7:09:08:000AM','0000014303',3,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 9 2020 9:07:27:000AM','0000803573',50,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 9 2020 9:28:32:000AM','0000803929',50,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 9 2020 10:16:29:000AM','0000803979',1,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 9 2020 6:18:19:000PM','0000175262',16,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 9 2020 7:19:16:000PM','0000361113',3,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 10 2020 6:12:51:000PM','0000300004',1,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 11 2020 6:07:36:000PM','0000177168',16,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 12 2020 8:10:08:000AM','0000175831',16,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 12 2020 8:14:40:000AM','0000014098',3,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 12 2020 9:13:32:000AM','0000803982',50,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 12 2020 6:22:11:000PM','0016009078',1,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 14 2020 8:09:11:000AM','0000177057',16,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 14 2020 8:24:40:000AM','0000013980',3,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 14 2020 8:44:20:000AM','0000013845',3,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 14 2020 6:05:46:000PM','0000182453',16,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 14 2020 6:13:07:000PM','0000803573',50,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 14 2020 10:04:23:000PM','0000014260',3,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 15 2020 8:10:54:000AM','0000361127',3,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 15 2020 8:15:34:000AM','0000182479',16,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Sep 15 2020 8:46:25:000AM','0000344641',14,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 18 2103 6:28:29:000AM','0000220003',59,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 18 2103 6:31:27:000AM','0000210414',59,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 18 2103 6:32:29:000AM','0000210380',59,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 18 2103 6:33:00:000AM','0000220027',59,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 18 2103 6:34:01:000AM','0000220027',59,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 18 2103 6:35:02:000AM','0000210400',59,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 18 2103 6:36:26:000AM','0000220027',59,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 18 2103 6:38:03:000AM','0000220008',59,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 18 2103 6:38:55:000AM','0000220015',59,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 18 2103 6:39:01:000AM','0000220033',59,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 18 2103 6:39:15:000AM','0000220033',59,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 18 2103 6:39:42:000AM','0000210415',59,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 18 2103 6:41:27:000AM','0000210380',59,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 18 2103 6:42:08:000AM','0000210380',59,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 18 2103 6:47:25:000AM','0000210420',59,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 18 2103 6:56:15:000AM','0000210404',59,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 18 2103 6:59:18:000AM','0000220003',59,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 18 2103 7:01:10:000AM','0000220002',59,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 18 2103 7:07:39:000AM','0000210420',59,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 18 2103 7:09:03:000AM','0000220002',59,'02')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 18 2103 7:11:15:000AM','0000210415',59,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 18 2103 7:13:00:000AM','0000220011',59,'01')
INSERT INTO [events2] ([DateTime],[ID],[Machine],[Status])VALUES('Aug 18 2103 7:25:40:000AM','0000220027',59,'02')
...

Please format your code using 3 ticks?

` before and after code. Hard to sort out

@yosiasz
I have formatted with ... ticks.
It seems it is still unformatted.
please help.

If you are looking for SQL Server to maintain your code in the computed column - so that you can 'script' it and have the same code you entered saved, then that is not going to happen. SQL Server rewrites the computed column code and that is saved.

If you need to be able to make modifications to the code - then you should create this code in a function and use the function in the computed column.

Better yet - instead of using a computed column you should update the code so you have the correct company code value being inserted/updated.

1 Like

Will try
The actual table maintains attendance of 4000 thousands. They scan the attendance machine one after another from various location (company code) and data is inserted immediately.
Will try your suggested functional approach and see if it runs smoothly.

In the past I have tried trigger approach to update column company code but it hangs the table.

I must be wrong, but it is my inner filling that sql server saves the computed expression somewhere before re-writing and that some undocumented feature can show that saved script.

Thanks.

Why do you need to generate scripts in the first place?

Do you not keep your code in source control?

Hi lfor

Yes. I do save a copy of my script.
and
I can use function to keep my script intact
and
I can use trigger to update company code
... These alternates are good
but I want to know, if there is a chance that sql server has saved the script hidden from us
and any undocumented hidden feature can show that.

Thanks