Sql Server Views

Sql Server Views

Creates a virtual table that represents the data in one or more tables in an alternative way. CREATE VIEW must be the first statement in a query batch.

schema_name

Is the name of the schema to which the view belongs.

view_name

Is the name of the view.

Column

Is the name to be used for a column in a view. A column name is required only when a column is derived from an arithmetic expression, a function, or a constant.

AS

Specifies the actions the view is to perform.

select_statement

Is the SELECT statement that defines the view. The statement can use more than one table and other views. In an indexed view definition, the SELECT statement must be a single table statement or a multitable JOIN with optional aggregation.

The SELECT clauses in a view definition cannot include the following:

• COMPUTE or COMPUTE BY clauses
• An ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement
• The INTO keyword
• The OPTION clause
• A reference to a temporary table or a table variable.

Syntax

CREATE VIEW view_name
[(column_name[,column_name]….)]
[WITH ENCRYPTION]
AS select_statement [WITH CHECK OPTION]

CREATE VIEW hiredate_view

AS

SELECT p.FirstName, p.LastName, e.BusinessEntityID, e.HireDate

FROM HumanResources.Employee e

JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID ;

GO

ENCRYPTION

Encrypts the entries in sys.syscomments that contain the text of the CREATE VIEW statement. Using WITH ENCRYPTION prevents the view from being published as part of SQL Server replication.

CREATE VIEW Purchasing.PurchaseOrderReject

WITH ENCRYPTION

AS

SELECT PurchaseOrderID, ReceivedQty, RejectedQty,

RejectedQty / ReceivedQty AS RejectRatio, DueDate

FROM Purchasing.PurchaseOrderDetail

WHERE RejectedQty / ReceivedQty > 0

AND DueDate > CONVERT(DATETIME,'20010630',101) ;

GO

SCHEMABINDING

Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the select_statement must include the two-part names - (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.

If a view is not created with the SCHEMABINDING clause, sp_refreshview should be run when changes are made to the objects underlying the view that affect the definition of the view. Otherwise, the view might produce unexpected results when it is queried.

CREATE VIEW vwSample
With SCHEMABINDING
As
SELECT
CustomerID,
CompanyName,
ContactName

FROM DBO.CUSTOMERS -- Two part name [ownername.objectname]
GO

When a view is created, information about the view is stored in the following catalog views: sys.views, sys.columns, and sys.sql_expression_dependencies. The text of the CREATE VIEW statement is stored in the sys.sql_modules catalog view.

Updatable Views

You can modify the data of an underlying base table through a view, as long as the following conditions are true:

• Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.

• The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as through the following:
  
  
• An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP.

• A computation. The column cannot be computed from an expression that uses other columns. Columns that are formed by using the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation and are also not updatable.

The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses.

• TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.
  
  

INSTEAD OF Triggers

INSTEAD OF triggers can be created on a view to make a view updatable. The INSTEAD OF trigger is executed instead of the data modification statement on which the trigger is defined. This trigger lets the user specify the set of actions that must happen to process the data modification statement. Therefore, if an INSTEAD OF trigger exists for a view on a specific data modification statement (INSERT, UPDATE, or DELETE), the corresponding view is updatable through that statement. For more information about INSTEAD OF triggers

Partitioned Views

If the view is a partitioned view, the view is updatable, subject to certain restrictions. When it is needed, the Database Engine distinguishes local partitioned views as the views in which all participating tables and the view are on the same instance of SQL Server, and distributed partitioned views as the views in which at least one of the tables in the view resides on a different or remote server.

A partitioned view is a view defined by a UNION ALL of member tables structured in the same way, but stored separately as multiple tables in either the same instance of SQL Server or in a group of autonomous instances of SQL Server servers, called federated database servers.

--Partitioned view as defined on Server1

CREATE VIEW Customers

AS

--Select from local member table.

SELECT *

FROM CompanyData.dbo.Customers_33

UNION ALL

--Select from member table on Server2.

SELECT *

FROM Server2.CompanyData.dbo.Customers_66

UNION ALL

--Select from mmeber table on Server3.

SELECT *

FROM Server3.CompanyData.dbo.Customers_99

Delete duplicate records from a table

If the use enters duplicate records in a table, a delete statement with a where clause will delete all the records that satisfy the where condition specified in the delete statement.

In that case delete statement is executed as:

DELETE FROM tableName WHERE columnName IN (value1,value2,value3)

Here all the records with value1, value2, value3 will be deleted.

What is required is that we have to retain one record and delete all other duplicate records. To retain one records, the where clause must be defined on the column that uniquely identified the record.

Lets do it  by example.

Suppose we have a table called tblDocs with 3 columns


CREATE TABLE tblDocs(
 id INT IDENTITY(1, 1)
,docId      INT
,orgId       INT
)

Here we insert some values in tblDocs


INSERT INTO tblDocs VALUES (1, 1)
INSERT INTO tblDocs VALUES (1, 2)
INSERT INTO tblDocs VALUES (1, 3)
INSERT INTO tblDocs VALUES (2, 1)
INSERT INTO tblDocs VALUES (3, 2)
INSERT INTO tblDocs VALUES (2, 2)
INSERT INTO tblDocs VALUES (1, 1)    --Duplicate record
INSERT INTO tblDocs VALUES (1, 3)    --Duplicate record
INSERT INTO tblDocs VALUES (4, 3)

Now we have result area of tblDocs as below

Now here if we see then record with id 7 & 8 are duplicate with id 1 & 3. Now suppose we have a requirement where we need to put the unique index then we need to delete duplicate record. Here we can either keep id 1 & 3 or id 7 & 8, depends upon MIN or MAX in the below query. We can achieve the requirement with the help of below query:

DELETE FROM tblDocs
WHERE id NOT IN(
SELECT MIN(id) FROM tblDocs GROUP BY docId, orgId
)

There might be other way to fulfill this requirement. This one I used during my project. Comments and other solutions are always welcome.

SQL Server Table Hints

Index hints (a form of a table hint) are used to specify which index/s you want used when a query runs. When you specify one or more index, the Query Optimizer will use them when creating the execution plan for your query.

The syntax for a table hint is:

SELECT column_list FROM table_name WITH (INDEX (index_name) [, ...])

Here's an example:

SELECT memberID, fname, lname FROM members WITH (INDEX(memberID_index))

While you can use an index ID instead of the index name, this is not a good idea. This is because index IDs can change if you drop and recreate the indexes differently than when you first created them.

In most cases, the SQL Server Query Optimizer will correctly evaluate a query and run it as optimally as possible. But on occasion the Query Optimizer will fail, producing a less than optimal execution plan, and query performance will suffer because of it. When you identify such a query, you can override the Query Optimizer using what is called an optimizer hint. Optimizer hints can be divided into five different categories:
• Table Hints: Used to force index selection.
• Join Hints: Used to specify the type of JOIN strategy used.
• Query Hints: Hints used to affect GROUP BY and UNION functionality.
• Lock Hints : Used to help avoid undesirable locking.
• View Hints: Used to specify indexes in indexed views.
• Other Hints: Misc. hints.

In essence, what a hint does is override what the Query Optimizer recommends. If the Query Optimizer is wrong, then using a hint is very beneficial. But if the hint you specify is wrong, or not ideal, it will continue to be wrong until you change it, hurting performance until it is corrected.

Sql Server Index Basics

Indexing a table is an access strategy, that is, a way to sort and search records in the table. Indexes are essential to improve the speed with which the records can be located and retrieved from a table.

Indexes are created on columns in tables or views. For example, if you create an index on the primary key and then search for a row of data based on one of the primary key values, SQL Server first finds that value in the index, and then uses the index to quickly locate the entire row of data. Without the index, a table scan would have to be performed in order to locate the row, which can have a significant effect on performance.

You can create indexes on most columns in a table or a view. The exceptions are primarily those columns configured with large object (LOB) data types, such as image, text, and varchar(max). You can also create indexes on XML columns.

An index is made up of a set of pages (index nodes) that are organized in a B-tree structure. This structure is hierarchical in nature, with the root node at the top of the hierarchy and the leaf nodes at the bottom.

When a query is issued against an indexed column, the query engine starts at the root node and navigates down through the intermediate nodes, with each layer of the intermediate level more granular than the one above. The query engine continues down through the index nodes until it reaches the leaf node.

Clustered Indexes

A clustered index stores the actual data rows at the leaf level of the index. An important characteristic of the clustered index is that the indexed values are sorted in either ascending or descending order. As a result, there can be only one clustered index on a table or view. In addition, data in a table is sorted only if a clustered index has been defined on a table.

Note: A table that has a clustered index is referred to as a clustered table. A table that has no clustered index is referred to as a heap.

Nonclustered Indexes

Unlike a clustered indexed, the leaf nodes of a nonclustered index contain only the values from the indexed columns and row locators that point to the actual data rows, rather than contain the data rows themselves. This means that the query engine must take an additional step in order to locate the actual data.

A row locator’s structure depends on whether it points to a clustered table or to a heap. If referencing a clustered table, the row locator points to the clustered index, using the value from the clustered index to navigate to the correct data row. If referencing a heap, the row locator points to the actual data row.

Nonclustered indexes cannot be sorted like clustered indexes; however, you can create more than one nonclustered index per table or view. SQL Server 2005 supports up to 249 nonclustered indexes, and SQL Server 2008 support up to 999. This certainly doesn’t mean you should create that many indexes. Indexes can both help and hinder performance, as I explain later in the article.

Index Types

Simple Index:-
An index created on a single column of a table it is called simple index. The syntax for creating simple index that allow duplicate values is :-

CREATE INDEX ix_name ON tableName (columnName)

Composite Index:-
An index that contains more than one column is known as composite index. In both SQL Server 2005 and 2008, you can include up to 16 columns in an index, as long as the index doesn’t exceed the 900-byte limit. Both clustered and nonclustered indexes can be composite indexes. The syntax creating a composite index that allows duplicate value is:-

CREATE INDEX ix_name ON tableName (column1, column2)

Unique Index:-
An index that ensures the uniqueness of each value in the indexed column is known as unique index. If the index is a composite, the uniqueness is enforced across the columns as a whole, not on the individual columns. For example, if you were to create an index on the FirstName and LastName columns in a table, the names together must be unique, but the individual names can be duplicated.

If an index is created on a single column it is called Simple Unique index. The syntax for creating simple unique index is:-

CREATE UNIQUE INDEX ux_name ON tableName(columnName)

If an index is created on more than one it column is called composite unique index. The syntax for creating a composite unique index is:-

CREATE UNIQUE INDEX ux_name ON tableName (column1, column2,..)

A unique index is automatically created when you define a primary key or unique constraint:
Primary key: When you define a primary key constraint on one or more columns, SQL Server automatically creates a unique, clustered index if a clustered index does not already exist on the table or view. However, you can override the default behavior and define a unique, nonclustered index on the primary key.
Unique: When you define a unique constraint, SQL Server automatically creates a unique, nonclustered index. You can specify that a unique clustered index be created if a clustered index does not already exist on the table.

Dropping index

Indexes associated with table can be removed by using the drop index command.
DROP INDEX tableName.ix_name

Index Design

As beneficial as indexes can be, they must be designed carefully. Because they can take up significant disk space, you don’t want to implement more indexes than necessary. In addition, indexes are automatically updated when the data rows themselves are updated, which can lead to additional overhead and can affect performance. As a result, index design should take into account a number of considerations.
• Every index increases the time in takes to perform INSERTS, UPDATES and DELETES, so the number of indexes should not be very much. Try to use maximum 4-5 indexes on one table, not more. If you have read-only table, then the number of indexes may be increased.
• Keep your indexes as narrow as possible. This reduces the size of the index and reduces the number of reads required to read the index.
• Try to create indexes on columns that have integer values rather than character values.
• If you create a composite (multi-column) index, the order of the columns in the key are very important. Try to order the columns in the key as to enhance selectivity, with the most selective columns to the leftmost of the key.
• If you want to join several tables, try to create surrogate integer keys for this purpose and create indexes on their columns.
• Create surrogate integer primary key (identity for example) if your table will not have many insert operations.
• Clustered indexes are more preferable than nonclustered, if you need to select by a range of values or you need to sort results set with GROUP BY or ORDER BY.
• If your application will be performing the same query over and over on the same table, consider creating a covering index on the table.
• You can use the SQL Server Profiler Create Trace Wizard with "Identify Scans of Large Tables" trace to determine which tables in your database may need indexes. This trace will show which tables are being scanned by queries instead of using an index.
• You can use the sp_MSforeachtable undocumented stored procedure to rebuild all indexes in your database. Try to schedule it to execute during CPU idle time and slow production periods.

sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"