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

I have a situation with a view. This view uses 2 or 3 or 4 tables to
retrieve data, doesn't matter I have several. Someone came to me and said
"During design, I forgot a field..." in one of the tables that make up this
view. So the field was inserted into one of the tables. Now when I execute
the view, the data is off one field AFTER the added field. What I mean by
that is I have an "Code" field and then a "Date" field after the newly added
field. The data from the "Code" field now shows up in the "Date" field, and
so on.
To fix this problem, I simply open the view, and resave it, and the problem
goes away. My questions are:
1) Am I doing something wrong in creating my views?
2) Is there a way to fix all my views instead of opening and resaving all.
Thanks for any help with this
BrijWhen you create a view,. SQL Server stores meta-data in syscolumns. This doesn't get updated if
change the underlying table. To refresh a view definition, you can use sp_refreshview. Most of us do
not use SELECT * in views, btw, instead we prefer to list all columns to avoid problems as table
structure changes...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brij Singh" <brijs@.telcorinc.com> wrote in message
news:4141d5d5$0$25239$9a6e19ea@.news.newshosting.com...
> I have a situation with a view. This view uses 2 or 3 or 4 tables to
> retrieve data, doesn't matter I have several. Someone came to me and said
> "During design, I forgot a field..." in one of the tables that make up this
> view. So the field was inserted into one of the tables. Now when I execute
> the view, the data is off one field AFTER the added field. What I mean by
> that is I have an "Code" field and then a "Date" field after the newly added
> field. The data from the "Code" field now shows up in the "Date" field, and
> so on.
> To fix this problem, I simply open the view, and resave it, and the problem
> goes away. My questions are:
> 1) Am I doing something wrong in creating my views?
> 2) Is there a way to fix all my views instead of opening and resaving all.
> Thanks for any help with this
> Brij
>