In this lesson, you will learn how to format the text and numbers.
In our last lesson, we developed a stored procedure that does some pretty cool things. It combines a lot of concepts we've learned thus far and even gives the user some feedback about what's happening. However, there are still some things we could do to make it work a little better.
As you've realized by now, SQL is very different than most programming languages. Most programming languages deal with sets of linear instructions that kick off when something happens. For example, a user clicks a button that says print, a series of commands kick off to print the document. It might also check to see if the printer is on or available before proceeding.
If you know a little bit about SQL server, you might have been wandering why we have not spoke about stored procedures until now. Stored procedures are a powerful way to create reusable packages of SQL code that live in your database. We didn’t get into it earlier because stored procedures are basically just a wrapper for everything we’ve learned so far.
Non-clustered indexes are a very important component in SQL server. We’ve discussed them briefly in a previous chapter. They basically act like an index in the back of a book. They give you a quick shortcut to find data that’s organized in an alternative way. Today, we’ll learn how they work and how SQL utilizes them. This will help us understand where to put them when designing a database.
Thus far, we’ve only worked with the actual data inside existing tables. But now we’re going to learn about adding new tables to our database. Later one we’ll get into the much more complicated steps of designing a good database. For today’s lesson, we’ll just keep it simple. You’ll learn how to get a new table added to your database using the CREATE SQL statement.
Before we go any further, we need to learn about a very powerful component of SQL server. The variable. If you have programmed in any programming language before, you already know what a variable is. If you haven’t, think of it as a sticky note that has space for you to write one thing. This sticky note has a predetermined lifespan, during which you can erase what it says and replace it with something else. You can ask to see what’s on the sticky note at any time, too.
Those of you who survived the subquery lesson are probably asking, “Isn’t there a simpler way to do all that?” The answer is, yes, there are simpler ways. We looked at the complex way first to help you understand how these things work. There are lots of alternatives to subqueries. We’ll take a look a brief look at each option for organizing or presenting a subset of data in today’s lesson and then discuss them in detail in the following lessons.
Now that we’ve learned how to insert and update data in SQL, we need to learn how to delete it. Deleting data is a little different than the other commands we’ve looked at. Deleting doesn’t deal with fields. It is strictly about rows of data. If you want to delete the contents of a single field, this is actually an update. Let’s jump right in with a look at the syntax for deleting a row or record in SQL.
Sometimes SQL code gets really complicated! We can’t always do everything we want in one neat statement. Before we delve into deleting data, we’re going to take a break and look at one of the trickier SQL concepts that we’ll use on a regular basis.