Index questions

Q1: When creating indexes, which is the best way:
1. Create one index for each column which needs to be indexed?
2. Create one index, which contains all the columns, which need to be indexe
d?
Q2: Does indexex affect the datafile (physical file) size a lot?Hi
http://msdn.microsoft.com/library/d...>
_05_2ri0.asp
What you are describing is a composite index.
http://www.sql-server-performance.c...ite_indexes.asp
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"David Vonasek" wrote:

> Q1: When creating indexes, which is the best way:
> 1. Create one index for each column which needs to be indexed?
> 2. Create one index, which contains all the columns, which need to be inde
xed?
> Q2: Does indexex affect the datafile (physical file) size a lot?
>|||Creating too many indexes also brings down the performance of the system as
the sorting needs to be done on the data files.
Create indexes only on those columns which you think are involved in a WHERE
or ORDER BY clause.
thanks and regards
Chandra
"David Vonasek" wrote:

> Q1: When creating indexes, which is the best way:
> 1. Create one index for each column which needs to be indexed?
> 2. Create one index, which contains all the columns, which need to be inde
xed?
> Q2: Does indexex affect the datafile (physical file) size a lot?
>|||Regarding your first question, it depends.
The first column of the index is very important. Because the optimiser
decides to use that index if your WHERE clause is querying the first column
of the index.
Sometime it is useful to have a covered index. That is a composite index
that contains all the columns required by a special query. See "Designing an
Index" in BOL for more info.
Indexes do consume space. Also, having too many indexes on an OLTP
(read/write) database hurts write performance too.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"David Vonasek" <David Vonasek@.discussions.microsoft.com> wrote in message
news:F09D8517-35D2-4F15-9526-7B161C0230A9@.microsoft.com...
Q1: When creating indexes, which is the best way:
1. Create one index for each column which needs to be indexed?
2. Create one index, which contains all the columns, which need to be
indexed?
Q2: Does indexex affect the datafile (physical file) size a lot?|||Another possibility to consider is whether or not the criteria that you
typically query on is fixed or not. For example if you really only have one
query and that query has criteria on 3 columns, then a covering index will
probably be best. If you support lots of adhoc queries and there may be
criteria on one or more of lets say 8 columns, it may make better sense to
just create 8 individual indexes and the the optimizer use index intersectio
n
in the query plans.
"Narayana Vyas Kondreddi" wrote:

> Regarding your first question, it depends.
> The first column of the index is very important. Because the optimiser
> decides to use that index if your WHERE clause is querying the first colum
n
> of the index.
> Sometime it is useful to have a covered index. That is a composite index
> that contains all the columns required by a special query. See "Designing
an
> Index" in BOL for more info.
> Indexes do consume space. Also, having too many indexes on an OLTP
> (read/write) database hurts write performance too.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "David Vonasek" <David Vonasek@.discussions.microsoft.com> wrote in message
> news:F09D8517-35D2-4F15-9526-7B161C0230A9@.microsoft.com...
> Q1: When creating indexes, which is the best way:
> 1. Create one index for each column which needs to be indexed?
> 2. Create one index, which contains all the columns, which need to be
> indexed?
> Q2: Does indexex affect the datafile (physical file) size a lot?
>
>

Index on Views ...

I know that we can create indexes on views (Indexed views) in sql 2k. I am
trying to understand what would be the reason for creating a index on a view
.
Anyway a view is a virtual table and if there is an index on those table
won't it be enough? Hope somebody could make me understand this.
Regards
PradeepHi
I think you missed the concept of INDEXED VIEWS
http://www.sql-server-performance.com/indexed_views.asp
In addition please take a look at Steve Kass's scipt to ensure that using
an indexe view SQL Server creates more efficient execution plan
create table T (
i int,
filler char(1000) default 'abc'
)
go
create view T_count with schemabinding as
select
cast(i as bit) as val,
count_big(*) T_count
from dbo.T group by cast(i as bit)
go
create unique clustered index T_count_uci on T_count(val)
go
insert into T(i)
select OrderID
from Northwind..[Order Details]
go
set statistics io on
select count(*) from T
go
select sum(T_count) from T_count with (noexpand)
go
set statistics io off
-- uses an efficient query plan on the materialized view
go
drop view T_count
drop table T
"SqlBeginner" <SqlBeginner@.discussions.microsoft.com> wrote in message
news:F47FC536-3817-4DCE-A1F5-8E1C077ACDCF@.microsoft.com...
>I know that we can create indexes on views (Indexed views) in sql 2k. I am
> trying to understand what would be the reason for creating a index on a
> view.
> Anyway a view is a virtual table and if there is an index on those table
> won't it be enough? Hope somebody could make me understand this.
> Regards
> Pradeep|||Thanks for ur response Uri. I would go through that link.
Regards
Pradeep
"Uri Dimant" wrote:

