Catálogo de publicaciones - libros

Welcome to . This book is centered around the Express Edition of SQL Server 2005, which is a free download from Microsoft. As you are reading this book, I assume that you are interested in learning how to create solutions with Microsoft SQL Server 2005 Express (SSE), but have no prior knowledge of SSE and probably no experience with any database. The aim of this book is to bring you quickly to a level at which you are developing competently with SSE. This book is specifically designed for beginners or those who at this stage wish to use only SSE. But you’ll find this book useful for understanding the basics of any relational database, so moving from SQL Server to Oracle, DB2, MySQL, PostgreSQL, etc., will be relatively easy.

Now that SQL Server Express is successfully installed on your machine, it is time to start exploring the various areas that make this an easy and effective product to use. With SSE, all the administration interfaces now reside in , such as SQL Server Management Studio Express, which features tools for working with developing database solutions, and if you move to the full version of SQL Server 2005, you’ll see SQL Server Business Intelligence Development Studio, designed for analyzing data with Analysis Services. This chapter concentrates on the SQL Server Management Studio Express tool, also known as SSMSE. It also looks at the Surface Area Configuration (SAC) tool.

Now that you’ve installed SQL Server 2005 Express Edition (SSE) and examined the main tools you’ll use as an SSE developer or administrator, it’s almost time to start building the ApressFinancial database solution. However, you can’t do this yet because you still don’t know what the database will hold. At this point in time, all the information you have so far is that you’ll be building a database to hold some financial transactions for a personal or corporate financial tool. You’ll gear this database toward a financial tool that a corporation might use, because SSE can be scaled from one user to thousands of users very easily. The next step is to gather more information about the requirements of the solution and about what information is required to be stored in the database. Once the information gathering stage is complete, you’ll then be able to create the database in SSE.

Security is important—more so, in fact, than design, creation, and performance. If your database had no security measures in place, absolutely anyone could come along and steal or corrupt the data, causing havoc to you and your company. And not just in one database, but in every database in every server.

Now that we’ve created the database, it obviously needs to have the ability to store information. After all, without this, what is the point of a database? The first area that needs to be worked on is the table definitions.

Now that we’ve created the tables, we could stop at this point and just work with our data from here. However, this would not be a good decision. As soon as any table contained a reasonable amount of information, and we wished to find a particular record, it would take SQL Server Express a fair amount of time to locate it. Performance would suffer, and our users would soon get annoyed with the slowdown in speed.

Now that we have created a major part of the database in the previous chapters, and before moving on to inserting and manipulating the data, this is a good point to take a moment to back up the database, just so that if things go wrong, it is possible to recover back to a stable point.

We have now built our tables, set up the relationships, and backed up our solution, so we are ready to start inserting data. The many tables within the database cover a number of different types of data that can be stored, ranging from characters and numbers to images and XML. This chapter will show you how to insert data into columns defined with all of these data types.

A view is a virtual table that, in itself, doesn’t contain any data or information. All it contains is the query that the user defines when creating the view. A view is actually a named query against one or more tables. Views are used as a security measure (to restrict users to specific columns or rows), as a method of joining data from multiple tables (and presenting it as if it resides in one table), and by returning summary data instead of detailed data. Another use for a view is to provide a method of accessing the underlying data in a manner that provides the end user with a business layout. For example, you will see within this chapter a view that shows customer details along with enriched transaction details, thus making it easier for anyone interrogating your data, even someone who has no knowledge of the underlying data model, to access useful information.

It is time to look at how to place T-SQL into a that can be run as often as required.