Problems in Search opertion using Like

Hi,
In our application we have a search page where people can search based on
different fields some of our fields support all chars like %, _ etc.,
We have to perform a like search on this field.
If the users typed % or _ in the text field then as usual its
considering it as wild card charecters.
Is there any way solve the problems like this.
Thanks & Regards,
Prasad DannaniTrap for searches on % and _. Replace these characters with a token like
PERCENT and UNDERSCORE. Modify your content so all instances of % and _ are
also replaced by PERCENT and UNDERSCORE. Then in your application have it
render PERCENT as % and UNDERSCORE as _.
Hilary Cotter
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
"Prasad Dannani" <prasad@.pennywise.com> wrote in message
news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Hi,
> In our application we have a search page where people can search based
on
> different fields some of our fields support all chars like %, _ etc.,
> We have to perform a like search on this field.
> If the users typed % or _ in the text field then as usual its
> considering it as wild card charecters.
> Is there any way solve the problems like this.
> Thanks & Regards,
> Prasad Dannani
>
>
>|||Use the ESCAPE clause for LIKE. I.e.escape the special craracter with a spec
ial character (specified
in the ESCAPE clause). Find all rows with text containing string "30% cheap"
:
SELECT *
FROM
(
SELECT 'It is 30% cheaper' AS a
UNION
SELECT 'it costs 30 bucke'
UNION
SELECT 'it costs 50 bucke'
) AS i
WHERE a LIKE '%30!% cheap%' ESCAPE '!'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Prasad Dannani" <prasad@.pennywise.com> wrote in message
news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Hi,
> In our application we have a search page where people can search based o
n
> different fields some of our fields support all chars like %, _ etc.,
> We have to perform a like search on this field.
> If the users typed % or _ in the text field then as usual its
> considering it as wild card charecters.
> Is there any way solve the problems like this.
> Thanks & Regards,
> Prasad Dannani
>
>
>|||Hilary wrote on Wed, 17 Aug 2005 05:22:21 -0400:

> Trap for searches on % and _. Replace these characters with a token like
> PERCENT and UNDERSCORE. Modify your content so all instances of % and _
> are also replaced by PERCENT and UNDERSCORE. Then in your application have
> it render PERCENT as % and UNDERSCORE as _.
How about using the ESCAPE keyword or bracketing? eg.
SELECT * FROM MyTable WHERE MyColumn LIKE '45/%' ESCAPE '/'
SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]'
Those will look for the string 45% rather than all strings starting with 45.
That way you don't have to mess around with token replacement.
Dan
[vbcol=seagreen]
> "Prasad Dannani" <prasad@.pennywise.com> wrote in message
> news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
> on|||Hi Dan,
Your Query ( SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]')
worked but it clearly says sql server like operator is always having
problems with one or the other charecter.
So, i was decided to make use of left function in the where clause
on for using Like which is not good performance wise.
We tries your first Query but its not worked for us.
In c or other languages we have proper escape charecters. I don't
why is it not there for sql server.
Once Again Thanks for Helping Us,
Prasad Dannani.
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:%23%23umhYxoFHA.2580@.TK2MSFTNGP09.phx.gbl...
> Hilary wrote on Wed, 17 Aug 2005 05:22:21 -0400:
>
have[vbcol=seagreen]
> How about using the ESCAPE keyword or bracketing? eg.
> SELECT * FROM MyTable WHERE MyColumn LIKE '45/%' ESCAPE '/'
> SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]'
> Those will look for the string 45% rather than all strings starting with
45.
> That way you don't have to mess around with token replacement.
> Dan
>
based[vbcol=seagreen]
>|||Did you read my post? Read it and you see how you escape a LIKE clause.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Prasad Dannani" <prasad@.pennywise.com> wrote in message
news:ejirWb8oFHA.1968@.TK2MSFTNGP14.phx.gbl...
> Hi Dan,
> Your Query ( SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]')
> worked but it clearly says sql server like operator is always having
> problems with one or the other charecter.
> So, i was decided to make use of left function in the where clause
> on for using Like which is not good performance wise.
> We tries your first Query but its not worked for us.
> In c or other languages we have proper escape charecters. I don't
> why is it not there for sql server.
> Once Again Thanks for Helping Us,
> Prasad Dannani.
>sql

Problems in Search opertion using Like