> Hi
> I think you missed the concept of INDEXED VIEWS
> http://www.sql-server-performance.com/indexed_views.asp
>
> In addition please take a look at Steve Kass's scipt to ensure that using
> an indexe view SQL Server creates more efficient execution plan
> create table T (
> i int,
> filler char(1000) default 'abc'
> )
> go
> create view T_count with schemabinding as
> select
> cast(i as bit) as val,
> count_big(*) T_count
> from dbo.T group by cast(i as bit)
> go
> create unique clustered index T_count_uci on T_count(val)
> go
> insert into T(i)
> select OrderID
> from Northwind..[Order Details]
> go
> set statistics io on
> select count(*) from T
> go
> select sum(T_count) from T_count with (noexpand)
> go
> set statistics io off
> -- uses an efficient query plan on the materialized view
> go
> drop view T_count
> drop table T
>
>
> "SqlBeginner" <SqlBeginner@.discussions.microsoft.com> wrote in message
> news:F47FC536-3817-4DCE-A1F5-8E1C077ACDCF@.microsoft.com...
>
>

Index on View

Hi

I have a problem creating an index on a view. The view should return the record corresponding to the Maximum Obje_ID. This seems to work.

CREATE VIEW dbo.D_Object_View
WITH SCHEMABINDING
AS
SELECT
Policy_ID,
Obj_ID,
Environment_Code,
CoB,
Sub_CoB,
Policy_No,
Version_No,
Object_Type,
Item_Seq,

FROM dbo.D_Object
WHERE
(Obj_ID IN
(SELECT MAX(Obj_ID)
FROM dbo.d_object
GROUP BY Environment_Code, COB, Policy_No, SUB_COB, Object_Type, Item_Seq))

I create the index with the following statement :
CREATE UNIQUE CLUSTERED INDEX [IX_Object_ID] ON [dbo].[D_Object_View]([Obj_ID]) ON [PRIMARY]

but get the following error :
Cannot index the view 'DB.dbo.D_Object_View'. It contains one or more disallowed constructs.

I think it is because of the MAX statement but don't know of any other way to do it. :confused:Is Obj_ID part of an index in the parent tables? If so the index on the view may not buy you much performance improvement. How many rows are in each table and what's the execution plan look like for the view sql without the index?

Have you tried creating a non-unique index on the column?|||Yes Obj_ID is an index on the parent table and it cpontains aprox. 5 mil records but will increase as i need to add more data.

I have tried creating a non-unique one but get the following error :
Nonunique clustered index cannot be created on view 'D_Object_View' because only unique clustered indexes are allowed.|||Sorry, forgot about that I'm sure you've tried it but what about a non-clustered index? And does the optimizer utilize the existing index in the execution plan?|||A nonclustered gives me the following error:
Cannot create index on view 'D_Object_View'. It does not have a unique clustered index.

My knowledge of SQL is limited but if I understand correctly about the optimizer ... the estimated execution plan utilises a Index scan. This is good right ?

Index on Computed column or Indexed View

I have a large nvarchar(2000) that need to be queried on often based on a
subset of the data (first 50 characters). The application creating and using
the data cannot be modified to capture a short and long column... I was
wondering if creating a computed column with the formula being
Left(longcolumn, 50) and creating an index based on this column could be a
good option? Or would it be preferable to create an indexed view?
Any other suggestion are welcomed
Thank you for your helpI would go with a computed column to start with.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Martin Rajotte" <MartinRajotte@.discussions.microsoft.com> wrote in message
news:0EB763CD-5C4E-455C-83E2-1454742D5507@.microsoft.com...
> I have a large nvarchar(2000) that need to be queried on often based on a
> subset of the data (first 50 characters). The application creating and
using
> the data cannot be modified to capture a short and long column... I was
> wondering if creating a computed column with the formula being
> Left(longcolumn, 50) and creating an index based on this column could be a
> good option? Or would it be preferable to create an indexed view?
> Any other suggestion are welcomed
> Thank you for your help
>|||Thank you for help. It confirms my tests that I performed last night.
"Narayana Vyas Kondreddi" wrote:

> I would go with a computed column to start with.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Martin Rajotte" <MartinRajotte@.discussions.microsoft.com> wrote in messag
e
> news:0EB763CD-5C4E-455C-83E2-1454742D5507@.microsoft.com...
> using
>
>

Index in view

I 've 5 tables not indexed(must).
Now i create 5 views for these with indexing.
creating index only on views are possible, if so it can increase the
performance od query. Can u give me the soln ?Read about indexed views in Books Online. Note that only Enterprise Edition
will use such indexes
automatically. I would reconsider why you cannot create indexes on the base
tables. Why is that?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"JJFreds" <JJFreds@.discussions.microsoft.com> wrote in message
news:9A6FA327-D8F1-4A85-99F4-E521EEA400D8@.microsoft.com...
>I 've 5 tables not indexed(must).
> Now i create 5 views for these with indexing.
> creating index only on views are possible, if so it can increase the
> performance od query. Can u give me the soln ?

Index Fragmentation

I have been tasked with a obtaining a method of manually creating Clustered Index fragmentation for testing purposes. Does anyone have a script that would cause fragmentation but would still keep the table contents the same. Thanks.

