About Me

My photo
Mumbai, Maharastra, India
He has more than 7.6 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in various database concepts. You can reach him at viki.keshari@gmail.com https://www.linkedin.com/in/vikrammahapatra/ https://twitter.com/VikramMahapatra http://www.facebook.com/viki.keshari

Search This Blog

Monday, April 27, 2015

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

Sunday, April 19, 2015

Find A, B, C with a condition where A+B+C=30 Chose A, B, C only from given list of number (1,3,5, 7,9,11)


Thanks Sushree for whatapping me this question, but unfortunately the answer is NO we cannot chose  any number from the given list to form A+B+C=30 :D

As a Techie as soon as I got this problem from my cute friend, the only reflection popped up in my mind...Oh!! Put pen to paper write a small block of code and hit upon the answer.

I am so stupid, I didn’t even thought that the given numbers are all ODD in nature, and when you add three odd number you will never going to get even result and here we are expecting to get 30 i.e. even output. :D

But anyways to my best level of stupidity I wrote a small piece of code, which ultimately proves that it is not possible J

DECLARE @no1 INT
       ,@no2 INT
       ,@no3 INT
       ,@no4 INT

CREATE TABLE #temp (
       id INT identity(1, 1)
       ,GivenNo INT
       );

INSERT INTO #temp
SELECT 1 UNION
SELECT 3 UNION
SELECT 5 UNION
SELECT 7 UNION
SELECT 9 UNION
SELECT 11 UNION
SELECT 13 UNION
SELECT 15

WHILE (1 = 1)
BEGIN
       SELECT @no1 = GivenNo
       FROM #temp
       WHERE id = FLOOR(10 * RAND());

       SELECT @no2 = GivenNo
       FROM #temp
       WHERE id = FLOOR(10 * RAND());

       SELECT @no3 = GivenNo
       FROM #temp
       WHERE id = FLOOR(10 * RAND());

       IF isnull(@no1, 0) + isnull(@no2, 0) + isnull(@no3, 0) = 30
       BEGIN
              PRINT cast(@no1 AS VARCHAR(10));
              PRINT cast(@no2 AS VARCHAR(10));
              PRINT cast(@no3 AS VARCHAR(10));

              BREAK
       END
END

DROP TABLE #temp

Expecting a tea with you  :)


Post Reference: Vikram Aristocratic Elfin Share