index and key: state

Showing posts with label state. Show all posts

Friday, March 23, 2012

Index question

When I have tables with small rows and few rows, are indexes needed?
ex: the U.S. federal government numbers every county in the USA. In my
state there are 100 counties. Create Table Counties (
CountyCode smallint,
CountyName VarChar(30),
Region smallint)
All data for all 100 counties will easily be read by SQL in one physical
read. So, is there any reason to create an index? Most applications
written in my office have lookup tables with a small number of small
rows. Same question.
Thanks in advance
Tom
--
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.Tom
If you have small table so optimizer will choose scan table to retrieve
the data. An index may improve the performance if your WHERE clause searches
for a few rows and the index is created on selective (at least 95%) column
Just testing,testing....
"Tom Williams" <Tom.Williams@.ncmail.net> wrote in message
news:O4dVMNqRGHA.5924@.TK2MSFTNGP09.phx.gbl...
> When I have tables with small rows and few rows, are indexes needed?
> ex: the U.S. federal government numbers every county in the USA. In my
> state there are 100 counties. Create Table Counties (
> CountyCode smallint,
> CountyName VarChar(30),
> Region smallint)
> All data for all 100 counties will easily be read by SQL in one physical
> read. So, is there any reason to create an index? Most applications
> written in my office have lookup tables with a small number of small rows.
> Same question.
> Thanks in advance
> Tom
> --
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.
>|||if you modify tables without any indexes, you may end up locking up the
whole table.
Also the best practice is to have a primary key for every table.|||[repost]
Every table should have at least one key. Keys exist to maintain the
integrity of your data not for performance. In SQL Server a key
constraint, whether PRIMARY KEY or UNIQUE, always has an index. So the
answer is yes, but not all indexes are required for performance
reasons.
BTW, under SQL Server's default ANSI configuration your sample table
can't have a key because all the columns are nullable. If the CREATE
TABLE statement you posted is accurate then I suggest you fix that.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Bah.
small reference tables do not require indexes. Keys are kind of nice,
but the average schmuck dba can ususally figure out how a reference
table works.
for really small tables, it is about 6's to add an index or not.
Personal preference.
My rule of thumb is 20 rows. more gets an index, less doesn't. 100 is
pretty close to 20.|||Doug wrote:
> Bah.
> small reference tables do not require indexes. Keys are kind of nice,
> but the average schmuck dba can ususally figure out how a reference
> table works.
> for really small tables, it is about 6's to add an index or not.
> Personal preference.
> My rule of thumb is 20 rows. more gets an index, less doesn't. 100 is
> pretty close to 20.
The job of a key is to help out the DBA? That's a good one!
I really hope you meant your reply ironically. If not, I hope no
database I see will ever be a victim of your rule of thumb.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Index Question

Index problem and another question

create clustered index AOP_C_Idx on AOP_Master([Date])

Running this query gives the error

Server: Msg 169, Level 15, State 2, Line 2
A column has been specified more than once in the order by list. Columns in the order by list must be unique.

However I do not have any other indexes on the table. What could be the problem ?

And where did the dBForums Yak corral vanish ??Ensure that there are no duplicates in the specified column.|||No duplicates ??Can a clustered index only be unique ? What if I do not have unique values in the table ?|||Any Ideas Gurus ??|||I don't know...started a new one...|||Originally posted by Enigma
create clustered index AOP_C_Idx on AOP_Master([Date])

Running this query gives the error

Server: Msg 169, Level 15, State 2, Line 2
A column has been specified more than once in the order by list. Columns in the order by list must be unique.

However I do not have any other indexes on the table. What could be the problem ?

And where did the dBForums Yak corral vanish ??

Is that the exact sql?|||Originally posted by Enigma
No duplicates ??Can a clustered index only be unique ? What if I do not have unique values in the table ?

CREATE a non unique index?

What are you trying to do?|||I am trying to create a non unique Clustered index on a table which already exists and getting the error .

And yes this is the exact sql I am using

create clustered index AOP_C_Idx on AOP_Master([Date])|||What happens when you run

select *
from AOP_Master
order by [Date]?

Do you somehow have two date columns in the table? Can't be.|||I've had similiar strange unexplainable problems trying to create foreign keys and indexes too. I would try to create the fk or index and SQL server thought the index was already there eventhough it wasn't. I had to drop and re-create the table in order to get the fk or index to create. This is a shot in the dark but hope it helps.|||http://support.microsoft.com/default.aspx?scid=kb;en-us;293177

Here is the solution to the problem ...

I was hoping and I was right ....

IT IS A DOCUMENTED BUG|||Please post this also to SQLTeam.com for completeness|||Originally posted by Enigma
And where did the dBForums Yak corral vanish ??

I guess I have been away for a while... what is a sbForums Yak corral?

Wednesday, March 7, 2012

Index Error

Hello,
We are running SQL 2000 Service Pack 3a.
We are getting the following error when we perform a load..
Error: 8646, Severity: 21, State: 1
The index entry for row ID was not found in index ID 5, of table 933578364,
in database 'MYDBNAME'.
I have run a DBCC CHECK db on the database and it reported no errors.
I have also followed the instructions in
http://support.microsoft.com/kb/822747 however we do not have a unicode
field, and the collation is different.
We backed up the Database and put it onto a different Server and the error
occured again.
The table it points to has just over 1 million records.
Now here is the oddity, if I delete the indexes and put them back in I can
run the load with the same data and it works.
I am at a loss on how to proceed with this, so I was wondering if some
bright people could help.
Thanks
J
The schema for the table is as follows :-
CREATE TABLE [dbo].[PARCELS] (
[PARCEL_ID] [int] IDENTITY (1, 1) NOT NULL ,
[CONSIGNMENT_NO] [int] NULL ,
[RETL_CODE] [smallint] NULL ,
[PARCEL_TYPE] [tinyint] NOT NULL ,
[PARCEL_NO] [int] NULL ,
[BOX_NUMBER] [smallint] NULL ,
[BARCODE] [varchar] (13) COLLATE Latin1_General_CI_AS NULL ,
[CREATED_AT] [bigint] NULL ,
[CREATED_BY] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[ROUTE_PLAN] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[SIZE] [int] NOT NULL ,
[WEIGHT] [int] NOT NULL ,
[CUSTOMER_CONSIGNMENT] [int] NULL ,
[LAST_TRACK_EVENT] [int] NULL ,
[HELD] [bit] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PARCELS] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[PARCEL_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PARCELS] ADD
CONSTRAINT [DF__PARCELS__HELD__398D8EEE] DEFAULT (0) FOR [HELD]
GO
CREATE INDEX [PARCELS_1] ON [dbo].[PARCELS]([RETL_CODE]) ON [PRIMARY]
GO
CREATE INDEX [PARCELS_3] ON [dbo].[PARCELS]([CUSTOMER_CONSIGNMENT]) ON
[PRIMARY]
GO
CREATE INDEX [PARCELS_4] ON [dbo].[PARCELS]([BARCODE]) ON [PRIMARY]
GO
CREATE INDEX [PARCELS_8] ON [dbo].[PARCELS]([PARCEL_NO]) ON [PRIMARY]
GO
CREATE INDEX [PARCELS_5] ON [dbo].[PARCELS]([CONSIGNMENT_NO]) ON [PRIMARY]
GO
CREATE INDEX [PARCELS_7] ON [dbo].[PARCELS]([PARCEL_TYPE]) ON [PRIMARY]
GOJulie,
Sounds like a questionable nonclustered index. Since the index doesn't
contain the actual data but pointers to the data the quick fix would
probably be to just drop the index and recreate it. If it occurs again, you
might check the disk (array) if DBCC CHECDB is not reporting anything.
HTH
Jerry
"Julie" <Julie@.discussions.microsoft.com> wrote in message
news:813D3A08-56FD-41A8-B307-C66963DCBC40@.microsoft.com...
> Hello,
> We are running SQL 2000 Service Pack 3a.
> We are getting the following error when we perform a load..
> Error: 8646, Severity: 21, State: 1
> The index entry for row ID was not found in index ID 5, of table
> 933578364,
> in database 'MYDBNAME'.
> I have run a DBCC CHECK db on the database and it reported no errors.
> I have also followed the instructions in
> http://support.microsoft.com/kb/822747 however we do not have a unicode
> field, and the collation is different.
> We backed up the Database and put it onto a different Server and the error
> occured again.
> The table it points to has just over 1 million records.
> Now here is the oddity, if I delete the indexes and put them back in I can
> run the load with the same data and it works.
> I am at a loss on how to proceed with this, so I was wondering if some
> bright people could help.
> Thanks
> J
> The schema for the table is as follows :-
> CREATE TABLE [dbo].[PARCELS] (
> [PARCEL_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [CONSIGNMENT_NO] [int] NULL ,
> [RETL_CODE] [smallint] NULL ,
> [PARCEL_TYPE] [tinyint] NOT NULL ,
> [PARCEL_NO] [int] NULL ,
> [BOX_NUMBER] [smallint] NULL ,
> [BARCODE] [varchar] (13) COLLATE Latin1_General_CI_AS NULL ,
> [CREATED_AT] [bigint] NULL ,
> [CREATED_BY] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
> [ROUTE_PLAN] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
> [SIZE] [int] NOT NULL ,
> [WEIGHT] [int] NOT NULL ,
> [CUSTOMER_CONSIGNMENT] [int] NULL ,
> [LAST_TRACK_EVENT] [int] NULL ,
> [HELD] [bit] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PARCELS] WITH NOCHECK ADD
> PRIMARY KEY CLUSTERED
> (
> [PARCEL_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PARCELS] ADD
> CONSTRAINT [DF__PARCELS__HELD__398D8EEE] DEFAULT (0) FOR [HELD]
> GO
> CREATE INDEX [PARCELS_1] ON [dbo].[PARCELS]([RETL_CODE]) ON [PRIMARY]
> GO
> CREATE INDEX [PARCELS_3] ON [dbo].[PARCELS]([CUSTOMER_CONSIGNMENT]) ON
> [PRIMARY]
> GO
> CREATE INDEX [PARCELS_4] ON [dbo].[PARCELS]([BARCODE]) ON [PRIMARY]
> GO
> CREATE INDEX [PARCELS_8] ON [dbo].[PARCELS]([PARCEL_NO]) ON [PRIMARY]
> GO
> CREATE INDEX [PARCELS_5] ON [dbo].[PARCELS]([CONSIGNMENT_NO]) ON
> [PRIMARY]
> GO
> CREATE INDEX [PARCELS_7] ON [dbo].[PARCELS]([PARCEL_TYPE]) ON [PRIMARY]
> GO
>
>

Index Error

Hello,
We are running SQL 2000 Service Pack 3a.
We are getting the following error when we perform a load..
Error: 8646, Severity: 21, State: 1
The index entry for row ID was not found in index ID 5, of table 933578364,
in database 'MYDBNAME'.
I have run a DBCC CHECK db on the database and it reported no errors.
I have also followed the instructions in
http://support.microsoft.com/kb/822747 however we do not have a unicode
field, and the collation is different.
We backed up the Database and put it onto a different Server and the error
occured again.
The table it points to has just over 1 million records.
Now here is the oddity, if I delete the indexes and put them back in I can
run the load with the same data and it works.
I am at a loss on how to proceed with this, so I was wondering if some
bright people could help.
Thanks
J
The schema for the table is as follows :-
CREATE TABLE [dbo].[PARCELS] (
[PARCEL_ID] [int] IDENTITY (1, 1) NOT NULL ,
[CONSIGNMENT_NO] [int] NULL ,
[RETL_CODE] [smallint] NULL ,
[PARCEL_TYPE] [tinyint] NOT NULL ,
[PARCEL_NO] [int] NULL ,
[BOX_NUMBER] [smallint] NULL ,
[BARCODE] [varchar] (13) COLLATE Latin1_General_CI_AS NULL ,
[CREATED_AT] [bigint] NULL ,
[CREATED_BY] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[ROUTE_PLAN] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[SIZE] [int] NOT NULL ,
[WEIGHT] [int] NOT NULL ,
[CUSTOMER_CONSIGNMENT] [int] NULL ,
[LAST_TRACK_EVENT] [int] NULL ,
[HELD] [bit] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PARCELS] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[PARCEL_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PARCELS] ADD
CONSTRAINT [DF__PARCELS__HELD__398D8EEE] DEFAULT (0) FOR [HELD]
GO
CREATE INDEX [PARCELS_1] ON [dbo].[PARCELS]([RETL_CODE]) ON
[PRIMARY]
GO
CREATE INDEX [PARCELS_3] ON [dbo].[PARCELS]([CUSTOMER_CONSI
GNMENT]) ON
[PRIMARY]
GO
CREATE INDEX [PARCELS_4] ON [dbo].[PARCELS]([BARCODE]) ON &
#91;PRIMARY]
GO
CREATE INDEX [PARCELS_8] ON [dbo].[PARCELS]([PARCEL_NO]) ON
[PRIMARY]
GO
CREATE INDEX [PARCELS_5] ON [dbo].[PARCELS]([CONSIGNMENT_NO
]) ON [PRIMARY]
GO
CREATE INDEX [PARCELS_7] ON [dbo].[PARCELS]([PARCEL_TYPE])
ON [PRIMARY]
GOJulie,
Sounds like a questionable nonclustered index. Since the index doesn't
contain the actual data but pointers to the data the quick fix would
probably be to just drop the index and recreate it. If it occurs again, you
might check the disk (array) if DBCC CHECDB is not reporting anything.
HTH
Jerry
"Julie" <Julie@.discussions.microsoft.com> wrote in message
news:813D3A08-56FD-41A8-B307-C66963DCBC40@.microsoft.com...
> Hello,
> We are running SQL 2000 Service Pack 3a.
> We are getting the following error when we perform a load..
> Error: 8646, Severity: 21, State: 1
> The index entry for row ID was not found in index ID 5, of table
> 933578364,
> in database 'MYDBNAME'.
> I have run a DBCC CHECK db on the database and it reported no errors.
> I have also followed the instructions in
> http://support.microsoft.com/kb/822747 however we do not have a unicode
> field, and the collation is different.
> We backed up the Database and put it onto a different Server and the error
> occured again.
> The table it points to has just over 1 million records.
> Now here is the oddity, if I delete the indexes and put them back in I can
> run the load with the same data and it works.
> I am at a loss on how to proceed with this, so I was wondering if some
> bright people could help.
> Thanks
> J
> The schema for the table is as follows :-
> CREATE TABLE [dbo].[PARCELS] (
> [PARCEL_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [CONSIGNMENT_NO] [int] NULL ,
> [RETL_CODE] [smallint] NULL ,
> [PARCEL_TYPE] [tinyint] NOT NULL ,
> [PARCEL_NO] [int] NULL ,
> [BOX_NUMBER] [smallint] NULL ,
> [BARCODE] [varchar] (13) COLLATE Latin1_General_CI_AS NULL ,
> [CREATED_AT] [bigint] NULL ,
> [CREATED_BY] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
> [ROUTE_PLAN] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
> [SIZE] [int] NOT NULL ,
> [WEIGHT] [int] NOT NULL ,
> [CUSTOMER_CONSIGNMENT] [int] NULL ,
> [LAST_TRACK_EVENT] [int] NULL ,
> [HELD] [bit] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PARCELS] WITH NOCHECK ADD
> PRIMARY KEY CLUSTERED
> (
> [PARCEL_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PARCELS] ADD
> CONSTRAINT [DF__PARCELS__HELD__398D8EEE] DEFAULT (0) FOR [HELD]
> GO
> CREATE INDEX [PARCELS_1] ON [dbo].[PARCELS]([RETL_CODE])
ON [PRIMARY]
> GO
> CREATE INDEX [PARCELS_3] ON [dbo].[PARCELS]([CUSTOMER_CON
SIGNMENT]) ON
> [PRIMARY]
> GO
> CREATE INDEX [PARCELS_4] ON [dbo].[PARCELS]([BARCODE]) ON
[PRIMARY]
> GO
> CREATE INDEX [PARCELS_8] ON [dbo].[PARCELS]([PARCEL_NO])
ON [PRIMARY]
> GO
> CREATE INDEX [PARCELS_5] ON [dbo].[PARCELS]([CONSIGNMENT_
NO]) ON
> [PRIMARY]
> GO
> CREATE INDEX [PARCELS_7] ON [dbo].[PARCELS]([PARCEL_TYPE]
) ON [PRIMARY]
> GO
>
>

Friday, February 24, 2012

Index column order?

I need to create an index with multple columns, for example, an index
contains "State", "City" and "Street". Obviously the density of the columns
is State < City < Street. Is it necessary to follow a particular order when
create index for better performance? For example (State, City, Street) or
(Street, City, State)?Yes, it can have a huge impact. Higher selectivity = less rows traversed =better performance. Also remember that index statistics are only computed
for the first column in the index; statistics play a large part when the
optimizer does its job, so it's important to keep that in mind.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"nick" <nick@.discussions.microsoft.com> wrote in message
news:56387438-D942-40EB-AE98-26653AF2F0E4@.microsoft.com...
> I need to create an index with multple columns, for example, an index
> contains "State", "City" and "Street". Obviously the density of the
columns
> is State < City < Street. Is it necessary to follow a particular order
when
> create index for better performance? For example (State, City, Street) or
> (Street, City, State)?|||If you frequently search on some subset of the indexed columns then put
those columns first. Otherwise, put the most selective first (the column
with the highest cardinality). So I expect that's (Street, City, State).
--
David Portas
SQL Server MVP
--|||Hi
Based on your where clause.
If you have an index on State, City, Street and use a where clause City,
State, Street, the index will not be used. In this case, SQL can not guess
that state is of any use, and it then can't use the index for the sort.
Maybe and Index on state, a separate one on city, and a separate one on
street.
A compound index is generally a bad idea as a data modification will result
in a lot of index maintenance, slowing down the operation.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"nick" <nick@.discussions.microsoft.com> wrote in message
news:56387438-D942-40EB-AE98-26653AF2F0E4@.microsoft.com...
>I need to create an index with multple columns, for example, an index
> contains "State", "City" and "Street". Obviously the density of the
> columns
> is State < City < Street. Is it necessary to follow a particular order
> when
> create index for better performance? For example (State, City, Street) or
> (Street, City, State)?|||"nick" <nick@.discussions.microsoft.com> wrote in message
news:56387438-D942-40EB-AE98-26653AF2F0E4@.microsoft.com...
> I need to create an index with multple columns, for example, an index
> contains "State", "City" and "Street". Obviously the density of the
columns
> is State < City < Street. Is it necessary to follow a particular order
when
> create index for better performance? For example (State, City, Street) or
> (Street, City, State)?
That depends on what you are searching for, and how you want your data
sorted.
If you were selecting city and street, then the State, City, Street index
would not be useful.
Think of the data in your index as if it were listed in that order in your
phone book. If you want to find everyone in the state, sorted by city and
street, then having the phone laid out by State, City and then Street would
work best for that particular query.
Take a look at the index tuning wizard. It can provide you a lot of great
information about which indexes to create given a workload.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Tips on Optimizing SQL Server Composite Indexes
http://www.sql-server-performance.com/composite_indexes.asp
AMB
"nick" wrote:
> I need to create an index with multple columns, for example, an index
> contains "State", "City" and "Street". Obviously the density of the columns
> is State < City < Street. Is it necessary to follow a particular order when
> create index for better performance? For example (State, City, Street) or
> (Street, City, State)?|||Adam,
I am a little bit confused. If I create the following index:
use northwind
go
create nonclustered index ix_nc_employees_country_city_postalcode on
employees(country, city, postalcode)
go
dbcc show_statistics (employees, ix_nc_employees_country_city_postalcode)
go
I see statistics for:
- country
- country, city
- country, city, postalcode
how does this apply to "index statistics are only computed for the first
column in the index"?
if I do:
set showplan_text on
go
select
*
from
dbo.employees
where
country = 'usa'
and city like 's%'
go
set showplan_text on
go
the stimated execution plan shows an index seek in
ix_nc_employees_country_city_postalcode.
Thanks in advance,
Alejandro Mesa
"Adam Machanic" wrote:
> Yes, it can have a huge impact. Higher selectivity = less rows traversed => better performance. Also remember that index statistics are only computed
> for the first column in the index; statistics play a large part when the
> optimizer does its job, so it's important to keep that in mind.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "nick" <nick@.discussions.microsoft.com> wrote in message
> news:56387438-D942-40EB-AE98-26653AF2F0E4@.microsoft.com...
> > I need to create an index with multple columns, for example, an index
> > contains "State", "City" and "Street". Obviously the density of the
> columns
> > is State < City < Street. Is it necessary to follow a particular order
> when
> > create index for better performance? For example (State, City, Street) or
> > (Street, City, State)?
>
>|||Sorry Mike, but the order of the logical expressions in the where clause is
not important. SQL Server does not evaluate them in a specific order. What I
think is important is that the columns in the where clause follow this
pattern (the order of the logical expressions is not important):
...
where state ...
-- or
where state ... and city ...
-- or
where state ... and street ...
-- or
...
where state ... and city ... and street ...
if you filter by (city, street) then sql server will not implement an index
seek in this index.
Regards,
Alejandro Mesa
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Based on your where clause.
> If you have an index on State, City, Street and use a where clause City,
> State, Street, the index will not be used. In this case, SQL can not guess
> that state is of any use, and it then can't use the index for the sort.
> Maybe and Index on state, a separate one on city, and a separate one on
> street.
> A compound index is generally a bad idea as a data modification will result
> in a lot of index maintenance, slowing down the operation.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "nick" <nick@.discussions.microsoft.com> wrote in message
> news:56387438-D942-40EB-AE98-26653AF2F0E4@.microsoft.com...
> >I need to create an index with multple columns, for example, an index
> > contains "State", "City" and "Street". Obviously the density of the
> > columns
> > is State < City < Street. Is it necessary to follow a particular order
> > when
> > create index for better performance? For example (State, City, Street) or
> > (Street, City, State)?
>
>|||So to maxmize the performance (without think about the update penalty), we
better create three index:
(state, city, street)
(City, Street)
(Street)
Or the following order is the same?
(Street, City, State)
(City, State)
(State)
will be used for all situation like
where state='...' and street = '...' and city='...' (Order doesn't matter?)
where street = ''
where city='' and street=''
but not "where sat
"Alejandro Mesa" wrote:
> Sorry Mike, but the order of the logical expressions in the where clause is
> not important. SQL Server does not evaluate them in a specific order. What I
> think is important is that the columns in the where clause follow this
> pattern (the order of the logical expressions is not important):
> ...
> where state ...
> -- or
> where state ... and city ...
> -- or
> where state ... and street ...
> -- or
> ...
> where state ... and city ... and street ...
> if you filter by (city, street) then sql server will not implement an index
> seek in this index.
>
> Regards,
> Alejandro Mesa
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > Based on your where clause.
> >
> > If you have an index on State, City, Street and use a where clause City,
> > State, Street, the index will not be used. In this case, SQL can not guess
> > that state is of any use, and it then can't use the index for the sort.
> >
> > Maybe and Index on state, a separate one on city, and a separate one on
> > street.
> >
> > A compound index is generally a bad idea as a data modification will result
> > in a lot of index maintenance, slowing down the operation.
> >
> > Regards
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > IM: mike@.epprecht.net
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> > "nick" <nick@.discussions.microsoft.com> wrote in message
> > news:56387438-D942-40EB-AE98-26653AF2F0E4@.microsoft.com...
> > >I need to create an index with multple columns, for example, an index
> > > contains "State", "City" and "Street". Obviously the density of the
> > > columns
> > > is State < City < Street. Is it necessary to follow a particular order
> > > when
> > > create index for better performance? For example (State, City, Street) or
> > > (Street, City, State)?
> >
> >
> >|||"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:327FEF28-39BC-4B5C-991F-612139545D77@.microsoft.com...
> I see statistics for:
> - country
> - country, city
> - country, city, postalcode
> how does this apply to "index statistics are only computed for the first
> column in the index"?
I probably phrased that poorly. Should have been, "index statistics are
based on the first column in the index." Which, I hope, serves to imply
that no statistics will be computed on only the second column, or only the
second and third columns, etc. The first column is the key.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||If you create the composite index described, SHOW_STATISTICS will show you
density info for each subset of keys, but the density info is only the
average number of duplicates across all values. Following the density info
you should see the statistics, which is the histogram on number of
occurences of specific values for the index key. That only applies to the
leading column of the index. Please see this whitepaper for more info:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/statquery.asp?frame=true
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:327FEF28-39BC-4B5C-991F-612139545D77@.microsoft.com...
> Adam,
> I am a little bit confused. If I create the following index:
> use northwind
> go
> create nonclustered index ix_nc_employees_country_city_postalcode on
> employees(country, city, postalcode)
> go
> dbcc show_statistics (employees, ix_nc_employees_country_city_postalcode)
> go
> I see statistics for:
> - country
> - country, city
> - country, city, postalcode
> how does this apply to "index statistics are only computed for the first
> column in the index"?
> if I do:
> set showplan_text on
> go
> select
> *
> from
> dbo.employees
> where
> country = 'usa'
> and city like 's%'
> go
> set showplan_text on
> go
> the stimated execution plan shows an index seek in
> ix_nc_employees_country_city_postalcode.
>
> Thanks in advance,
> Alejandro Mesa
> "Adam Machanic" wrote:
>> Yes, it can have a huge impact. Higher selectivity = less rows traversed
>> =>> better performance. Also remember that index statistics are only
>> computed
>> for the first column in the index; statistics play a large part when the
>> optimizer does its job, so it's important to keep that in mind.
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> http://www.datamanipulation.net
>> --
>>
>> "nick" <nick@.discussions.microsoft.com> wrote in message
>> news:56387438-D942-40EB-AE98-26653AF2F0E4@.microsoft.com...
>> > I need to create an index with multple columns, for example, an index
>> > contains "State", "City" and "Street". Obviously the density of the
>> columns
>> > is State < City < Street. Is it necessary to follow a particular order
>> when
>> > create index for better performance? For example (State, City, Street)
>> > or
>> > (Street, City, State)?
>>|||Thanks for the explanation.
AMB
"Adam Machanic" wrote:
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:327FEF28-39BC-4B5C-991F-612139545D77@.microsoft.com...
> >
> > I see statistics for:
> >
> > - country
> > - country, city
> > - country, city, postalcode
> >
> > how does this apply to "index statistics are only computed for the first
> > column in the index"?
> I probably phrased that poorly. Should have been, "index statistics are
> based on the first column in the index." Which, I hope, serves to imply
> that no statistics will be computed on only the second column, or only the
> second and third columns, etc. The first column is the key.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
>|||Kalen,
Thanks a lot for the link. It helped me to understand the theme better.
Does Sql Server query optimizer use the density of the combination of
columns (in the case of multi-column statistics, or an index) in conjuntion
with the histogram to choose the most efficient plan?
Thanks in advance,
Alejandro Mesa
"Kalen Delaney" wrote:
> If you create the composite index described, SHOW_STATISTICS will show you
> density info for each subset of keys, but the density info is only the
> average number of duplicates across all values. Following the density info
> you should see the statistics, which is the histogram on number of
> occurences of specific values for the index key. That only applies to the
> leading column of the index. Please see this whitepaper for more info:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/statquery.asp?frame=true
>
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:327FEF28-39BC-4B5C-991F-612139545D77@.microsoft.com...
> > Adam,
> >
> > I am a little bit confused. If I create the following index:
> >
> > use northwind
> > go
> >
> > create nonclustered index ix_nc_employees_country_city_postalcode on
> > employees(country, city, postalcode)
> > go
> >
> > dbcc show_statistics (employees, ix_nc_employees_country_city_postalcode)
> > go
> >
> > I see statistics for:
> >
> > - country
> > - country, city
> > - country, city, postalcode
> >
> > how does this apply to "index statistics are only computed for the first
> > column in the index"?
> >
> > if I do:
> >
> > set showplan_text on
> > go
> >
> > select
> > *
> > from
> > dbo.employees
> > where
> > country = 'usa'
> > and city like 's%'
> > go
> >
> > set showplan_text on
> > go
> >
> > the stimated execution plan shows an index seek in
> > ix_nc_employees_country_city_postalcode.
> >
> >
> > Thanks in advance,
> >
> > Alejandro Mesa
> >
> > "Adam Machanic" wrote:
> >
> >> Yes, it can have a huge impact. Higher selectivity = less rows traversed
> >> => >> better performance. Also remember that index statistics are only
> >> computed
> >> for the first column in the index; statistics play a large part when the
> >> optimizer does its job, so it's important to keep that in mind.
> >>
> >>
> >> --
> >> Adam Machanic
> >> SQL Server MVP
> >> http://www.datamanipulation.net
> >> --
> >>
> >>
> >> "nick" <nick@.discussions.microsoft.com> wrote in message
> >> news:56387438-D942-40EB-AE98-26653AF2F0E4@.microsoft.com...
> >> > I need to create an index with multple columns, for example, an index
> >> > contains "State", "City" and "Street". Obviously the density of the
> >> columns
> >> > is State < City < Street. Is it necessary to follow a particular order
> >> when
> >> > create index for better performance? For example (State, City, Street)
> >> > or
> >> > (Street, City, State)?
> >>
> >>
> >>
>
>|||Nick,
It depends on the selectivity of each column. Try using one index by each
column, test and see if it helps. If it does not help, then start doing
combinations (composite keys help to have better selectivity but at the
expense of more load when updating the keys), using in the most left side of
the key, the column with best selectivity or if the selectivity are similar
(or good selectivity), use the one you will use alone in a query.
Take the time to read the link posted by Kalen and all the good tips from
http://www.sql-server-performance.com/
AMB
"nick" wrote:
> So to maxmize the performance (without think about the update penalty), we
> better create three index:
> (state, city, street)
> (City, Street)
> (Street)
> Or the following order is the same?
> (Street, City, State)
> (City, State)
> (State)
> will be used for all situation like
> where state='...' and street = '...' and city='...' (Order doesn't matter?)
> where street = ''
> where city='' and street=''
> but not "where sat
> "Alejandro Mesa" wrote:
> > Sorry Mike, but the order of the logical expressions in the where clause is
> > not important. SQL Server does not evaluate them in a specific order. What I
> > think is important is that the columns in the where clause follow this
> > pattern (the order of the logical expressions is not important):
> >
> > ...
> > where state ...
> >
> > -- or
> >
> > where state ... and city ...
> >
> > -- or
> >
> > where state ... and street ...
> >
> > -- or
> > ...
> > where state ... and city ... and street ...
> >
> > if you filter by (city, street) then sql server will not implement an index
> > seek in this index.
> >
> >
> > Regards,
> >
> > Alejandro Mesa
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> > > Hi
> > >
> > > Based on your where clause.
> > >
> > > If you have an index on State, City, Street and use a where clause City,
> > > State, Street, the index will not be used. In this case, SQL can not guess
> > > that state is of any use, and it then can't use the index for the sort.
> > >
> > > Maybe and Index on state, a separate one on city, and a separate one on
> > > street.
> > >
> > > A compound index is generally a bad idea as a data modification will result
> > > in a lot of index maintenance, slowing down the operation.
> > >
> > > Regards
> > > --
> > > Mike Epprecht, Microsoft SQL Server MVP
> > > Zurich, Switzerland
> > >
> > > IM: mike@.epprecht.net
> > >
> > > MVP Program: http://www.microsoft.com/mvp
> > >
> > > Blog: http://www.msmvps.com/epprecht/
> > >
> > > "nick" <nick@.discussions.microsoft.com> wrote in message
> > > news:56387438-D942-40EB-AE98-26653AF2F0E4@.microsoft.com...
> > > >I need to create an index with multple columns, for example, an index
> > > > contains "State", "City" and "Street". Obviously the density of the
> > > > columns
> > > > is State < City < Street. Is it necessary to follow a particular order
> > > > when
> > > > create index for better performance? For example (State, City, Street) or
> > > > (Street, City, State)?
> > >
> > >
> > >|||There may be cases where it uses both, but in general, if the optimizer has
a specific value to work with it uses the histogram, otherwise it uses the
density.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:9EA20A9A-CB5B-42E4-BABE-1FF889E2EDA1@.microsoft.com...
> Kalen,
> Thanks a lot for the link. It helped me to understand the theme better.
> Does Sql Server query optimizer use the density of the combination of
> columns (in the case of multi-column statistics, or an index) in
> conjuntion
> with the histogram to choose the most efficient plan?
>
> Thanks in advance,
> Alejandro Mesa
>
> "Kalen Delaney" wrote:
>> If you create the composite index described, SHOW_STATISTICS will show
>> you
>> density info for each subset of keys, but the density info is only the
>> average number of duplicates across all values. Following the density
>> info
>> you should see the statistics, which is the histogram on number of
>> occurences of specific values for the index key. That only applies to the
>> leading column of the index. Please see this whitepaper for more info:
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/statquery.asp?frame=true
>>
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
>> message
>> news:327FEF28-39BC-4B5C-991F-612139545D77@.microsoft.com...
>> > Adam,
>> >
>> > I am a little bit confused. If I create the following index:
>> >
>> > use northwind
>> > go
>> >
>> > create nonclustered index ix_nc_employees_country_city_postalcode on
>> > employees(country, city, postalcode)
>> > go
>> >
>> > dbcc show_statistics (employees,
>> > ix_nc_employees_country_city_postalcode)
>> > go
>> >
>> > I see statistics for:
>> >
>> > - country
>> > - country, city
>> > - country, city, postalcode
>> >
>> > how does this apply to "index statistics are only computed for the
>> > first
>> > column in the index"?
>> >
>> > if I do:
>> >
>> > set showplan_text on
>> > go
>> >
>> > select
>> > *
>> > from
>> > dbo.employees
>> > where
>> > country = 'usa'
>> > and city like 's%'
>> > go
>> >
>> > set showplan_text on
>> > go
>> >
>> > the stimated execution plan shows an index seek in
>> > ix_nc_employees_country_city_postalcode.
>> >
>> >
>> > Thanks in advance,
>> >
>> > Alejandro Mesa
>> >
>> > "Adam Machanic" wrote:
>> >
>> >> Yes, it can have a huge impact. Higher selectivity = less rows
>> >> traversed
>> >> =>> >> better performance. Also remember that index statistics are only
>> >> computed
>> >> for the first column in the index; statistics play a large part when
>> >> the
>> >> optimizer does its job, so it's important to keep that in mind.
>> >>
>> >>
>> >> --
>> >> Adam Machanic
>> >> SQL Server MVP
>> >> http://www.datamanipulation.net
>> >> --
>> >>
>> >>
>> >> "nick" <nick@.discussions.microsoft.com> wrote in message
>> >> news:56387438-D942-40EB-AE98-26653AF2F0E4@.microsoft.com...
>> >> > I need to create an index with multple columns, for example, an
>> >> > index
>> >> > contains "State", "City" and "Street". Obviously the density of the
>> >> columns
>> >> > is State < City < Street. Is it necessary to follow a particular
>> >> > order
>> >> when
>> >> > create index for better performance? For example (State, City,
>> >> > Street)
>> >> > or
>> >> > (Street, City, State)?
>> >>
>> >>
>> >>
>>|||Kalen,
Thanks again, I really appreciate your help.
Alejandro Mesa
"Kalen Delaney" wrote:
> There may be cases where it uses both, but in general, if the optimizer has
> a specific value to work with it uses the histogram, otherwise it uses the
> density.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:9EA20A9A-CB5B-42E4-BABE-1FF889E2EDA1@.microsoft.com...
> > Kalen,
> >
> > Thanks a lot for the link. It helped me to understand the theme better.
> >
> > Does Sql Server query optimizer use the density of the combination of
> > columns (in the case of multi-column statistics, or an index) in
> > conjuntion
> > with the histogram to choose the most efficient plan?
> >
> >
> > Thanks in advance,
> >
> > Alejandro Mesa
> >
> >
> > "Kalen Delaney" wrote:
> >
> >> If you create the composite index described, SHOW_STATISTICS will show
> >> you
> >> density info for each subset of keys, but the density info is only the
> >> average number of duplicates across all values. Following the density
> >> info
> >> you should see the statistics, which is the histogram on number of
> >> occurences of specific values for the index key. That only applies to the
> >> leading column of the index. Please see this whitepaper for more info:
> >>
> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/statquery.asp?frame=true
> >>
> >>
> >> --
> >> HTH
> >> --
> >> Kalen Delaney
> >> SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> >> message
> >> news:327FEF28-39BC-4B5C-991F-612139545D77@.microsoft.com...
> >> > Adam,
> >> >
> >> > I am a little bit confused. If I create the following index:
> >> >
> >> > use northwind
> >> > go
> >> >
> >> > create nonclustered index ix_nc_employees_country_city_postalcode on
> >> > employees(country, city, postalcode)
> >> > go
> >> >
> >> > dbcc show_statistics (employees,
> >> > ix_nc_employees_country_city_postalcode)
> >> > go
> >> >
> >> > I see statistics for:
> >> >
> >> > - country
> >> > - country, city
> >> > - country, city, postalcode
> >> >
> >> > how does this apply to "index statistics are only computed for the
> >> > first
> >> > column in the index"?
> >> >
> >> > if I do:
> >> >
> >> > set showplan_text on
> >> > go
> >> >
> >> > select
> >> > *
> >> > from
> >> > dbo.employees
> >> > where
> >> > country = 'usa'
> >> > and city like 's%'
> >> > go
> >> >
> >> > set showplan_text on
> >> > go
> >> >
> >> > the stimated execution plan shows an index seek in
> >> > ix_nc_employees_country_city_postalcode.
> >> >
> >> >
> >> > Thanks in advance,
> >> >
> >> > Alejandro Mesa
> >> >
> >> > "Adam Machanic" wrote:
> >> >
> >> >> Yes, it can have a huge impact. Higher selectivity = less rows
> >> >> traversed
> >> >> => >> >> better performance. Also remember that index statistics are only
> >> >> computed
> >> >> for the first column in the index; statistics play a large part when
> >> >> the
> >> >> optimizer does its job, so it's important to keep that in mind.
> >> >>
> >> >>
> >> >> --
> >> >> Adam Machanic
> >> >> SQL Server MVP
> >> >> http://www.datamanipulation.net
> >> >> --
> >> >>
> >> >>
> >> >> "nick" <nick@.discussions.microsoft.com> wrote in message
> >> >> news:56387438-D942-40EB-AE98-26653AF2F0E4@.microsoft.com...
> >> >> > I need to create an index with multple columns, for example, an
> >> >> > index
> >> >> > contains "State", "City" and "Street". Obviously the density of the
> >> >> columns
> >> >> > is State < City < Street. Is it necessary to follow a particular
> >> >> > order
> >> >> when
> >> >> > create index for better performance? For example (State, City,
> >> >> > Street)
> >> >> > or
> >> >> > (Street, City, State)?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

Friday, March 23, 2012

Index question

Index Question

Index problem and another question

Wednesday, March 7, 2012

Index Error

Index Error

Friday, February 24, 2012

Index column order?

Index column order?

Index column order?

index and key

Blog Archive

About Me