CREATE TABLE [SMSDlv_Inbox]( [ID] [nvarchar](50) NOT NULL PRIMARY KEY, [FromNo] [nvarchar](50) NULL, [Message] [nvarchar](max) NULL, [RecTime] [datetime] NULL, [Route] [nvarchar](255) NULL, [IMSI] [nvarchar](50) NULL, [PhoneNo] [nvarchar](50) ) CREATE TABLE [SMSDlv_OutBox]( [ID] [nvarchar](50) NOT NULL PRIMARY KEY, [ToNo] [nvarchar](50) NULL, [Subject] [nvarchar](255) NULL, [Message] [nvarchar](max) NULL, [FileAttach] [nvarchar](max) NULL, [CreateTime] [datetime] NULL, [SendTime] [datetime] NULL, [IsPrior] bit NULL DEFAULT 0, [IsSent] bit NULL DEFAULT 0, [IsRead] bit NULL DEFAULT 0, [Route] [nvarchar](255) NULL, [MMS] [bit] NULL DEFAULT 0, [FlashSMS] bit NULL DEFAULT 0, [IMSI] [varchar](50) NULL, [readTag] [varchar](50) NULL, [clientTag] [varchar](50) NULL, [dlv_Status] [bit] NULL, [dlv_Time] [datetime] NULL, [sentIndex] [int] NULL, [smpp_refid] [varchar](50) NULL) CREATE TABLE [SMSDlv_SIM]( [IMSI] [nvarchar](50) NOT NULL PRIMARY KEY, [PhoneNo] [nvarchar](50) NULL, [COMPort] [nvarchar](50) NULL, [UpdateTime] [datetime] NULL) CREATE NONCLUSTERED INDEX [idx_readtag] ON [SMSDlv_OutBox] ( [readTag] ASC) CREATE NONCLUSTERED INDEX [idx_isread] ON [SMSDlv_OutBox] ( [IsRead] ASC) CREATE NONCLUSTERED INDEX [idx_sendtime] ON [SMSDlv_OutBox] ( [SendTime] ASC) CREATE NONCLUSTERED INDEX [idx_isprior] ON [SMSDlv_OutBox] ( [IsPrior] ASC) --To improve read speed (optional) go CREATE PROCEDURE[sp_ReadData] (@clienttag varchar(50),@readtag varchar(50),@rows int) AS BEGIN update[smsdlv_outbox] set isread=1,readTag=@readtag,clientTag=@clienttag where ID in ( select id from (select ROW_NUMBER() OVER( order by isPrior,createtime) as px,* from [smsdlv_outbox] where isread=0 or isread is null) as a where a.px between 1 and @rows ) select id,tono,subject,message,fileattach,mms,IsPrior,flashsms,route,imsi from [smsdlv_outbox] where readTag=@readtag order by createtime END