primary key with additional data

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 t开发者_如何学运维he 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?

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

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.

FYI: Note that you cannot add included columns on a clustered index, since a clustered index isn't really an index in the first place - it's just the b-tree of the data. In some cases, you may be better off with a heap and several efficient covering indexes (which may have included columns).

So if your primary key is also the clustered index, no included columns...

Only a non-clustered index can be made covering by INCLUDEs. Here, the included columns are in the lowest level of the index. This avoids what is known as a key (bookmark in SQL Server 2000) lookup into the clustered index

A clustered index is covering automatically: the lowest leaf level of the index is the data. By default, a PK is clustered in SQL Server.

This applies to the same table.

To do this across tables you need an indexed view (and see Joe's answer)