sp_bindcache assigns a database, table, index or text/image object to a cache. Before you can bind an entity to a cache, the following conditions must be met:
The named cache must exist, and its status must be “Active.”
The database or database object must exist.
To bind tables, indexes, or objects, you must be using the database where they are stored.
To bind system tables, including the transaction log table syslogs, the database must be in single-user mode.
To bind a database, you must be using the master database.
To bind a database, user table, index, text object, or image object to a cache, the type of cache must be “Mixed.” Only the syslogs table can be bound to a cache of “Log Only” type.
You must own the object or be the Database Owner or the System Administrator.
Binding objects to caches is dynamic.
The syntax for binding objects to caches is:
sp_bindcache cache_name, dbname [,[owner.]tablename [, indexname | "text only" ] ]
The owner name is optional if the table is owned by “dbo.”
This command binds the titles table to the pubs_cache:
sp_bindcache pubs_cache, pubs2, titles
To bind an index on titles, add the index name as the third parameter:
sp_bindcache pubs_cache, pubs2, titles, titleind
The owner name is not needed in the examples above because the objects in the pubs2 database are owned by “dbo.” To specify a table owned by any other user, add the owner name. You must enclose the parameter in quotation marks, since the period in the parameter is a special character:
sp_bindcache pubs_cache, pubs2, "fred.sales_east"
This command binds the transaction log, syslogs, to the pubs_log cache:
sp_bindcache pubs_log, pubs2, syslogs
The database must be in single-user mode before you can bind any system tables, including the transaction log, syslogs, to a cache. Use sp_dboption from master, and a use database command, and run checkpoint:
sp_dboption pubs2, single, true
use pubs2
checkpoint
text and image columns for a table are stored in a separate data structure in the database. To bind this object to a cache, add the “text-only” parameter:
sp_bindcache pubs_cache, pubs2, au_pix, "text only"
This command, executed from master, binds the tempdb database to a cache:
sp_bindcache tempdb_cache, tempdb
You can rebind objects without dropping existing bindings.
