SCHEMABINDING: You need to use schema qualified names for everything

A simple definition: Schema binding is a mode of ensuring that the objects referenced within a function or view, do not changes their definition in any way tat would crack the binded objects.

With SCHEMABINDING the obstacle is that you need to use schema qualified names for everything. You will get lots of error messages like this

Lets stimulate the same error with the below sample. Here we are creating table without any qualified schema

create table test1

(col1 int,

col2 varchar(10),

col3 varchar(10))

go

Since our table is ready lets try to create a view and refer the above table in it.

 create view vw_test1

with schemabinding

as

select col1,col2 from test1

Msg 4512, Level 16, State 3, Procedure vw_test1, Line 10

Cannot schema bind view 'vw_test1' because name 'test1' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Here we can see our code fall in error, since we have not specified any schema for our view. Lets add a schema and bind our above view to the newly created schema.

create view dbo.vw_test1

with schemabinding

as

select col1,col2 from test1

Msg 4512, Level 16, State 3, Procedure vw_test1, Line 10

Cannot schema bind view 'dbo.vw_test1' because name 'test1' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

So you need everything i.e every object to be schema bounded.

Lets drop the table and recreate it with full qualified name

drop table test1

Command(s) completed successfully.

create schema test

Command(s) completed successfully.

create table test.test1

(col1 int,

col2 varchar(10),

col3 varchar(10))

go

Command(s) completed successfully.

Lets now create view with fully qualified name

create view dbo.vw_test1

with schemabinding

as

select col1,col2 from test.test1

Command(s) completed successfully.

col1        col2       col3

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

(0 row(s) affected)

Conclusion: You need to create every object under full qualified name i.e. defined with schema while using SCHEMABINDING option.

Leave everything and do your coding; that’s the way to happiness. Just for SQL lovers :)

Post Reference: Vikram Aristocratic Elfin Share

Can we create Parameterized View in SQL Server?

SQL Server doesn’t have parameterized view but close to it they came up with Table Value User Defined Function in SQL Server 2000.

If it did exist, the syntax would be 

create view ParamterView

(@empName varchar(10))

AS

 select * from employee where emp_name like @empName

Msg 102, Level 15, State 1, Procedure ParamterView, Line 2

Incorrect syntax near '@empName'.

Msg 137, Level 15, State 2, Procedure ParamterView, Line 4

Must declare the scalar variable "@empName".

But unfortunately we don’t have luxury to create parameterized view like this.

But we have alternative approach to stimulate the same using Inline table value function

create function pv_studentInfo (@student_id int)

returns table

as

return

(

    select * from Student

    where id = @student_id

)

Go

Lets call this view using select statement.

select * from pv_studentInfo(1)

id          name

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

1           vikram

(1 row(s) affected)

View offer performance benefit against Function by allowing indexes on it, where as you cannot create indexes on Function but yes you can wrap indexed indexed view in Function.

Your code silently induce oxygen to your business partner.

 

Post Reference: Vikram Aristocratic Elfin Share

TOP 100 PERCENT and ORDER BY obsolete from SQL Server 2005

In SQL 2000 it was an attempt to return all records of the view in the correct order. In SQL 2005 and up you can not simulate ORDER BY using this trick anymore, so SELECT TOP (100) PERCENT has no meaning.

Lets stimulate the scenerio. We create a table testTop100 for our explanation with field id and name

create table testTop100

(id int identity(1,1),

name varchar(10))

Command(s) completed successfully.

Lets insert few records to testTop100

insert into testTop100 values('Abhaya')

insert into testTop100 values('Nitya')

insert into testTop100 values('Ananya')

insert into testTop100 values('Roma')

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

Now we will be creating view vwTestTop100 with top 100 Percent and Order by clause

create view vwTestTop100

as

select top 100 percent * from testTop100 order by id desc

go

Command(s) completed successfully.

Lets see the result with simple select query

select * from testTop100

id          name

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

1           Abhaya

2           Nitya

3           Ananya

4           Roma

(4 row(s) affected)

Now lets see the result of View which is using Top 100 Percent and order by on id desc

select * from vwTestTop100

id          name

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

1           Abhaya

2           Nitya

3           Ananya

4           Roma

(4 row(s) affected)

As we can see SQL Server neither throws error nor it acknowledge the presence of order by clause, it simply ignore the order by clause in View defination.

Now lets try the following

select * from vwTestTop100 order by id desc

id          name

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

4           Roma

3           Ananya

2           Nitya

1           Abhaya

(4 row(s) affected)

Now from the output we can see, desired result found

Conclusion :  It is pointless to add ORDER BY clause to the view definition and expect records to come in that ORDER. If you need records ordered, don't put ORDER BY in the view, but rather

select * from myView ORDER BY OrderFields.

This way the correct order will be guaranteed.

Abide by code semantic, u miss all coding fun..Walk off beyond   

Post Reference: Vikram Aristocratic Elfin Share

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