How to use index - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

How to use index

Author

Topic

Dimis
Starting Member

11 Posts

Posted - 2011-05-16 : 18:21:13

How to use indexes in my example. I would like your opinion

IF OBJECT_ID('Customer') IS NOT NULL
IF OBJECT_ID('Loan') IS NOT NULL
IF OBJECT_ID('Copy') IS NOT NULL
IF OBJECT_ID('DVD') IS NOT NULL
IF OBJECT_ID('TopTenRentalLastWeek') IS NOT NULL
IF OBJECT_ID('TopTenRentalThisWeek') IS NOT NULL

DROP TABLE Customer


CREATE TABLE Customer
(Customerid CHAR(9) NOT NULL CHECK (Customerid>0),
Customername VARCHAR(15) NOT NULL,
Surname VARCHAR(15) NOT NULL,
Address VARCHAR(15) NULL,
Email CHAR(20) NULL,
Telno VARCHAR(12) NULL)
CREATE TABLE Loan 
(Loanid CHAR(9) NOT NULL CHECK (Loanid>0),
Customerid CHAR(9) NOT NULL,
DOBstart DATETIME NULL,
DOBend DATETIME NULL,
Copyid CHAR(9) NOT NULL,
FOREIGN KEY (Customerid) REFERENCES Customer(Customerid),
FOREIGN KEY (Copyid) REFERENCES Copy(Copyid),
UNIQUE (Loanid))
CREATE TABLE Copy  
(Copyid CHAR(9) NOT NULL CHECK (Copyid>0),
Quantity VARCHAR(12) NULL,
Dvdid CHAR(9) NOT NULL,
Price VARCHAR(12) NULL,
FOREIGN KEY (Dvdid) REFERENCES DVD(Dvdid),
UNIQUE (Copyid))
CREATE TABLE DVD   
(Dvdid CHAR(9) NOT NULL CHECK (Dvdid>0),
Dvdname VARCHAR(15) NOT NULL,
TopTenRentalLastno CHAR(10) NOT NULL,
TopTenRentalThisno CHAR(10) NOT NULL,
FOREIGN KEY (TopTenRentalLastno) REFERENCES TopTenRentalLastWeek(TopTenRentalLastno),
FOREIGN KEY (TopTenRentalThisno) REFERENCES TopTenRentalThisWeek(TopTenRentalThisno),
UNIQUE (Dvdid))
CREATE TABLE TopTenRentalLastWeek    
(TopTenRentalLastno CHAR(10) NOT NULL CHECK (TopTenRentalLastno>0),
Dvdid CHAR(9) NOT NULL,
UNIQUE (TopTenRentalLastno))
CREATE TABLE TopTenRentalThisWeek     
(TopTenRentalThisno CHAR(10) NOT NULL CHECK (TopTenRentalThisno>0),
Dvdid CHAR(9) NOT NULL,
UNIQUE (TopTenRentalThisno))

Dimi

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-16 : 18:25:36

What do you mean by how to use indexes? You haven't post anything that can use indexes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Dimis
Starting Member

11 Posts

Posted - 2011-05-16 : 18:33:44

I would like help to use indexes in code

IF OBJECT_ID('Customer') IS NOT NULL
IF OBJECT_ID('Loan') IS NOT NULL
IF OBJECT_ID('Copy') IS NOT NULL
IF OBJECT_ID('DVD') IS NOT NULL
IF OBJECT_ID('TopTenRentalLastWeek') IS NOT NULL
IF OBJECT_ID('TopTenRentalThisWeek') IS NOT NULL

DROP TABLE Customer


