Microsoft Database Fundamentals - Complete 7-Hour Course [MTA 98-364]

[Music] hello and welcome to this course on database fundamentals this course is specifically designed to help you to succeed in the microsoft mta that's microsoft technology associate 98364 database fundamentals exam success in this exam means that you can actually describe yourself as a microsoft technology associate my name is toby and i'm your instructor on this course and before we get into the course itself i'd like to spend just a few minutes giving you some very important background information first of all there is some official material related to this course it's on the microsoft website and we're going to take a look at that right now one of the main sources of background information on this exam is the microsoft website here is the page as it is today note that the information is updated from time to time so you should always make sure to check for the latest you can find this page within microsoft.com by searching on the 98-364 code a key piece of official material is the microsoft book that accompanies the official classroom course you can buy this from online retailers there are also classroom courses available and there are practical samples and examples available on msdn that's the microsoft developer network and in microsoft visual studio one of the important sections on this page is skills measured and this lists the topics that are covered in the exam this list basically also shows you the list of topics that we're going to cover during this course so it's a good idea to have a quick read through there to see the kind of things that we're going to be looking at it also explains to you how the marks in the final exam are distributed between the topics one of the most important points to remember is that there is no practical work involved in the exam you will only need to answer multiple choice questions however during this course i will show you worked examples based on some of the key points you need to know i'll be using microsoft sql server if possible try to get access to an installation of sql server 2008 or sql server 2012 so that you can try some of these things out for yourself after each video there will be some exercises where you can test your knowledge of the material that i've just covered make sure you work through these exercises carefully if there are points that you didn't understand take some time to watch that part of the video again after the last video we'll be looking at a couple of sample exams and that's your chance to see if you're ready for the real exam where you'll need a score of 70 percent or better to pass so that's the introductory material it's time now for you to get started on the first section please join me for that [Music] hello and welcome to mta 98-364 and the first section is 1.1 understand how data is stored in tables first we're going to look at the objectives for this section the objectives are to understand what a table is and how it relates to the data that will be stored in a database to understand columns and fields and to understand rows and records they're the objectives of the section but first we need to look at some general concepts and questions so the first question is what is a database a database is an organized collection of data typically stored in electronic format database is actually a term that can be used more generally so even a pile of paper saying a filing cabinet at home could be considered to be a database but in this unit we're really talking about electronically stored databases a database allows you to store organize and retrieve data and databases are usually organized using structures such as files fields rows and columns now let me just explain something that's really quite important here if you wanted to store information say about your contacts things like their name and address and phone number you could actually store that information in a file on a pc maybe just a word document or a text file something like that and then if you wanted to find information such as somebody's phone number you could look in the file and find it but supposing you wanted to store their email address as well and maybe their date of birth how would you find all of the people who have birthdays next month you'd have to go right through the file check everybody's date of birth and find the ones with the date of birth beginning first of next month through to the last day of next month so in principle most types of data you can store in a file and then you can look at that file and go through it and find the information that you want with a database though you're usually dealing with much more complex information and your requirements in terms of both managing the information and finding the information that you want tend to be much more complex so although we can just store information in individual files for example we use databases to deal with much more complex requirements both in terms of storing and managing the data and in terms of retrieving the information that we need now let's look at another aspect of what a database is a database typically has two main components it comprises the files holding the physical database so this is a selection of files on a device and then it also has what's called the database management system or dbms software and that's the software that's used to access the data it's also the software that is used to manage the data to make sure that the structure of the database is kept secure and if you need to make changes to the structure of the database the database management system is the tool that you'll use to make those changes so what is a database management system a dbms it's responsible for enforcing the database structure including but this is not an exhaustive list maintaining the relationships between the data items in the database ensuring that the data is stored correctly making sure that the rules defining data relationships are not violated and recovering all data to a point of known consistency in the case of system failures so the dbms has an awful lot to do if you can imagine that some people are adding data to your database maybe somebody else is changing the structure of the database other people are deleting data and you've maybe got users who are retrieving and updating that data as well the dbms needs to make sure that everything is kept in shape consistent and obeys all of the rules that you've defined now let's consider an example in fact this is one of the examples i'm going to be using throughout the course let's look at a parts database used by a hardware company maybe a company that makes parts and sells other people's parts and has retail outlets online outlets and so on let's suppose that typically in the sort of parts that they keep the data includes part numbers stock levels how many items of that part number have we got in stock what's the price of one of those parts what's the price to the public what's the price to trades people how many of those have we sold who did we sell them to give me a description of one of those parts maybe a picture of one of them as well now nowadays all around us whatever we're doing people have databases with this sort of information in them it's important to organize this data so that it can be used easily and efficiently for many different purposes just think of all the people that might need to use that data trades people who've got to look at what's available and decide what to order before they embark on a project members of the public who want to buy parts perhaps spare parts that they're going to use in their house sales people in stores who are going to make sales of those parts and maybe somebody in the warehouse who needs to check stock levels so let's now look at the basics of how we store this kind of information in a database and the most fundamental part of database storage is the table and what i've got here is a very limited table of information about parts i've included some of the information about parts that i've mentioned already so we have a part number a category a subcategory description and price each of the parts in this database corresponds to a horizontal row in this table so the first part has part number one seven one two two it's in the category of extractor fans and the sub category of extractor fans for bathrooms and the description of this part is monroe's axial 20 watt pull cord bathroom fan and its price excluding tax is 12.25 the second item in this parts table has a part number of 54393 a category of door handles a sub-category of door packs modern and so on now the terminology we use here is very important terminology one of those rows corresponds to a record in that table so the table you can see there has eight records in it at the moment now later on we'll have quite a few more than eight records but there are eight records at the moment each record includes a number of what are called attributes so if we look at that first record again its first attribute is its part number now that part number sometimes called an attribute it's sometimes called a field and it's sometimes called a column don't let yourself get too confused by all these expressions think of the rows as horizontal and the columns as vertical in a very similar way to a spreadsheet i'll talk more about spreadsheets a little bit later on but in this table we have eight records and we have five columns and the columns sometimes also referred to as fields or attributes so for the first record in that table the value of its part number field is one seven one two two for the fifth record in the table what's the value of its subcategory attribute it's door packs modern and what's the value in the price column of the last record in this table it's 1495. so that's tables records and rows and columns fields and attributes let's move on now to queries what is a query a database query is an inquiry into the database so for example how many of those have we sold what is the price of one of those how many of those do we have in stock they're all queries related to this parts database in modern relational databases we create queries using sql which stands for structured query language and in microsoft sql server we use microsoft's extension to sql which is called transact sql or t-sql another term you're going to need to understand is an index what is an index well suppose you've got a table consisting of thousands or millions of records and let's think of that parts table that we looked at just eight rows of just now supposing we had a hundred thousand parts like that and i said to you i want you to tell me how many of our parts cost 14.95 well if you had to go through thousands and thousands and thousands of records looking at the price and checking with its 14.95 it would take a long time now you could say well supposing i sorted everything by price i just go down to where it says 14.95 and count from there it might still take a while but at least it will be a shorter job than the first method of doing it well that's fine if you've sorted the list on price but supposing the next question was to find everything in a particular category or in a particular subcategory and one of the problems you have with databases is that you may want to do queries you may want to get information based on all sorts of different criteria now in order to deal with many criteria we can create many indexes an index helps to speed up retrieval by providing the location of the required item in an equivalent way to the index in a book database indexes need to be created and updated though and their maintenance does add to the overhead of work that the dbms has to do this is an important point index is a great idea you pretty much can't do without indexes when you're working with databases but if you say have a pretty big parts database and maybe a couple of dozen indexes every time you make a change to some data the database system has to update the indexes now that may not be a huge job but it does need to be done every time so it does tend to add a little bit of an overhead of work to the database system and also indexes use up more space now let's look at another term you're going to come across what is a database server now in fact a database server has two pretty much completely different meanings and sometimes people get them confused and interchange them and so on but you're going to hear this term used in two different contexts the name is used in two often confused ways first of all a database server is a powerful computer that is configured specifically to host a database particularly in big organizations where they may have big databases they may have computers or even whole groups of computers devoted to handling their databases these are referred to as database servers they are not used for things like email it's not like a pc you might have on your desk or in the study at home this is a computer that's tuned to looking after databases the second context is the dbms the database software system the database management system these database server management systems are usually referred to as database servers and one example of a database service software system is microsoft sql server that i've mentioned already and that is the main software system that we're going to be using within this course and this is the system that i'm going to use to demonstrate many of the tools and techniques associated with databases now i mentioned earlier on that basically a database has two components it has the storage and then it has the dbms the next question is given that part of the database is all of the stored data how is that data actually stored on a computer on a database server computer well it's held in a set of files and in the case of microsoft sql server data is stored in three different types of file the main body of data what's referred to as the primary files are held in a set of mdf files these are files with an extension of mdf and this can hold user defined objects tables like the parts table we saw earlier views which we're going to talk about later and the system tables the dbms's own tables where it maintains information about things like who's allowed to use the database now in the case of a big database you may well find that you need to spread this storage onto other disks possibly even on other devices maybe over a network secondary files that are used for this additional storage are held in what are called ndf files so these are if you like extensions this is where you do the equivalent of building an extension onto the house to make a bit more space and then the third type of file is an ldf file and these are the transaction log files we won't really talk about transaction log files until fairly near the end of the course but basically every time somebody makes a change to the data in the database we would normally log that change and we might use that log of transactions to recover from a disaster now we'll look into this in quite a bit more detail towards the end of the course but basically you do need to take copies of the database for security reasons from time to time and sometimes you may take these copies what are referred to as backups and your transaction logs and rebuild your database but more of that later now so far i've talked about databases in generic terms but there are different types of database and i want to look now at the three main types of database the first type of database is what's called the flat type database which is a two-dimensional table consisting of rows and columns and we've already seen a very simple example of this with that parts table that i showed you the second type is a hierarchical database and a hierarchical database is like an upside down tree structure in this tree structure a parent can have many children but a child can only have one parent the third type and the type that is really the most important to us on this course is the relational database it's a similar principle to the hierarchical database but a parent can have many children and a child can have many parents so let's start with a flat type database you can see here this is a simple database it's only got eight parts in it it's only got five attributes so each of the parts we have five pieces of information about but it's a database you could have hundreds thousands millions more parts you could give the parts more dozens more hundreds more attributes now it's a perfectly feasible way of doing things but the trouble with flat type databases is that you don't easily get those relationships so for instance here we've got duplication of the category door handles all over the place and we've got subcategories within that category and so on and what happens is that you finish up proliferating duplicate copies of lots of pieces of information because you don't set up the relationships that are fundamental to the structure of the database so we don't have something somewhere that says there is a category called door handles now when we move on to the hierarchical database we go a good part of the way to solving this particular problem now with a hierarchical database which is like an inverted tree each parent can have many children each child can have only one parent each child can have multiple children itself so this parts database in a hierarchical database would look a bit like this you'd say right category a this might be something like door handles and then you might divide that up into subcategories and say within the category of door handles we've maybe got bathroom door handles and we've got kitchen door handles and within the sub-category of bathroom door handles we might have part 1265 and part 1762 each of those two may be assemblies with individual parts so they may have children as well so as you can see you can build up a hierarchical structure for your database so we can arrive at the situation where we have subcategory a1 is itself a child although it's a child of only one parent category a it has multiple children and those children in turn part 1762 for example may have its own children but it only has one parent now the problem is that within the database that's fine if we only have one hierarchy let's think about a different database what about a database of movies what hierarchy would you use in a database of movies would you perhaps have a hierarchy based on movie production companies so maybe you'd have all movies at the top and then you might have mgm as one branch warner brothers as another branch or maybe you'd arrange it by genre of movies so you might have a comedy branch and an action branch what would you do about a movie that was an action comedy now the problem is that in real life many of the databases that we create and use are more complicated than a single hierarchy and they need to accommodate many hierarchies so just moving back to this parts database again what if you wanted part 1762 there to actually have many parents and the parents it's got are actually parents in different hierarchies so again with the movie example you might want a movie to be an mgm movie so it's in that branch of the production company hierarchy but it's a comedy so you may want it to be in the comedy branch of the genre hierarchy and that's really where the third type of database comes in the relational database because a relational database basically means that a child can have multiple parents which means that it can feature in a number of different hierarchies so what is a relational database it has similarities to a hierarchical database but a child can have many parents this really means that a child can be involved in many hierarchies it can have many relationships at once and so on this course we're going to concentrate on relational databases so each table in a relational database can be thought of as a spreadsheet i.e a two-dimensional array of data it's easy to add or delete columns or attributes or fields when you're adding or deleting columns you are changing the design of the database if you add update or delete rows you're changing the data in the database you're actually putting new records in deleting records or changing the ones that are already there so adding updating or deleting rows is data maintenance adding or deleting columns is database design and very importantly data can be sorted on any column so one of the features is that if you want to sort data so that you can easily focus on a particular value of a particular attribute you can do that easily with a relational database comparing workbooks such as microsoft excel workbooks to database tables a spreadsheet which will be referred to as a workbook in microsoft excel can contain multiple worksheets this is the equivalent of a database containing multiple tables so a worksheet column is a database field a worksheet row is a database record a worksheet column can contain data that might be blank so you might have a blank cell in a worksheet in excel this would be stored as a null value in a database and in some cases as we'll see later on nulls are not allowed but more of that later and there's one final thing to talk about in relation to the basic understanding of databases and that is calculated values although we generally tend to store values many values in a database you can also have calculated values in an equivalent way to the way that you can have calculated values in an excel workbook in a spreadsheet you can calculate values for some sales from the values on other sales you can do that in databases as well for example we could calculate the price with tax in our parts database based on the current tax rate so given that the price excluding tax was one of the attributes we had in our simple table earlier in this unit it's probably a better idea to store the price without tax and then have some record of the current tax rate and then whenever we want to find the price with tax we can apply the current tax rate calculated values can be generated when we produce a report or when we display something on a screen so that's it for this root unit just let me remind you of the objectives understand what a table is and how it relates to the data that will be stored in a database understand columns and fields understand rows and records so i hope you've got a good handle on all of those now there's a set of questions now for you to answer a little q a session just to make sure that you've understood all of the main points in this unit i'll leave you with those and i'll see you in the next unit [Music] hello again and welcome to section 1.2 understand relational database concepts first of all let's look at the objectives for this section the first objective is to understand what a relational database is secondly you need to understand the need for relational database management systems and thirdly you need to understand how relations are established so let's review some fundamental relational database concepts first of all a relational database comprises tables each row in each table corresponds to one record each column in a table corresponds to one attribute of a record and we establish relationships between the tables as the sort of building blocks the skeleton of our relational database we're now going to talk about some of the language elements of relational databases i mentioned already that database objects are divided into two broad categories we have the storage of the data itself and then we have what's called the programmability so we're talking there about the database management system that enables us to interact with that stored data now let's concentrate now on storage and as i mentioned already the data in a relational database is stored in tables and there are various very important aspects of how the data is stored the first aspect i want to look at is that the data is stored in tables in various types and types can be built-in types or user-defined types now what i'm going to do is to show you an example of some stored data and i'm going to do it using sql server 2008 release 2 or r2 as it's often referred to now i'm going to use a particular interface to do this and i'm going to explain this interface in a little bit more detail just in a little while but first of all let's have a look at a table and the sort of data types that you're going to see in relational database tables now what you can see here is the microsoft sql server management studio ssms is normally referred to and i'm looking at one of the sample databases that's used by microsoft sql server now one of the tables in this database and there's a long list of tables is the one that i'm pointing out there humanresources.employee so we're looking at the employee table for a company and on the right the upper list here is a list of the pieces of information that is the attributes or fields or columns of information that is stored about each employee and the first one the one that's currently highlighted is the business entity id now that has a type of int and in is short for integer i'm going to tell you a lot more about types a little bit later on but if i click on the drop down there you can see all of the different types that are available and there are many of them but this one is of type int now there's also a check box there it's not checked in this case it heading on that is allow nulls and i'm going to talk about allow nulls a little bit later on but let's move on down through some of the other column names we've got a column called national id number that one's type is n of our chart with 15 in brackets that's also not got that last checkbox checked now if you look down through this list of fields you'll see that they're about a dozen or so fields each of them has a data type some of them have allow nulls checked and some of them don't one or two of the more important ones here are birth date which is a date data type and another important one is hire date which is also a date data type now it's very important when you're creating tables that each of the attributes each of the fields in a table has the correct data type and we're going to be talking about data types in quite a bit of detail later on as i mentioned but at this stage in the course i'd just like you to be aware of the fact that each of the tables will have a number of fields or attributes and each of those fields or attributes will have a data type so when designing a database you'll be creating tables with the correct data type for each of the attributes but very often getting the data type correct is not enough so let's suppose that we know that in the employee table we're going to put birth date date of birth for each employee we may also want to limit what that date of birth could be usually when you're designing a database you're also aware of any kinds of limitations on data and perhaps if you envisage somebody sitting at a screen entering data about employees what if they made a mistake and entered somebody's date of birth wrongly and indicated that they were sort of 300 years old or something like that and normally what happens when you're designing a database is that you put rules on each of the fields as well so in the case of birth date you may say well it's a date data type and the date of birth couldn't for instance be before the 1st of january 1900 or it couldn't be later than the 1st of january 2000 or that would imply an employee who was less than say 14 or 15 years old now these rules these kinds of limitations are referred to as constraints and if i right click somewhere within this table definition one of the options is check constraints and you'll see that this particular table has a number of constraints on it for instance we have a constraint here for birth date you can see the details there of the constraint it's got to be later than the 1st of january actually 1930 and also there's a calculation that's done as part of the constraints as well which just makes sure that somebody's at least 18 years old now with various ones of the attributes of an employee we have constraints and let's take a quick look at what sort of options we have for constraints now so constraints are limitations or rules placed on a field or column to ensure that data that is considered invalid cannot be entered so let's now look at the available types of constraint first of all we have a unique constraint and if a field has a unique constraint applied that means it cannot contain duplicate values in general a field with a unique constraint can contain a null value the second type of constraint is a check constraint and the example i used just now on date of birth is an example of a check constraint where we limit the values of the data that can be entered into a particular field the next type of constraint is a default constraint and a default constraint actually specifies the default value in a particular column and the default value is used if no other value is specified for a particular record the fourth type of constraint is a not null constraint and a field with a not null constraint applied cannot contain a null value so it cannot be left blank the next two constraints really go hand in hand but the primary key really is a critically important constraint because the primary key of a table in a relational database is the field or combination of fields that is basically used to order to sort into order the records in the table for storage a table can only have one primary key it must be unique and it cannot be null so this column must contain a unique non-null value a foreign key again corresponding to a foreign key constraint this column points to a primary key in another table and basically the way that we establish relations in a relational database is by linking primary keys and foreign keys so let's suppose that i've got a number of sales in my company and they all specify a part then the part number in the parts table which will probably be the primary key in that parts table that will be specified as a foreign key in all of the sales records that record sales of that part so it's the pairing of primary keys and foreign keys that forms the sort of skeleton of a relational database it's the combination of things that makes the whole database relational and that links all of the records to each other now i just like to point out a couple of specific things about foreign key constraints first of all it's possible for a foreign key to be null but this should generally be avoided as it can jeopardize database integrity and secondly a foreign key can reference a primary key in its own table and this is called a self reference the example i mentioned earlier on was one where you have a list of parts and perhaps one of the parts is an assembly with a lot of individual parts in it then it may well be that for each of the individual parts in that table the assembly that they reference and that they specify a foreign key for will have a primary key of a record in the same table as them because they are parts in the parts table and the assembly is a part in the parts table as well and as i say in that case the foreign key is called a self-referencing foreign key finally in this section i'd like to talk a little about the sql server management studio that i showed you briefly just now i want to tell you a little bit more about it now as far as this particular course goes and the mta 98364 exam you don't need to do practical work and in fact you don't actually need to have done any of the things that are covered in this course in practice but i really think it helps if you've tried many of these things out and most of them are pretty straightforward now you may have access to ssms at work or maybe you've got some other way of accessing it if you haven't then you can install sql server 2008 r2 or sql server 2012 on a pc or a laptop and suitable installation instructions full downloads of free versions of this software are available from microsoft you can also install a sample database the one that i was using just now was the adventure works sample database which is the standard sample database although as we go through this course i'm going to be creating a database and doing many exercises on different aspects of the tools and techniques associated with sql server 2008 r2 as we go so even if you don't download the sample database you can certainly follow along the exercises that i'm going to be doing so if you possibly can get this installed and get it available i really think it's well worth a bit of effort to get it in place ssms itself is a tool for managing the server and its databases using a graphical interface its main features are first of all something called the object explorer which allows you to browse select and manage objects within the database server and then the query analyzer which is a gui a graphical user interface interface to write and execute queries and these two sort of bodies of activities are really the basis of what we do during the balance of the course now let's take a look at ssms and those two aspects of its functionality now when you install sql server and ssms each time you start up you'll get a dialog like this one connect to server you set up a server name in my case i have an installation of sql express for sql server 2008 r2 on a server called xoro and my server name is xoro backslash sql express i connect to that and you can see on the left the object explorer within the object explorer i've got a sort of tree structure i'll talk about various aspects of this as we go through but at the top i've got databases one of my databases is that adventureworks database if i expand that i have a number of objects within that database among the objects within that i have the tables i showed you one of the tables a little while ago but i can now go in and work on any of the tables in my database so we looked at employee before what about person address let's right click on that and have a look at the design and there's the details of the design of that particular table and if i needed to go in and for instance change a data type or change a constraint i use the object explorer to find the object that i want to work on and then generally speaking i work on it on the right so that's really the part of ssms where we work on the database itself so let me just give you a quick example of how this works in practice let's suppose that i go to the databases node in object explorer right click and click on new database that brings up the new database dialog i'm going to give my database a name i'm going to call it test db for test database and then i'm going to click on ok my test database is created now it doesn't have any terrifically useful information in it at the moment but i've got a database and then if i expand that database it'll have loads and loads of default definitions of all sorts of different things but i could go into that and start to create the tables within that database if at some stage i decide that i don't need that database anymore if i right click on it and click on delete i'm given the option of deleting that database now this is a whole database this is not just a table it's a whole database so i have to confirm click on ok and the database is deleted now one of the things i'm going to be doing as we go through the course is to work on that parts database so i'm going to create my parts database now so i suggest you do the equivalent of this perhaps work on a database of your own right click new database i'm going to call it parts db and click on ok and although i haven't defined any actual tables or constraints or data types or anything my database is there ready now for me to work on it and then i'm going to come back to that a little bit later on and start to put tables etc into that database so returning now to the ssms overall usage the ssms is generally used to create and modify databases tables and indexes it can also be used to view and optimize database performance that's not something we're going to be going into in detail on the course but it's a very important aspect of administering a database now the other part of the functionality of ssms and something else we'll be looking at in detail later on is the use of the query analyzer now the query analyzer is a gui based interface that's used to write and execute queries and really there are three aspects of this there is x query which is aquarium functional programming language which is used to query collections of xml data we don't really look at this in detail on the course but it's probably important to be aware that it exists and then we have sqlcmd sometimes just referred to as sql command a command line application that allows sql queries to be written and executed from a command prompt and it's also used as a scripting language now as we'll discuss later on although a lot of what we do is to interactively query a database sometimes you need to run queries in a routine kind of way maybe for regular reporting but also for doing things like installing versions of a database and when you script queries you use this sqlcmd this sql command command line application most of our focus in this course though on queries will be on transact sql t sql and this is the primary means of programming and managing sql server you use keywords to create and manage databases and their components and monitor and manage the server itself when you're using ssms to perform an action or task generally speaking you are executing t sql commands so t sql is very much the focus of the course so let's just have a quick summary of those objectives that we started with understand what a relational database is you should understand the importance of tables and the linkage between them understand the need for relational database management systems this really follows on from what we covered in the first section and finally understand how relations are established i.e by having foreign keys in a table but point at primary keys in another table or in some cases in their own table so that's the end of this section please join me for the next one [Music] hello and welcome back to our course on mta exam 98364 in this section we're going to look at dml or data manipulation language so first of all let's look at the objectives for this section to understand what dml is and secondly to understand its role in databases the first question is what is dml well transact sql t sql is separated into two distinct categories of commands we have the data definition language commands the ddl commands and we have the data manipulation language commands the dml commands is used to manipulate the data in a database whereas ddl is used to define the database so dml does not have as its function defining the database it's only there to manipulate the data if i want to create a table to go into a database i use a ddl command a data definition language command to create a table once i've created the table if i want to put some data into it if i want to insert some data that's data manipulation and i use a dml command to manipulate the data having inserted that data into the table if i want to change it again i'd use a dml statement a data manipulation language statement to update that data and if i want to delete some of the data from that table again that's data manipulation so i have a dml command for that as well now these are the standard dml statements and we'll be looking at these in varying amounts of detail later on to insert data into a table we use the dml insert statement and as the description there says it adds one or more records to a table or view i'm using the term view there i haven't really explained views yet i'm going to come to views a bit later on but for the moment just think of a view as a special sort of table it's actually a virtual table but i'll explain that in more detail later on update is the statement that we use to change existing data to update data and we can update data in one or more columns in one or more records in a table next we have the delete statement and this deletes one or more rows from a table if we want to combine data from two or more other tables put them together maybe into a target table then we can use the dml merge statement we don't look at merge very much on this course but it's important to know that the fundamental function of the merge statement is to merge data from two or more sources together and then finally we have what might almost be called the most important dml statement it's certainly one that you use a lot when you're querying a relational database using sql and that's the select statement the select statement retrieves data from a database and it's the standard statement used in database queries one very important aspect when we are doing any kind of operation on a database is whether the operation succeeds or fails and dml statements in general either succeed or fail now one of the issues you have particularly if you're doing a lot of updates you're doing a lot of dml at once is that sometimes there is a kind of interdependency between some of the things that you're doing so for instance let's suppose a customer places an order you may need to insert some rows into an orders table and maybe update some customer information all at once you've got several things to do they're all related to each other now if one of those operations fails it may be that you don't want the other operations that go along with it to happen at all because otherwise you might finish up with some kind of inconsistency so for example if a customer order involves adding four or five records to various tables if one of those inserts of a record into a table doesn't work you don't want the others to happen either now there are various reasons why the execution of a dml statement might fail for example it might try to violate a unique constraint by trying to insert a record with the same primary key value as a record that already exists or you might be trying to delete a record that would violate a foreign key constraint so other records might reference the one that you're trying to delete now depending on the dml statement you're running there's a whole host of reasons why it might fail and the way that we get around this is by grouping the statements into transactions now by grouping the statements into transactions if a statement within the transaction fails the transaction is rolled back and that means that each of the individuals statements that have already been executed are rolled back as well and by rollback we mean that they are undone so if you've got 50 inserts to do and the 50th one fails the other 49 are all rolled back so the effects of those inserts are undone if on the other hand execution of all of the dml statements in a transaction are successful the effects of all of them are committed to the database when the database transactions are first executed they're in a sort of a a semi-done situation they're almost pending really being committed to the database and when the whole transaction succeeds then each of the effects of each of the individual statements are committed to the database but as i said just now if a dml statement fails the effects of all of the statements within the same transaction are rolled back and the use of transactions fundamentally helps to maintain database integrity and the use of transactions is an essential aspect of maintaining the integrity of a database so finally then let's just review the objectives for this section understanding what dml is well data manipulation language basically comprises those statements that we can use to manipulate data in a database insert data update data delete data and so on and therefore the role of dml within databases is to be able to manipulate the data and then i've also introduced you to the concept of a transaction which is a mechanism that we use in order to group dml statements together and by using transactions we can minimize the chance of the integrity of the database being broken when we're manipulating data that's the end of this section i'll see you in the next one [Music] hello and welcome to this section on data definition language ddl let's start by looking at the objectives for this particular section there is only one objective understanding how t t-sql can be used to create database objects such as tables and views now in fact we're going to look at views in quite a bit more detail later on so really we're concentrating on tables but we are going to look at ddl in general in this unit now before we started let's start to point out something i've actually pointed this out a couple of times already but i can't really emphasize this too much there is no practical element to this exam and you won't be required to execute commands using the ssms interface or write ddl script or anything like that during the exam you will be asked to look at some script and say whether syntactically it's correct or perhaps even choose from a number of options as to what piece of script particularly does but the practical element of the demonstrations that i'm using here is really to help you to understand what's going on and you don't need to be able to replicate these actions within the exam in this particular section when we're looking at ddl and it'll be a relatively long section because there's quite a lot to cover i am going to use both the ssms gui interface to do some operations and we're going to take a look some ddl script as well so there's quite a bit to look at quite a bit to cover let's get started so let's begin by answering the question what is data definition language what is ddl well first of all ddl is a subset of the transact sql t sql language and it deals with creating database objects such as tables constraints and stored procedures now talking there about creating those objects but we can do a lot of maintenance on those objects as well as we'll see as we go through the ssms graphical interface is often used to visually create and execute ddl statements we are going to be using the graphical interface at various points but basically what the graphical interface does is behind the scenes to create ddl statements and execute those ddl statements for you now although it's true to say that you can do most things using the graphical interface there are a few things that you can't so for ultimate power and being able to do everything that's possible you actually need to write ddl statements now you don't need to be able to write them for the exam as i said to you just now but if you were becoming a fully fledged database administrator with full control over ddl then you'd find situations in which you need to write the code when you can't do something with the graphical interface when it comes to ddl script you need to be able to run those ddl script statements this approach has the most power and the most flexibility the other reason for using scripted ddl for tasks is that they're very often used for tasks that are scheduled or that need to be repeated on a regular basis as you'll see when you're doing something using the graphical interface it's tends to be something that's a bit of a one-off task say just to create one table or make one change to one table if you do something every friday or under a particular set of circumstances that occur on a regular basis you probably want to write a script to do that task and then all you've got to do is to run the script to execute the task or tasks that you need to perform now these are the main ddl statements it's very easy to get confused about which ddl statements are which are not they're actually some conflicting information in the microsoft documentation we're going to go through each of these and take a look at what each of them does there's a note at the bottom there under the table note that in the official microsoft sql server documentation there are other ddl statements such as enable trigger and disable trigger and also in various places on the microsoft documentation where they talk about ddl statements they don't list use and delete whereas in some places they do it's important to know what use and delete do but largely speaking the ddl statements that will be referred to will be the middle four create alter drop and truncate but we're going to look at all of these during the course so we're going to start with the use statement you may recall that when i opened ssms before one of the first things we did was to select which database to work on and basically that's what the use statement does it changes what's called the database context to the specified database or database snapshot it's often the first statement executed in a sequence of statements so as an example it will be something like this use parts db that causes the name database to be used so we actually select the database context partsdb now we'll see an example of the use of that statement a little bit later on next we have the create statement and what the create statement does is to create a database object such as a table or a stored procedure now this particular example looks quite complex but i'm going to explain it to you step by step because it's actually a very important example first of all i'm writing here a ddl script and the first statement in the script is the use statement that i showed you just now use parts db the second statement is go and generally speaking what the go statement does is to execute the statements that precede it so if all i had in my script was use partsdb go then all that would do would be to change the database context to partsdb the next statement is the create table statement it begins with create table dbo dot parts [Music] now parts is going to be the name of the table and dbo dot denotes the schema for this table now i'm going to come back to schema in just a moment the opening round bracket there denotes the beginning of the list of attributes or fields of the parts table so first of all we have a field called part id its data type is int and it has a not null constraint now for the moment as far as data types are concerned i'm going to just introduce you to one or two of the simple types and then in the next part of the course we're going to look at these data types in quite a bit of detail int means integer and it basically means it's a whole number so the first piece of information that we have about a part in our parts table is its id it's part id which is a whole number an integer and it's not null not null means we have to have a value in there we can't leave that value empty every part must have a part id and the part id will be an integer the next field is short description the data type here is varchar 50 and with this one we have null stated now what null means is that this can have null values so i could leave the short description out if i wanted to now of course that wouldn't necessarily be a good idea but sometimes when you're setting up a table and maybe when you're setting up data itself you may leave values null temporarily and put in the details later but in this case the short description field can be left null varchar 50 means that the description will be a sequence of characters up to 50 characters in length so the next part of the create table statement is long description varchar 200 null and what this says is that the next field is a long description field where we can enter up to 200 characters and null values are allowed now after declaring the three fields that are in these parts records we close the list with the closing round bracket and in this case we say on primary now you may remember that i mentioned earlier on in the course that you can have file groups you can have your files stored in primary and secondary what are called file groups if you specify as i do here on primary that means that this particular table will be stored in the primary file group i could of course specify a secondary file group and in fact if you leave out this part of the statement altogether then this particular table will be stored in the default file group which could be set to primary but it could be set to a secondary file group the final part of this script is this go so all in all what this particular script is saying is use the parts database and create a new table called parts it's got three fields in each record there's a part id which is an integer and can't be omitted and then there's a short description of up to 50 characters and a long description of up to 200 characters either or both of those could be omitted and i want you to create this table on the primary file group so what i'm going to do now is to actually execute this statement using ssms now once you've opened sql server management studio and connected to your server you need to start a new empty query there is a button immediately above the object explorer that says new query click on new query and you get this sort of whiteboard if you like this blank panel that you can type a query into so for instance i could just type in here use and i'm going to type use parts db notice that as soon as i start typing a little helper comes up and suggests to me that maybe i want parts db since i've typed a p it knows the sort of objects i could specify in a use statement and it's basically trying to help me so partsd yep that's the one i want and then i'm going to do a go now i'm going to do a create table now i could type in that command that i just showed you on the previous slide i'm going to actually copy and paste it because i'm feeling a bit lazy today but the command will be exactly the same as it was there so let me just get rid of those characters and then i'll just paste that in now i've left all the formatting as it was on the slide just now it really doesn't matter when you're creating scripts in this way but there are some aspects of the formatting that are very important and one of the things you'll notice as you start to create this kind of script is that the color scheme very often gives you clues if you've got any kind of syntactical errors in what you've entered now purely the fact here that the right things are blue so for instance the data types are blue and i've got an on that's blue and i go that's blue that's all promising stuff but the other point is that when i try to execute this if there are any syntactic errors i'm going to find out about that pretty quickly pretty soon anyway but let's just quickly go through the elements of syntax here that are very important first of all we must have after the name of the table we must have those round brackets and then for the three fields they're separated by commas so when sql server takes this correct table statement and tries to execute it it'll know that it's got three fields to process it'll understand on with the primary and square brackets and then the go will cause the statements from the previous go to this one to be executed so all in all i should be ready now to execute this script which will select the parts database and then it will create a table in it now to execute a script you have an execute button here so let's click on execute and see what happens now the good news is that you get a message at the bottom and the good news is command completed successfully so now i should have a parts table in my database well let's go to the right database using the object explorer let's look at the tables in partsdb oh no sign of a table it says tables system tables well very often when you're using ssms when you're doing this kind of operation you will need to refresh it and the refresh button is that one up there it's on the same line as the word connect it's got a screen tip there refresh click on refresh now let's go back into parts db tables there it is there's my parts table created now let's take a look at what's in it now the parts table at the moment doesn't have a huge amount in it and i'm going to add something else to it in a little while but let's just expand dbo dot parts and we can see that under normal circumstances we could look at the columns the keys the constraints triggers indexes statistics let's have a look at the columns in this case there are three columns part id which has data type of int and not null short description varchar50 now long description varchar200 no exactly what i specified in the create table statement so that in a pretty basic form is how you create a table using the ddl create command in sql server 2008 r2 now before we move on i'd like to talk about one thing that i mentioned earlier on that i said i would come back to and that's the schema the objects of a database can be further subdivided into what are called schema let me give you an example in relation to the company that's going to operate this parts database assuming that the company has information about customers about parts about sales and so on they may decide to subdivide the tables in the database into groups that are related to parts so for instance the parts table itself they may then have a further set of tables related to customers so there could be some sort of fundamental customer information in a table and that's something we're going to actually look at later on as well and then there may be another subdivision of the tables of the database saying to those related to orders now partly for permission and security reasons the company may decide to divide those tables into for instance a parts schema a customers schemer and a sales schemer and for example a member of the parts team somebody who is an employee in the parts department may not have the permission to make changes to customer information conversely somebody in the customer team may not have the permission to make changes to parts information now in this way it's possible to separate security within a database by the use of schema and the schema in turn belong to named people or in some cases named roles so you may have somebody who is in charge of the parts schema and effectively indirectly owns all of the tables within the parts schema of this database now provided you have the permission to do so you can create tables but you may only be able to create tables within the schema within which you work the administrator of a database i'm an administrator on this database and hopefully if you're working along with me while i'm doing this you're the administrator of the database that you're working on can create a table anywhere and in fact can create their own schema you actually use a create schema statement to create a schema but by default and provided you have the permission if you don't specify a schema then a new table goes into the dbo schema which is owned by the database owner now i put dbo dot in here just really to emphasize the point that this parts table was going to be in the dbo schema but if i'd left that out that's what would have happened anyway you don't need to know too much more about schema other than the fact that they are these subdivisions of a database and that they are primarily used for security reasons and in order to separate responsibilities but there are one or two other situations which we'll come back to later on and i'll mention one or two other things in relation to schema later as well now when i finished with a script like this one i might want to save it to use it again later for the sort of reasons i talked about earlier perhaps because this is a job i'm going to do on a regular basis and i don't want to have to write this script again or if it's a bit of a one-off job i may just if you like discard the script if i close the window on the right there the query window using the button up at the top close button click on close i'm asked if i want to save this query note that if i do decide to save it it will be saved in a file with an sql extension on this occasion i'm going to say no so i'm closing this query without saving it but of course my parts table is still in place ready for me to use now we come to the next ddl statement which is the alter statement and as that name implies it's where we change the definition of something so this changes an existing database object alter table modifies a table definition by altering adding or dropping columns and constraints reassigning partitions or disabling or enabling constraints and triggers now that involves a couple of expressions like partitions and triggers that we haven't looked at so far but suffice to say if you want to make a change to the definition of a table this is not the same as changing the data in the table it's not the same as doing an update in this we're doing an alteration to the definition of the table then alter table is the command to use and as an example of what an alter table statement would look like let's suppose i want to alter the table parts and if i wanted to add a new column i would write something like add price net with a data type of money now a couple of things to say about this before we actually try it using ssms one of them is that the money data type is a very special one it is locale specific so depending on where you are in the world this money will be your type of money so if your locale is set to usa it'll be us dollars in the uk it will be pound sterling in europe in many countries in europe i should say it would be the euro and note you don't have to have a constraint this price net field doesn't have a constraint now what i'm going to do is to go back into ssms and set myself up with a new query in which i'm going to execute this alter table command so there's my command ready to run but by now you maybe can see the reason for that use statement because given that i've got a number of databases available here from ssms i must always make sure that i'm working in the correct database so that my database context is set correctly so as a matter of course i will normally have a use statement at the beginning of any of my scripts so let's try that click on and we have command completed successfully let's select the parts table and we better do a refresh let's go in and have a look at columns and you can now see that we have a price net column with a data type of money and by default null values are allowed now i'd like to mention one other thing here before we move on if you look at the script on the right alter table parts at the end of the line add price net money there's a semicolon and you may say well i haven't seen that semicolon at the end of any of the other statements and it's true that you haven't in what's called ansi standard sql all of the lines with commands such as add in this case a column or something like a select or an insert or an update in dml we'll have a semicolon at the end to denote the end of the line within microsoft's implementation of sql t sql semicolon is optional and very often in the microsoft documentation the semicolon is not shown in fact it's usually not shown although sometimes you see the semicolons generally accepted good practice in the wider world is to put these semicolons in but you don't generally need them when you are working within microsoft sql server and using t sql i sometimes put them in i sometimes don't it's a bit lazy really but in some ways it's probably good practice on a course like this to just make the point that in the microsoft implementation you don't need them but if you put them in they're not wrong and if you leave them out that's not wrong either but if you're using sql in another context using a different database system non-microsoft and there are of course several to choose from it's very likely you would have to put those semicolons in and there is a mover foot to encourage people to always use those semicolons for standardization reasons the next edl statement we're going to look at is the drop statement and the drop statement removes an object from the database if other database objects depend on the one being dropped then the drop will fail so for example if the object being dropped is a table which has a relationship with another table and objects in the other table refer to objects in this one that drop would certainly fail now we're going to demonstrate the drop here using an example first of all the example i'm going to use is going to be drop table parts so i'm basically going to delete the table that we just created and altered follow of course with a go statement so let's go back in and create a new query to execute this within our database now notice that i've still got that old query there the one with the alter table statement in it what i'm going to do is i'm going to copy that and i'm going to create a new query notice that i can have two query windows open at once i'm going to paste that in there and i'm just going to edit it and i'm going to say drop table parts and i'm going to delete that line and that's all i really need to do so it says use partsdb go drop table parts go let's try and execute notice that when i click execute it will execute the query in the window that is selected that's the one that's highlighted here with the drop table statement in it so let's execute that command completed successfully but let's click back on tables do a refresh and of course the table parts has gone so let's now see what's great about these scripts if i go back into the other script i've copied the content of the create table command in there i'm going to paste it in here and now look at what i've got use partsdb go create the table and then do the alter table so in this case what i'm doing is having deleted the parts table i'm now going to recreate it but i'm going to do the create and the alter in the same script so let's click on execute for that that's fine if i do a refresh i'll find that my parts table is back and of course it's got all four of its columns let's go back again let's go back and drop it again so let me run that command to delete it again execute again do a refresh table's gone now let me go back to the other script again and of course what i could have done here instead of create table and alter is i could have just put the price net definition in the original create table rather than doing it as an alter so now i can take out the alter table statement and just to create table with the four fields in it click on execute do a refresh there's my parts table and there i have my four columns and of course i didn't need to use the alter table on this occasion so i hope you get a good feeling from that of how these scripts work and how we can either do a succession of statements or in some cases merge them into one etc next we're going to look at the truncate statement and the truncate statement deletes the rows from a table and frees the space now the truncate statement is a bit of a combination of manipulation and definition the manipulation part is that it does delete all the rows from a table so you will lose any data that's in the rows of the table and it does free up the space that was used by those rows in the table truncate can only delete all the rows in a table you cannot be selective you can't say delete all the rows where the part id is greater than 5000 or something like that it can only delete the whole lot having said that it is a fast and efficient way of deleting all of the rows in a table and truncate can only be used against tables that are not referenced by a foreign key if another table in the database references the table you want to truncate using a foreign key then you cannot use truncate now if you need to truncate a table here's an example of a truncate statement truncate table parts and that's pretty much all there is to say about truncate however i will just mention delete in fact if you look at the official microsoft course documentation on this course delete is included in the list of ddl statements and in fact there is a comparison in the course documentation between truncate and delete as both being ddl statements now i think most people would say that the delete statement is actually a dml statement a manipulation language statement but it is useful to look at this comparison the delete statement as opposed to the truncate statement deletes rows from a table but does not free the space they contained and note that with a delete statement the delete operations may fail due to foreign key violations so if you're trying to delete something where there is a reference to that something one of the rows in the table you're trying to delete that row but it's referenced elsewhere the delete may fail now the typical format and syntax of a delete statement i can show you with a couple of examples we are going to look at deleting a little bit more detail later on but if you said delete from parts that would delete all of the records in the parts table but you can delete selectively with the delete statement so for instance you could have delete from parts where part id greater than 20 000 for example now before we move on there's just one other thing i need to briefly mention to you and that is that apart from the tables and as we'll see later views and other objects in the databases that we work on in sql server there are system tables and system views that are actually maintained by sql server itself i'll take a very quick look at those now now these are maintained pretty much in the background you won't necessarily have any knowledge of them while you're normally working but it's important to know that they're there and sometimes they can be very useful for establishing properties of the system certain things about the presence or absence of objects and so on now as usual you don't need to be able to demonstrate practical use of these things but particularly when we look at views later you'll see how useful they can be in terms of looking at a database and some of the properties of the objects within it now the system views include things like sys dot columns sysdot databases system indexes and things like sys.triggers systock views which give you good information about the overall sql server system that you're working in and the system views belong to a special schema called the sys schema in sql server so let me illustrate this with a very straightforward example in the object explorer on the left here under databases i have three databases i've got this one with the name beginning essay i've got the adventureworks demo database that we've looked at a couple of times i've got partsdb that we've created and then under system databases we have four very important standard system databases master model msdb and this one is just tempdb now if i go into one of the databases so if i go into parts db and click on views to expand it and then system views you'll see the long list of system views that are available and it is a long list and one of the views one of the ones that i mentioned to you just now was sys dot databases so if i go back to sys.databases and open that up it is effectively a virtual table so if i look at the columns within that virtual table so each record within this has these columns one of the columns is name now let me create a new query and what i'm going to say in this query is use partsdb and then i'm going to say select name from sis dot databases and then execute and what i see is a list of the names of the databases that are in the sys.databases view and it is that list of seven databases that i pointed out just now so within the partsdb database i have access to the system views and those system views each contain valuable information about each database that i have access to but also it will give me information about every table and every view in every database every column every constraint and so on so in the background to all the work that you're doing on your databases in sql server you have sql server itself maintaining this very complex set of information in terms of system tables and views which in turn is the way that the rdbms the relational database management system keeps control of everything and makes sure that your databases work so finally let's just have a quick review of the objectives or the objective for this particular section this objective may include but is not limited to understanding how t-sql can be used to create database objects such as tables and views you should have a good idea now of what each of the ddl commands does we've looked at at least one example of each don't worry too much about views and stored procedures we'll look at those in some more detail later on note the sql server help gives you the syntax in much more detail and exam questions will often test your knowledge of the syntax so make sure you check this out in detail take some time to look at the help and make sure that you understand the syntax of each of these commands we'll be doing more later but don't underestimate how much you need to know about these statements do some extra reading if you possibly can that's it for this section i'll see you in the next one [Music] hello again and welcome back to our course on mta exam 98-364 in this section we're going to start looking at data types in general we've already looked at data types briefly earlier on in the course but in this section we're going to go into data types in quite a bit of detail and the objectives for this section are shared with the next section part two on data types there's quite a lot of quite detailed information to be aware of in relation to data types so in these two sections we're going to cover quite a lot of material so let's get started first of all let's look at the objectives which is shared between the two sections first of all to understand what data types are secondly to understand why they are important and thirdly to understand how they affect storage requirements so let's look first of all at what a data type is a data type is an attribute that specifies the type of data an object can hold now in doing that it also determines how much space is used to store the object which is usually measured in bytes now there are various data types and the large number corresponds to the large number of types or sorts of data that you need to be able to store in a database so for instance you need to store strings of characters such as people's names so if i want to store names like john smith and ann brown in a database i need to use a data type that is suitable for storing those strings of characters now we've already seen fields in records such as customer id and full name and net price each of these had a data type and the data type was chosen not only to be able to store the type of data that was going into each of those fields but also to store it in an efficient way another example would be a currency value such as a price the example we saw in the previous section for net price for apart use one of the currency values which was a money data type very importantly we need to be able to store date values and in many cases along with a date value or in fact independently of a date value we may need to keep a time value as well so for something like a date of birth we very often know somebody's date of birth without knowing their time of birth whereas in other situations we may need to know explicitly the date and the time that something happened very commonly we need to be able to store counted values so these are whole number values that correspond to how many of something we have or how many of something occurred then we also have numbers stored with scientific precision such as dimensions where we may have a very large number of decimal places to store now what we don't do is use the same data type for all those different types of data each of them has one of a selection of specific data types that are suitable to that type there are many other types of data as well and it should be possible to store any type of data in sql server so this includes things like images pictures and so on now with sql server there is a very considerable list of what are called the built-in data types the ones that you can use out of the box with sql server money is an example of a built-in data type another example is int you may also remember in the previous section we saw something called varchar you can define your own data types these are called not surprisingly user defined data types but primarily here we're only interested in the built-in data types now the data types are organized into categories one thing i need to point out to you here is that depending on which microsoft documentation you read the list of categories does vary to some extent the list i'm going to show you here is the list that's in the official microsoft course documentation for this course but as i'll show you in a moment that's not consistent throughout the information that microsoft provide having said that the data types that you are likely to be asked about in the exam are in the first few categories so these inconsistencies shouldn't really be a problem to you so let's take a look at these categories first of all exact numerics approximate numerics date and time character strings what are called unicode character strings binary strings and then other data types now the categories include within the course documentation clr data types and spatial data types and some of these rather specialized ones i'm going to briefly mention later on but it's really data types from that first half a dozen categories that you need to be aware of in some detail now since there's actually quite a lot of detailed information associated with this part of the course i want to give you an alternative source for some more supportive and helpful information on the subject of data types if you google the term sql server data types which is exactly what i'm doing here one of the entries that should come up is one from msdn the microsoft developer network msdn.microsoft.com and it says data types transact sql msdm microsoft click on that that brings you to this page you can see that this page relates to sql server 2012 but if you click on the drop down for other versions if you choose the 2008 r2 version it's actually very very similar to the 2012 version but you'll see this very helpful page that not only gives you a list of categories which as i say is actually a shorter list than the one that's provided as part of the microsoft course material but it also gives you links through to each of the data types so for example there's a link here to date and time types you can click that and get a lot more information about date and time types and for each of the date and time types as we'll see in a moment there's another link through to a lot more detail about each of these now unfortunately you do need to know many of these types pretty well you need to know quite a lot of detail about what they can do and how much storage space they use and so on i'm going to go through most of that material on the course here certainly the parts that i would anticipate you needing in the exam but it really is a good idea to go to that section of msdn.microsoft.com and read microsoft's version of this as well because you really need to have quite a lot of this information which unfortunately is quite detailed in your head so let's just briefly go back to the data type categories list here you'll see that the first half dozen categories agree with the ones that i gave you and then it just says other data types and if you click through to other data types it actually gives you a slightly different list anyway but hopefully from this you can see an alternative listing of the categories and as i say very helpful links through to each of them to give you more detail and an alternative approach in some cases to explaining and describing what each of those categories can do now before we start to look at these categories in detail i'd like to look at some of the most commonly used data types because this will introduce you to some of the factors that need to be considered when choosing a data type one of the most straightforward data types is the money data type that you use where you need to store money or currency values this is a fixed precision data type which means that it basically always offers the same level of accuracy and it also relates to the locale in which you operate so for example if you're working in the united states the money values that you're storing would be considered to be values in u.s dollars in the uk pound sterling and in many countries in europe it would be the euro the date time data types are used to store dates and times or in some cases just states there are many different forms but the two main ones we use are date time and date time too in many cases where you have alternative data types the main reason for choosing one over the other is that you might need more or less precision and that correspondingly you may need more or less storage space for each value there's no point in storing something to a very high level of precision if you don't need values recorded to that very high level of precision another good example of the trade-off between storage space and precision or range of values comes with the integer data types if you know that an integer value can only be a very small number say up to a hundred then you can use one of the integer data types that uses a very small amount of storage space if you need to store integer values whole number values in the thousands and thousands of billions of trillions and so on then you need one of the integer data types that can store extremely large numbers varchar is one of the data types that we use to store character strings and there is a related set of data types the n version n varchar for example so if you're going to use characters from non-english character sets you may need to use the n varchar type data types instead of the varchar type data types to store true and false values yes and no values apart from actually storing them as the words true and false or yes and no as character strings you can store them in a bit data type sometimes referred to as a boolean data type where there really are just those two options true or false one or zero and finally in the commonly used data types we have the scientific the float type data types where you may need to store very large or very small numbers and it may not be possible to store those numbers exactly but you can store them to a high level of precision so there's some of the most commonly used data types and some of the factors that are considered when choosing data types let's start by going now through the exact numerics i'm going to fill in the detail of one or two of these in just a moment but let's go through the list first the first type we have is the bit type the name is on the left its use is summarized in the center column in that table and then the right is the amount of storage space that's required now the bit type is used to store either a one or a zero value and it can be used to store things like true or false yes or no if you actually want to store a number a whole number the smallest data type you can use is tiny int which can store an integer value from 0 to 255 but only needs one byte of storage to store an integer value next up from that we have small int that can store a value in that range minus 32 768 to plus 32 767 that requires two bytes now i'm not going to read out all of the numbers from now on because some of them are incredibly long numbers but you can see the values there you can check them on the msdn site in fact we're going to look at one or two details in just a moment but as you can see small int 32 000 ish plus or minus int becomes the two and a bit billion range plus or minus and then when you get to big inch you've got numbers in the millions of trillions and so on now you do need to know in the exam not necessarily what the largest number you can store in a begins is but given a range of numbers you would need to be able to choose between those exact numeric integer types so for instance if in the exam you're told a value can be between 0 and 100 you know tiny int will do whereas if you're told that it can go up to a thousand trillion then you know you're going to need bigins we then have two other data types very important also exact numerics but we have numeric and decimal and these are basically the same thing although the names are different the actual range of numbers that can be stored is the same and i'm going to come back to new american decimal in just a moment we then have two money types we have small money and money the reason one's called small money is fairly obvious there it can store a value in the range minus 214 748.3648 of your currency dollars pounds euros up to plus that same amount almost and then you have the money data type that can store a much bigger range now with both of those data types the precision is four decimal places and for a money data type you need eight bytes of storage and for a small money data type you need four bytes of storage now let's look at some specific details about some of those data types int is the primary whole number data type it's the one that's used the most often that doesn't mean it's always the right one to use but it is used very often where you are using the bit type to store a true or force or whatever the one or zero values correspond to true is one and false is zero for decimal and numeric data types there is a particular syntax decimal and then you can specify a p optionally and also additionally optionally specify an s p is the precision and that's the maximum total number of digits that can be stored if you specify p it must be at least 1 and at most 38 and the default value if you don't specify p is 18. so if you declare a data type to be decimal without a number after it at all then that means it will hold up to 18 digits in total that's totaling the digits before and after the decimal point the optional second argument there the s is the maximum number of digits to the right of the decimal point the least you can have is zero the default is zero and the maximum is p so if you just have a decimal open round brackets 10 that will give you a decimal number with a total of 10 digits and that would allow for none after the decimal point and d4 is zero if you had 10 comma 7 that would be a maximum of 10 digits with 7 to the right of the decimal point and so on and then finally as i pointed out just now both money data types are accurate to one ten thousandth of the monetary unit they represent so that's four decimal places now we come to the approximate numerics these are the data types which don't guarantee to store exactly the number you want them to store but they store numbers to a very high level of accuracy and of course particularly in scientific applications you don't necessarily have exact numbers to deal with anyway there is a type called float and if you look at the range that for each of these types you'll see you've got a range of negative numbers and a range of positive numbers and in each of those ranges you're talking about very large numbers to very small numbers so if you look at float the negative numbers go from minus 1.79 times 10 to the power 308 down to minus 2.23 times 10 to the power minus 308 now a number times 10 to the minus 308 is a very very very small number a minus 2.23 times 10 to the minus 308 is a very very very small negative number the positive range is pretty much the same and you can see there that when you declare float you have the option of declaring n which is the number of bits that are used to store the mantissa of the number that is the actual number part rather than the exponent the power on the 10 to the power now by default n is 53 which gives you a very very high level of accuracy but you can have n declared as any number between 1 and 53 optionally real numbers only occupy 4 bytes and give you the range you can see there in the positive range it's 1.18 times 10 to the minus 38 which is a small positive number up to 3.4 times 10 to the plus 38 which is a large positive number so they're the approximate numerics float and real now i'm not going to keep doing this but do take the time if you possibly can to go to the relevant page from that page i showed you just now in msdn and look through these data type definitions and many of these will give you more information than i'm able to give you in the time available about each of these data types and in some cases in this case for example explains exactly how to work out how much space a float number takes up now you're not going to be asked very complex questions like this in the exam but it is important to know basically what float is and basically what real is and potentially to choose which of them you'd use in a given situation so it's well worth doing that extra bit of reading in readiness for the exam now the last category we're going to look at in part one are the date and time data types there are six and the last four in the list the ones marked with an asterisk are the ones that you should be using now for alignment with sql standards but in fact you'll see all six of them in use with x sql server now i'm going to go into the detail of one or two of them in just a moment but it's important to recognize that they all actually do different things now let's start with the second one there small date time that's one that needs four bytes of storage and it stores a date in the range january first 1900 through to june 6 2079 and although it stores a time it only stores a time to an accuracy of one minute there are no fractional parts of seconds available and even if you store seconds the number is rounded to the nearest minute so small date time is pretty good when you need a date and a time to the level of a minute once you need a higher level of time accuracy then you need to look at one of the other data types such as for example the first one date time now the range of dates for date time is much better it starts january 1st 1753 goes right to december 31st in the year 9999 when i believe that any computer systems we're developing now will probably either have stopped being used or certainly be out of support by them and then you have other alternatives depending on needing a better date range more accuracy with the time and so on so for instance if you look at date time 2 right at the end there it goes from the year 1 the 1st of january through to the year 999 31st of december and the time can give you a very high accuracy level in terms of the seconds you can see that you've got basically seven decimal places of seconds there one of the other important points here relates to date time offset the second one from the bottom because that is the one where the time that saved is time zone aware so depending on where you are in the world that date time is the one that is time zone aware and the others aren't now i'm not going to go through each of those in a high level of detail but i just want to point out a couple of things first of all if i take one of these let's say take the date one i can see a full definition of what the date data type does it gives me the syntax the usage gives me a default string literal format tells me what the range is and so on describes how the range works in each case and it gives me precision scale how much storage space it uses which calendar it uses whether it's time zone offset aware and so on now that information is all available from that same part of msdn as i showed you earlier on so i really can't emphasize too much how important it is for you to spend some time reading through the definitions of these data types on the msdn site but here's just a few more points to bear in mind some of which i've mentioned already for dates in the range from the year 1 to the year 99999 you can just use the date data type basically the date data type saves that date as a sort of compressed character string and it only uses three bytes of storage to store that date use date time when you want time of day as well and that gets you down to the accuracy of a third of a hundredth of a second so that's quite a reasonable level of accuracy if you need more time precision you can use date time to which gives you seven decimal places of seconds or an accuracy of a hundred nanoseconds if you need time but you don't need seconds you can use small date time as i mentioned just now which is accurate to the minute date time offset is similar to date time 2 but that keeps track of time zones and finally for time of day alone based on a 24 hour clock you can use the time data type so that's a sort of checklist of which type to use in each situation but it's a very much simplified checklist and you do need to make sure that you know enough about each of those data types to make the right choice when you're designing a table and you've got to choose which data type to use for a field that's going to store a date or a time or both so that's the end of part one don't forget the objectives for part one and part two but of course we won't have completed working through these objectives and still we've finished part two that's where we're going next please join me for that [Music] hello again and welcome back to our course on mta 98-364 database fundamentals in the previous section part one on choose data types we looked at choosing data types in this section part two we're going to continue doing that let's start with a brief review of the objectives we've seen them already these objectives may include but are not limited to understanding what data types are understanding why they are important and understanding how they affect storage requirements now we're going to pick up where we left off from that previous section and in this section we're going to start by looking at character strings now although in principle character strings are pretty straightforward data type there are a couple of complications that it's important to understand so first of all let me tell you what the character string data types are first of all we have the char data type char and this is a fixed length non-unicode string data type now you use this for string data things like people's names and having a fixed length string means you're allowing a certain number of characters to hold that string sometimes the string may always be a certain number of characters for instance a part number for a vehicle may always have 10 characters in the part number therefore you need to assign a field with 10 characters as a fixed length field to hold that character type if you don't have a fixed length of string or where the length of string can vary considerably then you'll probably use the second data type which is varchar which is a variable length non-unicode character string data type now non-unicode i'm going to explain what unicode is in just a moment there is a third character string type text and this data type is actually going to be removed in future sql sql releases it's useful to know that text exists but as a rough correspondence it's like varchar max now i'll explain what varchar max means in a couple of minutes time so primarily we're concerned with char which is fixed length non-unicode string data and varchar which is variable length non-unicode string data now the next set of types we have are the unicode character strings we have an n char which is fixed length unicode string data n end varchar variable length unicode string data and end text another data type that's going to removed in a future sql release and instead of ntx if you were designing a new database now and creating a table in which one of the field types you needed to use variable length unicode data you wouldn't use n text you'd use envarchar max instead and again i'll come back to the meaning of that in just a moment now let me cover some of those specific points about character strings first of all with regular that's non-unicode character strings we use one byte of storage for each character which allows up to 256 possible characters now within the character system that's used for normal english text 256 characters allows for the numerals numeric digits standard punctuation characters uppercase characters a to z a to z lowercase characters a to z a to z and various other special characters but it doesn't allow for all of the character sets for all of the languages around the world so if within your database you need to be able to keep text that includes for example chinese characters or some of the eastern european languages you need to use a much larger character set for this reason we use what's called the unicode system where for each character for every individual character within a string of characters we use two bytes to store that character now that allows us to use 65 530 different characters in our database which really does cover the vast majority of other alphabets and character systems including things like arabic hebrew japanese and so on so using a unicode character set you can handle just about any character that you need to if there are other characters that you need then you can use special symbol fonts and there are various ways of using additional characters as well but that's really outside the scope of this course the most important thing to realize is that if you're storing regular english characters and this includes many standard european languages then standard non-unicode character strings are fine if you need to store a more extensive set of characters then you may need to use unicode data types unicode can store characters from virtually any language now one thing you'll need to do when you're dealing with characters is to assign values to character strings and generally speaking we do this by enclosing them in single quotes so for example if i wanted to give the first line of my address in a database as this is the first line of my address i would do that by enclosing it in single quotes if you need to specify a value for a unicode character string variable you do the same but you put the letter n in front of it so if i was using a database where the first line of my address was to be stored in a field with a unicode data type the way i would actually declare the literal value of that field in order to store it would be by writing n followed by the first line of my address in single quotes so that's the significance of non-unicode and unicode so let's now look in some more detail at the char and n-chart data types these are the fixed length data types and you use them when the sizes of the column data the field entries will be similar for all records or rows in a table to define a fixed length character field you use a declaration like cha n or n char n now as usual within the char n definition the square brackets indicate that what's there is optional if you do show something then it will be in round brackets and it will be n similarly for n chart it's an optional number but if it's there you put it in round brackets if n is not specified the default is one but if it is specified the maximum number of bytes is 8000 which means that the maximum value for n for regular data is eight thousand and the maximum value for unicode data is four thousand so if you declare n char four thousand that can store four thousand unicode characters which requires eight thousand bytes of storage now let's look at the varchar and n varchar data types these are the variable length data types you use them when the sizes of the column data or field entries will vary considerably or could be very unpredictable to define a variable length character field for non-unicode data you put varchar with again optionally a length and the length the vertical bar there indicates an either or you can either give a value of n or you can specify max and the same argument options available for nvarchar the maximum value for n is 8 000 for regular non-unicode data or 4 000 for unicode data if you specify max that allows up to two gigabytes of data to be stored so if you have a very very large amount of character data either varchar or invar chart you put max in round brackets after it's and the amount of data you can store is up to two gigabytes of data now the last category that we're going to look at in any kind of detail is the binary string category which has three data types binary var binary and image binary is fixed length binary data var binary is variable length binary data image is a data type that is being discontinued it will be removed in future sql releases so now you would use var binary max instead of image the first question you might think of is what do you use this for well in fact you can use it for just about anything if you declare binary with optionally a number after it binary n that allocates n bytes of storage and in that storage you can pretty much put anything one of the types of data that has traditionally been stored in this kind of data type is image data where the sequence of bits or bytes in the image will make sense when you view the image through an appropriate piece of software but the actual bits and bytes themselves are just bits and bytes that get stored within your database within a field within a record and you can use this binary data for any purpose that you can store binary data for so you have the fixed and the variable length options you have a max option on var binary as well as a maximum value of n of 8000 and you can basically use it to store anything some people will do things like embed or compact other data into it so if you have a particular type of spatial data geodata something like that you can pack it in there any way you like and you can use the data in your applications storing it in the database retrieving it from the database and interpreting that data in any way you like so if you like almost binary data types are the ones that you can use to pretty much store anything and the interpretation of what it is is really in your hands now although i said that binary strings were the last category there are some other data types that you may come across and if you go systematically through the msdn documentation on data types or indeed the official microsoft course material on the course for this exam you will come across the clr data type the spatial data type the sql variant data type there's a brief summary of each of those there i believe that it's not very likely you would need to have a high level of understanding of any of these data types and i'm not going to be referring to them anymore but i think it would be useful to you to have a look at those and see what they're used for some of them such as spatial data type pretty easy to understand the sql variant data type is one that's used quite commonly by programmers who are programming applications that interface to sql server databases and it's one that can be used flexibly as a data type but as i say each of these you're unlikely to need to know any great level of detail about have a look on them have a look on mstm find out what they are but don't worry too much about them in relation to the exam now for the rest of this section i'm going to talk about a few concepts related to data types that you need to be aware of and one of them is data type precedence there is a hierarchy of data types a precedence starting at the top with any user defined data types and going down through the many data types that we've already looked at such as sql variant there down through some of the date time data types float real decimal money small money and so on way down to the binary data types at the bottom and this hierarchy is important when we're performing operations on data and data types have to be converted to make the operations possible so when we're combining data in some way we're performing operations on data perhaps mathematical operations or concatenating strings or something of that sort then the rules of data type precedence are important first of all when an operator combines two expressions of different data types the rules for data type precedence specify that the type with the lower precedence is converted to the data type with the higher precedence if you check the list that i just showed you you'll see that for example int is lower in the precedence hierarchy than float so if you combine an int with a float the int is converted to a float and then the operation is performed if the conversion is not a supported implicit conversion an error is returned if sql server doesn't know how to do that and i'll explain implicit conversion in a little while then you get an error when both operand expressions have the same data type the result of the operation has that data type so in that situation if i combine a float with a float i get a float seems obvious but it's an important part of the rules the next general concept that you need to be aware of is collation collation refers to a set of rules that determine how character data is stored retrieved sorted and compared and for many of us that are used to using the english language by default we sort alphabetically for example a to z numerically zero to nine but when you're dealing with unusual data possibly data using unusual character sets you may need to change your collation rules sql server has predefined default collation precedence and provided you have your locale set correctly then generally speaking this set of collation rules this collision precedence will be the one that you want to use most of the time you can override this using a collation clause etc for instance if you were using a character set other than your default western european character set if you were using a character set for a particular reason or in general other than western european character set you may need to select a collation sequence which suited the character set that you were using and for example and this is something that you may well need to do even if you're using your western european character set you may want to specify in a particular case whether sorting is case sensitive or not so for instance does capital a uppercase a count the same as lowercase a in a particular case so it's very important to be aware of collation and to know that you can actually override default collation when you need to now earlier on i introduce you to precision and scale in relation to data types and something else that you need to be aware of although you certainly don't need to know all the rules of this are how precision scale and length affect primarily computations mathematical operations the precision scale and length of the result of an operation depend on the precision scale and length of the input objects there are some sources available via microsoft to set out all of these rules for you you don't really need to know all these rules you really only need to understand the principle that if you were for example multiplying a float by a decimal then the precision scale of the result of that operation would depend on the precision and scale of the two numbers that you were combining together in this case multiplying together so you don't need to know the rules you just need to understand that concept there is a page available on msdn that can explain it in more detail and here it is microsoft sql server precision scale and length transact sql sets out the basic principles a little bit about the hierarchy a couple of examples of mathematical operations and the precision and scale of their results again you really only need to understand the concept not the details so it is worth having a read through that page apart from the mathematical ones that i've already mentioned one or two other ones that are pretty much verging on the obvious really one of them is that if you were to say concatenate two character strings the length of the resulting string would be equal to the sum of the lengths of the individual strings but there are a couple of other examples like that well worth familiarizing yourself with those just to reinforce the overall principle so as we've seen already there are often occasions when we need to do data type conversions and when it's necessary to convert values from one data type to another it is often done automatically in sql server a conversion that is done automatically is called an implicit conversion for example if you multiply the price of an item with a data type of money by the number of items which may have a data type of int the answer automatically has a data type of money however there are some implicit conversions that are not allowed and you need to know how to perform an explicit conversion between data types now there is a data type conversion chart available from microsoft that summarizes the implicit conversions that are available and what you would need to do as an explicit conversion and also some of the problem areas now there is absolutely no way that you need to know all of the detail in this chart i'm going to show you the chart now and you only need really need to know that it exists it's available as a free download from microsoft the best way to find it is to search microsoft.com or just google sql server data type conversion chart it's quite a complicated chart and you certainly don't need to memorize it all however i suggest you take some time to review it and get some of the general ideas from it so let's have a look at that chart now so the download page for the sql server data type conversion chart should look something like this when you get to it click on download follow the instructions and then eventually you'll see the data type conversion chart now you probably can't quite see all of it on the screen here but you should be able to see enough to give you some idea of what's going on note that it's a very very big and complicated chart you have data types along the top binary var binary char and so on same ones down the left obviously you don't need to convert a binary to a binary so that cell is empty but there is a code then for the types of conversion that are possible between the various pairs of data types implicit conversion that's the gray one you can see that many of the pairs are covered by an implicit conversion the black ones require an explicit conversion the white one's a conversion is not allowed and then there are one or two very special cases such as these ones in here with the sort of spidery symbol in the middle and note requires explicit caste to prevent the loss of precision or scale that might occur in an implicit conversion i'm going to talk to you about cast in just a moment but basically that's the chart as i say you don't need to memorize that you need to know that it exists and you need to know that if you needed to you could look that chart up and work out whether a conversion is implicit or not or whether you'd need to do an explicit conversion so let's look now at how we do explicit conversions between data types some implicit conversions are not allowed and you need to know how to perform an explicit conversion between data types there are two functions available in sql server that you can use to perform explicit conversions there is a cast function and there is a convert function the two functions do similar jobs but convert is the more powerful one let's look at cast first of all the big advantage to cast is that it is compliant with what are called ansi standards the international standards so if you were using an rdbms other than sql server then there should be a cast function available the syntax is when you use this function cast and then you specify the source value and the type of output that you want so for example i could say cast quantity as float so i've got a value as a quantity maybe an integer value and i want to make it into a float i would actually put cast quantity as float as another example i could cast an id as a varchar 10. so if for example my id was a number i could convert it to a varchar with a maximum of 10 characters when you're converting to a character string type the default number of characters is 30. so if you are going to convert to a character string type you can use the default of 30 but it's actually good practice to always specify the number now let's take a look at convert convert is specific to t sql so this is a microsoft specific conversion function but it's more powerful than cast and it has one particular advantage the syntax of convert is actually it's the other way around you put the data type first and if there's a length to specify you can specify the length you then specify the expression that you want to convert and optionally you can specify a style a format and this is particularly used in the case of date time conversions so for example if i wanted to convert date of birth to varchar 10 it would look like that and the one zero one at the end is a particular style code that specifies that the code for this conversion will be the usa date format of mm stroke dd stroke yyyy now if you look up on the microsoft site you can find out what all of those style codes are but that's the big advantage of convert and that is that you can specify a style code the thing to be wary of is that the actual placement of the other arguments is the other way around so the data type first then the value that you want to convert and then for date time fields you'd specify a style code so that's it really let's just have a quick review of the objectives these objectives may include but are not limited to understanding what data types are understanding why they are important and understanding how they affect storage requirements we've also looked at things like conversions and collation and precision and scale and length as well that's the end of this section i'll see you in the next one [Music] hello again and welcome back to our course on mta database fundamentals exam 98-364 in this section we're going to look in quite a bit more detail at tables and how to create them we've already created tables before fairly straightforward versions of tables but now that we know all about data types and a little bit more about some of the other workings of sql server we can go about this in a much more professional kind of way now the objectives for this section are to understand the purpose of tables to know how to create tables in a database by using proper ansi sql syntax so let's look at the purpose of tables first of all to provide a structure for storing data within a relational database secondly a sql server database is generally a container for a number of tables a simple database might have just three or four tables a much more complicated one could have hundreds or even thousands of tables you can use queries to retrieve data from these tables and one advantage that a database has over a series of spreadsheets is that the database can parse out redundant data for you now i want to briefly explain what the term pars out means parsing out redundant data means that by structuring the tables correctly and by having the relationships between the tables set up correctly you don't have to duplicate data unnecessarily so you reduce the amount of redundant data hopefully to an absolute minimum which means that you are using the minimum amount of space to store the information that you need now later on in the course we're going to talk about data normalization and i'll talk more about redundancy then now the next thing we need to look at is standards i haven't talked about standards at all so far but you do need to be aware of them it's a good idea to establish unused standards when working with databases these standards should be used consistently throughout your company even if you're working on your own it's a good idea to adopt a set of standards and to work according to those standards as an example there are a couple of popular naming standards that are helpful there is a naming standard called pascal case which starts each word with a capital letter and so far when i've been naming fields for database tables i've generally used pascal case now the naming of these standards is a little bit controversial but there is another standard called camel case which also starts each new word with a capital letter but not the first word so you have terms perhaps with many words included that look like a camel with one two or more humps generally speaking i tend to use pascal case and that's what i'll tend to use in this course both these standards and in fact standards in general assume that you don't use spaces in the names of objects which is a good principle to adhere to use underscore characters if you need separation within a name in general whether sql server is case sensitive or not in terms of naming and objects depends on how it was installed in many installations the naming of objects is not case sensitive so if i called something customer id with a capital c and a capital i and a capital d and everything else lower case it will be treated exactly the same whenever i refer to it if i refer to it in all capitals or lowercase or any combination of lowercase and uppercase characters i tend to work on the principle as i like to use pascal case that i do like to always refer to things using the same case but as i say dependent on your installation you may get away with being a little bit more lazy about whether you're consistent about the use of case but be aware that in some instances and depending on the installation then the naming of objects may be case sensitive and if you called something customer id with a capital c and a capital i and a capital d and you refer to it in all lower case that all lower case name will be considered to be a completely different name and could actually be a completely different object so you can see from that how a lot of confusion could set in now note that when we're dealing with this we're talking about the naming of objects not the data the data of course generally will have case uppercase lowercase within character strings and of course generally speaking that is important so with your newfound knowledge of data types and so on we can now do some more work on our partsdb database we're going to demonstrate table creation first we've done some table creation before but we can do now a much more sophisticated version so we're going to use a very simple database with orders for parts we're going to have a table parts a table that we've created before but this one will have some differences in its definition and design we'll have a customers table containing details of customers and we'll have an orders table containing details of orders now so that this doesn't get too complicated to begin with what we're going to say is that each order will contain an order for one or more units but of only a single type of part so you might have 10 or 20 of a particular part it would be quite straightforward later on to cater for a number of different parts but for the moment we're only going to have a single part in an order although you can have many of that part so the next thing to do is to create the new parts table we could actually amend the existing part table it's quite reasonable to amend the definition of a table in sql server but we haven't so far got any really usable data that we're dependent on so i'm going to delete the existing table and create a new one let's look at the four fields in the new table first of all the part id is a unique five character identifier we'll often use numeric digits for ids but on this occasion we're going to allow alphabetic characters as well so our part id like 1986a would be fine this field is the primary key for the parts table we then have a category field a field with 20 characters that specifies the part category so for example this might be door packs modern we have a description field a text field with up to 100 characters it stores regular english characters for the description of a part we don't need to worry about unicode characters and then finally we have a net price field which will store the price without tax in the local currency so on this occasion i'm going to use the gui ssms component to create the parts table we already have a parts table i'm going to delete that one first now when it comes to using the graphical user interface within ssms first of all i need to make sure i have the correct database open so i need to select the correct database context then i enter the basic details of each field for the parts table make sure i'm careful with data type and also that i'm careful regarding whether i allow nulls or not part id will be the primary key so here i am i'm working within parts db go to the tables go to the parts table that i created earlier right click and one of the options there is delete now we use the term delete here when we're working with the gui for dropping this table there are some circumstances of course in which i wouldn't be allowed to delete this object we've talked about some of those already in terms of its involvement in relationships and so on but on this occasion i should be fine to delete it because i haven't set up any relationships so far so i click on ok and that table has gone so now we need to create a new table right click on tables within partsdb top option there is new table and that opens the table designer on the right now the table designer lets us enter the fields the details of those fields one at a time so we choose a column name a data type and then whether that particular column allows nulls or not and basically when we're setting the table up initially it's a good idea to get the fields in place and then consider any constraints that we need to put into force so i'm going to start with the first field here it's the part id fields here are referred to as columns but as we know that makes no difference now the data type here by default it comes up there with an n-sharp 10 but i want this to be a char 5 and again by default it's allowing nulls now the part id is going to be the primary key so i certainly don't want to allow null so i'm going to uncheck that now and then i can click on to the next column the next column is going to be category this is also a fixed length field this is going to be a 20 character field so again i can go in there now for this one i am going to allow nulls we haven't really clarified exactly yet how we're going to deal with categories i.e when somebody enters a part into this table must they specify a category but if we leave allow nulls as checked at the moment that means they can set up parts without specifying categories it may be that some parts aren't in categories for example so a setting like this one requires a bit of careful consideration it isn't only a technical decision it's very often a business decision as well can you have parts that aren't in categories if you can have parts that aren't in categories then clearly you must allow nulls for that particular column so let's now do the next one which is description this one is going to be a varchar but we're okay for it to be a regular one we don't need it to be unicode and we're going to allow 100 characters there's a similar argument regarding whether we allow those so i'm going to allow nulls at the moment and then net price this is going to be a money field probably ought to be a small money field really because we aren't going to have parts that are that expensive but we don't want to have to change this later on and we're not absolutely sure at the moment so let's go for money and in this case i'm going to insist that if a part is in this table we must have a price so i'm going to uncheck allow nulls so that's the basic content of my parts table in place now as i mentioned just now if we need to apply any constraints then we can apply those later on but there is one thing i'm going to do now which i almost always do unless there's a real reason not to and that is to set up the primary key that's going to be part id if i right click there against part id the top option set primary key and a little key icon that comes up just to remind me visually that that particular field is the primary key for this table one other thing i need to show you is that if you click on the view menu option and click on the properties window you get an additional window on the right where you can set the properties of various objects in this case the object we're interested in is table one and the thing that i want to set is its name i want to change its name default name is table underscore one and i want to change its name to parts so parts close and that's it really now all i need to do is to save my changes so close here at the top right of the table designer do you want to save your changes to the following items yes and now you can see on the left here my new table dbo dot parts and if i click on its columns you'll see part id primary key etc so i have successfully set up my new table so having successfully set up the parts table the next job is to set up the customers table this contains the following fields there's a customer id that will be set up as a unique integer value this will be the primary key for this table the customer name field will be a field name with up to 50 characters we have quite a few overseas customers and the name must be unique now it's worth bearing in mind here that particularly if you communicate with overseas customers perhaps with ones from the far east china japan or any other country where the character set they're likely to use is different from ours you may want the facility to hold things like their name and address in what i'll loosely call the western format and in their own local format so even the customer name you may want to keep a version of that name which uses their own character set now obviously this is a whole field in itself but as a basic principle with this customers table the customer name and the one line of address that we're going to store for the moment are going to be stored as unicode fields but the fourth field the country field we're going to keep in 30 english characters on the grounds that we may use it not only for say addressing a parcel but we may also use it in queries and so on and we want to be able to use the standard western character set english language names for the countries of the world and then finally we're going to have a little field at the end here a discounted field true or false whether this customer gets a discount on prices in reality we'd probably have a discount percentage or something but let's keep it straight forward for the moment now rather than use the gui interface to create this table i'm going to write a sql statement a create table statement and at this point you may want to pause the video and see if you could write a statement to create this table yourself but let's see the statement that i've written and then i'll show you this actually running in the database so here's the statement create a table using transact sql create the table customers the customer id i've said is an int field which allows me a few million customers so i'm reasonably optimistic it's not null you must have a customer id and it is the primary key the customer name is an envarchar50 it can't be null and it must be unique the address one is an nvarchar 60 this can be null the country is a char 30 this can be null and the discounted bit defaults to zero and that can't be null either so let's see if that sql statement actually works okay now i'm in great danger here of turning this into a lesson in ssms which isn't what it's supposed to be but let me just show you something that if you're using ssms you might find useful when you've entered a script like this one what you can do is to actually check the script out by running a parse operation it's on the query menu and if you check it pause it's got a keyboard shortcut of control f5 so you run through your script to make sure that it's happy that syntactically it's all correct it doesn't guarantee it will work but it will certainly find you any initial problems so seems to be okay let's click execute command completed successfully let's go back over here and do a refresh let's have a look at our tables and there's dbo dot customers let's look at the columns there we are customer id customer name address one country and discounted now i think that's pretty good now one thing i want to just point out to you now is that you can also insert data into these tables using sql and if you're working along with me and in particular if you're working on your own database maybe not a parts database then you may want to be able to set up some data now using sql this is a typical insert statement for the customers table that we just created i'm going to be looking at insert tables in a lot more detail later on so if you don't actually want to set some data up yet you may want to wait a little while i'm going to need some data for a couple of the sections that are coming up soon but let's just say that i want to insert a single line of customer data i would use the sql statement insert into and then the table name customers open round brackets and make a list of the fields that list doesn't have to be in the same order that the fields are defined in the table but when we come up with the list of values it must be in the same sequence as the order of the list at the beginning of this statement so the fact that i've got the order customer id customer name address one country discounted means that the values must be in that order as well so here we go i've copied and pasted that command into a new query in ssms i'm just going to execute that query now if i right click on customers and say select top 1000 rows i'll see the top 1000 rows at the bottom and as you can see the right just inserted is now there in place now if you want to set some data up yourself that's a fine way of doing it i am going to set up some data for reasons that will become apparent a little bit later on but the last thing i want to do in this section is to talk about the third table the orders table now with the orders table we have six fields there is an order id which is a unique code with eight characters and that's the primary key on this table the customer id is an integer value that will correspond to a customer in the customers table this must not be empty we cannot have an order that is not assigned to a customer in other words we also have a part id this is a five character identifier for the part that is being ordered so this will correspond to a part in the parts table this also must not be empty we cannot have an order with no parts specified as i said earlier in reality we'd probably allow an order to specify multiple parts but in this case it will be multiple items but for a single part the quantity is a whole number that must be positive this field also must not be empty and the source will be a 20 character field this may be telephone and so on it must not be empty but the default value will be online it may be that when we actually implement an application to manage these orders we'll have a list of possible values to go in here but for the moment the default value will be online and then the shipped status determines whether the order has been shipped or not it'll be true or false the default value was false and again this must not be empty now at this point you may want to try to put together a sql statement to do this create table yourself it has a few default values in it though so what i'm going to do now is to look at the create table statement that i use to create the orders table so this is what my sql statement looks like for creating the orders table create table orders order id is chart8 not null and it's the primary key customer id is an int and it's not null note that i haven't set up these relationships such as the relationship between the orders table and the customer table yeah we'll look at those later on part id is char 5 that's also not null the quantity is a small int and it's not null and now i'm starting to introduce constraints so first of all there's a check constraint it's in brackets it's after the word check and it says quantity that's the name of the field greater than zero so i know that i'm going to store this number in a small link field and i'm going to check that it's greater than zero now of course it doesn't have to be a small infield any sort of infield i could put that kind of check in you can put constraints on whole tables that are composites of constraints on individual fields but that's really beyond the scope of what we want to do here you just need to be able to know how to do straightforward constraints like these ones the source is a char 20 also not null note how we state its default value it's default and then because this is a character field it's a character string field we put to the default value in single quotes and the default value is online and finally the shipped status is a bit data type also not null the default value here since this is a numeric data type the default value is zero with no quotes so that's the create table i used to create the orders table that worked fine for me you might want to try something similar yourself that's it for this section let's have a quick look now at the objectives again the objectives may include but are not limited to understanding the purpose of tables and knowing how to create tables in a database by using proper and c sql syntax that's the end of this section i'll see you in the next one [Music] [Music] hello again and welcome back to our course on mta database fundamentals exam 98-364 in this section we're going to take a look at creating views i've mentioned views a few times already but in this section we're going to find out what views actually are and how to create one so let's first look at the objectives for this section understanding when to use views and understanding how to create a view by using t-sql or a graphical designer so what is a view a view is a virtual table it is made up of a combination of a selection of records from one or more tables a selection of the fields or columns in those rows [Music] and it can be a subset of data from other views a view is basically a query object its data comes from one or more tables either directly or possibly via another view the next question is why do we use views some of the main reasons for using views are these first of all security views can be used to restrict access to some fields or records from what are called the underlying tables for example only the orders for a specific customer if you wanted to give a customer access to information about their orders you could create a view which only showed them the orders for them so it might get most of the information it needs from the orders table but it would only give them information for their own orders a second reason for using views is simplicity in a complex database it might be actually quite difficult for users people perhaps writing their own reports based on the data in the database to work out exactly which information from which tables they need to put together in order to get the information that they need you as a database administrator can create one or more views that make it easier for them to find the information that they need a third reason for using views is analysis within a view you can actually provide summaries and totals so for instance if you were providing views say of the orders for a customer you could actually give a total order value at the end of the view or even perhaps just a count of the number of orders that they currently have with your company another reason for providing views is support it may be that you can provide views to reduce the need for users to write their own complex queries rather than telling users about all the tables in your database you can say to them well i provide you with these views of the database and you can base your queries on these views this is very closely related to the simplicity reason that i mentioned just now so we're going to create a couple of views now there are two methods for creating views one of them is to use the gui in ssms the other way of course is to write a sql command to use some t sql so this first view i'm going to call vw budget parts i'm going to create it using ssms so in order to do that we open ssms and make sure we have the correct database context then i'm going to review the data i've set up in the parts table then i'll right click on the views folder and select new view and then as you'll see we use an add table dialog that appears to select the other tables i've used to use in the new view in this case there won't be any additional tables or views but it's important to realize that you can base a view on many other tables or views as necessary so we'll only need the parts table and the view will include the part id description and net price for all parts that cost less than ten dollars in the parts table so first of all i'm in the partsdb database let's look at tables let's look at the parts table i'm going to right click one of the options there is select top 1000 rows which will list the top 1000 rows for me as you can see i've added probably about 20 or so records there you can see the prices on the right and you can see here part ids categories and descriptions so what i'm going to do now is to go to the views folder right click and click on new view the first thing i do is to choose the tables or views that this new view is going to be based on i'm just going to base it on the parts table so click on parts and click on add and you'll see in this sort of view designer here that a table has appeared it's actually appeared in a little control with a list of the fields in it if i wanted to base this view on more than one table or indeed if i wanted to base it on one or more other views or a combination of those things if i wanted to use functions particularly say if i want to do a bit of totaling or averaging or counting i've got tabs here to select between them on this occasion i only need to base this view on one table the parts table so i've finished with the add table dialog click on close the next step is to select the fields that are going to appear in this view now as i do this towards the lower part of the view designer here you can see that it's actually making a select statement for me and the select statement really is the basis of this view so i said i wanted the part id the description and the net price note that as i click each of those it appears in that select statement the other aspect of this view that i want is that i want it to be for what i'm calling budget items so it's where the net price is less than 10 or less than 10 of your local currency units if you're working along with me now there are a couple of ways of doing this later on when you're more familiar with the where clause you could actually immediately type aware clause at the end of this select statement but in this middle section of the view designer you've got some other options that can help us here for instance we can actually say what order we want the view sorted in whether we want to apply filters and if we are going to apply filters way that we can combine filters now we are going to apply filters in this case we are going to apply a filter to the net price and the filter we're going to use let me just click in there is less than 10 which means that we are going to filter the contents of the parts table so that we're only going to show parts where the net price is less than 10 currency units now right at the beginning of this section on views i mentioned really that a view is based on a subset of rows and a subset of columns and that's exactly what this is because we're only selecting the rows where the price is less than 10 and we're only selecting three of the columns in the table part id description and net price now once i've put my nest than 10 in there if i just click somewhere else you'll see that it builds up my select statement a little bit further and puts in a where clause that says net price less than 10. if i wanted to apply additional filters which would generate additional where clauses i could do that at this point but that's all i need for the moment now i could just close this view then rename it i'm going to show you a different way of doing that if i go to the file menu one of the options there is save and if i click on save it'll actually ask me to give this view a name so i'm going to call it vw budget parts so if i click on ok i've now saved my view with that name i'm going to close it i'm going to close the parts table as well let's go over here to views there is my budget parts view right click select top 1000 rows there are in fact just two items which have a net price of less than ten dollars in this case and there they are one very important thing to understand about views is that they are not in some way frozen in time and the view is very much a dynamic view a representation of the data that's in the underlying tables and views let me change the price of a part in the parts table i'm going to go into edit top 200 rows and this item 21354 timber flooring construction change the price of that item to 8.95 now let's take another look at our view the budget parts view select top 1000 rows and of course now that timber flooring item is shown as well because its net price is less than 10. now the other way to create a view is using t sql so let me show you an example of that i'm pretty sure you better work out what this does yourself create view we give the view a name note that with the command create you must specify what it is you're creating create is used for creating a number of things we've already created tables we can create views as well as other things so create view give the viewer name vw customers usa as is the next essential part of the syntax of creating a view and then we really just put in the select statement so select customer id customer name from customers so it's just the id and the name from the customers table where country is usa so this view is going to show us the usa based customers of our company there it is i've already got ssms to check the syntax for me so let's just do an execute now let me go to views looks as though i need a refresh there it is vw customers usa i haven't pointed this out but you note how each of these objects that i'm creating is getting the default schema of dbo assigned so customers usa select top 1000 rows there are two of them acme metals and pacific hats i don't think i showed you what data i'd put in the customers table that's not very much so select the top 1000 rows of the customers table i only put in three customers one uk based and two usa based so that view seems to work fine as well so finally then a quick review of the objectives understanding when to use views understanding how to create a view by using t-sql or a graphical designer the views we've created here have been very simple but you primarily need to understand the concept of views you don't need to be able to create very complex ones that's the end of this section i'll see you in the next one [Music] hello again and welcome back to our course on mta database fundamentals exam 98-364 in this section we're going to look at creating stored procedures and functions and if you've never come across stored procedures or the concept of stored procedures before i think it's fair to say that store procedures provide the backbone and the database interface for virtually every sql server application in the world really this is really how programmers people who are developing sql server applications interact with sql server very often by using stored procedures and in this section i'm going to show you how to create a stored procedure once again you don't need to be able to do this you won't be examined on this you just need to understand the overall concept and some of the specific concepts involved as well so if you do find this example a little bit more complex than you're used to please don't worry too much about it just try to get the general idea and some understanding of some of the basic terms some of the basic principles that are involved with stored procedures so let's look at the objectives for this section pretty straightforward creating stored procedures for selecting inserting updating or deleting data now the way that this works in practice if i can put it in a sort of programming context for you is as follows let's suppose that you are using a website perhaps an internal website to enter details into the parts table in the parts database a programmer will develop a stored procedure that will take the data that's entered on a web page and create a database record for a part using a stored procedure probably a web designer web developer will develop the page whereby users can specify the fields that are going into that part record and then a user will use that page enter the values for the fields on the page click on ok button and then that stored procedure will be called to actually create the record with those field values now that's a pretty straightforward example but it's probably typical of the sort of example that's being run thousands millions probably hundreds of millions of times around the world every time people are using a sql server based database in order to store data so the stored procedure really is the sort of interface between the user and the database and in this section we're going to look at just one example of a stored procedure but it'll introduce you to the terminology that you need to know and hopefully a little bit more to the idea of how you create store procedures so what is a stored procedure well it's a group of one or more t sequel statements stored procedures are efficient in general if they're written efficiently that is and they can be stored in the database itself that's a great advantage because it becomes a little more difficult to lose your stored procedures they avoid the repetition and potential errors associated with typing t sql commands directly they can aid security they are particularly useful when programming operations on a database as i've just pointed out stored procedures can be made powerful and flexible by the use of structures such as variables parameters flow control and error handling and i'm going to cover one or two of those topics in this section so what we're going to do is to create a stored procedure that we're going to call usp parts details usp is the standard prefix it's user stored procedure and this stored procedure very straightforward is going to find the details of a specified part so just try to visualize what this store procedure is going to do your users are sitting there with a web page a user can enter a part id and the web page will pass the part id to this store procedure usp part details and usp part details will pass back some of the details of this part so simplistically we might enter the part id call the store procedure specifying that part id and then the store procedure finds the description of the part and maybe the price and passes them back to us which we can then display on the screen for the user so the part will be specified using its part id which as we know is a five character field and the value of the part id is what we call an input parameter to the procedure and the details we need will get from the parts table now unfortunately this is a rather multi-step procedure you of course don't need to remember all of these steps or be able to do this yourself but most of the steps involve some piece of information that it's useful for you to have so i really do think you should follow this through step by step if you're working along with it yourself maybe using your own database then create a similar kind of stored procedure itself now with each of the steps i'm going to start by doing a little list of what we're going to do and then i'm going to go through and do that using the ssms gui so this is page one we'll open ssms select our database expand the programmability section of the database right click on store procedures and select new store procedure and what we see then is a template that we can use for our stored procedure now this is the first thing that tends to confuse people because the template is really just a starting point it's really just something to get you started to help you a lot of the template will delete or change or drastically remodel in some way so let's get started i've got my partsdb there's the programmability section within that store procedures click on new store procedure and there is my new store procedure now you may look at that and think well wow well that's done a lot very quickly most of that store procedure won't be with us very long but i do want to point out a couple of common features of store procedures one of which is that if you're writing fairly complex stored procedures it's very much like programming in general you should put in plenty of comments that actually explain what's going on the comments themselves are not executed by sql server they don't cause anything to happen but they will help somebody who's reading this stored procedure to understand what's going on now comments in a stored procedure can be done in a number of ways the simplest way and the way that you can see here is that the two hyphens at the beginning of a line as here where it says template generated that two hyphens at the beginning of a line indicates a comment you can also mark a whole block as a comment by putting a slash star symbol in front of it and a star symbol at the end so if i wanted to make these four blue lines here into a block comment if i put slash star and then after the go i put star slash then everything between those is now comment note that it's no longer blue it's treated as a comment so that's two ways of commenting within a store procedure and you may be asked a question like that in the exam also within store procedures you will use variables now variables if you're not familiar with programming are objects that are there when a program is running that can hold a value that may change over time and in fact the part id that we're going to enter into the stored procedure will be a variable in fact it's a special type of variable called an input parameter but more of that in a moment generally speaking something with an at at the beginning of the name is a variable so at p1 is a variable name now there's a special symbol a pair of at symbols at and if you see something that begins at that's a special type of variable called a global variable but you won't need to use global variables here standard variables are all you need to be familiar with so there's the beginning of our stored procedure so let's move on to step two page two of the instructions here select specify values for template parameters from the query menu and enter values for the following now the first couple of these are really for documentation purposes so the author name the creation date a description and the procedure name which i mentioned earlier on was usp part details we're only going to use one parameter in our stored procedure p1 but we're going to change its name to part id because that is the input parameter to this procedure and we're going to change its type to char 5 because we know that our part ids are char 5 and we're going to set the default value to five zeros in fact it's five zero characters as opposed to the number zero and then we're going to click ok to save those settings so here we are on the query menu we have the option here specify values for template parameters click on that opens up this dialog the author put your own name in there the creation date put in today's date put in a suitable description give it a name now the parameter we're going to use is going to be its data type and the default value now i'll get rid of parameter 2 in a moment i'll show you how to do that click on ok now you can see that we have here part id now within this create procedure section here char 5 got our initial value there we've still got p2 mentioned you can literally go in here and just delete that you don't need p2 you can literally delete it like that so that's the next stage completed now before we move on to page three of these instructions just take a look at the lower half of what we have here starting here what we're actually looking at here is not the stored procedure what we're looking at is some code to create the stored procedure and what we're doing here is specifying the features of the procedure we will actually execute what we have here and right in the middle here create procedure i mentioned earlier on you can create all sorts of things with a create command something else you can create is you can create a procedure there is its name and the rest of this is the content of the procedure so we're actually creating the thing that creates the stored procedure can seem a bit mind-boggling but hopefully it'll make sense to you in a little while from now let's move on to step three now one of the key features possibly the key feature of this store procedure is that it executes a select statement in this case we're using this still procedure to select data from our database so we need to enter the required select statement it will say select category description and net price from the parts table subject to one where condition where the part id is equal to the input parameter so when we specify our input parameter that's the part id we're going to search the parts table on now what i need to do is to type that into the code to replace the select statement that's there already and then i can check the syntax using that parse command that we've used a couple of times already once we've checked the syntax everything seems to be okay i can click execute and the stored procedure itself will be created now just bear this in mind when we actually execute this code it's going to create a procedure usp part details and the major feature of this procedure will be a select statement the point being here that when we specify the input parameter of the id of a part we're going to get some data some information about that part back from the procedure the category the description and the net price if this procedure were actually going to be one for example to insert a record into the parts table then instead of a select statement here we'd have an insert statement here if we wanted to do an update instead of a select statement here we'd have an update statement and so on so that is really the way in which the store procedures can perform the majority of the database operations that we might need to perform so let me now put that select statement in click in there it's category description net price so just to emphasize again we're selecting category description and net price from the parts table where the part id is equal to the input parameter part id let's just do a pause on that to make sure that it's okay yep sql server is happy with that let's try to do and execute now notice the message at the bottom query executed successfully and if i look over here amongst my stored procedures i've now got my usp part details stored procedure now given that we've used this script to create the stored procedure and bear in mind that occasionally things don't go quite the way that you plan and you may want to create either this procedure again with a change or perhaps a different procedure but with some common elements it's often a good idea to keep these scripts that you've used to create procedures if i just click on close here it offers me the opportunity to save this script gives me a name save changes to the following items there's the script name now i'm going to save this particular script i quite often reuse these i think oh i had something like that a while ago i can use it again i could obviously give it a different name if i wanted to but i'll just go with the default here i've saved that script so let's have a look at our stored procedure itself if i right click go into modify i can see it begins with a use statement which makes sure that i'm using the correct database it's then got some control settings no i've got some comments as well it's actually put in there the author creation date and description that i specified and here is the actual procedure itself alter procedure there it is what it does is it's got the default value in there and then basically with the select statement that i entered it gives me the data that i'm going to be looking for provided that of course i specify a part id for a part that exists in the database so that's my stored procedure now what i'm going to do is to write a little script to run it so this last step really is the last one i'm just going to set up a little script that will enable us to test the stored procedure and then i want to demonstrate a couple of particular features that you need to be aware of the query to test the stored procedure is going to be pretty simple it's going to be execute then the name of the stored procedure usp part details and then the parameter value equals and then i'm going to put in the part id for the part that i want the details of now execute is normally abbreviated to exec and one of the things that i haven't included here is a bit of code within the stored procedure to handle a situation where i specify a part id that does not exist i would need some proper error handling you don't need to know how to do that but hopefully you can appreciate the need to handle a situation where we specify a non-existent part id so let me just go in and create that query and we'll give it a run now as i pointed out earlier on this store procedure is stored inside the database so you need to make sure that you have the correct database context set before you execute the stored procedure so in this case i've done my use partsdb before i've done my execute i've done a parse and sql server is pretty happy with what i've put there so let's try and execute and with the execute note i get back category door handles description there it is net price there it is so my store procedure seems to work okay let me try a different number let me try three four two eight nine let me execute that one there we are i've got a different part and let's try a number for a part that doesn't exist in the database as i mentioned not having any error handling in here is likely to be a problem click on execute and you finish up with an empty set now the just one other thing that i wanted to mention here going back to the stored procedure which seems to be working fine and that is this statement set no count on now the presence of this statement means that when we run this store procedure we don't get the count that you typically get say at the end of a select where it says something like five records selected or eight records updated or 23 records deleted or whatever it might be the reason you may not want that is that you don't want that to interfere with if you like the receiving end of running a stored procedure if the stored procedure is being used as in this case to return some values some field values from a record having specified as an input parameter the id of the record particularly in a programming context it's very likely that you don't want some sort of summary value returned in that way as well if you got 10 records back you would count them yourself you may have noticed in the title of this section that it also refers to functions and specifically here we mean user defined functions in many ways these are closely related to stored procedures but there are some important differences and i just want to quickly run through the important differences and show you one example of the effects of a user defined function first of all a user defined function is used to return a specific value or perhaps a table of values an example i'm going to show you actually gives you a table of values you cannot use a user-defined function to modify the database state so you can't do inserts and updates but where it can be particularly useful to use a user-defined function is for things like totaling and averaging so for instance you could write a function to calculate how much stock of a part the company has taking into account current orders and so on so you could have a function you give it a name and you can call it somewhere within your perhaps web application your application that needs sql server and if you use this function say with a part id it will come back and say this is how many of this part you've got now for an example of the use of a udf statement this statement select star from ufn gross prices bathroom will actually give you back a table of values for the parts in the parts table with the category of bathroom but it will include the price with tax so ufn gross prices is a function that calculates prices with 20 tax added to net price a function can return a value what's called a scalar value or it can return a table so let me just quickly show you that function just to give you some idea it looks very much like a stored procedure but there are those significant differences there are one or two other technical differences as well that you don't really need to know about now first of all notice the syntax that's used here we're not doing execute exec of a stored procedure we actually have in the middle of this code ufn gross prices and then in brackets bathroom now ufn gross prices is the function and then the arguments of the function there is only one in this case but you can have more than one is a category and in this case i've specified a category of bathroom what the function does is to produce a table the output of this function is a table and select star from says select all of the columns from that table and that table will contain a list of the items with category bathroom and for each of them in fact it shows their net price and their gross price the price with tax let's just run that and see what happens click on execute and there at the bottom you have your part ids net prices and gross prices for all of the bathroom parts obviously if i wanted to include other fields such as description and so on i could do let's try changing the category instead of bathroom let's try door handles there we are execute that now you've got the equivalent output for all of the door handle parts now let's just take a look at the function itself it's actually not particularly complicated and the workings of it are quite similar to those of a stored procedure so if i go into programmability we've already seen the store procedures folder within programmability there's also a functions folder and within the table valued functions that's the functions that produce tables there's the one dbo ufn gross prices if i now modify that you can see much of the content of this looks very much like a stored procedure but the actual workings of the function are down here and basically if you look at this line here five from the end as select part id net price and then net price times 1.2 that's where the adding 20 tax comes from you don't need to understand the detail of how this works just the principle that you can have a function that produces a single value or a table and then you can use that function within a script in a similar way to the way that i've used it here so that's an example of a function now the last topic that i'm going to cover in this section actually may seem a little bit different from the rest of the section but there's a good reason for putting it here and that is that it closely relates to some of the things that we've been looking at in this section and it's the subject of sql injection now sql injection is an attack on your computer or on a broader computer system in which malicious code is inserted into strings when code such as a stored procedure is run the malicious code is then also run resulting in data loss disclosure or some other harmful result now sql injection is something that people quite often have trouble understanding i'm going to give you a very simple example of how sql injection works here i hope there aren't any computer systems still in existence that couldn't protect themselves from what i'm going to show you here but this example i hope will give you an idea of what sql injection is about sql injection can be achieved by inserting sql code into user input variables and that's why i've included this section here when you're maybe looking at a web page and you're asked to enter a part id instead of entering a part id you enter sql code it can also be achieved by storing malicious code in literal strings or metadata this code is subsequently concatenated with other code and executed with harmful effect it's often achieved by terminating a string prematurely then appending a new command to follow on from it again the example that i'm going to show you isn't quite that but it'll give you the general idea and i hope you'll appreciate how sql injection can get itself established so let's look at an example it's a very much simplified example suppose you have a website where a customer enters their customer id and is shown their own details the customer enters the customer id on the web page and it is stored by the web programmer as a field let's call that field txt cust id so the web programmer has said when somebody's entered the customer id there and gone to the next field or hit okay i'm going to store that value in a variable within my program the programmer then calls a stored procedure that uses this sql statement select everything from customers where the customer id is equal to the customer id that was just entered now that looks absolutely fine doesn't you put your customer id in you get your details back surely that's okay well it certainly isn't so there's the select statement again it looks absolutely fine what the user enters as their id is this character string single quote single quote or one equals one very strange customer id to enter but look what that does to the select statement when that value quote quote or one equals one which has become txt cust id within the software when it's passed to our stored procedure the sql statement that's executed now is this select star from customers where customer id equals nothing so either the customer id is empty or one equals one now you may say 1 equals 1 is a strange thing but the great thing about 1 equals 1 is that it's always true so in that where statement which says either the customer id is blank or something that's always true is true the answer to that is always going to be true so you get the details of all customers and so in principle that's the sort of way in which sql injection works so there we are just a quick summary of the objectives now creating stored procedures for selecting inserting updating or deleting data now as i've said many times you don't need to be able to do it you certainly won't have a practical element in the exam but you need to understand pretty much all the topics that i've covered in this section that's it for this section i'll see you in the next one [Music] welcome back to our course on mta exam 98-364 database fundamentals we're now starting part three of the course and we're going to start to look at selecting data in quite a bit more detail we've already covered some of the basics such as the fundamentals of using select statements but here we're going to really look at select statements in a lot more detail i'll be setting up some more additional data as we go along if you're using your own database i suggest you do that too it'll become clear as we go through what additional data you're going to need first of all then the objectives for this and the following sections the objectives may include but are not limited to utilizing select queries to extract data from one table extracting data by using joins and combining result sets by using union and intersect now we're only going to start working through these objectives in this particular section first of all let's start again looking at the select statement using queries to select data this is a very simple select statement select star from parts [Music] we could be querying a table or we could be querying a view the star indicates that we want to see all of the available fields the lack of a where clause indicates that we will see all of the records in this table let's take a look at what happens if i run this in ssms so there is my very straightforward select statement click on execute and i see a list of all of the fields for all of the records in the parts table now let's introduce a where clause select star from parts where category equals door packs modern now the way i've written the select statement here emphasizes the three fundamental elements of the select the first part select star says what to select select star says select everything but if i just said select part id i would have just got a list of part ids if i'd said select part id comma net price i would have got a price list comprising the ids and the net prices the second part from parts tells sql server where to get the data from and in this case it's from the parts table and the third part the where clause specifies any conditions that apply so in this case where category equals door packs modern so the star indicates that we will see all of the available fields and the where clause causes the filtering of the records we retrieve now again i just want to quickly demonstrate this within ssms partly because i want to demonstrate something else as well so there i have my select statement with the where clause in this case i've used the where clause of where category equals bathroom let's execute that and i just now list the parts in the parts table where the category is bathroom now something that i mentioned earlier on that i haven't really demonstrated in this context so far are the count messages you see how we can see these bathroom parts there's a tab down here next to results of messages and that says ten rows affected that tells me how many rows there are how many records with a part with category of bathrooms let me i'm in the partsdb so i can get rid of those first two commands there i'm going to put in set no count on now what set no count on does is to say that i want no counts when i execute these commands i don't want you to give me a count back so if i now click on execute obviously i still get the same 10 records in the parts table that are in the category of bathroom but look at the messages all i get is the default message command completed successfully if i want to unsuppress no count no count means don't give me account so if i want to set that off in other words do give me a count if i now execute look up messages i'm back to getting my count of the records again now selecting specific columns is straightforward enough we've done this earlier on in the course a few times i just list the columns that i want separated by commas so select part id comma description comma net price from parts as we'll see later on this gets a little bit more complicated when you're selecting from more than one table or view but fundamentally it still works in the same way you just separate the data entities that you want by commas in the select statement there and that's really is all there is to it the other thing that you may well need to do and we've seen i think just one example of this it's one of the things we're going to focus on and the rest of this section combining conditions so conditions can be combined in a where clause as in this example select part id description net price from parts where category equals door packs modern and the net price is less than 10. there are various ways of combining conditions and there are many individual conditions as well so what i want to do now is to look at individual conditions and combined conditions various examples of each and just try and go through in quite a bit more detail how they work so let's look at some conditions and combined conditions we're actually going to try running one or two of them one of the things i want to make clear is that the conditions generally are quite sensitive to syntax so even what might seem a slight variation in syntax which may make a significant difference in whether a particular where clause is syntactically correct or not i'd also like to point out something which is very important these conditions are very often examined there's very often exam questions about whether conditions are valid or not so once again i would suggest that you spend a reasonable amount of time experimenting with these conditions yourself and when we've looked at two or three examples i'm going to show you a couple of pages on the microsoft msdn site that give a much fuller list of all of the available options so let's step through a few of these first i'll explain what the condition is supposed to do i'm not guaranteeing that they're all valid i'm going to lead you to experiment with some of them but with one or two of the ones that do work i'm going to make small changes that stop them working so here's the first one net price is greater than or equal to 10 and net price is less than or equal to 15. so that is a combined condition with two conditions net price greater than or equal to 10 and net price less than or equal to 15 both conditions need to be true the next condition does exactly the same thing net price between 10 and 15. here's another condition category in bathroom comma construction note those two names in brackets separated by a comma this condition says that the value of the category for a row in whichever table we are selecting from is in that set the category is either bathroom or its construction sometimes you may want to negate a condition so for instance supposing you wanted to specify a net price which is not below 10 well of course you could say greater than or equal to 10 but you can also use this condition not net price less than 10. similarly you could say not category equals bathroom show me all the parts that aren't bathroom parts you can use wild cards for matching text so for instance description like percent door percent the percent wild card means any number of characters and in this case it means the description has just got door in it somewhere this one says the category is not like door so when looking at the category for each record in this table if it's like door percent so in other words if it begins with the word door and then there's anything after door i want you to exclude it from the selection it's not category like door if i said not category like percent modern that means that the category doesn't end in the word modern when it comes to numeric values there's a whole host of possibilities the part id is above five thousand the part id is not equal to five thousand or alternatively the part id is not equal to five thousand and as you can see there are different symbols there for those logical operators now i'm going to show you a full list of those in a moment but what i want to do is to run through a couple of those examples so let's start with this one select from parts where net price greater than or equal to 10 and net price less than or equal to 15. the spacing there is really not critical you need to separate and for instance from net price but notice how the spaces either side of the logical operators like greater than or equal to less than or equal to they are not critical let's execute that query and see what we get that gives us just those five parts now let's suppose that i said because i was about to make a mistake select star from parts where net price greater than or equal to 10 and less than or equal to 15. that sounds pretty reasonable doesn't it but in fact it won't work because you need to say the name net price again it's two conditions combined together and each condition must make sense on its own so net price greater than or equal to 10 and that price less than or equal to 15 is fine note that there is a difference here between something which is syntactically correct and something which actually yields any parts so supposing i put this in wrongly and said i want you to find me any parts where the net price is greater than or equal to 15 and the net price is less than or equal to 10. now of course that's impossible but when i execute the query the query is fine there isn't a syntax problem it just gives me an empty table of results now when i use instead the between operator i'm only applying one condition so this is not an and as in this condition and that condition this is one condition the net price is between 10 and 15. execute that and i'm back to my five records again now for this next example i'm going to demonstrate not so select all from parts where the net price is less than or equal to 20 execute that that's fine i've got that all those parts where the price is below 20. what about not we're not net price less than or equal to 20. where you are going to do a knot as a general rule you put the knot in front of the whole condition execute now and i finish up with the parts whose prices are above 20. let me change that again and this time i'm going to put where net price not less than or equal to 20. let's try that no that's not good because that is syntactically incorrect i need to put the not in front of the whole condition not part way through it which is what i've done here now let's look at one or two cases involving text character strings here's a new select statement select all from parts where the description is like screen if i execute that syntactically is correct but i don't find anything if i wanted to find all instances which begin with the word screen so it's the screen followed by anything else i put a percent wildcard character let's execute that see what i find it don't find anything what about trying a wild card character at the beginning so we're now looking for any parts where the description has just got screened somewhere in it let's execute again now we find three but the three we find the reason we didn't find them before is the word screen is at the end so in fact i don't need that percent at the end if i run it just said percent screen so any string with screen at the end would give me the same three results so percent is a wildcard character that says any number of characters if you want to specify say one character single character the single character is the underscore character so that says it ends with a screen but it's only got one character in front of it let's execute that don't find any of course but if i put in say um seven underscores so i'm looking for a description that's got seven of any kind of character followed by screen let's execute that and i get those two because shower and a space is seven characters so there are actually various places within microsoft.com where you can find more information about where and the associated conditions this is actually one of the places within the microsoft sql server site as usual make sure you select the correct version of sql server it gives you the syntax of where conditions and then there are a number of examples so for instance here's one where name equals blade and another one where name is like there are various numeric ones using comparison operators and then also examples with various conditions here's one for instance with three conditions where name like frame and name like hl percent and color equals red so that's a good list of the basic types and then from there you can link to other particular parts of a where clause such as this link to search conditions and with the search condition link there's a general syntax statement and then a list of the arguments now that includes a list of the available operators equal not equal not equal greater than greater than or equal to through down to things like between and in the not options and so on so there are plenty of examples included as well so for some extra reading some extra examples that's a great place to go now i want to look again and a little bit more detail at queries with combined conditions so select all from parts where the part id is greater than 5000 or the net price is greater than 15. when we execute that command what happens is we get all of the records from the parts table where either of those conditions is true if we had an and there instead of an or we'd be looking for all of the records from the parts table where both conditions are true now look at this statement select all from parts where the part id is greater than 50 000 and the net price is less than 10 or greater than 15. now in this case what happens is where we have brackets anything in brackets is executed first so in this case first of all what happens is we evaluate for each record is the net price less than 10 or is the net price greater than 15 if either of those two conditions are true then we've got a true and then we also have to have true the fact that the part id is greater than 50 000. if i didn't have those brackets the way that that particular select would work would be different because without the brackets the and is evaluated first and the or is evaluated second i'm going to demonstrate that to you in just a moment but the rule is this the order of precedence for the logical operators is not followed by and followed by or and parentheses can be used to override this precedence in a search condition so if you see a particular sequence with a combination of knots and and and ores take note of the brackets but in the absence of brackets or even in the presence of brackets for that matter within that the order is not then and then or let me just demonstrate that now with a variation on that last example so my parts table currently has 19 parts in it they're all listed there first thing i'm going to do is to put in a where clause the where clause is select all from parts where the part id is above 50 000. and there's actually seven parts where the id number is greater than 50 000. prices range from 990 up to 699. now i'm going to put in an and and then in brackets i'm going to say net price is less than 10 or net price is greater than 15. so the way that's evaluated is first of all go for the brackets net price less than 10 or net price greater than 15 find the parts for which that's true and then the ones for which it's also true that the part id is greater than 50 000. so let's execute that and see how many parts we find there are five parts where that condition is satisfied if you like the condition is that the part rd is over fifty thousand well they all definitely have part ids over fifty thousand and the price costs either less than ten or more than fifteen now let me take those brackets out parentheses gone now the way that this particular select works is as follows the no brackets so the order of precedence is do any knots first well there are no knots so next we look for any ands so the first condition is part id greater than 50 000 and net price less than 10 so that's one condition or now that second condition that or net price greater than 15 doesn't require part id to be greater than 50 000 if you like it's a condition standing on its own because of the precedence so let's execute that and see if we still get five now in this case we get actually quite a few let me see out of 19 we get 13. so these 13 parts out of the 19 parts in my table either have the property that the part id is greater than 50 000 and the net price is less than 10 or the net price is greater than 15. so that's how not and or and the parentheses work the final topic that i'm going to cover in this section is the use of built-in functions built-in functions can be used to produce aggregate and dependent results from queries examples of the built-in functions are count max and get date now unfortunately there are many many built-in functions and it's quite a frighteningly long list to be honest with you so there is absolutely not the time to go through many of those on this course but there is a location within microsoft.com i'm going to show you in just a moment with a list of all of those built-in functions some of them are really quite esoteric and you certainly wouldn't be expected to know anything about those but some of these very basic functions have come up in the exam before as to basically what they do and how they work so once again you need to go through this area that i'm linking to try a couple of the examples i've also included as part of the exercises that go with this course some examples involving built-in functions and if you know these few basic ones and you do a little bit of extra reading you should be well placed to answer any questions that include functions within select statements but let's move on now to those couple of examples so here's the first example select count star from parts where part id greater than 50 000 count is a function and the function has in this case a single argument and what we count doesn't matter because really we're counting the number of records by saying count star we're saying count how many records have a part id of greater than 50 000 in the parts table let's execute that the answer we get is seven now let's select max net price what do you think that does that says find the maximum price from any part where the part id is greater than fifty thousand execute that and we find it's 699. now finally let me just show you one other function and this one's very different to the others with this function i'm going to type in select get date with empty brackets and execute that and it tells me that the current date and time are 2014 january 17th and it's 1500 in the afternoon now that get date function just returns the current date and time and you may think well okay that's good but what use is that to me well surprisingly that kind of function is extremely useful for all sorts of reasons one of them is let's suppose that you were writing a stored procedure to find all invoices that are due within the next three days you would have to write a select statement that included the current date in order to work out what three days from the current date is to build your where condition to look for invoices that are due well you can't actually write today's date in a stored procedure you have to change it every day but having get dates in there means you can base it always on the current date similarly if you were doing an update or an insert and you needed to put the latest date into a record you could use the getdate function or one of the other many functions associated with the current date and time in order to put the current date or the current time or whatever you needed into one of those other types of sql statement so functions like that informational functions date and time functions in particular can be extremely useful so here's that built-in functions page within microsoft sql server on microsoft.com within the technics section there's a page make sure you've got the right version of sql server there's the page with the list of the types of functions scalar functions etc each of the types say aggregate functions you can click through in the case of aggregate functions amongst the aggregate functions you've got count you've got max two of the ones that we looked at there also if you go back you'll see date and time functions and so on i do suggest that you spend some time browsing through this trying some of the functions out to get a general feel for them so read through these and try the examples and exercises so we'll be completing this work in the next section just a quick reminder on the objectives utilizing select queries to extract data from one table that's what we've been doing really in part one in the next part we're going to look at extracting data by using joins and combining result sets by using union and intersect that's the end of this section i'll see you in the next one [Music] hello again and welcome back to our course on mta database fundamentals exam 98-364 select data part two in part one we looked at querying a database in quite a bit more detail in this section we're going to look at making our database queries more complex in a couple of different ways one way is to effectively combine the results of two queries together and the other way is to look at the use of joins let's take a look at the objectives that we started with in part one utilizing select queries to extract data from one table which is what we did extracting data by using joins that's what we're going to look at a little bit later on in this section and what we're going to look at first in this section is combining result sets by using union and intersect so what we're going to look at first is the use of the union clause the union clause enables you to combine the results of two or more queries together the result is the set of all results from all of the constituent queries the number and order of the columns must be the same for all of the queries in the union and the data types you use must be compatible now the key point here is that when you're doing a union you can actually get the results from the individual queries from different tables and in fact the results may even represent different things but when you actually perform the union itself they must actually have compatible data types now i think the best way to explain this is to explain it with an example so let's take an example of a union we start with a single query select first name last name from employees where date of birth greater than or equal to first of january 1953. now let's assume that i was running this query sometime last year on an employee's table for the company and that would give me the first name and the last name of the employees who were 60 years old and over now let's take a second query and this query comes from a different company table and this is the table called retirees so these are the people who used to work for the company don't any longer and for those we are looking for the ones where a field called in touch equals one which will be true so basically let's interpret this as being the company's retirees but who are still in touch with us now let's suppose that we've arranged some kind of christmas party or some kind of trip for the over 60s that still currently work for the company and the retirees and we actually want to put these two lists together now the important point here is that the select statement in each case selects a first name and a last name and you can take it from me that the data types for those are the same so the first name data type in employees is the same as the first name data type for retirees and similarly last names have the same data types provided those two objects those two fields in the selects in that same sequence have the same data types then i can form the union of the two queries so it's basically all of the people who satisfy the conditions in the first query all the people from the second list and we put the two lists together we make one big long list out of the two and that's basically what union is and does now one important point here of course is that in theory we might have the same person in both lists in this case it would actually be impossible because and the retiree probably wouldn't be in the employees list but if in another situation one person could be in both lists it's absolutely not a problem because basically their name just appears once in the union list so this is basically a list of the people who are in one list or the other list or both lists now the next case of combining two result sets together is to use the accept clause and the accept clause enables you to retrieve data using the first select but then to exclude any matching data retrieved by the second query the second select so once again let's have a look at an example of this select full name from parents so what we're doing here is to get a list of the full names from our parents table this is a school database select governor name from governors now in this case full name is a full name governor name is a full name but in one case it's of a parent in the other it's a case of governors now if i were about to embark on a recruitment drive to get parents to become school governors what i might do is to get a list of the parents and want to exclude the ones who are already in the governors list so in that case i would say select full name from parents except select governor name from governors and what that does is to make a list of full names from the parents table and then basically deletes the ones that are in the results of selecting governor name from governors so that's how accept works next we're going to look at the intersect clause the intersect clause enables you to identify the data that is retrieved by both the first select and the second select now union says either this one says both so let's look at an example of intersect our first query says select part id from warehouse.parts where net price greater than 15. don't forget what i told you much earlier on in the course about schema we've actually got a parts table which is in the warehouse schema and what we're selecting here is the ids of the parts where the net price is greater than 15 of our currency units the second select is selecting the part id from our production orders table that's the orders table which is in the production schema where the shipped status is zero which probably means that we've got orders but they haven't yet been shipped if we actually form an intersect of those two what we're going to do is to get a list of part ids for parts where the price is over of 15 we have orders which are not shipped so there we've effectively got part ids from two completely different tables in fact these two tables are in different schemas and we will form a list of those part ids that's what intersect does now it's a very good idea particularly if you've put together your own database to experiment with union and accept and intersect just try a few cases yourself make sure you understand what each of them does and the syntax of each of them as well next we're going to look at joins and in order to do this i'm going to tell you some more about primary keys and foreign keys we'll be covering this in much more detail later on but i've set up some orders manually and i'll show you those in a moment but i want you to try to remember back to when we set up the orders table you may recall that orders records specify both parts and customers later we'll be setting up foreign keys to establish the relationships between the tables based on primary keys and foreign keys usually we would do this before setting up the data to ensure integrity on this occasion i'm going to set up orders data without the relationships having already been set up however when i do this i do need to make sure that i only specify parts and customers that exist in the relevant tables in the database so first of all let's take a look at those orders that i've set up so if i right click on the orders table here and say select top 1000 rows when you do that select it actually builds the select statement for you puts it at the top there and at the bottom you can see the six records that i've put in the orders table i've got six orders now because these are specifying ids they're rather difficult orders to understand but that's exactly the way they should be there's an order number at the beginning there then the customer id 21437-22358 of course the name of the customer isn't there and that is really what normalization is all about the name of the customer is elsewhere more of that later then we have the part id the description of the part the category of the part none of that is there either i'd get that from the parts table then the quantity of the part that's in the order and then the source of the order at the moment the options i've used online and telephone and the shipped status is zero for false so it hasn't been shipped yet and one for true it has been shipped so they're the orders that i've already set up now what we're going to look at for the rest of this section is various types of join clause the join clause enables you to combine related data from multiple data sources that's multiple tables multiple views there are three main types of join there's first of all inner join then there's outer join and in fact with outer join there are three types of outer join left outer join right outer join and full outer join and then the third type of join is what's called a cross join now if you want to read about various other types of join which aren't really going to be of importance in this course but if you really want to do a bit of interesting reading about all sorts of other joins there's a very interesting wikipedia article on joins that you may or may not be interested in but really what you need to know the aspects of joins you need to know about i'm going to cover but if you really like joins they will help yourself to some more information let's start by looking at an inner join an inner join selects records that match in both tables let me explain what that means here's an example select orders dot quantity comma part stop description now note here in this select instead of saying select quantity comma description where we're specifying field names from one table because we've now got two tables or in many cases more than two tables involved we have to specify which table each of the fields comes from so this actually says select the quantity field from the orders table and the description field from the parts table from orders inner join parts they're the two tables that we're joining using an inner join and then on and this is how we match them the part id in the orders table matches the part id in the parts table now that may seem a little bit obvious but we won't always have the fields name the same part id in the parts table may be called that but the part id may be called something else in another table so in this case we're saying i want you to show me the quantity and the description for every order i've got using the parts table effectively to get the description so let's try running that now in ssms and see what it comes up with so there we are let's execute that select query with the inner join and what we see there is listed the quantities and the descriptions within our orders so we've got two of that particular fan on order six of those door handle sets one of those shell trays and screens and so on now we're not restricted in terms of which fields we could include here so for instance if i wanted to put the part id in as well i could add that there and i can also add a where clause if i want to i could say show me the ones where i haven't actually shipped the order yet so that will be orders now notice as i type there's this helping system that's suggesting what i might mean so orders now what shipping status called oh there it is shipped status double click that and i want that to be equal to false which is zero so i'm now saying show me the quantity the part id and the description for all of the orders that haven't shipped yet now there's one very important point to bear in mind here and that is that at the moment part id would be ambiguous and sql server doesn't know whether i want the part id from the orders table or the part id from the parts table of course we know that in this case they're going to be the same but as a general rule you should always once you start using joins make sure that you specify which table each field comes from now it's true that if a name only appears in one of the tables then you'll usually get away with it but it's always a good idea to be absolutely specific about which table a field is coming from partly because if subsequently changes are made to tables it may be that something that wasn't ambiguous before becomes ambiguous in the future so when i'm writing these select statements with joins involved i always put the table names in just to be absolutely specific so having done that let's execute again we now find that we've also got the part id shown as well as the description and we only have four orders in this case because we're showing the four orders that haven't shipped yet next we're going to look at a left outer join a left outer join includes records from the left table and those that match in the right table now i'm going to show you an example of this first and i'll explain exactly what goes on select customer name from the customers table order id from the orders table quantity from the orders table and part id from the orders table from customers left outer join orders on customers.customerid equals orders.customerid so there are two tables involved in this select customers and orders and we match them up by the fact that the customer id in the customers table must equal the customer id in the orders table now it's a left outer join which means we will have all customers and whichever matching records we've got in the orders table so let's see what happens now if we run that in ssms so there it is i've entered that query let's execute it and see what we get and what we get there in the results panel at the bottom is that we have customer name order id quantity and part id the four fields that i asked for we get all customers and for each of the customers we get a list of their orders so acme metals has order two three four where it's ordered two of part number one seven one two two acme medals two five one is the order number quantity 2 of part id 54683 another order for acme metals three orders for consolidated international and then pacific hats my third company has no orders at all so i just get a row of nulls there so pacific hats doesn't currently have any orders so that's what a left outer join does it says take the table on the left i want all of those and whatever i've got matching in the table on the right so that's effectively how you'd make a list of all your customers and any orders that exist for those if you only wanted to show the customers for whom you have orders you could use an inner join instead so hopefully by now this is starting to make some sense so if you can figure this one out a right outer join a right outer join includes records from the right table and those that match in the left table so here's an example select order id from the orders table customer id from the orders table and description from the parts table from orders right outer join parts on order stop part id equals part stock part id now what this is saying is i want a full list of the parts and i want to know what orders i've got for each of those parts so let's try running that and see what we get okay execute that query and what we have down here is a list all the parts and where there is an order we have the order id and the customer id and where there isn't it says null so we've got no orders for that sink no orders for that timber flooring we have got an order for the shower tray and screen there if we had more than one order for a particular part we'd see the part name appear twice and the orders one after the other the order number and the customer id now one other thing just to mention here although we're entering into realms that are more complex than you need to know about if you wanted in this particular case for example to see say the customer name as well you'd have to get the customer table involved and you'd have to use effectively two lots of joins in order to get the customer name as well that's way beyond the complexity of what you need to know here but just be aware that you can actually involve all of the tables and views and so on from your database in increasingly complex select statements if you really need to now for the remaining types of join i'm just going to talk about them i'm not going to demonstrate them you can experiment with these yourself if you'd like to pretty straightforward a full outer join includes records from both tables so in this case if you took an example of select orders the order id orders dot customer id so that's order id and customer id from the orders table and then parts.part id parts.description that's part id and description from the parts table from orders full outer join parts on matching the part id what you would get is a list of all orders whether or not there's parts assigned in our case they would have parts of sign but maybe in another scenario we might have order with no parts assigned yet and then all parts whether or not they're on order so you get a list with nulls on the left and nulls on the right so you'd know which orders have parts and which don't and you'd know which parts have orders and which parts don't so that's everything now the next type of join the third type the cross join includes records from both tables as a cartesian product that means it doesn't match anything in terms of matching an id it just gives you every entry on the left is combined with every entry on the right so for example it would look like that in hours select orders to order id auto stop customer id part stop part id part stop description from orders cross join parts now there's no on condition there if you actually run this there's a little exercise for you to do on your database or if you've been following along with mine on my database you would get 114 results because there are six orders and 19 parts and it would combine every order with every part even though of course every order is not for every part and every part is not for every order across join basically includes records from both tables a cartesian product is everything goes with everything now there is another type of join but this is not a join type as such it's called a self join but it's a term that's used in a situation where we join a table to itself you can actually have the same table on both sides of a join for example we might join an employees table to itself so that we could see each employee's supervisor details bear in mind the supervisor will be an employee as well we might want to match employees to their supervisors by doing a join of the employees table to the employees table that's called a self join self joins are often associated with hierarchies for example we might also use self joins if we added assemblies to our parts table and a part in the parts table might be a component of an assembly that was in turn apart in our parts table so self joins have a very definite use within relational databases so there we are that's it on joins let's review our objectives overall utilizing select queries to extract data from one table we've done that we've also looked at extracting data by using joins up to two tables and then we've also looked at combining result sets using union and intersect and in fact we look to accept as well that's the end of this section i'll see you in the next one [Music] hello again and welcome back to our course on mta database fundamentals 98-360 in this section we're going to start looking at inserting and updating data in a bit more detail we've already looked at inserting data earlier on in the course we're going to do a couple more examples here let's just look at our objectives understanding how data is inserted into a database understanding how to use insert statements and understanding how data is updated in a database and how to write the updated data to the database by using the appropriate update statements and the final objective understanding how to update by using a table now although you don't actually need to be able to do this let me just demonstrate inserting data in ssms using the gui if i choose one of my tables say the customers table and right click one of the options is select top 1000 rows which lists the top 1000 rows another one is edit top 200 rows now when i click on that i see the top 200 rows in fact i only have three customers in the database at the moment but you'll see at the end that there is a final row with an asterisk and then a list of nulls if i wanted to manually enter the details of a client a new customer i could just type the values in here now let me just adjust the headings there so that you can see the existing data a little bit more clearly now let me put that data in here so if i want to put in a new customer i can assign a new value say the next one in sequence notice that as soon as i started typing there a new empty record was created again a complete row of nulls with an asterisk on the left and the record that i'm working on has got like a little pen there and that's basically the record that's in progress if i were to abandon this entry here basically the row i'm working on would revert back to being a new empty row again and the other new empty row the one that was just created below it would basically be deleted again so let's put a company name in here now at any time if i want to finish entering the details of this customer i can just click into another record if there is a problem however if i haven't done something that i need to do then i wouldn't be allowed to do that now notice on this occasion that it's let me do it and the reason is because nulls in the address are allowed nulls in the country are allowed and discounted has a default value of false so whether or not i can stop inserting a record will to a large extent depend on the declarations for nulls and so on now on this occasion i did actually want to put an address in there so i'm going to put in the first line of an address and then on this occasion the country is there we are if on the other hand on this record here i started putting in the details of a new customer and tried to save this by clicking on the last line i wouldn't be able to because one column where i must have a value is customer id so i must have a customer id therefore i cannot leave this as it is without a customer id however let's suppose i don't want to insert that last record if i press the escape key it basically abandons that record and as you can see i finish up with a row of nulls and this asterisk marker indicating where the next new record will go so that's how to insert data using the ssms gui let's now look at inserting data using sql this is the syntax for the insert t sql command i should point out with the syntax of pretty much all of the commands that i've shown you that i've shown you a very much reduced version without a lot of the additional optional clauses that you don't need to know about if you look all of these up on the microsoft technical site msdn or within technet as the case may be do a search within microsoft.com and you will find a full syntactical specification for each of these statements and in the case of insert and as we'll see update later there are quite a few additional pieces of information that you may find useful but i am showing you the things that you need to know for this course insert into then the name of the table then optionally a column list don't forget square brackets around that means this is optional if there is a column list put it in round brackets you only really need to put a column list if you are not using the default list of columns in the order that they're declared within the table definition having said that i'm pretty much always put a column list in one good reason and that is that the table definition may change and if the table definition may change that may stop my insert working you could argue that if the table definition changes i should revisit my inserts anyway but that's a level of detail we don't really need to go into on this course but basically the column list is optional but if it's not there then you have to put the values in in the same order as the table definition so after the column list you put the word values and then you put a list of data values if you want to insert more than one row you put lists of data values separated by commas so here's an example of how that will look i've actually done it in a pretty untidy way there just to emphasize the point that you don't need to sort of put the word values on a new line or anything like that and you don't have to have the values in their round brackets starting new lines either it goes values and then open round brackets then a list of the values for the four fields close around brackets comma open some more round brackets and so on and you can pretty much insert as many records in one go as you want to now one thing that i haven't mentioned before is the identity property the identity property is a property that you can set for a field in sql server that basically says this particular field will be given a value by sql server automatically and i could for instance have used the identity property for customer number and if i did that what would happen is that sql server would assign automatically the next customer number when i insert a customer if a field has the identity property then you mustn't try to give it a value yourself sql server is going to give it a value you mustn't try so so values for columns that have the identity property set are assigned values by sql server so let's just try that particular insert into and insert those two parts okay i've used the parse option on the query menu just to make sure that that is syntactically valid now let's execute that query as you can see because i've got set no count off i get the message two rows effective which in this case means two rows have been inserted let me right click on parts select top 1000 rows and 23915 and 23916 have successfully been inserted into my parts table next we're going to take a look at updating data now the update statement is the statement that we use and its function is to change data in a table or view so the syntax of the statement is update and then the table or view name set and then a column name equal to a value with optionally where conditions let's look at an example of what an update statement would look like update customers set discounted equals 1 where customer name equals acme metals now it's clear that we're updating the customers table and that we're setting the value of the discounted field to one but we're only doing it for records that satisfy the where condition customer name equals acme metals now within the where condition you can pretty much use anywhere condition you like you might as in this case identify a single customer one record to update but you might for instance say where country equals usa and that would update the discounted value to one for every customer where their country was usa if you omit the where clause altogether then the update will affect every record in the table let's just set that particular one up and run it quickly just to show you what happens first of all let's just look at the customers table let's have a look at acme metals down here they've got a discounted value of zero at the moment there's the query ready to run update customers set discounted is one where customer name equals acme metals execute one row affected let's go back into the customers table again that's the view that i had just now you can either refresh this by just rerunning the query or you can press the f5 key and of course i'm pressing the f5 key you can see that acme metals is now marked as discounted now one other thing to be aware of is that you can of course do manual updates using the gui let me close that and the query which i don't need to save i'm going to right click on customers go into edit top 200 rows there is acme metals notice that the discounted value which i just set to 1 is shown here as true if i wanted to update that i could literally just change the value manually to false and that of course is the manual alternative to writing a sql statement and again if you had just one update to do you might well do it manually like that whereas if you had a whole batch perhaps where the where clause was actually going to return hundreds or thousands of records then you certainly wouldn't go through and do it manually finally then let's just take a look at another example of an update statement this one has nowhere clause it says update parts set net price equals net price times 1.1 now because there's no where clause this would affect all records in the parts table and in each case we're setting the value of net price that's the thing on the left of the equal sign what are we setting net price to we're setting it to net price times multiplied by 1.1 which means that we are increasing all our prices by ten percent now if you had thousands or tens or hundreds of thousands of records in your parts table you wouldn't really want to go through and update them all manually by increasing them by 10 percent of course you could use a where clause here if you needed to be selective about which prices got updated so for instance if it was just for bathroom category items and you could of course apply discounts in the same kind of way so that's our final example of an update statement and that's the end of this section quick look at the objectives understanding how data is inserted into a database we've done it two ways using insert statements and then doing it manually and then we've also looked at how to update data in the database using update statements and we've also done it just by updating by going into the table and doing a manual update doing the gui that is the end of this section i'll see you in the next one [Music] welcome back to our course on mta exam 98-364 database fundamentals in this section we're going to look in more detail at the options for deleting data the objectives are understanding how to delete data from single or multiple tables and knowing how to ensure data and referential integrity by using transactions now as we've seen already we have a number of options for deleting data we can delete selected rows from tables using the delete statement we can delete all the rows from a table using the truncate statement we can delete a table altogether using the drop statement now we've already looked at truncate and drop earlier in the course but i'll briefly review them again in just a moment but let's look now at delete delete using sql the syntax is delete from table where and then whatever the conditions you want to apply are so as an example of that delete from parts where category equals bathroom and that will delete all of the parts where the category is bathroom from the parts table now the choice of where clause will determine which and how many records are deleted so just as a very quick and straightforward example if we take that company that i did the canadian company ta holdings inc if i wanted to delete that if i do new query and i can literally just type in here i've said delete from customers where customer name is like ta something click on execute one row affected and then just reopen the customers table i'll see that ta holdings inc has been deleted so let's just review those other two options truncate table was the first one truncate deletes all of the rows from a table without deleting the table itself note that the alternative to what truncate table does which you could do with a delete statement will be just delete from employees that deletes all of the records don't forget however that truncate frees up the space that was used by those records whereas delete doesn't and as an example of the syntax for a truncate statement to achieve this using truncate the command is truncate table employees the other option that we looked at earlier was drop table and drop table removes a table and all its data so for example drop table employees will completely delete the table employees and all of its data now there may be a number of reasons why you would want to drop a table amongst these are the fact that database design may change for example what was one table may be split into two new tables and the old table can be dropped when it's no longer needed the table may contain transient data and maybe at the end of the year the end of the financial year or after a certain period of time that table is no longer needed maybe the table is a copy of another table that's been used for testing or verification and in that case you may get to the point where you no longer need that copy so there are all sorts of reasons why you may need to drop a table now before we go any further on the subject of deletions we need to look again at referential integrity data is usually spread across several tables and these are related to each other through the key columns that we've talked about there is a set of data integrity rules that enforce the relationships between tables and keep the database as consistent as possible following this set of rules maintains the referential integrity of the database when we do deletions then we want to make sure that whatever we're deleting isn't needed elsewhere so far in our parts database i have not set up the relationships that ensure that referential integrity is maintained and if i just deleted anything that i wanted from any of the tables i could quite easily break that referential integrity so for example we already have some orders on the database each of them specifies a part and each of them specifies a customer if for one of those orders i deleted the part or the customer that order would no longer be a valid order i would have broken the referential integrity of the database now in order to demonstrate how to set up one of these relationships and then what happens if the relationship is in place i'm going to actually use an alter table command we've not used this command so far it's pretty straightforward to follow in this case because what i'm going to do is to add a foreign key constraint the constraints going to be added to the orders table and to say that the part id in the orders table is to become a foreign key and that foreign key will point at the part id in the parts table so i'll only be able to remove apart from the parts table if there are no orders in the order table for which i need that part so let's start a new query there's the first line of my command alter table orders just basically means i'm going to make a change to the orders table now the change i'm going to make is to add a constraint the constraint will have a name actually how you name these is not critical but there are some conventions and standards for how to name these and a very good standard is for a foreign key constraint to begin it with the letters fk and then to say what we're actually forming the constraint between that is in effect in this case the part and the order so i would call this fk part order or fk parts orders and then point out that it's a foreign key constraint and on the orders table which is the table we are modifying we're altering the field which is part of the constraint the field which is going to actually be the foreign key is the part id field the next clause in this statement is references parts that's the parts table and within the parts table the field part id and then a go so first of all let's just parse that check everything's okay yep it's happy about that and then let's execute and what should have happened now is that we now have a foreign key constraint set up for the orders table so first of all let's just check that that foreign key constraint has been set up if i go over to the orders table on the left and click on constraints it's not there but if i look under keys i'll see fk part order if i right click on that there is a modify option there which will allow me to go in here to modify that key constraint i don't need to go into the details of that now but what i really want to do is to try now to delete a part that features in one of the orders so let me just close that script i'm going to go back into orders look at the top thousand rows i've got six orders here let's delete a part let's try to delete that part there one nine two three two so right click there edit top 200 rows if i want to delete a part i can just select it right click and one of the options is delete click on delete you are about to delete one rose click yes to permanently delete these rows click on yes now no rows were deleted a problem occurred attempting to delete this row and basically what this is saying is that we have a foreign key constraint that prevents us from deleting this record because it's needed in a record elsewhere in the database in this case this particular part is used in at least one order so that prevents us from deleting this part so having done that we cannot delete apart from the parts table if there are orders in the orders table that reference the part however we can delete orders that reference that part provided that some other aspect of referential integrity does not protect them so the other way around of course doesn't apply if i've got 10 orders for that part the relationship we just set up doesn't stop me deleting the orders it only stops me from deleting the parts however the orders themselves may feature in some other relationship that's been set up for referential integrity reasons and that prevents me from deleting them there may be many parts in the parts table for which we have no orders and we might be able to delete them provided that some other aspect of referential integrity does not protect them so once you get even a moderately complex database and you start to set up more and more relationships there may be an increasing number of reasons why you cannot delete records from tables but on the basis of what i just demonstrated hopefully you get a good idea now of what those relationships are about and what referential integrity is about now we need to move on to something else to do with integrity and that's the use of transactions and in fact from this point onwards in the course most of what we need to cover is more theory than practice although i will be demonstrating one or two more things a lot of the content of the course from now on unfortunately involves you in learning quite a lot of terminology and quite a few new concepts most of which it's impractical or unnecessary to demonstrate and transactions is the first one that i'm just going to go through the theory of to avoid making only some of an interdependent set of inserts updates and deletes to a database we group them into transactions let's suppose that you have an interdependent set of things that you need to do supposing you have to do 50 inserts and you really need either all of them to work or none of them to work the way that you do this is to group them into a transaction and if you get any kind of failure even if the first 49 work and the 50th one doesn't you can undo the effect of all of them by rolling back the transaction so if we were actually going to put together a group of statements like that we would insert a begin tran before the first actual statement in the transaction begin tran obviously is short for begin transaction and then if all of the operations were successful the changes would be applied to the database with a commit trend and that commit train commits the transaction if there is an error somewhere all of the changes can be aborted with a rollback trend which actually undoes everything in the current transaction of course a whole batch of work that you may need to do on a database might involve several transactions and each transaction may have dozens or hundreds or even thousands of operations in it but it's very important that the interdependent ones are grouped into transactions although it won't have been apparent implicitly in what we've been doing so far sql server has been doing begin trends and commit trends for us but it's been doing them on one operation at a time and where everything was fine it will have done a commit for us and where something went wrong it will have done a rollback for us the example you saw just now where i tried to delete a part will have actually had an implicit begin tran that sql server would have put in there but when it came across that problem due to integrity it will have done a rollback and in fact when you're working on a database in this way you may see database status values that represent progress part way through a transaction and it's only when the whole transaction is committed that the rest of the world the other users using that database will see the changes that you've made the grouping of operations into a transaction is really just a concept that you need to understand for this particular course an exam something else where you only really need to understand the concept is the concept of triggers a trigger is a special kind of stored procedure that executes in response to an action triggers are executed automatically you do not explicitly invoke a trigger what happens is that you attach a trigger to a particular action there are dml triggers and ddl triggers dml triggers execute in response to an insert an update or a delete the reason i'm mentioning this here is we've just dealt with insert an update in the previous section delete in this section and with any of those you can fire a trigger an example of when you might use a dml trigger would be to log or audit an insert of rows into a table so what you might have is an insert statement perhaps using a stored procedure itself but that might in turn trigger the execution of a stored procedure that logs the insert that's just been made or perhaps notifies somebody by email that a record has been inserted into that table so you may have a dml trigger say whenever somebody inserts a new part into the parts table so noted by somebody else in the company that a new part is available and now ddl triggers ddl triggers execute in response to various ddl events including those associated with create such as create table alter such as alter table drop grant which we haven't looked at so far etc as an example of a ddl trigger you might log and notify the creation of any databases on a server so you may have a message sent to somebody to say that user x just created a database sometimes these kinds of things are run for security reasons and sometimes just for routine administration and management reasons there is a special case where a log on trigger fires in response to a log on event and log on triggers are really quite commonly used and for an example of where you might use a logon trigger as an example a check might be made to ensure that this user is not already logged in so the logon executes the trigger is fired a check is made this person's already logged in maybe only let you log in once so that login is actually disabled so that's it quick run through of the objectives for this section understanding how to delete data from single or multiple tables knowing how to ensure data and referential integrity by using transactions that's the end of this section please join me for the next one [Music] welcome back to our course on mta database fundamentals exam 98-364 in this section we're going to look at normalization the objectives of this section are understanding the reasons for normalization understanding the five most common levels of normalization and understanding how to normalize a database to third normal form now in order to understand the reasons for normalization we need to look at one or two key factors in database design first of all when designing a database there can appear to be various conflicting criteria that guide and inform the design these include storage space performance the relationships between objects and the ease of use of the database and all of its components following the process of normalization can help a designer to arrive at a good design in a structured and methodical way and a major aspect of good database design is making sure that data is properly distributed amongst the databases tables and really what normalization is about is the design of the tables bearing in mind particularly the relationships between the tables so let's look next at what normalization actually is normalization is primarily about the elimination of redundant data to save space and to safeguard the integrity of the database now when we start to go through normalization in some detail i'll explain the ways in which those two things are achieved the normalization process can also have other consequential advantages such as improvements in performance in some cases it's not true to say that normalization always improves performance everywhere in a database in fact in some situations performing some operations and some processes it's quite possible that performance will be degraded by the implementation of normalization but normalization in those situations will have other advantages and as usual in any kind of design activity related to it there is some kind of trade-off between advantages and disadvantages in each situation again when we look at one or two particular cases i'll point out how normalization can help performance now if you've never come across normalization before or you want to find out more about it there is a very good wikipedia article there's the link to it which gives you a lot of background information some of it is quite technical but also some of it is pretty straightforward as well so if you want to find out more that's a good place to go and i'm going to show you that article in just a moment the process of normalization involves progress through up to five what are called normal forms and in this section i'm going to explain the five normal forms to you and we're going to pay particular attention to the first three now i should point out to you that depending on which literature you read there are many other normal forms there are numbered normal forms there are named normal forms there are all sorts of esoteric newly discovered normal forms and so on but if you're asked the question how many normal forms are there in relation to this course and this exam the answer is five and in fact most people that deal with normalization would say there are five normal forms and that usually when you're working on database design you will at least get to third normal form the other thing i need to point out to you is although we talk about progressing through the normal forms it would be very unusual indeed for somebody to go through the design of a database by going totally to first normal form then totally to second normal form then third normal form etc what generally happens is that you arrive in a sort of staggered zigzag kind of way you may work in a particular aspect of a database maybe get that to third normal form without even looking at another part so it isn't a straight progression through those three to five normal forms it tends to be a little bit more reflecting the nature of the database how you actually get to those various normal forms anyway let's have a look at that wikipedia article next so here is that wikipedia article and as you can see there's a section at the top that basically describes what normalization is gives a little bit of the history of normalization based on the work of a guy called cod from around about the year 1970 and then it goes through first of all a number of examples down on the right here explaining some of the issues around normalization then later on we get some terms now i'm going to use some of these terms in this section things like transitive dependency you don't really need to know exactly what these terms are but i will try to explain enough of them and in enough detail so that you can answer any questions that you're likely to be asked in the exam and then it goes through the normal forms now you can see here one nf is first normal form 2 and f3 and f4 and f5 and f here are some of the others bcnf that's boyce cod normal form you only really need to worry about one to five there is even a six normal form again you don't need to know about that but if you do want a bit of additional reading if you want some other good examples and explain some of the issues around normalization it is in fact a very good article so let's now look at the five normal forms first of all they each have numbered names as you've just seen first normal form we normally refer to that as one nf written in the way that you can see it there and then we've got two nf3 and f etc now a very brief description of what each of these normal forms is shown on the right you shouldn't take these descriptions as definitions we're going to look at the definitions a little bit later on first normal form is primarily about eliminating repeating groups it's about other things as well but if you think of it as eliminating repeating groups that will remind you of what you need to do second normal form involves eliminating redundant data third normal form involves eliminating columns that are not dependent on the key if at this point you've got no idea what i'm talking about don't worry because i'm going to explain each of these in quite a bit of detail fourth normal form is isolating independent multiple relationships and fifth normal form and this one really is a brain boggler isolate semantically related multiple relationships now you may never have imagined that you'd be isolating semantically related multiple relationships but in fact it's not really as complicated as that term makes it sound as i hope to show you in a little while so let's start with first normal form let's look at a formal definition of first normal form first we're not actually going to work from this formal definition but the formal definitions of these normal forms do sometimes come up in the exam so you should at least be able to recognize which definition goes with which normal form and when we've worked through this section a little longer i hope the formal definitions will start to make a bit of sense a relation is in first normal form that's a table a relation is in first normal form if the domain of each attribute contains only atomic values and the value of each attribute contains only a single value from that domain now i'm going to explain that more fully later on but now let's look at the everyday language definition of first normal form the table has a primary key and basically linked to that primary key is the concept of atomicity no single attribute or column has multiple values [Music] and the non-key attributes columns depend on the primary key so that's the everyday definition of what first normal form means and what i'm going to do now is to show you a design that breaks first normal form and then we're going to look at how you could get that particular design changed so that the relation satisfies first normal form so let's look first at this example of a design that breaks first normal form this is a customers table the id the primary key is in the column on the left then we have the name of the company the address and then we have order one that is details of the first order that they place then we have order two then we have order three and then we have their account manager on the right the main problem here and the one that primarily breaks first normal form is the repeating field repeating field like that in early days of database design you would see quite often but of course it has numerous problems for instance what if a customer makes more than three orders and what about the wasted space for the customers who've only placed one order there is an even worse version of that where for each order you have multiple fields so instead of just order one you might have several columns for order one several for order two and so on you finish up in a situation where you've got to decide what the maximum number of orders a customer can places another really bad situation which was an attempt to solve that first problem in the early days of database design was to have an orders field and then put the names of the orders in there perhaps separated by commas as in the last sentence on that slide again that has numerous problems what if you delete an order and how many orders can you have in there and also what about things like searching for orders and putting more details of those orders in so overall this particular type of design is what we deal with in first normal form by refusing to allow repeating fields repeating field groups here's a better version of that design that does satisfy first normal form there's our customer table has the same information it has the customer id the name the address the account manager but then what we have is an orders table the orders table lists the order ids for each order and with each order each row in that table there is a customer id which is the foreign key if you look at the first order a75621 customer id 21437 that which is of course a foreign key which links to the primary key in the customers table gives us access to the information about the customer each record in the orders table has customer id as a foreign key the key corresponds to the primary key customer id in the customers table customer information is held in the customers table and its access for an order using this relationship now let's take a look at second normal form formal definition first a table is in second normal form if it is in first normal form and no non-prime attribute is dependent on a part proper subset of a candidate key everyday language definition the table meets the criteria for first normal form if the primary key is a composite of attributes i.e it contains multiple columns the non-key attributes or columns must depend on the whole key a table with a primary key that is composed of a single attribute is automatically in second normal form now let me take you through an example of that now let's take a look at a design that breaks second normal form this relates to an orders table it's a differently arranged orders table to the example i showed you just now so these examples don't follow on from each other in some way in this orders table we have a primary key which comprises two columns so it has a customer id and an order number one of the advantages of this approach is that each customer can have their orders numbered one two three four and so on of course if we only use the order number as a primary key we'd get duplication which is not allowed but by combining customer id five characters there with order number three characters we come up with unique primary key values now in this case the account manager field which is really the account manager for the customer does not depend on the whole of the primary key the problem here is that the primary key in the orders table is a composite key comprising both the customer id and an order number but the account manager name depends only on part of that primary key i.e the customer id the account manager is not a function of which order you place the account manager for that customer is always the same account manager so because the account manager only depends on the customer id this design breaks second normal form there's a term that's used quite often in relation to normalization which i think is very helpful it relates to the first three normal forms and it says that fields in a table must depend on the key the whole key and nothing but the key well first normal form is the key second normal form is the whole key so here we have account manager doesn't depend on the whole key only part of it so this design breaks second normal form to achieve second normal form the account manager should be in a customers table as it is identified purely by the customer id let's now look at third normal form first the formal definition a table is in third normal form if it is in second normal form and every non-prime attribute is non-transitively dependent on every candidate key in the table now in everyday language the table meets the criteria for second normal form and each non-key attribute in a row does not depend on the entry in another non-key column so let's look at a design that breaks third normal form again this is a customers table and here we have a customer id we have an account manager for that customer and details of the account manager's email address the problem here is that the account manager email depends on the account manager which in turn depends on the customer id now that situation where one field depends on another field which in turn depends on another field means that account manager email is only transitively dependent on customer id it's only dependent on customer id via the account manager so to achieve third normal form the account manager email should be in the account managers table or the equivalent to that account managers table so in practical terms what this means is that the account manager email is actually an attribute of an account manager and therefore we should set up an account managers table with the details we need of that account manager including their email address and then we can maintain the account manager's details independently and if from the customer table or from an order or anywhere else we want to find the account manager's email we would go to the account manager table to find it now let's take a look at fourth normal form the wording here gets even more torturous i'm afraid but the concept is not that difficult to understand formal definition it's unlikely that this particular definition will help you very much a table is in fourth normal form if and only if for every one of its non-trivial multi-valued dependencies x to y x is a super key iex is either a candidate key or a superset thereof or alternative formal definition which is slightly easier to understand a table is in fourth normal form if there are no non-trivial multi-valued dependencies other than a candidate key now in everyday language what this means is the table meets the criteria for third normal form and a record type should not contain two or more independent multi-valued facts about an entity or fourth normal form separates independent multi-valued facts stored in one table into separate tables now in those last couple of sentences there there is actually the essence of what fourth normal form is about if you have two or more independent multi-valued facts in a table you should separate them into separate tables and really to explain fourth normal form it really is best to explain it with an example so here's a design that breaks fourth normal form it's an account managers table and for each of my account managers i have two multi-valued facts about them now let's look at that first account manager his id is employee id is 761 298 he operates in two industries he works in manufacturing and in consultancy he also works in three different regions in the country he works in the northwest he works in the central region he works in the south east now by placing those two multi-valued facts industry and region about him all in the same table in order to represent all of the combinations of things that he can do within this particular account managers table i have to put together a table with all of the combinations of the industries he works in ie manufacturing and consultancy and the regents he works in northwest central and southeast so what i'm effectively saying there is 761298 can do manufacturing in the northwest 761298 can do consultancy in the northwest 761 298 can do manufacturing in the central region 761 and so on so i'm basically having to represent all of the combinations of things that person can do by having rows and rows in a table showing all those combinations in order to make this obey fourth normal form what i should do is to separate those multi-valued facts out so i should have one table which records the fact that an account manager can work in whatever industries he works in so i'd have one table 761298 manufacturing 761298 consultancy that's one multi-valued fact about an account manager and then i'll have another table recording the other multi-valued fact which is the regions that account manager can work in seven six one two nine eight can work in the northwest seven six one two nine eight can work in central seven six one two nine eight can work in the southeast so because these are independent multi-valued facts that is how i would make this situation obey fourth normal form and i wouldn't need that extremely clumsy set of records there to show all those combinations of those multi-valued facts however and this is a very important however indeed if in fact these are not independent so for instance if 761298 could do manufacturing in the northwest or consultancy in central and no other combinations then the design that we've got here may well be the correct design but in fact in that situation these are not independent multi-valued facts we can't say that the industry the person works in and the region they operate in are in fact independent in that situation if there is dependence then fourth normal form does not apply in that way so here we're dealing with independent multivalued facts in this case industry and region going back to the original argument and they should be separated into separate tables that is fourth normal form now we're going to take a look at fifth normal form we'll start with a formal definition a table is said to be in the fifth normal form if and only if every non-trivial join dependency in it is implied by the candidate keys now in somewhat plainer english and everyday language definition the information content in the table cannot be reconstructed from several smaller record types i.e from record types each having fewer fields than the original record now in this particular case and in the example i'm going to show you here the key thing to understand is that to a large extent fifth normal form depends on the business rules that are in force that affect the design of the database now here is a design that breaks fifth normal form well to be fair it only breaks fifth normal form provided that the business rules work in a certain way let's suppose we've got a table that defines the scope of work that some tradesmen can do so we utilize the services of smith and son they will do work on bathrooms but they only do fitting work they work on kitchens but they only fit them they work on bedrooms but they only fit them they're a fitting company so basically smith and some will work on bathrooms kitchens and bedrooms but they only do fitting on the other hand janet brown and co only work on bathrooms but the work they do is design fitting and repairs now if it's possible to break this information down into smaller parts in the way that i'm going to show you in just a moment then we can say that this design breaks fifth normal form whether this actually breaks fifth normal form will depend on the business rules but in this case we're going to assume that it does because the tables on the following slide accurately contain the same information so i've taken the information from the table on the previous slide and i've broken it down into three separate tables first of all a tradesman scope table and this says what each of the tradesmen can do in terms of their scope are three entries for smith and son they do bathrooms they do kitchens they do bedrooms janet brown and co only work on bathrooms second table is a tradesman work table this one says that smith and son do fitting janet brown and co do design janet brown and co do fitting janet brown and cody repairs and then finally i have a scope work table that defines what sort of work i might need done on each of the possible values of scope so if my services include providing services for designer bathrooms filling the bathrooms repairs of bathrooms filling up bedrooms fitting of kitchens and so on that's what's all in the scope work table now let's suppose that i need some bathroom design work done i could first of all see who does work on bathrooms looking at the top left table i can see that bathroom work is done by smith and son and by janet brown and co then i can say who does design janet brown and co so the only supplier i've got who does design of bathrooms is janet brownenko and that's the answer to my question if i need some bathroom design done i would go to janet brown and go what if i wanted bathroom fitting done again you can follow the same argument through now provided that the possible combinations of work and scope that i am able to provide are all satisfied by these separate three tables then this is a better design this is now in fifth normal form however and let me just return to the previous slide again if in fact the combinations of tradesmen scope and work that are possible are not accurately reflected by that breakdown so if for example there is a situation when that breakdown into the three smaller tables would actually imply a service from a tradesman in a scope or on work that isn't available then the original table is in fifth normal form so it only breaks fifth normal form if i can break it down into smaller tables with less fields in each and those smaller tables working together make me able to glean exactly the same information so the business rules say that the smaller tables do exactly the same job as the bigger table now there are a couple of other ways in which you can reduce redundancy and so on within your database not directly related to normalization just mention one or two of them here when designing a database under a specific table there are other steps that can be taken to reduce redundancy and assist in maintaining database integrity for example if you've got an order there will be a total cost of an order made up of for instance the cost of all the parts taxes delivery packaging and so on you can store that in an order field such as total order value but you should always consider the option of making a calculated field which is recalculated and populated when it is needed and then finally let me just summarize the advantages of normalization normalization reduces the incidence of conflicts due to record locking one of the consequences of normalization normally is that the records in your tables are smaller and there are more of them when people are looking at records certainly when they're updating then sometimes even when they're just looking at records they may well have the records locked and stop other database users from seeing or updating those records the more records you have and the smaller each record is the less chance you have of record locking and this in turn can improve multi-user performance it may take longer to design a normalized database but the extra time is usually at least reclaimed by future ease of maintenance one of the problems particularly with earlier databases before normalization became if you like the norm was that you did finish up with tables with many many many fields in them became incredibly and increasingly difficult to know exactly what was going on and to perform maintenance on the database software as a consequence normalized databases tend to be easier to understand and to query of course with more records and smaller records you need to be able to use joins and unions and so on effectively but that's part of building up your skills as a database administrator and user and it is usually easier to extend a normalized database because it is easier to understand and changes have a more clearly identifiable impact as i mentioned earlier on a couple of times there is always a trade-off when you normalize between the fact that you have a lot more tables each of the tables has less in it and you need to understand the relationships between the tables but once you've got that if you're going to make a change it's usually much easier to work out what impacts it's going to have in a normalized database the one that isn't so finally let's just have a run through of our objectives understanding the reasons for normalization well i hope you know why we normalize databases now you should understand the five most common levels of normalization and understanding how to normalize the database as far as third normal form so you should think back to those first three examples first normal form second normal form third normal form and how we would go about checking that our database tables were in each of the normal forms and the steps to take in order to correct a situation where they aren't that's the end of this section i'll see you in the next one [Music] [Music] welcome back to our course on mta exam 98-364 database fundamentals in this section we're going to look at primary foreign and composite keys as usual let's start with the objectives understanding the reasons for keys in a database choosing appropriate primary keys selecting appropriate data types for keys selecting appropriate fields for composite keys and understanding the relationship between foreign and primary keys now there are three constraints specifically that enable database integrity they are the primary key constraint which we've already looked at it uniquely identifies a record a primary key may be made up of more than one attribute or field this is a composite key we're going to look at that a little bit later on primary key value is not allowed to be null and the table can have at most one primary key foreign keys reference primary keys in another table or in some cases in the same table a table may have many foreign keys now constraint i've mentioned before and which is actually very important as well but which i haven't demonstrated so far is the unique constraint and the way in which this can enable database integrity is to make sure that within the database you only have one value for a particular attribute in a table so the value in that table is unique now the uniqueness may not feature as part of a primary key foreign key relationship but it may still be important that that value is unique one record only may have a null value in this field obviously if two records could have a null value in this field then it wouldn't maintain the property of uniqueness and a table may have many unique constraints now what i'm going to do is to demonstrate the unique constraint very quickly by setting up a unique constraint on a main contact telephone number in the customers table now this telephone number doesn't feature in a key anywhere but we want to make sure that we don't use the same telephone number in two places so what i've done is i've added a telephone number field to the customers table and now we're going to set a unique constraint on that field to apply a unique constraint using the ssms gui you open ss ms and connect select your database open tables right click in this case the customers table and click design from the table designer menu select indexes and keys click add change the type on the right to unique key click the button with the three little dots the ellipses to the right of columns select the field this field is called telephone this is the field i've added click ok and click close and then if you select save all from the file menu you'll see that the unique constraint has been established so let's go through that so first of all you can see the new telephone column on the right there and i've given the three existing customers telephone numbers that are of course all different i've got the parts database open obviously if i then right click on the customers table click on design and then from the table designer menu i select indexes and keys you'll see that i currently have a primary key pk customer with that code and then i already have a unique constraint this was the one that was established when i created this table using the create table command very early on in the course so i'm really going to add another one of these unique constraints unique keys but this time for the telephone column so click on add that brings up by default ix underscore customers it's got a name default name there so first of all i need to change the type to become unique key and then to the right of the columns entry there there's the little button with the three dots on it and i go in and choose the column that i'm going to use that's going to be telephone click on ok and i can now see that i have a unique key on the telephone column now it's a good idea to give this unique key a more suitable name so i'm going to change the default name there to uq and then click on close and what i should now find is that when i've done a save of the schema so just click on save all maybe come back here to keys do a refresh look at the keys i'll see that i have my new key established uq customer telephone now let me try to add a new customer with the same telephone number as an existing customer so i'm going to right click on customers click on edit top 200 rows put a number in there this is just a dummy entry really let's give it the same phone number as an existing one now let's try to save that record and you can see the message in the where uq customer telephone cannot insert duplicate key in object so the error message tells us there that we're trying to enter a new record or changing existing record sense that we have a duplicated telephone number so we're going to escape out of that particular insert everything's back to the way it should be and that's how to set up a unique constraint using the ssms gui so that's a little more about the unique constraint let's now return to the subject of primary keys again a table can have at most one primary key and a primary key value must be unique also a primary key value cannot be none you're designing a database some tables may have one or more candidate primary keys and when the actual primary key is selected the candidates that are not selected are called alternate keys let me give you an example supposing you were designing an employees table and obviously that's going to contain details of company employees you need a unique way of identifying a company employee you may choose not to use something like their name for obvious reasons but people very often use some kind of identifier that's associated with your legislation this could be something like a social security number national insurance number citizen number identity card number even something like a driving license number now all of these candidates are referred to as candidate primary keys until you choose the one that you're actually going to use and then the others become alternate keys it's worth pointing out that in many cases rather than use one of these more obvious candidates as a primary key database designers tend to use a pretty meaningless number very often an integer value to identify individuals and that reduces the chance of needing to change a primary key so for instance if you were to use somebody's national insurance number maybe for some reason that number needed to change maybe changing legislation it could cause you some problems so quite often a an independent primary key is set up and then that's not subject to influence from outside in terms of having to change the value when it comes to primary keys foreign keys and composite keys there's a few more things you need to know about a primary key has a special function for a table it is a unique identifier for the records in the table and may be referenced by foreign keys in other tables or as we've seen earlier even foreign keys within the same table a table can have only one primary key although the primary key may be what's called a composite key i.e be made up of more than one field for example a table of customer orders may have a primary key comprising the unique customer id and an order number for that customer although each customer may have an order number zero zero zero one when this is combined with the customer id a unique identifier is created now i'll come back to that point in a moment when it comes to foreign keys a foreign key supports a relationship between two tables by specifying a primary key value in another or in some cases the same table you may remember the example i mentioned earlier on where if you have an employees table it may be that one of the foreign keys in the employees table is supervisor id and the value of the supervisor id foreign key may be the primary key value for the supervisor in the employees table the relationship that is represented by this link between two records is sometimes referred to as a parent-child relationship a table may have any number of foreign key constraints or foreign keys including none now let me just briefly come back to composite primary keys again a composite primary key is a key that is composed of more than one column now let's look at that example in a little bit more detail our customers have a unique customer id in the customers table each customer places orders in the orders table and we number the orders from zero zero zero one upwards for each customer we cannot uniquely identify an order using order number as each customer would have for example order number two once they'd placed two orders they'd have an order number two we can make a suitable primary key for the orders table by combining the customer id and the order number so we actually string those two things together to make our primary key now the last thing i want to talk about in this section is data types for keys often the data type of a key is dictated by the nature of the data for example if the primary key on an employee table is a national insurance number or identity card number then you will probably use an appropriate character data type for that key if however you wanted to do what i mentioned just now and use a completely independent identifier perhaps a meaningless number that wouldn't need to change because of some kind of outside influence then you're not constrained by the actual nature of the data in say an identity card number and you can choose more freely what the most appropriate data type is another situation where you may need to use a specific data type is if you need a unique identifier what's called a globally unique identifier a guide which is a unique identifier data type in sql server this gives you a globally unique number so if you needed to create a number that you would never create again then you would use a guide guides are very often used for transactional items for instance if in your database you're generating perhaps sales transactions you want to make sure that you always generate a unique identifier you would use a guide data type having said that goods need to be used with care because the processing load associated with goods can be much larger than the processing load associated with simpler data types however when you have a free choice it is usually best to use an integer data type and you should use the smallest one you can bearing in mind future growth so for example if you're setting up a database you're setting up a primary key for something and that's something you've currently got 250 records in the table and you expect that number to grow don't use a data type of tiny int which only goes up to 255 because you'll very soon hit the limit of tiny ins be much better to go for small int or even int so if you are going to use integer data types it's a very good idea to give a lot of thought to how big the variables that they represent might become because if you have to change the data type later on it could cause you some problems bear in mind that integer data types generally can represent extremely large numbers on the other hand don't always set them to the largest available type set them to bigint or something if it's completely unnecessary if you know something isn't going to go beyond a few hundred then smallint will be fine a few thousand why not just use straightforward int etc so that's pretty much all you need to know specifically about data types for keys at the moment let's just review the objectives for this section again understanding the reason for keys in a database how to choose appropriate primary keys selecting appropriate data types selecting appropriate fields for composite keys and understanding the relationship between foreign and primary keys that is the end of this section i'll see you in the next one [Music] hello and welcome back to our course on mta database fundamentals exam 98-364 in this section we're going to look at indexes as usual let's start with the objectives understanding clustered and non-clustered indexes and their purpose in a database so first of all let's talk about indexes in general indexes in databases are used for a similar purpose to indexes in books in a book an index can be used to find a piece of information quickly and without having to read the whole book database indexes are used for the same purpose but whereas a book will usually only have one index a table in a database may have many indexes now of course encyclopedias atlases some kinds of reference works often have more than one index and for those types of book what they have is a pretty good equivalent of the use of many indexes for a database table an index in a book is a list of words with the page numbers that contain each word an index in a database is a list of values in a table with the storage location of rows in the table that contain each value indexes can be created on either a single column or a combination of columns in a table now let me tell you some things specifically about database indexes when designing and building a database you should create indexes for those fields or combinations of fields that will benefit from them i.e for which the benefit of having the index outweighs the cost the main benefit is the improvement in performance when searching the main costs of having an index are these first of all the additional space required to store each index and secondly the degradation of performance due to the need to maintain the index so let me give you an example if you have say four indexes on a table every time a change is made to a record in that table or every time a record is inserted into that table four indexes have to be updated so there is a performance cost also those four indexes each of them will use some disk space however if those indexes are used for a lot of searching the actual benefit of having the indexes may far outweigh that cost because when users are searching on those index fields they're going to get a much quicker response from the system now sql server automatically creates indexes for primary keys and foreign keys so that's done for you automatically but if you search on a field that does not have an index sql server has to look through every record to see if the value of that field in the record satisfies your search criteria so although we've looked at the cost of having each of these indexes the improvement in performance can be considerable if you've got a very large table millions of records perhaps and you may just be looking for one particular value in one field that may only occur in one record if you're searching on that field if it doesn't have an index sql server has to go through every single record in the table potentially to find the one that you want what we're now going to look at is the two types of indexes that you can have on a relational database specifically sql server and they are clustered indexes and non-clustered indexes and it's important to understand the difference between them a table can have only one clustered index and the clustered index defines how the data in the table are stored in terms of their sort order now the clustered index and the non-clustered index are actually completely different things the clustered index is the actual physical index that determines where the records in a table are stored physically on your storage media looked at rather simplistically non-clustered indexes point at the clustered index the non-clustered indexes are implemented in software and ultimately they get the location they need from the clustered index so the clustered index is the sort of building block of the indexing system and you can only have one cluster index because the records can only be arranged on your storage media in one way the clustered index is the only physical index i that relates to how the records in the table are actually stored a clustered index is automatically created when you define the primary key for a table if the table does not already have a clustered index clustered indexes are based on data structures called b trees b tree stands for balanced trees key values are created from one or more fields in each row of a table and these are stored in a b tree i'm going to show you a b tree in just a moment a table with a clustered index is called a clustered table a table without a clustered index is called a heap now you don't need to know exactly how a b tree works but you do need to have some idea of the structure of a bee tree and i'm going to just show you an example of a b tree here let's suppose that the leaves of this upside down tree represent 200 records in a table the way that the b tree structure works is that you start at the top in order to find the particular leaf of the tree that you want now i think the best way to imagine this is if you're familiar with an old style telephone directory so just a straightforward list of phone numbers in alphabetical order if you open the telephone directory somewhere in the middle of it think of the top of that tree where it says 1-200 what you would do if you were say looking for smith you'd say is this to the right of where i am or to the left of where i am so going down to the next intermediate level from the root of the b tree is what i want in 101 to 200 or one to a hundred do i go right or do i go left if it's on the right say i've opened it at matthews and i want smith i go right i open again now i've got peterson well peterson i need to go right again so that's into 151 to 200. and so what happens is you progressively you work your way through the tree or through the telephone directory to find the phone number of the person whose phone number you want now that's basically how a b tree works it is a clustered index with a structure like that and when sql server needs to find something it basically works its way through the tree to find the specific record that you want now let's take a look at non-clustered indexes you can create additional indexes on other key fields or combinations of fields as non-clustered indexes non-clustered indexes are b tree indexes and point to the base table which is either a clustered index or a heap if you create a primary key constraint on a table that already has a clustered index the addition of the primary key constraint causes the creation of a non-clustered index for the primary key now just to demonstrate non-clustered index what we're going to do is to create a non-clustered index on the country column in the customers table in our parts database so the procedure we're going to need to follow is this start ssms and connect select the database partsdb expand tables and select customers right click on indexes and select new index then we enter an index name and choose the type of non-clustered then we click add check the country column and click ok click ok again click save all on the file menu and then we should be able to check that our new index is okay so let's go through that so we've decided to improve performance by adding a non-clustered index to the country column in the customers table i've connected found the parts db expanded tables now i'm going to expand customers right click on indexes one of the options there is new index now notice that the index type has already been defaulted to non-clustered now i'm going to choose an index name there is a standard for naming these in fact they're probably several standards but the one that i use is to begin with ix underscore then the name of the table underscore and then the name of the field of fields in the index as you'll see in just a moment you can base the index on more than one field if you want to so i've called it ix customers country now what i'm going to do is to add fields and i'm going to add one country but if i click on add check the country field there click on ok you'll see it appears in the list there i can say what sort order i want i'll just go with the default of ascending if i made a mistake there's a remove button there as well that's all i want click on ok and now my new index should have been created save the changes to the schema and then let's just expand indexes and there you can see index customers country and you can see it's non-unique we know of course that we can have the same country repeated many times and we know that it's non-clustered there is only one clustered index and it's that one on the primary key the other indexes we've got are non-clustered so that's it on indexes let's have a quick review of the objectives understanding clustered and non-clustered indexes and their purpose in a database that is the end of this section please join me for the next one [Music] hello again and welcome back to our course on mta database fundamentals exam 98-364 in this section we're going to look at database security concepts let's start by taking a look at the objectives for this section first of all to understand the need to secure a database secondly to understand what objects can be secured and what objects should be secured and then understanding user accounts and roles let's look now at some basic security principles first of all development of a security plan should occur early in the development of a database secondly security must be carefully designed implemented and tested the security plan will depend heavily on the size and nature of the database and on its business requirements consider these two very different scenarios in the first case we have a small database containing information that is not confidential and is to be accessed by three or four users in this case the three or four users may each be able to do pretty much anything they want to the database and they have no confidentiality either from each other or from the outside world and therefore there's no particular reason to protect anything in the database in the second scenario we have a large database containing financial data that is to be maintained by a professional team and made available to the general public in this case what the professional team who are maintaining the database can do and what members of the general public can do are very likely to be very different things now when we're talking about security in general in relation to databases we have the concept of securables things that can be secured and different objects the databases themselves the tables in the database the logins the users different roles all of the kinds of objects that we're going to talk about in this section can be granted permissions to access these different securables in that first scenario we may well say that every object can access everything in the second scenario we need a very careful plan a very careful mapping from the objects to the securables in terms of who or what can access what now in order to demonstrate these general security principles i'm going to use a very straightforward simplified security model and this security model works simply on the principle going from left to right that a user can log into a database server and then via the master database which keeps information basically about who can access which databases a user is given an id and is allowed to access certain user databases what they're allowed to do in those user databases is governed by their roles that they may have and by specific permissions that they may have alongside this and optionally there may be guests with access to user databases and guests generally will have very limited access they will have very limited permissions on the database and in fact guest users may not be allowed at all but this is the very simplified security model that we're going to use now the first step in our security model is server level security this is where the user gets access first of all to the server users get access to the server via one of three methods either via a windows user login or by a membership of a windows user group or by a sql server specific login this third case is only available if sql server is using mixed mode security now i'm going to talk some more about that a little bit later on a login is controlled by a user's credentials which are often just a username and password once logged in users are known at the server level by their login either their sql server login if we're using a sql server specific login or by their windows login and once a user is logged in his or her rights are determined by their server roles now it's important that you're familiar with these fixed server roles and this table shows you the fixed server roles unfortunately you pretty much need to know these the most important one is the one right at the top sysadmin because assist admin can perform any activity in the server at all and that means sysadmin can do all sorts of dangerous things including granting other users rights to do anything and they can map any network login id to a specific database user id so that means the sysadmin really can do just about anything a user may belong to multiple roles let's just take a quick look at these roles a server admin can change server-wide configuration options and shut down the server security admin can manage logins and their properties they can grant deny and revoke server level permissions they can also grant deny and revoke database level permissions if they have access to a database we'll talk more about that a little bit later on as well and they can reset passwords for sql server logins process admin can end processes that are running in an instance of sql server setup admin can add and remove linked servers by using transact sql statements bulk admin can run the bulk insert statement which enables bulk insert data disk admin can manage disk files and db creator can create alter drop and restore any database so they are the fixed server roles now in addition there are some other roles that you need to be aware of one of them is the public role which is another fixed role but it can have object permissions like a standard role we'll talk about object permissions later as well every user is automatically a member of the public role and cannot be removed from the public role and for most users when they log in that's the role that they get so they don't get things like sysadmin or setup admin they get the public role and it is in effect a baseline level of permission for users you cannot modify the permissions assigned to a fixed server role for this reason it is possible to establish user-defined roles with very specific sets of permissions to avoid assigning a fixed server role that may have more permissions than a user needs and particularly in a large and sophisticated database where the security model is pretty complicated you may need to establish user defined roles so that people only have the permissions that they need so the user has logged in and they will have a server role or roles whether they can use a windows login or a sql server login will be determined by the authentication mode that has been set up and this is set up when sql server is installed windows authentication mode will only allow windows authentication so they'll log in by the windows login in mixed mode then both windows authentication and sql server user authentication are allowed but which authentication mode is in use is set up when sql server is installed although it can be changed sql server uses a two phase authentication scheme so once the user has been authenticated in the appropriate mode access can be granted to individual databases a sql server stores login information within its master database now in the case of windows authentication obviously windows users are created and managed using tools that are dependent on the version of windows that is in use so if you needed to set up a windows user that is a windows function once a user exists in windows they can then be recognized by sql server within sql server users can be managed using either the logins option within ssms or using t sql commands let's take a quick look at the dialog for creating a new login bearing in mind the ssms can also be used for other administrative functions such as removing a windows login note that when we talk about removing and creating new logins we're not actually creating or deleting the windows accounts we're just making them recognized or not recognized by sql server now there's a very important point to note here i've connected to my installation of sql server we're looking at logins logins are unrelated to a specific database at this point we haven't tried to get access to a database yet we're not talking yet about doing something to or in a database we're talking about access to the sql server installation itself access to the server so we look under security and within security we look at logins and if i expanded logins you could see all of the users that have logins to my installation of sql server if i wanted to create a new login right click click on new login and i get login new now that login new dialog lets me set up a login name to choose between windows authentication and sql server authentication i should point out that we're not going through all this so that you can actually do it it's just the concept of what you can do here and you can also do things like select the server roles now as you can see by default the new login here gets the public role if i wanted to give this person some of the other roles i could and in fact with the login that i'm logged in under i can do anything i'm effectively sysadmin on this so that's how we would set up a new login also if i wanted to delete a login i could right click on it and i could actually remove that login now apart from using the ssms gui interface to create and delete windows logins i can also set up windows authentication using t sql commands so to add a new windows user or group i can use the create login command like this create login and then the name of the existing windows user or group now bear in mind that here i've got a windows domain so it's on windows domain acme domain and the user is johnson b if i wanted to remove a windows user or group i would use drop login something like this now let's just briefly return to that login new dialog again notice that apart from the windows authentication option the sql server authentication option is there if i choose that option then the user will actually be set up as a sql server user so i'm going to need to give that user a password and i'm going to need to set some options related to passwords and so on and in fact when i set up a new login in either case there are some other options down here just to be aware of such as the default database for that user now for sql server users you have two options again you can either do administration using the ssms gui in a similar way to windows users as i just pointed out or of course you can use t sql commands so to add a new sql server user you would use a create login command so for example create login smith a the thing to note of course is when you're setting up a new sql server user you'll need to set up additional information as i pointed out just now and there is an alter login command and with auto login you can set up or modify all of the various properties that you might need to set for a sql server user so for example if you wanted to set the default database one of the things i pointed out just now having created the login smith a you could also login smith a with default database is one of the options equals partsdb now if you search for create login and all to auto login within the microsoft.com site and look within msdn or technet you will see all of the options that are available with those create login and auto login commands now let's go back to our simplified security model again we've managed to get as far as the server now we've logged in we have access to the server we've got our server roles the next thing we need to do is to be able to get into a database so we need to look at database security next once a user has gained access to the server access may be granted to individual user databases users are generally granted access to a specific database by adding them to the database so the list of users for the database and a guest database user can be set up for users without their own access so let's now turn our attention back to ssms and have a look at users within a specific database now what happens within the server and what the person who is doing administration on the server does for each user let's choose this user here if i right click on that user and look at that user's properties i'll see that the pages of properties include a user mapping page and that user mapping page lets me choose which databases that user can access so this user can access those two databases now within those databases i can also say what that user's username is now in this particular case that user owns those databases so their username is dbo but if i wanted to log them in with just a different username in any case i could now within one of those databases and let's go to partsdb within the partsdb database there is a security folder within that security folder there is a list of users and one of the users is dbo which is the name the mapped name given to that windows user when they're working within this database now again if i wanted to add a new user i could right click on users click on new user a database user new comes up i can put in a username i can choose the login name for the person who is going to correspond to this username and then i can assign to them database roles and i'm going to talk about database roles in just a moment the other thing i'm going to talk about in just a couple of minutes time is securables and remember i mentioned securables earlier on and this is where i specify which of the securables within the database they have access to and what sort of access they have so i can actually say what they can insert what they can select what they can delete and so on now again we'll come back to that in a couple of minutes time but for the moment that is one of the ways that i can add a user to a database and i've also shown you there how i can map the login to the username within the database so when it comes to granting access to a database as we've seen it can be done using the ssms gui it can also be done by issuing a t sql command from within the database it has to be done from within the database itself and it would be create user and then you use the login name that's a windows login name in that particular case and then the username for the user within the database that would set up the user with windows login that as database user tom b note also if you want to drop that user to remove that user's access to the database you would use drop user tom b now i mentioned just now database roles let's look at the fixed database roles there are again quite a few of them and again you pretty much need to know what these are db owner can perform all configuration and maintenance activities on the database and can also drop the database db security admin can modify a role membership and manage permissions adding principles to this role could enable unintended privilege escalation so you've got to be careful who has that role because it can easily escalate so that people get that role that you don't want to have that role db access admin can add or remove access to the database for windows logins windows groups and sql server logins db backup operator can backup the database db ddl admin can run any ddl command in a database db data writer can add delete or change data in all user tables db data reader can read all data from all user tables db deny data writer cannot add modify or delete any data in the user tables within a database and db underscore deny data reader cannot read any data in the user tables within a database so you need to know those and now we need to turn our attention to object ownership and security a critical aspect of security in sql server is ownership objects include tables views store procedures and columns every object is contained within a schema and a schema is owned and everything within the schema has the same owner if a user has access to a database that doesn't actually give them permissions by default what they get will depend on membership of roles but they can also be granted permissions on an individual basis so if a user has access to a database they may be granted permissions to individual database objects permission may be granted directly to the user or by assigning the permission to the role and assigning the user to the role this is the preferred approach to security in relation to object permissions also note that users may be assigned to multiple roles so there may be multiple permission paths working out what permissions a user has can be quite complicated particularly if the user has multiple roles and particularly if in a particular database permissions are complicated anyway now before i go into object permissions in a bit more detail just like to show you something very quickly in ssms i'm in the parts database and if i expand tables and look at the parts table look at the properties of the parts table notice that one of the pages in the table properties is permissions now i'm not going to do this now but basically if i want to assign permissions to either users or roles on an object such as a table and bear in mind that i can assign permissions on other objects such as columns or stored procedures then there is usually a dialogue like this one where you can assign those permissions in a very flexible and powerful way now i'm not going to go through that here in ssms you don't need to be able to do that you only really need to understand the basic principles and we're going to look at that now and a couple of examples using t sql commands so let's look then at object permissions object permissions are assigned by means of grant revoke and deny revoke removes the permission assigned deny overrules grant and don't forget a user may have multiple permission paths to an object now the rule to remember is that deny always rules so if there are five permission paths to a particular object for a user and four of them grant access and one denies then access is denied deny always overrules grant permissions can be administered using ssms or t-sql commands now the basic object permissions are these select is the right to select data and select permission can be applied to specific columns insert is the right to insert data update is the right to modify existing data update rights for which a where clause is used do require those select rights as well and update permissions can be set on specific columns in a table delete is the right to delete existing data dri is the right to create foreign keys with dri and execute is the right to execute stored procedures or user defined functions and if you're wondering what dri is it's declarative referential integrity so let's look at some examples of granting object permission using t sql commands grant select on parts to tom b would give the user tombi the permission to select on the parts table you can grant more than one permission to more than one user at the same time so for instance you could say grant both insert and delete on the customers table to jane c and tom b and also there are various additional clauses you might want to look at grant and deny and revoke in msdn or technet to see all the various options but this is an important one grant delete on orders to jane c so jane c has the permission to delete from the orders table with grant option means that not only that but jane c can pass this permission on to somebody else revoke all on parts to tom b says whatever permissions have been given to tom b on the parts table are to be revoked now note that is not the same as saying that tom b has permissions denied it just means that anything that's been said about permissions for him on the parts table is revoked and the final example deny select on customers to carl d so carl d cannot select from the customers table so that's it just a quick review of the objectives understanding the need to secure a database understanding what objects can be secured understanding what objects should be secured understanding user accounts and understanding roles that's the end of this section please join me for the next one [Music] hello again and welcome back to our course on mta database fundamentals exam 98-364 in this section we're going to look at database backups and restore let's start with the objectives understanding various backup types such as full and incremental understanding the importance of backups and understanding how to restore a database let's start by looking at the purpose of database backups and first of all i should point out as usual that there will be no practical element to this in the exam so you don't actually need to be able to run a backup or even to restore from a backup but i am going to just demonstrate one or two points within this section the primary purpose of database backups is to be in a position to recover the live database if it is lost or damaged now many people think that database backups and restores probably about the most dull thing you can deal with in relation to databases and i suppose that to some extent that's true but having worked with databases over many years there is seldom as much excitement as there is when a database is broken when a live database is lost or damaged that's when things really start to happen and if it's your job to recover that situation you need to know what you're doing now examples of possible causes of loss of failure are hardware failure this might be as simple as a power cut a disk failure and software failure software failures are probably a little bit rarer than hardware failures in actual number terms in terms of damaging a database but they do happen versions of software are released aren't really ready that have unknown bugs part of the data in a database get lost maybe something gets completely deleted but you can get problems due to software failures as well as hardware failures to some extent which of those has caused a particular problem can be a little bit incidental the fact that you need to recover the database recover the situation is what's important you may as a separate issue want to deal with what caused that problem so that you can hopefully stop it happening again but when it comes to backup and restore we don't draw a distinction between these two things database backups are also sometimes used to move a database or to use a copy elsewhere or for another purpose we don't only take backups in order to secure our live system sometimes you may want to take a backup you may want to use a copy of the database somewhere else or perhaps do a bit of testing say with a new piece of software or on some new hardware that kind of thing now this may seem a little bit back to front but before you even think of performing database backups you need to understand database recovery because the recovery process will dictate what database backups you run although you will need to backup your database before you recover it you will need to design and understand your recovery process before you perform backups you can back up according to a plan but you'll probably be recovering in an emergency i know people who've run very good backup system for many years never need to do a recovery they thought everything was fine then something did happen and they had no idea what to do with all those backups they've made make sure you know how to recover the database and make sure you've tested recovery a few times it's even a good idea almost like a fire drill to do a bit of a recovery drill every now and then just to make sure that your whole team are familiar with what happens when you need to recover the database how much time and expense you can devote to this of course will depend on your individual circumstances now a few more points about database recovery sql server uses what's called a right ahead transaction log this means that every transaction is written to the log before the transaction is actually performed now the reason this is important is that every transaction can be recovered right up to the point of failure provided that the transaction log is not lost or damaged and another very important point is this the data files the data files that actually hold your data part of the database and transaction log may be stored on different hardware to support this now one of the single most important things to bear in mind is that if everything the database the backups the transaction log everything if they're all stored on the same device say the same hard drive if it's that hard drive that fails then you have no way of recovering the situation that's why it's so important that you give a lot of careful thought to where the data files are stored where the backups are stored and where the transaction log is stored and usually they need to be different places now the approach we take with sql server is to choose one of its three recovery models your choice of recovery model will reflect your overall recovery plan which in turn reflects your organization's tolerance level for lost transactions now the available recovery models are these first of all there is the simple recovery model where there are no transaction log backups then we have in the middle what's called the bulk logged recovery model where we do minimal logging about transactions but full logging of other transactions and then we have the full recovery model now i'm going to explain all three of these in more detail as we go along basically for most people the choice is between simple and full bulk logged is a bit of a special case but having said that for the people for whom it's important it is very important so you need to understand what bulk logged is all about the key difference between the models is in what happens with the transaction log and in order to choose between these you need to understand the essential features of each model now before i show you the features of each model i just like to introduce you to a few other backup terms and options some of the terms that i'm going to use now are ones that we're going to use later on in the section a full backup is a complete backup of all data a differential backup is a backup of all changes since the last full backup an incremental backup is a backup of all changes since the last backup it's very important to know the difference between a differential backup and an incremental backup if you have a backup schedule something along the lines of taking a full backup at the weekend and then each day taking a differential backup the differential backup will get bigger every day because it's always a backup of the changes since last weekend if instead of using differential backups you use incremental backups then each incremental backup contains the changes since the last backup so on monday you'll have the changes since the weekend on tuesday you'll have the changes since monday on wednesday you'll have the changes since tuesday and so on now as we'll see later on choices between these various options depend on your own particular situation a big advantage of a differential backup is that if you say had to do a restore today you'd only have to use the full backup from the weekend and yesterday's differential backup if you were using incremental backups then you'd have to do the full restore from the weekend and then each of the incremental backups since the weekend would need to be restored in sequence as well now you may prefer one of those approaches or the other but one of the advantages of the incremental backup is that it takes less space one of the advantages of the differential backup is it tends to be quicker to restore and it's those sort of factors that are very important in your backup and restore plan your recovery plan now let's look at these other terms quickly partial backup is a backup of the primary file group and other specific and optional files the transaction log backup is the backup of all transactions in the log a file or file group backup is a backup of all data in a file or file group now the partial backup is a special case of that as i mentioned much earlier in the course of course your database particularly if it's big and complex may be split over various devices and you may have parts of the database divided into file groups you may want to back up those file groups separately and it may be that in your primary file group you have your core information and in your secondary and other file groups you may have information that's used perhaps less often or it's less critical now rather than do a backup of everything which may be a huge amount of data you may actually be forced to back up your database in different file groups a file or file group differential backup is a backup of all changes since the last file of file group backup and a copy only backup is a backup of all data without affecting the regular backup and restore activities so this is the sort of backup you do when you just really want a copy of the database to use for a different purpose and it's not part of the normal backup schedule and regime so let's briefly look at the main features of each of the recovery models and then we'll go through each of them in more detail in the simple model the transaction log is not backed up in fact the transaction log is truncated regularly to reclaim space in the simple model you can only recover to the last full or differential backup and changes since the last backup are not protected so they may need to be redone in the full model the transaction log is backed up and this means that you can recover to a point in time in general if you want to recover to a specific point in time you can however there is a situation where that isn't possible and that is where the current transaction log is damaged and in that case recovery is only possible to the last transaction log backup in the bulk logged model basically you have the same situation as the full model but with an important difference and that is that if any bulk copy operations have been performed the logging on them is not complete a form of minimal logging is used on those with these bulk copy operations you're talking about very large numbers of for instance data inserts transactions being processed and so on are pushed into the database very quickly and in order that the performance of those doesn't become a problem minimal logging is done now if you've done that kind of operation since the last backup that's a different story altogether so with a bulk logged model you can always recover to the last full or differential backup but if you want to be able to recover to a later point in time this can only happen if the transaction log backup is not lost and there have been no bulk copy operations so they're the main features now let's look at each of those models in turn in a little bit more detail first of all the simple recovery model this is generally used for non-critical databases such as demo and test databases containing read-only data this is very often the model that people use while they're developing a database and before it goes into production it keeps the transaction log small by emptying it on checkpoints once a transaction has been confirmed in the database its log entry may be removed and the space reused a typical backup plan in the simple recovery model would be to do a full backup once a week and then maybe a differential backup at the end of each working day so if i were using the simple recovery model and i say did a full backup on a friday night i do a differential backup at the end of monday and at the end of tuesday and so on and if the system failed on a wednesday worst case scenario i would go back to tuesday night's differential backup and anything i'd done on wednesday would need to be redone if i need to recover in that situation the way i do a recovery is to restore from the most recent fall backup and then to restore the most recent differential backup if in fact there has been a differential backup since the last full backup so that's the simple recovery model now let's look at the full recovery model this is the most robust recovery model all transactions including bulk transactions are fully logged every committed transaction up to the point of failure can be restored one of the important things and i mentioned earlier on the right ahead nature of transactions is that the transactions are written to the log before they're performed so even if a transaction fails part way through it will still have been written to the log and therefore we know that that transaction will have been due to have been performed but we'll also know that it hasn't actually been performed there are some potential disadvantages to the full recovery model and these contribute to the reason that it's not used all the time some operations notably bulk operations will be slower and this goes back to the reason that we have the bulk logged option as a recovery model the transaction log can get very large i think if you think of say one of the major financial institutions dealing with say credit card transactions or an online store you can probably imagine their transaction logs get to be pretty big and recovery using such a large transaction log can take some time the reality of the situation of course is that there may not be any alternative this is the preferred option for production databases now as far as backup and recovery go a typical backup plan would be a full backup maybe three times a week two times a week differential backups on each of the other days and then during each working day the transaction log may be backed up several times as well so if you needed to recover from a failure the first thing you do is to do a backup of the current transaction log before you do anything else you secure the current transaction log then you restore the most recent full backup then you restore the most recent differential backup if there is one so if that's necessary and then you restore in sequence all the transactional backups since the most recent full or differential backup as necessary and that of course will include the backup of the current transaction log that you performed as the first step in this recovery plan so that's how the full recovery model works now let's look at the bulk logged recovery model this is similar to the full recovery model as i mentioned earlier but some operations are minimally logged in order to make those operations run faster this option is only useful when a database is subject to a large number of bulk logged operations and improving performance of those operations is important you don't want them slowed down by doing full logging otherwise use the full recovery model if you do need to use the bulk log recovery model every bulk logged operation should be followed immediately by a transaction log backup so if you do need to use this model whenever you have one of these major bulk logging operations do a transaction log back up afterwards note also that you can temporarily switch to and from this model if you normally use the full recovery model so if this is just something that happens from time to time run on the full recovery model switch to this one temporarily and then switch back again now what i want to look at next very briefly and only an outline is how you set or change the recovery model in ssms this can be done from the options tab of the database properties dialog and i'll show you that in just a moment but if you were doing it with t sql it's pretty straightforward you can determine the current recovery model for each database using this t sql command for example again i'll show you this in ssms in just a moment you can actually ask sql server to tell you what the recovery model is for each database within your sql server installation and it's just to select select the name and the recovery model description from the system table sys.databases i'll show you that in just a moment and to change the recovery model being used again it's absolutely straightforward with t sql alter database parts db for example set recovery full sets the recovery model to the full recovery model so let's just have a look at those in ssms so first of all take a look at that select statement select name comma recovery model desk from sys.databases sys.databases is the system table that lists the databases in my sql server instance here and now i'm just asking for a list of the names and recovery models click on execute there are my databases and as you can see they all currently have the simple recovery model now let me create a new query and now i'm going to use t sql to change the recovery model on one of those databases so i've said alter database parts db set recovery full let's execute that statement all seems to be okay let's switch back to my select execute my select again look at the result down at the bottom and as you can see the parts db now has a recovery model of full if i go to the databases folder here on the left in the object explorer on parts db if i click on the properties options there click on options one of the options there is the recovery model as you can see it's now set to full i can set it back to simple or indeed bulk logged choosing the option there and click on ok obviously i could change it back using t sql if i wanted to so click on ok let's run my execute query again and now you can see all my databases are back to having the simple recovery model now let's take a look at performing a backup or restore using the ssms gui interface once again you don't need to be able to do all of this or even have gone through it all yourself but i want to show you how to access it in ssms and some of the basic options that you would choose from in ssms select the database right click select tasks and then backup or restore the backup dialog shows options that include the backup type whether you're backing up the whole database or files and file groups whether the backup needs to have an expiry date on it bear in mind that there will be possibly a date beyond which the backup will be no further use and if you have a definite schedule you may want to denote that a particular backup's going to expire on a certain day and then the destination where is this backup going to go if you're doing a restore you can restore a database or you can restore files and file groups so let's have a look at that now in ssms so over in the object explorer on the left if i right click on partsdb i then have tasks and then on the tasks menu i have backup and restore if i choose the restore option i can choose to restore a whole database or files and file groups i'll leave you to experiment with those but let's look at the backup option that brings up this backup database dialog and you can set the options that i've just mentioned and many others as well so you can choose the database you want to backup in this case partsdb recovery model is simple backup type i have a choice of full or differential at the moment as i have no alternative files and file groups i can only backup the whole database i can give the backup set a name and a description i can set expiry so i can say say it's going to expire after 7 days or 14 days or something and i can also specify where i want to back it up to now you won't be surprised to learn that you can also perform a backup or restore using t sql commands and i'm really just going to show you a couple of very straightforward examples here just so you can get a feel for those so a typical backup command would be something like this backup database partsdb to disk and then there's the name of the disk when you run a backup or indeed when you run a restore it's almost always the case that you would have many options to go with it as well simple one here would be to give the backup a name like with name equals a restore would look something like this restore database partsdb from and then you might have a backup device that it's stored on or you may actually restore it from a disk location etc so that's the basics of backup and restore using t sql now the last couple of things i'd like to look at in this section are just some general points about some of the things i've mentioned and not said very much about but points that you may need to be aware of first of all as far as file group backups are concerned a full backup of a large database can consume a lot of space and time as i mentioned and it may physically be impossible to back up a whole large database in one go apart from the fact that it may have a huge impact on your operations a file or file group backup can just back up a portion of a database and in fact you can use file group backups for another advantage and that is if you use them with differential and transaction log backups you can recover just a portion of a database so you can effectively slice your database into portions and if things go wrong you may only need to deal with recovering a portion the other thing is if a file group does not include the primary file group it may be possible to keep the database online whilst the recovery is proceeding if your business allows for a file group to exist which perhaps has a lot of data in it but which is not essential to the use of the database then you could in fact deal with that particular file group separately and you wouldn't even need to stop using the database if you had to recover that particular file group if it was just that file group that suffered from a problem something else i should mention is in relation to transaction log backups transaction log backups contain only the changes in the transaction log some of the transactions in a transaction log have been committed and some have not the committed transactions are stored in the backup and removed from the log when the backup is run so if you were doing a recovery the part of the recovery usually a latter part where you're recovering the transactions in the transaction log is only going to restore the committed transactions the transactions in the log that haven't been committed will still be in the log as non-committed transactions and here is an example of part of a typical transaction log backup command just some idea of what it looks like backup log parts db to disk so you might be backing up the transaction log for that database to a particular disk file and one final set of points to do with scheduling backups a backup operation has minimal effect on running transactions under normal circumstances backups can be run during normal operations and generally speaking transactions that are in progress should not be delayed by running a backup now exactly what impact running a backup has will very much depend on your own business and it will depend on how good your hardware is how well your database is set up and so on but in general terms you should be able to run backups in the business without disrupting normal operations to the point that the disruption becomes a problem so finally then a look again at the objectives for this section understanding various backup types such as full and incremental understanding the importance of backups and understanding how to restore a database that is the end of this section and in fact the end of this course i hope you've enjoyed the course as much as i've enjoyed preparing it for you and presenting it to you if you are aiming to do the mta 98 364 exam i wish you very good luck with the exam make sure you work through all of the examples that go with the course and any more that you can get access to and also please make sure to do some at least of that extra reading there's an absolute wealth of information on the microsoft site on the msdn section and within technet and microsoft.com in general various forums about all of the aspects of database fundamentals administration and it's a good idea as a good example to look at some of the examples of database backup and restore commands and some of the options that go with them that's it from me i hope to see you again online soon goodbye for now

Channel: Paul Browning

Views: 799

Rating: 5 out of 5

Keywords:

Id: -VjPgCRJJpI

Length: 413min 6sec (24786 seconds)

Published: Mon Sep 27 2021