Part 2 of 2 in the SQL Server 2012 Querying (70-461) series is designed to teach students how to query and work with a SQL Server 2012 database. Skills gained will include querying a database using joins and data aggregations and query optimization. This course includes basic management of database objects and using XML SQL Server.
Managing Transactions Up until now, every data modification that we have made has been on a single table. Now while that works very well for going in and doing simple demos, and you may actually find yourself just needing to update a single table more often than not, because of the fact that your database is going to be normalized or in other words that your data is going to be split up into multiple tables, you're going to need to update multiple tables. The problem that we have when we're trying to update multiple tables is how do we make sure that our database is always going to be left in a consistent state. What happens if part of the operation succeeds and the other part of the operation fails? This is where managing transactions comes into play, and in this lesson we want to take a look at how we're going to be able to control our transactions, the different types of locking that we can perform, and how we can ensure that we're going to limit our exposure to potential problems.
XML Basics One of the things that's become increasingly pervasive throughout, well frankly, all of technology, is the need to send signals or make calls from one system to another. For example, if you fire up an application on your smart phone that needs to access something from the internet, typically what's going to wind up happening is it's going to make a call over the internet by utilizing a little thing known as a web service. Now I'm not going to get into the specifics of a web service, but the big thing that I want to mention here is that when you call that little web service, behind the scenes, the data that's going to be sent out, the data that's going to be sent back, is going to be formatted in a special way by utilizing XML.
Data Validation with Constraints One of your biggest responsibilities as a user of a database is to ensure that invalid values are never placed inside of the database. Now we're going to talk about why that is in just a couple of moments, but what I do want to highlight is the fact that you can use data types to help you out. So, for example, if I specify DateTime as a data type, then that's all that you're going to be able to provide is just simply a DateTime. But, of course, validation frequently needs to go beyond that, so what we're going to see in this lesson is a series of different options that we have available to us when it comes to validating our data.
Creating and Modifying Triggers As we mentioned in another lesson, one of the things that you need to ensure is that all data that goes into a database is kept valid at all times. One way that we can do this is through the use of constraints. And constraints, while fast and to a certain extent rather powerful, are going to lack the full capabilities of SQL. So if you need to be able to modify other values based on something that happened in a particular table or anything like that, you're not going to be able to perform that operation with a constraint. This is where triggers come into play. Let's talk first about what a trigger is and then how we're going to create them.
Creating and Modifying User Defined Functions Whenever you're creating code, one of the biggest things that you're looking for is reuse and modularity. And this is where User Defined Functions can help save us an awful lot of time. They are actually probably one of the most under-utilized components inside of SQL, but if properly implemented, they can actually really help out in regards to executing queries, being able to reuse code, and sometimes even a little bit of performance. In this lesson, what we want to do is we want to start off by talking a bit about the basics of User Defined Functions, and then we want to roll into how to actually create them.