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
Showing posts with label bit. Show all posts
Showing posts with label bit. Show all posts
Thursday, March 29, 2012
Thursday, March 22, 2012
Data Restore on Elm Street!
Hi all, a bit of a nasty one here!
Ive inherited a server that has just collapsed. It contains SQL Server
and has a fair amount of data on it.
The server would only boot into a BSOD Stop Error. I suspected that
one of the hives in the registry was corrupt so I used the Win2K
Recovery Console to restore the registry to its original settings (So
Windows can not see the SQL stuff now). There is no restore points
from which to take recent registry settings.
My Question is this...
We have a spare server (just lying about you know!!!) which we plan to
install Win2K and MS SQL Server onto... How can I transfer the data,
in a nice easy manner, from the old server to the new server?
Is it possible to move just the data files into the new install?
Remember I inherited this server! (I dont have Backups!!!)
Please... No email's about how important backups are... I know how
important backups are.
Any help/ideas would be appreciated.
Thanks, John T.You can certainly try
Exec sp_attach_db (details in BOL)
There are times when this will only work if you have explicitly used
sp_detach_db (details in BOL)
Failing that and no backups then you may want to give the boys and girls in
PSS a bell.
--
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"John Tear" <johntear@.hotmail.com> wrote in message
news:92ae3244.0404150027.72b19847@.posting.google.c om...
> Hi all, a bit of a nasty one here!
> Ive inherited a server that has just collapsed. It contains SQL Server
> and has a fair amount of data on it.
> The server would only boot into a BSOD Stop Error. I suspected that
> one of the hives in the registry was corrupt so I used the Win2K
> Recovery Console to restore the registry to its original settings (So
> Windows can not see the SQL stuff now). There is no restore points
> from which to take recent registry settings.
> My Question is this...
> We have a spare server (just lying about you know!!!) which we plan to
> install Win2K and MS SQL Server onto... How can I transfer the data,
> in a nice easy manner, from the old server to the new server?
> Is it possible to move just the data files into the new install?
> Remember I inherited this server! (I dont have Backups!!!)
> Please... No email's about how important backups are... I know how
> important backups are.
> Any help/ideas would be appreciated.
> Thanks, John T.|||Hi Allan
Thanks for your reply. I got this sorted, basically I took the data
files and transfered them accross to the new server then I attached
them in the management console for MS SQL. Pretty lucky I think!
I have advised that a backup solution is the VERY next step!!!
John T.
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message news:<407e4e4e$0$31686$fa0fcedb@.lovejoy.zen.co.uk>...
> You can certainly try
> Exec sp_attach_db (details in BOL)
> There are times when this will only work if you have explicitly used
> sp_detach_db (details in BOL)
>
> Failing that and no backups then you may want to give the boys and girls in
> PSS a bell.
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "John Tear" <johntear@.hotmail.com> wrote in message
> news:92ae3244.0404150027.72b19847@.posting.google.c om...
> > Hi all, a bit of a nasty one here!
> > Ive inherited a server that has just collapsed. It contains SQL Server
> > and has a fair amount of data on it.
> > The server would only boot into a BSOD Stop Error. I suspected that
> > one of the hives in the registry was corrupt so I used the Win2K
> > Recovery Console to restore the registry to its original settings (So
> > Windows can not see the SQL stuff now). There is no restore points
> > from which to take recent registry settings.
> > My Question is this...
> > We have a spare server (just lying about you know!!!) which we plan to
> > install Win2K and MS SQL Server onto... How can I transfer the data,
> > in a nice easy manner, from the old server to the new server?
> > Is it possible to move just the data files into the new install?
> > Remember I inherited this server! (I dont have Backups!!!)
> > Please... No email's about how important backups are... I know how
> > important backups are.
> > Any help/ideas would be appreciated.
> > Thanks, John T.|||Hi Allan
Thanks for your reply. I got this sorted, basically I took the data
files and transfered them accross to the new server then I attached
them in the management console for MS SQL. Pretty lucky I think!
I have advised that a backup solution is the VERY next step!!!
John T.
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message news:<407e4e4e$0$31686$fa0fcedb@.lovejoy.zen.co.uk>...
> You can certainly try
> Exec sp_attach_db (details in BOL)
> There are times when this will only work if you have explicitly used
> sp_detach_db (details in BOL)
>
> Failing that and no backups then you may want to give the boys and girls in
> PSS a bell.
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "John Tear" <johntear@.hotmail.com> wrote in message
> news:92ae3244.0404150027.72b19847@.posting.google.c om...
> > Hi all, a bit of a nasty one here!
> > Ive inherited a server that has just collapsed. It contains SQL Server
> > and has a fair amount of data on it.
> > The server would only boot into a BSOD Stop Error. I suspected that
> > one of the hives in the registry was corrupt so I used the Win2K
> > Recovery Console to restore the registry to its original settings (So
> > Windows can not see the SQL stuff now). There is no restore points
> > from which to take recent registry settings.
> > My Question is this...
> > We have a spare server (just lying about you know!!!) which we plan to
> > install Win2K and MS SQL Server onto... How can I transfer the data,
> > in a nice easy manner, from the old server to the new server?
> > Is it possible to move just the data files into the new install?
> > Remember I inherited this server! (I dont have Backups!!!)
> > Please... No email's about how important backups are... I know how
> > important backups are.
> > Any help/ideas would be appreciated.
> > Thanks, John T.sql
Data Restore on Elm Street!
Hi all, a bit of a nasty one here!
Ive inherited a server that has just collapsed. It contains SQL Server
and has a fair amount of data on it.
The server would only boot into a BSOD Stop Error. I suspected that
one of the hives in the registry was corrupt so I used the Win2K
Recovery Console to restore the registry to its original settings (So
Windows can not see the SQL stuff now). There is no restore points
from which to take recent registry settings.
My Question is this...
We have a spare server (just lying about you know!!!) which we plan to
install Win2K and MS SQL Server onto... How can I transfer the data,
in a nice easy manner, from the old server to the new server?
Is it possible to move just the data files into the new install?
Remember I inherited this server! (I dont have Backups!!!)
Please... No email's about how important backups are... I know how
important backups are.
Any help/ideas would be appreciated.
Thanks, John T.You can certainly try
Exec sp_attach_db (details in BOL)
There are times when this will only work if you have explicitly used
sp_detach_db (details in BOL)
Failing that and no backups then you may want to give the boys and girls in
PSS a bell.
--
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"John Tear" <johntear@.hotmail.com> wrote in message
news:92ae3244.0404150027.72b19847@.posting.google.c om...
> Hi all, a bit of a nasty one here!
> Ive inherited a server that has just collapsed. It contains SQL Server
> and has a fair amount of data on it.
> The server would only boot into a BSOD Stop Error. I suspected that
> one of the hives in the registry was corrupt so I used the Win2K
> Recovery Console to restore the registry to its original settings (So
> Windows can not see the SQL stuff now). There is no restore points
> from which to take recent registry settings.
> My Question is this...
> We have a spare server (just lying about you know!!!) which we plan to
> install Win2K and MS SQL Server onto... How can I transfer the data,
> in a nice easy manner, from the old server to the new server?
> Is it possible to move just the data files into the new install?
> Remember I inherited this server! (I dont have Backups!!!)
> Please... No email's about how important backups are... I know how
> important backups are.
> Any help/ideas would be appreciated.
> Thanks, John T.
Saturday, February 25, 2012
Data Manipulation
HI everyone,
I have a column STATUS of type bit(1,0) , i would like to manipulate it(without editing the database) in the Store Procedure in such a way that
if
STATUS =1 // return result STATUS= 'Active '
Else if
STATUS = 0 // return result STATUS = 'Inactive'
End if
My Stored Procedure Is As Follow
SELECT
STATUS + '--' + NAME AS STATUSNAME
FROM M_USER
END
Desired Result When Populated Into a Listbox Should Be As Follow
Active--Tester
Inactive--Tester1
Thanks In Advance for the Help Provided!
SELECT
CASE When Status = 1 Then 'Active'
ELSE 'Inactive'
END As STATUSNAME
FROM M_USER
:)
JB|||Thanks JBelthoff for the reply,
I follow ur example but its still not working , the code are as follow,
IF (@.MASTER = 'NRICSEARCH')
BEGIN
SELECT
CASE WHEN ACTIVE= 1 THEN 'ACTIVE'
ELSE 'INACTIVE'
END AS ACTIVE,
USER_ID,
NRIC + '--' + NAME+ '(' + CONVERT(VARCHAR, ACTIVE) + ')' AS NRICNAME
FROM M_USER
ORDER BY NRIC
--
Column ACTIVE is of type bit.
NRICNAME is used in the listbox as datatextfield
TheCurrent Output from the current code in the listbox is as follow,
123--Ben(0)
124--Andy(1)
TheDesired Output in the listbox is as follow,
123--Ben(ACTIVE)
124--Andy(INACTIVE)
Apparantly, The CASE is not working.|||To get a 1 column result set in your desired output.......
SELECT CAST(USER_ID As Varchar) + '--' + NAME + '(' +
CASE
WHEN ACTIVE= 1 THEN 'ACTIVE'
ELSE 'INACTIVE'
END
+ ')' As NRICNAME
FROM M_USER
:-D
Enjoy!
JB|||Hi JB,
Its working now, thanks a lot.
Friday, February 17, 2012
Data from two databases
Hello.
I have situation here, whitch comfises me a bit. I have to IDENTICAL databases. Diference between then is that they where "filled in" at different times, one till 1st of January 2000, the other after 1st January 2000. Is it possibe to get data out of two databases into one query (resultset) covering periods, included in both databases (let's say from 1st January 1999 till 1st January 2003)
Thanks in advanceSELECT * FROM Server.DatabaseBefore.Owner.TableName
UNION
SELECT * FROM Server.DatabaseAfter.Owner.TableName|||Make sure to use a UNION ALL in this instance, not just a UNION. If you just say UNION then SQL will implicitly do a DISTINCT, which will take longer to process and is not at all necessary in this case.
Terri
Subscribe to:
Posts (Atom)