This book explores the programmatic elements of Microsoft SQL Server 2005 with a focus on T-SQL, but it also covers other elements that have T-SQL interfaces, such as XML, .NET integration, and Service Broker. I’m assuming that you’ve already read my companion book to this one, Inside Microsoft SQL Server 2005: T-SQL Querying (Microsoft Press, 2006) or have equivalent querying and query-tuning background. For the sake of brevity, I will refer throughout this book to the T-SQL Querying volume as Inside T-SQL Querying.

More Info

Note that you can download the source code for both Inside T-SQL books, send comments or corrections, and access resources related to the books at the Web site http://www.insidetsql.com.

Datatypes play a key role in the database. Your choices of datatypes will affect both the functionality and performance of applications interacting with your database. Schema changes related to datatypes–for example, changing a column that allows NULLs to not allow NULLs, changing an INT column to a BIGINT one, and so on–can take some time to complete and might result in the tables involved being unavailable for a while. Furthermore, some changes might cause data loss–such as shortening the length of a dynamic column, changing a NUMERIC(12, 2) column to INT, and so on. Therefore, if you’re the designer involved in making datatype choices, a DBA implementing them, or a programmer interacting with the objects, studying datatypes in depth, down to the level of their internals, is time well spent. But most importantly, use extra caution when choosing datatypes because changes in a production environment can be very problematic.

Tip

You can use a simple test to check whether a certain schema change requires physical access to the data and might therefore take a while, or whether it is merely a metadata change. Turn on the STATISTICS IO option in your session, and perform the schema change against a small test table. If no I/O is reported, you know that the change didn’t involve access to the base data and therefore will be fast. For example, enhancing a VARCHAR column to a larger size will involve no I/O against the base data and will be instantaneous. On the other hand, shortening a VARCHAR column will require access to the base data and might take a while to complete against a large table.

Also, in production environments with large tables, refrain from making schema changes using graphical tools such as Enterprise Manager or SQL Server Management Studio (SSMS). These tools, in many cases, use unnecessary activities to achieve the change—such as creating a new table, copying the data, dropping the original table, and renaming the new table to the original table name. As a good practice, perform schema changes using your own T-SQL code.

I urge you to take the time to study datatypes and their internals, which are covered in depth in Inside SQL Server 2005: The Storage Engine (Microsoft Press, 2006) by Kalen Delaney. In this chapter, I’ll cover some T-SQL programming issues related to datatypes, focusing on DATETIME, character manipulations, large objects, and new functionality in Microsoft SQL Server 2005.