Unique colm indexes and Primary Keys

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

New to SQL Server Programming

Unique colm indexes and Primary Keys

Author

Topic

Meow
Starting Member

8 Posts

Posted - 2006-01-25 : 13:05:43

I have a deal table, each of these investments must be unique. I created a int pk : idDeal. Does that make sense or should i just use the deal colm being it has a unique constraint,
Reguarding indexes, should i make the auto # colm my pk and make that the clustered index? and put another index on the Deal Colmn? Any suggestions welcomed

Thank you

X002548
Not Just a Number

15586 Posts

Posted - 2006-01-25 : 14:12:43

Maybe you could post some more info....read the link in my sig

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Meow
Starting Member

8 Posts

Posted - 2006-01-25 : 14:38:19

my question in more theory, if i have a Unique colm Named Deal would i benifit from adding an auto increment colm and making that the pk? if so the auto colm would then be clustered index, many queries on this table will be looking for the Deal name in the where clause, that being the case should i add an index on it?

I dont have any date in the table yet so here is the scrpt for the table and an example of a select query

Thanks for the info on the posting!

CREATE TABLE [dbo].[tblDeal] (
[idDeal] [int] NOT NULL ,
[DateEntered] [smalldatetime] NOT NULL ,
[Deal] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Code] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fund] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Alias] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comments] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Inactive] [bit] NOT NULL ,
[Grp] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Category] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Removed] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DropDown] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblDeal] WITH NOCHECK ADD
CONSTRAINT [PK_tblDeal] PRIMARY KEY CLUSTERED
(
[idDeal]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblDeal] ADD
CONSTRAINT [IX_tblDealUniqueDeal] UNIQUE NONCLUSTERED
(
[idDeal]
) ON [PRIMARY]
GO

SELECT dbo.tblMaster.CheckDate, dbo.tblMaster.CkNumber, [APOLLOLP\kerr].tblCompanies.Company, dbo.tblVendor.VendorName,
dbo.tblDetail.InvDate, dbo.tblDetail.InvAmount, dbo.tblEmployee.Name, dbo.tblDeal.Deal
FROM dbo.tblDeal INNER JOIN
dbo.tblDetail ON dbo.tblDeal.idDeal = dbo.tblDetail.idDeal INNER JOIN
dbo.tblMaster ON dbo.tblDetail.idMaster = dbo.tblMaster.idMaster INNER JOIN
dbo.tblVendor ON dbo.tblDetail.idVendor = dbo.tblVendor.idVendor AND dbo.tblMaster.idVendor = dbo.tblVendor.idVendor INNER JOIN
dbo.tblEmployee ON dbo.tblDetail.idEmployee = dbo.tblEmployee.idEmployee INNER JOIN
[APOLLOLP\kerr].tblCompanies ON dbo.tblMaster.idCompany = [APOLLOLP\kerr].tblCompanies.idCompany

X002548
Not Just a Number

15586 Posts

Posted - 2006-01-25 : 15:02:06

The Index is not required. Creating a PK will create a Unique index for you. And based on this query, the PK should be fine. If however you need to access other data based on different criteria, I'd think about adding indexes for them.


    SELECT m.CheckDate
	 , m.CkNumber
	 , c.Company
	 , v.VendorName
	 , dt.InvDate
	 , dt.InvAmount
	 , e.Name
	 , d.Deal
      FROM dbo.tblDeal     			d
INNER JOIN dbo.tblDetail  			dt
	ON d.idDeal 	    = dt.idDeal 
INNER JOIN dbo.tblMaster   			m
	ON dt.idMaster      = m.idMaster 
INNER JOIN dbo.tblVendor   			v
	ON dt.idVendor      = v.idVendor 
       AND m.idVendor       = v.idVendor 
INNER JOIN dbo.tblEmployee 			e
	ON dt.idEmployee    = e.idEmployee 
INNER JOIN [APOLLOLP\kerr].tblCompanies		c
	ON m.idCompany      = c.idCompany

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Meow
Starting Member

8 Posts

Posted - 2006-01-25 : 16:17:16

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources