Does SQL Server 2005 Express has the method that delete automatically rows ordered by time colum

Hi, I made table that stores monitoring data every 1 minute.

It should have only 60 minutes-data from current time.

I delete data which stored 60 minutes ago before insert.

I'll be happy to find that the database engine does automatically above procedure.

You could use an insert trigger which fires when new data is inserted. Place your delete statement here to delete old records.

--
SvenC

Does SQL Server 2005 Express has the method that delete automatically rows ordered by time c

Hi, I made table that stores monitoring data every 1 minute.

It should have only 60 minutes-data from current time.

I delete data which stored 60 minutes ago before insert.

I'll be happy to find that the database engine does automatically above procedure.

You could use an insert trigger which fires when new data is inserted. Place your delete statement here to delete old records.

--
SvenC

Does it store all the results to tempdb database when I query against a large table which joins

Hi, all experts here,

I am wondering if tempdb stores all results tempararily whenever I query a large fact table with over 4 million records which joins another dimension table? Since each time when I run the query, the tempdb grows to nearly 1GB which nearly runs out all the space on my local system drive, as a result the performance totally down. Is there any way to fix this problem? Thanks a lot in advance and I am looking forward to hearing from you shortly for your kind advices.

With best regards,

Yours sincerely,

It doesn't store all the results - but it may store some intermediate results if it needs to.

You really shouldn't be putting tempdb on your local system drive. tempdb always has a lot of activity (typically the busiest database in any system), and ideally should be on its own disk - a fast one.

If you have over 4 million records in a single table, 1GB doesn't sound too big for tempdb. But I appreciate this doesn't fix your immediate problem.

Rob|||

Hi, Rob,

Thank you for your kind advices.

But how can we change the storage location of the tempdb database? It looks like tempdb database can not be detached.

I am looking forward to hearing from you further for your kind advices.

With best regards,

Yours sincerely,

|||

And also, though I have moved the data files of tempdb to other places, but when I restarted the database engine, the system automatically generates the data files of tempdb again back to its root data files?

Please give me some more advices for this.

Thanks a lot in advance.

With best regards,

Yours sincerely,

|||I believe moving tempdb is as simple as altering the file paths and restarting SQL Server. It'll create the new files automatically. I'm not sure if it removes the old ones automatically, so you may have to to some brief housekeeping once the database is up and running in its new location.

ALTER DATABASE tempdb MODIFY FILE (NAME = logical_filename, FILENAME = 'new_physical_file_path')

Just make sure you do the log in addition to all the data files you have.
|||

Hi, davidbrit2,

Thank you for your kind attention.

But I failed to modify the files paths for the data files of the tempdb database, I did restart the service, but the data files of tempdb again went back to its original file paths. Therefore I does not seem like we are able to change the file paths of the data files of tempdb.

Any more ideas? Thanks a lot in advance.

With best regards,

Yours sincerely,

|||You need to alter the database to tell it to have its files in a different location. But then you need to restart SQL Server (ie, the service - use SQL Configuration Manager) to actually have it start using those new locations. Once you've restarted SQL, you should see the new files start to be used. Then you can delete the old ones.

Hope this helps,

Rob|||

Hello Helen999888,

Let me start with giving you a brief of what is tempdb.
The tempdb system database is a global resource that is available to all users that are connected to an instance of SQL Server. The tempdb database is used to store the following objects: user objects, internal objects, and version stores.

User Objects

User objects are explicitly created by the user. These objects may be in the scope of a user session or in the scope of the routine in which the object is created. A routine is a stored procedure, trigger, or user-defined function. User objects can be one of the following:

User-defined tables and indexes
System tables and indexes
Global temporary tables and indexes
Local temporary tables and indexes
Table variables
Tables returned in table-valued functions
|||

Hi, HS_DJ,

Thanks a lot for your very kind and helpful advices. Got it done now.

With best regards,

Yours sincerely,

Does full text indexing works on unicode field?

Hi all,
Does full text indexing supports searching on a unicode field, for
example, a field that stores chinese characters?
Thank you in advance.Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"alvinyyt" <alvinyyt@.gmail.com> wrote in message
news:1144720305.221875.221920@.e56g2000cwe.googlegroups.com...
> Hi all,
> Does full text indexing supports searching on a unicode field, for
> example, a field that stores chinese characters?
> Thank you in advance.
>|||Yes, make sure you select the appropriate language word breaker.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"alvinyyt" <alvinyyt@.gmail.com> wrote in message
news:1144720305.221875.221920@.e56g2000cwe.googlegroups.com...
> Hi all,
> Does full text indexing supports searching on a unicode field, for
> example, a field that stores chinese characters?
> Thank you in advance.
>

Document Search for CMS

I have a database which stores documents (e.g. policies and guidelines)
in our content management system. The follow relationships exist:

Documents (1-to-Many) LinkTableKeywordLink (Many-to-1) Keywords

Documents (1-to-Many) LinkTableAttachments (Many-to-1) Attachments

Documents (1-to-Many) LookUpSubjects

Documents (1-to-Many) LookUpDocType

When the user conducts a search I want the SQL to check if the string
they enter is present in:

* the Title, Author, Summary fields of Documents OR
* the Title or Path of the Attachments
* the Keywords that are links OR
* the Subject that is linked OR
* the DocType that is linked.

Is this possible in one SQL query. I have tried the following:

SELECT DISTINCT Documents.Document, Documents.Title,Priority

FROM Documents,
Attachments,Keywords,LinkTableAttachments,LinkTabl eKeywordLink

