{"id":69266,"date":"2021-08-16T04:10:05","date_gmt":"2021-08-16T08:10:05","guid":{"rendered":"http:\/\/bangla.salearningschool.com\/recent-posts\/sql-server-index-38\/"},"modified":"2021-08-16T04:10:05","modified_gmt":"2021-08-16T08:10:05","slug":"sql-server-index-38","status":"publish","type":"post","link":"http:\/\/bangla.sitestree.com\/?p=69266","title":{"rendered":"SQL Server: Index #38"},"content":{"rendered":"<p><b>SQL Server: Index<\/b><\/p>\n<ul>\n<li> Indexes make database access faster. Without index your queries will run but indexes can increase the performance dramatically.<\/li>\n<li> You can define multiple indexes for a table and select the index that is required for current operations.<\/li>\n<li> Only one clustered index is supported for a table<\/li>\n<li> To get advantage of indexes you have to mention the index related constraints in your where clause like where last_name =&#8217;xyz&#8217;<\/li>\n<li> If you have indexes on two or more columns, in where clause mention the first column name\/constraint first [that was first in the index creation].<\/li>\n<li> A clustered index contains table data sorted in the index where a non-clustered index contains reference to the table data or clustered index. Non-clustered indexes are both physically and logically independent of table data.<\/li>\n<\/ul>\n<p><b>Index Best Practices<\/b> <\/p>\n<ol>\n<li> Select a column\/(columns) for indexes that will provide good uniqueness and selectivity otherwise indexing may degrade the performance<\/li>\n<li> Create indexes that result in lower number of rows to be searched <\/li>\n<li> Create indexes that select a range of rows<\/li>\n<li> Try to create clustered index with as uniqueness as possible<\/li>\n<li> Keep indexes as narrow as possible (not many\/unnecessary columns)<\/li>\n<li> Don&#8217;t index very small table<\/li>\n<\/ol>\n<p>In SQL server, you can create indexes through your sql statements or using the development studio interface.<\/p>\n<p>From: http:\/\/sitestree.com\/?p=4836<br \/> Categories:38<br \/>Tags:<br \/> Post Data:2011-01-21 05:26:33<\/p>\n<pre><code>    Shop Online: &lt;a href='https:\/\/www.ShopForSoul.com\/' target='new' rel=\"noopener\"&gt;https:\/\/www.ShopForSoul.com\/&lt;\/a&gt;\n    (Big Data, Cloud, Security, Machine Learning): Courses: &lt;a href='http:\/\/Training.SitesTree.com' target='new' rel=\"noopener\"&gt; http:\/\/Training.SitesTree.com&lt;\/a&gt; \n    In Bengali: &lt;a href='http:\/\/Bangla.SaLearningSchool.com' target='new' rel=\"noopener\"&gt;http:\/\/Bangla.SaLearningSchool.com&lt;\/a&gt;\n    &lt;a href='http:\/\/SitesTree.com' target='new' rel=\"noopener\"&gt;http:\/\/SitesTree.com&lt;\/a&gt;\n    8112223 Canada Inc.\/JustEtc: &lt;a href='http:\/\/JustEtc.net' target='new' rel=\"noopener\"&gt;http:\/\/JustEtc.net (Software\/Web\/Mobile\/Big-Data\/Machine Learning) &lt;\/a&gt;\n    Shop Online: &lt;a href='https:\/\/www.ShopForSoul.com'&gt; https:\/\/www.ShopForSoul.com\/&lt;\/a&gt;\n    Medium: &lt;a href='https:\/\/medium.com\/@SayedAhmedCanada' target='new' rel=\"noopener\"&gt; https:\/\/medium.com\/@SayedAhmedCanada &lt;\/a&gt;\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>SQL Server: Index Indexes make database access faster. Without index your queries will run but indexes can increase the performance dramatically. You can define multiple indexes for a table and select the index that is required for current operations. Only one clustered index is supported for a table To get advantage of indexes you have &hellip; <\/p>\n<p><a class=\"more-link btn\" href=\"http:\/\/bangla.sitestree.com\/?p=69266\">Continue reading<\/a><\/p>\n","protected":false},"author":8,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[1917],"tags":[],"class_list":["post-69266","post","type-post","status-publish","format-standard","hentry","category-fromsitestree-com","item-wrap"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":5939,"url":"http:\/\/bangla.sitestree.com\/?p=5939","url_meta":{"origin":69266,"position":0},"title":"Title: SQL CREATE INDEX Statement . SQL Create Index Statement","author":"Author-Check- Article-or-Video","date":"January 30, 2015","format":false,"excerpt":"Title: SQL CREATE INDEX Statement \u0985\u09a8\u09c1\u09ac\u09be\u09a6\u0995: \u09ab\u09df\u09b8\u09be\u09b2 \u09b0\u0995\u09bf (\u098f\u09ae.\u098f) Total word count: 247 ----------------------------------------------------- CREATE INDEX \u09b8\u09cd\u099f\u09c7\u099f\u09ae\u09c7\u09a8\u09cd\u099f\u09c7\u09b0 \u09b8\u09be\u09b9\u09be\u09af\u09cd\u09af\u09c7 \u0995\u09cb\u09a8\u09cb table \u098f\u09b0 index \u09aa\u09cd\u09b0\u09b8\u09cd\u09a4\u09c1\u09a4 \u0995\u09b0\u09be \u09b9\u09df\u0964 index \u09ac\u09cd\u09af\u09ac\u09b9\u09be\u09b0 \u0995\u09b0\u09c7 database application \u098f\u09b0 \u09b8\u09be\u09b9\u09be\u09af\u09cd\u09af\u09c7 \u0995\u09cb\u09a8 table \u098f\u09b0 \u09ae\u09a7\u09cd\u09af\u0995\u09be\u09b0 data \u0995\u09c7 \u09b8\u09b9\u099c\u09c7 \u0996\u09c1\u0981\u099c\u09c7 \u09ac\u09c7\u09b0 \u0995\u09b0\u09be \u09af\u09be\u09df\u0964 \u098f\u0995\u09cd\u09b7\u09c7\u09a4\u09cd\u09b0\u09c7 \u09aa\u09c1\u09b0\u09cb table \u099f\u09bf \u0996\u09c1\u0981\u099c\u09a4\u09c7 \u09b9\u09df \u09a8\u09be,\u2026","rel":"","context":"In &quot;\u098f\u09b8 \u0995\u09bf\u0989 \u098f\u09b2 - \u09e6\u09e6\u09e7 \u0964 SQL - 001&quot;","block_context":{"text":"\u098f\u09b8 \u0995\u09bf\u0989 \u098f\u09b2 - \u09e6\u09e6\u09e7 \u0964 SQL - 001","link":"http:\/\/bangla.sitestree.com\/?cat=155"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":5941,"url":"http:\/\/bangla.sitestree.com\/?p=5941","url_meta":{"origin":69266,"position":1},"title":"SQL DROP INDEX, DROP TABLE, and DROP DATABASE","author":"Author-Check- Article-or-Video","date":"January 30, 2015","format":false,"excerpt":"Title: SQL DROP INDEX, DROP TABLE, and DROP DATABASE \u0985\u09a8\u09c1\u09ac\u09be\u09a6\u0995: \u09ab\u09df\u09b8\u09be\u09b2 \u09b0\u0995\u09bf (\u098f\u09ae.\u098f) Total word count: 164 DROP statement \u098f\u09b0 \u09b8\u09be\u09b9\u09be\u09af\u09cd\u09af\u09c7 Indexes, tables \u0995\u09bf\u0982\u09ac\u09be databases \u0995\u09c7 \u09b8\u09b9\u099c\u09c7\u0987 deleted \u09ac\u09be removed \u0995\u09b0\u09be \u09af\u09be\u09df\u0964 The DROP INDEX Statement DROP INDEX statement \u09ac\u09cd\u09af\u09ac\u09b9\u09be\u09b0 \u0995\u09b0\u09c7 \u098f\u0995\u099f\u09bf table \u098f\u09b0 \u098f\u0995\u099f\u09bf index \u0995\u09c7 delete \u0995\u09b0\u09be \u09af\u09be\u09df\u0964\u2026","rel":"","context":"In &quot;\u098f\u09b8 \u0995\u09bf\u0989 \u098f\u09b2 - \u09e6\u09e6\u09e7 \u0964 SQL - 001&quot;","block_context":{"text":"\u098f\u09b8 \u0995\u09bf\u0989 \u098f\u09b2 - \u09e6\u09e6\u09e7 \u0964 SQL - 001","link":"http:\/\/bangla.sitestree.com\/?cat=155"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":23196,"url":"http:\/\/bangla.sitestree.com\/?p=23196","url_meta":{"origin":69266,"position":2},"title":"How to optimize query response time? #Root #By Sayed Ahmed","author":"Author-Check- Article-or-Video","date":"March 26, 2021","format":false,"excerpt":"Check that only the required columns are selected [in select statements] Check that the right types of joins are used Check that if re-ordering the joins will help or not Check the indexes of the tables [is indexing done on the right columns] Check that in joins you used the\u2026","rel":"","context":"In &quot;FromSitesTree.com&quot;","block_context":{"text":"FromSitesTree.com","link":"http:\/\/bangla.sitestree.com\/?cat=1917"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":69264,"url":"http:\/\/bangla.sitestree.com\/?p=69264","url_meta":{"origin":69266,"position":3},"title":"SQL Server Database System Tuning #38","author":"Author-Check- Article-or-Video","date":"August 16, 2021","format":false,"excerpt":"Database tuning approach Proactive approach Reactive approachBest Approach: Use a mix of both.Be aware (collect information) of the followings that may have significant impact on the performance. Hardware: Server, Available memory, number of processors, disk subsystems. Network Infrastructure: Network Cards, Switches, and the rest of your LAN and WAN. Operating\u2026","rel":"","context":"In &quot;FromSitesTree.com&quot;","block_context":{"text":"FromSitesTree.com","link":"http:\/\/bangla.sitestree.com\/?cat=1917"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":69412,"url":"http:\/\/bangla.sitestree.com\/?p=69412","url_meta":{"origin":69266,"position":4},"title":"Comparison: FireBird, MySQL, PostGreSQL #2","author":"Author-Check- Article-or-Video","date":"August 20, 2021","format":false,"excerpt":"PosTGRESQL Provides better query responsetime than firebird and MySql. Firebird stands somewhere in the middle. A little longer response time than PostGreSql.http:\/\/benchw.sourceforge.net\/benchw_results_open3.html Several PostGreSQL versions are compared at:http:\/\/benchw.sourceforge.net\/benchw_results_postgres_history.html Several MySQL versions are compared at:http:\/\/benchw.sourceforge.net\/benchw_results_mysql_indexes.html A good comparison among these databases will be found at:http:\/\/www.geocities.com\/mailsoftware42\/db\/ Mentionable comments PostGreSQL Supports 400+GB DB.One\u2026","rel":"","context":"In &quot;FromSitesTree.com&quot;","block_context":{"text":"FromSitesTree.com","link":"http:\/\/bangla.sitestree.com\/?cat=1917"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":16497,"url":"http:\/\/bangla.sitestree.com\/?p=16497","url_meta":{"origin":69266,"position":5},"title":"Magento 2: Reset indexes","author":"Sayed","date":"December 9, 2019","format":false,"excerpt":"Reset indexes if index reindexing causes issues. All possible combinations for native indexes: \" bin\/magento indexer:reset design_config_grid; bin\/magento indexer:reset customer_grid; bin\/magento indexer:reset catalog_category_product; bin\/magento indexer:reset catalog_product_category; bin\/magento indexer:reset catalogrule_rule; bin\/magento indexer:reset catalog_product_attribute; bin\/magento indexer:reset cataloginventory_stock; bin\/magento indexer:reset catalog_product_price; bin\/magento indexer:reset catalogrule_product; bin\/magento indexer:reset catalogsearch_fulltext; Reference:https:\/\/mirasvit.com\/knowledge-base\/magento-2-reindex-error-locked.html","rel":"","context":"In &quot;\u09ac\u09cd\u09b2\u0997 \u0964 Blog&quot;","block_context":{"text":"\u09ac\u09cd\u09b2\u0997 \u0964 Blog","link":"http:\/\/bangla.sitestree.com\/?cat=182"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/posts\/69266","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/users\/8"}],"replies":[{"embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=69266"}],"version-history":[{"count":0,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/posts\/69266\/revisions"}],"wp:attachment":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=69266"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=69266"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=69266"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}