Views in sql server

Views are virtual table that are compiled at runtime. We use that column in view that need to retrieve / query again and again. We can also make index, trigger on views. We can also write insert/ update query with a view like an actual table.

In the main reason of using view is for security purpose. Second reason is that the end user only knows a few columns. End users do not have knowledge about actual tables. By using view we can minimize writing long query again and again.

Syntax:

Syntax for create view is:

CREATE VIEW "VIEW NAME"

AS

[Select statements]

Syntax for alter view is:

ALTER VIEW "VIEW NAME"

WITH SCHEMABINDING

AS

[Select statements]

Syntax for drop view is:

DROP VIEW "VIEW NAME"

Note: We do not use BEGIN and END while creating/ altering view.

Example:

CREATE TABLE MST_EMPLOYEE_MASTER

(

      EMP_ID NUMERIC(18,0)IDENTITY(1,1),

      EMP_NAME VARCHAR(50) NOT NULL,

      DEPARTMENT_NAME VARCHAR(25),

      SALARY NUMERIC(18,2) DEFAULT 0,

      JOINING_DATE DATETIME NOT NULL DEFAULT GETDATE()

);

INSERT INTO MST_EMPLOYEE_MASTER (EMP_NAME, DEPARTMENT_NAME, SALARY) VALUES('Shashi','Software Development',50000.00);

INSERT INTO MST_EMPLOYEE_MASTER (EMP_NAME, DEPARTMENT_NAME, SALARY) VALUES('Vikas','Software Development',100000.00);

INSERT INTO MST_EMPLOYEE_MASTER (EMP_NAME, DEPARTMENT_NAME, SALARY) VALUES('Prince','Software Testing',80000.00);

INSERT INTO MST_EMPLOYEE_MASTER (EMP_NAME, DEPARTMENT_NAME, SALARY) VALUES('Mathew','Software Testing',50000.00);

INSERT INTO MST_EMPLOYEE_MASTER (EMP_NAME, DEPARTMENT_NAME, SALARY) VALUES('Rahul','Software Testing',50000.00);

INSERT INTO MST_EMPLOYEE_MASTER (EMP_NAME, DEPARTMENT_NAME, SALARY) VALUES('Jonson','Human Resource',60000.00);

Example 1:

CREATE VIEW V_EMPLOYEE_MASTER

AS

      SELECT EMP_ID, EMP_NAME, DEPARTMENT_NAME FROM MST_EMPLOYEE_MASTER

And when select from view :

SELECT * FROM V_EMPLOYEE_MASTER;

Example 2:

ALTER VIEW V_EMPLOYEE_MASTER

AS

      SELECT EMP_ID, EMP_NAME, DEPARTMENT_NAME, SALARY FROM MST_EMPLOYEE_MASTER

And when select from view :

SELECT * FROM V_EMPLOYEE_MASTER;

Example 3:

DROP VIEW V_EMPLOYEE_MASTER

Some important points about View:

1.       We cannot use order by in view without using TOP clause.

2.       When we change table(s) schema from which view has been created, we have to refresh view for preventing unexpected result from view.

Syntax: EXECUTE SP_REFRESHVIEW 'View Name'

Example:EXECUTE SP_REFRESHVIEW 'V_EMPLOYEE_MASTER'

3.       In a view we can use maximum 1024 column(s).

4.       When we want to perform DML (INSERT/ UPDATE/ DELETE) operation on view then view should follow following condition:

i)                    Column(s) that we modify must be from only one table.

ii)                   Column(s) that we modify must not be created manually by other ways besides actual table column (actual column alias can be but not some operation performed and then created like column made from aggregate function like COUNT,MAX,AVG etc.)

iii)                 When we are doing INSERT operation all NOT NULL column(s) must be included except IDENTITY, DEFAULT column(s).

5.       Use WITH SCHEMABINDING when we want to prevent table dropped and view remain orphaned.

Syntax:

CREATE VIEW "VIEW NAME"

WITH SCHEMABINDING

AS

[Select statements]

Example:

CREATE VIEW V_EMPLOYEE_MASTER

WITH SCHEMABINDING

AS

SELECT EMP_ID, EMP_NAME, DEPARTMENT_NAME, SALARY FROM DBO.MST_EMPLOYEE_MASTER

getting css property value in jquery and applying css to an element in jquery

In jquery we can get the css value in following ways:

“.css” function is used to get css property of any element in jquery.

Example :

    <p style="font-size:20px" id="para1">

        Put content here.

    </p>

    <input type="submit" name="btnSubmit" value="Submit" onclick="return getBackColor();"        id="btnSubmit" />

function getBackColor() {

var color=$("p").css("font-size");

       alert(color);      

}  

We can set css to an element as following:

1.       Applying Single CSS

     $("p").css("font-size","30px");

2.       Applying Multiple CSS

     $("p").css({ "font-size": "30px", "color": "red" });

Note: We can use ID of the element also for applying css or getting value of css like $("#para1").css

Scope of a variable in javascript

Global scope of a variable:

When we declare a variable outside of a function then that variable has the global scope.

This type of variable can be accessed anywhere in the program or file.

Example:

var str = "test value";

It can be used anywhere in the program.

Local scope of a variable:

When we create a variable inside a function body then that variable has the local scope.

This type of variable cannot be accessed outside the function.

Example:

function getResult() {

    var result = 10;

    alert(result);

}

Note 1: When we do not declare a variable with “var” inside a function then

that variable can also be accessible outside of the function. Hence it has global scope.

Example:

<asp:Button ID="btn" runat="server" Text="submit" OnClientClick="getResult();" />

function getResult() {

    result = 10;   

    test();

    return false;

}

function test() {

    alert(result);//result will return 10 which is declared in getResult()

    return false;

} 

Note 2: When we define nested function then inner function has access to the variable defined in outer function but outer function cannot access to the variable defined in inner function.

Example:

function getResult() {

    var result = 10;

    function test() {

        var str = "Test";

        alert(result); //output will be 10

        return false;

    }

    alert("str" + str);// str will not be accessible

    return false;

}