Well, here we are. We’re at the beginning, but, if you’re someone who’s read my Professional level titles before, you’ll... more

Well, here we are. We’re at the beginning, but, if you’re someone who’s read my Professional level titles before, you’ll find we’re not quite at the same beginning that we were at in previous editions. SQL Server has gotten just plain too big, and so the “Pro” is going to become a little bit more “Pro” in level right from the beginning.

So, why am I still covering objects first then? Well, because I think a review is always in order on the basics, because you may need the see what’s changed versus previous editions, and, last but not least, I still want this book to retain some of its use as a reference (I hate having to use 25 books to get all the info that seems like it should have been in just one).

With this in mind, I’m going to start off the same way I always start off—looking at the objects available on your server. The difference is that I’m going to assume that you already largely know this stuff, so we’re going to move along pretty quickly and make a rather short chapter out of this one.

... less

Okay, so if you took the time to read Chapter 1, then you should now have a feel for how fast we’re moving here. Again, if... more

Okay, so if you took the time to read Chapter 1, then you should now have a feel for how fast we’re moving here. Again, if you’re new to all of this, I would suggest swallowing your pride and starting with the Beginning SQL Server 2005 Programming title—it covers the basics in far more detail. For this book, our purpose in covering the stuff in the first few chapters is really more about providing a reference than anything else, so we really are just taking a whirlwind tour. With that in mind, it’s time to move on to the toolset. If SQL Server 2005 was not your first experience with SQL Server, then this is a place where you’ll want to pay particular attention. With SQL Server 2005, the toolset has changed—a lot.

Virtually everything to do with the SQL Server toolset has seen a complete overhaul for SQL Server 2005. Simplifying the “where do I find things?” question was a major design goal for the tools team in this release. For old fogies such as me, the new tools are a rather nasty shock to the system. For people new to SQL Server, I would say that the team has largely met that simplification goal. In general, there are far fewer places to look for things, and most of the toolset is grouped far more logically.

The tools we will look at in this chapter are:

• SQL Server Books Online

• The SQL Server Configuration Manager

• SQL Server Management Studio

• SQL Server Business Intelligence Development Studio

• SQL Server Integration Services (SSIS): including the Import/Export Wizard

• Reporting Services

• The Bulk Copy Program (bcp)

• Profiler

• sqlcmd

... less

Well, here we are still in review time. In this chapter, we will take a whirlwind tour of the most fundamental... more

Well, here we are still in review time. In this chapter, we will take a whirlwind tour of the most fundamental Transact-SQL (or T-SQL) statements. As with all of the first few chapters of this book, we will be mostly assuming that you know a lot of this story already—so the goal here is to provide review material plus set you up to “fill in the blanks” of your learning.

T-SQL is SQL Server’s own dialect of Structured Query Language (or SQL). T-SQL received a bit of an overhaul for this release, with many new programming constructs added. Among other things, it was converted to be a Common Language Runtime (CLR) compliant language—in short, it is a .NET language now. While, for SQL Server 2005 we can use any .NET language to access the database, in the end we’re always going to be using some SQL for the root data access, and T-SQL remains our core language for doing things in SQL Server. For purposes of this chapter though, things are pretty much as they always have been—there is very little in the way of changes in this most fundamental statements.

The T-SQL statements that we will learn in this chapter are:

• SELECT

• INSERT

• UPDATE

• DELETE

These four statements are the bread and butter of T-SQL. We’ll learn plenty of other statements as we go along, but these statements make up the basis of T-SQL’s Data Manipulation Language—or DML. Since you’ll, generally, issue far more commands meant to manipulate (that is, read and modify) data than other types of commands (such as those to grant user rights or create a table), there really isn’t anything more fundamental than these.

In addition, SQL provides for many operators and keywords that help refine your queries. We’ll review some of the most common of these, including JOINs, in this chapter.

While T-SQL is unique to SQL Server, the statements you use most of the time are not. T-SQL is entry-level ANSI SQL-92 compliant, which means that it complies up to a certain level of a very wide open standard. What this means to you as a developer is that much of the SQL you’re going to learn in this book is directly transferable to other SQL-based database servers such as Sybase (which, long ago, used to share the same code base as SQL Server), Oracle, DB2, and MySQL. Be aware, however, that every RDBMS has different extensions and performance enhancements that it uses above and beyond the ANSI standard. I will try to point out the ANSI versus non-ANSI ways of doing things where applicable. In some cases, you’ll have a choice to make—performance vs. portability to other RDBMS systems. Most of the time, however, the ANSI way is as fast as any other option. In such a case, the choice should be clear—stay ANSI compliant.

... less

In this chapter, we will be studying the syntax to create our own tables. We will also take a look at how to make use of... more

In this chapter, we will be studying the syntax to create our own tables. We will also take a look at how to make use of the SQL Server Management Studio to help with this (after we know how to do it for ourselves).

However, before we get too deep in the actual statements that create tables and other objects, we need to digress far enough to deal with the convention for a fully qualified object name, and, to a lesser extent, object ownership.

... less

You’ve heard me talk about them, but now it’s time for a serious review of keys and constraints. SQL Server has had many... more

You’ve heard me talk about them, but now it’s time for a serious review of keys and constraints. SQL Server has had many changes in this area over the last few versions, and that trend has continued with SQL Server 2005.

We’ve talked a couple of times already about what constraints are, but let’s review in case you decided to skip straight to this chapter.

A constraint is a restriction. Placed at either column or table level, a constraint ensures that your data meets certain data integrity rules.

This gets back to the notion that I talked about in Chapter 1, where ensuring data integrity is not the responsibility of the programs that use your database, but rather the responsibility of the database. If you think about it, this is really cool. Data is inserted, updated, and deleted from the database by many sources. Even in standalone applications (situations where only one program accesses the database), the same table may be accessed from many different places in the program. It doesn’t stop there though. Your database administrator (that might mean you if you’re a dual role kind of person) may be altering data occasionally to deal with problems that arise. In more complex scenarios, hundreds of different access paths can exist for altering just one piece of data, let alone your entire database.

Moving the responsibility for data integrity into the database itself has been revolutionary to database management. There are still many different things that can go wrong when you are attempting to insert data into your database, but your database is now proactive rather than reactive to problems. Many problems with what programs allow into the database are now caught much earlier in the development process because, although the client program allowed the data through, the database knows to reject it. How does it do it? Primarily with constraints. (Data types and triggers are among the other worker bees of data integrity.)

In this chapter, we will look at three types of constraints at a high level:

• Entity constraints

• Domain constraints

• Referential integrity constraints

At a more specific level, we’ll be looking at the specific methods of implementing each of these types of constraints:

• PRIMARY KEY constraints

• FOREIGN KEY constraints

• UNIQUE constraints (also known as alternate keys)

• CHECK constraints

• DEFAULT constraints

• Rules

• Defaults (similar to, yet different from, DEFAULT constraints)

SQL Server 2000 was the first version to support two of the most commonly requested forms of referential integrity actions: cascade updates and cascade deletes. These were common complaint areas, but Microsoft left some other areas of ANSI referential integrity support out. These were added in SQL Server 2005. You look at cascading and other ANSI referential integrity actions in detail when you look at FOREIGN KEY constraints.

We also take a cursory look at triggers and stored procedures as a ways of implementing data integrity rules.

... less

It was a tough decision. Advanced query design before cursors, or cursors before advanced query design? You see, it’s something... more

It was a tough decision. Advanced query design before cursors, or cursors before advanced query design? You see, it’s something of a chicken and egg thing (which came first?). Not that you need to know anything about cursors to make use of the topics covered in this chapter, but rather because we’ll be discussing some benefits of different query methods that avoid cursors—and it really helps to understand the benefits if you know what you’re trying to avoid.

That said, I went for the advanced queries first notion. In the end, I figured I wanted to try to get you thinking about non-cursor-based queries as much as possible before we start talking cursors. Since I figure that a large percentage of the readers of this book will already have experience in some programming language, I know that you’re going to have a natural tendency to think of things in a procedural fashion rather than a “set” fashion. Since cursors are a procedural approach, the odds are you’re going to think of the more complex problems in terms of cursors first rather than how you could do it in a single query.

Suffice to say that I want to challenge you in this chapter. Even if you don’t have that much procedural programming experience, the fact is that your brain has a natural tendency to break complex problems down into their smaller subparts (subprocedures—logical steps) as opposed to as a whole (the “set,” or SQL way). My challenge to you is to try and see the question as its whole first. Be certain that you can’t get it in a single query. Even if you can’t think of a way, quite often you can break it up into several small queries and then combine them one at a time back into a larger query that does it all in one task. Try to see it as a whole, and, if you can’t, then go ahead and break it down, but then recompile it into the whole again to the largest extent possible.

Several of the topics in this chapter represent, to me, significant marks of the difference between a “beginner” and “professional” when it comes to SQL Server programming. While they are certainly not the only thing that marks when you are a true “pro,” developers that can move from “Yeah, I know those exist and use one or two of them” to using them to make unsolvable queries solvable are true gold. I write on these subjects for beginners to let them know they are there and give them a taste of what they can do. I write on them for professionals because full understanding of these concepts is critical to high-level success with SQL Server (or almost any major DBMS for that matter).

In this chapter, we’re going to be looking at ways to ask what amounts to multiple questions in just one query. Essentially, we’re going to look at ways of taking what seems like multiple queries and place them into something that will execute as a complete unit. Writing top-notch queries isn’t just about trickiness or being able to make them complex—it’s making them perform. With that in mind, we’ll also be taking a look at query performance, and what we can do to get the most out of our queries.

Among the topics we’ll be covering in this chapter are:

• Nested subqueries

• Correlated subqueries

• Derived tables

• Making use of the EXISTS operator

• Using external calls to perform complex actions

• Optimizing query performance

We’ll see how by using subqueries we can make the seemingly impossible completely possible, and how an odd tweak here and there can make a big difference in our query performance.

... less

And so I come to another one of those things where I have to ponder how much to assume you already know. “To normalize, or... more

And so I come to another one of those things where I have to ponder how much to assume you already know. “To normalize, or not to normalize—THAT is the question!” Okay, the real question is one of whether you already understand the most basic tenants of relational database design yet or not. Since you come to this book with a degree of experience already, I’m going to take an approach that assumes you’ve heard of it, know it’s important, and even grasp the basics of it. I’m going to assume you need the information filled in for you rather than that you are starting from scratch.

With the exception of a chapter or two, this book has an Online Transaction Processing, or OLTP, flare to the examples. Don’t get me wrong; I will point out, from time to time, some of the differences between OLTP and its more analysis-oriented cousin Online Analytical Processing (OLAP). My point is that you will, in most of the examples, be seeing a table design that is optimized for the most common kind of database—OLTP. Thus, the table examples will typically have a database layout that is, for the most part, normalized to what is called the third normal form.

What is “normal form”? We’ll start off by taking a very short look at that in this chapter and then will move quickly onto more advanced concepts. For the moment though, just say that it means your data has been broken out into a logical, nonrepetitive format that can easily be reassembled into the whole. In addition to normalization (which is the process of putting your database into normal form), we’ll also be examining the characteristics of OLTP and OLAP databases. And, as if we didn’t have enough to do between those two topics, we’ll also be looking at many examples of how the constraints we’ve already seen are implemented in the overall solution.

... less

Indexes are a critical part of your database planning and system maintenance. They provide SQL Server... more

Indexes are a critical part of your database planning and system maintenance. They provide SQL Server (and any other database system for that matter) with additional ways to look up data and take shortcuts to that data’s physical location. Adding the right index can cut huge percentages of time off your query executions. Unfortunately, too many poorly planned indexes can actually increase the time it takes for your query to run. Indeed, indexes tend to be one of the most misunderstood objects that SQL Server offers and, therefore, also tend to be one of the most mismanaged.

We will be studying indexes rather closely in this chapter from both a developer’s and an administrator’s point of view, but in order to understand indexes, we also need to understand how data is stored in SQL Server. For that reason, we will also take a look at SQL Server’s data storage mechanism including the index allocation strategies SQL Server employs and internal structures.

... less

Up to this point, we’ve been dealing with base objects—objects that have some level of substance of their own. In contrast... more

Up to this point, we’ve been dealing with base objects—objects that have some level of substance of their own. In contrast, this chapter goes virtual (well, mostly) to take a look at views.

Since we’re assuming, in this book, that you already know something about SQL Server, I am, as I have in most of the previous chapters, going to rip through the basic part of views in more of a review approach. We will, however, be introducing some more advanced concepts—particularly in the area of partitioned and index views.

Views have a tendency to be used either too much, or not enough—rarely just right. When we’re done with this chapter, you should be able to use views to:

• Reduce apparent database complexity for end users

• Prevent sensitive columns from being selected, while still affording access to other important data

• Add additional indexing to your database to speed query performance—even when you’re not using the view the index is based on

• Understand and utilize the notion of partitioned tables and the early stages of federated servers (often used for very high-end scalability)

A view is, at its core, really nothing more than a stored query. You can create a simple query that selects from only one table and leaves some columns out, or you can create a complex query that joins several tables and makes them appear as one.

... less

Geez. I’ve been writing too long. For some reason when I see the phrase “Scripts and Batches” it reminds me of the old song... more

Geez. I’ve been writing too long. For some reason when I see the phrase “Scripts and Batches” it reminds me of the old song “Love and Marriage.” While scripts and batches do go together like a horse and carriage, they are hardly as lyrical—but I digress. . . .

We have, of course, already been writing SQL scripts. Every CREATE statement that you write, every ALTER, every SELECT is all (if you’re running a single statement) or part (multiple statements) of a script. It’s hard to get excited, though, over a script with one line in it—could you imagine Hamlet’s "To be, or not to be . . . ?" if it had never had the following lines—we wouldn’t have any context for what he was talking about.

SQL scripts are much the same way. Things get quite a bit more interesting when we string several commands together into a longer script—a full play or at least an act to finish our Shakespeare analogy. Now imagine that we add a more rich set of language elements from .NET to the equation—now we’re ready to write an epic!

Scripts generally have a unified goal. That is, all the commands that are in a script are usually building up to one overall purpose. Examples include scripts to build a database (these might be used for a system installation), scripts for system maintenance (backups, Database Consistency Checker utilities (DBCCs)—scripts for anything where several commands are usually run together.

We will be looking into scripts during this chapter, and adding in the notion of batches—which control how SQL Server groups your commands together. In addition, we will take a look at SQLCMD—the command-line utility—and how it relates to scripts.

SQLCMD is new with SQL Server 2005. For backward compatibility only, SQL Server also supports osql.exe (the previous tool that did command-line work). You may also see references to isql.exe (do not confuse this with isqlw.exe), which served this same function in earlier releases. Isql.exe is no longer supported as of SQL Server 2005.

... less

OK, so here we are. We added a little bit of meat to the matter in terms of being able to actually code things in the last... more

OK, so here we are. We added a little bit of meat to the matter in terms of being able to actually code things in the last chapter, but this is where we start to get serious about the “programmer” aspect of things.

Let me digress for a moment though—temper your excitement about procedural work as relates to SQL. The reality is that the most important part of your work is probably already done—the design of your database. The way you build your schema is the very foundation of everything else that you do, so be careful to take the time you need in that seemingly simple stuff. Far too many database developers just throw together some tables and focus their time on the procedural logic—bad choice. As in all things in life, balance is the key.

Things have gotten more exciting from a “what you can” do perspective. In addition to the T-SQL programming constructs that we’ve always had, we now add .NET to the picture. In general, T-SQL will be the way we want to do things, but now we have the flexibility of adding .NET assemblies to not only create more complex procedures but also to create our own complex data types.

In this chapter, we’re going to review how to create a basic stored procedure (sproc) and user-defined function (UDF) from the core elements of SQL Server, and then move quickly onto more complex procedural constructs, debugging. After we finish with the basic objects of SQL Server, we will be set to add in the .NET element in Chapter 14 and get an idea of some of the new power it makes available to us.

... less

Ahhhh . . . the fundamentals. In this case, a fundamental that even lots of fairly advanced users don’t quite “get it” on... more

Ahhhh . . . the fundamentals. In this case, a fundamental that even lots of fairly advanced users don’t quite “get it” on. I’ve said it in every version of this book I’ve done, and even in my Beginning SQL Server 2005 Programming title: nothing in this chapter is wildly difficult, yet transactions and locks tend to be two of the most misunderstood areas in the database world.

This is one of those chapters that, when you go back to work, make you sound like you’ve had your Wheaties today. Nothing in what we’re going to cover in this chapter is wildly difficult, yet transactions and locks tend to be two of the most misunderstood areas in the database world. As such, this “beginning” (or at least I think it’s a basic) concept is going to make you start to look like a real pro.

In this chapter, we’re going to:

• Examine transactions

• Examine how the SQL Server log and “checkpoints” work

• Unlock your understanding of locks

We’ll learn why these topics are so closely tied to each other, and how to minimize problems with each.

... less

Hi, ho Trigger! And away!

Okay, so it’s a little cliché even for me. What’s more, it glorifies what is, although really wonderful... more

Hi, ho Trigger! And away!

Okay, so it’s a little cliché even for me. What’s more, it glorifies what is, although really wonderful, also really dastardly. I am often asked, “Should I use triggers?” The answer is, as with most things in SQL, “It depends.” There’s little that’s black and white in the wonderful world of SQL Server—triggers are definitely a very plain shade of gray.

Know what you’re doing before you go the triggers route—it’s important for the health and performance of your database. The good news is that’s what we’re here to learn.

As with most of the core subjects we’ve covered in this book (save for a few that were just too important to rush), we’re going to be moving along quickly in the assumption that you already know the basics. In this chapter, we’ll try to look at triggers in all of their colors—from black all the way to white and a whole lot in between. The main issues we’ll be dealing with include:

• What is a trigger (the very quick and dirty version)?

• Using triggers for more flexible referential integrity

• Using triggers to create flexible data integrity rules

• Using INSTEAD OF triggers to create more flexible updateable views

• Other common uses for triggers

• Controlling the firing order of triggers

• Performance considerations

By the time we’re done, you should have an idea of just how complex the decision about when and where not to use triggers is. You’ll also have an inkling of just how powerful and flexible they can be.

Most of all, if I’ve done my job well, you won’t be a trigger extremist (which so many SQL Server people I meet are) with the distorted notion that triggers are evil and should never be used. Neither will you side with the other end of the spectrum, those who think that triggers are the solution to all the world’s problems. The right answer in this respect is that triggers can do a lot for you, but they can also cause a lot of problems. The trick is to use them when they are the right things to use, and not to use them when they aren’t.

Some common uses of triggers include:

• Enforcement of referential integrity: Although I recommend using declarative referential integrity (DRI) whenever possible, there are many things that DRI won’t do (for example, referential integrity across databases or even servers, many complex types of relationships, and so on). The use of triggers for RI is becoming very special case, but it’s still out there.

• Creating audit trails, which means writing out records that keep track of not just the most current data but also the actual change history for each record.

• Functionality similar to a CHECK constraint, but which works across tables, databases, or even servers.

• Substituting your own statements in the place of a user’s action statement (usually used to enable inserts in complex views).

In addition, you have the new but likely much more rare case (as I said, they are new, so only time will tell for sure) DDL trigger—which is about monitoring changes in the structure of your table.

And these are just a few. So, with no further ado, let’s look at exactly what a trigger is.

... less

In terms of where to locate a subject in my books, this subject has to be the hardest one to place I’ve ever had to deal... more

In terms of where to locate a subject in my books, this subject has to be the hardest one to place I’ve ever had to deal with. At issue is how fundamental the addition of .NET assemblies is to SQL Server programming in the SQL Server 2005 era versus how much it is shared between the various programming topics and how much of it happens in its own world. Oh well, obviously I made a choice, and that choice was to hold the introduction of major .NET elements until we had all the major SQL Server–specific programming elements covered. So, that done, here we go.

.NET and all things related to it were just on the horizon when SQL Server 2000 came out. It’s been a long wait since early 2000 (yes, even before SQL Server 2000 was out) when I first heard that T-SQL was finally going to accept code from non T-SQL languages. The story just got better and better as we heard that complex user-defined data types would be supported, and T-SQL itself would become a .NET language with associated error handling. And so it is here, and the days of the old claustrophobic TSQL are gone, and we have a wide world of possibilities available to us.

In this chapter, we’re going to take a look at some of the major elements that .NET has brought to SQL Server 2005. We’ll see such things utilizing .NET as:

• Creating basic assemblies—including non T-SQL based stored procedures, functions, and triggers

• Defining aggregate functions (something T-SQL user defined functions can’t do)

• Complex data types

• External calls (and with it, some security considerations)

.NET is something of a wide-ranging topic that will delve into many different areas we’ve already touched on in this book and take them even farther, so, with that said, let’s get going!

Note that several of the examples in this chapter utilize the existing Microsoft Sample set. You must install the sample scripts during SQL Server installation or download the SQL Server .NET development SDK to access these samples. In addition, there is a significant reliance on Visual Studio .NET (2005 is used in the examples).

... less

Throughout this book thus far, we’ve been dealing with data in sets. This tends to go against the way that the more procedure-driven... more

Throughout this book thus far, we’ve been dealing with data in sets. This tends to go against the way that the more procedure-driven languages go about things. Indeed, when the data gets to the client end, they almost always have to take our set and then deal with it row by row. What they are dealing with is a cursor. Indeed, even in traditional SQL Server tools, we can wind up in something of a cursor mode if we utilize a non-SQL-oriented language in our scripts using the new CLR-based language support.

In this chapter, we will be looking at:

• What a cursor is

• The lifespan of a cursor

• Cursor types (sensitivity and scrollability)

• Uses for cursors

We'll discover that there’s a lot to think about when creating cursors.

Perhaps the biggest thing to think about when creating cursors is, “Is there a way I can get out of doing this?” If you ask yourself that question every time you’re about to create a cursor, then you will be on the road to a better performing system. That being said, we shall see that there are times when nothing else will do.

... less

Extensible Markup Language (XML)—looking back at its history is something of a funny thing to me. Part of its strength lies... more

Extensible Markup Language (XML)—looking back at its history is something of a funny thing to me. Part of its strength lies in its simplicity, so it would seem like it wouldn’t change much. Indeed, the basic rules of it haven’t changed at all—but all the things surrounding XML (such as how to access data stored in XML) have gone through many changes. Likewise, the way that SQL Server supports XML has seen some fairly big changes from the time it was first introduced to the relatively massive support seen in SQL Server 2005.

So, to continue my “it’s a funny thing” observation, I realize that as recently as when I did the Beginning edition for SQL Server 2005 I referred to XML support as being an “extra”—what a truly silly thing for me to say. Yeah, yeah, yeah—I’ve tempered that “extra” comment with the notion that it’s only because XML support isn’t really required to have a working SQL Server, but I’ve come to realize in today’s world that it isn’t much of a working SQL Server without support for XML. Indeed, as we continue through the rest of what I have generally referred to as “extras” in the past (Reporting Services, Integration Services, Connectivity and more), you’ll see that even SQL Server stores many of the definition objects for these extra services using XML!

So, with all that said, in this chapter we’ll look at:

• The XML data type

• XML schema collections

• Methods of representing your relational data as XML

• Methods of querying data that we have stored natively in XML (XQuery, Microsoft’s “XDL” language, and other methods)

• XML indexes

• HTTP endpoints and SOAP support

Some of these are actually embedded within each other, so let’s get to taking a look so we can see how they mix.

This chapter assumes that you have an existing knowledge of at least basic XML rules and constructs. If you do not have that foundation knowledge, I strongly recommend picking up a copy of a Wrox book like Beginning XML, 3rd Edition, by David Hunter, et al. (Wiley 2005) or another XML-specific book before getting too far into this chapter.

... less

After all the queries have been written, after all the stored procedures have been run, there remains a rather important... more

After all the queries have been written, after all the stored procedures have been run, there remains a rather important thing we need to do in order to make our data useful—make it available to end users.

Reporting is one of those things that seem incredibly simple but turn out to be rather tricky. You see, you can’t simply start sticking numbers in front of people’s faces—the numbers must make sense and, if at all possible, capture the attention of the person you’re reporting for. In order to produce reports that actually get used and, therefore, are useful, there are a couple of things to keep in mind:

• Use just the right amount of data—Do not try to do too much in one report; nor should you do too little. A report that is a jumble of numbers is going to quickly lose a reader’s attention, and you’ll find that it doesn’t get utilized after the first few times it is generated. Likewise, a barren report will get just a glance and get tossed without any real thought. Find a balance, mixing the right amount of data with the right data.

• Make it appealing—Sad as it is to say, another important element in reporting is what one of my daughters would call making it “prettiful”—which is to say, making it look nice and pleasing to the eye. An ugly report is a dead report.

In this chapter, we’re going to be taking a look at the Reporting Services tools that are new with SQL Server 2005. As with all the “add-on” features of SQL Server that we cover in this book, you’ll find the coverage here to be largely something of “a taste of what’s possible”—there is simply too much to cover to get it all in one chapter of a much larger book. If you find that this “taste” whets your appetite, consider reading a book dedicated specifically to Reporting Services.

... less

If your system is going to be operating in something of a bubble, then you can probably skip this chapter and move on. Unfortunately... more

If your system is going to be operating in something of a bubble, then you can probably skip this chapter and move on. Unfortunately, the real world doesn’t work that way, so you probably ought to hang around for a while.

There will be times when you need to move around large blocks of data. You need to bring in data that’s in the wrong format or that’s sitting in another application’s data files. Sometimes, reality just gets in the way. The good thing is SQL Server has two tools to help you move data fast—the Bulk Copy Program (BCP) and SQL Server Integration Services (SSIS). In this chapter, we’ll be looking primarily at the first of these. In addition, we’ll take a look at BCP’s close cousins—the BULK INSERT command and OPENROWSET (BULK). We will examine SSIS in the next chapter.

BCP is something of an old friend. You know the one—where you hardly ever see them anymore, but, when you do, you reminisce on all the crazy things you used to do together. It was, for a very long time, the way we moved around large blocks of data—and it did so (still does as far as that goes) amazingly fast. What, however, it lacks is sex appeal—well, frankly, since version 7.0, it has lacked appeal in a whole lot of areas.

So, why then am I even spending a chapter on it? Well, because BCP still definitely has its uses. Among its advantages are:

• It’s very compact.

• It can move a lot of data very quickly.

• It is legacy—that is, there may be code already running that is making effective use of it, so why change it?

• It uses a cryptic, yet very traditional scripting style (which will probably appeal to some).

• It is very consistent.

BCP is used for transferring text and SQL Server native format data to and from SQL Server tables. It has changed very little in the last several versions, and other bulk features have continued to erode the usefulness of BCP, but it still holds its own. You can think of BCP as a data pump, with little functionality other than moving data from one place to the other as efficiently as possible. The various other bulk operations we’ll look at in this chapter are often easier to use, but usually come at the price of less flexibility.

In this chapter, we will look at some of the ins and outs of BCP and then use what we learn about BCP to form the foundations of many of the other features that serve a similar purpose—get data in and out of your system as quickly as possible.

... less

Out with the old, in with the new—that’s going to be what this chapter is about. For context, we need to touch base on what... more

Out with the old, in with the new—that’s going to be what this chapter is about. For context, we need to touch base on what the old was—Data Transformation Services, or DTS. That’s what we had before we had Integration Services—the topic of this chapter.

I mention DTS for two reasons. First, it was revolutionary. Never before was a significant tool for moving and transforming large blocks of data included in one of the major Relational Database Management Systems (RDBMSs). All sorts of things that were either very difficult or required very expensive third-party tools were suddenly a relative piece of cake. Second, I mention it because it’s gone—or, more accurately, replaced.

DTS was completely rewritten for this release and, as part of that, also got a new name: Integration Services.

If you’re running a mixed environment with SQL Server 2000 or migrating from that version, do not fear. SQL Server 2005 Integration Services (SSIS) will run old DTS packages with the installation of Legacy Services in the Installation Wizard when you install SQL Server 2005.

Use the SSIS Package Migration Wizard to help upgrade old DTS packages.

In this chapter, we’ll be looking at how to perform basic import and export of data, and we’ll briefly discuss some of the other things possible with tools like Integration Services.

... less

Replication is one of those areas of big change for SQL Server 2005. If you’re new to it, well . . . you had some adjusting... more

Replication is one of those areas of big change for SQL Server 2005. If you’re new to it, well . . . you had some adjusting to do to understand replication anyway, but I want to caution those of you who already know replication from SQL Server 2000 and prior that some fairly core things—particularly in the area of replication security—have changed, so listen up and pay attention!

Okay, okay—enough with the drill sergeant act. I’m going to guess that most of you reading this chapter are actually fairly new to replication. You see, replication is one of those things that everyone loves to ignore—until they need it. Then, it seems, there is a sudden crisis about learning and implementing it instantly (and not necessarily in that order I’m sorry to say).

So, what then, exactly, is replication? I’ll shy entirely away from the Webster’s definition of it and go to my own definition:

Replication is the process of taking one or more databases and systematically providing a rule based copy mechanism for that data to and potentially also from a different database.

Replication is often a topology and administration question. As such, many developers have a habit of ignoring it—bad idea. Replication has importance to software architects in a rather big way, as it can be a solution to many complex load and data distribution issues such as:

• Making data available to clients that are generally not connected to your main network

• Distributing the load associated with heavy reporting demands

• Addressing latency issues with geographically dispersed database needs

• Supporting geographic redundancy

And those are just a few of the biggies.

So, with that in mind, we’re going to take a long look at replication. I’m going to warn you in advance that this isn’t going to have quite as many walkthroughs as I usually do, but patience, my young padawan—there is a reason. In simple terms, once you’ve built one or two of the styles of replication, you have most of the “constructing” part of the learning out of the way. What’s more, the actual building up of the replication instance is indeed mostly an administrator’s role. Instead, we’re going to focus on understanding what’s happened, and, from there, save most of the space in this chapter for understanding how different replication methods both create and solve problems for us and how we might use the different replication models to solve different problems.

In this chapter we will look at things like:

• General replication concepts

• What replication models are available (we will see an example or two here)

• Security considerations (an area of big change for SQL Server 2005)

• Replication Management Objects (RMO)—the programmatic way of managing replication

In the end, while I can’t promise to make you a replication expert (to be honest, I’m not really one myself), you will hopefully have a solid understanding of the fundamentals and have a reasonable understanding of the possibilities.

... less

Using plain old T-SQL (without full-text functionality), our options for querying text information are somewhat limited.... more

Using plain old T-SQL (without full-text functionality), our options for querying text information are somewhat limited. Indeed, we have only a couple of options:

• Use a LIKE clause. This is generally woefully inefficient, and is not able to utilize any kind of index structure unless your search pattern starts with an explicit value. If the search starts with a wildcard (say “%” or “_”), then SQL Server wouldn’t know which spot in the index to begin with—any indexes become worthless.

• Use some other form of pattern matching, such as PATINDEX or CHARINDEX. This is generally even more inefficient, but this can allow us to do things that LIKE will not.

With Full-Text Search, however, we gain the ability to index the contents of the text—essentially keeping a word list that lets us know what words we can find and in what rows. In addition, we are not limited to just pattern-matching algorithms—we can search for the inflected forms of words. For example, we might use the word university but have SQL Server still find the word universities, or, even better, SQL Server can find a word like drunk when the word we asked for was drink. It’s up to us to decide how precise we want to be, but even if the word we are searching for is located deep in the middle of a large text block, SQL Server can quickly find the rows that contain the word in question.

Full-Text Search, or FTS, supports any document type that is supported by Microsoft Index Server—this means that you can store things like Word, Excel, Acrobat, and other supported files in an image data type, but still perform full-text searches against that data! Indeed, the format for building Index Server plug-ins to support new document types is a published API, so you could even write your own extensions to support other document types if necessary.

Personally, I find this later point to be extremely cool. As we saw back in Chapter 16, we’re now living in an XML world. We can use OPENXML to query XML on the fly, but there isn’t currently any way to index the contents. Since Index Server file extensions are now supported, you could, for example, build an extension that knows the format of your XML document, store the document in an image data type using the extension, and, whammo, you can now use Full-Text Search to be able to quickly search a large store of XML documents for those that contain certain data. It’s not an ideal query model by any means—searching for the existence of a text string in an XML document rather than querying individual columns as we can in a relational data store—still, it has some slick possibilities to it.

In this chapter, we’ll take a look at all of these Full-Text Search features. Full-Text is something of a different animal from the kinds of things that we’ve seen in SQL Server thus far—living partly in SQL Server itself and partly as an autonomous unit that leverages non-SQL Server technologies. We’ll examine some of the issues that are unique to full-text indexing and search, and explore the special syntax that is used in Full-Text queries.

Among the sections we’ll look at are:

• Full-Text Search architecture

• Setting Up Full-Text indexes and catalogs

• Full-Text query syntax

• Full-Text quirks

• Noise words

In addition, we’ll see how there are now two ways of completing most Full-Text-related operations. By the time we’re done, you should be prepared for the hassles that FTS creates for you, but you should also be ready to utilize what can be some wonderful functionality in return.

... less

There are probably as many ideas on security as there are programmers. It’s one of those things where there isn’t necessarily... more

There are probably as many ideas on security as there are programmers. It’s one of those things where there isn’t necessarily a right way to do it, but there are definitely plenty of wrong ones.

The first thing to understand about security is that there is no such thing as a totally secure application. If you can make it secure, rest assured that someone, somewhere, can defeat your efforts and “hack” into the system. Even with this knowledge, the goal still needs to be to keep unwanted intruders out of your system. The good news about security is that, for most instances, you can fairly easily make it such a hassle that 99.999 percent of people out there won’t want to bother with it. For the other .001 percent, I can only encourage you to make sure that all your employees have a life so they fall into the 99.999 percent. The .001 percent will hopefully find someplace else to go.

With SQL Server 2005, Microsoft has gotten very serious about security for SQL Server. There are a ton of new features here, and, while there were already books that were specific to SQL Server security out there before, I can imagine them as being huge tomes now—the subject has grown that much with this release.

In this chapter, we’re going to cover:

• Security basics

• SQL Server security options

• Database and server roles

• Application roles

• Credentials

• Certificates

• Schema management

• XML integration security issues

• More advanced security

What we’ll discover is that there are a lot of different ways to approach the security problem. Security goes way beyond giving someone a user ID and a password—we’ll see many of the things that you need to think about.

Before beginning any of the examples in this chapter, you’ll need to load and execute the script called NorthwindSecure.sql. This builds a special database we’ll use throughout this chapter. You can download what you need for this at the book’s Web site at www.wrox.com.

Okay, so this is a chapter where I have to make you create a working database in order for the examples to work—my apologies for that. What we’re going to utilize is the old Northwind database but with any changes to permissions removed. The NorthwindSecure database that we’ll use throughout this chapter is a more typical database scenario—that is, it has absolutely no permissions added to it beyond what comes naturally with creating tables and objects (which means NONE). We’ll learn how to deal with this and explicitly add what permissions we want as the chapter progresses.

... less

This is probably the toughest chapter in the book from my perspective as the author, but not for the normal reasons. Usually... more

This is probably the toughest chapter in the book from my perspective as the author, but not for the normal reasons. Usually, the issue is how to relate complex information in a manner that’s easy to understand. As we’re getting near the end of the book, I hope that I’ve succeeded there—even if there is still more to come. At this point, you have a solid foundation in everything we’re going to discuss in this chapter. That means I’m relatively free to get to the nitty-gritty and not worry quite as much about confusion.

Why then would this be a tough chapter for me to write? Well, because deciding exactly what to put into this chapter is difficult. You see, this isn’t a book on performance tuning—that can easily be a book unto itself. It is, however, a book about making you successful in your experience developing with SQL Server. Having a well-performing system is critical to that success. The problem lies in a line from Bob Seger: “What to leave in, what to leave out.” What can we focus on here that’s going to get you the most bang for your buck?

Perhaps the most important thing to understand about performance tuning is that you are never going to know everything there is to know about it. If you’re the average SQL developer, you’re going to be lucky if you know 20 percent of what there is to know. Fortunately, performance tuning is one of those areas where the old 80-20 rule (80 percent of the benefit comes from the right 20 percent of the work) definitely applies.

With that in mind, we’re going to be roaming around quite a bit in this chapter topically speaking. Everything we talk about is going to be performance related in some fashion, but we’ll touch on a wide range of ways to squeeze the most out of the system performance-wise. The topics we’ll go into include both new and old subjects. In many cases, it will be a subject we’ve already covered, but with a particular eye on performance. Some of the places we’ll focus on include:

• Index choices

• Client vs. server-side processing

• Strategic de-normalization

• Routine maintenance

• Organizing your sprocs

• Uses for temporary tables

• Small gains in repetitive processes vs. big gains in long-running processes

• Hardware configuration issues

• Troubleshooting

Even though we’re going to touch on these, there is a more important concept to be sure that you get—this is only the beginning. The biggest thing in performance is really just to stop and think about it. There is, for some strange reason, a tendency when working with SQL to just use the first thing that comes to mind that will work. You need to give the same kind of thought to your queries, sprocs, database designs—whatever—that you would give to any other development work that you’re doing. Also, keep in mind that your T-SQL code is only one part of the picture—hardware, client code, SQL Server configuration, and network issues are examples of things that are “outside the code” that can have a dramatic impact on your system.

Performance means a lot of different things to a lot of different people. For example, many will think in terms of simple response time (how fast does my query finish). There is also the notion of perceived performance (many users will think in terms of how fast they receive enough to start working on, rather than how fast it actually finishes). Yet another perspective might focus on scalability (for example, how much load can I put on the system before my response time suffers or until users start colliding with each other?).

Many of the examples and suggestions in this chapter are about raw speed—how fast do I return results—we do, however, touch on perceived performance and scalability issues where appropriate. Make sure that all facets of performance are considered in your designs—not just time to completion.

... less

So, at this point we’ve covered all of the core database topics and then some. We still have a chapter or two to clean up... more

So, at this point we’ve covered all of the core database topics and then some. We still have a chapter or two to clean up the edges around our development effort, but we’ve mostly covered everything—heh, NOT!!! For the developer, we like to think our job is done, but for the application we’re building, it’s just beginning. And so, it’s time to talk a bit about maintenance and administration of the databases you develop.

As a developer, I can just hear it now: “Isn’t that the database administrator’s job?” If you did indeed say something like that, then step back, and smack yourself—hard (and no, I’m not kidding). If there is anything I hope to instill in you in your database development efforts, it’s to avoid the “hey, I just build ‘em—now it’s your problem” attitude that is all too common out there.

A database-driven application is a wildly different animal than most standalone apps. Most standalone applications are either self-maintaining or deal with single files that are relatively easy for a user to copy somewhere for backup purposes. Likewise, they usually have no “maintenance” issues the way that a database does.

In this chapter, we’re going to take a look at some of the tasks that are necessary to make sure that you end users can not only recover from problems and disasters but also perform some basic maintenance that will help things keep running smoothly.

Among the things we’ll touch on are:

• Scheduling jobs

• Backing up and recovering

• Basic defragmenting and index rebuilding

• Setting alerts

• Archiving

While these are far from the only administration tasks available, these do represent something of “the minimum” you should expect to address in the deployment plans for your app.

... less

It’s been a long road, and we’re getting closer and closer to the end of our walk through SQL Server. It is, of course, no... more

It’s been a long road, and we’re getting closer and closer to the end of our walk through SQL Server. It is, of course, no coincidence that our chat about how to manage your SQL Server programmatically has been held until very close to the end. Among other things, we needed to have a solid idea as to what objects we were managing and what administrative needs we had before we were ready to understand the SMO object model and talk about some of the reasons we might want to use SMO.

So, what exactly is SMO? Well, as the title of this chapter implies, SMO is an object model for managing SQL Server. Whereas connectivity models like ADO are all about accessing data, SMO is all about access the structure and health of your system.

In this chapter, we’ll look at:

• The convoluted history of SQL Server management object models

• The basics of the SQL SMO object model

• A simple SMO example project

As with many of the SQL Server topics we cover in this book, SQL SMO can and will be a book unto itself (indeed, there are several such books already on the market), so please do not expect to come out of this chapter an expert. That said, hopefully, you will have the fundamentals down to at least the point to where you know what’s possible and how much work is likely to be involved. From there, you can look for sources of more information as necessary.

... less

SQL Server includes a number of “System Functions” as well as more typical functions with the product. Some of these are... more

SQL Server includes a number of “System Functions” as well as more typical functions with the product. Some of these are used often and are fairly clear right from the beginning in terms of how to use them. Others, though, are both rarer in use and more cryptic in nature.

In this appendix, we’ll try to clarify the use of most of these functions in a short, concise manner.

Just as an FYI, in prior releases, many system functions were often referred to as “Global Variables.” This was a misnomer, and Microsoft has striven to fix it over the last few releases—changing the documentation to refer to them by the more proper “system function” name. Just keep the old terminology in mind in case any old fogies (such as myself) find themselves referring to them as Globals.

The T-SQL functions available in SQL Server 2005 fall into 11 categories:

• Legacy “system” functions

• Aggregate functions

• Cursor functions

• Date and time functions

• Mathematical functions

• Metadata functions

• Rowset functions

• Security functions

• String functions

• System functions

• Text and image functions

... less

Having a SQL Server but not allowing programs to connect to it is the same as not having a SQL Server at all. Sure, we may... more

Having a SQL Server but not allowing programs to connect to it is the same as not having a SQL Server at all. Sure, we may log into Management Studio and write queries directly, but the reality is that the vast majority of our users out there never actually see the database directly—they are just using input and reporting screens in some system we’ve written.

With this in mind, it probably makes sense to figure out how your application is actually going to talk to the database. There are tons of books out there that cover this topic directly (and, outside of a basic connection, it really is a huge topic unto itself), so we’re going to stick a few basic methods of connecting and also some information on do’s and don’ts of connectivity.

I can’t stress enough how these examples are truly the basics. You can make many, many choices and optimizations for connectivity. I’ll touch on a few key things about it here and there, but this is mostly just code. I highly recommend taking a look at a connectivity-specific book.

... less

SQL Server is a wildly complex product. Putting it that way is somewhat at odds with the notion that it is one of the easiest... more

SQL Server is a wildly complex product. Putting it that way is somewhat at odds with the notion that it is one of the easiest Relational Database Management Systems (RDBMSs) to use. But, while using the basic functionality comes much easier than it does with most database management systems, SQL Server has all sorts of “extras,” and some of these extras are almost full products into themselves. So, what this appendix is about is outlining each of the additional services SQL Server provides, but that, for whatever reason, is not covered in its own chapter in the book.

In this appendix, we’ll take a look at:

• Analysis Services—This started as a robust Online Analytical Processing (OLAP) engine, but has grown into much more. Data mining—also often sold by other companies as a standalone product—is also provided within Analysis Services.

• Notification Services—This is an event-monitoring and notification service. You can have “subscribers” receive information automatically when events they have subscribed to occur. The service monitors events you have defined and checks them against subscriptions. If there is a match, it can notify the subscriber using one of a few different delivery methods (such as e-mail or text message).

• Service Broker—You can think of this as something of a replacement for the Microsoft Message Queue (MSMQ). Since the Service Broker is built into the core SQL Server, you do not have the issues that used to exist mixing two different server products. The Service Broker facilitates asynchronous communication between different processes or even different servers.

What we’ll find is that SQL Server is much more than just SQL Server. Oh sure, we’ve seen that somewhat in our looks at Reporting Services, Integration Services, and the Full-Text Search engine, but we’ll see that what SQL Server has to offer just keeps going and going.

... less