primary key with additional data

Question

I've read somewhere that there is an option to store additional data on the leaves of the tree created by the primary key. For example, If I have a table with columns: row_id, customer_id and I need to display customer_name, I can do join between my table and customers table. But I can also store the customer_name with the primary key of customers table (with customer_id) and the sql engine wouldn't have to load the entire row of customer in order to fins customer name.

Can someone describe it better? How can I implement that?

Answer 1

For SQL Server 2005+, it sounds like you're talking about included columns, but that only works when all of the columns are in one table.

CREATE INDEX IX_Customers_RowCust
ON Customers (customer_id)
INCLUDE (customer_name);

But, I think you're describing a situation where (row_id, customer_id) are in one table, and customer_name is in a second table. For that situation, you'd want to create an indexed view.

CREATE VIEW vwCust WITH SCHEMABINDING AS 
    SELECT t.row_id, t.customer_id, c.customer_name
        FROM SomeTable t
            INNER JOIN Customers c
                ON t.customer_id = c.customer_id
GO
CREATE UNIQUE CLUSTERED INDEX vwCustRow ON vwCust (row_id)
GO

Answer 2

The MSDN article explains it very well

http://msdn.microsoft.com/en-us/library/ms190806.aspx

Basically as it fetches the data from the index (based on your where clause) instead of having to hit the table again to get the additional data the index bring back the data which has been included as part of the index.

It is important to note that the included columns do not make up the index used for search purposes but they will affect the size of the index and so will therefore take up more memory space.

Joe has got the syntax that you need to implement it.