WHERE
((Documents.Document=LinkTableAttachments.Document ID and
LinkTableAttachments.AttachmentID=Attachments.Id) OR
(Documents.Document=LinkTableKeywordLink.DocumentI D and
LinkTableKeywordLink.KeywordID=Keywords.Id))

AND (Documents.Title Like '%SEARCHSTRING%' OR Documents.Author Like
'%SEARCHSTRING%' OR Documents.Summary Like '%SEARCHSTRING%' OR
Keywords.Keyword Like '%SEARCHSTRING%' OR DocType.DocType Like
'%SEARCHSTRING%' OR Attachments.AttachmentTitle Like '%SEARCHSTRING%'
OR Attachments.Path like '%SEARCHSTRING%' or Subjects.Subject Like
'%SEARCHSTRING%')

AND StartDate<=getDate() and ReviewDate>getDate() order by Title

but this causes, perhaps understandably, a timeout error.

Any thoughts?

Thanks!
Steve(StevePBurgess@.gmail.com) writes:

Quote:

Originally Posted by

Is this possible in one SQL query. I have tried the following:
>
SELECT DISTINCT Documents.Document, Documents.Title,Priority
>
FROM Documents,
Attachments,Keywords,LinkTableAttachments,LinkTabl eKeywordLink
>
WHERE
((Documents.Document=LinkTableAttachments.Document ID and
LinkTableAttachments.AttachmentID=Attachments.Id) OR
(Documents.Document=LinkTableKeywordLink.DocumentI D and
LinkTableKeywordLink.KeywordID=Keywords.Id))
>
AND (Documents.Title Like '%SEARCHSTRING%' OR Documents.Author Like
'%SEARCHSTRING%' OR Documents.Summary Like '%SEARCHSTRING%' OR
Keywords.Keyword Like '%SEARCHSTRING%' OR DocType.DocType Like
'%SEARCHSTRING%' OR Attachments.AttachmentTitle Like '%SEARCHSTRING%'
OR Attachments.Path like '%SEARCHSTRING%' or Subjects.Subject Like
'%SEARCHSTRING%')
>
AND StartDate<=getDate() and ReviewDate>getDate() order by Title
>
>
but this causes, perhaps understandably, a timeout error.

The most crucial is probably the date conditions. This is a difficult
one there are two dates. But a start at least is make sure that the
query is parameterised, so the last line:

AND StartDate <= @.now and ReviewDate @.now order by Title

And with parameter I don't mean that you do "SELECT @.now = getdate()"
just before the query. You need it to pass @.now as a parameter to a
stored procedure or a parameterised query. Then the optimzer will
look at the value to determine how selective an index might be and
use it. If you use an unknown value, the optimizer will assume that 30%
of the rows will match, which is far too many,

If the search columns should are indexed, you should distinguish between
"starts with" and "contains" for the indexes to be useful. That is, only
include % first the search string, if the user actually wants "conatins",
because indexes are only good for starting values.

Adjust the application to have no timeout (= 0) or a very long timeout,
because in some cases the user may need to do searches that take a very long
time.

Finally, drop the DISTINCT, and use the EXISTS predicate to get from
the subtables. That is more efficient,since there no need to sort
the result to weed out the duplicates.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi - and thanks for the feedback.

If I run the query with against just one of the 1-many-many-1 table
relationships it works perfectly. The issue, I think, is having two
OR-ed together. When running against one set (e.g. just keywords and
not attachments) the search is very quick.

Best wishes,
Steve

Erland Sommarskog wrote:

Quote:

Originally Posted by

>
The most crucial is probably the date conditions. This is a difficult
one there are two dates. But a start at least is make sure that the
query is parameterised, so the last line:
>
AND StartDate <= @.now and ReviewDate @.now order by Title
>
And with parameter I don't mean that you do "SELECT @.now = getdate()"
just before the query. You need it to pass @.now as a parameter to a
stored procedure or a parameterised query. Then the optimzer will
look at the value to determine how selective an index might be and
use it. If you use an unknown value, the optimizer will assume that 30%
of the rows will match, which is far too many,
>
>
If the search columns should are indexed, you should distinguish between
"starts with" and "contains" for the indexes to be useful. That is, only
include % first the search string, if the user actually wants "conatins",
because indexes are only good for starting values.
>
Adjust the application to have no timeout (= 0) or a very long timeout,
because in some cases the user may need to do searches that take a very long
time.
>
Finally, drop the DISTINCT, and use the EXISTS predicate to get from
the subtables. That is more efficient,since there no need to sort
the result to weed out the duplicates.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||(StevePBurgess@.gmail.com) writes:

Quote:

Originally Posted by

Hi - and thanks for the feedback.
>
If I run the query with against just one of the 1-many-many-1 table
relationships it works perfectly. The issue, I think, is having two
OR-ed together. When running against one set (e.g. just keywords and
not attachments) the search is very quick.

The more tables you throw into the mix, the more tables there are to search.

If you want me or anyone else to say somehting more useful, we would need to
see:

o CREATE TABLE and INDEX statements for the tables.
o The fast query (and repeat the slow while you are it).
o The output from SET STATISTICS PROFILE ON when you run the two from
a query tool. This output is very wide, so put in an attachment if
possible.
o Which version of SQL Server you are running.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||

Quote:

Originally Posted by

If you want me or anyone else to say somehting more useful, we would need to
see:
>
o CREATE TABLE and INDEX statements for the tables.
o The fast query (and repeat the slow while you are it).
o The output from SET STATISTICS PROFILE ON when you run the two from
a query tool. This output is very wide, so put in an attachment if
possible.
o Which version of SQL Server you are running.
>

Hi. Thanks. The question was really about whether the SQL is valid
(given the relationships that are defined) and/or whether it could be
written more efficiently.