Retrieving records within an index range, the nth record?

if I create an index for a table with some records, do you think I can retrieve records in a giving range? for example, the 5th to 10th records?

Possible? How can I do it?

When we insert data at the table, would the index in sequential order? How would the index be created for new inserted records?

I'm using SQL 2005 Express, not SQL 2000.if I create an index for a table with some records, do you think I can retrieve records in a giving range? for example, the 5th to 10th records?
Create index and schedule update statistics according to your requirement...

Use comparison operator for retrieving desired data.

i.e. Like, Between, Not Between etc.

When we insert data at the table, would the index in sequential order? How would the index be created for new inserted records?
Use clustered and non clustered index considering your needs.

Indexes are for arranging, sorting & fast retrieval of the data. Each time you don't need to create index when you insert a row, just schedule update statistics job or set auto update statistics.

Explore Books OnLine (From query analyzer -> Help Menu) for more information.|||how do we include index as a criteria when we use normal SQL query?|||how do we include index as a criteria when we use normal SQL query?
You don't need to do such thing, SQL Server will do it for you...|||after some research, i think it's easier for me to insert row_number() into the table instead of using index. What do you think?|||Read documents / books (rather get some knowledge) before making any changes...|||Data in a relational database has no inherent order.
Why do you think you need to add a rownumber column?

There are several ways to get a "page" or "range" of records from a table. Here is one:

select top 5 *
from
(select top 10 *
from [YourTable]
order by [YourColumn] asc) Subquery
order by [YourColumn] desc

You should not be relying on the concept of a "row number"|||this is for a particular case to generate rigid report using SQL 2005.

I need the output to be in the right order in my control, and because there is headers and footers involved, I got no choice but to fix them in certain special order.

the output is to an excel spreadsheet.|||So throw an ORDER BY clause into your query.

If you want the data to be ordered according to the way it was entered, then use a datetime column to record the entry date.|||Order by cannot work without row_number. I have too many identical rows.

Entry date is not accurate as the time unit used by SQL 2005 is not small enough.|||Then use an Identity column.|||unfortunately, there is no identity column, because I use this to generate a rigid report. the only identity column is the row number I created as part of the table.|||You're not listening...|||You can also use temporary table to process desired request in memory, rather to store in table permanently,
try just like this:

SELECT ROWID=IDENTITY(int,1,1) , Col1
INTO #TempTable FROM
<UrTable List and Where Clause>

and then retrieve from Temporary table, it will save ur time, disk space and locking issues on underlying table.

--Riaz

unfortunately, there is no identity column, because I use this to generate a rigid report. the only identity column is the row number I created as part of the table.|||it will save ur time, disk space and locking issues on underlying table.
Dunno about time but this will use more disk space than blindman's query (temp tables are not held in memory but written to tempdb) and will lock tempdb while it runs (this is due to the "select into" bit).

You can use the OVER clause if you are really eager to use row_number().

SELECT *
FROM--Derived TABLE - numbering rows
(SELECT *
, ROW_NUMBER() OVER (ORDERBY my_unique_column ASC) AS rn
FROM dbo.MyTable) AS der_t
WHERE rn BETWEEN 5 AND 10

Retrieving Index Size in SQL Server 2005

I have a seemingly simple task, but the solution isn't quite as
obvious/simple as one would think.
I am trying to find out the disk space used by each of the indexes in my
database. I have been able to get the size used by indexes within each
table (sp_spaceused), but this sums all of the indexes within the table. I
have also been able to get the broken down stats of each index
(dm_db_index_physical_stats), but it doesn't show how much total size the
index consumes.
I can see exactly what I'm looking for in Management Studio, through the
"Disk Usage" report on the Summary tab for my database. It is listed under
the "Disk Space Used by Partitions" section, which shows each index, its
"Total Memory Used", and its "Total Memory Reserved". But I can't manage to
find a query that gives me this information.
Thanks in advance for your help.
JeradJerad Rose,
1 - You can use "Profiler" to trace the batch / statement sent to SQL Server
by the custom report.
2 - Take a look to these catalog views in BOL.
- sys.indexes
- sys.partitions
- sys.allocation_units
3 - You can read more about this in:
Inside Microsoft (r) SQL Server (tm) 2005: The Storage Engine
http://www.amazon.com/Inside-Micros...5/dp/0735621055
Example:
use northwind
go
select
i.[object_id],
i.index_id,
p.partition_number,
p.rows as [#Records],
a.total_pages * 8 as [Reserved(kb)],
a.used_pages * 8 as [Used(kb)]
from
sys.indexes as i
inner join
sys.partitions as p
on i.object_id = p.object_id
and i.index_id = p.index_id
inner join
sys.allocation_units as a
on p.partition_id = a.container_id
where
i.[object_id] = object_id('dbo.order details')
and i.index_id = 1 -- clustered index
order by
p.partition_number
go
AMB
"Jerad Rose" wrote:

> I have a seemingly simple task, but the solution isn't quite as
> obvious/simple as one would think.
> I am trying to find out the disk space used by each of the indexes in my
> database. I have been able to get the size used by indexes within each
> table (sp_spaceused), but this sums all of the indexes within the table.
I
> have also been able to get the broken down stats of each index
> (dm_db_index_physical_stats), but it doesn't show how much total size the
> index consumes.
> I can see exactly what I'm looking for in Management Studio, through the
> "Disk Usage" report on the Summary tab for my database. It is listed unde
r
> the "Disk Space Used by Partitions" section, which shows each index, its
> "Total Memory Used", and its "Total Memory Reserved". But I can't manage
to
> find a query that gives me this information.
> Thanks in advance for your help.
> Jerad
>
>|||Thanks for taking the time to respond, Alejandro. Your example works well.
That's the information I needed!
Thanks again.
Jerad
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:6CF06094-5C27-4A2E-B8A2-46FF1C33569E@.microsoft.com...[vbcol=seagreen]
> Jerad Rose,
> 1 - You can use "Profiler" to trace the batch / statement sent to SQL
> Server
> by the custom report.
> 2 - Take a look to these catalog views in BOL.
> - sys.indexes
> - sys.partitions
> - sys.allocation_units
> 3 - You can read more about this in:
> Inside Microsoft (r) SQL Server (tm) 2005: The Storage Engine
> http://www.amazon.com/Inside-Micros...5/dp/0735621055
>
> Example:
> use northwind
> go
> select
> i.[object_id],
> i.index_id,
> p.partition_number,
> p.rows as [#Records],
> a.total_pages * 8 as [Reserved(kb)],
> a.used_pages * 8 as [Used(kb)]
> from
> sys.indexes as i
> inner join
> sys.partitions as p
> on i.object_id = p.object_id
> and i.index_id = p.index_id
> inner join
> sys.allocation_units as a
> on p.partition_id = a.container_id
> where
> i.[object_id] = object_id('dbo.order details')
> and i.index_id = 1 -- clustered index
> order by
> p.partition_number
> go
>
> AMB
> "Jerad Rose" wrote:
>

Retrieving Index Size in SQL Server 2005

I have a seemingly simple task, but the solution isn't quite as
obvious/simple as one would think.
I am trying to find out the disk space used by each of the indexes in my
database. I have been able to get the size used by indexes within each
table (sp_spaceused), but this sums all of the indexes within the table. I
have also been able to get the broken down stats of each index
(dm_db_index_physical_stats), but it doesn't show how much total size the
index consumes.
I can see exactly what I'm looking for in Management Studio, through the
"Disk Usage" report on the Summary tab for my database. It is listed under
the "Disk Space Used by Partitions" section, which shows each index, its
"Total Memory Used", and its "Total Memory Reserved". But I can't manage to
find a query that gives me this information.
Thanks in advance for your help.
JeradJerad Rose,
1 - You can use "Profiler" to trace the batch / statement sent to SQL Server
by the custom report.
2 - Take a look to these catalog views in BOL.
- sys.indexes
- sys.partitions
- sys.allocation_units
3 - You can read more about this in:
Inside Microsoft (r) SQL Server (tm) 2005: The Storage Engine
http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621055
Example:
use northwind
go
select
i.[object_id],
i.index_id,
p.partition_number,
p.rows as [#Records],
a.total_pages * 8 as [Reserved(kb)],
a.used_pages * 8 as [Used(kb)]
from
sys.indexes as i
inner join
sys.partitions as p
on i.object_id = p.object_id
and i.index_id = p.index_id
inner join
sys.allocation_units as a
on p.partition_id = a.container_id
where
i.[object_id] = object_id('dbo.order details')
and i.index_id = 1 -- clustered index
order by
p.partition_number
go
AMB
"Jerad Rose" wrote:
> I have a seemingly simple task, but the solution isn't quite as
> obvious/simple as one would think.
> I am trying to find out the disk space used by each of the indexes in my
> database. I have been able to get the size used by indexes within each
> table (sp_spaceused), but this sums all of the indexes within the table. I
> have also been able to get the broken down stats of each index
> (dm_db_index_physical_stats), but it doesn't show how much total size the
> index consumes.
> I can see exactly what I'm looking for in Management Studio, through the
> "Disk Usage" report on the Summary tab for my database. It is listed under
> the "Disk Space Used by Partitions" section, which shows each index, its
> "Total Memory Used", and its "Total Memory Reserved". But I can't manage to
> find a query that gives me this information.
> Thanks in advance for your help.
> Jerad
>
>|||Thanks for taking the time to respond, Alejandro. Your example works well.
That's the information I needed!
Thanks again.
Jerad
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:6CF06094-5C27-4A2E-B8A2-46FF1C33569E@.microsoft.com...
> Jerad Rose,
> 1 - You can use "Profiler" to trace the batch / statement sent to SQL
> Server
> by the custom report.
> 2 - Take a look to these catalog views in BOL.
> - sys.indexes
> - sys.partitions
> - sys.allocation_units
> 3 - You can read more about this in:
> Inside Microsoft (r) SQL Server (tm) 2005: The Storage Engine
> http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621055
>
> Example:
> use northwind
> go
> select
> i.[object_id],
> i.index_id,
> p.partition_number,
> p.rows as [#Records],
> a.total_pages * 8 as [Reserved(kb)],
> a.used_pages * 8 as [Used(kb)]
> from
> sys.indexes as i
> inner join
> sys.partitions as p
> on i.object_id = p.object_id
> and i.index_id = p.index_id
> inner join
> sys.allocation_units as a
> on p.partition_id = a.container_id
> where
> i.[object_id] = object_id('dbo.order details')
> and i.index_id = 1 -- clustered index
> order by
> p.partition_number
> go
>
> AMB
> "Jerad Rose" wrote:
>> I have a seemingly simple task, but the solution isn't quite as
>> obvious/simple as one would think.
>> I am trying to find out the disk space used by each of the indexes in my
>> database. I have been able to get the size used by indexes within each
>> table (sp_spaceused), but this sums all of the indexes within the table.
>> I
>> have also been able to get the broken down stats of each index
>> (dm_db_index_physical_stats), but it doesn't show how much total size the
>> index consumes.
>> I can see exactly what I'm looking for in Management Studio, through the
>> "Disk Usage" report on the Summary tab for my database. It is listed
>> under
>> the "Disk Space Used by Partitions" section, which shows each index, its
>> "Total Memory Used", and its "Total Memory Reserved". But I can't manage
>> to
>> find a query that gives me this information.
>> Thanks in advance for your help.
>> Jerad
>>
>>

Retrieving Index Size in SQL Server 2005

I have a seemingly simple task, but the solution isn't quite as
obvious/simple as one would think.
I am trying to find out the disk space used by each of the indexes in my
database. I have been able to get the size used by indexes within each
table (sp_spaceused), but this sums all of the indexes within the table. I
have also been able to get the broken down stats of each index
(dm_db_index_physical_stats), but it doesn't show how much total size the
index consumes.
I can see exactly what I'm looking for in Management Studio, through the
"Disk Usage" report on the Summary tab for my database. It is listed under
the "Disk Space Used by Partitions" section, which shows each index, its
"Total Memory Used", and its "Total Memory Reserved". But I can't manage to
find a query that gives me this information.
Thanks in advance for your help.
Jerad
Jerad Rose,
1 - You can use "Profiler" to trace the batch / statement sent to SQL Server
by the custom report.
2 - Take a look to these catalog views in BOL.
- sys.indexes
- sys.partitions
- sys.allocation_units
3 - You can read more about this in:
Inside Microsoft (r) SQL Server (tm) 2005: The Storage Engine
http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621055
Example:
use northwind
go
select
i.[object_id],
i.index_id,
p.partition_number,
p.rows as [#Records],
a.total_pages * 8 as [Reserved(kb)],
a.used_pages * 8 as [Used(kb)]
from
sys.indexes as i
inner join
sys.partitions as p
on i.object_id = p.object_id
and i.index_id = p.index_id
inner join
sys.allocation_units as a
on p.partition_id = a.container_id
where
i.[object_id] = object_id('dbo.order details')
and i.index_id = 1 -- clustered index
order by
p.partition_number
go
AMB
"Jerad Rose" wrote:

> I have a seemingly simple task, but the solution isn't quite as
> obvious/simple as one would think.
> I am trying to find out the disk space used by each of the indexes in my
> database. I have been able to get the size used by indexes within each
> table (sp_spaceused), but this sums all of the indexes within the table. I
> have also been able to get the broken down stats of each index
> (dm_db_index_physical_stats), but it doesn't show how much total size the
> index consumes.
> I can see exactly what I'm looking for in Management Studio, through the
> "Disk Usage" report on the Summary tab for my database. It is listed under
> the "Disk Space Used by Partitions" section, which shows each index, its
> "Total Memory Used", and its "Total Memory Reserved". But I can't manage to
> find a query that gives me this information.
> Thanks in advance for your help.
> Jerad
>
>
|||Thanks for taking the time to respond, Alejandro. Your example works well.
That's the information I needed!
Thanks again.
Jerad
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:6CF06094-5C27-4A2E-B8A2-46FF1C33569E@.microsoft.com...[vbcol=seagreen]
> Jerad Rose,
> 1 - You can use "Profiler" to trace the batch / statement sent to SQL
> Server
> by the custom report.
> 2 - Take a look to these catalog views in BOL.
> - sys.indexes
> - sys.partitions
> - sys.allocation_units
> 3 - You can read more about this in:
> Inside Microsoft (r) SQL Server (tm) 2005: The Storage Engine
> http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621055
>
> Example:
> use northwind
> go
> select
> i.[object_id],
> i.index_id,
> p.partition_number,
> p.rows as [#Records],
> a.total_pages * 8 as [Reserved(kb)],
> a.used_pages * 8 as [Used(kb)]
> from
> sys.indexes as i
> inner join
> sys.partitions as p
> on i.object_id = p.object_id
> and i.index_id = p.index_id
> inner join
> sys.allocation_units as a
> on p.partition_id = a.container_id
> where
> i.[object_id] = object_id('dbo.order details')
> and i.index_id = 1 -- clustered index
> order by
> p.partition_number
> go
>
> AMB
> "Jerad Rose" wrote:

retrieve special column with Sql Full Text Search

hi,
I'm using SQL Server to store files in image column (blob).
Previuosly I Used 'Index Server' with FileSystem To Search text in the files
by doing this i could retrieve special column like DocAuthor,Content, ...
I didn't find a way to do the same in SQL Server Full Text Search to
retrieve the
abstract of the file.
Is there a way to do this and extend the query to more "in file" data?
Any help would be greatly appreciated.
Nissim L
This feature is rumored to ship in SQL 2005. It is not present in SQL 2000.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"nissiml" <nissiml@.discussions.microsoft.com> wrote in message
news:BBA52221-EAEF-428D-BAB1-D61AAE21AE65@.microsoft.com...
> hi,
> I'm using SQL Server to store files in image column (blob).
> Previuosly I Used 'Index Server' with FileSystem To Search text in the
files
> by doing this i could retrieve special column like DocAuthor,Content, ...
> I didn't find a way to do the same in SQL Server Full Text Search to
> retrieve the
> abstract of the file.
> Is there a way to do this and extend the query to more "in file" data?
> Any help would be greatly appreciated.
> Nissim L
>

Retrieve Index names

What is the query that list out all the index names used in the tables?
MadhivananTry this
select Name from sysindexes where id = object_id('Table_Name')
Prad
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1115102277.855498.76730@.f14g2000cwb.googlegroups.com...
> What is the query that list out all the index names used in the tables?
> Madhivanan
>|||Hi
SELECT s1.name, s2.name,
INDEX_COL( s1.name, s2.indid, 1 ),
CASE INDEXPROPERTY( s1.id, s2.name, 'IsClustered' )
WHEN 1 THEN 'Clustered'
ELSE 'Non-clustered'
END
FROM sysobjects s1
INNER JOIN sysindexes s2
ON s1.id = s2.id
WHERE s1.xtype = 'U'
AND s2.indid > 0 AND s2.indid < 255
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1115102277.855498.76730@.f14g2000cwb.googlegroups.com...
> What is the query that list out all the index names used in the tables?
> Madhivanan
>|||Thanks for the replies
This also gives the same
select *
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
Madhivanan|||That only list indexes created through constraints (PK and UNIQUE). The quer
y doesn't list indexes
created using CREATE INDEX.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1115116082.106013.258960@.z14g2000cwz.googlegroups.com...
> Thanks for the replies
> This also gives the same
> select *
> from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
> Madhivanan
>