Designing a perfect entity relationship diagram for our database together with a marvellous table structure is never good enough…

Have look at the following example (for MS SQL Server) creating two tables and a view:

CREATE TABLE T1(
  F1 INT
, F2 VARCHAR(10)
);

CREATE TABLE T2(
  F1 INT
, F3 INT
, F4 VARCHAR(30)
);

CREATE VIEW V12
AS
  SELECT
    T1.*
  , T2.F3
  , T2.F4
  FROM T1
  INNER JOIN T2 
  ON T1.F1 = T2.F1
;

A view, a function or something else, we try to write re-usable code so that we do not have to repeat it again and again. Certainly, while we are developing a system, we keep changing things especially when the requirements are not so clearly defined at the beginning, or the client keeps asking for new features. In this case, let’s say another field is required in table T1 and we have to add it (ALTER TABLE).

ALTER TABLE T1
ADD F5 DATETIME
;

This simply means that our view is now broken! It needs to be recompiled!

1. One of the solutions to this problem is to create our view with the option of WITH SCHEMABINDING.

CREATE VIEW V12 WITH SCHEMABINDING
AS
  SELECT
    T1.*
  , T2.F3
  , T2.F4
  FROM T1
  INNER JOIN T2 
  ON T1.F1 = T2.F1
;

Now this is supposed to be preventing any changes to the schema of the database that will damage our view.

2. The second solution is to use fields explicitly without using asterisk (*)

CREATE VIEW V12 WITH SCHEMABINDING
AS
  SELECT
    T1.F1
  , T1.F2
  , T2.F3
  , T2.F4
  , T1.F5
  FROM T1
  INNER JOIN T2 
  ON T1.F1 = T2.F1
;

If we have many fields in your tables and a list of views, functions that depend on each other, this will cause a headache. You will end up with recompiling the objects.

Try to find the balance; avoid too many dependencies.
Do not create unnecessary objects such as:

CREATE FUNCTION F(@ID INT)
RETURNS TABLE
AS
  RETURN
     SELECT *
     FROM T1
     WHERE F1 = @ID
;
Tags: ,
Categories: