How to force a table into memory.

Hello,
On Sybase ASE, there is a command that will force the table and all its
contents into RAM, so that it can be queried faster. Is there such a
feature on SQL Server 2000?
Thanks
There is no command that will force table data into buffer cache. However,
you can mark a table as pinned so that SQL Server will keep table data in
buffer cache once read into memory. See DBCC PINTABLE in the Books Online
for details.
Note that it is very rare that this option will improve performance.
Microsoft SQL Server's buffer management algorithm will automatically keep
frequently used data in memory and makes the best use of available memory
resources.
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:O%23K12MF8FHA.3636@.TK2MSFTNGP09.phx.gbl...
> Hello,
> On Sybase ASE, there is a command that will force the table and all its
> contents into RAM, so that it can be queried faster. Is there such a
> feature on SQL Server 2000?
> Thanks
|||You could run a query like this:
SELECT COUNT(*) FROM MyTable (index=0)
If your SQL Server has enough memory, then this will cause the entire
table to be loaded into SQL Server's data buffer. If there is no memory
pressure, it will stay in memory.
There is an option called DBCC PINTABLE, but I would definitely advise
against it. It is best to let SQL Server manage the available memory.
Gert-Jan
Frank Rizzo wrote:
> Hello,
> On Sybase ASE, there is a command that will force the table and all its
> contents into RAM, so that it can be queried faster. Is there such a
> feature on SQL Server 2000?
> Thanks
|||Just want to add that the act of pinning a table will not actually place the
contents into memory. It just keeps them there after you access them. You
would have to do a select to get the data into cache. But I will also
emphasize what Dan stated in that this usually is not the right thing to do.
And this option is going away in SQL2005.
Andrew J. Kelly SQL MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23JhFDXF8FHA.1028@.TK2MSFTNGP11.phx.gbl...
> There is no command that will force table data into buffer cache.
> However, you can mark a table as pinned so that SQL Server will keep table
> data in buffer cache once read into memory. See DBCC PINTABLE in the
> Books Online for details.
> Note that it is very rare that this option will improve performance.
> Microsoft SQL Server's buffer management algorithm will automatically keep
> frequently used data in memory and makes the best use of available memory
> resources.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Frank Rizzo" <none@.none.com> wrote in message
> news:O%23K12MF8FHA.3636@.TK2MSFTNGP09.phx.gbl...
>

How to force a table into memory.

Hello,
On Sybase ASE, there is a command that will force the table and all its
contents into RAM, so that it can be queried faster. Is there such a
feature on SQL Server 2000?
ThanksThere is no command that will force table data into buffer cache. However,
you can mark a table as pinned so that SQL Server will keep table data in
buffer cache once read into memory. See DBCC PINTABLE in the Books Online
for details.
Note that it is very rare that this option will improve performance.
Microsoft SQL Server's buffer management algorithm will automatically keep
frequently used data in memory and makes the best use of available memory
resources.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:O%23K12MF8FHA.3636@.TK2MSFTNGP09.phx.gbl...
> Hello,
> On Sybase ASE, there is a command that will force the table and all its
> contents into RAM, so that it can be queried faster. Is there such a
> feature on SQL Server 2000?
> Thanks|||You could run a query like this:
SELECT COUNT(*) FROM MyTable (index=0)
If your SQL Server has enough memory, then this will cause the entire
table to be loaded into SQL Server's data buffer. If there is no memory
pressure, it will stay in memory.
There is an option called DBCC PINTABLE, but I would definitely advise
against it. It is best to let SQL Server manage the available memory.
Gert-Jan
Frank Rizzo wrote:
> Hello,
> On Sybase ASE, there is a command that will force the table and all its
> contents into RAM, so that it can be queried faster. Is there such a
> feature on SQL Server 2000?
> Thanks|||Just want to add that the act of pinning a table will not actually place the
contents into memory. It just keeps them there after you access them. You
would have to do a select to get the data into cache. But I will also
emphasize what Dan stated in that this usually is not the right thing to do.
And this option is going away in SQL2005.
Andrew J. Kelly SQL MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23JhFDXF8FHA.1028@.TK2MSFTNGP11.phx.gbl...
> There is no command that will force table data into buffer cache.
> However, you can mark a table as pinned so that SQL Server will keep table
> data in buffer cache once read into memory. See DBCC PINTABLE in the
> Books Online for details.
> Note that it is very rare that this option will improve performance.
> Microsoft SQL Server's buffer management algorithm will automatically keep
> frequently used data in memory and makes the best use of available memory
> resources.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Frank Rizzo" <none@.none.com> wrote in message
> news:O%23K12MF8FHA.3636@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> On Sybase ASE, there is a command that will force the table and all its
>> contents into RAM, so that it can be queried faster. Is there such a
>> feature on SQL Server 2000?
>> Thanks
>

How to force a table into memory.

Hello,
On Sybase ASE, there is a command that will force the table and all its
contents into RAM, so that it can be queried faster. Is there such a
feature on SQL Server 2000?
ThanksThere is no command that will force table data into buffer cache. However,
you can mark a table as pinned so that SQL Server will keep table data in
buffer cache once read into memory. See DBCC PINTABLE in the Books Online
for details.
Note that it is very rare that this option will improve performance.
Microsoft SQL Server's buffer management algorithm will automatically keep
frequently used data in memory and makes the best use of available memory
resources.
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:O%23K12MF8FHA.3636@.TK2MSFTNGP09.phx.gbl...
> Hello,
> On Sybase ASE, there is a command that will force the table and all its
> contents into RAM, so that it can be queried faster. Is there such a
> feature on SQL Server 2000?
> Thanks|||You could run a query like this:
SELECT COUNT(*) FROM MyTable (index=0)
If your SQL Server has enough memory, then this will cause the entire
table to be loaded into SQL Server's data buffer. If there is no memory
pressure, it will stay in memory.
There is an option called DBCC PINTABLE, but I would definitely advise
against it. It is best to let SQL Server manage the available memory.
Gert-Jan
Frank Rizzo wrote:
> Hello,
> On Sybase ASE, there is a command that will force the table and all its
> contents into RAM, so that it can be queried faster. Is there such a
> feature on SQL Server 2000?
> Thanks|||Just want to add that the act of pinning a table will not actually place the
contents into memory. It just keeps them there after you access them. You
would have to do a select to get the data into cache. But I will also
emphasize what Dan stated in that this usually is not the right thing to do.
And this option is going away in SQL2005.
Andrew J. Kelly SQL MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23JhFDXF8FHA.1028@.TK2MSFTNGP11.phx.gbl...
> There is no command that will force table data into buffer cache.
> However, you can mark a table as pinned so that SQL Server will keep table
> data in buffer cache once read into memory. See DBCC PINTABLE in the
> Books Online for details.
> Note that it is very rare that this option will improve performance.
> Microsoft SQL Server's buffer management algorithm will automatically keep
> frequently used data in memory and makes the best use of available memory
> resources.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Frank Rizzo" <none@.none.com> wrote in message
> news:O%23K12MF8FHA.3636@.TK2MSFTNGP09.phx.gbl...
>sql

How to find the NULL counts and non NULL counts?

