Data Structure for Date Time Selection

Hi All,
We have a table which looks like the following:
ID (int)
Status (int)
Elgiblity (int)
EligibleTime (datetime)
Counter (int)
Flag (bit)
Typically our queries look like
SELECT TOP 1 * FROM TABLE WHERE (Status IN 1000, 2000, 3000) AND
Eligibilty = 2000 AND EligiblityTime < '09/12/2005 1:31:33 PM' AND Flag
= 0 AND Counter < 2
Since we're doing a lot of selects based on time + status codes... are
there any database structures suited for this?
Also, the values in this table are updated quite often... how do ensure
the indexes are update to date?
We load and use about 200K rows of data per day. We keep about 45 days
worth of data in the database. We would lilke to return the data as fast
as possible... does anyone have suggestions on how to store the data?
Thanks.
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
Newmarket Volvo Sucks! http://newmarketvolvo.tripod.comLucas
Are you concerned about performance of the query? Do the users complain that
they have to wait for a request?
What are the inedexes defined on the table?
"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns96D131BA935A9nntprogerscom@.127.0.0.1...
> Hi All,
> We have a table which looks like the following:
> ID (int)
> Status (int)
> Elgiblity (int)
> EligibleTime (datetime)
> Counter (int)
> Flag (bit)
> Typically our queries look like
> SELECT TOP 1 * FROM TABLE WHERE (Status IN 1000, 2000, 3000) AND
> Eligibilty = 2000 AND EligiblityTime < '09/12/2005 1:31:33 PM' AND Flag
> = 0 AND Counter < 2
> Since we're doing a lot of selects based on time + status codes... are
> there any database structures suited for this?
> Also, the values in this table are updated quite often... how do ensure
> the indexes are update to date?
> We load and use about 200K rows of data per day. We keep about 45 days
> worth of data in the database. We would lilke to return the data as fast
> as possible... does anyone have suggestions on how to store the data?
> Thanks.
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com|||Hi
to auto update statistics, use database option
AUTO_UPDATE_STATISTICS
>from bol
When set to ON, existing statistics are automatically updated when the
statistics become out-of-date because the data in the tables has changed.
When set to OFF, existing statistics are not automatically updated; instead,
statistics can be manually updated. For more information, see Statistical
Information.
By default, AUTO_UPDATE_STATISTICS is set to ON.
The status of this option can be determined by examining the
IsAutoUpdateStatistics property of the DATABASEPROPERTYEX function.
To know when exactly statistics are updated
DBCC SHOW_STATISTICS
To updat statistics
UPDATE STATISTICS table | view
[
index
| ( statistics_name [ ,...n ] )
]
[ WITH
[
[ FULLSCAN ]
| SAMPLE number { PERCENT | ROWS } ]
| RESAMPLE
]
[ [ , ] [ ALL | COLUMNS | INDEX ]
[ [ , ] NORECOMPUTE ]
]
Regards
R.D
"Uri Dimant" wrote:

> Lucas
> Are you concerned about performance of the query? Do the users complain th
at
> they have to wait for a request?
> What are the inedexes defined on the table?
>
> "Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
> news:Xns96D131BA935A9nntprogerscom@.127.0.0.1...
>
>|||SORRY AT WRONG PLACE
"R.D" wrote:
> Hi
> to auto update statistics, use database option
> AUTO_UPDATE_STATISTICS
> When set to ON, existing statistics are automatically updated when the
> statistics become out-of-date because the data in the tables has changed.
> When set to OFF, existing statistics are not automatically updated; instea
d,
> statistics can be manually updated. For more information, see Statistical
> Information.
> By default, AUTO_UPDATE_STATISTICS is set to ON.
> The status of this option can be determined by examining the
> IsAutoUpdateStatistics property of the DATABASEPROPERTYEX function.
> To know when exactly statistics are updated
> DBCC SHOW_STATISTICS
> To updat statistics
> UPDATE STATISTICS table | view
> [
> index
> | ( statistics_name [ ,...n ] )
> ]
> [ WITH
> [
> [ FULLSCAN ]
> | SAMPLE number { PERCENT | ROWS } ]
> | RESAMPLE
> ]
> [ [ , ] [ ALL | COLUMNS | INDEX ]
> [ [ , ] NORECOMPUTE ]
> ]
> Regards
> R.D
>
> "Uri Dimant" wrote:
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in
news:eUaMZwQuFHA.360@.TK2MSFTNGP12.phx.gbl:

> Lucas
> Are you concerned about performance of the query? Do the users
> complain that they have to wait for a request?
> What are the inedexes defined on the table?
Hi,
It's an application that runs against the table... the application
places phone calls on behalf of records in the database - we need to put
out 100s or 1000s of calls per minute(i.e. Emergency phone calls).
Right now there are a combination of factors:
1. Table Structure Could be Optimized
2. hardware could be better
As you see in my original post, the table is pretty much a flat file
(there are actually more columns - like 30+ but they're parameter
columns)... I'm just wondering if you guys have any pointers on how to
store datetime data in a way that is easily selectable.
Thanks!
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com|||Lucas
can be bit specific
is it the problem of insertion or select statement to retrive it.
pl.Post script
Regards
R.D
"Lucas Tam" wrote:

> "Uri Dimant" <urid@.iscar.co.il> wrote in
> news:eUaMZwQuFHA.360@.TK2MSFTNGP12.phx.gbl:
>
> Hi,
> It's an application that runs against the table... the application
> places phone calls on behalf of records in the database - we need to put
> out 100s or 1000s of calls per minute(i.e. Emergency phone calls).
> Right now there are a combination of factors:
> 1. Table Structure Could be Optimized
> 2. hardware could be better
> As you see in my original post, the table is pretty much a flat file
> (there are actually more columns - like 30+ but they're parameter
> columns)... I'm just wondering if you guys have any pointers on how to
> store datetime data in a way that is easily selectable.
> Thanks!
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com
>|||examnotes <RD@.discussions.microsoft.com> wrote in
news:B0959AEA-AB2D-48A9-922D-5E0528AB5CE3@.microsoft.com:

> Lucas
> can be bit specific
> is it the problem of insertion or select statement to retrive it.
> pl.Post script
> Regards
> R.D
Problem with select statement:
SELECT TOP 1 * FROM TABLE WHERE (Status IN 1000, 2000, 3000) AND
Eligibilty = 2000 AND EligiblityTime < '09/12/2005 1:31:33 PM' AND Flag
= 0 AND Counter < 2
Something like that can take a while... and use a lot of resources.
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com

Data lost during converting int to smallint

I am trying to keep it consistent with data from the source, I change
datatype for a field from int to smallint and it has a value between 200 and
1000.
When I do it in Enterprise Manager, I give me this warning:
- Warning: Data might be lost converting column 'auto_pre_appr' from 'int'.
Is this just a general warning?
CulamYes, it is warning and you may or may not lose some data, if you know what
the actual data values in the column and what value scope of smallint type
is (-32768 to 32767). Obviously, if the column of int contains data with
value smaller that -32768 or greater than 32767, the warning would become
reality.
"culam" <culam@.discussions.microsoft.com> wrote in message
news:2DB9A059-2D1A-44C9-8D44-32F8F7D7848C@.microsoft.com...
>I am trying to keep it consistent with data from the source, I change
> datatype for a field from int to smallint and it has a value between 200
> and
> 1000.
> When I do it in Enterprise Manager, I give me this warning:
> - Warning: Data might be lost converting column 'auto_pre_appr' from
> 'int'.
> Is this just a general warning?
> Culam

Data Integrity Question

