Some useful Interview questions

What is materialized view?We can name it materialized view in different ways like Schema Binding view, Structure binding view and materilizaed view. The main use of the view is it acts as a table like hold the schema. For a example normal view is a select query written by combining multiple tables. If we want to improve the performance there is only a way that we can alter the statements in better way. But if consider materialized view, one can create indexes apart from altering a query for better performance. As you understood indexes will give more performance compares with any other option available in sql server.

What is the diff between inner join and union?Inner join retrieves the matched data from 2 or more tables where as union will retrieve distinct data from 2 or more tables. Join is like horizontal result, Union is like vertical result.

What is the diff between master database and resource database? If resource database was corrupted sql server will work or not? How you can repair that?
Master and Resource both are system dbs in sql server. But master is the primary database which will contain all security structures, objects structures and other user database dependent information where as resource will have copy of the users database system tables which helps at the time of upgradation or degradation. The resource purpose is intended in Sql server 2005 is only for Upgradation or degradation of the versions or service packs. If resource is not available, then also Sql server will not work as some dependent services are working based on resource db structured in Sql Server 2005. Eg : Alert Service, Performance related services.

If i need to give permission per column in table how you can give and tell me process?--
Step 1:
Create table NB_Perm (sl int, name varchar(10))

--STep 2 :
Create role NB_role

--Step 3 :
grant select (sl) on nb_perm to NB_role
deny select(sl) on nb_perm to NB_Role

Make your users member of this role.

What is use of built/administrator in sql server?It is a Windows Group defaultly created with installation of SQL Server. The role defaultly assigned is Sysadmin. If we want this group to be there in Sql server we can keep it and create the users under this group. For creating users refer Administrator Tools – Computer Management - Users and Groups.

What is the diff between having clause and where clause?‘Having’ clause can be used when the query consists aggregate functions like count, avg …..
Eg : Select id,count(id) from table having count(id)>5
‘Where’ clause can be used to filter the data in any statement irrespective of conditions.

How you can know who was last using the query?In earlier versions we can find by implementing the trigger. But in Sql server 2008 new concept auditing is available. With help of this we can find out.

What is the diff between physical file and logical file and which one you refer at the time shrink the file?Logical and Physical both are database files where logical file available inside the database structure and physical file available at the destination drive. Logical and physical both are important at the time of backup, restore or any kind of database operation. As you requested I refer the logical file only at the time of shrinking or restoring the db.

What is covering Index and can you give me one example?Covering index is an index which helps to reduce the index size in a database. Like a index will improve the performance and as a disadvantage it occupies some space in your db. Suppose if you want to create index by using more columns then you can refer convering index as best solution. Addition to decreasing the table space it gives good performance also as running the indexes only it requires on the columns. The suggestions from my side is create all int kind of data columns in index list and varchar kind of column in include list.
Eg : Create (cluster/nonclustered) index IndexName on table (main columns) include (other columns)


What is live lock?A live lock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A live lock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely

Diff between live lock and dead lockDeadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user's process.

A live lock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A Live lock also occurs when read transactions monopolize a table or page, forcing a write
transaction to wait indefinitely

Lock escalationLock escalation is the process of escalating a significant number of row level locks to a single table lock. This is trick performed by SQL Server as a way of conserving memory and improving performance. The problem is it works well for smaller systems but can easily throttle performance on larger systems when the activity reaches a certain threshold.

How can rebuild master dbProcess :
1) Shutdown the SQL server and its services.
2) Goto Program Files\Microsoft SQL Server\80\Tools\Binn directory.
3) Open Rebuildm.exe
4) In the Browse for Folder dialog box, select the \Data folder on the SQL Server 2000 compact disc or in the shared network directory from which SQL Server 2000 was installed, and then click OK.
5) Change collation settings if required or leave it default.
6) In the Rebuild Master dialog box, click Rebuild to start the process.
DTS to SSIS migration
We cannot migrate DTS packages from 2000 to 2005 (or) 2008. If it is simple package then we can use it as it is. If it consists vbcode and all it will not convert instead we need to rewritten the package using SSIS. Because SSIS uses .net code.

Types of replication agentsReplication Snapshot Agent:
Replication Log Reader Agent:
Replication Distribution Agent:
Replication Merge Agent:
Replication Queue Reader Agent:

Public/private networkPublic vs. private networks
Computers that are connected to each other create a network. These networks are often configured with "public" Internet Protocol (IP) addresses -- that is, the devices on the network are "visible" to devices outside the network (from the Internet or another network). Networks can also be configured as "private" -- meaning that devices outside the network cannot "see" or communicate directly to them.
Computers on a public network have the advantage (and disadvantage) that they are completely visible to the Internet. As such, they have no boundaries between themselves and the rest of the Internet community. This advantage oftentimes becomes a distinct disadvantage since this visibility can lead to a computer vulnerability exploit -- a.k.a., a "hack" -- if the devices on the public network are not properly secured.

The difference between Master and Resource databases

The difference between Master and Resource database

Mster db is the mother database for all other databases like it consits of other database information, configurations, file locations, security context and etc. whereas Resource db is a database addition to master consists of database schema and stored procedures which requires to run databases in current instance. Resource db mainly introduced for Upgradation or rollback your installations in Sql server 2005.

Some differences:

Master :
1. Master will have data specific to your instance.

2. The database can be found in your sqlserver installation data folder with the name of master.mdf.

3. For any maintenance activity you need to make the db in single user mode.



Resource :
1. Resource will have schema and stored procedures to run your instance.

2. It is introduced in Sql server 2005 for upgradation purpose. In sql server 2000 if you want to upgrade to a new service pack, you would need to run many log scripts that drop and create system scripts. It is too long process and much time consume. In Sql server 2005 if you install service pack or quick fix a copy of resource database will overrite the old database. With help of this resource db you can easily upgrade or rollback your service packs.


3. The database can be found in your sqlserver installation data folder with the name of mssqlsystemresource.mdf.

4. It is a hidden database, you can't findout it in sysdatabases table. By using serverproperty we can view the information about the resource db.

Query to findout JOBs information for a period of time

I hope this requirement is very common in DBA work life, as DBA needs to findout how the JOBs are performing currently and for a long time. I too faced the requirement. I have searched most of the pages from google and I couldn't found the solution. Finally I written the following query spending almost 3 hours.

Hopefully it will useful for all SQL Server DBAs.


Syntax :
Select b.Name JobName, a.step_name,a.run_date,run_time,
case when len(a.run_time) = 5 then convert(varchar,left(a.run_time,1)) + ':' + convert(varchar,left(right(a.run_time,4),2)) + ':' + convert(varchar,right (a.run_time,2))
when len(a.run_time) = 4 then '00' + ':'+convert(varchar,left(a.run_time,2)) + ':'+convert(varchar,right(a.run_time,2))
when len(a.run_time) = 3 then '00:0' + convert(varchar,left(a.run_time,1)) + ':'+convert(varchar,right(a.run_time,2))
when len(a.run_time) = 2 then '00:00:' + convert(varchar,left(a.run_time,2))
when len(a.run_time) = 1 then '00:00:0' + convert(varchar,left(a.run_time,1))
else
convert(varchar,left(a.run_time,2)) + ':' + convert(varchar,left(right(a.run_time,4),2)) + ':' + convert(varchar,right (a.run_time,2)) End run_time,
run_duration,
ISNULL(SUBSTRING(CONVERT(varchar(7),run_duration+1000000),2,2) + ':'
+ SUBSTRING(CONVERT(varchar(7),run_duration+1000000),4,2) + ':'
+ SUBSTRING(CONVERT(varchar(7),run_duration+1000000),6,2),'') AS [Duration], a.message
from sysjobs b inner join sysjobhistory a on a.job_id = b.job_id
where b.name like '%Reindex%' AND step_name not like 'Notification'
and step_name not like '%Job Outcome%' order by b.Name


For the above query you can add filter conditions as per your requirements. By getting the result of above query you can have an idea of how the jobs are involved and making the analysis you can increase the performance of the server.

Query to findout list of Objects count from all databases in SQL Server :

This is very useful query on DBA daily work environment. I have struggled to findout the information before. Finally I developed following query to get the information.

