{"id":464,"date":"2020-06-17T09:08:49","date_gmt":"2020-06-17T09:08:49","guid":{"rendered":"https:\/\/datarebus.wordpress.com\/?p=184"},"modified":"2021-04-09T08:13:49","modified_gmt":"2021-04-09T08:13:49","slug":"index-yaratmadan-once","status":"publish","type":"post","link":"https:\/\/datarebus.com\/index.php\/2020\/06\/17\/index-yaratmadan-once\/","title":{"rendered":"8 Steps Before Creating an Index On Your SQL Table:"},"content":{"rendered":"\n
\"\"<\/figure><\/div>\n\n\n\n

1-     Not based on Table design, based on load<\/strong><\/h2>\n\n\n\n

As soon as creating table, you don\u2019t need to create whole indexes. Observing loads and managing indexes would be better.<\/p>\n\n\n\n

2-    Size of Table boyutlar\u0131<\/strong><\/h2>\n\n\n\n

Most of the operating systems read data from disk with a 64KB packages. So if your table is small (lets say less than 1000 rows) you may need an index. Additionally creating new index may slower your queries. Because sql server should read index pages first and then it reads data pages. So it means 2 read actions.<\/p>\n\n\n\n

3-    Execution Count of SQL Scripts<\/strong><\/h2>\n\n\n\n

It is not good that having a table with tens of indexes. So try to minimize index counts to get rid of heavy loads. Some of your reports may run slower if other applications are ok with it (or you report doesn\u2019t belongs to your CEO)<\/p>\n\n\n\n

4-    Cardinality<\/strong><\/h2>\n\n\n\n

The higher cardinality the better performance you will have. For example creating index on status field or something like flag, sex etc. may not limit your reading size. You can join more than a column and create unified indexed column to gain more cardinality.<\/p>\n\n\n\n

5-    Not Only \u201cWhere\u201d part, \u201cSelect\u201d parts are also Important<\/strong><\/h2>\n\n\n\n

SELECT name, sirname, city\u00a0 FROM Table_name<\/p>

WHERE name = \u2018omer\u2019 and sirname = \u2018Zeybek\u2019<\/p><\/blockquote>\n\n\n\n

Let\u2019s assume that you have an index on name and sirname columns, SQL Server can find the specified record and read directly from disk. However, in the SELECT part you want to browse city, too. So SQL SERVER should access again to read city. For small data returns, effect of this query may not sense, but if your query is requesting thousands of records, SQL SERVER must read all records twice and blocks more columns than needed.<\/p>\n\n\n\n

6-    Updating Indexed Columns<\/strong><\/h2>\n\n\n\n

Any updates, deletes and inserts on a table also performed on index pages if update columns are part of index. For example, you have an index on update_date column and your application is updating simultaneously this column. SQL SERVER should update index pages after each updates on update_date column. It causes defragmentation on index pages and also decrease the performance of your updates.<\/p>\n\n\n\n

7-    Sort and Order By<\/strong><\/h2>\n\n\n\n

Sorting and order by operations are performed by CPU. It you have not any problem on table, index size and counts and CPU usage is your only problem to concern, than you may create an index to help your CPU.<\/p>\n\n\n\n

8-    Creating Indexes on Different Disks<\/strong><\/h2>\n\n\n\n

If you have GB level database and need more and more performance but you have done everything, than you can separate your indexes and data on to separated disks. It helps your reads and performs parallel. But only non-clustered indexes must be separated, clustered indexes must be located in same drive with data since clustered indexed contains data not addresses of data.<\/u><\/em><\/strong><\/p>\n\n\n\n

<\/p>\n\n\n\n

.\u00a0\u00a0\u00a0<\/p>\n","protected":false},"excerpt":{"rendered":"

1-     Not based on Table design, based on load As soon as creating table, you don\u2019t need to create whole indexes. Observing loads and managing indexes would be better. 2-    Size of Table boyutlar\u0131 Most of the operating systems read data from disk with a 64KB packages. So if your table is small (lets say […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[3],"tags":[10,11,7],"_links":{"self":[{"href":"https:\/\/datarebus.com\/index.php\/wp-json\/wp\/v2\/posts\/464"}],"collection":[{"href":"https:\/\/datarebus.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/datarebus.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/datarebus.com\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/datarebus.com\/index.php\/wp-json\/wp\/v2\/comments?post=464"}],"version-history":[{"count":1,"href":"https:\/\/datarebus.com\/index.php\/wp-json\/wp\/v2\/posts\/464\/revisions"}],"predecessor-version":[{"id":470,"href":"https:\/\/datarebus.com\/index.php\/wp-json\/wp\/v2\/posts\/464\/revisions\/470"}],"wp:attachment":[{"href":"https:\/\/datarebus.com\/index.php\/wp-json\/wp\/v2\/media?parent=464"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/datarebus.com\/index.php\/wp-json\/wp\/v2\/categories?post=464"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/datarebus.com\/index.php\/wp-json\/wp\/v2\/tags?post=464"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}