SQL Indexes

  • Clustered Indexes:
    • Use B-Tree algorithm.
    • Only one index for a table.
    • Clustered indexes are similar to a telephone directory where you search a person's name alphabetically and get his phone number there only.
    • CLUSTERED INDEX SCAN:
      • When table with crusted index is accessed
      • The query doesn't use the non-clustered index in the table.
      • The table does not have any non-clustered index.
      • It will be bad when large data with most columns and rows are retrieved
    • CLUSTERED INDEX SEEK:
      • When
        • Table with clustered index is accessed and query locates specific rows in B+ tree.
        • It will be always good.
      • Even though evaluate the possibility of non-clustered index.
  • Non Clustered Indexes:
    • Use B-Tree algorithm.
    • For SQL 2005 249 Non Clustered Index. It is 999 for SQL 2008.
    • Non Clustered indexes are similar to the Index of a book where you get the page number of the item you were searching for. Then turn to that page and read what you were looking for.
  • NON - CLUSTERED INDEX SCAN:
    • When
      • Columns part of non-clustered index is accessed in query.
    • It will be bad when large data with most columns and rows are retrieved.
    • When it occur, need to create more defined non-clustered index.
  • NON - CLUSTERED INDEX SEEK:
    • When
      • Columns part of non-clustered index is accessed in query and rows located in B+ tree.
    • It will be always good.

Comments

Post a Comment

Popular posts from this blog

BDD - Acceptance Test Driven Development

Image
Acceptance Test Driven Development What is BDD: BDD is an agile software development technique that encourages collaboration between developers, QA, and non-technical or business participants in a software project. It's more about business specifications than about tests. You write a specification for a story and verify whether the specs work as expected. The main features of BDD development are outlined below:   A testable story (it should be the smallest unit that fits in an iteration) The title should describe an activity The narrative should include a role, a feature, and a benefit The scenario title should say what's different The scenario should be described in terms of Givens, Events, and Outcomes The givens should define all of, and no more than, the required context The event should describe the feature   Advantage of BDD:   The first advantage is that this domain language is usable/understandable by domain experts as well. Given that you e...
Read more

Angular JS – Part 2

Angular Routing Services and Directives: Routing Services: $route - $routeProvider is used to map the URL with template + assigned controller. (specially for SPAs). Methods: when(), otherwise(redirectTo:), current, params, pathParams, reload(). Also we can use resolve() to decide before load template. $routeParams – Used to pass the parameters via URL $location –Use to enable html5 routing. Can get the URL informations Eg. absUrl, protocol, port, host, path, search, hash, url. Replace() method will not keep URL history. Directives: Uses of Directives: Create custom element, custom event, observe and react to changes Use eventApp.dirctive("directiveName",function(){ return {restrict: 'E', template: "<input>"}}); Can restrict the directive to how should use. E(element), C(class), A(attribute), M(comment). Instead of template HTML text, the URL can be used. Use" replace: true" to replace the element name to html i...
Read more

.Net Collections

The  System.Collections namespace contains interfaces and classes that define various collections of objects, such as lists, queues, bit arrays, hash tables and dictionaries. Array list: Implements the IList interface using an array whose size is dynamically increased as required. Dictionary Base: Provides the abstract (MustInherit in Visual Basic) base class for a strongly typed collection of key-and-value pairs. Dictionary is a Generic type. So we can get type safety with Dictionary. Hash table: Represents a collection of key-and-value pairs that are organized based on the hash code of the key. A key cannot be a null reference (Nothing in Visual Basic), but a value can be. Hashtable is not a Generic type. So we can’t get type safety with Hashtable. Hashtable is thread safe. Queue: Represents a first-in, first-out collection of objects. Queues are useful for storing messages in the order they were received for sequential processing. Stack: ...
Read more