Catálogo de publicaciones - libros

By this point, you should understand the small differences between each version of SQL Server. You should also know how to check your computer to see if it is suitable for a SQL Server installation.

By following the steps given earlier, you should have a successful installation of SQL Server on your computer. You may even have completed the installation twice so that you have a development server installation as well as a test server installation. This is a good idea, and something to consider if you have only one installation so far. Whether you are working in a large corporation or are a “one-man band,” keeping your production and development code separate leads to greatly reduced complications if, when developing, you need to make a production fix.

This chapter introduced you to security in SQL Server so that you can feel comfortable knowing which way you want to implement this and how to deal with different usernames. You may not have any data yet, but you want to ensure that when you do, only the right people get to look at it!

You are now ready to explore SQL Server 2005. One of the best ways of managing SQL Server is by using the SQL Server Workbench, which will be discussed next.

SQL Server Management Studio (SSMS) is a tool for working with SQL Server that you will see in action throughout this book, whether we work with the graphical interface or use Query Editor for writing T-SQL code. As you learned in this chapter, the main areas of the tool are the Registered Servers Explorer, the Object Explorer, and the main documents window that will contain graphical representations of objects in the database.

In this chapter we looked at designing and building our example database. The steps covered are very important on the development front. The database itself requires careful thought regarding some of the initial settings, but as time moves on and you have a better idea about the volume of data and how people will use the data, you may find you need to alter some of these options. As you move to user acceptance testing, keep an eye on the statistic options mentioned here.

In the next chapter, we’ll start adding some meat to the bones of our example database by creating tables to hold data.

By this point, you should understand the small differences between each version of SQL Server. You should also know how to check your computer to see if it is suitable for a SQL Server installation.

By following the steps given earlier, you should have a successful installation of SQL Server on your computer. You may even have completed the installation twice so that you have a development server installation as well as a test server installation. This is a good idea, and something to consider if you have only one installation so far. Whether you are working in a large corporation or are a “one-man band,” keeping your production and development code separate leads to greatly reduced complications if, when developing, you need to make a production fix.

This chapter introduced you to security in SQL Server so that you can feel comfortable knowing which way you want to implement this and how to deal with different usernames. You may not have any data yet, but you want to ensure that when you do, only the right people get to look at it!

You are now ready to explore SQL Server 2005. One of the best ways of managing SQL Server is by using the SQL Server Workbench, which will be discussed next.

So, now you know how to create a table. This chapter has covered several options for doing so, but there is one point that you should keep in mind when building a table, whether you are creating or modifying it. When creating a table in SQL Server Management Studio, you should always save the table first by clicking the Save toolbar button. If you have made a mistake when defining the table and you close the table, and in doing so save in one action, you will get an error message informing you that an error has occurred, and all your changes will be lost. You will then have to go back in to the Table Designer and reapply any changes made.

Try also to get used to using both SQL Server Management Studio and the Query pane, as you may find that the Query pane gives you a more comfortable feel to the way you want to work. Also, you will find that in the Query pane, you can save your work to a file on your hard drive as you go along. You can also do this within SQL Server Management Studio; however, the changes are saved to a text file as a set of SQL commands, which then need to be run through the Query pane anyway.

We’ve covered yet another major building block in creating a SQL Server solution. The last few chapters have shown you how to store data, and in this chapter you’ve learned about indexes and how to use them to quickly and efficiently retrieve the data stored in the table.

There are many types of indexes, and choosing the right one at the right time to complete the right job is quite an art. This chapter has taken you through the steps to decide which columns will make an efficient index, and then build those columns in the right type of index to make the most of the information.

This chapter also covered database diagramming. Database diagrams should initially be thought of as a form of documentation. Keep in mind, though, that the database diagram tool may expand in future versions of SQL Server to become much more sophisticated and powerful than it is now—although even now it is quite a powerful utility.

Don’t be caught out by the fact that changes in the diagram are not applied until the diagram is saved, and that your changes could overwrite another’s changes. If you’re using the database diagram tool for development in any sort of multiuser environment, take the greatest of care when completing updates (in fact, try to avoid them altogether). Unless you split your database solution into multiple diagrams, with any table being found in at most one diagram, don’t use the database designer as a development tool.

You have seen a great deal in this chapter that is crucial to ensuring that your database is always secure if there are any unforeseen problems. As a manager drummed into me, the unexpected will always happen, but you must always be able to recover from it, no matter what.

Therefore, regular backups that are known to work and even the occasional “disaster recovery test” should be done to ensure that you can restore when something unexpected happens. No matter what your managing director says, it is the data of a company that is its most important asset, not the people. Without the data, a company cannot function. If you cannot ensure that the data will be there, then the company is in a very dangerous position.

So, now you know how to create a table. This chapter has covered several options for doing so, but there is one point that you should keep in mind when building a table, whether you are creating or modifying it. When creating a table in SQL Server Management Studio, you should always save the table first by clicking the Save toolbar button. If you have made a mistake when defining the table and you close the table, and in doing so save in one action, you will get an error message informing you that an error has occurred, and all your changes will be lost. You will then have to go back in to the Table Designer and reapply any changes made.

Try also to get used to using both SQL Server Management Studio and the Query pane, as you may find that the Query pane gives you a more comfortable feel to the way you want to work. Also, you will find that in the Query pane, you can save your work to a file on your hard drive as you go along. You can also do this within SQL Server Management Studio; however, the changes are saved to a text file as a set of SQL commands, which then need to be run through the Query pane anyway.

We’ve covered yet another major building block in creating a SQL Server solution. The last few chapters have shown you how to store data, and in this chapter you’ve learned about indexes and how to use them to quickly and efficiently retrieve the data stored in the table.

There are many types of indexes, and choosing the right one at the right time to complete the right job is quite an art. This chapter has taken you through the steps to decide which columns will make an efficient index, and then build those columns in the right type of index to make the most of the information.

This chapter also covered database diagramming. Database diagrams should initially be thought of as a form of documentation. Keep in mind, though, that the database diagram tool may expand in future versions of SQL Server to become much more sophisticated and powerful than it is now—although even now it is quite a powerful utility.

Don’t be caught out by the fact that changes in the diagram are not applied until the diagram is saved, and that your changes could overwrite another’s changes. If you’re using the database diagram tool for development in any sort of multiuser environment, take the greatest of care when completing updates (in fact, try to avoid them altogether). Unless you split your database solution into multiple diagrams, with any table being found in at most one diagram, don’t use the database designer as a development tool.

In this chapter, you have met stored procedures, which are collections of T-SQL statements compiled and ready to be executed by SQL Server. You have learned the advantages of a stored procedure over an ad hoc query, encountered the basic CREATE PROCEDURE syntax, and created some simple stored procedures.

The basics of building a stored procedure are very simple and straightforward. Therefore, building a stored procedure within Query Editor may be as attractive as using a template. As stored procedures are sets of T-SQL statements combined together, you will tend to find that you build up your query, and then at the end surround it with a CREATE PROCEDURE statement.

Probably the largest area of code creation outside of data manipulation and searching will be through control-of-flow statements. We look at other areas, such as error handling, in Chapter 11, which aims to advance your T-SQL knowledge.