{"id":6361,"date":"2019-03-11T07:12:55","date_gmt":"2019-03-11T07:12:55","guid":{"rendered":"https:\/\/ittutorial.org\/?p=6361"},"modified":"2019-11-28T07:02:16","modified_gmt":"2019-11-28T07:02:16","slug":"sql-server-check-table-partitioned","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/sql-server-check-table-partitioned\/","title":{"rendered":"SQL Server Check Table Partitioned"},"content":{"rendered":"<p>Hi,<\/p>\n<p>Partitioning is to create our very large tables or indexes in separate segments.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-5171\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/01\/sql-server-perf-icon.jpg\" alt=\"\" width=\"870\" height=\"292\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/01\/sql-server-perf-icon.jpg 870w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/01\/sql-server-perf-icon-300x101.jpg 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/01\/sql-server-perf-icon-768x258.jpg 768w\" sizes=\"auto, (max-width: 870px) 100vw, 870px\" \/><\/p>\n<p><!--more--><\/p>\n<p>In other words, when we convert the large table or indexes that appear logically as a whole into a Partitioned structure, we can divide them into smaller physical parts.<\/p>\n<div class=\"tlid-input input\">\n<div class=\"source-wrap\">\n<div class=\"input-full-height-wrapper tlid-input-full-height-wrapper\">\n<div class=\"source-input\">\n<div class=\"source-footer-wrap source-or-target-footer\">\n<div class=\"character-count tlid-character-count\"><\/div>\n<div class=\"source-footer\">\n<div class=\"src-tts left-positioned ttsbutton jfk-button-flat source-or-target-footer-button jfk-button\" tabindex=\"0\" role=\"button\" aria-label=\"Listen\" data-tooltip=\"Listen\" aria-pressed=\"false\" data-tooltip-align=\"t,c\" aria-hidden=\"false\" aria-disabled=\"false\">\n<div class=\"jfk-button-img\"><\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"tlid-results-container results-container\">\n<div class=\"tlid-result result-dict-wrapper\">\n<div class=\"result tlid-copy-target\">\n<div class=\"text-wrap tlid-copy-target\">\n<div class=\"result-shield-container tlid-copy-target\"><span class=\"tlid-translation translation\"><span class=\"\" title=\"\">SQL Server table Partitioning has a great contribution to performance. So If there are tables and columns which are\u00a0appropriate for Partitioning, they should make partitioned.<\/span><\/span><\/div>\n<\/div>\n<\/div>\n<div><\/div>\n<div>You can check if table is partitioned in SQL Server with following query.<\/div>\n<div><\/div>\n<\/div>\n<\/div>\n<div><\/div>\n<div><\/div>\n<pre>select distinct\r\npp.[object_id],\r\nTbName = OBJECT_NAME(pp.[object_id]), \r\nindex_name = i.[name],\r\nindex_type_desc = i.type_desc,\r\npartition_scheme = ps.[name],\r\ndata_space_id = ps.data_space_id,\r\nfunction_name = pf.[name],\r\nfunction_id = ps.function_id\r\nfrom sys.partitions pp\r\ninner join sys.indexes i \r\non pp.[object_id] = i.[object_id] \r\nand pp.index_id = i.index_id\r\ninner join sys.data_spaces ds \r\non i.data_space_id = ds.data_space_id\r\ninner join sys.partition_schemes ps \r\non ds.data_space_id = ps.data_space_id\r\ninner JOIN sys.partition_functions pf \r\non ps.function_id = pf.function_id\r\norder by TbName, index_name ;<\/pre>\n<div><\/div>\n<div><\/div>\n<p>&nbsp;<\/p>\n<h4><span style=\"color: #ff0000;\">Do you want to learn Microsoft SQL Server DBA Tutorials for Beginners, then read the following articles.<\/span><\/h4>\n<p><a href=\"https:\/\/ittutorial.org\/sql-server-tutorials-microsoft-database-for-beginners\/\">https:\/\/ittutorial.org\/sql-server-tutorials-microsoft-database-for-beginners\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Hi, Partitioning is to create our very large tables or indexes in separate segments.<\/p>\n","protected":false},"author":1,"featured_media":5171,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3828,2227,3827],"tags":[2369,2370,1028,1031,1600,1610,2231,2368,2371,2230,2229],"class_list":["post-6361","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-sql-scripts","category-sql-server-2","category-sql-server-dba-scripts","tag-check-if-table-is-partitioned","tag-check-if-table-is-partitioned-sql-server","tag-mehmet-deveci","tag-mehmet-salih-deveci","tag-sql-server-2012","tag-sql-server-2014","tag-sql-server-2019","tag-sql-server-check-table-partitioned","tag-sql-server-partitioned-table","tag-sql-server-useful-query","tag-sql-server-useful-script"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/01\/sql-server-perf-icon.jpg","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6361","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=6361"}],"version-history":[{"count":2,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6361\/revisions"}],"predecessor-version":[{"id":12961,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6361\/revisions\/12961"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/5171"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=6361"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=6361"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=6361"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}