Hi,
In our application we have a search page where people can search based on
different fields some of our fields support all chars like %, _ etc.,
We have to perform a like search on this field.
If the users typed % or _ in the text field then as usual its
considering it as wild card charecters.
Is there any way solve the problems like this.
Thanks & Regards,
Prasad DannaniTrap for searches on % and _. Replace these characters with a token like
PERCENT and UNDERSCORE. Modify your content so all instances of % and _ are
also replaced by PERCENT and UNDERSCORE. Then in your application have it
render PERCENT as % and UNDERSCORE as _.
--
Hilary Cotter
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
"Prasad Dannani" <prasad@.pennywise.com> wrote in message
news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Hi,
> In our application we have a search page where people can search based
on
> different fields some of our fields support all chars like %, _ etc.,
> We have to perform a like search on this field.
> If the users typed % or _ in the text field then as usual its
> considering it as wild card charecters.
> Is there any way solve the problems like this.
> Thanks & Regards,
> Prasad Dannani
>
>
>|||Use the ESCAPE clause for LIKE. I.e.escape the special craracter with a special character (specified
in the ESCAPE clause). Find all rows with text containing string "30% cheap":
SELECT *
FROM
(
SELECT 'It is 30% cheaper' AS a
UNION
SELECT 'it costs 30 bucke'
UNION
SELECT 'it costs 50 bucke'
) AS i
WHERE a LIKE '%30!% cheap%' ESCAPE '!'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Prasad Dannani" <prasad@.pennywise.com> wrote in message
news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Hi,
> In our application we have a search page where people can search based on
> different fields some of our fields support all chars like %, _ etc.,
> We have to perform a like search on this field.
> If the users typed % or _ in the text field then as usual its
> considering it as wild card charecters.
> Is there any way solve the problems like this.
> Thanks & Regards,
> Prasad Dannani
>
>
>|||Hilary wrote on Wed, 17 Aug 2005 05:22:21 -0400:
> Trap for searches on % and _. Replace these characters with a token like
> PERCENT and UNDERSCORE. Modify your content so all instances of % and _
> are also replaced by PERCENT and UNDERSCORE. Then in your application have
> it render PERCENT as % and UNDERSCORE as _.
How about using the ESCAPE keyword or bracketing? eg.
SELECT * FROM MyTable WHERE MyColumn LIKE '45/%' ESCAPE '/'
SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]'
Those will look for the string 45% rather than all strings starting with 45.
That way you don't have to mess around with token replacement.
Dan
> "Prasad Dannani" <prasad@.pennywise.com> wrote in message
> news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> In our application we have a search page where people can search based
> on
>> different fields some of our fields support all chars like %, _ etc.,
>> We have to perform a like search on this field.
>> If the users typed % or _ in the text field then as usual its
>> considering it as wild card charecters.
>> Is there any way solve the problems like this.
>> Thanks & Regards,
>> Prasad Dannani|||Hi Dan,
Your Query ( SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]')
worked but it clearly says sql server like operator is always having
problems with one or the other charecter.
So, i was decided to make use of left function in the where clause
on for using Like which is not good performance wise.
We tries your first Query but its not worked for us.
In c or other languages we have proper escape charecters. I don't
why is it not there for sql server.
Once Again Thanks for Helping Us,
Prasad Dannani.
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:%23%23umhYxoFHA.2580@.TK2MSFTNGP09.phx.gbl...
> Hilary wrote on Wed, 17 Aug 2005 05:22:21 -0400:
> > Trap for searches on % and _. Replace these characters with a token like
> > PERCENT and UNDERSCORE. Modify your content so all instances of % and _
> > are also replaced by PERCENT and UNDERSCORE. Then in your application
have
> > it render PERCENT as % and UNDERSCORE as _.
> How about using the ESCAPE keyword or bracketing? eg.
> SELECT * FROM MyTable WHERE MyColumn LIKE '45/%' ESCAPE '/'
> SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]'
> Those will look for the string 45% rather than all strings starting with
45.
> That way you don't have to mess around with token replacement.
> Dan
> > "Prasad Dannani" <prasad@.pennywise.com> wrote in message
> > news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
> >> Hi,
> >>
> >> In our application we have a search page where people can search
based
> > on
> >> different fields some of our fields support all chars like %, _ etc.,
> >>
> >> We have to perform a like search on this field.
> >>
> >> If the users typed % or _ in the text field then as usual its
> >> considering it as wild card charecters.
> >>
> >> Is there any way solve the problems like this.
> >>
> >> Thanks & Regards,
> >>
> >> Prasad Dannani
> >>
>|||Did you read my post? Read it and you see how you escape a LIKE clause.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Prasad Dannani" <prasad@.pennywise.com> wrote in message
news:ejirWb8oFHA.1968@.TK2MSFTNGP14.phx.gbl...
> Hi Dan,
> Your Query ( SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]')
> worked but it clearly says sql server like operator is always having
> problems with one or the other charecter.
> So, i was decided to make use of left function in the where clause
> on for using Like which is not good performance wise.
> We tries your first Query but its not worked for us.
> In c or other languages we have proper escape charecters. I don't
> why is it not there for sql server.
> Once Again Thanks for Helping Us,
> Prasad Dannani.
>

Problems in Search opertion using Like

Hi,
In our application we have a search page where people can search based on
different fields some of our fields support all chars like %, _ etc.,
We have to perform a like search on this field.
If the users typed % or _ in the text field then as usual its
considering it as wild card charecters.
Is there any way solve the problems like this.
Thanks & Regards,
Prasad Dannani
Trap for searches on % and _. Replace these characters with a token like
PERCENT and UNDERSCORE. Modify your content so all instances of % and _ are
also replaced by PERCENT and UNDERSCORE. Then in your application have it
render PERCENT as % and UNDERSCORE as _.
Hilary Cotter
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
"Prasad Dannani" <prasad@.pennywise.com> wrote in message
news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Hi,
> In our application we have a search page where people can search based
on
> different fields some of our fields support all chars like %, _ etc.,
> We have to perform a like search on this field.
> If the users typed % or _ in the text field then as usual its
> considering it as wild card charecters.
> Is there any way solve the problems like this.
> Thanks & Regards,
> Prasad Dannani
>
>
>
|||Use the ESCAPE clause for LIKE. I.e.escape the special craracter with a special character (specified
in the ESCAPE clause). Find all rows with text containing string "30% cheap":
SELECT *
FROM
(
SELECT 'It is 30% cheaper' AS a
UNION
SELECT 'it costs 30 bucke'
UNION
SELECT 'it costs 50 bucke'
) AS i
WHERE a LIKE '%30!% cheap%' ESCAPE '!'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Prasad Dannani" <prasad@.pennywise.com> wrote in message
news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Hi,
> In our application we have a search page where people can search based on
> different fields some of our fields support all chars like %, _ etc.,
> We have to perform a like search on this field.
> If the users typed % or _ in the text field then as usual its
> considering it as wild card charecters.
> Is there any way solve the problems like this.
> Thanks & Regards,
> Prasad Dannani
>
>
>
|||Hilary wrote on Wed, 17 Aug 2005 05:22:21 -0400:

> Trap for searches on % and _. Replace these characters with a token like
> PERCENT and UNDERSCORE. Modify your content so all instances of % and _
> are also replaced by PERCENT and UNDERSCORE. Then in your application have
> it render PERCENT as % and UNDERSCORE as _.
How about using the ESCAPE keyword or bracketing? eg.
SELECT * FROM MyTable WHERE MyColumn LIKE '45/%' ESCAPE '/'
SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]'
Those will look for the string 45% rather than all strings starting with 45.
That way you don't have to mess around with token replacement.
Dan
[vbcol=seagreen]
> "Prasad Dannani" <prasad@.pennywise.com> wrote in message
> news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
> on
|||Hi Dan,
Your Query ( SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]')
worked but it clearly says sql server like operator is always having
problems with one or the other charecter.
So, i was decided to make use of left function in the where clause
on for using Like which is not good performance wise.
We tries your first Query but its not worked for us.
In c or other languages we have proper escape charecters. I don't
why is it not there for sql server.
Once Again Thanks for Helping Us,
Prasad Dannani.
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:%23%23umhYxoFHA.2580@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hilary wrote on Wed, 17 Aug 2005 05:22:21 -0400:
have
> How about using the ESCAPE keyword or bracketing? eg.
> SELECT * FROM MyTable WHERE MyColumn LIKE '45/%' ESCAPE '/'
> SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]'
> Those will look for the string 45% rather than all strings starting with
45.[vbcol=seagreen]
> That way you don't have to mess around with token replacement.
> Dan
based
>
|||Did you read my post? Read it and you see how you escape a LIKE clause.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Prasad Dannani" <prasad@.pennywise.com> wrote in message
news:ejirWb8oFHA.1968@.TK2MSFTNGP14.phx.gbl...
> Hi Dan,
> Your Query ( SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]')
> worked but it clearly says sql server like operator is always having
> problems with one or the other charecter.
> So, i was decided to make use of left function in the where clause
> on for using Like which is not good performance wise.
> We tries your first Query but its not worked for us.
> In c or other languages we have proper escape charecters. I don't
> why is it not there for sql server.
> Once Again Thanks for Helping Us,
> Prasad Dannani.
>

