XML Index

XML INDEX
xml index can be created on columns that have the xml data type, this index includes all tags, values ​​and path so that it can improve query performance.
applications that benefit from the xml index is; -Query on xml column is general in workload, xml index maintenance cost should be in calculated on data modification. Xml generally large-value and the part taken is usually small, making the index avoid parsing all the data and benefit index for query process
XML indexes are divided into two categories Xml Primary index Xml Secondary index
The first index on XML column must be the primary xml index, using xml index, xml secondary index can be supported: path, value, property. depend on the type query, xml secondary index may improve query performance.
xml instance is stored in column type xml as blobs. xml isntace can be sized great and save the xml data type binary form, size up to 2GB. without index, BLOB in the iris at run time to evaluate the query. This can take a lot of slicing time.

primary xml index primary xml index will index all the tags, values ​​and paths in the xml instance in xml column. to going to the xml indexes, tables in xml column should have a clustered index on the primary key in the table. sql server using the primary xml index row by row on the table that contains xml column. primary xml index was presented and excised from the xml xml BLOB column. for each xml BLOB in a column. index make some rows of data. many rows in the index is estimated to equal to the number nodes in the XML BLOB, when the query asks isntance full xml, sql server will provide instance of the xml column. query in xml intance using primary xml index, and return scalar value from the xml subtree using its own index.

each row stores information following node Name-tag Node-value -Type nodes such as element node, attribute node, or text node. Document-sequence information, which is represented by an internal node identifier -Path from each node up to root on the xml tree. Kery-primary of the base tables, primary key of the base tables, duplicated in the primary xml index back to join the base tables, and the maximum number of columns in a table primary key based in the limit 15


by looking at the primary xml index, rather than each xml BLOB shreeding isntance in base table, a corresponding row in the index with each XML BLOB in sequential search for expression that exists in the method exists (). if the path is found in the path column in index. The <summary> element together with subtreenya in retrieving the primary xml index and converted into an XML BLOB as a result of the query method.


xml secondary index type Xml-type secondary-path -Value -Property
The following is a guide to make one or more secondary indexes: 1.jika workload uses path expressions in the form of significantly in the xml column, SXI path will accelerate performance, general case using methods exist () 2.jika workload request values ​​from individual XML instances using the expression path, clustering paths in the xml instance of the property index will be useful. This scenario generally appear in the property bag scenarios, when the properties of an object in the fetch and primary key value is not known. 3.jika workload contains a query value in the xml instance, without knowing the name of the element or attribute that contains the value. value index. usually appears with the search derivatives, such as / / author [last-name = 'howard'] Nowhere <author> Dapa element appears at all levels of hierarchy. This also appears in the query was a wildcard, such as / book [@*=' novel "], where the query to see who has the book element novel attributes.

Database Transaction

What is Database transaction? 

A transaction is a unit in the database that typically contains some commands that read and write databases. 

Enterprise Relational Database management system must have ACID characteristic, because this is what make RDBMS someway different than normal database like MS.access.
ACID Property  
Atomicity = mean all the effects of the transaction must be fully successful or rolled back.For example ATM cash withdrawals, machine have to spend money and then debited from your account .
Consistency = Consistency make the transaction can not violate the rules of integrity, the database must be in state Consistent. for example the value of the currency should not be negative, the data in the field of sex must be male or female.
Isolation = means the transaction is not completed bole not be read by other transactions, locking.  
Durability = When there are transactions that have dicommit transaction log to be more resilient, will maintain a database transaction logs can still be recovered at some point

SQL Server transactions Distinguished by how they are made.
IMPLICIT - This transaction is used automatically by SQL Server to ensure the ACID properties of a command, for example update statement is modifying 10 rows, SQL server will run that 10 rows will be updated or not all of them. 

Explicit - translation that starts with BEGIN TRANSACION tsql command and terminated with a COMMIT or ROLLBACK Transaction Transaction. 

Relational and storage engines, SQL SERVER divided into two parts
Relational query engine or also called prosesor, its main function to query optimization and execution. Storage engine is responsible for managing all data IO, and IO requests for the row handles, index pages and allocations and other Other components exist BUFFER Pool that contains a variety of cache in sql server including plan cache and data cache.

What Is MS SQL SERVER

MS SQL SERVER is a relational database management system and its make use of SQL and propritery T-SQL, T-SQL are SQL with an extent to more like a programming language (Branch, Decision, And Recursif, Etc)
The original code of MS SQL Server was sold by Sysbase SQL Server to Microsoft, and it was the beginning microsoft going into the enterprise level database market, with a competitor like Oracle, DB2 and etc.
Microsoft then develop SQL Server Product with many advancement in performance, Client IDE tools, and ETL tools (SQL Server Integration Services), a reporting server, OLAP and data mining (SQL Server Analysis Services), and some messaging technology specifically service broker and notification services.

The newest version of SQL Server are MS SQL Server 2008 R2 with some update, the update typically called Cumulative Update (CU).

SQL Server Installation also come with Management Studio, you can do almost many DBA related job in some what Easy even for new people who learning SQL SERVER, the GUI is very windows like, so if any of you used windows, you will know what i say.