{"id":4326,"date":"2013-01-31T17:20:22","date_gmt":"2013-01-31T17:20:22","guid":{"rendered":"http:\/\/craftydba.com\/?p=4326"},"modified":"2013-02-01T20:39:59","modified_gmt":"2013-02-01T20:39:59","slug":"schema-design-with-ssms","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=4326","title":{"rendered":"Schema Design 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 sections 1E in the sample code.<\/p>\n

I want to explore how the CREATE SCHEMA<\/a>, ALTER SCHEMA<\/a>, and DROP SCHEMA<\/a> data definition language (DDL<\/a>) constructs can be achieved with SSMS.<\/p>\n

A user defined schema allows a database administrator (DBA) to define security on groups of objects. I think this is a very powerful but under utilized technique.<\/p>\n

The security node under the [AUTOS] database in the object explorer tree shows the default schema for any database. As we know, every newly created object defaults to the [dbo] schema if not explicitly defined. Exceptions to this rule are when a default schema other than [dbo] is selected when creating a database user.<\/p>\n

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

I want to execute the CREATE SCHEMA<\/a> statement twice in the [AUTOS] database: one for [ACTIVE] data and one for [STAGE] data. <\/p>\n

The first step<\/span> is to right click the security node in the object explorer and select the New Schema menu option. <\/p>\n

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

The second step<\/span> is to choose a name and owner for the new schema. I usually default schema ownership to [dbo].<\/p>\n

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

Repeat these steps again for the [STAGE] schema.<\/p>\n

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

Taking another look at the security node under the [AUTOS] database in the object explorer show our two new SCHEMA.<\/p>\n

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

What is the use of a schema without declaring a member object?<\/p>\n

I am jumping ahead in our series of SSMS talks to table creation. Just run the T-SQL code in Section 1L to create the [MODELS] table on the [STAGE] schema. Please see screen shot of table below.<\/p>\n

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

Now, let’s see what happens when we try to execute the DROP SCHEMA<\/a> statement on a schema that is bound to an object. <\/p>\n

Find the security node in the object explorer and select the [STAGE] schema. Right click and choose the delete menu option. This will bring up the delete object dialog box. Clicking OKAY will try to remove the schema.<\/p>\n

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

The following error occurs when we try this action. In short, the schema must be empty before we try this operation. I will leave the task of dropping the [MODELS] table then the [STAGE] schema as an exercise that you can do.<\/p>\n

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

In summary, we can do many of the Data Definition Language (DDL<\/a>) tasks that can be done in T-SQL. The cool script button shows you what SQL Server Management Studio (SSMS) will use for code to accomplish a particular task. Next time, I will be talking about creating server logins and database users with 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":[55,53,31,391,748,54,12,15,28,741,470],"class_list":["post-4326","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-alter-schema","tag-create-schema","tag-database-developer","tag-ddl","tag-designing-schemas","tag-drop-schema","tag-free-code","tag-john-f-miner-iii","tag-sql-server","tag-sql-server-management-studio","tag-ssms"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/4326","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=4326"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/4326\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4326"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4326"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4326"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}