Problems importing data into sql server

I have a table for authors (for our bookstore) that has several fields
(firstname, lastname, etc.) and an author_id field (set as identity)

I'm trying to import a spreadsheet into this table, but keep getting
error messages that say I can't import data into the author_id field
(the identityf field).

Can someone suggest what I can do to overcome this?

Thanks,

Bill1) Import the data into a new flat table (ie: without indexes).
2) Run a report on the key field (identityf) -- there must be no duplicates.

select indentityf, count(*)
from newtable
group by identityf
having count(*)>1

should yield no results.

3) Correct the spreadsheet.
4) reimport the spreadsheet.

"Bill" <billzimmerman@.gospellight.com> wrote in message
news:8da5f4f4.0312091329.c066e47@.posting.google.co m...
> I have a table for authors (for our bookstore) that has several fields
> (firstname, lastname, etc.) and an author_id field (set as identity)
> I'm trying to import a spreadsheet into this table, but keep getting
> error messages that say I can't import data into the author_id field
> (the identityf field).
> Can someone suggest what I can do to overcome this?
> Thanks,
> Bill|||"Bill" <billzimmerman@.gospellight.com> wrote in message
news:8da5f4f4.0312091329.c066e47@.posting.google.co m...
> I have a table for authors (for our bookstore) that has several fields
> (firstname, lastname, etc.) and an author_id field (set as identity)
> I'm trying to import a spreadsheet into this table, but keep getting
> error messages that say I can't import data into the author_id field
> (the identityf field).
> Can someone suggest what I can do to overcome this?
<snip
Use the DTS. On the screen that allows you to select the tables, click the
Transform button. On this screem, you'll see 'Allow Identity Insert'.

BV
www.iheartmypond.comsql

problems about displaying chinese and mislocation of controls

1. I have problems on displaying chinese on reporting services.

e.g. §Cˉ×a′oà-J′?3??h?o(¤??)

The data type of the fields is char. Is it possible to display chinese using this database setting but not setting the data type to nvarchar?

2. On the other hand, there is mislocation of the controls. If I put a textbox on another textbox, one of the textbox will not be in the orginal location but in somewhere nearby. The same situation appears for the case of images. How can I "lock" the location of the controls?

I almost forgot I am assuming you know the Chinese alphabet is more than 2000 characters compared to the 26 characters Latin. I don't see how you can render Chinese with Char, you need Nvarchar or Nvarchar(max) with the version of Chinese you need because there are about six defined in SQL Server. You can use collation for the database table and column, the report dataset data option property and the international section of the table and text box. The later just change the collation of the ReportServerDB and the ReportServerTempDB. But clean collation in your tables and columns with the stored procedure will take care of it. Hope this helps.

|||

There is nothing happen when I set any collation in MS VS. As other applications are using the same database, I can't change the datatype into nvarchar.

Is nvarchar the only solution to the problem?

|||

(Is nvarchar the only solution to the problem?)

Nvarchar only tells SQL Server the data is not ASCII but Unicode it is the collation that tells SQL Server what code page to use and sort order which is very important with Chinese. You can create a View with Chinese collation and Nvarchar, then add the collation in the stored procedure run a search for collation precedence in the BOL.

Problem: too many mark fields in the table

Many applications retrieve information from the same table. Each application
needs to remember the records it has retrieved before, so that it does not
retrieve these records in the following run any more.
The current solution is to put the different "flag" field for each
application on the table. The flag field for each application will be marked
by the application after it retrieves the records.
The problem is that the table becomes extremely wide and keep widening. The
table needs to be changed each time a new appliction comes up.
I am thinking about keep a list for each application which stores the key
values of the records the application has retrieved, instead of marking the
original table.
Is there any better way to do it? What is the workaround you recommend?
Thanks,
Lixin
That is a strange requirement, that I haven't come across myself so far.
Yes, the workaround you have seems sensible.
Are your keys incrementing/follow a logical order? If so, your applications
could remember the last key retrieved and next time, look for keys greater
than the saved key.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"FLX" <nospam@.hotmail.com> wrote in message
news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
Many applications retrieve information from the same table. Each application
needs to remember the records it has retrieved before, so that it does not
retrieve these records in the following run any more.
The current solution is to put the different "flag" field for each
application on the table. The flag field for each application will be marked
by the application after it retrieves the records.
The problem is that the table becomes extremely wide and keep widening. The
table needs to be changed each time a new appliction comes up.
I am thinking about keep a list for each application which stores the key
values of the records the application has retrieved, instead of marking the
original table.
Is there any better way to do it? What is the workaround you recommend?
Thanks,
Lixin
|||If rows may be retrieved by multiple applications and you need to keep a
history of this just put the data in a table:
CREATE TABLE ApplicationHistory (keycol INTEGER NOT NULL REFERENCES
YourTable (keycol), application CHAR(10) NOT NULL REFERENCES Applications
(application), appdate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY
KEY(keycol, application))
David Portas
SQL Server MVP
|||Why can't the applications themselves internally store sets (or arrays, or
dictionarys, or whatever your favorite container is) containing identifiers
for rows they've already retrieved?
"FLX" <nospam@.hotmail.com> wrote in message
news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Many applications retrieve information from the same table. Each
application
> needs to remember the records it has retrieved before, so that it does not
> retrieve these records in the following run any more.
> The current solution is to put the different "flag" field for each
> application on the table. The flag field for each application will be
marked
> by the application after it retrieves the records.
> The problem is that the table becomes extremely wide and keep widening.
The
> table needs to be changed each time a new appliction comes up.
> I am thinking about keep a list for each application which stores the key
> values of the records the application has retrieved, instead of marking
the
> original table.
> Is there any better way to do it? What is the workaround you recommend?
> Thanks,
> Lixin
>
|||Unfortunately, the key values are not in the logic order.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:u0w0VWvTEHA.808@.tk2msftngp13.phx.gbl...
> That is a strange requirement, that I haven't come across myself so far.
> Yes, the workaround you have seems sensible.
> Are your keys incrementing/follow a logical order? If so, your
applications
> could remember the last key retrieved and next time, look for keys greater
> than the saved key.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Many applications retrieve information from the same table. Each
application
> needs to remember the records it has retrieved before, so that it does not
> retrieve these records in the following run any more.
> The current solution is to put the different "flag" field for each
> application on the table. The flag field for each application will be
marked
> by the application after it retrieves the records.
> The problem is that the table becomes extremely wide and keep widening.
The
> table needs to be changed each time a new appliction comes up.
> I am thinking about keep a list for each application which stores the key
> values of the records the application has retrieved, instead of marking
the
> original table.
> Is there any better way to do it? What is the workaround you recommend?
> Thanks,
> Lixin
>
>
|||Thanks. This is what I am going to do.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:w_CdnY17nKuK81XdRVn_iw@.giganews.com...
> If rows may be retrieved by multiple applications and you need to keep a
> history of this just put the data in a table:
> CREATE TABLE ApplicationHistory (keycol INTEGER NOT NULL REFERENCES
> YourTable (keycol), application CHAR(10) NOT NULL REFERENCES Applications
> (application), appdate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY
> KEY(keycol, application))
> --
> David Portas
> SQL Server MVP
> --
>
|||The application runs and exits, therefore I can't use array or any other
internal data structures.
I think I can store the key values in the text file or in a database table.
The latter might be more convenient and efficient.
Thanks a lot.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Ko2BZvTEHA.3476@.tk2msftngp13.phx.gbl...
> Why can't the applications themselves internally store sets (or arrays, or
> dictionarys, or whatever your favorite container is) containing
identifiers[vbcol=seagreen]
> for rows they've already retrieved?
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> application
not[vbcol=seagreen]
> marked
> The
key
> the
>
|||I think David Portas' solution is probably best; you can JOIN to the table
in order to filter for future retrievals of data, without reading from an
external source.
"FLX" <nospam@.hotmail.com> wrote in message
news:%23PkxrmvTEHA.544@.TK2MSFTNGP11.phx.gbl...
> The application runs and exits, therefore I can't use array or any other
> internal data structures.
> I think I can store the key values in the text file or in a database
table.
> The latter might be more convenient and efficient.
>
|||Instead of separate 'mark' columns use single column (SelectedFlags int),
storing a bit mask of applications marking the row.
Ramon @. Havana Club
"FLX" <nospam@.hotmail.com> / :
news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Many applications retrieve information from the same table. Each
application
> needs to remember the records it has retrieved before, so that it does not
> retrieve these records in the following run any more.
> The current solution is to put the different "flag" field for each
> application on the table. The flag field for each application will be
marked
> by the application after it retrieves the records.
> The problem is that the table becomes extremely wide and keep widening.
The
> table needs to be changed each time a new appliction comes up.
> I am thinking about keep a list for each application which stores the key
> values of the records the application has retrieved, instead of marking
the
> original table.
> Is there any better way to do it? What is the workaround you recommend?
> Thanks,
> Lixin
>

Problem: too many mark fields in the table

Many applications retrieve information from the same table. Each application
needs to remember the records it has retrieved before, so that it does not
retrieve these records in the following run any more.
The current solution is to put the different "flag" field for each
application on the table. The flag field for each application will be marked
by the application after it retrieves the records.
The problem is that the table becomes extremely wide and keep widening. The
table needs to be changed each time a new appliction comes up.
I am thinking about keep a list for each application which stores the key
values of the records the application has retrieved, instead of marking the
original table.
Is there any better way to do it? What is the workaround you recommend?
Thanks,
LixinThat is a strange requirement, that I haven't come across myself so far.
Yes, the workaround you have seems sensible.
Are your keys incrementing/follow a logical order? If so, your applications
could remember the last key retrieved and next time, look for keys greater
than the saved key.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"FLX" <nospam@.hotmail.com> wrote in message
news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
Many applications retrieve information from the same table. Each application
needs to remember the records it has retrieved before, so that it does not
retrieve these records in the following run any more.
The current solution is to put the different "flag" field for each
application on the table. The flag field for each application will be marked
by the application after it retrieves the records.
The problem is that the table becomes extremely wide and keep widening. The
table needs to be changed each time a new appliction comes up.
I am thinking about keep a list for each application which stores the key
values of the records the application has retrieved, instead of marking the
original table.
Is there any better way to do it? What is the workaround you recommend?
Thanks,
Lixin|||If rows may be retrieved by multiple applications and you need to keep a
history of this just put the data in a table:
CREATE TABLE ApplicationHistory (keycol INTEGER NOT NULL REFERENCES
YourTable (keycol), application CHAR(10) NOT NULL REFERENCES Applications
(application), appdate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY
KEY(keycol, application))
--
David Portas
SQL Server MVP
--|||Why can't the applications themselves internally store sets (or arrays, or
dictionarys, or whatever your favorite container is) containing identifiers
for rows they've already retrieved?
"FLX" <nospam@.hotmail.com> wrote in message
news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Many applications retrieve information from the same table. Each
application
> needs to remember the records it has retrieved before, so that it does not
> retrieve these records in the following run any more.
> The current solution is to put the different "flag" field for each
> application on the table. The flag field for each application will be
marked
> by the application after it retrieves the records.
> The problem is that the table becomes extremely wide and keep widening.
The
> table needs to be changed each time a new appliction comes up.
> I am thinking about keep a list for each application which stores the key
> values of the records the application has retrieved, instead of marking
the
> original table.
> Is there any better way to do it? What is the workaround you recommend?
> Thanks,
> Lixin
>|||Unfortunately, the key values are not in the logic order.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:u0w0VWvTEHA.808@.tk2msftngp13.phx.gbl...
> That is a strange requirement, that I haven't come across myself so far.
> Yes, the workaround you have seems sensible.
> Are your keys incrementing/follow a logical order? If so, your
applications
> could remember the last key retrieved and next time, look for keys greater
> than the saved key.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Many applications retrieve information from the same table. Each
application
> needs to remember the records it has retrieved before, so that it does not
> retrieve these records in the following run any more.
> The current solution is to put the different "flag" field for each
> application on the table. The flag field for each application will be
marked
> by the application after it retrieves the records.
> The problem is that the table becomes extremely wide and keep widening.
The
> table needs to be changed each time a new appliction comes up.
> I am thinking about keep a list for each application which stores the key
> values of the records the application has retrieved, instead of marking
the
> original table.
> Is there any better way to do it? What is the workaround you recommend?
> Thanks,
> Lixin
>
>|||Thanks. This is what I am going to do.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:w_CdnY17nKuK81XdRVn_iw@.giganews.com...
> If rows may be retrieved by multiple applications and you need to keep a
> history of this just put the data in a table:
> CREATE TABLE ApplicationHistory (keycol INTEGER NOT NULL REFERENCES
> YourTable (keycol), application CHAR(10) NOT NULL REFERENCES Applications
> (application), appdate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY
> KEY(keycol, application))
> --
> David Portas
> SQL Server MVP
> --
>|||The application runs and exits, therefore I can't use array or any other
internal data structures.
I think I can store the key values in the text file or in a database table.
The latter might be more convenient and efficient.
Thanks a lot.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Ko2BZvTEHA.3476@.tk2msftngp13.phx.gbl...
> Why can't the applications themselves internally store sets (or arrays, or
> dictionarys, or whatever your favorite container is) containing
identifiers
> for rows they've already retrieved?
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> > Many applications retrieve information from the same table. Each
> application
> > needs to remember the records it has retrieved before, so that it does
not
> > retrieve these records in the following run any more.
> >
> > The current solution is to put the different "flag" field for each
> > application on the table. The flag field for each application will be
> marked
> > by the application after it retrieves the records.
> >
> > The problem is that the table becomes extremely wide and keep widening.
> The
> > table needs to be changed each time a new appliction comes up.
> >
> > I am thinking about keep a list for each application which stores the
key
> > values of the records the application has retrieved, instead of marking
> the
> > original table.
> >
> > Is there any better way to do it? What is the workaround you recommend?
> > Thanks,
> >
> > Lixin
> >
> >
>|||I think David Portas' solution is probably best; you can JOIN to the table
in order to filter for future retrievals of data, without reading from an
external source.
"FLX" <nospam@.hotmail.com> wrote in message
news:%23PkxrmvTEHA.544@.TK2MSFTNGP11.phx.gbl...
> The application runs and exits, therefore I can't use array or any other
> internal data structures.
> I think I can store the key values in the text file or in a database
table.
> The latter might be more convenient and efficient.
>|||Instead of separate 'mark' columns use single column (SelectedFlags int),
storing a bit mask of applications marking the row.
Ramon @. Havana Club
"FLX" <nospam@.hotmail.com> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Many applications retrieve information from the same table. Each
application
> needs to remember the records it has retrieved before, so that it does not
> retrieve these records in the following run any more.
> The current solution is to put the different "flag" field for each
> application on the table. The flag field for each application will be
marked
> by the application after it retrieves the records.
> The problem is that the table becomes extremely wide and keep widening.
The
> table needs to be changed each time a new appliction comes up.
> I am thinking about keep a list for each application which stores the key
> values of the records the application has retrieved, instead of marking
the
> original table.
> Is there any better way to do it? What is the workaround you recommend?
> Thanks,
> Lixin
>

Problem: too many mark fields in the table

Many applications retrieve information from the same table. Each application
needs to remember the records it has retrieved before, so that it does not
retrieve these records in the following run any more.
The current solution is to put the different "flag" field for each
application on the table. The flag field for each application will be marked
by the application after it retrieves the records.
The problem is that the table becomes extremely wide and keep widening. The
table needs to be changed each time a new appliction comes up.
I am thinking about keep a list for each application which stores the key
values of the records the application has retrieved, instead of marking the
original table.
Is there any better way to do it? What is the workaround you recommend?
Thanks,
LixinThat is a strange requirement, that I haven't come across myself so far.
Yes, the workaround you have seems sensible.
Are your keys incrementing/follow a logical order? If so, your applications
could remember the last key retrieved and next time, look for keys greater
than the saved key.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"FLX" <nospam@.hotmail.com> wrote in message
news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
Many applications retrieve information from the same table. Each application
needs to remember the records it has retrieved before, so that it does not
retrieve these records in the following run any more.
The current solution is to put the different "flag" field for each
application on the table. The flag field for each application will be marked
by the application after it retrieves the records.
The problem is that the table becomes extremely wide and keep widening. The
table needs to be changed each time a new appliction comes up.
I am thinking about keep a list for each application which stores the key
values of the records the application has retrieved, instead of marking the
original table.
Is there any better way to do it? What is the workaround you recommend?
Thanks,
Lixin|||If rows may be retrieved by multiple applications and you need to keep a
history of this just put the data in a table:
CREATE TABLE ApplicationHistory (keycol INTEGER NOT NULL REFERENCES
YourTable (keycol), application CHAR(10) NOT NULL REFERENCES Applications
(application), appdate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY
KEY(keycol, application))
David Portas
SQL Server MVP
--|||Why can't the applications themselves internally store sets (or arrays, or
dictionarys, or whatever your favorite container is) containing identifiers
for rows they've already retrieved?
"FLX" <nospam@.hotmail.com> wrote in message
news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Many applications retrieve information from the same table. Each
application
> needs to remember the records it has retrieved before, so that it does not
> retrieve these records in the following run any more.
> The current solution is to put the different "flag" field for each
> application on the table. The flag field for each application will be
marked
> by the application after it retrieves the records.
> The problem is that the table becomes extremely wide and keep widening.
The
> table needs to be changed each time a new appliction comes up.
> I am thinking about keep a list for each application which stores the key
> values of the records the application has retrieved, instead of marking
the
> original table.
> Is there any better way to do it? What is the workaround you recommend?
> Thanks,
> Lixin
>|||Unfortunately, the key values are not in the logic order.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:u0w0VWvTEHA.808@.tk2msftngp13.phx.gbl...
> That is a strange requirement, that I haven't come across myself so far.
> Yes, the workaround you have seems sensible.
> Are your keys incrementing/follow a logical order? If so, your
applications
> could remember the last key retrieved and next time, look for keys greater
> than the saved key.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Many applications retrieve information from the same table. Each
application
> needs to remember the records it has retrieved before, so that it does not
> retrieve these records in the following run any more.
> The current solution is to put the different "flag" field for each
> application on the table. The flag field for each application will be
marked
> by the application after it retrieves the records.
> The problem is that the table becomes extremely wide and keep widening.
The
> table needs to be changed each time a new appliction comes up.
> I am thinking about keep a list for each application which stores the key
> values of the records the application has retrieved, instead of marking
the
> original table.
> Is there any better way to do it? What is the workaround you recommend?
> Thanks,
> Lixin
>
>|||Thanks. This is what I am going to do.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:w_CdnY17nKuK81XdRVn_iw@.giganews.com...
> If rows may be retrieved by multiple applications and you need to keep a
> history of this just put the data in a table:
> CREATE TABLE ApplicationHistory (keycol INTEGER NOT NULL REFERENCES
> YourTable (keycol), application CHAR(10) NOT NULL REFERENCES Applications
> (application), appdate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY
> KEY(keycol, application))
> --
> David Portas
> SQL Server MVP
> --
>|||The application runs and exits, therefore I can't use array or any other
internal data structures.
I think I can store the key values in the text file or in a database table.
The latter might be more convenient and efficient.
Thanks a lot.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Ko2BZvTEHA.3476@.tk2msftngp13.phx.gbl...
> Why can't the applications themselves internally store sets (or arrays, or
> dictionarys, or whatever your favorite container is) containing
identifiers
> for rows they've already retrieved?
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> application
not[vbcol=seagreen]
> marked
> The
key[vbcol=seagreen]
> the
>|||I think David Portas' solution is probably best; you can JOIN to the table
in order to filter for future retrievals of data, without reading from an
external source.
"FLX" <nospam@.hotmail.com> wrote in message
news:%23PkxrmvTEHA.544@.TK2MSFTNGP11.phx.gbl...
> The application runs and exits, therefore I can't use array or any other
> internal data structures.
> I think I can store the key values in the text file or in a database
table.
> The latter might be more convenient and efficient.
>|||Instead of separate 'mark' columns use single column (SelectedFlags int),
storing a bit mask of applications marking the row.
Ramon @. Havana Club
"FLX" <nospam@.hotmail.com> / :
news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Many applications retrieve information from the same table. Each
application
> needs to remember the records it has retrieved before, so that it does not
> retrieve these records in the following run any more.
> The current solution is to put the different "flag" field for each
> application on the table. The flag field for each application will be
marked
> by the application after it retrieves the records.
> The problem is that the table becomes extremely wide and keep widening.
The
> table needs to be changed each time a new appliction comes up.
> I am thinking about keep a list for each application which stores the key
> values of the records the application has retrieved, instead of marking
the
> original table.
> Is there any better way to do it? What is the workaround you recommend?
> Thanks,
> Lixin
>

Problem: sql server 2000 server low performance. thanks:)

I have a table of 60,000rows. There are 47 fields in the table. 43 of them
are nvarchar with length of 100. Two of them are big nvarchar with length of
1024 and 2048. an indexno field is int and the other is date. two primary
keys, one of them is indexno;the other is nvarchar.
Low performance:
1.using query of 'like', it shows timeout. I have to use indexno to control
the number of the rows i am querying.
2.When I use application program to query some length of bytes, it costs
quite a lot of minutes.
Any one can help?
Thanks in advance.treese
Do you have appropriate indexes for your table?
Using LIKE '%Something' may prevent from query optimizer to use an index.
"treesy" <treesy@.hostran.com.cn> wrote in message
news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
> I have a table of 60,000rows. There are 47 fields in the table. 43 of them
> are nvarchar with length of 100. Two of them are big nvarchar with length
of
> 1024 and 2048. an indexno field is int and the other is date. two primary
> keys, one of them is indexno;the other is nvarchar.
> Low performance:
> 1.using query of 'like', it shows timeout. I have to use indexno to
control
> the number of the rows i am querying.
> 2.When I use application program to query some length of bytes, it costs
> quite a lot of minutes.
> Any one can help?
> Thanks in advance.
>|||Thanks! Uri Dimant
I have tried to set up index on those frequently used fiels. It doesn't
work. Any other clues?
treesy
"Uri Dimant" <urid@.iscar.co.il> дÈëÏûÏ¢ÐÂÎÅ
:eSKuQfS9DHA.3364@.TK2MSFTNGP09.phx.gbl...
> treese
> Do you have appropriate indexes for your table?
> Using LIKE '%Something' may prevent from query optimizer to use an index.
>
>
> "treesy" <treesy@.hostran.com.cn> wrote in message
> news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
> > I have a table of 60,000rows. There are 47 fields in the table. 43 of
them
> > are nvarchar with length of 100. Two of them are big nvarchar with
length
> of
> > 1024 and 2048. an indexno field is int and the other is date. two
primary
> > keys, one of them is indexno;the other is nvarchar.
> >
> > Low performance:
> > 1.using query of 'like', it shows timeout. I have to use indexno to
> control
> > the number of the rows i am querying.
> > 2.When I use application program to query some length of bytes, it costs
> > quite a lot of minutes.
> > Any one can help?
> >
> > Thanks in advance.
> >
> >
>|||Did you see that after adding these indexes the optimizer was able to use
them?
"treesy" <treesy@.hostran.com.cn> wrote in message
news:utAb5uT9DHA.2656@.TK2MSFTNGP11.phx.gbl...
> Thanks! Uri Dimant
> I have tried to set up index on those frequently used fiels. It doesn't
> work. Any other clues?
> treesy
> "Uri Dimant" <urid@.iscar.co.il> дÈëÏûÏ¢ÐÂÎÅ
> :eSKuQfS9DHA.3364@.TK2MSFTNGP09.phx.gbl...
> > treese
> > Do you have appropriate indexes for your table?
> > Using LIKE '%Something' may prevent from query optimizer to use an
index.
> >
> >
> >
> >
> > "treesy" <treesy@.hostran.com.cn> wrote in message
> > news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
> > > I have a table of 60,000rows. There are 47 fields in the table. 43 of
> them
> > > are nvarchar with length of 100. Two of them are big nvarchar with
> length
> > of
> > > 1024 and 2048. an indexno field is int and the other is date. two
> primary
> > > keys, one of them is indexno;the other is nvarchar.
> > >
> > > Low performance:
> > > 1.using query of 'like', it shows timeout. I have to use indexno to
> > control
> > > the number of the rows i am querying.
> > > 2.When I use application program to query some length of bytes, it
costs
> > > quite a lot of minutes.
> > > Any one can help?
> > >
> > > Thanks in advance.
> > >
> > >
> >
> >
>|||If your like clause begins with a wildcard, it is unlikely that SQL will use
the index(in an index seek), however it may do an index scan of the leaf
level...
Look at the graphical showplan to see which access method the optimizer is
choosing.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"treesy" <treesy@.hostran.com.cn> wrote in message
news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
> I have a table of 60,000rows. There are 47 fields in the table. 43 of them
> are nvarchar with length of 100. Two of them are big nvarchar with length
of
> 1024 and 2048. an indexno field is int and the other is date. two primary
> keys, one of them is indexno;the other is nvarchar.
> Low performance:
> 1.using query of 'like', it shows timeout. I have to use indexno to
control
> the number of the rows i am querying.
> 2.When I use application program to query some length of bytes, it costs
> quite a lot of minutes.
> Any one can help?
> Thanks in advance.
>|||Hi, thank you all!
I have solved the problem! I split my big table into 5 tables. The
performance is good this time.
"Wayne Snyder" <wsnyder@.computeredservices.com> дÈëÏûÏ¢ÐÂÎÅ
:ufDflpV9DHA.972@.tk2msftngp13.phx.gbl...
> If your like clause begins with a wildcard, it is unlikely that SQL will
use
> the index(in an index seek), however it may do an index scan of the leaf
> level...
> Look at the graphical showplan to see which access method the optimizer is
> choosing.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "treesy" <treesy@.hostran.com.cn> wrote in message
> news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
> > I have a table of 60,000rows. There are 47 fields in the table. 43 of
them
> > are nvarchar with length of 100. Two of them are big nvarchar with
length
> of
> > 1024 and 2048. an indexno field is int and the other is date. two
primary
> > keys, one of them is indexno;the other is nvarchar.
> >
> > Low performance:
> > 1.using query of 'like', it shows timeout. I have to use indexno to
> control
> > the number of the rows i am querying.
> > 2.When I use application program to query some length of bytes, it costs
> > quite a lot of minutes.
> > Any one can help?
> >
> > Thanks in advance.
> >
> >
>

Problem: sql server 2000 server low performance. thanks:)

I have a table of 60,000rows. There are 47 fields in the table. 43 of them
are nvarchar with length of 100. Two of them are big nvarchar with length of
1024 and 2048. an indexno field is int and the other is date. two primary
keys, one of them is indexno;the other is nvarchar.
Low performance:
1.using query of 'like', it shows timeout. I have to use indexno to control
the number of the rows i am querying.
2.When I use application program to query some length of bytes, it costs
quite a lot of minutes.
Any one can help?
Thanks in advance.treese
Do you have appropriate indexes for your table?
Using LIKE '%Something' may prevent from query optimizer to use an index.
"treesy" <treesy@.hostran.com.cn> wrote in message
news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
> I have a table of 60,000rows. There are 47 fields in the table. 43 of them
> are nvarchar with length of 100. Two of them are big nvarchar with length
of
> 1024 and 2048. an indexno field is int and the other is date. two primary
> keys, one of them is indexno;the other is nvarchar.
> Low performance:
> 1.using query of 'like', it shows timeout. I have to use indexno to
control
> the number of the rows i am querying.
> 2.When I use application program to query some length of bytes, it costs
> quite a lot of minutes.
> Any one can help?
> Thanks in advance.
>|||Thanks! Uri Dimant
I have tried to set up index on those frequently used fiels. It doesn't
work. Any other clues?
treesy
"Uri Dimant" <urid@.iscar.co.il> д?
:eSKuQfS9DHA.3364@.TK2MSFTNGP09.phx.gbl...
> treese
> Do you have appropriate indexes for your table?
> Using LIKE '%Something' may prevent from query optimizer to use an index.
>
>
> "treesy" <treesy@.hostran.com.cn> wrote in message
> news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
them
length
> of
primary
> control
>|||Did you see that after adding these indexes the optimizer was able to use
them?
"treesy" <treesy@.hostran.com.cn> wrote in message
news:utAb5uT9DHA.2656@.TK2MSFTNGP11.phx.gbl...
> Thanks! Uri Dimant
> I have tried to set up index on those frequently used fiels. It doesn't
> work. Any other clues?
> treesy
> "Uri Dimant" <urid@.iscar.co.il> д?
> :eSKuQfS9DHA.3364@.TK2MSFTNGP09.phx.gbl...
index.
> them
> length
> primary
costs
>|||If your like clause begins with a wildcard, it is unlikely that SQL will use
the index(in an index seek), however it may do an index scan of the leaf
level...
Look at the graphical showplan to see which access method the optimizer is
choosing.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"treesy" <treesy@.hostran.com.cn> wrote in message
news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
> I have a table of 60,000rows. There are 47 fields in the table. 43 of them
> are nvarchar with length of 100. Two of them are big nvarchar with length
of
> 1024 and 2048. an indexno field is int and the other is date. two primary
> keys, one of them is indexno;the other is nvarchar.
> Low performance:
> 1.using query of 'like', it shows timeout. I have to use indexno to
control
> the number of the rows i am querying.
> 2.When I use application program to query some length of bytes, it costs
> quite a lot of minutes.
> Any one can help?
> Thanks in advance.
>|||Hi, thank you all!
I have solved the problem! I split my big table into 5 tables. The
performance is good this time.
"Wayne Snyder" <wsnyder@.computeredservices.com> д?
:ufDflpV9DHA.972@.tk2msftngp13.phx.gbl...
> If your like clause begins with a wildcard, it is unlikely that SQL will
use
> the index(in an index seek), however it may do an index scan of the leaf
> level...
> Look at the graphical showplan to see which access method the optimizer is
> choosing.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "treesy" <treesy@.hostran.com.cn> wrote in message
> news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
them
length
> of
primary
> control
>

Problem with Updatetext in SQL 2000 trying to concatenate multiple text fields

Hi all, I am creating a search table where the keywords field is made
up of several text fields and this is causing me some problems. I can
concatentate the text ok but i can't seem to concatenate matching
records here is the cursor loop. I'm not a fan of cursors but also
didn't see another way of achieving this.

declare @.ptr1 varbinary(16)
declare @.Ptr2 varbinary(16)
declare @.profileid int
declare @.x int

set @.profileid = 0

while @.profileid is not null
begin

select
@.profileid = min([id]),
@.ptr1 = MIN(textptr(text1))
from #holding
where [id] @.profileid

declare c2 cursor fast_forward for
select textptr(searchterms), datalength(searchterms)
from search
where search.[id] = @.profileid

open c2

fetch c2 into @.ptr2, @.x

while @.@.fetch_status = 0
begin
updatetext search.searchterms @.ptr2 null 0 #holding.text1 @.ptr1
fetch c2 into @.ptr2, @.x
end

close c2
deallocate c2

end

The #holding table contains the fields that i want to concatenate and
the search table is the resulting table. This example would loop
through search and find id 1 in search and then append another field
matching id 1 in holding then move onto the next field in turn going
through the whole table.

i.e.

search holding result after each loop
id text id text
1 abc 1 def abcdef
2 ghi 2 jkl ghijkl

When I run this, some of the records concatenate properly but most dont
with the same text being appended to the end of searchterms. i.e loads
of results will end up with jkl tagged onto the end. I can't figure out
when my loop is falliing over!!! Can anyone help?

DanDan (dan_barber2003@.hotmail.com) writes:

Quote:

Originally Posted by

select
@.profileid = min([id]),
@.ptr1 = MIN(textptr(text1))
from #holding
where [id] @.profileid

This does not look good. Is there are any reason to assume that text
pointers are aligned with id?

Either do

SELECT @.profileid = MIN(id)
FROM #holding
WHERE id @.profileid

SELECT @.ptr1 = textptr(text1) FROM #holding WHERE id = @.profileid

Or run a second cursor over #holding.

And, yeah, this is ugly, painful and no fun at all. But it's the only
alternative.

In SQL 2005 there are new data types that replaces text & co, and
which does not have all the restrictions of text. Or the need to
use UPDATETEXT...

--
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|||Dan wrote:

Quote:

Originally Posted by

When I run this, some of the records concatenate properly but most dont
with the same text being appended to the end of searchterms. i.e loads
of results will end up with jkl tagged onto the end. I can't figure out
when my loop is falliing over!!! Can anyone help?

I don't know /why/ the loop is doing the wrong thing, but adding some
debugging output after the UPDATETEXT line (e.g. SELECT * FROM SEARCH)
should at least reveal /when/ it's doing the wrong thing.

Also, I would seriously avoid columns named "id".|||Thanks Erland, you hit the nail on the head, i misunderstood what the
textptr did and thought both could be set at the same point. Hopefully
this will be the one and only time i have to use either text
concatenation or cursors. Is is the varchar(max) that replaces text in
SQL Server 2005?

Thanks again

Dan
Erland Sommarskog wrote:

Quote:

Originally Posted by

Dan (dan_barber2003@.hotmail.com) writes:

Quote:

Originally Posted by

select
@.profileid = min([id]),
@.ptr1 = MIN(textptr(text1))
from #holding
where [id] @.profileid

>
This does not look good. Is there are any reason to assume that text
pointers are aligned with id?
>
Either do
>
SELECT @.profileid = MIN(id)
FROM #holding
WHERE id @.profileid
>
SELECT @.ptr1 = textptr(text1) FROM #holding WHERE id = @.profileid
>
Or run a second cursor over #holding.
>
And, yeah, this is ugly, painful and no fun at all. But it's the only
alternative.
>
In SQL 2005 there are new data types that replaces text & co, and
which does not have all the restrictions of text. Or the need to
use UPDATETEXT...
>
>
--
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

|||Dan (dan_barber2003@.hotmail.com) writes:

Quote:

Originally Posted by

Is is the varchar(max) that replaces text in SQL Server 2005?

Yes.

And to make it clear, text is still there but deprecated. You will
have to make an explicit change to the tables to use the new goodies.

--
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

Problem with unit design

Hi

In my inventory system I have a table Units which has 3 fields - UnitID, UnitName, Quantity.

Suppose I purchase any product. I will input that in my Receives table with a Quantity and Unit field. When I issue a product I'll input that in Issues table with a Quantity and Unit field.

Now problem is I will purchase products with one unit like Box and issue product in another unit like piece. A box may contain some pieces.

How can I provide this facility with the following tables ? I want something like, when quering current stock, it should show -

Product Quantity

-- --

Pencil 16 Box and 3 Piece

And while issueing and purchasing I want to calculate the stock. In the Unit table I want to define each unit with the containing smallest quantity, Like -

Dozen 12

Packet 20

Need help!

Regards

Kapalic

Option 1:

Always store the peices on your table. Then you can represent it any unit.

Product QuantityInPieces DisplayUnit

Option 2:

You can change the datatype of Quantity from int to float.

[Box/Dozen].[Pieces]

12.5

12 Box 5 Pieces if displayunit = Box

The floor is always Box/Dozen it may be identified from the Unit column. The decimal numbers always indicate the pieces..

BUT I recommand to use OPTION 1.

Code Snippet

Declare @.Units Table
(
UnitId int,
UnitName varchar(100),
UnitQty int
)

Insert Into @.Units Values(1,'Box',20);
Insert Into @.Units Values(2,'Dozen',12);

Declare @.Product Table
(
product varchar(100),
qtyinpieces int,
displayunit int
)

Insert Into @.Product Values('One', 122,1);
Insert Into @.Product Values('Two', 234,2);
Insert Into @.Product Values('Three', 24,2);
Insert Into @.Product Values('Four', 50,1);

Select
P.product
,Convert(varchar,P.qtyinpieces/UnitQty) + ' ' + UnitName + Case When P.qtyinpieces%UnitQty <> 0 Then ' ' + Cast(P.qtyinpieces%UnitQty as Varchar) + ' Pieces' Else '' End
From
@.Product P Join @.Units U on P.displayunit=U.UnitId