🔥Explore Our Free Courses: https://www.simplilearn.com/skillup-free-online-courses?utm_campaign=MSSQLServerTrainingOnline&utm_medium=DescriptionFirstFold&utm_source=youtube

http://www.simplilearn.com/os-and-databases/administering-ms-sql-server-2012-databases-certification-training?utm_campaign=MSSQLServerTrainingOnline&utm_medium=Description&utm_source=youtube
Hello and welcome to the administering SQL Server 2012 databases (exam number 70-462) certification course offered by Simplilearn. This is the sixth lesson of the course in this lesson you will learn about indexes for tables and understand how to manage them.

Objectives of this lesson
After completing this lesson you will be able to describe index, list the new types of indexes in SQL Server 2012, explain column store index, describe FILESTREAM, explain FILETABLE.
Understanding Indexes: This section introduces you to the concept of indexes for columns in a table you will see why indexes are required, how they are stored, how they work and some of the index types that are commonly used in SQL server.

An overview of indexes: Indexes are used to quickly find some data within a table. The index stores the values for each column and a reference to the rows in which they appear. When you try to find a particular value instead of scanning the entire table just the index is looked up and this gives the rows in which the value appears, this is very similar to the way and index works in a book when you wish to look up a term and find all references to the term within a book the index at the back of the book lists the page numbers where the term appears, all you need to do is to first look up the index and then the page numbers themselves while indexes in books are grouped alphabetically for easier search the index for a column in a table is created as what is known as a B-Tree structure or its variant the B-plus tree structure. This structure basically divides the values in the index into two binary sets that helps you never down the value to search for quickly. Do note that while indexes make queries much faster they slow down changes that is inserts updates and deletes to the table. This is because each index attached to the columns of the table must be updated as soon as any changes made. In many scenarios highly transacted tables are kept without indexes to speed up rights to it a copy of the table through many different ways is kept with all the indexes which are periodically refreshed to allow only queries to the table.

Different types of indexes there are many types of indexes that are available and we’ll take a look at many of them in this lesson however in most cases you will use only one of the two types of indexes. The first is what is known as the clustered index you can only have one clustered index on a table and in most cases is automatically created for the primary key. The clustered index has the unique property that the index is which stores the row itself rather than a reference or pointer to it, since it’s on the primary key it is certain that each value can only point to a single row so for the sake of performance the clustered index stores the road itself so that when you search based on a value on the column it gets you to the row directly. This is useful since the primary key is used not just in the WHERE clause but also in joins and therefore it makes sense to get to the row as quickly as possible.
The other type of commonly used index is the non-clustered index this is one that you can add to any column in the table and you can even combine columns if they are used together you can create up to 999 indexes for table. Although this is not recommended this type of index stores the reference to the row or rows as the case may be you can create indexes in two ways either using the CREATE INDEX statement for using the user interface in SSMS.
Watch full video for more information.
For more updates on courses and tips follow us on:
– Facebook : https://www.facebook.com/Simplilearn
– Twitter: https://twitter.com/simplilearn

Get the Simplilearn app: https://simpli.app.link/OlbFAhqMqgb