How could you ensure that your VIEW is binded to your table in such a way that the tables cannot be deleted without deleting the VIEW. (SCHEMABINDING)

Finally I got some time to write this post; yes it is quite possible with the schemabing clause, creating a view with SCHEMABINDING option locks the underlying tables and prevents any changes that may change the table schema.

Imagine that you have created a view and you have altered the schema of underlying table (deleted one column).Next time when you run your view, it will fail. Here is when SCHEMABINDING comes into picture

lets do some practical on this.. we having a able named  DEMOFORSCHEMABINDING_TAB.

select * from demoforschemabinding_tab

id          name                 department           salary         pf_contribution

----------- -------------------- ----------------- -------------- -----------------

1           Aadita               IT                   35000.00       12000.00

2           Aakarsh              Sofware              45000.00       13000.00

3           Aaliya               Insurance            56000.00       13500.00

4           Falak                Account              35000.00       12000.00

(4 row(s) affected)

Now we are trying to create a view without SCHEMABINDING option

create view vw_without_schmeabinding_demoforschemabinding_tab

as (select * from demoforschemabinding_tab)

Command(s) completed successfully.

select * from vw_without_schmeabinding_demoforschemabinding_tab

id          name          department  salary   pf_contribution

----------- --------      ----------  ------   ---------------

1           Aadita         IT         35000.00  12000.00

2           Aakarsh        Sofware    45000.00  13000.00

3           Aaliya         Insurance  56000.00  13500.00

4           Falak          Account    35000.00  12000.00

(4 row(s) affected)

Now let’s try to drop a column say pf_contribution

alter table demoforschemabinding_tab

drop column pf_contribution

Command(s) completed successfully.

Let’s see the table data.

select * from demoforschemabinding_tab

id          name                 department           salary

----------- -------------------- -------------------- --------

1           Aadita               IT                   35000.00

2           Aakarsh              Sofware              45000.00

3           Aaliya               Insurance            56000.00

4           Falak                Account              35000.00

(4 row(s) affected)

Yes the column has been dropped. Now it is having only four columns.

Let find the same record from the view we have created in the above step.

select * from vw_without_schmeabinding_demoforschemabinding_tab

Msg 4502, Level 16, State 1, Line 1

View or function 'vw_without_schmeabinding_demoforschemabinding_tab' has more column names specified than columns defined.

The reason for this error message: The underlying schema (structure) of table has been changed on which the view was created.

So the question of the discussion is how to ensure that the schema (structure) of the table cannot be modified or dropped unless that view is dropped or changed. Otherwise, the Database Engine raises an error when executing ALTER statement on the table.

The answer to this question is using SCHEMABINDING. Let’s take an example to explain SCHEMABINDING.

select * from demoforschemabinding_tab

id          name                 department           salary

----------- -------------------- -------------------- -----------

1           Aadita               IT                   35000.00

2           Aakarsh              Sofware              45000.00

3           Aaliya               Insurance            56000.00

4           Falak                Account              35000.00

(4 row(s) affected)

Now we will try to create a view on this table with SCHEMABINDING

create view vw_with_schmeabinding_demoforschemabinding

with schemabinding

as ( select id,name,department,salary from dbo.demoforschemabinding_tab)

Command(s) completed successfully.

Let’s run a select statement with the above created view

select * from vw_with_schmeabinding_demoforschemabinding

id          name                 department           salary

----------- -------------------- -------------------- ---------------------------------------

1           Aadita               IT                   35000.00

2           Aakarsh              Sofware              45000.00

3           Aaliya               Insurance            56000.00

4           Falak                Account              35000.00

(4 row(s) affected)

Now let’s try to modify the structure of the underlying table DEMOFORSCHEMABINDING_TAB

alter table demoforschemabinding_tab

drop column salary

Msg 5074, Level 16, State 1, Line 1

The object 'vw_with_schmeabinding_demoforschemabinding' is dependent on column 'salary'.

Msg 4922, Level 16, State 9, Line 1

ALTER TABLE DROP COLUMN salary failed because one or more objects access this column.

The ALTER fails and gives the message 'vw_with_schmeabinding_demoforschemabinding' is depends on column salary so it cannot be modified or dropped. So this is the benefit of schema binding in view that is it will not allow to modify the underlying structure of table if any view is depends on the table schema.

Post Reference: Vikram Aristocratic Elfin Share

Case with Update statement Or Conditional Update

There are certain situation where we need some updation  on the basis of condition. For example  if name contain ‘Kruthika’ update it to ‘Kruthik’.

Our table is containing the following records

select * from user_tab

user_id     user_name                                          active

----------- -------------------------------------------------- ------

1           Kruthika                                           1

2           Orpita                                             1

3           Aalap                                              1

4           Trishona                                           1

5           Shobna                                             1

6           Eekalabya                                          1

7           Phaneesh                                           1

8           Kishalaya                                          1

9           Mrinmoy                                            1

10          Satyajit                                           1

16          suranjan                                           1

(11 row(s) affected)

You might be thinking this update can be accomplished with the help of were condition in update statement. Correct it goes like this

update user_tab

set user_name = 'Kruthik'

where user_name = 'Kruthika'

(1 row(s) affected)

select * from user_tab

user_id     user_name                                          active

----------- -------------------------------------------------- ------

1           Kruthik                                            1

2           Orpita                                             1

3           Aalap                                              1

4           Trishona                                           1

5           Shobna                                             1

6           Eekalabya                                          1

7           Phaneesh                                           1

8           Kishalaya                                          1

9           Mrinmoy                                            1

10          Satyajit                                           1

16          suranjan                                           1

(11 row(s) affected)

But consider a situation where you need to update ‘Trishona’ with ‘Trishon’ and ‘suranjan’ with ‘suranjana’.

Is it possible with WHERE clause in Update statement. ANSWER IS NO.

Then what is option left for such update, Here comes CASE, let see how this update can be done with CASE

update user_tab

set user_name = (case user_name

                              when 'Trishona' then 'Trishon'

                              when 'suranjan' then 'suranjana'

                              else user_name end)

user_id     user_name                                          active

----------- -------------------------------------------------- ------

1           Kruthika                                           1

2           Orpita                                             1

3           Aalap                                              1

4           Trishon                                            1

5           Shobna                                             1

6           Eekalabya                                          1

7           Phaneesh                                           1

8           Kishalaya                                          1

9           Mrinmoy                                            1

10          Satyajit                                           1

16          suranjana                                          1

(11 row(s) affected)

I really like this keyword a lot of thing can be accomplished with CASE.

Post Reference: Vikram Aristocratic Elfin Share

How to call a user defined function with a default parameter

Today it happened one of my team member called up me and ask me how to call his function where he has used default parameter. The snapshot of his function is like this only..

create function fnTest(@param1 int, @param2 int = 1 )

returns int

as

begin      

      return @param2      

end

His question was how to call this function in select statement, I simple said, u can either pass two parameter while calling this function, if you are doing so then the second default parameter @param2 value  will get replaced by the new one you pass while calling this function.

select dbo.fnTest(2,5)

-----------

5

(1 row(s) affected)

Where as  if you pass only one parameter to the calling funtion then the @param2 will take the dufault value as 1 in this case.

I give him the below mention line for calling the funtion with one parameter as soon as we excute this line of code we got  error message

select dbo.fnTest(7)

Msg 313, Level 16, State 2, Line 1

An insufficient number of arguments were supplied for the procedure or function dbo.fnTest.

Belive me I was not aware of this then somebody from other team sitting behind us told to use default keyword while calling funtion with default parameter.

select dbo.fnTest(1,default)

-----------

1

(1 row(s) affected)

Thanks Shekhar for the solution.

Post Reference: Vikram Aristocratic Elfin Share