Applying Index, View, As Well As Full – Text Search Part – 5

How To Optimize Index?

The SQL Server inevitably handles the indexes each and every time add, alter or delete activities are made to the base or underlying information. These alterations are the reason why the data inside the index turns out to be dispersed in the database. Fragmentation happens whenever the indexes have pages where the reasonable organization does not get synchronized with the bodily organization in the information file. Comprehensively fragmented indexes can destroy the enquiry performance besides that it may be the reason why an individual’s system replies inefficiently. Fragmentation generally happens the minute a huge amount of add and alter activities are done on the table or relation. Fragmented information can be the reason why SQL Server carries out pointless information reads. This disturbs the performance of the enquiry. Hence, the index defragmentation is essential to achieve the promptness in the enquiry performance.

In SQL Server, index defragmentation can be achieved either by rearranging or reconstructing an index. The very first phase in determining which defragmentation technique to practice is to conclude the degree of index fragmentation. An individual can spot index fragmentation by means of the SYS . DM_DB_INDEX_PHYSICAL_STATS system function. Once the degree of defragmentation is identified, the fragmentation requires to be adjusted. The subsequent table shows the activities that are to be use at varied fragmentation stages to defragment the index:

An individual can run whichever commands mentioned above to defragment every index, subject to the fragmentation stage, as shown in the subsequent cases:

i) For fragmentation level > 5 % and < = 30 % use the following command:

ALTER INDEX My_Index_Name ON My_Table_Name REORGANIZE

ii) For fragmentation level > 30 % use the following command:

ALTER INDEX My_Index_Name ON My_Table_Name REBUILD WITH ONLINE = ON

Building Plus Handling View

From time to time, the database admins may need to limit the obtainability of information to dissimilar employee or end – users. Admins may need few employees or end – users to be capable of retrieving every column or attribute of a table or relation, on the other hand, may want that some other employees or end – users are only capable to retrieve only few designated columns or attributes. The SQL Server permits an individual to build view which limits the employee or end – users’ accessibility to the information. View benefits in streamlining enquiry performance whenever the enquiry includes recovering information from several tables or relations by means of joins.

The view is a simulated table or relation, which offers accessibility to a subsection of the columns or attributes from one or several tables or relations. It is simply a query kept as an item in the database, which is not having its personal information. The view can originate its information from one or several tables or relations, which are termed as the Base tables or relations or Underlying tables or relations. Subject to the size of information, an individual can build a view using or dodging an index. For a database designer, it is significant to know how to build as well as how to handle view.

What Is View?

The view is a database item which is castoff to observe information from the tables or relations within the database. The view takes an arrangement alike to a table or relation. It has no information of its own; nevertheless it originates its information from the underlying tables or relations or base tables or relations. The view guarantees the safety of information by means of limiting the approachability to:

· The exact rows or tuples of a table or relation

· The exact columns or attributes of a table or relation

· The exact rows or tuples as well as the columns or attributes of a table or relation

· The rows or tuples derived by means of joins

· The statistical brief of the information in a particular table or relation

· The subsections of additional view or a subsection of views as well as tables or relations

Besides from limiting approachability, the view can be castoff to craft plus save the queries grounded on the numerous tables or relations too. In the direction of viewing information from numerous tables or relations, an individual can craft a query which contains several joins. Certainly, if an individual is required to run this particular query often, then the individual may build a view which will implement this particular query. The individual can obtain the information from this specific view all the time whenever it is required. An individual can build a view by means of the CREATE VIEW command. The code of the CREATE VIEW command is as follows:

CREATE VIEW My_View_Name

[ ( My_Column_Name1 [ , My_Column_Name2 , …. My_Column_NameN ] ) ]

[ WITH ENCRYPTION [ , SCHEMABINDING ] ]

AS My_Select_Commands [ WITH CHECK OPTION ]

here,

· My_View_Name – It stipulates the name of the view that an individual wants to provide.

· My_Column_Name1 , My_Column_Name2 , …. My_Column_NameN – It stipulates the name or names of the column or attribute / columns or attributes that are to use in a view.

· WITH ENCRYPTION – It stipulates that the script of the view will be encoded in the SYSCOMMENTS system view.

· SCHEMABINDING – It links the view to the scheme of the base table or tables / relation or relations or underlying table or tables / relation or relations.

· AS – It stipulates the activity that will be done through the view.

· My_Select_Commands – It stipulates the SELECT command which describes a view. A view might use the information confined in some another views as well as tables or relations.

· WITH CHECK OPTION – It obligates the information alteration commands to match up with the standards mentioned in the SELECT command describing the view. The information is observable by the view later the alterations has been done everlastingly.

SYSCOMMENTS is a system view that is kept in the database. It has records for every single view, default, rule and check constriction in addition to stored procedure in the database. This system view holds a textual column or attribute which stocks the unique SQL description commands.

In the upcoming article we will be discussing about Instruction For Forming View, Limitation At The Time Of Altering Information With View, How To Index View? & Instruction For Forming Indexed View in details.