Edit More Than 200 Rows in SQL Server 2008

While working with Sql Server, sometimes we need to update a record quickly. Even so quickly that we don’t want to write a query. Even that’s not a big problem just we need to right click desired table and select “Edit Top 200 Rows” option. This opens top 200
records of that table in editable mode and you need to just make desired changes inside cell of specific record and press tab. Its quite quick but what if that our records doesn’t come in top 200 and its placed after 200 in table e.g. 500th position. Ohhh for this i cann’t be lazy and have to write update query 😦
But before doing this wait for minute as even we can edit more than 200 records using editable window. But how ??? Below is the solution :

In Sql Server 2008

  1. Click on Tools > Options…
  2. Inside Options window, Select “SQL Server Object Explorer” in left pane.
  3. On Right side pane under “Table and View Options” section, change value of “Value for Edit Top<n> Rows command” property from 200 to something higher e.g. 1000.
  4. Click on Ok and you are done.

Now whenever you right click on any database table you will find option “Edit Top 1000 Rows“. Just click on that and an editable window with top 1000 records will open in fronts of you.

So be lazy after doing this smart workaround. 🙂

 

Stored Procedure in Depth

Stored Procedures can be defined as compiled SQL statements stored in the database.
Using stored procedures has many advantages over simply calling a query from the front-end, especially in web applications and large size applications.

Advantages of Stored Procedures:
1. Since the queries are executed as a batch, the network traffic is significantly reduced.
2. Execution time is greatly reduced due to the fact that the stored procedures are pre-compiled.
3. Once a stored procedure is executed, it will reside in the cache of the SQL Server. As a result, any of the subsequent calls will be speeded up.
4. Stored procedures provide a different layer of abstraction for the application and has two main advantages.
1. First, without changing the application, we can easily modify the stored procedure when business rules change.
2. More than that, they provide more efficient and secure data access.

Creating a Stored Procedure
Syntax:

CREATE PROC [ EDURE ] procedure_name [ ;number ]
[ { @parameter data_type }
[ VARYING ] [ =default ] [ OUTPUT ]
] [ ,… ]
[ WITH
{
RECOMPILE
| ENCRYPTION
| RECOMPILE, ENCRYPTION
}
]
[ FOR REPLICATION ]
AS
sql_statement
procedure_name
Is the name of the stored procedure to be created.
number
Is an optional parameter, which is used to group procedures of the same name so they can be dropped using a single DROP PROCEDURE statement.
@parameter
Is a parameter in the procedure. There can be one or more parameters.
data_type
Is the data type of the given parameter.
VARYING
Specifies the result-set supported as an output. Applies only to the cursor parameters.
default
Is a default value for the parameter.
OUTPUT
Indicates that the given parameter is a return parameter.
RECOMPILE
RECOMPILE indicates that SQL Server does not keep the cache for the procedure and it is recompiled each time when it is executed.
ENCRYPTION
ENCRYPTION indicates that the SQL will prevent the procedure from being published as part of SQL Server replication.
FOR REPLICATION
This is used to specify that stored procedures created for replication cannot be executed on the subscribing server and is executed only during replication.
AS
The keyword used just before the SQL statements in the procedure.
sql_statement
Is the SQL statement that is to be executed in the procedure.
The CREATE PROCEDURE (or CREATE PROC) statement is used to create a stored procedure.
Examples
Code:

CREATE PROCEDURE spDisplayAll
AS
SELECT * FROM Students
GO
Output:
The command(s) completed successfully.
Explanation:
In the above example, we have created a stored procedure spDisplayAll that fetches all of the records in the Students table.
Executing a Stored Procedure
Syntax:
[ EXEC [ UTE ] ]
{ procedure_name [ ;number ] | @procedure_name_variable }
[ [ @parameter= ] { value | @variable [ OUTPUT ] | [ DEFAULT ] ,… } ]
[ WITH RECOMPILE ]
procedure_name
Is the name of the stored procedure.
number
An optional integer used to group procedures of the same name so they can be dropped using a single DROP PROCEDURE statement.
@procedure_name_variable
The locally defined variable that represents a stored procedure name.
@parameter
Is the parameter for a procedure, as defined in the CREATE PROCEDURE statement.
value
Is the value of the parameter to the procedure. If parameter names are not specified, parameter values must be supplied in the order defined in the CREATE PROCEDURE statement.
@variable
Is the variable that stores a parameter or a return parameter.
OUTPUT
Specifies that the stored procedure returns a parameter.
DEFAULT
Supplies the default value of the parameter as defined in the procedure.
Stored procedures can be run by using the EXEC or EXECUTE command. Parameter values can be supplied if a stored procedure is written to accept them.
Examples
Code:
EXEC spDisplayAll;
Output:
Id Name Std_Course Phone Std_Grade
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
1 Job Mathew 2 12345 2
2 Rock Feller 4 46565 3
3 Harri Robins 3 78788 1
4 Joe Philip 3 46456 2
5 Nadia Alex 0 78565 4
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
(5 row(s) affected)
Explanation:
When we execute the spDisplayAll stored procedure, using the EXEC command, we get all the records in the ‘Students’ table.
Using Parameters
Parameters can be passed to the stored procedures. This makes the procedure dynamic.
The following points are to be noted:
o One or more number of parameters can be passed in a procedure.
o The parameter name should proceed with an @ symbol.
o The parameter names will be local to the procedure in which they are defined.
The parameters are used to pass information into a procedure from the line that executes the parameter. The parameters are given just after the name of the procedure on a command line. Commas should separate the list of parameters.

The values can be passed to stored procedures by:
1. By supplying the parameter values exactly in the same order as given in the CREATE PROCEDURE statement.
2. By explicitly naming the parameters and assigning the appropriate value.
Examples
Code:
CREATE PROCEDURE spSelectStudent (@Course INTEGER, @Grade INTEGER)
AS
SELECT * FROM Students
WHERE Std_Course=@Course AND Std_Grade <= @Grade
GO
EXEC spSelectStudent 3, 2;
Output:
Id Name Std_Course Phone Std_Grade
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
3 Harri Robins 3 78788 1
4 Joe Philip 3 46456 2
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
(2 row(s) affected)
Explanation:
In the above example, the procedure is defined with two parameters. It should be noted that while executing the procedure the parameters should be passed in the same order of that in the CREATE statement. In this case, the first argument corresponds to Std_Course and second argument to Std_Grade.
Language(s): MS SQL Server
Code:

EXEC spSelectStudent @Course=3, @Grade=2;

EXEC spSelectStudent @Grade=2, @Course=3;
Output:
Id Name Std_Course Phone Std_Grade
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
3 Harri Robins 3 78788 1
4 Joe Philip 3 46456 2
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
(2 row(s) affected)

Id Name Std_Course Phone Std_Grade
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
3 Harri Robins 3 78788 1
4 Joe Philip 3 46456 2
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
(2 row(s) affected)
Explanation:
Here since we are explicitly naming the parameters and assigning the appropriate value, the stored procedure allows the parameters to be supplied in any order.
Language(s): MS SQL Server
Code:
CREATE PROCEDURE spSelectStudent1 (@Course INTEGER=2, @Grade INTEGER=3)
AS
SELECT * FROM Students
WHERE Std_Course=@Course AND Std_Grade <= @Grade
GO
EXEC spSelectStudent1;
Output:
Id Name Std_Course Phone Std_Grade
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
1 Joe Mathew 2 12345 2
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
(1 row(s) affected)
Explanation:
The stored procedures can be created with optional parameters with default values, so that if no values are assigned to the parameters then the default value will be taken as the value of the parameter.

In the above example, the procedure is executed without any parameter. So it takes the default parameters, which are @Course as 2, @Grade as 3.
Language(s): MS SQL Server
Code:
EXEC spSelectStudent1 @Course=4, @Grade=4;
Output:
Id Name Std_Course Phone Std_Grade
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
2 Rock Feller 4 46565 3
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
(1 row(s) affected)
Explanation:
In the above example, the procedure takes the specified parameters of @Course as 4, @Grade as 4.
Deleting a Stored Procedure
Syntax:
DROP PROCEDURE procedure_name, …
procedure_name
Is the name of the stored procedure or stored procedure group to be removed.
When a stored procedure is no longer needed, it can be deleted using the DROP PROCEDURE command.
Examples
Code:
DROP PROCEDURE spSelectStudent1;
Output:
The command(s) completed successfully.
Explanation:
The above DROP command deletes the stored procedure spSelectStudent1 from the database.
Modifying a Stored Procedure
Syntax:
ALTER PROC [ EDURE ] procedure_name [ ;number ]
[ { @parameter data_type }
[ VARYING ] [ =default ] [ OUTPUT ]
] [ ,… ]
[ WITH
{
RECOMPILE
| ENCRYPTION
| RECOMPILE, ENCRYPTION
}
]
[ FOR REPLICATION ]
AS
sql_statement
The stored procedure modification is usually done using the ALTER PROCEDURE statement. It can also be done by deleting the existing stored procedure and then creating a new one with the same name. If we are using the ALTER PROCEDURE statement any of the permissions associated with the stored procedure are retained. In the other case, however, the permissions will be lost.
We can alter a stored procedure so that only the parameter definition is changed and not the permissions that are set for the stored procedure. The parameters from the ALTER PROCEDURE statement are the same as the ones for the CREATE PROCEDURE statement.
Examples
Code:
CREATE PROCEDURE spGetAvgGrade
AS
SELECT AVG(Std_Grade) FROM Students
GO
EXEC spGetAvgGrade;
Output:
AverageGrade
________________________________________
2
________________________________________
(1 row(s) affected)
Language(s): MS SQL Server
Code:
ALTER PROCEDURE spGetAvgGrade (@Course INTEGER)
AS
SELECT AVG(Std_Grade) as AverageGrade FROM Students
WHERE Std_Course = @Course
GO

EXEC spGetAvgGrade 3;
Output:
AverageGrade
________________________________________
1
________________________________________
(1 row(s) affected)
Explanation:
This example demonstrates how to use the ALTER PROCEDURE command to modify a procedure and then execute it.
Language(s): MS SQL Server
Code:
DROP PROCEDURE spGetAvgGrade;

CREATE PROCEDURE spGetAvgGrade (@Course INTEGER)
AS
SELECT AVG(Std_Grade) as AverageGrade FROM Students
WHERE Std_Course = @Course
GO

EXEC spGetAvgGrade 3;
Output:
The command(s) completed successfully.

AverageGrade
________________________________________
1
________________________________________
(1 row(s) affected)
Explanation:
In the above example, we first delete the existing procedure and then recreate the procedure.
Renaming a Stored Procedure
Syntax:
sp_rename ‘procedure_name1’, ‘procedure_name2’
procedure_name1
The current name of the stored procedure
procedure_name2
The new name of the stored procedure.
A stored procedure can be renamed. The new name should follow the rules for identifiers.
Examples
Code:
EXEC sp_rename ‘spGetAvgGrade’, ‘spNewAvgGrade’;
Output:
Caution: Changing any part of an object name could break scripts and stored procedures.
The object was renamed to ‘spNewAvgGrade’.
Explanation:
In the above example we change the name of the stored procedure spGetAvgGrade to spNewAvgGrade.
To View a Stored Procedure
Syntax:
1. To view the definition of a stored procedure:
sp_helptext procedure_name
2. To view the information about a stored procedure:
sp_help procedure_name
3. To view the dependencies of the stored procedure:
sp_depends procedure_name
procedure_name
Is the name of the stored procedure.
SQL Server allows us to view the definition, information, and dependencies of a stored procedure.
Examples
Code:
sp_helptext spNewAvgGrade;
Output:
CREATE PROCEDURE spGetAvgGrade (@Course INTEGER)
AS
SELECT AVG(Std_Grade) as AverageGrade FROM Students
WHERE Std_Course = @Course
Explanation:
In the above example, the sp_helptext displays the text of the spNewAvgGrade stored procedure.
Language(s): MS SQL Server
Code:
sp_help spNewAvgGrade;
Output:
Name Owner Type Created_datetime
________________________________________ ________________________________________ ________________________________________ ________________________________________
spNewAvgGrade dbo stored procedure 2003-09-14 23:53:13.810
________________________________________ ________________________________________ ________________________________________ ________________________________________

Parameter_name Type Length Prec Scale Param_order
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
@Course int 4 10 0 1
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
Explanation:
This example displays information about the stored procedure.
Language(s): MS SQL Server
Code:
sp_depends spNewAvgGrade;
Output:
In the current database, the specified object references the following:
Name Type Updated Selected Column
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
dbo.Students user table no no Std_Course
dbo.Students user table no no Std_Grade
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
Explanation:
This example shows the dependencies of the stored procedure.

Sql Server: Insert comma separated values in different rows

This is a code snippet for a Stored Procedure which accepts a comma separated values in parameter @Retailers which needs to be stored in a table with different rows. One fine day i had such type of requirement and i fount this post in a forum. I felt like it to share with you.

Declare @MakerBlockID int, @Pos int, @phrase Varchar(4000)
— Insert the maker details
Insert into MakerBlock Values (@Routingnum, @Accountnum, @MinDecAmt, @MaxDecAmt)
— Get the Id from MakerBlock
Select @MakerBlockID = SCOPE_IDENTITY( )

Set @phrase = Replace(@Retailers,’ ‘ ,”)
While Len(@phrase) >0
Begin
Set @pos = CHARINDEX (‘,’,@Phrase)
if @pos > 0
Begin
Insert into MakerBlockRetailers Select @MakerBlockID, Left(@Phrase,@pos-1)
Set @Phrase = Right(@Phrase, Len(@phrase)-@pos)
end
else
Begin
Insert into MakerBlockRetailers Select @MakerBlockID, @Phrase
Set @phrase=”
end
End

Thanx to Say Hey Kid for providing this code.

Original reference: http://www.sqlservercentral.com/Forums/Topic282332-8-1.aspx

Object cannot be cast from DBNull to other types

Sometimes you need to code around dates in your program. Today i will discuss about the common error we get while displaying a date from back end to front end. What if you have blank/null value inserted in your back end for date and when you get this date to front end and try to bind this in a text box control then you will get a common error …

“Object cannot be cast from DBNull to other types”

No problem you have to put a check before binding the date in text box like below:

if (drPublication[strReleaseDate] != System.DBNull.Value )
{
this.dtTest.SelectedDate = Convert.ToDateTime(drPublication[strReleaseDate]);
}

Convert date from dd/MM/yyyy to MM/dd/yyyy for storing in SharePoint list or Sql Server Database

I was working on a custom web part that takes the date in Indian format e.g. dd/MM/yyyy while inserting the record in SharePoint custom list. But as we know that SharePoint custom list insert the date value as MM/dd/yyyy format. So it was throwing me the error for invalid string format so below is the code snippet that how i resolved this issue:

string sdate = “15/04/2010”;
DateTime dt = new DateTime();
dt = DateTime.ParseExact(sdate, “dd/MM/yyyy”, new System.Globalization.CultureInfo(“en-US”));
string dateToStore = dt.ToShortDateString();

In this way you are capturing date in dd/MM/yyyy format and storing this date in MM/dd/yyyy format in SharePoint custom list.

Happy Coding 🙂

Triggers

A TRIGGER is a special type of stored procedure, which is ‘fired’ automatically when the data in a specified table is modified. It is invoked when an INSERT, UPDATE, or DELETE action is performed on a table.

 

