View in SQL Server

What is View in SQL?

• View is a virtual table.
• it won’t occupies any memory space for the data.
• it is referencing to the other table like pointer concept in programming language.
• View definition contains only SELECT statement.
• Views simplify the user perception of the database
• View improves the data security by preventing undesired access
• View faciliates the provision of additional data independence
• View doesn’t allow ORDER BY clause in view definition.

Can You Drop a table, If it has a view?

If the view is created with SCHEMABINDING clause from Table or views then base object can’t delete otherwise you can.

Why SQL Server Doesn’t allow ORDER BY Clause in View definition?
SQL Server excludes an ORDER BY clause from a view to comply with the ANSI SQL-92 standard. Because analyzing the rationale for this standard requires a discussion of the underlying structure of the structured query language (SQL) and the mathematics upon which it is based, also if you use the ORDER BY Clause then it will lead to major performance issue, sothat by default SQL server doesn’t allow this clause in view definition.

If you try to create view with ORDER BY clause it will throw the following error.
Msg 1033, Level 15, State 1, Procedure , Line 2
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Try... Catch... Error Handling in SQL 2005

Recently I’ve posted about the topic Handling Error in SQL, in this link, I’ve explained, what is the simplest way to handle the error?  This can be implemented in all version of SQL Server.

In SQL Server 2005 offers major improvements in error handling inside T-SQL transactions. Now you can use Try… Catch Block in SQL Transactions Which is available in Programming languages Like C#, VB.NET..,
 

You can now catch -SQL and transaction abort errors using the TRY/CATCH model without any loss of the transaction context. 

The syntax is shown here:

BEGIN TRY
sql statement
END TRY
BEGIN CATCH TRAN_ABORT
    --sql statement for catching your errors
END CATCH

This will be functioning the same way of Try… Catch in Programming language.
If an error within an explicit transaction occurs inside a TRY block, control is passed to the CATCH block that immediately follows. If no error occurs, the CATCH block is completely skipped.
You can investigate the type of error that was raised and react accordingly. To do so, you can use the new ERROR functions to return error information in the CATCH block, as shown here:

BEGIN TRY
   SELECT 5/0
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH

 You can examine the value of the ERROR functions to decide what to do with the control flow of your procedure and whether to abort any transactions. When you experience a transaction abort error inside a transaction located in the TRY block, control is passed to the CATCH block. The transaction then enters a failed state in which locks are not released and persisted work is not reversed until you explicitly issue a ROLLBACK statement. You're not allowed to initiate any activity that requires opening an implicit or explicit transaction until you issue a ROLLBACK
Sometimes certain types of errors are not detected by the TRY/CATCH block, and you end up with an unhandled exception even though the error occurred inside your TRY block. If this happens, the CATCH block is not executed. This is because CATCH blocks are invoked by errors that take place in actual executing code, not by compile or syntax errors. Two examples of such errors are syntax errors and statement-level recompile errors (for example, selecting from a nonexistent table). These errors are not caught at the same execution level as the TRY block, but at the lower level of executionwhen you execute dynamic SQL or when you call a stored procedure from the TRY block. For example, if you have a syntax error inside a TRY block, you get a compile error and your CATCH block will not run:

-- Syntax error doesn't get caught
BEGIN TRY
   SELECT * * FROM XYX
END TRY
BEGIN CATCH
   PRINT  'Error'
END CATCH
GO

 

The result is an error from SQL Server, not from your CATCH block:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '*'.

Statement-level recompilation errors don't get caught by CATCH blocks, either. For example, using a nonexistent object in a SELECT statement in the TRY block forces an error from SQL Server, but your CATCH block will not execute:

-- Statement level recompilation doesn't get caught
-- Example - nonexistent object

BEGIN TRY
   SELECT * FROM XYX
END TRY
BEGIN CATCH
   PRINT 'Error'
END CATCH
GO

The result is an error from SQL Server: 

Msg 208, Level 16, State 1, Line 2
Invalid object name 'XYX'.