zanima me moram li staviti indexe na polja koja mi sluze kao foreign keys?
Dakle imam npr tabelu Knjige i na njoj polja id (koje je index), naziv i polje kojavrsta (to polje mi sluzi kao foreign key za drugu tabelu)
E sad trebam li i na to polje "kojavrsta" staviti index (u tom polju ce biti samo vrijednosti npr. 1 i 0)?
I drugo pitanje. Ako trebam staviti indexe na ta polja koja su foreign (ako ih imam vise), dali je bolje napraviti ovako:
Code:
CREATE NONCLUSTERED INDEX [Knjige_idx3] ON [dbo].[Knjige]
([KojaVrsta])
WITH (
PAD_INDEX = OFF,
DROP_EXISTING = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [Knjige_idx4] ON [dbo].[Knjige]
([KojiAutor])
WITH (
PAD_INDEX = OFF,
DROP_EXISTING = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [Knjige_idx3] ON [dbo].[Knjige]
([KojaVrsta])
WITH (
PAD_INDEX = OFF,
DROP_EXISTING = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [Knjige_idx4] ON [dbo].[Knjige]
([KojiAutor])
WITH (
PAD_INDEX = OFF,
DROP_EXISTING = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
ili je bolje ovako - da ih zajedno stavim u "jedan" index:
Code:
CREATE NONCLUSTERED INDEX [Knjige_idx11] ON [dbo].[Knjige]
([KojaVrsta], [KojiAutor])
WITH (
PAD_INDEX = OFF,
DROP_EXISTING = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [Knjige_idx11] ON [dbo].[Knjige]
([KojaVrsta], [KojiAutor])
WITH (
PAD_INDEX = OFF,
DROP_EXISTING = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
Uf nadam se da ce netko skuziti sta je pjesnik mislio reci :)