Select 'select ('''+name+''') Database_name,
(Select count(*) from '+name+'.dbo.sysobjects where xtype = ''u'') Table_Count,
(Select count(*) from '+name+'.dbo.sysobjects where xtype = ''v'') View_Count,
(Select count(*) from '+name+'.dbo.sysobjects where xtype in (''fn'', ''tn'')) func_Count,
(Select count(*) from '+name+'.dbo.sysobjects where xtype = ''p'') proc_Count,
(Select count(*) from '+name+'.dbo.sysobjects where xtype = ''tr'') Trig_Count union all'
from sysdatabases where dbid > 4


Instructions :

* you need to take the result script of above query and execute in other window. And you need to remove 'UNION ALL" from end statement.
* dbid > 4 = it will not show for sysdatabases



Result Query :

select ('TEST') Database_name, (Select count(*) from TEST.dbo.sysobjects where xtype = 'u') Table_Count, (Select count(*) from TEST.dbo.sysobjects where xtype = 'v') View_Count, (Select count(*) from TEST.dbo.sysobjects where xtype in ('fn', 'tn')) func_Count, (Select count(*) from TEST.dbo.sysobjects where xtype = 'p') proc_Count, (Select count(*) from TEST.dbo.sysobjects where xtype = 'tr') Trig_Count

Statistics in Synchronization and Asynchronization mode to gain the performance.

In SQL Server we have Statistics to improve the performance on tables. Following are some of the concepts about Statistics in Synchronization/Asynchronization mode to gain the performance from tables.

1. Statistics Asynchronization mode was introduced in SQL Server 2005. In earlier versions only synchronizations mode was available. But the default level is synchronization only.

2. Synchronization /Asynchronization options are at database level, not individual object level. There is single option we can enable or disable this for entire database. We can do this by using alter database statement.
Syntax : ALTER DATABASE dbName SET AUTO_UPDATE_STATISTICS_ASYNC ON
ALTER DATABASE dbName SET AUTO_UPDATE_STATISTICS_ASYNC OFF

3. To checkup this option on existing databases.
Syntax : Select name,is_auto_update_stats_async_on from sys.databases

1 = enabled
0 = disabled

When the setting is off and a statistics update is initiated due to out-of-date statistics in the execution plan, the query must wait until the statistics update is complete before compiling and then returning the result set. When the setting is on, the query does not need to wait as the statistics update are handled by a background process. Mainly it is using to improve the performance on the tables.

So, prior to utilize the statistics aynchronous option at database level, the following process should do at object level.
1. Create statistics on indexed columns. It is suggestable that statistics must create on composite indexed columns.

2. All the created statistics must be updated regularly. When we update the statistics on regular basis, we can gain the performance from tables.

3. This options will work on tables which are having statistics and updating regularly will improve the performance compare to synchronization mode.

Class 8 - Functions in SQL Server

FUNCTIONS
What is function ?
A function in SQL Server can be defined primary as 2 types. User Defined Functions which is creating by user based on the requirement and System Defined Functions which was already created by Microsoft and provided for ready use.

These can be classified in other ways also :

The Transact-SQL programming language provides three types of functions:
• Rowset functions
Can be used like table references in an SQL statement.
• Aggregate functions
Operate on a collection of values but return a single, summarizing value.
• Scalar functions
Operate on a single value and then return a single value.

Deterministic and Nondeterministic Functions
All functions are deterministic or nondeterministic:
• Deterministic functions always return the same result any time they are called with a specific set of input values.
• Nondeterministic functions may return different results each time they are called with a specific set of input values.


USER DEFINED FUNCTIONS : When we come to User Defined Function again there are 3 types.
1) Scalar Function
2) Table Valued Function
3) Multi Statement Function

Scalar Functions
Scalare functions always returns single resultant value.
Syntax :
CREATE FUNCTION [ owner. ] fn_name ( [ { @parameter [ AS ] data_type }[ ,...n ] ] )
RETURNS data_type
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
Eg :
create function fn_studage (@id int) returns int
as
begin
declare @studage int
select @studage = datediff(year,dob,getdate()) from students where id = @id
return @studage
end

Inline Table-Valued Functions
Inline Table-Valued Functions return a resultset, as opposed to a single scalar value. A table valued function specifies the TABLE keyword in its RETURN clause. Its basic form is :
Syntax :
CREATE FUNCTION [ owner_name. ] fn_name ( [ { @parameter [ AS ] type } [ ,...n ] ])
RETURNS TABLE
[ AS ]
RETURN [ ( ] select-statement [ ) ]

An Inline Table-Valued Function created by this command:
CREATE FUNCTION datesales (@deadline as datetime)
RETURNS TABLE
AS
RETURN ( SELECT *
FROM sales
WHERE ord_date > @deadline)

and called by this sequence:
USE PUBS
GO
select * from datesales('09/13/1994')
will yield the following table:
stor_id ord_num ord_date qty payterms title_id
6380 6871 09/14/94 5 Net 60 BU1032
7067 D4482 09/14/94 10 Net 60 PS2091
7131 N914008 09/14/94 20 Net 30 PS2091
7131 N914014 09/14/94 25 Net 30 MC3021
8042 423LL922 09/14/94 15 ON invoice MC3021
8042 423LL930 09/14/94 10 ON invoice BU1032

Multi-statement Table-Valued Function
The final type of UDF is the Multi-statement Table-Valued Function. This UDS also returns a resultset, like the Inline variety UDF, but with a much more powerful result. The Multi-statement UFD can actually create a temporary table, specifying the fields, their type and characteristics. Now the scope of that temporary table is limited to the UDF in which it was defined, so when the UDF ends, the temporary table evaporates too. However, this can be a tremendously powerful technique to obtain or manipulate data.

The general form for the Multi-statement Table-Valued Function is:
CREATE FUNCTION [ owner_name. ] fn_name ( [ { @parameter [AS] type } [ ,...n ] ] )
RETURNS @return_variable TABLE < table_type_definition >
[ AS ]
BEGIN
function_body
RETURN
END
The following creates the datesales2 UDF:
CREATE FUNCTION datesales2 (@deadline datetime) RETURNS @table TABLE (stor_id varchar(6) null, ord_num varchar(8) null, ord_date datetime null, qty int, payterms varchar(20), title_id varchar(6))
AS
BEGIN
INSERT @table
SELECT *
FROM sales
WHERE ord_date > @deadline
RETURN
END
When the function is called by this sequence,
USE PUBS
GO
select * from datesales2('09/13/1994')
The following resultset is displayed and available. (It is the same set displayed in the Inline UDF, but created with the temporary table.)
stor_id ord_num ord_date qty payterms title_id
6380 6871 09/14/94 5 Net 60 BU1032
7067 D4482 09/14/94 10 Net 60 PS2091
7131 N914008 09/14/94 20 Net 30 PS2091
7131 N914014 09/14/94 25 Net 30 MC3021
8042 423LL922 09/14/94 15 ON invoice MC3021
8042 423LL930 09/14/94 10 ON invoice BU1032
Now these temporary tables by me combined, joined, with virtually any number created within the Multi-Tabled UDF, giving the user a very powerful tool for calculating and presenting data from the server in a way that truly leverages the effectiveness of writing code in the native language of SQL Server.

SYSTEM DEFINED FUNCTIONS :

FUNCTION EXPLANATION :

Function category Explanation
Configuration Functions : Returns information about the current configuration.
Cursor Functions : Returns information about cursors.
Date and Time Functions : Performs an operation on a date and time input value and returns either a string, numeric, or date and time value.
Mathematical Functions : Performs a calculation based on input values provided as parameters to the function, and returns a numeric value.
Metadata Functions : Returns information about the database and database objects.
Security Functions : Returns information about users and roles.
String Functions : Performs an operation on a string (char or varchar) input value and returns a string or numeric value.
System Functions : Performs operations and returns information about values, objects, and settings in Microsoft® SQL Server™.
System Statistical Functions : Returns statistical information about the system.
Text and Image Functions : Performs an operation on a text or image input values or column, and returns information about the value.


Configuration Functions :
@@REMSERVERReturns the name of the remote Microsoft® SQL Server™ database server as it appears in the login record.
Syntax
@@REMSERVER
Example :
CREATE PROCEDURE check_server
AS
SELECT @@REMSERVER

@@LANGUAGE
@@SERVERNAME

@@SERVICENAME
Example
SELECT @@SERVICENAME
Here is the result set:
------------------------------
MSSQLServer

@@SPID
@@MAX_CONNECTIONS
The actual number of user connections allowed also depends on the version of SQL Server installed and the limitations of your application(s) and hardware.
To reconfigure SQL Server for fewer connections, use sp_configure.
Examples
SELECT @@MAX_CONNECTIONS

@@VERSION

@@NESTLEVEL
Returns the nesting level of the current stored procedure execution (initially 0).

@@DATEFIRST
Returns the current value of the SET DATEFIRST parameter, which indicates the specified first day of each week: 1 for Monday, 2 for Wednesday, and so on through 7 for Sunday.
Syntax
@@DATEFIRST
Examples
SET DATEFIRST 5
SELECT @@DATEFIRST AS '1st Day', DATEPART(dw, GETDATE()) AS 'Today'
Here is the result set. Counting from Friday, today (Saturday) is day 2.
1st Day Today
---------------- --------------
5 2



2. Cursor Functions
@@CURSOR_ROWS
@@CURSOR_STATUS
@@FETCH_STATUS

3. Date and Time Functions
Datepart Abbreviations
Year yy, yyyy
Quarter qq, q
Month mm, m
dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
minute mi, n
second ss, s
millisecond ms

DATEADD
Eg : SELECT DATEADD(day, 21, pubdate) AS timeframe fROM titles

DATEDIFF
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles

DATENAMESELECT DATENAME(month, getdate()) AS 'Month Name' = February

DATEPARTSELECT DATEPART(month, GETDATE()) AS 'Month Number' = 2

DAY SELECT DAY('03/12/1998') AS 'Day Number'

MONTHSELECT MONTH('03/12/1998') AS 'MONTH Number'

YEARSELECT YEAR('03/12/1998') AS 'YEAR Number'

GETDATE()SELECT GETDATE()

4. Mathematical Functions
CEILING Returns the smallest integer greater than, or equal to, the given numeric expression.
Examples
This example shows positive numeric, negative, and zero values with the CEILING function.
SELECT CEILING($123.45), CEILING($-123.45), CEILING($0.0)
GO
Here is the result set:
--------- --------- -------------------------
124.00 -123.00 0.00

FLOORReturns the largest integer less than or equal to the given numeric expression.
Examples
This example shows positive numeric, negative numeric, and currency values with the FLOOR function.
SELECT FLOOR(123.45), FLOOR(-123.45), FLOOR($123.45)
The result is the integer portion of the calculated value in the same data type as numeric_expression.
--------- --------- -----------
123 -124 123.0000

TRIGNOMETRY :
SIN
COS
TAN
COT

5. Metadata Functions
@@PROCIDReturns the stored procedure identifier (ID) of the current procedure
SELECT @@PROCID AS 'ProcID'

OBJECTPROPERTYIF OBJECTPROPERTY ( object_id('authors'),'ISTABLE') = 2
print 'Authors is a table'

DB_IDReturns the database identification (ID) number.

DB_NAMEReturns the database name.
Ex :
SELECT dbid, DB_NAME(dbid) AS DB_NAME
FROM sysdatabases

OBJECT_ID


6. Security Functions
USER
USER_ID
SUSER_NAME : EG :select SUSER_NAME(0X01)
SUSER_SID : EG : SELECT SUSER_SID('sa')

7. String Functions
LTRIMReturns a character expression after removing leading blanks.
select ltrim(' venkat')

RTRIM
Returns a character string after truncating all trailing blanks.

UPPERReturns a character expression with lowercase character data converted to uppercase.
select upper('venkat')

LOWERReturns a character expression with UPPER character data converted to lowercase.

LENReturns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.

LEFTReturns the part of a character string starting at a specified number of characters from the left.
Ex : left(‘venkat’,3)

RIGHTReturns the part of a character string starting at a specified number of characters from the right.
Ex : Right(‘venkat’,3)

REPLACEReplaces all occurrences of the second given string expression in the first string expression with a third expression.
Examples
This example replaces the string cde in abcdefghi with xxx.
SELECT REPLACE('abcdefghicde','cde','xxx')


8. System Functions
CASE
CAST and CONVERTExplicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality.

CAST : SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales
FROM titles
WHERE CAST(ytd_sales AS char(20)) LIKE '3%'

CONVERT : SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales
FROM titles
WHERE CONVERT(char(20), ytd_sales) LIKE '3%'

ISNULLCOALESCE
CURRENT_USER
HOST_ID
HOST_NAME
Eg : CREATE TABLE Orders
(OrderID INT PRIMARY KEY,
Workstation NCHAR(30) NOT NULL DEFAULT HOST_NAME())

@@IDENTITY
ISNUMERIC
@@row_count
SCOPE_IDENTITY
SERVERPROPERTY
eg : SELECT SERVERPROPERTY('servername'),
SELECT SERVERPROPERTY('productlevel')

SYSTEM_USER

@@TRANCOUNT

USER_NAME

System Statistical Functions
@@CONNECTIONSReturns the number of connections, or attempted connections, since Microsoft® SQL Server™ was last started.

@@PACK_RECEIVEDReturns the number of input packets read from the network by Microsoft® SQL Server™ since last started.

@@CPU_BUSYReturns the time in milliseconds (based on the resolution of the system timer) that the CPU has spent working since Microsoft® SQL Server™ was last started.

@@PACK_SENTReturns the number of output packets written to the network by Microsoft® SQL Server™ since last started.

@@TOTAL_ERRORSReturns the number of disk read/write errors encountered by Microsoft® SQL Server™ since last started.

9. Text and Image FunctionsPATINDEX
TEXTPTR
TEXTVALID

Class 7 - Views in SQL Server

VIEW IN SQL SERVER 2000
Views are nothing but saved SQL queries. Suppose you have written a query by using multiple SQL statements to view the data. But it may require that watching the data from these tables whenever required. For this you don’t need to write all these queries every time, instead you can save it as a view. View doesn’t contain any data, it is just a query.

CREATE A VIEW
This example creates a view with a simple SELECT statement. A simple view is helpful when a combination of columns is queried frequently.
CREATE VIEW titles_view
AS
SELECT title, type, price, pubdate
FROM titles
GO

JOINS :
Views can be written by using multiple tables with help of joins. There are different joins available in SQL Server. As per the requirement we can choose join to use in the queries for view.

INNER
LEFT OUTER
RIGHT OUTER
FULL
CROSS
SELF
CARTISAN

Eg : Create view ABCD_View
AS
Select a.Col1, a.col2, b.col1, b.col2
from Table_A inner join Table_B
on a.Col1 = b.Col2


Some Arguments about Views in Sql Server 2000 : •
You can create views only in the current database. However, the tables and views referenced by the new view can exist in other databases or even other servers if the view is defined using distributed queries.
• A view can reference a maximum of 1,024 columns.
• A View can be used as a security mechanism like we can given permissions for the users also.
• View names must follow the rules for identifiers and must be unique for each user. Additionally, the name must not be the same as any tables owned by that user.
• You can build views on other views and on procedures that reference views. Microsoft® SQL Server™ 2000 allows views to be nested up to 32 levels.
• You cannot associate rules or DEFAULT definitions with views.
• You cannot associate AFTER triggers with views, only INSTEAD OF triggers.
• The query defining the view cannot include the ORDER BY, COMPUTE, or COMPUTE BY clauses or the INTO keyword.
• You cannot define full-text index definitions on views.
• You cannot create temporary views, and you cannot create views on temporary tables.
• Views or tables participating in a view created with the SCHEMABINDING clause cannot be dropped, unless the view is dropped or changed so that it no longer has schema binding. In addition, ALTER TABLE statements on tables that participate in views having schema binding will fail if these statements affect the view definition.
• When a view is created, the name of the view is stored in the sysobjects table.
• If the new table (or view) structure changes, then the view must be dropped and recreated.

View can be createD by using following options for security and performance purposes.

WITH CHECK OPTIONForces all data modification statements executed against the view to adhere to the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION ensures the data remains visible through the view after the modification is committed.
Eg :
CREATE VIEW CAonly
AS
SELECT au_lname, au_fname, city, state
FROM authors
WHERE state = 'CA'
WITH CHECK OPTIONGO

WITH ENCRYPTIONIndicates that SQL Server encrypts the system table columns containing the text of the CREATE VIEW statement. Using WITH ENCRYPTION prevents the view from being published as part of SQL Server replication.
Eg :
CREATE VIEW CAonly
AS
SELECT au_lname, au_fname, city, state
FROM authors
WHERE state = 'CA'
WITH ENCRYPTION
GO

SCHEMABINDINGBinds the view to the schema. When SCHEMABINDING is specified, the select_statement must include the two-part names (owner.object) of tables, views, or user-defined functions referenced.
Views or tables participating in a view created with the schema binding clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, SQL Server raises an error. In addition, ALTER TABLE statements on tables that participate in views having schema binding will fail if these statements affect the view definition.

Use built-in functions within a view : This example shows a view definition that includes a built-in function. When you use functions, the derived column must include a column name in the CREATE VIEW statement.
CREATE VIEW categories (category, average_price)
AS
SELECT type, AVG(price)
FROM titles
GROUP BY type
GO