How to make a table READ ONLY

Posted on by madhivanan

Visakh Murukesh, SQL Server MVP and my friend, posted about SQLServer Tips: How To Make a Table Read Only in Database in which he showed many methods on how to make a table read-only

I know another method of doing it using a view.

Let us create the following dataset

create table sales_target(sales_year int, sales_target decimal(12,2))
insert into sales_target (sales_year,sales_target)
select 2015, 2000000 union all
select 2016, 2500000

Now create an view out of this table which has union/union all included as shown below
 


CREATE view sales_target_read_only as
select * from sales_target
union all
select * from sales_target where 1=0

Now let the user access to the view sales_target_read_only and not the underlying table sales_target.

If you try to add data to this view

insert into sales_target_read_only(sales_year,sales_target)
select 2017, 3000000

You get the following error

Msg 4406, Level 16, State 1, Line 2
Update or insert of view or function 'sales_target_read_only' failed because it contains a derived or constant field.

Similar error occurs when try to update this view

update sales_target_read_only set sales_target=10000 where sales_year=2015

The error is

Msg 4406, Level 16, State 1, Line 2
Update or insert of view or function 'sales_target_read_only' failed because it contains a derived or constant field.

If you try to delete rows from this view

delete from sales_target_read_only where sales_year=2015

You get this error

Msg 4426, Level 16, State 1, Line 1
View 'sales_target_read_only' is not updatable because the definition contains a UNION operator.

Advertisement

Empty string for non character datatypes

Posted on by madhivanan

One of my friends asked me why an empty string for a datetime value is stored as 1900-01-01 00:00:00.000. Well you should not pass an empty string to a non- character data type because depends on the datatype SQL Server internally convert empty string and store it differently.

Let us consider the following example

declare @test table (int_col int, datetime_col datetime, float_col float,money_col money)
insert into @test (int_col, datetime_col, float_col,money_col)
select '','','',''
select * from @test

The result is

int_col datetime_col           float_col   money_col
------- ---------------------- ---------   -----------
0       1900-01-01 00:00:00.000   0         0.00

All empty strings are actually 0 and for datetime datatype 0 is Jan 01, 1900

select cast(0 as datetime)

results to

1900-01-01 00:00:00.000

 

My point is that you should not pass empty string to non-character datatype. If you want you need to set it to NULL. Passing empty string will result to default value of 0 in SQL Server and probably will throw error in other RDBMSs. I also think that SQL Server should throw an error in this case