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 "SurveyFiel d" 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