I don't have a script, but something like this for each table...

You have to reload it I beleive. Can't rebuild the clusterered index a different order and set the order back, as it would defrag at that point...so get the data off to a temp table...

select * into #tableName from tableName

Then you need to reload it in chunks, with an order by that goes against the clustered index column order... If you just try one insert from that #tableName, it won't frag enough, SQL will get it mostly in clustered order as it loads.. but if you break up the loads into unordered batches it'll frag the clusterered index... You'll need to deal with PK/FK's and copy teh data off to a temp table , then bring it back something like this, in small batches, until all teh data is back in...

Code Snippet

insert into tableName

select top 1000 *

from #tableName t1

where not exists

(select *

from tableName t2

where t1.pkcolumn = t2.pkcolumn)

order by non_clusterd_columns desc

You could generate that script if you have numerous table to reload.... I was looking to see if there is a command on a CREATE INDEX to just not rebuild the clustered index if you change it, but didn't see it.. might be a way though someone else knows......... Have fun.... Bruce

|||

This seems like it will do the job. The only issue i have is that the table does not have a unique ID. So any ideas on loading in 1000 row chunks would be appreciated.

Thanks

|||

One way of doing that... you could add a row number to the # table, like this...

Code Snippet

select

ROW_NUMBER() OVER (ORDER BY non_clustered_column) AS 'ID',*

into #tableName

from tableName

order by non_clustered_column

so your # table would then be setup with an ID and in jumbled order .... could then loop thru that by ID, in batches of 1000 and it would be loading in scrambled order. Do enough batches so it scrambles it good, the lower the batch row count the better to cause fragmentation... Bruce

|||Many thanks for this. Just need to figure out the best way to insert in 1000 row batches now.|||I am stuggling to find a method of importing 1000 row batches. What would be the best method, if i have added the ID column to the table?|||

Once you have your temp table, and the ID field in it... you could do some type of a LOOP like this...

Code Snippet

while (1=1)

BEGIN

INSERT INTO yourTable

select top 1000 *

from #tempTable t1

where not exists

(select *

from yourTable t2

where t1.ID = t2.ID)

order by nonclustereColumn desc

if (@.@.rowcount = 0 ) break

END

Might have to play some games with the ID not being in your base table. Like, add the column, then drop it after you load... But you said you have a clustered index, so just do the match based on all clustered index columns... and don't bother with the new ID column?!?...

Bruce|||Thanks Bruce

Index Fragmentation

I have been tasked with a obtaining a method of manually creating Clustered Index fragmentation for testing purposes. Does anyone have a script that would cause fragmentation but would still keep the table contents the same. Thanks.

I don't have a script, but something like this for each table...

You have to reload it I beleive. Can't rebuild the clusterered index a different order and set the order back, as it would defrag at that point...so get the data off to a temp table...

select * into #tableName from tableName

Then you need to reload it in chunks, with an order by that goes against the clustered index column order... If you just try one insert from that #tableName, it won't frag enough, SQL will get it mostly in clustered order as it loads.. but if you break up the loads into unordered batches it'll frag the clusterered index... You'll need to deal with PK/FK's and copy teh data off to a temp table , then bring it back something like this, in small batches, until all teh data is back in...

Code Snippet

insert into tableName

select top 1000 *

from #tableName t1

where not exists

(select *

from tableName t2

where t1.pkcolumn = t2.pkcolumn)

order by non_clusterd_columns desc

You could generate that script if you have numerous table to reload.... I was looking to see if there is a command on a CREATE INDEX to just not rebuild the clustered index if you change it, but didn't see it.. might be a way though someone else knows......... Have fun.... Bruce

|||

This seems like it will do the job. The only issue i have is that the table does not have a unique ID. So any ideas on loading in 1000 row chunks would be appreciated.

Thanks

|||

One way of doing that... you could add a row number to the # table, like this...

Code Snippet

select

ROW_NUMBER()OVER(ORDERBYnon_clustered_column)AS'ID',*

into #tableName

from tableName

order by non_clustered_column

so your # table would then be setup with an ID and in jumbled order .... could then loop thru that by ID, in batches of 1000 and it would be loading in scrambled order. Do enough batches so it scrambles it good, the lower the batch row count the better to cause fragmentation... Bruce

|||Many thanks for this. Just need to figure out the best way to insert in 1000 row batches now.|||I am stuggling to find a method of importing 1000 row batches. What would be the best method, if i have added the ID column to the table?|||

Once you have your temp table, and the ID field in it... you could do some type of a LOOP like this...

Code Snippet

while(1=1)

BEGIN

INSERTINTO yourTable

selecttop 1000 *

from #tempTable t1

wherenotexists

(select*

from yourTable t2

where t1.ID= t2.ID)

orderby nonclustereColumn desc

if(@.@.rowcount= 0 )break

END

Might have to play some games with the ID not being in your base table. Like, add the column, then drop it after you load... But you said you have a clustered index, so just do the match based on all clustered index columns... and don't bother with the new ID column?!?...

Bruce|||Thanks Bruce