{"id":4618,"date":"2013-02-21T17:24:15","date_gmt":"2013-02-21T17:24:15","guid":{"rendered":"http:\/\/craftydba.com\/?p=4618"},"modified":"2013-02-21T19:05:57","modified_gmt":"2013-02-21T19:05:57","slug":"managing-indexes-with-ssms","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=4618","title":{"rendered":"Managing Indexes with SSMS"},"content":{"rendered":"

\"\"<\/a>Most of the articles that I presented so far for database development have been centered around the exact Microsoft T-SQL syntax to perform a certain operation. At the University of Rhode Island<\/a>, I learnt how to program in Pascal before typing in my first ANSI SQL statement. In short, I will always be some type of developer in my heart. <\/p>\n

However, many people do not come from a computer science and math background. The SQL Server Management Studio (SSMS<\/a>) has menus and dialog boxes to achieve many of the same results. <\/p>\n

Today, I am going to continue the series of articles that will recreate the [AUTOS] sample database while demonstrating how common T-SQL tasks in my script<\/a> can be done with SSMS. <\/p>\n

I want to explore how to CREATE INDEX<\/a>, DROP INDEX<\/a>, and ALTER INDEX<\/a> commands be duplicated using the SSMS graphical interface. Please see section 1I in the code for defining indexes.<\/p>\n

My example will assume that the [ACTIVE].[MAKES] table has been created without any indexes. <\/p>\n

\"\"<\/a><\/p>\n

Let’s explore some of the CREATE INDEX<\/a> graphical windows.<\/p>\n

Find the indexes node for the table in SSMS and right click to bring up the sub-menu. Select the new index option. This action will bring up the table designer in the background and the new index dialog box in the foreground. <\/p>\n

First, we are going to work on creating the unique index<\/span>. Type [UNQ_MAKER_NM] as the index name, check the unique index box and make sure the index key column is [MAKER_NM]. Clicking the OKAY button will add the index and the CANCEL button will abort the action.<\/p>\n

\"\"<\/a><\/p>\n

Second, we need to create the primary key<\/span>. This task is even easier. Select the [MAKER_ID] column in the table designer and click the primary key tool bar button.<\/p>\n

\"\"<\/a><\/p>\n

The primary key has been created. However, our naming standard uses the column name instead of table name. Find the PK_MAKES index and right click to access the sub-menu. Select the re-name option. Correct the name right in the object explorer window.<\/p>\n

\"\"<\/a><\/p>\n

After you are done, you should have the following indexes created.<\/p>\n

\"\"<\/a><\/p>\n

Now, let’s explore some of the ALTER INDEX<\/a> graphical windows.<\/p>\n

There are some common weekly index tasks that you might encounter as a DBA. I will be covering three of them. These tasks can be found on the indexes node sub-menu.<\/p>\n

The first task is to disable<\/span> the index. This is good idea if you have a large, manual data load. <\/p>\n

\"\"<\/a><\/p>\n

The second task is to rebuild<\/span> a disabled or highly fragmented index.<\/p>\n

\"\"<\/a><\/p>\n

The third task is to reorganize<\/span> a slightly fragmented index.<\/p>\n

\"\"<\/a><\/p>\n

There are many properties that can be viewed or changed on an INDEX. Right click the index name in the object explorer and select the properties option from the sub-menu. <\/p>\n

The General<\/span> page shows a read only version of the information we can enter in the new index dialog box.<\/p>\n

\"\"<\/a><\/p>\n

The Options<\/span> page has various choices that effect how a index works. I have mainly worked with the storage section. Fill factor, pad index, and sort in tempdb are some useful options.<\/p>\n

\"\"<\/a><\/p>\n

The Storage<\/span> page shows a read only version of file groups and\/or partitioning that is being used.<\/p>\n

\"\"<\/a><\/p>\n

The Fragmentation<\/span> page shows read only information about the characteristics of the index. <\/p>\n

I am mainly interested in fragmentation percentage and the forwarded records. Both will cause performance issues with you system. Other properties are interesting when you are performing indexing tuning.<\/p>\n

\"\"<\/a><\/p>\n

The Extended properties<\/span> page allows you to attach custom properties to the index. I have used this option in the past to add a description (comment) to the index definition.<\/p>\n

\"\"<\/a><\/p>\n

Finally, let’s explore some of the DROP INDEX<\/a> graphical windows.<\/p>\n

To drop an index, find the index name in the indexes node in the object explorer. Right click the name and select the delete sub-menu option. This will bring up the delete object menu below. Click OKAY to remove the index or CANCEL to abort the operation.<\/p>\n

\"\"<\/a><\/p>\n

In a nutshell, the indexing of tables is a major part of being a database administrator. <\/p>\n

I have shown you a couple of the types of indexes that can be created on a table. There are many more types such as covered<\/a>, filtered<\/a>, spatial<\/a>, column store<\/a>, fulltext<\/a> and\/or XML<\/a> to be reviewed in the future. <\/p>\n

In short, you should have a good understanding on how to CREATE, ALTER and DROP indexes using SSMS after reading this article.<\/p>\n","protected":false},"excerpt":{"rendered":"

Most of the articles that I presented so far for database development have been centered around the exact Microsoft T-SQL syntax to perform a certain operation. At the University of Rhode Island, I learnt how to program in Pascal before typing in my first ANSI SQL statement. In short, I will always be some type of developer in my heart. However, many people do not come from a computer science and math background. The SQL Server Management Studio (SSMS) has menus and dialog boxes to achieve many of the same…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[769,732,768,31,770,310,774,773,12,743,15,776,746,75,771,772,28,741,470,754,775],"class_list":["post-4618","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-alter-index","tag-clustered-index","tag-create-index","tag-database-developer","tag-disable-index","tag-drop-index","tag-extended-properties","tag-fragmentation-page","tag-free-code","tag-general-page","tag-john-f-miner-iii","tag-non-clustered-index","tag-options-page","tag-primary-key","tag-rebuild-index","tag-reorganize-index","tag-sql-server","tag-sql-server-management-studio","tag-ssms","tag-storage-page","tag-unique-index"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/4618","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4618"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/4618\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4618"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4618"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4618"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}