SEO

I need to develop an internal messaging sub-system that is similar to
a web mail application but without SMTP support (e.g message routes
are confined to the webapp domain). The requirements are rather
simple: Each user (e.g mailbox) can view incoming messages and his
outgoing messages. Message quota is defined as the sum of all incoming
and outgoing messages per user
and tracked in the users' row (Users table – log_TotalMessages). The
quota is enforced by the business logic layer and not by the DB.

I am considering the following data model for the storage component,
and would appreciate community feedback:


Table layout for incoming and outgoing messages
************************************************

CREATE TABLE [dbo].[Messages] (
[MessageID] [int] IDENTITY (1, 1) NOT NULL , // The messageID
[RecipientID] [int] NOT NULL , // The userid ('Users'
Table)
[SenderID] [int] NOT NULL , // The userid ('Users'
Table)
[GroupID] [uniqueidentifier] NULL , // Only assigned if the
user "replyed" to an incoming message

[SubmitDate] [smalldatetime] NOT NULL , // the date of the
message

[DeleteBySender] [bit] NOT NULL , // Since I want to maintain only
one copy of each message I mark a message "to be deleted" and delete
only if both are true.

[DeleteByRecipient] [bit] NOT NULL ,

[SeenByRecipient] [bit] NOT NULL , // Used to "highlight" unread
messages

[Subject] [tinyint] NOT NULL , // Subject is derived from a fixed
list

[MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
) ON [PRIMARY]


CREATE INDEX [Messages_RecipientID_IDX] ON
[dbo].[Messages]([RecipientID]) ON [PRIMARY]
CREATE INDEX [Messages_SenderID_IDX] ON [dbo].[Messages]([SenderID])
ON [PRIMARY]




/* Send Message */


CREATE PROCEDURE SendMessage (

@IN_RecipientID int,
@IN_SenderID int,
@IN_GroupID uniqueidentifier,
@IN_Subject tinyint,
@IN_MessageText varchar(2000),


@OUT_ERRCODE tinyint OUTPUT
)

AS
BEGIN TRANSACTION SendMessageTrans

INSERT INTO Messages

(RecipientID,
SenderID,
GroupID,
SubmitDate,
Subject,
MessageText)

VALUES (@IN_RecipientID,
@IN_SenderID,
@IN_GroupID,
GETDate(),
@IN_Subject,
@IN_MessageText)


UPDATE Users

SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
WHERE usr_AccountNo = @IN_RecipientID

UPDATE Users

SET log_TotalMessages = log_TotalMessages + 1
WHERE usr_AccountNo = @IN_SenderID



SAVE TRANSACTION SendMessageTrans

SET @OUT_ERRCODE = @@error

IF (@@error <> 0)

BEGIN
ROLLBACK TRANSACTION SendMessageTrans
END

ELSE

BEGIN
COMMIT TRANSACTION SendMessageTrans
END




/* ReadMessage */

CREATE PROCEDURE ReadMessage (

@IN_MessageID int,
@IN_RecipientID int,

@OUT_ERRCODE tinyint OUTPUT
)

AS

BEGIN TRANSACTION ReadMessageTrans

SELECT MessageText FROM Messages WHERE MessageID = @IN_MessageID

UPDATE Messages SET SeenByRecipient = 1 WHERE MessageID =
@IN_MessageID

UPDATE Users SET log_NumberOfNewMessages =
log_NumberOfNewMessages - 1 WHERE usr_AccountNo = @IN_RecipientID

SAVE TRANSACTION ReadMessageTrans

SET @OUT_ERRCODE = @@error

IF (@@error <> 0)
BEGIN
ROLLBACK TRANSACTION ReadMessageTrans
END

ELSE

BEGIN
COMMIT TRANSACTION ReadMessageTrans
END





/* Delete Message */



CREATE PROCEDURE DeleteMessage (

@IN_MessageID int,
@IN_DeleteIncomingMessage bit,
@IN_DeleteOutgoingMessage bit,

@OUT_ERRCODE tinyint OUTPUT
)

AS
BEGIN TRANSACTION DeleteMessageTrans

DECLARE @Recipient int
DECLARE @Sender int

SET @Recipient = (SELECT RecipientID FROM Messages WHERE MessageID =
@IN_MessageID)
SET @Sender = (SELECT SenderID FROM Messages WHERE MessageID =
@IN_MessageID)


IF (@IN_DeleteIncomingMessage = 1)

BEGIN

IF((SELECT DeleteBySender FROM Messages WHERE MessageID =
@IN_MessageID) = 1)

BEGIN
DELETE FROM Messages WHERE MessageID = @IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @Recipient
END

ELSE

BEGIN

UPDATE Messages SET DeleteByRecipient = 1 WHERE MessageID =
@IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @Recipient
END
END


IF (@IN_DeleteOutgoingMessage = 1)

BEGIN

IF((SELECT DeleteByRecipient FROM Messages WHERE MessageID =
@IN_MessageID) = 1)

BEGIN
DELETE FROM Messages WHERE MessageID = @IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @Sender
END

ELSE

BEGIN
UPDATE Messages SET DeleteBySender = 1 WHERE MessageID =
@IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @Sender
END
END


SAVE TRANSACTION DeleteMessageTrans

SET @OUT_ERRCODE = @@error
IF (@@error <> 0)
BEGIN
ROLLBACK TRANSACTION DeleteMessageTrans
END
ELSE
BEGIN
COMMIT TRANSACTION DeleteMessageTrans
END





/* ListIncomingMessages */


CREATE PROCEDURE ListIncomingMessages (

@IN_RecipientID int

)

AS

SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
= @IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC



/* ListOutgoingMessages */


CREATE PROCEDURE ListOutgoingMessages (

@IN_SenderID int

)

AS

SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
= @IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC


Thanks in advance!

-Itai

Itai,shalom
What is a primary key of the table? So what are you actually asking?
Are you concerned about a perfomance of the stored procedures? Do they give
you a wrong output?