Dear experts,
I am finding a LOT of rows with NULL columns in the Sybase
tables I'm querying.
Say, there is a table, with 100 rows.
25 rows are NULL
75 rows are NOT NULL.
What I'm trying to eliminate is:
select count(*)
from some_table
where fieldx is null
and then running the next query:
select count(*)
from some_table
where fieldx is NOT null
What functions can I use to run a query such as:
select count(f1( fieldx ),) AS count_of_null,
count(f2( fieldx ) ) AS count_of_not_null,
count(*)
from some_table
that would return one row that would look like:
count_of_null count_of_not_null count(*)
25 75 100
I know there is the ISNULL function. But that converts the NULL
to an actual number. Could I use other functions in conjunction with
it?
Thanks a lot!<dba_222@.yahoo.com> wrote in message
news:1162305541.838434.188080@.f16g2000cwb.googlegroups.com...
> Dear experts,
> I am finding a LOT of rows with NULL columns in the Sybase
> tables I'm querying.
> Say, there is a table, with 100 rows.
> 25 rows are NULL
> 75 rows are NOT NULL.
>
> What I'm trying to eliminate is:
> select count(*)
> from some_table
> where fieldx is null
> and then running the next query:
> select count(*)
> from some_table
> where fieldx is NOT null
>
> What functions can I use to run a query such as:
> select count(f1( fieldx ),) AS count_of_null,
> count(f2( fieldx ) ) AS count_of_not_null,
> count(*)
> from some_table
>
> that would return one row that would look like:
>
> count_of_null count_of_not_null count(*)
> 25 75 100
>
> I know there is the ISNULL function. But that converts the NULL
> to an actual number. Could I use other functions in conjunction with
> it?
SELECT COUNT(fieldx) AS count_of_not_null,
COUNT(*) - COUNT(fieldx) AS count_of_null
FROM some_table
Is how you would do it with MS SQL Server. Should also work with Sybase,
but I don't have a Sybase server to test on.|||SELECT COUNT(*) as TotalRows,
COUNT(Col1) as Col1_NotNull,
COUNT(Col2) as Col2_NotNull,
COUNT(Col3) as Col3_NotNull
FROM TableWithNulls
The first column tells you the total number of rows in the table, the
other columns the number of non-nulls for the column specified. Not
that you can deal with all the columns in one SELECT.
Roy Harvey
Beacon Falls, CT
On 31 Oct 2006 06:39:01 -0800, dba_222@.yahoo.com wrote:
>Dear experts,
>I am finding a LOT of rows with NULL columns in the Sybase
>tables I'm querying.
>Say, there is a table, with 100 rows.
>25 rows are NULL
>75 rows are NOT NULL.
>
>What I'm trying to eliminate is:
>select count(*)
>from some_table
>where fieldx is null
>and then running the next query:
>select count(*)
>from some_table
>where fieldx is NOT null
>
>What functions can I use to run a query such as:
>select count(f1( fieldx ),) AS count_of_null,
> count(f2( fieldx ) ) AS count_of_not_null,
> count(*)
>from some_table
>
>that would return one row that would look like:
>
>count_of_null count_of_not_null count(*)
>25 75 100
>
>I know there is the ISNULL function. But that converts the NULL
>to an actual number. Could I use other functions in conjunction with
>it?
>
>Thanks a lot!|||Brilliant!
I really should have thought of that.
But it was a looong tedious day yesterday.
Thanks a lot!
Mike C# wrote:
> <dba_222@.yahoo.com> wrote in message
> news:1162305541.838434.188080@.f16g2000cwb.googlegroups.com...
> > Dear experts,
> >
> > I am finding a LOT of rows with NULL columns in the Sybase
> > tables I'm querying.
> >
> > Say, there is a table, with 100 rows.
> > 25 rows are NULL
> > 75 rows are NOT NULL.
> >
> >
> > What I'm trying to eliminate is:
> >
> > select count(*)
> > from some_table
> > where fieldx is null
> >
> > and then running the next query:
> >
> > select count(*)
> > from some_table
> > where fieldx is NOT null
> >
> >
> > What functions can I use to run a query such as:
> >
> > select count(f1( fieldx ),) AS count_of_null,
> > count(f2( fieldx ) ) AS count_of_not_null,
> > count(*)
> > from some_table
> >
> >
> > that would return one row that would look like:
> >
> >
> > count_of_null count_of_not_null count(*)
> >
> > 25 75 100
> >
> >
> >
> > I know there is the ISNULL function. But that converts the NULL
> > to an actual number. Could I use other functions in conjunction with
> > it?
> SELECT COUNT(fieldx) AS count_of_not_null,
> COUNT(*) - COUNT(fieldx) AS count_of_null
> FROM some_table
> Is how you would do it with MS SQL Server. Should also work with Sybase,
> but I don't have a Sybase server to test on.

how to find median in sybase /sql server (Using query)

Hi Experters
How to find out the medain in sybase using SQL Qvery

With thanks

Manjunath

Quote:

Originally Posted by mnjgoin

Hi Experters
How to find out the medain in sybase using SQL Qvery

With thanks

Manjunath

Moved from articles to forum section.

P.S. Welcome to TSDN.