{"id":637,"date":"2011-08-19T20:04:55","date_gmt":"2011-08-19T20:04:55","guid":{"rendered":"http:\/\/craftydba.com\/?p=637"},"modified":"2012-12-28T17:32:10","modified_gmt":"2012-12-28T17:32:10","slug":"crafting-databases-part-1","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=637","title":{"rendered":"Crafting Databases &#8211; Part 1"},"content":{"rendered":"<p>The <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms176061.aspx\">CREATE DATABASE <\/a>SQL statement is part of the Data Definition Language (DML) defined by <a href=\"http:\/\/en.wikipedia.org\/wiki\/Edgar_F._Codd\">Codd<\/a> and is used define (CREATE), modify (ALTER) or remove (DROP) databases along with there associated files.<\/p>\n<p>Before I talk about crafting a database, I want to classify SQL statements by four known groups.<\/p>\n<p>Data Definition Language (<a href=\"http:\/\/en.wikipedia.org\/wiki\/Data_definition_language\">DDL<\/a>) statements are SQL statements that support the definition or declaration of database objects.<\/p>\n<ul>\n<li>CREATE<\/li>\n<li>ALTER<\/li>\n<li>DROP<\/li>\n<\/ul>\n<p>Data Manipulation Language (<a href=\"http:\/\/en.wikipedia.org\/wiki\/Data_manipulation_language\">DML<\/a>) is a vocabulary used to add, modify, query, or remove data from a SQL Server database.<\/p>\n<ul>\n<li>SELECT<\/li>\n<li>INSERT<\/li>\n<li>UPDATE<\/li>\n<li>DELETE<\/li>\n<\/ul>\n<p>Data Control Language (<a href=\"http:\/\/en.wikipedia.org\/wiki\/Data_Control_Language\">DCL<\/a>) is a language used to control access to data in a database.<\/p>\n<ul>\n<li>GRANT<\/li>\n<li>REVOKE<\/li>\n<li>\u00a0DENY<\/li>\n<\/ul>\n<p>Transaction Control Language (<a href=\"http:\/\/en.wikipedia.org\/wiki\/Transaction_Control_Language\">TCL<\/a>) is a language used to control access to data in a database.<\/p>\n<ul>\n<li>BEGIN<\/li>\n<li>COMMIT<\/li>\n<li>ROLLBACK<\/li>\n<li>SAVE<\/li>\n<\/ul>\n<p>I am going to use a presentation that I did at the local <a href=\"http:\/\/www.snessug.org\/\">Souther New England SQL Server Users Group <\/a>as the basis of our future talks.<\/p>\n<p>Hypothetical Business Problem:<\/p>\n<p>What if you were a SQL Server developer at a local consulting company tasked with creating a database for a local Boy Scout of America (BSA) Troop.\u00a0 You are responsible for designing the data storage solution and access methods for the .NET Web designers.\u00a0 Where would you begin?\u00a0<\/p>\n<p>Today, I am going to talk about defining databases, file groups and schemas.\u00a0 Each database has at least one data file (*.mdf) and one transaction log file (*.ldf).\u00a0 Additional data files (*.ndf) can be added per file group so that tables can be placed in seperate files for parallel access.<\/p>\n<p>The snipet below is our first crack at creating the database via TSQL. The code detects if the database already exists and drops the database.\u00a0 Then, the code creates a database with one data file and one log file. Please note, the minimum size, maximum size and file growth can all be specified at this time.<\/p>\n<pre><span style=\"color: #008000;\">-- Which database to use.\r\nUSE [master]\r\nGO\r\n\r\n-- Delete existing databases.\r\nIF EXISTS (SELECT name FROM sys.databases WHERE name = N'BSA')\r\nDROP DATABASE [BSA]\r\nGO\r\n\r\n-- Add new databases.\r\nCREATE DATABASE [BSA] ON\r\nPRIMARY\r\n( NAME = N'BSA_PRI_DAT', FILENAME = N'C:\\SNESSUG\\DATA\\BSA-PRIMARY.MDF' , \r\nSIZE = 10240KB , MAXSIZE = 51200KB , FILEGROWTH = 15%)\r\nLOG ON\r\n( NAME = N'BSA_LOG1', FILENAME = N'C:\\SNESSUG\\LOG\\BSA-LOG1.LDF' , \r\nSIZE = 5120KB , MAXSIZE = 25600KB , FILEGROWTH = 5120KB )\r\nGO<\/span><\/pre>\n<p>&nbsp;<\/p>\n<p>While this code is useful, we want the ability to load balance table access with different file groups. The snipet below alters the database by adding an additional filegroup, data file and log file. If this was a real life example, we would want to have the data files and log files on\u00a0different physical disks.<\/p>\n<p>This is assuming you have direct attached storage. With today RAID arrays and SAN environments, the actual storage might be abstracted from the DBA.<\/p>\n<pre><span style=\"color: #008000;\">-- Add a new file group\r\nALTER DATABASE [BSA] ADD FILEGROUP [STAGE]\r\nGO\r\n\r\n--Add a data file to the file group\r\nALTER database [BSA]\r\nADD FILE\r\n( NAME = N'BSA_STG_DAT', FILENAME = N'C:\\SNESSUG\\DATA\\BSA-STAGE.MDF' , \r\nSIZE = 10240KB , MAXSIZE = 51200KB , FILEGROWTH = 15%)\r\nTO FILEGROUP STAGE\r\nGO\r\n\r\n-- Add a log file to the database\r\nALTER DATABASE [BSA]\r\nADD LOG FILE\r\n( NAME = N'BSA_LOG2', FILENAME = N'C:\\SNESSUG\\LOG\\BSA-LOG2.LDF' , SIZE = 5120KB , \r\nMAXSIZE = 25600KB , FILEGROWTH = 5120KB )\r\n<\/span><\/pre>\n<p>&nbsp;<\/p>\n<p>Last but not least, we are going to create two schemas to seperate the tables from each other. This allows security to be admistered at this level if need be. Using descriptive schema names allows a new developer on the team\u00a0to understand how the tables fit into the overall logical model.\u00a0 We will be creating a RECENT schema that contains current data and STAGE schema for any future ETL jobs that we write.<\/p>\n<pre><span style=\"color: #008000;\">-- -- Which database to use.\r\nUSE [BSA]\r\nGO\r\n\r\n-- Delete existing schema.\r\nIF EXISTS (SELECT * FROM sys.schemas WHERE name = N'RECENT')\r\nDROP SCHEMA [RECENT]\r\nGO\r\n\r\n-- Add new schema.\r\nCREATE SCHEMA [RECENT] AUTHORIZATION [dbo]\r\nGO\r\n\r\n-- Delete existing schema.\r\nIF EXISTS (SELECT * FROM sys.schemas WHERE name = N'STAGE')\r\nDROP SCHEMA [STAGE]\r\nGO\r\n\r\n-- Add new schema.\r\nCREATE SCHEMA [STAGE] AUTHORIZATION [dbo]\r\nGO<\/span><\/pre>\n<p>&nbsp;<\/p>\n<p>The <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms173423.aspx\">ALTER SCHEMA <\/a>statement transfers ownership of one table from one schema to another. Therefore, to rename a schema, one needs to create a new schema, transfer ownership of the objects, and delete the old schema.<\/p>\n<p>The key points to remember from this\u00a0post is that every database has two files\u00a0(data &amp; log). Also, schemas can be used to segregate large numbers of tables by functional names and security. I will continue this business solution next time by talking about server logins and database users.<\/p>\n<div id=\"_mcePaste\" class=\"mcePaste\" style=\"position: absolute; width: 1px; height: 1px; overflow: hidden; top: 0px; left: -10000px;\">\ufeff<\/div>\n","protected":false},"excerpt":{"rendered":"<p>The CREATE DATABASE SQL statement is part of the Data Definition Language (DML) defined by Codd and is used define (CREATE), modify (ALTER) or remove (DROP) databases along with there associated files. Before I talk about crafting a database, I want to classify SQL statements by four known groups. Data Definition Language (DDL) statements are SQL statements that support the definition or declaration of database objects. CREATE ALTER DROP Data Manipulation Language (DML) is a vocabulary used to add, modify, query, or remove data from a SQL Server database. SELECT&hellip;<\/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":[51,55,50,53,31,52,54,12,15,28,29],"class_list":["post-637","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-alter-database","tag-alter-schema","tag-create-database","tag-create-schema","tag-database-developer","tag-drop-database","tag-drop-schema","tag-free-code","tag-john-f-miner-iii","tag-sql-server","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/637","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=637"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/637\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=637"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=637"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=637"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}