Creating a Trigger
Syntax:
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
    {
       { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
       [ WITH APPEND ]
       [ NOT FOR REPLICATION ]
       AS
       [
          {
             IF UPDATE ( column )
                [ { AND | OR } UPDATE ( column ) … ]
             |
           &nbs
trigger_name
Is the name of the trigger. It should conform to the rules for identifiers.
table
Is the table on which the trigger is to be created.
WITH ENCRYPTION
If this option is specified, the syscomments entries that contain the text of CREATE TRIGGER will be encrypted.
DELETE, INSERT, UPDATE
These keywords specify on which action the trigger should be activated. One of these keywords or any combination thereof in any order can be used.
WITH APPEND
This specifies that an additional trigger should be added.
IF UPDATE
This checks for an INSERT or UPDATE to a specified column and is not used with the DELETE operations. One or more columns can be specified here.
column
It is the name of the column to check for an INSERT or UPDATE action.
IF
Checks, whether the mentioned column or columns were inserted or updated.
COLUMNS_UPDATED
Can be used anywhere inside the body of the trigger.
bitwise_operator
It is the bitwise-operator used for the comparison.
updated_bitmask
This is the integer bitmask of the columns that are actually updated or inserted.
comparison_operator
Is the comparison operator. The equals (=) sign checks whether all columns specified in the updated_bitmask are actually updated. The greater (>) than symbol checks whether any or some of the columns specified in updated_bitmask are updated.
column_bitmask
Is the integer bitmask of those columns to check whether they are updated or inserted.
sql_statement
Is the trigger condition(s) and action(s).

A TRIGGER is created using the CREATE TRIGGER command.
Examples
Code:

CREATE TRIGGER trigAddStudents
ON Students
FOR INSERT
AS
DECLARE @Newname VARCHAR(100)
SELECT @Newname =(SELECT Name FROM INSERTED)
PRINT ‘THE STUDENT ‘ + @Newname + ‘ IS ADDED.’;
Explanation:
Executing this creates a new trigger named trigAddStudents, which is attached to the ‘Students’ table. Whenever a new record is added to the ‘Students’ table, SQL Server will automatically execute our trigger.

Let’s look at the above example in detail:

CREATE TRIGGER trigAddStudents
ON Students
–A new trigger object, trigAddStudents, should be attached to the ‘Students’ table.

FOR INSERT
–The trigger will be fired when an INSERT command is executed on the ‘Students’ table
(If we would like to handle the INSERT and UPDATE events, we would have to use FOR INSERT, UPDATE).

AS
DECLARE @Newname VARCHAR(100)
SELECT @Newname = (SELECT Name FROM Inserted)
–When the trigger is called the queries after the AS keyword is executed.

SELECT Name FROM INSERTED
–The SQL command retrieves the ‘Name’ field from the inserted table. The inserted table, which contains all the values we inserted using the INSERT command.
(Similarly for UPDATE and DELETE command we can use UPDATED and DELETED.)

PRINT ‘THE STUDENT ‘ + @Newname + ‘ IS ADDED.’
–Prints the name selected from the INSERTED table

Language(s): MS SQL Server
Code
:
INSERT INTO Students VALUES (6,’George Mathew’, 1, 25542, 1);
Output:
THE STUDENT George Mathew IS ADDED.
(1 row(s) affected)
Explanation:
When the above INSERT statement is executed, the new record is added to the ‘Students’ table and automatically calls the trigger.

Note:
1. The name of a trigger should follow the rules for identifiers.
2. The CREATE TRIGGER must be the first statement in the batch.
3. Triggers cannot be created on a view, temporary table or system table, but they can reference views or temporary tables.
4. The system tables should not be referenced in a trigger. Use the Information Schema Views instead.

Modifying a Trigger
Syntax:
ALTER TRIGGER trigger_name
ON table
[ WITH ENCRYPTION ]
{
    {
       FOR { [ DELETE ] [ , ] [ UPDATE ] [ , ] [ INSERT ] }
          [ NOT FOR REPLICATION ]
          AS
             sql_statement [ …n ]
    }
    |
    {
       FOR { [ INSERT ] [ , ] [ UPDATE ] }
          [ NOT FOR REPLICATION ]
          AS
          {
             IF UPDATE ( column )
  &nb
A trigger can be modified by either, deleting the trigger and recreating a new trigger or by altering the existing trigger. The parameters from the ALTER TRIGGER statement are similar to those from the CREATE TRIGGER statement.

Examples
Code:
ALTER TRIGGER trigAddStudents
ON Students
FOR INSERT
AS
PRINT ‘THE TRIGGER IS CHANGED.’;
Output:
The command(s) completed successfully.
Explanation:
The above example modifies the trigger created earlier by changing the print that is to be displayed when the trigger is ‘fired’.

Language(s): MS SQL Server
Code:
INSERT INTO Students VALUES (7,’Tom George’, 1, 45452, 2);
Output:
THE TRIGGER IS CHANGED.
(1 row(s) affected)
Explanation:
When the INSERT statement is executed, the modified trigger is ‘fired.’

Renaming a Trigger
Syntax:
sp_rename ‘obj_name’, ‘obj_newname’
obj_name
The current name of the trigger.
obj_newname
The new name of the trigger.
A trigger can also be renamed. The new name should follow the same rules that are used while naming a trigger.
Examples
Code:
sp_rename ‘trigAddStudents’, ‘trigAddStudentsNew’;
Output:
Caution: Changing any part of an object name could break scripts and stored procedures. The object was renamed to ‘trigAddStudentsNew’.
Explanation:
Here the name of trigAddStudents is changed to trigAddStudentsNew.

Viewing a Trigger
Syntax:

1. To view the types of triggers on a table:
sp_helptrigger table1 [ , type ]
2. To view a trigger:
sp_helptext trigger1
table1
Is the name of the table for which to return trigger information.
type
Is an optional parameter where the type of the trigger is to be specified. Its value can be INSERT, UPDATE or DELETE.
trigger1
Is the name of the trigger for which the definition information is to be displayed.
SQL server allows us to determine the types of triggers on a table, view information about a trigger, to view a trigger, and to view the dependencies of it.
Examples
Code:
sp_helptrigger Students;
Output:
trigger_name trigger_owner isupdate isdelete isinsert
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
trigAddStudentsNew dbo 0 0 1
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
(1 row(s) affected)
Explanation:
The above example returns all the information about the triggers on the ‘Students’ table – the name of the triggers, type of the triggers etc.
Language(s): MS SQL Server
Code:

sp_helptrigger Students, ‘UPDATE’;
Output:
trigger_name trigger_owner isupdate isdelete isinsert
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
(0 row(s) affected)
Explanation:
If we specify the type of the trigger, we only get the information about that type of trigger.
Language(s): MS SQL Server
Code:
sp_helptext trigAddStudentsNew;
Output:
CREATE TRIGGER trigAddStudents
ON Students
FOR INSERT
AS
PRINT ‘THE TRIGGER IS CHANGED.’
Explanation:
Here the trigger text is displayed.

Deleting a Trigger
Syntax:

DROP TRIGGER trigger1, …
trigger1
Is the name of the trigger(s) to be removed.
If a trigger is deleted, the table on which the trigger was created and the data in the table is not in any way affected. However, if the table is deleted, any trigger associated with the table is also deleted.
Examples
Code:
DROP TRIGGER trigAddStudentsNew;
Output:
The command(s) completed successfully.
Explanation:
Here the trigger, trigAddStudentsNew, is deleted from the database.

 

Views in Sql Server

A view, or virtual table, can be defined as an alternate way of collecting data from one or more tables in a database. Thus, the view consists of rows and columns just like a normal table that is generated dynamically when a query is executed. It, however, does not actually exist as a stored set of data in the database.

Views are useful for the following purposes:
o To restrict a user to specific rows in a table.
o To restrict a user to specific columns.
o To join columns from multiple tables so that they appear to be a single table.
o To get aggregate information instead of supplying details.
Views are generally used to:
Focus on Specific Data
Views allow users to focus only on data that interests them, rather than viewing all of the data in the table. Thus it can be said that views provide a security mechanism for the data in the table.

Simplify Data Manipulation
Views also help in simplifying the data manipulation. Queries, joins, and projections that are used frequently can be defined as views. This helps in keeping the user from having to specify the conditions every time an operation is performed on the data.

Customize Data
Views help users to customize data by allowing them to view the data in different ways. This helps primarily when different users use the data concurrently.

Export and Import Data
Views allow the user to export and import data to and from other applications.

Combine Partitioned Data
We can combine the results of two or more queries by using the T-SQL union operator within a view.

Creating a View
Syntax:
CREATE VIEW view_name [ ( column1, … ) ]
[ WITH ENCRYPTION ]
AS
select_statement
[ WITH CHECK OPTION ]
view_name
The name of the view to be created. It should follow the rules for identifiers.
column1, …
The name to be used for a column in a view. This is necessary only when a column is derived from an arithmetic expression, a function, or a constant, when two or more columns have the same name (caused by a join), or when a column in a view is given a name different from that of the column from which it is derived.
WITH ENCRYPTION
The optional keyword that encrypts the syscomments entries that contain the text of the CREATE VIEW statement.
AS
The keyword that preceds the select_statement parameter.
select_statement
The SELECT statement used to define the view.
WITH CHECK OPTION
Forces all data modification statements executed against the view to adhere to the criteria set within the select_statement. When a row is modified through a view, the WITH CHECK OPTION guarantees that the data remains visible through the view after the modification has been committed.
To create a view, the user must have the appropriate permissions on any tables or views referenced within its definition.
Examples
Code:
CREATE VIEW vwStudentDetails
AS
SELECT * FROM Students;
SELECT Name FROM vwStudentDetails;
Output:
The command(s) completed successfully.

Name
________________________________________
Job Mathew
Rock Feller
Harri Robins
Joe Philip
Nadia Alex
________________________________________
(5 row(s) affected)

Explanation:
The first segment of code creates the view, vwStudentDetails, which contains the Name field. The second code segment executes the view and displays the result set.
Language(s): MS SQL Server
Code:
CREATE VIEW vwStudentDetails_Filter
AS
SELECT Id, Name FROM Students
WHERE Std_Course=3;
SELECT Name FROM vwStudentDetails_Filter;
Output:
The command(s) completed successfully.

Name
________________________________________
Harri Robins
Joe Philip
________________________________________
(2 row(s) affected)

Explanation:
Views can also be used as filters to filter out only the data desired.

The following things are to be considered, while creating a view:
o Views can be created in the current database only.
o View names must adhere to the rules for naming identifiers.
o A view can reference other views.
o DEFAULT definitions, triggers, etc. cannot be associated with views, as it is used in the case of a table.
o Keywords such as ORDER BY, COMPUTE, COMPUTE BY, or INTO cannot be used in the query that defines the view.
o Indexes or full-text index definitions cannot be built on views.
o Views are not created on temporary tables and vice-versa.
o The name of every column in the view must be explicitly specified if:
o Any of the columns in the view is derived from an arithmetic expression, built-in function, or constant.
o Two or more of the columns in the view would otherwise have the same name (usually because the view definition includes a join and the columns from two or more different tables have the same name).
Modifying a View
Syntax:
ALTER VIEW view_name [ ( column1, … ) ]
[ WITH ENCRYPTION ]
AS
select_statement
[ WITH CHECK OPTION ]
A View can be modified by:
1. Deleting the existing view and creating a new one.
2. Using the ALTER VIEW T-SQL command.
Modifying a view does not change any of the dependents on the view, but the dependents are lost when a view is recreated. The parameters from the ALTER VIEW statement are the same as those for the CREATE VIEW statement.
Examples
Code:
ALTER VIEW vwStudentDetails
AS
SELECT * FROM Students
WHERE Std_Grade=1;
SELECT * FROM vwStudentDetails;
Output:
The command(s) completed successfully.

Id Name Std_Course Phone Std_Grade
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
3 Harri Robins 3 78788 1
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
(1 row(s) affected)
Explanation:
This example alters the view created earlier, and then executes it.
Renaming a View
Syntax:
sp_rename ‘view1’, ‘view1_new’
view1
Is the name of the view, which is to be renamed.
view1_new
Is the new name of the view.
Views can be renamed. The new name must follow the rules for identifiers.
Examples
Code:
sp_rename ‘vwStudentDetails’, ‘vwStudentDetails1’;
Output:
Caution: Changing any part of an object name could break scripts and stored procedures.
The object was renamed to ‘vwStudentDetails1’.
Explanation:
The above example renames the vwStudentDetails to vwStudentDetails1.
Modifying Data Through a View
Syntax:
1. Insert data using a view:
INSERT [ INTO ]
view_name
[ ( column1, … ) ]
VALUES ( value1, … )
2. Update data using a view:
UPDATE view_name
SET column1=value1, …
[ WHERE condition ]
3. Remove Data using a view:
DELETE
[ FROM ]
view_name
[ WHERE condition ]
4. To get information about a view:
sp_helptext view_name
5. Dependencies of a view:
sp_depends view_name
INTO
An optional keyword used between the INSERT keyword and the name of the view.
view_name
The name of the view.
(column1, …)
The list of columns into which values are to be inserted or updated.
VALUES
The keyword used just before the list of values.
(value1, …)
The values that are to be inserted or updated, in the columns mentioned.
SET
The keyword which specifies the list of columns to be updated.
WHERE
The keyword that specifies the conditions that limit the rows to be updated.
condition
The condition that determines which rows are to be updated or deleted.
Views can be used for data modification:
o If the view contains at least one table in the FROM clause of the view definition.
o If no aggregate functions (such as MIN, MAX, AVG, COUNT, SUM, etc.), GROUP BY, UNION, DISTINCT, or TOP clauses are used in the main query. Aggregate functions, however, can be used in a subquery.
o The view has no derived columns (columns derived using operators and functions) in the result set.
Examples
Code:
INSERT INTO vwStudentDetails1(Id, Name, Std_Course, Phone, Std_Grade)
VALUES (20, ‘Sam Simon’, 4, 98758, 2);
Output:
(1 row(s) affected)
Explanation:
Here a new row with Id=20 is inserted into the ‘Students’ table. A SELECT query can verify that the new row was added.
Language(s): MS SQL Server
Code:
UPDATE vwStudentDetails1
SET Name=’Billy James’, Phone=27751
WHERE Id=20;
SELECT * FROM Students WHERE Id=20;
Output:
(1 row(s) affected)

Id Name Std_Course Phone Std_Grade
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
20 Billy James 4 27751 2
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
(1 row(s) affected)
Explanation:
In this example, the first statement updates the data, while the second statement is used to verify the results.
Language(s): MS SQL Server
Code:
DELETE FROM vwStudentDetails WHERE Id=20;
Output:
(1 row(s) affected)
Explanation:
Here the row with Id=20 gets deleted from the ‘Students’ table. It can be verified by querying the table.
Language(s): MS SQL Server
Code:
sp_helptext vwStudentDetails;
Output:
CREATE VIEW vwStudentDetails
AS
SELECT * FROM Students;
Explanation:
The above is the definition of the view, vwStudentDetails.
Language(s): MS SQL Server
Code:
sp_depends vwStudentDetails;
Output:
In the current database, the specified object references the following:
name type updated selected column
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
dbo.Students user table no yes Id
dbo.Students user table no yes Name
dbo.Students user table no yes Std_Course
dbo.Students user table no yes Phone
dbo.Students user table no yes Std_Grade
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
Explanation:
The above example displays the dependencies of the view, vwStudentDetails.
Deleting a View
Syntax:
DROP VIEW view_name, …
view_name
The name of the view(s) to be removed.
A view can be permanently removed from a database using the DROP command.
Examples
Code:
DROP VIEW vwStudentDetails;
Output:
The command(s) completed successfully.
Explanation:
The view, vwStudentDetails, gets completely removed from the database.