A View in SQL is a logical subset of data from one or more tables.
CREATE View
View consists of :
·
Header comments, Which includes author, date, purpose and
change history with example.
·
View Name.
·
Body of View.
1. T-SQL [Collection of SQL Statements]
1. T-SQL [Collection of SQL Statements]
EXAMPLE:
--
======================================================
/*
AUTHOR : Govind
CREATE DATE : 09-JAN-2015
DESCRIPTION : To get the table field information
SNO ALTERED
DATE AUTHOR DESCRIPTION
--- ------------ ------
----------------------------
1 09-APR-2015
Govind Commented the TableID
column
*/
--
======================================================
CREATE VIEW [dbo].[VW_SystemTableField]
AS
SELECT
COLUMN_NAME AS FieldName,
--OBJECT_ID(TABLE_NAME) AS TableID,
SystemTable.SystemTableID,
SystemTable.TableName,
INFORMATION_SCHEMA.COLUMNS.DATA_TYPE As
DataType
FROM INFORMATION_SCHEMA.COLUMNS WITH (NOLOCK)
INNER JOIN dbo.SystemTable WITH (NOLOCK) ON SystemTable.TableName
= INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
WHERE TABLE_NAME
<> 'SystemTableField'
AND TABLE_NAME
<> 'SystemTable'
GO
ALTER View
Example for modifying the
existing View using the ALTER VIEW command is given below.
EXAMPLE:
ALTER VIEW [dbo].[VW_SystemTableField]
SELECT View
Example for retrieving the existing
View data using the SELECT command is given below.
Selecting the View consists of:
EXAMPLE:
SELECT * FROM [dbo].[VW_SystemTableField]
DROP View
Example for deleting the existing
View using the DROP VIEW command is given below.
EXAMPLE:
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[VW_SystemTableField]'))
DROP VIEW [dbo].[SystemTableField]
GO
RENAME View
Example for renaming the existing View from VW_SystemTableField to VW_SystemTableColumn is given below.
EXAMPLE:
IF NOT EXISTS (SELECT 1 FROM sys.views WHERE object_id = OBJECT_ID(N'VW_SystemTableColumn'))
AND EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'VW_SystemTableField'))
EXEC sp_rename 'VW_SystemTableField',
'VW_SystemTableColumn'
Another way of renaming the existing View (from VW_SystemTableField to VW_SystemTableColumn) is, CREATE a new view, and then DROP the existing View
and is given below.
EXAMPLE:
CREATE VIEW [dbo].[VW_SystemTableColumn]
........
........
........
........
........
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[VW_SystemTableField]'))
DROP VIEW [dbo].[SystemTableField]
GO
Create Index On View:
The following
steps are required to create an indexed view and are critical to the successful
implementation of the indexed view:
·
Verify the SET options are correct for all existing tables that will be
referenced in the view.
·
Verify that the SET options for the session are set correctly before you
create any tables and the view.
·
Verify that the view definition is deterministic.
·
Create the view by using the WITH SCHEMABINDING
option.
·
Create the unique clustered index on the view.
EXAMPLE:
ALTER VIEW [dbo].[VW_PT_Customer]
WITH SCHEMABINDING
AS
SELECT
CustomerID,
CustomerName
FROM dbo.Customer
WHERE
CustomerName <> 'Internal
Transfer'
CREATE UNIQUE CLUSTERED INDEX [IX_PT_VW_Customer_CustomerName] ON [dbo].[VW_PT_Customer]
(
[CustomerName] ASC
)WITH
(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS
= ON)
ON [PRIMARY]
GO
NOTE: WITH (NOLOCK) Option can’t be applied for Tables while creating the
VIEW.
General Rules:
·
Follow the naming conventions.
·
Do not use spaces in the name of database objects.
·
Write neat, readable and meaningful comments wherever needed.
o
Use -- for single line comment.
o
Use /* ……*/ for block comments.
·
Do not use SQL keywords as the name of database objects, In cases
where this is necessary, surround the object name with brackets, such as [Year].
Coding:
Good database design and coding will achieve all the required
project functionality and deliver acceptable performance. It takes into quality
delivery to your customer.
·
Optimize queries
using the tools provided by SQL Server.
Use the graphical execution plan in Query Analyzer or SHOWPLAN_TEXT or SHOWPLAN_ALL commands to analyze your queries. Make sure your
queries do an "Index seek" instead of an "Index scan" or a
"Table scan." A table scan or an index scan is a highly undesirable
and should be avoided where possible.
EXAMPLE
1:
IF EXISTS (SELECT 1 FROM
dbo.VW_Action WITH (NOLOCK) WHERE ActionID = 50)
Instead Of:
IF EXISTS (SELECT COUNT(ActionID) FROM dbo.VW_Action WHERE
ActionID = 50)
EXAMPLE
2:
Consider the following query to
find the second highest offer MaxManPayAmount from the Operator table:
SELECT MIN(MaxManPayAmount) FROM Operator WHERE OperatorID IN
(SELECT
TOP 2 OperatorID FROM Operator ORDER BY MaxManPayAmount Desc)
The same query can be re-written
using a derived table, as shown below, and it performs generally as fast as the
above query:
SELECT MIN(opr.MaxManPayAmount) FROM
(SELECT
TOP 2 MaxManPayAmount FROM dbo.Operator WITH (NOLOCK) ORDER BY
MaxManPayAmount DESC) AS opr
·
Do not use SELECT *, instead practice writing required column names
after the SELECT statement.
·
Do not use SQL keywords as the name of database objects, In cases
where this is necessary, surround the object name with brackets, such as [Year].
·
Use table WITH
(NOLOCK) command for SELECT statement which avoids a
deadlock occurrence.
·
Avoid using <> as
a comparison operator.
o
Use ID IN (1,3,4,5)
instead of ID <> 2.
·
Fully qualify tables and column names.
o
Prefix table names with the owner name. This improves readability
and avoids unnecessary confusion. Microsoft SQL Server Books Online states that
qualifying table names with owner names help in execution plan reuse, further
boosting performance.
Example: dbo.VW_FeatureAction
·
Use ANSI 92 Joins instead
of old style joins. This improves readability and avoids unnecessary confusion.
False code:
SELECT * FROM dbo.VW_FeatureAction, Feature
WHERE
Feature.FeatureID =
VW_FeatureAction.FeatureID
True code:
SELECT FeatureAction.FeatureID, Feature.FeatureName FROM dbo.VW_FeatureAction
FeatureAction
INNER JOIN dbo.Feature WITH (NOLOCK) ON Feature.FeatureID = FeatureAction.FeatureID
·
Avoid the use of cross
joins, if possible.
·
Do not use column numbers in the ORDER BY clause as it is not allowed.
·
Create VIEW as much as
possible with business logic on the same database for accessing data.
o
Do prefix stored procedures with “VW_”. (user defined view)
·
Prefix table names with the owner name.
o
Example: dbo.VW_Action instead
of VW_Action
·
USE
UNION and UNION ALL for combining two or more tables data for returning a
single result set form view to avoid trips from the
application server to SQL server.
Query the list of View
EXAMPLE 1:
-- View and used
columns
select t.TABLE_NAME, c.COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS c
inner join INFORMATION_SCHEMA.TABLES t on t.TABLE_NAME = c.TABLE_NAME
where t.TABLE_TYPE = 'VIEW'
and c.COLUMN_NAME
in ('TaxId', 'BnkAcct', 'BnkRout')
order by t.TABLE_NAME, c.COLUMN_NAME
EXAMPLE 2:
-- View and its
referenced tabled columns
select
t.TABLE_NAME,
c.COLUMN_NAME,
Refer.referencing
AS [USED_TABLE]
from INFORMATION_SCHEMA.COLUMNS c
inner join INFORMATION_SCHEMA.TABLES t on t.TABLE_NAME = c.TABLE_NAME
inner join
(
select
coalesce(Referenced_server_name+'.','')+ --possible server name if cross-server
coalesce(referenced_database_name+'.','')+ --possible database name if cross-database
coalesce(referenced_schema_name+'.','')+ --likely schema name
coalesce(referenced_entity_name,'') + --very likely entity name
coalesce('.'+col_name(referenced_ID,referenced_minor_id),'') AS [referencing],
coalesce(object_schema_name(Referencing_ID)+'.','')+ --likely schema name
object_name(Referencing_ID)+ --definite entity name
coalesce('.'+col_name(referencing_ID,referencing_minor_id),'') AS [referenced]
FROM sys.sql_expression_dependencies
) Refer ON Refer.[referenced]
= 'dbo.'+ t.TABLE_NAME
where t.TABLE_TYPE = 'VIEW'
and c.COLUMN_NAME
in ('TaxId', 'BnkAcct', 'BnkRout')
and Refer.referencing in ('Chk', 'Vnd', 'Hst')
order by t.TABLE_NAME, c.COLUMN_NAME
Check the existence of View
EXAMPLE:
if OBJECT_ID(N'VW_SystemTableColumn')
IS NULL
print 'object
does not exist'
else
print 'object
exists'
No comments:
Post a Comment