How should I ensure the efficient operation of the table

Question

I am going to create a new table that will have 200K rows. I would like to make sure the queries to this table are efficient.

in the past, I always gave the string a unique identifier under the assumption that this would result in an index:

CREATE TABLE [dbo].[Equipment](
    [EquipID] [nchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [EquipDescription] [nchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Category] [nchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [id] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

enough ?, should I set the primary key.

If anyone has suggestions, let them fly.

T-SQL, SQL2000,

Answer 1

, , . , , , , , .

, , .

article, , , .

, , Database Systems , . , .

Answer 2

. @deinst, , . - .

[id], , . , , ( ), .

, ( , @deinst).

[EquipID] . -. . [EquipID] , , NULL NULL.

Answer 3

- / , . , . . , - .

1) , . .

2) , . JOIN, WHERE ORDER BY, . , , . .

Answer 4

, : " " "?" EquipID, ? , null.

-, " " "?" EquipID? . EquipID Category? , . , , , , , . , , - . , . , , , ( EquipID).

, : " ?" , , "SELECT EquipID FROM Equipment WHERE Category = 3". , .

, , , , . , .

- ( , ):

CREATE TABLE [dbo].[Equipment]( 
  [EquipID] [nchar](20) NOT NULL
  ,[EquipDescription] [nchar](100) NOT NULL
  ,[CategoryID] [bigint] NOT NULL
  ,CONSTRAINT [PK_Equipment] PRIMARY KEY CLUSTERED (
    [EquipID] ASC
  )
) ON [PRIMARY] 
GO

CREATE TABLE [dbo].[Categories]( 
  [CategoryID] [bigint] IDENTITY(1,1) NOT NULL 
  ,[CategoryName] [nchar](100) NOT NULL
  ,CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED (
    [CategoryID] ASC
  )
) ON [PRIMARY] 
GO

CREATE NONCLUSTERED INDEX [IDX_Equipment_Category] ON [dbo].[Equipment] (
  [CategoryID] ASC
) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [IDX_Categories_CategoryName] ON [dbo].[Categories] (
  [CategoryName] ASC
) ON [PRIMARY]

ALTER TABLE [dbo].[Equipment]  WITH CHECK ADD  CONSTRAINT [FK_Equipment_Categories] 
FOREIGN KEY([CategoryID]) REFERENCES [dbo].[Categories] ([CategoryID])
GO

Answer 5

, .

• Are you going to always refer to equipment on id? Or through EquipID?
• Other tables store links (foreign keys) in the Equipment, storing the value idor EquipID?
• Are you going to aggregate Category?
• Is there a hierarchy Category?
• Does it change EquipIDfor equipment?