SQL SERVER 2005 INDEX - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL SERVER 2005 INDEX

Author

Topic

ti60
Starting Member

2 Posts

Posted - 2009-03-01 : 17:23:53

We have index :

CREATE NONCLUSTERED INDEX [IX_Customer_TerritoryID] ON [Sales].[Customer]
(
[TerritoryID] ASC
)
WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]

From were or how I can get value of this variable's :
STATISTICS_NORECOMPUTE,
DROP_EXISTING,
ONLINE,
SORT_IN_TEMPDB,
DROP_EXISTING

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-03-01 : 18:21:05

If you are looking for a description of what they mean, they are described in books online here: http://technet.microsoft.com/en-us/library/ms188677(SQL.90).aspx

If you want to find out what the settings are for one of your indexes, you can right click on the index in the SQL Server Management Studio object browser and select properties (in the object browser expand the YourDatabaseName->Tables->YourTableName->Indexes), or right click on it and select the script option.

ti60
Starting Member

2 Posts

Posted - 2009-03-02 : 16:31:53

I want to create a query that retrieves the values of variables :
STATISTICS_NORECOMPUTE,
DROP_EXISTING,
ONLINE,
SORT_IN_TEMPDB,
DROP_EXISTING

from system tables - metadate

Example :
With this select query :

select
ignore_dup_key,
is_padded ,
allow_row_locks,
allow_page_locks
from sys.indexes

retrieves from the system tables 'sys.indexes' the values of variables :
PAD_INDEX = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON

,but in the system table 'sys.indexes' not have a column with value for :

STATISTICS_NORECOMPUTE,
DROP_EXISTING,
ONLINE,
SORT_IN_TEMPDB,
DROP_EXISTING

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources