{"id":5104,"date":"2013-03-28T16:56:27","date_gmt":"2013-03-28T16:56:27","guid":{"rendered":"http:\/\/craftydba.com\/?p=5104"},"modified":"2013-03-28T17:55:33","modified_gmt":"2013-03-28T17:55:33","slug":"crafting-views-with-ssms","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=5104","title":{"rendered":"Crafting Views with SSMS"},"content":{"rendered":"

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

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. I will be going over section 1K in the sample code with a little literary lead way to go off topic when necessary to demonstrate my ideas.<\/p>\n

I want to explore how the CREATE VIEW<\/a>, ALTER VIEW<\/a> and DROP VIEW<\/a> statements can be executed within the SSMS graphical interface.<\/p>\n

The first step<\/span> in creating a view involves finding the views node under the [AUTOS] database in the SSMS object explorer. Right click and select new view as seen below.<\/p>\n

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

The second step<\/span> is to choose both the [MAKES] and [MODELS] tables from the [ACTIVE] schema. These two tables are the basis of our view.<\/p>\n

Instead of tables, we could choose any item from the list of valid objects: TABLES, VIEWS, FUNCTIONS or SYNONYMS.<\/p>\n

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

The third step<\/span> is to define relationships, columns, aliases, calculations, filters and\/or sorting to define the view in the designer. Because the [MAKER_ID] is common to both tables, it already has a inner join defined between the tables.<\/p>\n

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

The fourth step<\/span> is to close the view designer. This will prompt you to save your work. Save it as [ACTIVE].[VW_JOIN_MAKES_2_MODELS]. <\/p>\n

You have created your first view using SSMS ~ CREATE VIEW<\/a>.<\/p>\n

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

The view now shows up in the object explorer as seen below. <\/p>\n

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

To edit the view, right click and choose edit. Upon saving any changes, you would have altered your view using SSMS ~ ALTER VIEW<\/a>. <\/p>\n

To drop the view, right click and choose delete. Upon okaying the delete, you would have dropped your view using SSMS ~ DROP VIEW<\/a>. I will leave these simple tests for you to perform. <\/p>\n

Last but not least, to select from the view, right click and choose select top 1000 rows.<\/p>\n

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

To recap, we can CREATE, ALTER and DROP views using the Graphical dialog boxes in the SQL Server Management Studio (SSMS) application. Next time I want to talk about inserting, updating and deleting rows using SSMS. <\/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":[809,707,813,316,31,563,810,804,807,15,812,811,28,741,470,808,805,806],"class_list":["post-5104","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-alias","tag-alter-view","tag-calculations","tag-create-view","tag-database-developer","tag-drop-view","tag-filter","tag-free-advice","tag-function-source","tag-john-f-miner-iii","tag-join-data","tag-sort","tag-sql-server","tag-sql-server-management-studio","tag-ssms","tag-synonymns-source","tag-table-source","tag-view-source"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/5104","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=5104"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/5104\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5104"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5104"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5104"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}