Multiple Same indexes same table

microsoft.public.sqlserver.programming

18 Aug 2005 5:54 PM

tshad

I was looking at Northwind and noticed that some of the tables have the same
index, but different names on the same table.

For example, on the Orders table you have the following script:

****************************************************************************************
CREATE TABLE [dbo].[Orders] (
[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmployeeID] [int] NULL ,
[OrderDate] [datetime] NULL ,
[RequiredDate] [datetime] NULL ,
[ShippedDate] [datetime] NULL ,
[ShipVia] [int] NULL ,
[Freight] [money] NULL ,
[ShipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipPostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ShipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE INDEX [CustomerID] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]
GO

CREATE INDEX [CustomersOrders] ON [dbo].[Orders]([CustomerID]) ON
[PRIMARY]
GO

CREATE INDEX [EmployeeID] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]
GO

CREATE INDEX [EmployeesOrders] ON [dbo].[Orders]([EmployeeID]) ON
[PRIMARY]
GO

CREATE INDEX [OrderDate] ON [dbo].[Orders]([OrderDate]) ON [PRIMARY]
GO

CREATE INDEX [ShippedDate] ON [dbo].[Orders]([ShippedDate]) ON [PRIMARY]
GO

CREATE INDEX [ShippersOrders] ON [dbo].[Orders]([ShipVia]) ON [PRIMARY]
GO

CREATE INDEX [ShipPostalCode] ON [dbo].[Orders]([ShipPostalCode]) ON
[PRIMARY]
GO

******************************************************************************

CustomerID and CustomerOrders are the same as are EmployeeID and
EmployeesOrders.

Why would you have this?

This would seem to create more overhead for the system.

Just curious.

Thanks,

Tom

18 Aug 2005 7:11 PM

Tibor Karaszi

>I was looking at Northwind and noticed that some of the tables have the same index, but different
>names on the same table.

<snip>

> Why would you have this?

Because you don't know what you are doing. ;-)
Honestly, there was some type of confusion in the build scripts for the Northwind database. You
don't create two same type of indexes on the same column.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quote

"tshad" <tscheider***@ftsolutions.com> wrote in message
news:eSmBW3BpFHA.3304@tk2msftngp13.phx.gbl...
>I was looking at Northwind and noticed that some of the tables have the same index, but different
>names on the same table.
>
> For example, on the Orders table you have the following script:
>
> ****************************************************************************************
> CREATE TABLE [dbo].[Orders] (
> [OrderID] [int] IDENTITY (1, 1) NOT NULL ,
> [CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [EmployeeID] [int] NULL ,
> [OrderDate] [datetime] NULL ,
> [RequiredDate] [datetime] NULL ,
> [ShippedDate] [datetime] NULL ,
> [ShipVia] [int] NULL ,
> [Freight] [money] NULL ,
> [ShipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ShipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ShipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ShipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ShipPostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ShipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> CREATE INDEX [CustomerID] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]
> GO
>
> CREATE INDEX [CustomersOrders] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]
> GO
>
> CREATE INDEX [EmployeeID] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]
> GO
>
> CREATE INDEX [EmployeesOrders] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]
> GO
>
> CREATE INDEX [OrderDate] ON [dbo].[Orders]([OrderDate]) ON [PRIMARY]
> GO
>
> CREATE INDEX [ShippedDate] ON [dbo].[Orders]([ShippedDate]) ON [PRIMARY]
> GO
>
> CREATE INDEX [ShippersOrders] ON [dbo].[Orders]([ShipVia]) ON [PRIMARY]
> GO
>
> CREATE INDEX [ShipPostalCode] ON [dbo].[Orders]([ShipPostalCode]) ON [PRIMARY]
> GO
>
> ******************************************************************************
>
> CustomerID and CustomerOrders are the same as are EmployeeID and EmployeesOrders.
>
> Why would you have this?
>
> This would seem to create more overhead for the system.
>
> Just curious.
>
> Thanks,
>
> Tom
>

19 Aug 2005 5:26 AM

tshad

"Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in
message news:%23q$RciCpFHA.496@TK2MSFTNGP10.phx.gbl...
> >I was looking at Northwind and noticed that some of the tables have the

same index, but different

> >names on the same table.
> <snip>
> > Why would you have this?
>
> Because you don't know what you are doing. ;-)
> Honestly, there was some type of confusion in the build scripts for the

Northwind database. You

> don't create two same type of indexes on the same column.

I guess I do know what I am doing, since I asked the question :).

I figured there was something wrong, but I wanted to make sure I wasn't
missing something.

Thanks,

Tom

>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
>
> "tshad" <tscheider***@ftsolutions.com> wrote in message
> news:eSmBW3BpFHA.3304@tk2msftngp13.phx.gbl...
> >I was looking at Northwind and noticed that some of the tables have the

same index, but different
Show quote

> >names on the same table.
> >
> > For example, on the Orders table you have the following script:
> >
> >
****************************************************************************
************
> > CREATE TABLE [dbo].[Orders] (
> > [OrderID] [int] IDENTITY (1, 1) NOT NULL ,
> > [CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [EmployeeID] [int] NULL ,
> > [OrderDate] [datetime] NULL ,
> > [RequiredDate] [datetime] NULL ,
> > [ShippedDate] [datetime] NULL ,
> > [ShipVia] [int] NULL ,
> > [Freight] [money] NULL ,
> > [ShipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ShipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> > [ShipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ShipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ShipPostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> > [ShipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ) ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [CustomerID] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [CustomersOrders] ON [dbo].[Orders]([CustomerID]) ON
[PRIMARY]
> > GO
> >
> > CREATE INDEX [EmployeeID] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [EmployeesOrders] ON [dbo].[Orders]([EmployeeID]) ON
[PRIMARY]
> > GO
> >
> > CREATE INDEX [OrderDate] ON [dbo].[Orders]([OrderDate]) ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [ShippedDate] ON [dbo].[Orders]([ShippedDate]) ON
[PRIMARY]
> > GO
> >
> > CREATE INDEX [ShippersOrders] ON [dbo].[Orders]([ShipVia]) ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [ShipPostalCode] ON [dbo].[Orders]([ShipPostalCode]) ON
[PRIMARY]
> > GO
> >
> >
****************************************************************************
**
> >
> > CustomerID and CustomerOrders are the same as are EmployeeID and
EmployeesOrders.
> >
> > Why would you have this?
> >
> > This would seem to create more overhead for the system.
> >
> > Just curious.
> >
> > Thanks,
> >
> > Tom
> >
>