I was preparing for the SQL Server Exam 70-441 (Designing Database Solutions by Using Microsoft® SQL Server™ 2005) and I kept coming across a whole bunch of small features that you don’t hear a lot about. I’m not talking about the big ones everyone already knows about like CLR Integration, DDL triggers, XML, and ranking functions. These are ones that took me by pleasant surprise. I think they’re really cool, so I wanted to point them out.
Included Columns / Covering Indexes
Say you had a critical/oft-used report that ran a statement like:
SELECT Name, ProductNumber
FROM Production.Product
WHERE Style='U' AND Class='L'
If you wanted to put an index on the table to speed things up, you’d put an index on Style, Class, Name, and ProductNumber. That would remove the need to go from the index page to the table page to get all the information for the query. SQL 2005 has added the include clause to the Create Index statement.
CREATE INDEX IX_Product_Class_Style
ON Production.Product (Class,Style)
INCLUDE (Name, ProductNumber);
What this is doing is indexing just the Class and Style fields of the table. The Name and ProductNumber are stored at the leaf nodes of the index. This saves space in the index pages, meaning you can get more rows in an index page, reducing I/O and Disk Space.
Dynamic Management Views
Seriously, these are pretty cool. Really. If you haven’t looked at these, take the time to play around with a few.
With these new views, you are able to get high visibility into the SQL Server engine. Wondering what’s about to be written to the disk? Query sys.dm_io_pending_io_requests. Troubleshooting execution plans? Use sys.dm_exec_cached_plans and sys.dm_exec_plan_attributes. Need to see what’s running on the server right now? sys.dm_os_tasks. How often is an index used? There’s sys.dm_db_index_usage_stats that will answer that question. There are over 80 views that provide very useful information when you are troubleshooting problems or just curious as to how SQL Server organizes itself.
sp_create_plan_guide
Every now and then, I have a query that runs like a jackrabbit on my development machine, but runs like 3 legged turtle on a production machine. This is usually because it decided not to use an index on the production machine due to differences in data density or because running the query with parallelism is slower with some statements. To troubleshoot these situations, I end up modifying the stored procedure to add various hints.
Rather than modify the stored procedure, you can specify a hint for an existing query plan using the sp_create_plan_guide stored proc. This way you can experiment without modifying the stored procedure and the hint doesn’t become part of your codebase. MSDN documentation can be found here.
OUTPUT Clause
A lot of people already know about this pretty cool feature, but I didn’t. In the past, if you do an insert to a table, and the table has an identity column, after the insert, you would look at @@IDENTITY or scope_identity(). That always seemed hackish to me.
You can now add an OUTPUT clause to the end of an INSERT, UPDATE, or DELETE statement. That will return the rows changed by the statement.
INSERT INTO Person.Address
(AddressLine1,AddressLine2,City
,StateProvinceID,PostalCode,rowguid,ModifiedDate)
VALUES(@AddressLine1,@AddressLine2,@City
,@StateProvinceID,@PostalCode,@rowguid,@ModifiedDate)
OUTPUT INSERTED.AddressID;
You use the pseudo tables INSERTED and DELETED, just like in triggers.
Storing BLOBs in Table page
Inevitability, on each project I work on, there is a requirement to have a field capable of storing more than 8k in a table. It’s usually a field that the user can enter unlimited comments about a customer. So I define it as a text field. I always feel guilty about it because the user’s text will rarely be more than 100 bytes and I know it’s going to be stored on a different page.
In SQL 2005, if the data will fit in the same page as the rest of the record, it will place it in the same page. This works with varchar/varbinary/nvarchar(max) and XML datatypes. The behavior is described here at MSDN.
Persistence of Computed Columns
This is kind of neat. We’ve had the ability to define computed columns in tables for a while. They worked exactly as they’re named. When the column was needed, it was computed. Now the results of the computed column can be saved to disk. This allows you to index computed columns. You can also feel free to create calculated columns that can be computationally intense.
CREATE TABLE LineItem (
LineItemID int IDENTITY(1,1),
OrderID int,
ItemDesc varchar(512),
Qty int,
Cost money,
LineTotal as Qty * Cost PERSISTED
)
Those are all the ones I found. What you have noticed as small new features in SQL that make life easier?