"Itai" <itaitai2003@yahoo.com> wrote in message
news:429f6e7d.0410030033.7573337c@posting.google.c om...
> I need to develop an internal messaging sub-system that is similar to
> a web mail application but without SMTP support (e.g message routes
> are confined to the webapp domain). The requirements are rather
> simple: Each user (e.g mailbox) can view incoming messages and his
> outgoing messages. Message quota is defined as the sum of all incoming
> and outgoing messages per user
> and tracked in the users' row (Users table - log_TotalMessages). The
> quota is enforced by the business logic layer and not by the DB.
>
> I am considering the following data model for the storage component,
> and would appreciate community feedback:
>
>
> Table layout for incoming and outgoing messages
> ************************************************
>
> CREATE TABLE [dbo].[Messages] (
> [MessageID] [int] IDENTITY (1, 1) NOT NULL , // The messageID
> [RecipientID] [int] NOT NULL , // The userid ('Users'
> Table)
> [SenderID] [int] NOT NULL , // The userid ('Users'
> Table)
> [GroupID] [uniqueidentifier] NULL , // Only assigned if the
> user "replyed" to an incoming message
>
> [SubmitDate] [smalldatetime] NOT NULL , // the date of the
> message
>
> [DeleteBySender] [bit] NOT NULL , // Since I want to maintain only
> one copy of each message I mark a message "to be deleted" and delete
> only if both are true.
>
> [DeleteByRecipient] [bit] NOT NULL ,
>
> [SeenByRecipient] [bit] NOT NULL , // Used to "highlight" unread
> messages
>
> [Subject] [tinyint] NOT NULL , // Subject is derived from a fixed
> list
>
> [MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL
> ) ON [PRIMARY]
>
>
> CREATE INDEX [Messages_RecipientID_IDX] ON
> [dbo].[Messages]([RecipientID]) ON [PRIMARY]
> CREATE INDEX [Messages_SenderID_IDX] ON [dbo].[Messages]([SenderID])
> ON [PRIMARY]
>
>
>
>
> /* Send Message */
>
>
> CREATE PROCEDURE SendMessage (
>
> @IN_RecipientID int,
> @IN_SenderID int,
> @IN_GroupID uniqueidentifier,
> @IN_Subject tinyint,
> @IN_MessageText varchar(2000),
>
>
> @OUT_ERRCODE tinyint OUTPUT
> )
>
> AS
> BEGIN TRANSACTION SendMessageTrans
>
> INSERT INTO Messages
>
> (RecipientID,
> SenderID,
> GroupID,
> SubmitDate,
> Subject,
> MessageText)
>
> VALUES (@IN_RecipientID,
> @IN_SenderID,
> @IN_GroupID,
> GETDate(),
> @IN_Subject,
> @IN_MessageText)
>
>
> UPDATE Users
>
> SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
> WHERE usr_AccountNo = @IN_RecipientID
>
> UPDATE Users
>
> SET log_TotalMessages = log_TotalMessages + 1
> WHERE usr_AccountNo = @IN_SenderID
>
>
>
> SAVE TRANSACTION SendMessageTrans
>
> SET @OUT_ERRCODE = @@error
>
> IF (@@error <> 0)
>
> BEGIN
> ROLLBACK TRANSACTION SendMessageTrans
> END
>
> ELSE
>
> BEGIN
> COMMIT TRANSACTION SendMessageTrans
> END
>
>
>
>
> /* ReadMessage */
>
> CREATE PROCEDURE ReadMessage (
>
> @IN_MessageID int,
> @IN_RecipientID int,
>
> @OUT_ERRCODE tinyint OUTPUT
> )
>
> AS
>
> BEGIN TRANSACTION ReadMessageTrans
>
> SELECT MessageText FROM Messages WHERE MessageID = @IN_MessageID
>
> UPDATE Messages SET SeenByRecipient = 1 WHERE MessageID =
> @IN_MessageID
>
> UPDATE Users SET log_NumberOfNewMessages =
> log_NumberOfNewMessages - 1 WHERE usr_AccountNo = @IN_RecipientID
>
> SAVE TRANSACTION ReadMessageTrans
>
> SET @OUT_ERRCODE = @@error
>
> IF (@@error <> 0)
> BEGIN
> ROLLBACK TRANSACTION ReadMessageTrans
> END
>
> ELSE
>
> BEGIN
> COMMIT TRANSACTION ReadMessageTrans
> END
>
>
>
>
>
> /* Delete Message */
>
>
>
> CREATE PROCEDURE DeleteMessage (
>
> @IN_MessageID int,
> @IN_DeleteIncomingMessage bit,
> @IN_DeleteOutgoingMessage bit,
>
> @OUT_ERRCODE tinyint OUTPUT
> )
>
> AS
> BEGIN TRANSACTION DeleteMessageTrans
>
> DECLARE @Recipient int
> DECLARE @Sender int
>
> SET @Recipient = (SELECT RecipientID FROM Messages WHERE MessageID =
> @IN_MessageID)
> SET @Sender = (SELECT SenderID FROM Messages WHERE MessageID =
> @IN_MessageID)
>
>
> IF (@IN_DeleteIncomingMessage = 1)
>
> BEGIN
>
> IF((SELECT DeleteBySender FROM Messages WHERE MessageID =
> @IN_MessageID) = 1)
>
> BEGIN
> DELETE FROM Messages WHERE MessageID = @IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @Recipient
> END
>
> ELSE
>
> BEGIN
>
> UPDATE Messages SET DeleteByRecipient = 1 WHERE MessageID =
> @IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @Recipient
> END
> END
>
>
> IF (@IN_DeleteOutgoingMessage = 1)
>
> BEGIN
>
> IF((SELECT DeleteByRecipient FROM Messages WHERE MessageID =
> @IN_MessageID) = 1)
>
> BEGIN
> DELETE FROM Messages WHERE MessageID = @IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @Sender
> END
>
> ELSE
>
> BEGIN
> UPDATE Messages SET DeleteBySender = 1 WHERE MessageID =
> @IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @Sender
> END
> END
>
>
> SAVE TRANSACTION DeleteMessageTrans
>
> SET @OUT_ERRCODE = @@error
> IF (@@error <> 0)
> BEGIN
> ROLLBACK TRANSACTION DeleteMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION DeleteMessageTrans
> END
>
>
>
>
>
> /* ListIncomingMessages */
>
>
> CREATE PROCEDURE ListIncomingMessages (
>
> @IN_RecipientID int
>
> )
>
> AS
>
> SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
> = @IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC
>
>
>
> /* ListOutgoingMessages */
>
>
> CREATE PROCEDURE ListOutgoingMessages (
>
> @IN_SenderID int
>
> )
>
> AS
>
> SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
> = @IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC
>
>
> Thanks in advance!
>
> -Itai

Hi

I am not sure what you are requiring people to do regarding your post, you
are the only person that can do the analysis of what is required, if you
have captured them correctly then you will know what to store in the
database. That said, there does not seem to be any referential integrity
built into the DDL, FKs and PKs should be defined. There is probably a
natural key of RecipientID, SenderID, and SubmitDate so a covering unique
index may be useful, and a INT may not be sufficient for you messageid.
Whether just storing bits to indicated the actions or whether a date would
be better would depend on whatever auditing requirements you require.

In your stored procedures you should implement better error handling, any
statement may fail and you are only checking the result from a few. From
Books online "Because @@ERROR is cleared and reset on each statement
executed, check it immediately following the statement validated, or save it
to a local variable that can be checked later." you may not even be checking
what you think!.

John

"Itai" <itaitai2003@yahoo.com> wrote in message
news:429f6e7d.0410030033.7573337c@posting.google.c om...
> I need to develop an internal messaging sub-system that is similar to
> a web mail application but without SMTP support (e.g message routes
> are confined to the webapp domain). The requirements are rather
> simple: Each user (e.g mailbox) can view incoming messages and his
> outgoing messages. Message quota is defined as the sum of all incoming
> and outgoing messages per user
> and tracked in the users' row (Users table - log_TotalMessages). The
> quota is enforced by the business logic layer and not by the DB.
>
> I am considering the following data model for the storage component,
> and would appreciate community feedback:
>
>
> Table layout for incoming and outgoing messages
> ************************************************
>
> CREATE TABLE [dbo].[Messages] (
> [MessageID] [int] IDENTITY (1, 1) NOT NULL , // The messageID
> [RecipientID] [int] NOT NULL , // The userid ('Users'
> Table)
> [SenderID] [int] NOT NULL , // The userid ('Users'
> Table)
> [GroupID] [uniqueidentifier] NULL , // Only assigned if the
> user "replyed" to an incoming message
>
> [SubmitDate] [smalldatetime] NOT NULL , // the date of the
> message
>
> [DeleteBySender] [bit] NOT NULL , // Since I want to maintain only
> one copy of each message I mark a message "to be deleted" and delete
> only if both are true.
>
> [DeleteByRecipient] [bit] NOT NULL ,
>
> [SeenByRecipient] [bit] NOT NULL , // Used to "highlight" unread
> messages
>
> [Subject] [tinyint] NOT NULL , // Subject is derived from a fixed
> list
>
> [MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL
> ) ON [PRIMARY]
>
>
> CREATE INDEX [Messages_RecipientID_IDX] ON
> [dbo].[Messages]([RecipientID]) ON [PRIMARY]
> CREATE INDEX [Messages_SenderID_IDX] ON [dbo].[Messages]([SenderID])
> ON [PRIMARY]
>
>
>
>
> /* Send Message */
>
>
> CREATE PROCEDURE SendMessage (
>
> @IN_RecipientID int,
> @IN_SenderID int,
> @IN_GroupID uniqueidentifier,
> @IN_Subject tinyint,
> @IN_MessageText varchar(2000),
>
>
> @OUT_ERRCODE tinyint OUTPUT
> )
>
> AS
> BEGIN TRANSACTION SendMessageTrans
>
> INSERT INTO Messages
>
> (RecipientID,
> SenderID,
> GroupID,
> SubmitDate,
> Subject,
> MessageText)
>
> VALUES (@IN_RecipientID,
> @IN_SenderID,
> @IN_GroupID,
> GETDate(),
> @IN_Subject,
> @IN_MessageText)
>
>
> UPDATE Users
>
> SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
> WHERE usr_AccountNo = @IN_RecipientID
>
> UPDATE Users
>
> SET log_TotalMessages = log_TotalMessages + 1
> WHERE usr_AccountNo = @IN_SenderID
>
>
>
> SAVE TRANSACTION SendMessageTrans
>
> SET @OUT_ERRCODE = @@error
>
> IF (@@error <> 0)
>
> BEGIN
> ROLLBACK TRANSACTION SendMessageTrans
> END
>
> ELSE
>
> BEGIN
> COMMIT TRANSACTION SendMessageTrans
> END
>
>
>
>
> /* ReadMessage */
>
> CREATE PROCEDURE ReadMessage (
>
> @IN_MessageID int,
> @IN_RecipientID int,
>
> @OUT_ERRCODE tinyint OUTPUT
> )
>
> AS
>
> BEGIN TRANSACTION ReadMessageTrans
>
> SELECT MessageText FROM Messages WHERE MessageID = @IN_MessageID
>
> UPDATE Messages SET SeenByRecipient = 1 WHERE MessageID =
> @IN_MessageID
>
> UPDATE Users SET log_NumberOfNewMessages =
> log_NumberOfNewMessages - 1 WHERE usr_AccountNo = @IN_RecipientID
>
> SAVE TRANSACTION ReadMessageTrans
>
> SET @OUT_ERRCODE = @@error
>
> IF (@@error <> 0)
> BEGIN
> ROLLBACK TRANSACTION ReadMessageTrans
> END
>
> ELSE
>
> BEGIN
> COMMIT TRANSACTION ReadMessageTrans
> END
>
>
>
>
>
> /* Delete Message */
>
>
>
> CREATE PROCEDURE DeleteMessage (
>
> @IN_MessageID int,
> @IN_DeleteIncomingMessage bit,
> @IN_DeleteOutgoingMessage bit,
>
> @OUT_ERRCODE tinyint OUTPUT
> )
>
> AS
> BEGIN TRANSACTION DeleteMessageTrans
>
> DECLARE @Recipient int
> DECLARE @Sender int
>
> SET @Recipient = (SELECT RecipientID FROM Messages WHERE MessageID =
> @IN_MessageID)
> SET @Sender = (SELECT SenderID FROM Messages WHERE MessageID =
> @IN_MessageID)
>
>
> IF (@IN_DeleteIncomingMessage = 1)
>
> BEGIN
>
> IF((SELECT DeleteBySender FROM Messages WHERE MessageID =
> @IN_MessageID) = 1)
>
> BEGIN
> DELETE FROM Messages WHERE MessageID = @IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @Recipient
> END
>
> ELSE
>
> BEGIN
>
> UPDATE Messages SET DeleteByRecipient = 1 WHERE MessageID =
> @IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @Recipient
> END
> END
>
>
> IF (@IN_DeleteOutgoingMessage = 1)
>
> BEGIN
>
> IF((SELECT DeleteByRecipient FROM Messages WHERE MessageID =
> @IN_MessageID) = 1)
>
> BEGIN
> DELETE FROM Messages WHERE MessageID = @IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @Sender
> END
>
> ELSE
>
> BEGIN
> UPDATE Messages SET DeleteBySender = 1 WHERE MessageID =
> @IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @Sender
> END
> END
>
>
> SAVE TRANSACTION DeleteMessageTrans
>
> SET @OUT_ERRCODE = @@error
> IF (@@error <> 0)
> BEGIN
> ROLLBACK TRANSACTION DeleteMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION DeleteMessageTrans
> END
>
>
>
>
>
> /* ListIncomingMessages */
>
>
> CREATE PROCEDURE ListIncomingMessages (
>
> @IN_RecipientID int
>
> )
>
> AS
>
> SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
> = @IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC
>
>
>
> /* ListOutgoingMessages */
>
>
> CREATE PROCEDURE ListOutgoingMessages (
>
> @IN_SenderID int
>
> )
>
> AS
>
> SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
> = @IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC
>
>
> Thanks in advance!
>
> -Itai

Uri, John and especially David! Thanks for the code review; I am now
one step further

"John Bell" <jbellnewsposts@hotmail.com> wrote in message news:<41605341$0$21630$afc38c87@news.easynet.co.uk >...

> There is probably a
> natural key of RecipientID, SenderID, and SubmitDate so a covering unique
> index may be useful, and a INT may not be sufficient for you messageid.

"Covering index" is something I lack to understand ... How is it
stored in a
b-tree structure, does the whole string composed of the diffrent
columns get saved as one key in each node? How does SQL server
(depending on the query's' where clause of course) 'extracts' the
right column and scan for its appropriate value within the index? What
are the questions to ask when considering a covering index as a design
requirement.

Regarding the INT data type for messageID, what would you suggest?
Messages will often be deleted but the counter value will always
progress...

I thought about using a uid, but they are not suitable for a Clustered
Index since they are not guaranteed to be chosen in incremental
order...

Thanks again

-Itai


BTW does anyone know how to dump a table to a text file using command
line with arguments?

Hi

I have not seen Davids reply!!
itaitai2003@yahoo.com (Itai) wrote in message news:<429f6e7d.0410050519.28073d82@posting.google. com>...
> Uri, John and especially David! Thanks for the code review; I am now
> one step further
>
> "John Bell" <jbellnewsposts@hotmail.com> wrote in message news:<41605341$0$21630$afc38c87@news.easynet.co.uk >...
>
> > There is probably a
> > natural key of RecipientID, SenderID, and SubmitDate so a covering unique
> > index may be useful, and a INT may not be sufficient for you messageid.
>
> "Covering index" is something I lack to understand ... How is it
> stored in a
> b-tree structure, does the whole string composed of the diffrent
> columns get saved as one key in each node? How does SQL server
> (depending on the query's' where clause of course) 'extracts' the
> right column and scan for its appropriate value within the index? What
> are the questions to ask when considering a covering index as a design
> requirement.
The uniqueness of the index would make sure no duplicates values of
the three combined columns are inserted into your database, this may
be important for maintaining integrity. I would expect that when you
are looking for a message you will be mainly searching on a
combination of these three columns. The query processor will decide on
whether an index is useful using various algorithms.


>
> Regarding the INT data type for messageID, what would you suggest?
> Messages will often be deleted but the counter value will always
> progress...
You would have to determine the number of records and what growth you
are expecting, but when you could be mailing a significant number of
recipients then the maximum number offered by an INT datatype will
probably get used up quickly, therefore BIGINT may be better.
>
> I thought about using a uid, but they are not suitable for a Clustered
> Index since they are not guaranteed to be chosen in incremental
> order...
>
> Thanks again
>
> -Itai
>
>
> BTW does anyone know how to dump a table to a text file using command
> line with arguments?

BCP, DTS or even osql will do this, look at books online for more
information on these.

John