I have the following table:
CREATE TABLE [transactions] (
[Transaction_ID] [int] IDENTITY (1, 1) NOT NULL ,
[CID] [int] NULL ,
[Effec_date] [smalldatetime] NULL ,
[Amount] [money] NULL ,
[Transaction_Type] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Trans_code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Accuracy_disclaimer] [bit] NULL
CONSTRAINT [PK_transactions] PRIMARY KEY CLUSTERED
(
[Transaction_ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
---
I want to prevent a record from being inserted where a record already exists
based on the following columns:
--CID
--Effec_Date
--Amount
In other words, if I have a record where CID = 1, Effec_Date = '04/01/2006'
and
Amount = 1.50, I want to prevent another record with these values from being
inserted. Would I use a muti column check constraint? What is the syntax?In a similar fashion as you have the PRIMARY KEY constraint, add a UNIQUE co
nstraint over those
three columns.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:0AFE3D9C-6934-44A8-9160-CEDE47D2C76A@.microsoft.com...
>I have the following table:
> CREATE TABLE [transactions] (
> [Transaction_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [CID] [int] NULL ,
> [Effec_date] [smalldatetime] NULL ,
> [Amount] [money] NULL ,
> [Transaction_Type] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Trans_code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Accuracy_disclaimer] [bit] NULL
> CONSTRAINT [PK_transactions] PRIMARY KEY CLUSTERED
> (
> [Transaction_ID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> ---
> I want to prevent a record from being inserted where a record already exis
ts
> based on the following columns:
> --CID
> --Effec_Date
> --Amount
> In other words, if I have a record where CID = 1, Effec_Date = '04/01/2006
'
> and
> Amount = 1.50, I want to prevent another record with these values from bei
ng
> inserted. Would I use a muti column check constraint? What is the syntax
?
>|||create a unique constraint on these 3 columns
create table t_1
(
col1 int,
col2 int,
col3 int
)
alter table t_1 add constraint UQ_col1_col2_col3 unique (col1,col2,col3)
go
"Eric" wrote:

> I have the following table:
> CREATE TABLE [transactions] (
> [Transaction_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [CID] [int] NULL ,
> [Effec_date] [smalldatetime] NULL ,
> [Amount] [money] NULL ,
> [Transaction_Type] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Trans_code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Accuracy_disclaimer] [bit] NULL
> CONSTRAINT [PK_transactions] PRIMARY KEY CLUSTERED
> (
> [Transaction_ID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> ---
> I want to prevent a record from being inserted where a record already exis
ts
> based on the following columns:
> --CID
> --Effec_Date
> --Amount
> In other words, if I have a record where CID = 1, Effec_Date = '04/01/2006
'
> and
> Amount = 1.50, I want to prevent another record with these values from bei
ng
> inserted. Would I use a muti column check constraint? What is the syntax
?
>|||"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:0AFE3D9C-6934-44A8-9160-CEDE47D2C76A@.microsoft.com...
>I have the following table:
> CREATE TABLE [transactions] (
> [Transaction_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [CID] [int] NULL ,
> [Effec_date] [smalldatetime] NULL ,
> [Amount] [money] NULL ,
> [Transaction_Type] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Trans_code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Accuracy_disclaimer] [bit] NULL
> CONSTRAINT [PK_transactions] PRIMARY KEY CLUSTERED
> (
> [Transaction_ID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> ---
> I want to prevent a record from being inserted where a record already
> exists
> based on the following columns:
> --CID
> --Effec_Date
> --Amount
> In other words, if I have a record where CID = 1, Effec_Date =
> '04/01/2006'
> and
> Amount = 1.50, I want to prevent another record with these values from
> being
> inserted. Would I use a muti column check constraint? What is the
> syntax?
>
This is a UNIQUE constraint. I suggest you make those columns non-nullable
first though:
ALTER TABLE transactions
ALTER COLUMN cid INTEGER NOT NULL;
ALTER TABLE transactions
ALTER COLUMN effec_date SMALLDATETIME NOT NULL;
ALTER TABLE transactions
ALTER COLUMN amount MONEY NOT NULL;
/* Add the unique constraint */
ALTER TABLE transactions
ADD CONSTRAINT ak1_transactions
UNIQUE (cid, effec_date, amount);
BTW. The MONEY datatype is typically a poor choice for amounts that will be
used in calculations. Using MONEY can lead to rounding errors. Use DECIMAL
instead.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Yes, noticed the rounding issue w/money. Thanks for the tip(s).
"David Portas" wrote:

> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:0AFE3D9C-6934-44A8-9160-CEDE47D2C76A@.microsoft.com...
> This is a UNIQUE constraint. I suggest you make those columns non-nullable
> first though:
> ALTER TABLE transactions
> ALTER COLUMN cid INTEGER NOT NULL;
> ALTER TABLE transactions
> ALTER COLUMN effec_date SMALLDATETIME NOT NULL;
> ALTER TABLE transactions
> ALTER COLUMN amount MONEY NOT NULL;
> /* Add the unique constraint */
> ALTER TABLE transactions
> ADD CONSTRAINT ak1_transactions
> UNIQUE (cid, effec_date, amount);
> BTW. The MONEY datatype is typically a poor choice for amounts that will b
e
> used in calculations. Using MONEY can lead to rounding errors. Use DECIMAL
> instead.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>|||You actually do not have a table at all, so you can NEVER have data
integrity. IDENTITY cannot be a key by definition and alll the other
columns are NULL-able and have no constraints.
Literally every row is wrong according to the basic rules of RDBMS, ISO
rules and data modeling.
CREATE TABLE Transactions - vague name!!
(transaction_id INTEGER IDENTITY (1, 1) NULL - non-relatioanl ,
non-key!! Total crap
cid INTEGER NULL, nulls in an identifier? Duh!
effect_date DATETIME NULL, -- no default?
amount MONEY NULL, -- proprietary data type and bad name!!
transaction_type VARCHAR (25) NULL, -- variable length & ISO-11179
violation
trans_code NVARCHAR (50) NULL, --really? codes in Chinese?
accuracy_disclaimer BIT NULL --bits in SQL?
);
Please give me an example of a transaction_type that is CHAR(50); if
you can find one, we need to re-design this encoding, unless it is an
industry standard.
Why did you use MONEY? Do you spit on ANSI./ISO Standards or do you
want the math problems?
Why did you think that BIT is a SQL data type or valid way to program
once you get above the assembly language level?
I have to use this in a book. When I try to make up a bad example, I
keep correcting gross errors; you do not!!
.
based on the following columns:
--CID
--Effec_Date
--Amount <<
Then put them into a key or a UNIQUE constraint!! Duh! And learn that
a row is not anything like a record.

Data Getting truncated as I insert i.e. instead of inserting Hello it inserts H

m inserting some data in big-sized fieldand small-sized fields, data of varchar type, int's, dateTime, andothers... i am using something called a stored procedure to add datainto a table.. now when i execute the stored procedure my data getstruncated (although i made the sizes for my fields ridiculously biglike 1000 just in case) for example if i want to enter Jasmine it onlyinserts 'J' in the field
I am sure there's something wrong in the stored procedure, and I amguessing it's a problem of using single vs. double quotes and stufflike that within my insert statement...
the following is my stored procedure:
CREATE procedure addLabor
@.lName varchar,
@.fName varchar,
@.mName varchar,
@.title varchar,
@.craft varchar,
@.lastFour varchar,
@.SSN varchar,
@.dateOfHire varchar,
@.currentProj varchar,
@.status tinyInt,
@.project_id int,
@.updateBy varchar,
@.updateDate varchar,
@.address varchar,
@.email varchar,
@.phone varchar,
@.zip varchar,
@.myfeedBack varchar,
@.ethnicity varchar,
@.userID int
as
BEGIN
SET NOCOUNT OFF
DECLARE @.newid INT
insert into laborPersonal ( lName, fName, mName, title, craft,lastFour, SSN, dateOfHire, currentProj, status, project_id,updateBy,
updateDate, address, email, phone, zip, ethnicity)
VALUES
(@.lName , @.fName , @.mName , @.title , @.craft , @.lastFour , @.SSN ,@.dateOfHire , @.currentProj ,
@.status, @.project_id , @.UpdateBy, @.UpdateDate , @.address , @.email , @.phone , @.zip , @.ethnicity )
SELECT @.newid = SCOPE_IDENTITY()
insert into feedBack
(lID, feedBack, userID, project_id)
values
(@.newid ,+'
+@.myfeedBack + ',
+@.userID ,
@.project_id )
SET NOCOUNT ON
END
GO

When i use 2 single quotes it insert the following string: "@.lName" not the actual value of the variable @.lName
my exec statement is this:
EXEC addLabor 'Razor', 'Nazor', 'mid', 'Mr', 'Carpenter Forman','1234','keOWVozC+wmBvaqgkVkZci5y4vFLdTKfZOVG4C6BSN6H2MBP6pdsIWA0SdPAlPJra0EjEj+uXI/kXSiBuwwnKQ==','6/27/2005 12:00:00 AM' , 'O.C. Public Library', 1, 3, '3', '7/25/20052:38:02 PM', '1233 Shady Canyon, Irvine', '', '', '12345','123-12-1234', 'African American', '3'
I appreciate any help or hints
thanks to all
All of your parameters are declared like this:
@.lName varchar,
Should be like this:

@.lName varchar(1000),
a type of varchar is like varchar(1), meaning a variable length string with a max of 1 character. The number used should likely match the underlying length of the field in the table