Table Design Question - Expecting ~1 Million Records

[ashurack]

I'm writing survey software and wanted the data to be vertical. What keys/indexes should I have so queries against the table run as quickly as possible? The fields "SurveyID" and "SurveyGUID" are used for reference. The field "SurveyField" is the name of the question (ex. "Q1", "Q2", etc...) and the field "SurveyData" is the answered value of the question.

Thanks in advance for any help!

USE [WebSurvey]
GO
 
/****** Object:  Table [dbo].[SurveyData]    Script Date: 03/09/2010 01:09:35 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[SurveyData](
    [RID] [int] IDENTITY(1,1) NOT NULL,
    [SurveyID] [int] NOT NULL,
    [SurveyGUID] [nvarchar](50) NOT NULL,
    [SessionID] [nvarchar](50) NOT NULL,
    [SurveyField] [nvarchar](50) NOT NULL,
    [SurveyData] [nvarchar](max) NULL,
    [DateTime] [datetime] NOT NULL,
    [isQueryString] [bit] NOT NULL,
 CONSTRAINT [PK_SurveyData] PRIMARY KEY CLUSTERED 
(
    [RID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
ALTER TABLE [dbo].[SurveyData] ADD  CONSTRAINT [DF_SurveyData_DateTime]  DEFAULT (getdate()) FOR [DateTime]
GO
 
ALTER TABLE [dbo].[SurveyData] ADD  CONSTRAINT [DF_SurveyData_isQueryString]  DEFAULT ((0)) FOR [isQueryString]
GO

[ck9663]

It depends on a lot of things including how you want the result of your query should look like. Aggregated? Detailed? etc.

Good Luck!!!

~~ CK