CREATE TABLE Customer
(Customerid CHAR(9) NOT NULL CHECK (Customerid>0),
Customername VARCHAR(15) NOT NULL,
Surname VARCHAR(15) NOT NULL,
Address VARCHAR(15) NULL,
Email CHAR(20) NULL,
Telno VARCHAR(12) NULL)
CREATE TABLE Loan 
(Loanid CHAR(9) NOT NULL CHECK (Loanid>0),
Customerid CHAR(9) NOT NULL,
DOBstart DATETIME NULL,
DOBend DATETIME NULL,
Copyid CHAR(9) NOT NULL,
FOREIGN KEY (Customerid) REFERENCES Customer(Customerid),
FOREIGN KEY (Copyid) REFERENCES Copy(Copyid),
UNIQUE (Loanid))
CREATE TABLE Copy  
(Copyid CHAR(9) NOT NULL CHECK (Copyid>0),
Quantity VARCHAR(12) NULL,
Dvdid CHAR(9) NOT NULL,
Price VARCHAR(12) NULL,
FOREIGN KEY (Dvdid) REFERENCES DVD(Dvdid),
UNIQUE (Copyid))
CREATE TABLE DVD   
(Dvdid CHAR(9) NOT NULL CHECK (Dvdid>0),
Dvdname VARCHAR(15) NOT NULL,
TopTenRentalLastno CHAR(10) NOT NULL,
TopTenRentalThisno CHAR(10) NOT NULL,
FOREIGN KEY (TopTenRentalLastno) REFERENCES TopTenRentalLastWeek(TopTenRentalLastno),
FOREIGN KEY (TopTenRentalThisno) REFERENCES TopTenRentalThisWeek(TopTenRentalThisno),
UNIQUE (Dvdid))
CREATE TABLE TopTenRentalLastWeek    
(TopTenRentalLastno CHAR(10) NOT NULL CHECK (TopTenRentalLastno>0),
Dvdid CHAR(9) NOT NULL,
UNIQUE (TopTenRentalLastno))
CREATE TABLE TopTenRentalThisWeek     
(TopTenRentalThisno CHAR(10) NOT NULL CHECK (TopTenRentalThisno>0),
Dvdid CHAR(9) NOT NULL,
UNIQUE (TopTenRentalThisno))



GO

 INSERT INTO Customer VALUES
('102345','Freda', 'Bloggs','Nikiforou-7','HND102345@tees.ac.uk', '01642-345678')
 INSERT INTO Customer VALUES
('103456','Greta', 'Davies','Kostou-5','HND103456@tees.ac.uk', '01642-456789')
 INSERT INTO Customer VALUES
('205112','Jayne', 'Orrells','Panagou-8','BSC205112@tees.ac.uk', '01642-567890')
 INSERT INTO Customer VALUES
('204113','Jack', 'Lee','Fousts-6','BSC204113@tees.ac.uk', '01642-334655')
 INSERT INTO Customer VALUES
('206114','Ralph', 'Harries','Jakou-3','BSC206114@tees.ac.uk', '01642-344509')
 INSERT INTO Customer VALUES
('207233','Sue', 'Peters','Patou-6','BSC207233@tees.ac.uk', '01642-945778')
 INSERT INTO Customer VALUES
('205112','Jayne', 'Orrells','Patsouka-5','BSC205112@tees.ac.uk', '01642-567899')
 INSERT INTO Customer VALUES
('204113','Jack', 'Lee','Pat-4','BSC204113@tees.ac.uk', '01642-334664')
 INSERT INTO Customer VALUES
('206114','Ralph', 'Harries','Papou-12','BSC206114@tees.ac.uk', '01642-344544')
 INSERT INTO Customer VALUES
('207233','Sue', 'Peters','Patatouka-6','BSC207233@tees.ac.uk', '01642-945712')
 INSERT INTO Loan VALUES
('1001','American_Black','102345','02/01/2011','02/04/2011','101')
 INSERT INTO Loan VALUES
('1002','103456','02/01/2011','02/04/2011','102')
 INSERT INTO Loan VALUES
('1003','205112','02/01/2011','02/04/2011','103')
 INSERT INTO Loan VALUES
('1004','204113','02/01/2011','02/05/2011','104')
 INSERT INTO Loan VALUES
('1005','206114','02/02/2011','02/06/2011','105')
 INSERT INTO Loan VALUES
('1006','207233','02/03/2011','02/08/2011','106')
 INSERT INTO Loan VALUES
('1007','205112','02/03/2011','02/05/2011','107')
 INSERT INTO Loan VALUES
('1008','204113','02/03/2011','02/06/2011','108')
 INSERT INTO Loan VALUES
('1009','206114','02/03/2011','02/06/2011','109')
 INSERT INTO Loan VALUES
('1010','207233','02/01/2011','02/06/2011','110')
INSERT INTO Copy VALUES
('101','30', '500','20')
 INSERT INTO Copy VALUES
('102','30', '501','25')
 INSERT INTO Copy VALUES
('103','30', '502','25')
 INSERT INTO Copy VALUES
('104','30', '503','25')
 INSERT INTO Copy VALUES
('105','30', '504','25')
 INSERT INTO Copy VALUES
('106','30', '505','25')
 INSERT INTO Copy VALUES
('107','40', '506','25')
 INSERT INTO Copy VALUES
('108','40', '507','25')
 INSERT INTO Copy VALUES
('109','40', '508','25')
 INSERT INTO Copy VALUES
('110','40', '509','25')
 INSERT INTO DVD VALUES
('500','American_Black','1')
 INSERT INTO DVD VALUES
('501','French_horn','3')
 INSERT INTO DVD VALUES
('502','Harmonica','4')
 INSERT INTO DVD VALUES
('503','Bobolink','10')
 INSERT INTO DVD VALUES
('504','Nightjar','9')
 INSERT INTO DVD VALUES
('505','King_Eider','6')
 INSERT INTO DVD VALUES
('506','Killdeer','8')
 INSERT INTO DVD VALUES
('507','Jews','7')
 INSERT INTO DVD VALUES
('508','Melodeon','5')
 INSERT INTO DVD VALUES
('509','Rock','2')
 INSERT INTO TopTenRentalLastWeek VALUES
('1','500')
 INSERT INTO TopTenRentalLastWeek VALUES
('2','501')
 INSERT INTO TopTenRentalLastWeek VALUES
('3','502')
 INSERT INTO TopTenRentalLastWeek VALUES
('4','503')
 INSERT INTO TopTenRentalLastWeek VALUES
('5','504')
 INSERT INTO TopTenRentalLastWeek VALUES
('6','505')
 INSERT INTO TopTenRentalLastWeek VALUES
('7','506')
 INSERT INTO TopTenRentalLastWeek VALUES
('8','507')
 INSERT INTO TopTenRentalLastWeek VALUES
('9','508')
 INSERT INTO TopTenRentalLastWeek VALUES
('10','509')
 INSERT INTO TopTenRentalThisWeek VALUES
('1','500')
 INSERT INTO TopTenRentalThisWeek VALUES
('2','501')
 INSERT INTO TopTenRentalThisWeek VALUES
('3','502')
 INSERT INTO TopTenRentalThisWeek VALUES
('4','503')
 INSERT INTO TopTenRentalThisWeek VALUES
('5','504')
 INSERT INTO TopTenRentalThisWeek VALUES
('6','505')
 INSERT INTO TopTenRentalThisWeek VALUES
('7','506')
 INSERT INTO TopTenRentalThisWeek VALUES
('8','507')
 INSERT INTO TopTenRentalThisWeek VALUES
('9','508')
 INSERT INTO TopTenRentalThisWeek VALUES
('10','509')

GO

Dimi

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-16 : 18:35:50

You haven't posted enough information for us to help. What code do you want to use indexes on?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Dimis
Starting Member

11 Posts

Posted - 2011-05-16 : 18:40:56

In the above code that create dynamic tables can I use indexes.Thanks.

Dimi

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-16 : 18:54:38

There are no dynamic tables in what you've posted. But yes, you can index tables.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Dimis
Starting Member

11 Posts

Posted - 2011-05-16 : 18:58:30

I would like to learn why I cannot create dynamic table with thw code.Thanks

Dimi

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-16 : 19:00:45

You aren't explaining yourself clearly. What makes your code dynamic?

In order for us to tell you what indexes you'll need on your tables, you'll have to post the queries that will be used against these tables. We can't just look at base tables and tell you what indexes they need. Only the queries that will go against the tables will help solve your problem.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-05-19 : 09:56:02

I agree with Tara it is hard to recommend indexes when we don't know what is slow or see the code. Usually adding indexes on foreign keys is a good practice since that is where the joins will likely occur.

Do you mean temp tables rather than dynamic - created inside a Stored Procedure for temporary purposes?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources