MDAC and Oracle9

Hei guys, today we have encountered a problem with connectivity from MS SQL
server to ORACLE. We have been using MDAC for linked Oracle DB and all
worked fine on ORACLE8 and early ORACLE9 DBs (for example 9.0.1.0.0). Some
days ago, the ORACLE DB has been upgraded to 9.2.0.4.0 and the link doesn't
work any longer. It is created (even dropped and recreated), but when we try
SELECT from whatever table, it returns known Error 7356: "OLE DB provider
'MSDASQL' supplied inconsistent metadata for a column.Metadata information
was changed at execution time......"
We used time to go through newsgroups and found references to this error
message. For example
http://support.microsoft.com/defaul...kb;en-us;264012 or
http://groups.google.com/groups?hl=...hreadm=UORMZEmf
DHA.2332%40cpmsftngxa06.phx.gbl&rnum=9&prev=/groups%3Fhl%3Dcs%26lr%3D%26ie%3
DUTF-8%26oe%3DUTF-8%26selm%3DUORMZEmfDHA.2332%2540cpmsftngxa06.phx.gbl%26rnu
m%3D9
or
http://groups.google.com/groups?q=E...-8&oe=UTF-8&sel
m=OW5q0QuLDHA.1552%40TK2MSFTNGP10.phx.gbl&rnum=3 - just our problem but with
newer ORACLE vrsion.
All suggestions concern registry. We checked the registry but there are no
registry entries for HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
DTC\MTxOCI on
the server. In addition, ORACLE 9 is not mentioned in articles we've found.
We run SQL Server 2000 and ORACLE 9.2.0.4.0
Any ideas how to fix this problem? Thanks in advance.
JiriHello. I am seeing a similar problem. I have SQL 2000 server running
Oracle 9i client 9.2.0.1 and linking to an Oracle 9.2.0.3 server. I
can create the link to the Oracle server, but when I do a SELECT
against the Oracle tables I get the error 7356 with the MSDAORA
provider, same Metadata references.
I followed this article but it still didn't work:
http://www.databasejournal.com/feat...cle.php/3290801
I have not found a solution yet either. I tried installing MDAC 2.8
but that didn't make a difference.
"feli" <jife@.email.cz> wrote in message news:<erFkIG3BEHA.684@.tk2msftngp13.phx.gbl>...[colo
r=darkred]
> Hei guys, today we have encountered a problem with connectivity from MS SQ
L
> server to ORACLE. We have been using MDAC for linked Oracle DB and all
> worked fine on ORACLE8 and early ORACLE9 DBs (for example 9.0.1.0.0). Some
> days ago, the ORACLE DB has been upgraded to 9.2.0.4.0 and the link doesn'
t
> work any longer. It is created (even dropped and recreated), but when we t
ry
> SELECT from whatever table, it returns known Error 7356: "OLE DB provider
> 'MSDASQL' supplied inconsistent metadata for a column.Metadata information
> was changed at execution time......"
> We used time to go through newsgroups and found references to this error
> message. For example
> http://support.microsoft.com/defaul...kb;en-us;264012 or
> [url]http://groups.google.com/groups?hl=cs&lr=&ie=UTF-8&oe=UTF-8&threadm=UORMZEmf[/ur
l]
> DHA.2332%40cpmsftngxa06.phx.gbl&rnum=9&prev=/groups%3Fhl%3Dcs%26lr%3D%26ie
%3
> DUTF-8%26oe%3DUTF-8%26selm%3DUORMZEmfDHA.2332%2540cpmsftngxa06.phx.gbl%26r
nu
> m%3D9
> or
> [url]http://groups.google.com/groups?q=Error+7356&hl=cs&lr=&ie=UTF-8&oe=UTF-8&sel[/ur
l]
> m=OW5q0QuLDHA.1552%40TK2MSFTNGP10.phx.gbl&rnum=3 - just our problem but wi
th
> newer ORACLE vrsion.
> All suggestions concern registry. We checked the registry but there are no
> registry entries for HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
DTC\MTxOCI on
> the server. In addition, ORACLE 9 is not mentioned in articles we've found
.
> We run SQL Server 2000 and ORACLE 9.2.0.4.0
> Any ideas how to fix this problem? Thanks in advance.
> Jiri[/color]|||We are having, apparently, the same problem here. Also with Oracle
9.2.0.4.0.
We have tried Oracle's native driver, the MS OLE DB driver for Oracle
and the MS OLE DB Driver for ODBC Drivers using either the Microsoft
or Oracle ODBC drivers. We have had some success retriving data with
a linked server connection using Microsoft OLE DB Provider for ODBC
Drivers using Microsoft ODBC for Oracle {version 2.573.9030.00}. [
Not
sure what MDAC provided this]
All other methods fail with error messages like:
Server: Msg 7356, Level 16, State 1, Line 2
OLE DB provider 'OraOLEDB.Oracle' supplied inconsistent metadata for a
column. Metadata information was changed at execution time.
OLE DB error trace [Non-interface error: Column 'MIN_EXTENTS'
(compile-time ordinal 12) of object '"SYS"."ALL_TABLES"' was reported
to have a DBTYPE of 130 at compile time and 5 at run time].
or
Server: Msg 7318, Level 16, State 1, Line 2
OLE DB provider 'MSDASQL' returned an invalid column definition.
OLE DB error trace [Non-interface error: OLE/DB provider returned an
invalid column definition.].
We are getting error messages like this from W2K Server SQL Server
2000 machines whether the Oracle 8i or 9i drivers are installed if the
linked target is Oracle 9.2.0.4.0.
We suspect that SQL Server 2000 is using old methods to communicate
with Oracle that are not appropriate for Oracle 9.2.0.4.0 and perhaps
other releases around that version. Since Oracle is working fine and
connections to it to retrieve data using a Windows script file are
successful [using ODBC and UDL files], my current suspicion is that a
patch will be required for MS SQL Server 2000 to work with more recent
versions of Oracle.
Anyone else having this experience or perhaps information on a fix for
this problem?|||Has anyone resolved this error?

quote:

---

[i] ... Error 7356: "OLE DB provider
'MSDASQL' supplied inconsistent metadata for a column.Metadata information w
as changed at execution time......"
[/B]

---

|||"feli" <jife@.email.cz> wrote in message
news:erFkIG3BEHA.684@.tk2msftngp13.phx.gbl...
> Hei guys, today we have encountered a problem with connectivity from MS
SQL
> server to ORACLE. We have been using MDAC for linked Oracle DB and all
> worked fine on ORACLE8 and early ORACLE9 DBs (for example 9.0.1.0.0). Some
> days ago, the ORACLE DB has been upgraded to 9.2.0.4.0 and the link
doesn't
> work any longer. It is created (even dropped and recreated), but when we
try
> SELECT from whatever table, it returns known Error 7356: "OLE DB provider
> 'MSDASQL' supplied inconsistent metadata for a column.Metadata information
> was changed at execution time......"
Do you have to use the MS supplied oledb driver - the Oracle supplied
provider works just fine for me.
Niall Litchfield
Oracle DBA
Audit Commission UK
http://www.niall.litchfield.dial.pipex.com/

MDAC 2.8 not accepting nulls

Hi,
My Windows XP client machines are using MDAC 2.8 SP1 to connect to a
clustered SqlServer 2005 database. SqlServer definition says null values are
acceptable in foreign key columns. But MDAC 2.8 SP1 says nulls are not
accepted. The same application connecting to the same database/table from
other clients using up to MDAC 2.7 do not have any problem. Is this a bug? I
f
so any idea on ways to get around it?
Any input is appreciated.
Thanks.
--
SankarVSWhat object are you using in ADODB to update the table?
Would you mind posting some example code? I am trying to reproduce.
Thanks,
Adam
"sankarvs" wrote:

> Hi,
> My Windows XP client machines are using MDAC 2.8 SP1 to connect to a
> clustered SqlServer 2005 database. SqlServer definition says null values a
re
> acceptable in foreign key columns. But MDAC 2.8 SP1 says nulls are not
> accepted. The same application connecting to the same database/table from
> other clients using up to MDAC 2.7 do not have any problem. Is this a bug?
If
> so any idea on ways to get around it?
> Any input is appreciated.
> Thanks.
> --
> SankarVS|||The application uses ODBC (System DSN) connection to the SqlServer database.
A simple insert statement fails:
insert into temp1( gf_code, gf_key ) values( :sGFCode, :sGFKey )
where gf_key is a foreign key linked to another string column
SankarVS
"Goose" wrote:
[vbcol=seagreen]
> What object are you using in ADODB to update the table?
> Would you mind posting some example code? I am trying to reproduce.
> Thanks,
> Adam
> "sankarvs" wrote:
>

MDAC 2.8 not accepting nulls

Hi,
My Windows XP client machines are using MDAC 2.8 SP1 to connect to a
clustered SqlServer 2005 database. SqlServer definition says null values are
acceptable in foreign key columns. But MDAC 2.8 SP1 says nulls are not
accepted. The same application connecting to the same database/table from
other clients using up to MDAC 2.7 do not have any problem. Is this a bug? If
so any idea on ways to get around it?
Any input is appreciated.
Thanks.
SankarVS
What object are you using in ADODB to update the table?
Would you mind posting some example code? I am trying to reproduce.
Thanks,
Adam
"sankarvs" wrote:

> Hi,
> My Windows XP client machines are using MDAC 2.8 SP1 to connect to a
> clustered SqlServer 2005 database. SqlServer definition says null values are
> acceptable in foreign key columns. But MDAC 2.8 SP1 says nulls are not
> accepted. The same application connecting to the same database/table from
> other clients using up to MDAC 2.7 do not have any problem. Is this a bug? If
> so any idea on ways to get around it?
> Any input is appreciated.
> Thanks.
> --
> SankarVS
|||The application uses ODBC (System DSN) connection to the SqlServer database.
A simple insert statement fails:
insert into temp1( gf_code, gf_key ) values( :sGFCode, :sGFKey )
where gf_key is a foreign key linked to another string column
SankarVS
"Goose" wrote:
[vbcol=seagreen]
> What object are you using in ADODB to update the table?
> Would you mind posting some example code? I am trying to reproduce.
> Thanks,
> Adam
> "sankarvs" wrote:

MCMS Content inventory

Hi,

I would like to know how does CMS stores content inventory in SQLserver.?

Example-If I create or migrate an application to CMS in sqlDB how does information is stored ?

Regards

T.Gunaseelan

what is MCMS? is this part of the "Dynamics" platform? And how does this related to SQLCLR technology?

Derek

|||

Hi,

MCMS stands for Microsoft Content Management Server.

The MCMS handles all the web contents using SQL2000 DataBase . I would like to know how it is handling or managing the content repsitory or inventory in the DB?

Regards,

Guna

|||

"I would like to know how it is handling or managing the content repsitory or inventory in the DB?"

This questions sounds more specific to the MCMS product than SQL Server.

Maximum Table Size

We have been recommended by our database designers that 20million rows is th
e
maximum number of rows a data warehouse should be on SQLServer.
What is the opinion of you guys on this benchmark? Have you seen tables
bigger than that? Did you notice any performance impact.
Our table is at 14million rows and is about 14gb, we are analysing all
opportunities at present and would like a second opinion please.
Regards,
Marc> What is the opinion of you guys on this benchmark? Have you seen tables
> bigger than that?
YES!

> Did you notice any performance impact.
There are always performance concerns. The guideline should be more focused
on proper indexing and row widths, rather than number of rows.
A|||My recommendation is to sack your database designers and hire some people
who know what they are talking about. Seriously. The 20 million row comment
is pure rubbish.
There are SQL Server databases (not many though, because not many companies
have that much data) that have tables with billions of rows. 20 million rows
should not present any problems at all in a properly designed SQL Server
database, and I have seen numerous tables that contain that number of rows
or more.14 GB in itself is not much for a SQL Server database, but if the
table only contains 14 million rows, that is 1000 bytes per row, a rather
large rowsize for a datawarehouse fact table. A datawarehouse fact table
should almost exclusively contain numeric columns, and these columns should
be designed to be as small as possible, usually they are 4 byte integers. If
that is the case you are talking about 250 columns in that table, which is
possible from a proper logical design point of view, but sounds a bit much
too me. SQL Server can of course easily handle 250 columns in a table, it is
usually an indication of a bad database design though, to have that many
columns in one table.
Jacco Schalkwijk
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:55BE7712-1CAE-4E6A-9EEA-05286669D9D2@.microsoft.com...
> We have been recommended by our database designers that 20million rows is
> the
> maximum number of rows a data warehouse should be on SQLServer.
> What is the opinion of you guys on this benchmark? Have you seen tables
> bigger than that? Did you notice any performance impact.
> Our table is at 14million rows and is about 14gb, we are analysing all
> opportunities at present and would like a second opinion please.
> Regards,
> Marc|||Depends on a lot of things
for example the wider the tables the slower the selects (less rows on a page
)
CPU, Disk etc etc etc
http://sqlservercode.blogspot.com/
"marcmc" wrote:

> We have been recommended by our database designers that 20million rows is
the
> maximum number of rows a data warehouse should be on SQLServer.
> What is the opinion of you guys on this benchmark? Have you seen tables
> bigger than that? Did you notice any performance impact.
> Our table is at 14million rows and is about 14gb, we are analysing all
> opportunities at present and would like a second opinion please.
> Regards,
> Marc|||> We have been recommended by our database designers that 20million rows is theed">
> maximum number of rows a data warehouse should be on SQLServer.
If your database designers say this then what do they recommend you do about
it? Sounds like either a sales pitch or a lame excuse for poor performance.
There is no fixed limit on the number of rows in a SQL Server table. Even if
there were such a limitation it would be irrelevant in a DW scenario because
you can implement a partitioned view across many tables on many different
devices.
14GB is a fairly modest sized data warehouse. DW on the terabyte scale is
pretty normal in SQL Server today.
David Portas
SQL Server MVP
--|||thx jacco,
we have a table 95 columns wide with 3228 characters.
the table is actually 18million rows(my mistake), we have had some
performance issues with it such as when linked to other large
tables(4million+ records).
Do you think the number of records or the row size is more important
Most of our columns are integers [ID's] but we do have some with
smalldatetime and one with a 19 char length!
we have people coming to talk teradata/oracle etc etc but no one has yet
identified the database design flaws yet. How can we look at this in more
detail especially in respect to the 20million maximum rowcount table size!
Appreciate your input
Marc|||As David emphasised, there is no limit to the number of rows in a table in
SQL Server. That the (flawed) design and lack of performance tuning skills
of your database designers doesn't allow for a reasonable performance with
20 millions row on your system, is their fault and not SQL Server's.
It's almost impossible to give proper advice in a newsgroup post on database
design flaws and performance issues in what seems a reasonably large system.
The best thing you can do is get an independent consultant in for a few days
to check the system. It will cost some money upfront, but will save you
loads in the long run. A number of MVPs work as independent consultants, and
I can forward your details to them if you contact me offline.
Jacco Schalkwijk
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:40A756CA-0E23-4F44-8EE0-D742C1EF9C99@.microsoft.com...
> thx jacco,
> we have a table 95 columns wide with 3228 characters.
> the table is actually 18million rows(my mistake), we have had some
> performance issues with it such as when linked to other large
> tables(4million+ records).
> Do you think the number of records or the row size is more important
> Most of our columns are integers [ID's] but we do have some with
> smalldatetime and one with a 19 char length!
> we have people coming to talk teradata/oracle etc etc but no one has yet
> identified the database design flaws yet. How can we look at this in more
> detail especially in respect to the 20million maximum rowcount table size!
> Appreciate your input
> Marc
>|||> Do you think the number of records or the row size is more important
Neither. Optimal design and implementation are immeasurably more important.
Lousy design can destroy performance with only a few thousand rows. Since yo
u
(or your namesake) just stated in another thread that you "always use
cursors" you may not need to look any further than that for an explanation o
f
why you can't scale.
David Portas
SQL Server MVP
--
"marcmc" wrote:

> thx jacco,
> we have a table 95 columns wide with 3228 characters.
> the table is actually 18million rows(my mistake), we have had some
> performance issues with it such as when linked to other large
> tables(4million+ records).
> Do you think the number of records or the row size is more important
> Most of our columns are integers [ID's] but we do have some with
> smalldatetime and one with a 19 char length!
> we have people coming to talk teradata/oracle etc etc but no one has yet
> identified the database design flaws yet. How can we look at this in more
> detail especially in respect to the 20million maximum rowcount table size!
> Appreciate your input
> Marc
>|||To add to what everyone else says, after design (and a 95 column wide table
may or may not be a design issue, depending on if this is a fact table or
not (if so, then 80+ dimensions may be an issue, but I degress) the hardware
is the key. Too often people who claim some fixed number as a maximum don't
think of a Windows server as scalable. A lot will depend on your disk
subsystem for example. You might be doing your work on IDE drives, or a
slow Raid-5 array, or one of unlimited possibilites. When you start to
approact a large size/many users, the cost does go up greatly, but it is
likely not SQL Server's fault (as these vendors may tell you it is, since
they have a vested interest in you going to Oracle on their hardware)
As Jacco says in particular, you need someone to look at all of these
factors independent of a hardward/software vendor (ie they aren't
salespeople!) to get a valid look at what is going on.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:40A756CA-0E23-4F44-8EE0-D742C1EF9C99@.microsoft.com...
> thx jacco,
> we have a table 95 columns wide with 3228 characters.
> the table is actually 18million rows(my mistake), we have had some
> performance issues with it such as when linked to other large
> tables(4million+ records).
> Do you think the number of records or the row size is more important
> Most of our columns are integers [ID's] but we do have some with
> smalldatetime and one with a 19 char length!
> we have people coming to talk teradata/oracle etc etc but no one has yet
> identified the database design flaws yet. How can we look at this in more
> detail especially in respect to the 20million maximum rowcount table size!
> Appreciate your input
> Marc
>

Maximum number of records per second that can be inserted into SQLServer 2000.

Summary: Maximum number of records per second that can be inserted into
SQLServer 2000.

I am trying to insert hundreds (preferably even thousands) of records
per second in to SQLServer table (see below) but I am getting the
following error in the Windows Event Viewer Application log file:

"Insufficent Memory....."

And very few records were inserted and no errors where sent back via
the JDBC.

By removing the indexes on the table we have stopped getting the error
message and have managed to load the table at 300 records per second.
However I have couple of questions:

1) Are the indexes definitely to blame for this error and is there
anyway of getting around this problem i.e. keeping the indexes in place
when inserting?

2) How should I configure SQLServer to maximise the speed of
inserts?

3) What is the limiting factor for inserting into SQLServer?

4) Does anyone know of any metrics for inserting records? At want
point should we consider load balancing across DBs.

I currently populate 1.6 million records into this table. Once again
thanks for the help!!

CREATE TABLE [result] (

[id] numeric(20,0) NOT NULL,

[iid] numeric(20,0) NOT NULL,

[sid] numeric(20,0) NOT NULL,

[pn] varchar(30) NOT NULL,

[tid] numeric(20,0) NOT NULL,

[stid] numeric(6,0) NOT NULL,

[cid] numeric(20,0) NOT NULL,

[start] datetime NOT NULL,

[ec] numeric(5,0) NOT NULL,

)

GO

CREATE INDEX [ix_resultstart]

ON [dbo].[result]([start])

GO

CREATE INDEX [indx_result_1]

ON [dbo].[result]([id], [sid], [start], [ec])

GO

CREATE INDEX [indx_result_3]

ON [dbo].[result]([id], [sid], [stid], [start])

GO

CREATE INDEX [indx_result_2]

ON [dbo].[result]([id], [sid], [start])

GOHi

Is it possible to set this up so that you use DTS to do this insert? Write
the data to a text file and then run the DTS package. Perhaps start a
scheduled job that runs the DTS Package.

This would be considerably faster than individual insert statements.

--
-Dick Christoph
<JSParker1@.hotmail.co.uk> wrote in message
news:1144416921.518307.31270@.j33g2000cwa.googlegro ups.com...
> Summary: Maximum number of records per second that can be inserted into
> SQLServer 2000.
> I am trying to insert hundreds (preferably even thousands) of records
> per second in to SQLServer table (see below) but I am getting the
> following error in the Windows Event Viewer Application log file:
> "Insufficent Memory....."
> And very few records were inserted and no errors where sent back via
> the JDBC.
> By removing the indexes on the table we have stopped getting the error
> message and have managed to load the table at 300 records per second.
> However I have couple of questions:
> 1) Are the indexes definitely to blame for this error and is there
> anyway of getting around this problem i.e. keeping the indexes in place
> when inserting?
> 2) How should I configure SQLServer to maximise the speed of
> inserts?
> 3) What is the limiting factor for inserting into SQLServer?
> 4) Does anyone know of any metrics for inserting records? At want
> point should we consider load balancing across DBs.
>
> I currently populate 1.6 million records into this table. Once again
> thanks for the help!!
>
> CREATE TABLE [result] (
> [id] numeric(20,0) NOT NULL,
> [iid] numeric(20,0) NOT NULL,
> [sid] numeric(20,0) NOT NULL,
> [pn] varchar(30) NOT NULL,
> [tid] numeric(20,0) NOT NULL,
> [stid] numeric(6,0) NOT NULL,
> [cid] numeric(20,0) NOT NULL,
> [start] datetime NOT NULL,
> [ec] numeric(5,0) NOT NULL,
> )
> GO
> CREATE INDEX [ix_resultstart]
> ON [dbo].[result]([start])
> GO
> CREATE INDEX [indx_result_1]
> ON [dbo].[result]([id], [sid], [start], [ec])
> GO
> CREATE INDEX [indx_result_3]
> ON [dbo].[result]([id], [sid], [stid], [start])
> GO
> CREATE INDEX [indx_result_2]
> ON [dbo].[result]([id], [sid], [start])
> GO|||You could also pass in an XML file and directly insert its contents
into a table. I've found this method several times faster (in my case,
8x) than calling an stored procedure for each record.|||<JSParker1@.hotmail.co.uk> wrote in message
news:1144416921.518307.31270@.j33g2000cwa.googlegro ups.com...
> Summary: Maximum number of records per second that can be inserted into
> SQLServer 2000.

"Quite a few". I don't know what the limits aer and Id oubt anyone can say
for sure. But you can look up the TPC benchmarks for ideas.

> I am trying to insert hundreds (preferably even thousands) of records
> per second in to SQLServer table (see below) but I am getting the
> following error in the Windows Event Viewer Application log file:
> "Insufficent Memory....."
> And very few records were inserted and no errors where sent back via
> the JDBC.

I believe JDBC has (had?) some performance issues, so it may not be your
best choice.

> By removing the indexes on the table we have stopped getting the error
> message and have managed to load the table at 300 records per second.
> However I have couple of questions:
> 1) Are the indexes definitely to blame for this error and is there
> anyway of getting around this problem i.e. keeping the indexes in place
> when inserting?

Well, not sure they are "definitely" to blame, but they will slow down DML
statements since they increase the overhead.

But there's ways around this.

> 2) How should I configure SQLServer to maximise the speed of
> inserts?

Well, for one thing, "how do you need to do it" BULK INSERT or BCP will be
far faster than individual inserts.

Inserting a row at a time will be slower than "N". What's N? It depends.
To many and the commits will take too long and slow things down. To few and
you're committing more often than needed.

You can try putting your indexes on a different set of disks.
Aslo, pay VERY close attention to your disk setup. Hardware RAID over
Software RAID, RAID 10 is probably going to be better for RAID 5. Keep in
mind the logging has to be synchronous, so often that's where the disk
bottle neck will be.

Take advantage of perfmon to track disk queues and other metrics.

> 3) What is the limiting factor for inserting into SQLServer?
> 4) Does anyone know of any metrics for inserting records? At want
> point should we consider load balancing across DBs.

SQL doesn't necessarily do load balancing as you may think.

But again, is this constant inserts over the course of the day or a bulk
insert?

I do a quartly load of millions of records (somewhat wide) and can insert
and rebuild the indices in about 2-3 hours.

Hope some of this helps.

>
> I currently populate 1.6 million records into this table. Once again
> thanks for the help!!
>
> CREATE TABLE [result] (
> [id] numeric(20,0) NOT NULL,
> [iid] numeric(20,0) NOT NULL,
> [sid] numeric(20,0) NOT NULL,
> [pn] varchar(30) NOT NULL,
> [tid] numeric(20,0) NOT NULL,
> [stid] numeric(6,0) NOT NULL,
> [cid] numeric(20,0) NOT NULL,
> [start] datetime NOT NULL,
> [ec] numeric(5,0) NOT NULL,
> )
> GO
> CREATE INDEX [ix_resultstart]
> ON [dbo].[result]([start])
> GO
> CREATE INDEX [indx_result_1]
> ON [dbo].[result]([id], [sid], [start], [ec])
> GO
> CREATE INDEX [indx_result_3]
> ON [dbo].[result]([id], [sid], [stid], [start])
> GO
> CREATE INDEX [indx_result_2]
> ON [dbo].[result]([id], [sid], [start])
> GO|||(JSParker1@.hotmail.co.uk) writes:
> By removing the indexes on the table we have stopped getting the error
> message and have managed to load the table at 300 records per second.
> However I have couple of questions:
> 1) Are the indexes definitely to blame for this error and is there
> anyway of getting around this problem i.e. keeping the indexes in place
> when inserting?
> 2) How should I configure SQLServer to maximise the speed of
> inserts?
> 3) What is the limiting factor for inserting into SQLServer?
> 4) Does anyone know of any metrics for inserting records? At want
> point should we consider load balancing across DBs.

1) Indexes does add overhead to inserts, that cannot be denied.

2) That depends a little on the answer to the question you did not ask.
But a standard reply would be: you shouldn't.

3) A lot of things: network, CPU, disk etc.

4) I guess that st some point, it may pay off to set up partitioned
views over partitioned servers, but with 1.6 million rows you are
not there yet.

But you did not ask the most important question: how do I insert many
rows into SQL Server effeciently.

If you are sending INSERT statements that look like:

INSERT result (id, iid, sid, pb, tid, stid, cid, start, ec)
VALUES(9, 9, 9, '99999', 9, 9, 9, '20060408 12:12:12', 9)

you have chosen the slowest option available.

If you use a parameterised query, you will be better off, and probably
even a little better if you use a stored procedure.

But since you would still be sending one row at a time, there is a lot
of network overhead, so if it's possible to use some bulk mechanism,
there is a lot to gain. I don't know if JDBC exposeses any bulk-copy
facilities, but that can very well be an option. Using XML as suggested
in one post is also an option.

--
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|||JSParker1@.hotmail.co.uk wrote:
> Summary: Maximum number of records per second that can be inserted into
> SQLServer 2000.
> I am trying to insert hundreds (preferably even thousands) of records
> per second in to SQLServer table (see below) but I am getting the
> following error in the Windows Event Viewer Application log file:
> "Insufficent Memory....."

Did you maybe misconfigure your system? You might have set up SQL
Server to use more mem than you have virtual mem in your machine.
Otherwise I don't see how SQL Server should bail out with this error.

> And very few records were inserted and no errors where sent back via
> the JDBC.

Hint: use batch mode if you don't yet. Alternatives: bcp, DTS.

Kind regards

robert

maximum length of database objects in sqlserver 2000

what 's the maximum length of database objects in sqlserver 2000?
ThanksI'm not certain what you mean by "maximum length of database objects" but
you can find data on SQL Server's maximum capacities here:
http://msdn.microsoft.com/library/e..._ar_ts_8dbn.asp
David Portas
SQL Server MVP
--|||I mean the maximum character that we can assign to their names...
thanks
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:14CdndaZxKwXeNzfRVn-rQ@.giganews.com...
> I'm not certain what you mean by "maximum length of database objects" but
> you can find data on SQL Server's maximum capacities here:
> http://msdn.microsoft.com/library/e..._ar_ts_8dbn.asp
> --
> David Portas
> SQL Server MVP
> --
>|||128
David Portas
SQL Server MVP
--|||thanks alot
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:Kv-dndfGO4cmddzfRVn-jw@.giganews.com...
> 128
> --
> David Portas
> SQL Server MVP
> --
>

maximum characters per column

Hello:
How do you set the maximum characters per column in SQL
Server 6.5? Also, how do you specify this using T-SQL in
7.0 and 2000? Thanks.
Hi,
Did you mean the display then:-
For SQL 2000
default Query analyzer can display only 256 characters. This can be changed
by giving a higher value , say 2500.
Query analyzer -> Tools -> Options -> Results -> Maximum Characters Per
Column -> click OK
FOR SQL 6.5
There is no restriction on display, because in SQL 6.5 a varchar/char data
type can support a maximum of 255 bytes storage.
How do you set the maximum characters per column in SQL
Server 6.5?
Also, how do you specify this using T-SQL in 7.0 and 2000?
If you meant the storage per column, then it depends up on the data types.
See datatypes in books online
Thanks
Hari
MCDBA
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1cbf601c452fe$cd4e7b60$a101280a@.phx.gbl...
> Hello:
> How do you set the maximum characters per column in SQL
> Server 6.5? Also, how do you specify this using T-SQL in
> 7.0 and 2000? Thanks.
|||> How do you set the maximum characters per column in SQL
> Server 6.5? Also, how do you specify this using T-SQL in
> 7.0 and 2000? Thanks.
For a table that doesn't yet exist:
CREATE TABLE dbo.foo
(
bar1 VARCHAR(32), -- 32 characters
bar2 VARCHAR(64), -- 64 characters
bar3 VARCHAR(8000) -- 8000 characters
)
To change one,
ALTER TABLE dbo.foo
ALTER COLUMN bar1 VARCHAR(256)
For 6.5, I believe you had to drop the table and re-create it (no ALTER).
Also, you had more limitations (e.g. VARCHAR only goes up to 255).
http://www.aspfaq.com/
(Reverse address to reply.)
|||"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1cbf601c452fe$cd4e7b60$a101280a@.phx.gbl...
> How do you set the maximum characters per column in SQL
> Server 6.5? Also, how do you specify this using T-SQL in
> 7.0 and 2000? Thanks.
You can use the CREATE TABLE or ALTER TABLE statement to define the maximum
characters stored per column. If you are talking about limiting the maximum
character length to something less than the column width... In SQL Server
6.5 you could use a trigger, in SQL Server 7/2000 you could use a trigger or
a constraint.
Steve
|||Sorry, I should have been more articulate.
I am using QA and would like to limit the # of characters
displayed for each field/column.
In 7 & 2K, I can set this option under
Tools/Options/Results tab. I was wondering if there is an
equivalent option under 6.5. And also, its T-SQL
equivalent in setting this option using T-SQL.
Thanks.

>--Original Message--
>Hi,
>Did you mean the display then:-
>For SQL 2000
>default Query analyzer can display only 256 characters.
This can be changed
>by giving a higher value , say 2500.
>Query analyzer -> Tools -> Options -> Results -> Maximum
Characters Per
>Column -> click OK
>
>FOR SQL 6.5
>There is no restriction on display, because in SQL 6.5 a
varchar/char data
>type can support a maximum of 255 bytes storage.
>
>How do you set the maximum characters per column in SQL
>Server 6.5?
>Also, how do you specify this using T-SQL in 7.0 and
2000?
>If you meant the storage per column, then it depends up
on the data types.
>See datatypes in books online
>Thanks
>Hari
>MCDBA
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1cbf601c452fe$cd4e7b60$a101280a@.phx.gbl...
>
>.
>
|||Sorry, I should have been more articulate.
I am using QA and would like to limit the # of characters
displayed for each field/column.
In 7 & 2K, I can set this option under
Tools/Options/Results tab. I was wondering if there is an
equivalent option under 6.5. And also, its T-SQL
equivalent in setting this option using T-SQL.
Thanks.

>--Original Message--
>For a table that doesn't yet exist:
>CREATE TABLE dbo.foo
>(
> bar1 VARCHAR(32), -- 32 characters
> bar2 VARCHAR(64), -- 64 characters
> bar3 VARCHAR(8000) -- 8000 characters
>)
>To change one,
>ALTER TABLE dbo.foo
> ALTER COLUMN bar1 VARCHAR(256)
>For 6.5, I believe you had to drop the table and re-
create it (no ALTER).
>Also, you had more limitations (e.g. VARCHAR only goes up
to 255).
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>
|||Sorry, I should have been more articulate.
I am using QA and would like to limit the # of characters
displayed for each field/column.
In 7 & 2K, I can set this option under
Tools/Options/Results tab. I was wondering if there is an
equivalent option under 6.5. And also, its T-SQL
equivalent in setting this option using T-SQL.
Thanks.

>--Original Message--
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1cbf601c452fe$cd4e7b60$a101280a@.phx.gbl...
>You can use the CREATE TABLE or ALTER TABLE statement to
define the maximum
>characters stored per column. If you are talking about
limiting the maximum
>character length to something less than the column
width... In SQL Server
>6.5 you could use a trigger, in SQL Server 7/2000 you
could use a trigger or
>a constraint.
>Steve
>
>.
>
|||> I was wondering if there is an
> equivalent option under 6.5.
Not that I can remember...

> And also, its T-SQL
> equivalent in setting this option using T-SQL.
There is none. You can always use TRIM() in your queries, of course...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:1c94401c4530f$7ec0b5b0$a601280a@.phx.gbl...[vbcol=seagreen]
> Sorry, I should have been more articulate.
> I am using QA and would like to limit the # of characters
> displayed for each field/column.
> In 7 & 2K, I can set this option under
> Tools/Options/Results tab. I was wondering if there is an
> equivalent option under 6.5. And also, its T-SQL
> equivalent in setting this option using T-SQL.
> Thanks.
> message
> define the maximum
> limiting the maximum
> width... In SQL Server
> could use a trigger or
|||Hi Tiber / Rob,
There is no such option in SQL 6.5. But for your information in SQL 6.5
varchar/ char datatypes can
accoodate only 255 characters. So this option may not be required for
varchar columns. But for text data type
this will show only 255 characters.
Thanks
Hari
MCDBA
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uUR5Hy2UEHA.3596@.tk2msftngp13.phx.gbl...
> Not that I can remember...
>
> There is none. You can always use TRIM() in your queries, of course...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1c94401c4530f$7ec0b5b0$a601280a@.phx.gbl...
>

maximum characters per column

Hello:
How do you set the maximum characters per column in SQL
Server 6.5? Also, how do you specify this using T-SQL in
7.0 and 2000? Thanks.Hi,
Did you mean the display then:-
For SQL 2000
default Query analyzer can display only 256 characters. This can be changed
by giving a higher value , say 2500.
Query analyzer -> Tools -> Options -> Results -> Maximum Characters Per
Column -> click OK
FOR SQL 6.5
There is no restriction on display, because in SQL 6.5 a varchar/char data
type can support a maximum of 255 bytes storage.
How do you set the maximum characters per column in SQL
Server 6.5?
Also, how do you specify this using T-SQL in 7.0 and 2000?
If you meant the storage per column, then it depends up on the data types.
See datatypes in books online
Thanks
Hari
MCDBA
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1cbf601c452fe$cd4e7b60$a101280a@.phx
.gbl...
> Hello:
> How do you set the maximum characters per column in SQL
> Server 6.5? Also, how do you specify this using T-SQL in
> 7.0 and 2000? Thanks.|||> How do you set the maximum characters per column in SQL
> Server 6.5? Also, how do you specify this using T-SQL in
> 7.0 and 2000? Thanks.
For a table that doesn't yet exist:
CREATE TABLE dbo.foo
(
bar1 VARCHAR(32), -- 32 characters
bar2 VARCHAR(64), -- 64 characters
bar3 VARCHAR(8000) -- 8000 characters
)
To change one,
ALTER TABLE dbo.foo
ALTER COLUMN bar1 VARCHAR(256)
For 6.5, I believe you had to drop the table and re-create it (no ALTER).
Also, you had more limitations (e.g. VARCHAR only goes up to 255).
http://www.aspfaq.com/
(Reverse address to reply.)|||"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1cbf601c452fe$cd4e7b60$a101280a@.phx
.gbl...
> How do you set the maximum characters per column in SQL
> Server 6.5? Also, how do you specify this using T-SQL in
> 7.0 and 2000? Thanks.
You can use the CREATE TABLE or ALTER TABLE statement to define the maximum
characters stored per column. If you are talking about limiting the maximum
character length to something less than the column width... In SQL Server
6.5 you could use a trigger, in SQL Server 7/2000 you could use a trigger or
a constraint.
Steve|||Sorry, I should have been more articulate.
I am using QA and would like to limit the # of characters
displayed for each field/column.
In 7 & 2K, I can set this option under
Tools/Options/Results tab. I was wondering if there is an
equivalent option under 6.5. And also, its T-SQL
equivalent in setting this option using T-SQL.
Thanks.

>--Original Message--
>Hi,
>Did you mean the display then:-
>For SQL 2000
>default Query analyzer can display only 256 characters.
This can be changed
>by giving a higher value , say 2500.
>Query analyzer -> Tools -> Options -> Results -> Maximum
Characters Per
>Column -> click OK
>
>FOR SQL 6.5
>There is no restriction on display, because in SQL 6.5 a
varchar/char data
>type can support a maximum of 255 bytes storage.
>
>How do you set the maximum characters per column in SQL
>Server 6.5?
>Also, how do you specify this using T-SQL in 7.0 and
2000?
>If you meant the storage per column, then it depends up
on the data types.
>See datatypes in books online
>Thanks
>Hari
>MCDBA
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1cbf601c452fe$cd4e7b60$a101280a@.phx
.gbl...
>
>.
>|||Sorry, I should have been more articulate.
I am using QA and would like to limit the # of characters
displayed for each field/column.
In 7 & 2K, I can set this option under
Tools/Options/Results tab. I was wondering if there is an
equivalent option under 6.5. And also, its T-SQL
equivalent in setting this option using T-SQL.
Thanks.

>--Original Message--
>For a table that doesn't yet exist:
>CREATE TABLE dbo.foo
>(
> bar1 VARCHAR(32), -- 32 characters
> bar2 VARCHAR(64), -- 64 characters
> bar3 VARCHAR(8000) -- 8000 characters
> )
>To change one,
>ALTER TABLE dbo.foo
> ALTER COLUMN bar1 VARCHAR(256)
>For 6.5, I believe you had to drop the table and re-
create it (no ALTER).
>Also, you had more limitations (e.g. VARCHAR only goes up
to 255).
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>|||Sorry, I should have been more articulate.
I am using QA and would like to limit the # of characters
displayed for each field/column.
In 7 & 2K, I can set this option under
Tools/Options/Results tab. I was wondering if there is an
equivalent option under 6.5. And also, its T-SQL
equivalent in setting this option using T-SQL.
Thanks.

>--Original Message--
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1cbf601c452fe$cd4e7b60$a101280a@.phx
.gbl...
>You can use the CREATE TABLE or ALTER TABLE statement to
define the maximum
>characters stored per column. If you are talking about
limiting the maximum
>character length to something less than the column
width... In SQL Server
>6.5 you could use a trigger, in SQL Server 7/2000 you
could use a trigger or
>a constraint.
>Steve
>
>.
>|||> I was wondering if there is an
> equivalent option under 6.5.
Not that I can remember...

> And also, its T-SQL
> equivalent in setting this option using T-SQL.
There is none. You can always use TRIM() in your queries, of course...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:1c94401c4530f$7ec0b5b0$a60
1280a@.phx.gbl...[vbcol=seagreen]
> Sorry, I should have been more articulate.
> I am using QA and would like to limit the # of characters
> displayed for each field/column.
> In 7 & 2K, I can set this option under
> Tools/Options/Results tab. I was wondering if there is an
> equivalent option under 6.5. And also, its T-SQL
> equivalent in setting this option using T-SQL.
> Thanks.
>
> message
> define the maximum
> limiting the maximum
> width... In SQL Server
> could use a trigger or|||Hi Tiber / Rob,
There is no such option in SQL 6.5. But for your information in SQL 6.5
varchar/ char datatypes can
accoodate only 255 characters. So this option may not be required for
varchar columns. But for text data type
this will show only 255 characters.
Thanks
Hari
MCDBA
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uUR5Hy2UEHA.3596@.tk2msftngp13.phx.gbl...
> Not that I can remember...
>
> There is none. You can always use TRIM() in your queries, of course...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1c94401c4530f$7ec0b5b0$a601280a@.phx
.gbl...
>

max(keyfield)

I forget, does SQLServer have an internal optimization, such that if
you have a (clustered) index on fields A and B, and you do a select
where max(A) = 'X', SQLServer does NOT have to do a scan to figure out
the max?
Thanks.
J.
A clustered index is an index but with the data at the leaf level. So if A
is the only or first column in the index (clustered or not) it can determine
what the max is with a seek and not a scan.
Andrew J. Kelly SQL MVP
"jxstern" <jxstern@.nowhere.com> wrote in message
news:pouhp01v0onje4a3tgserrtb1ijr8bljd7@.4ax.com...
>I forget, does SQLServer have an internal optimization, such that if
> you have a (clustered) index on fields A and B, and you do a select
> where max(A) = 'X', SQLServer does NOT have to do a scan to figure out
> the max?
> Thanks.
> J.
>
|||In addition to Andrew's points, I have a question. What kind of query is
[vbcol=seagreen]
? Do you expect this to return a row? How? MAX() is an aggregate
function that works on all rows, while WHERE is a clause that works on
individual rows. Maybe you can show us a more practical query ...
http://www.aspfaq.com/
(Reverse address to reply.)
|||On Mon, 15 Nov 2004 14:33:57 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:
>In addition to Andrew's points, I have a question. What kind of query is
>
>? Do you expect this to return a row? How? MAX() is an aggregate
>function that works on all rows, while WHERE is a clause that works on
>individual rows. Maybe you can show us a more practical query ...
OK, that was a little terse, try something like:
select name
from mytable
where trxdate = (select max(trxdate) from mytable)
So a seek is better than a scan, anyway, and it can do this even for a
clustered index because the top index page has a last row. I just
wondered if it might do even better and cache the high value, but the
important thing is that you confirm it at least an avoid the full
scan. Though, now that I look at my real code again, I suspect it's
going to scan, anyway, oh well, at least I can improve my education
from this example.
Thanks.
J.
|||> So a seek is better than a scan, anyway, and it can do this even for a
> clustered index because the top index page has a last row. I just
> wondered if it might do even better and cache the high value,
No, I don't think individual column values can be cached the way you are
describing.
However, for your education, wouldn't it be relatively simple to set up a
simple test?
CREATE TABLE Kerplunk
(
foo INT,
bar INT,
raboof INT
)
GO
CREATE CLUSTERED INDEX f ON Kerplunk(foo)
CREATE INDEX f ON Kerplunk(foo)
GO
SET NOCOUNT ON
-- <<< populate data here! >>>
-- hit Ctrl+K to see execution plan
-- it will show where scans/seeks are used...
SELECT * FROM Kerplunk WHERE foo = (SELECT MAX(foo) FROM Kerplunk)
SELECT * FROM Kerplunk WHERE bar = (SELECT MAX(bar) FROM Kerplunk)
SELECT * FROM Kerplunk WHERE raboof = (SELECT MAX(raboof) FROM Kerplunk)
GO
DROP TABLE Kerplunk
GO
|||On Mon, 15 Nov 2004 15:18:06 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:
>No, I don't think individual column values can be cached the way you are
>describing.
Just the high and low.

>However, for your education, wouldn't it be relatively simple to set up a
>simple test?
Well, yeah, I guess, but sometimes it feels more productive to ask
than to do the reverse engineering, and some happy lurker might
benefit thereby.

>CREATE TABLE Kerplunk
>(
> foo INT,
> bar INT,
> raboof INT
>)
>GO
>CREATE CLUSTERED INDEX f ON Kerplunk(foo)
>CREATE INDEX f ON Kerplunk(foo)
on (bar), I suppose you meant.

>GO
>SET NOCOUNT ON
>-- <<< populate data here! >>>
>-- hit Ctrl+K to see execution plan
>-- it will show where scans/seeks are used...
>SELECT * FROM Kerplunk WHERE foo = (SELECT MAX(foo) FROM Kerplunk)
>SELECT * FROM Kerplunk WHERE bar = (SELECT MAX(bar) FROM Kerplunk)
>SELECT * FROM Kerplunk WHERE raboof = (SELECT MAX(raboof) FROM Kerplunk)
>GO
>DROP TABLE Kerplunk
>GO
|||> Just the high and low.
My statement remains as is.

> Well, yeah, I guess, but sometimes it feels more productive to ask
> than to do the reverse engineering, and some happy lurker might
> benefit thereby.
Perhaps, but unless we know every single detail about your environment,
you're only going to get educated guesses.

> on (bar), I suppose you meant.
Yes, I did.
|||On Mon, 15 Nov 2004 17:41:19 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:
>My statement remains as is.
>
>Perhaps, but unless we know every single detail about your environment,
>you're only going to get educated guesses.
>
>Yes, I did.
CREATE TABLE Kerplunk
(
foo INT,
bar INT,
raboof INT
)
GO
CREATE CLUSTERED INDEX f ON Kerplunk(foo)
CREATE INDEX b ON Kerplunk(bar)
GO
-- <<< populate data here! >>>
SET NOCOUNT ON
declare @.ix int
set @.ix = 0
while @.ix < 100000
begin
insert into Kerplunk values (@.ix, @.ix, @.ix)
set @.ix = @.ix + 1
end
-- hit Ctrl+K to see execution plan
-- it will show where scans/seeks are used...
SELECT * FROM Kerplunk WHERE foo = (SELECT MAX(foo) FROM Kerplunk)
-- .00640
SELECT * FROM Kerplunk WHERE bar = (SELECT MAX(bar) FROM Kerplunk)
-- .00650
SELECT * FROM Kerplunk WHERE raboof = (SELECT MAX(raboof) FROM
Kerplunk)
-- 1.13
GO
DROP TABLE Kerplunk
GO

max(keyfield)

I forget, does SQLServer have an internal optimization, such that if
you have a (clustered) index on fields A and B, and you do a select
where max(A) = 'X', SQLServer does NOT have to do a scan to figure out
the max?
Thanks.
J.A clustered index is an index but with the data at the leaf level. So if A
is the only or first column in the index (clustered or not) it can determine
what the max is with a seek and not a scan.
--
Andrew J. Kelly SQL MVP
"jxstern" <jxstern@.nowhere.com> wrote in message
news:pouhp01v0onje4a3tgserrtb1ijr8bljd7@.4ax.com...
>I forget, does SQLServer have an internal optimization, such that if
> you have a (clustered) index on fields A and B, and you do a select
> where max(A) = 'X', SQLServer does NOT have to do a scan to figure out
> the max?
> Thanks.
> J.
>|||In addition to Andrew's points, I have a question. What kind of query is
>> where max(A) = 'X'
' Do you expect this to return a row? How? MAX() is an aggregate
function that works on all rows, while WHERE is a clause that works on
individual rows. Maybe you can show us a more practical query ...
--
http://www.aspfaq.com/
(Reverse address to reply.)|||On Mon, 15 Nov 2004 14:33:57 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:
>In addition to Andrew's points, I have a question. What kind of query is
>> where max(A) = 'X'
>' Do you expect this to return a row? How? MAX() is an aggregate
>function that works on all rows, while WHERE is a clause that works on
>individual rows. Maybe you can show us a more practical query ...
OK, that was a little terse, try something like:
select name
from mytable
where trxdate = (select max(trxdate) from mytable)
So a seek is better than a scan, anyway, and it can do this even for a
clustered index because the top index page has a last row. I just
wondered if it might do even better and cache the high value, but the
important thing is that you confirm it at least an avoid the full
scan. Though, now that I look at my real code again, I suspect it's
going to scan, anyway, oh well, at least I can improve my education
from this example.
Thanks.
J.|||> So a seek is better than a scan, anyway, and it can do this even for a
> clustered index because the top index page has a last row. I just
> wondered if it might do even better and cache the high value,
No, I don't think individual column values can be cached the way you are
describing.
However, for your education, wouldn't it be relatively simple to set up a
simple test?
CREATE TABLE Kerplunk
(
foo INT,
bar INT,
raboof INT
)
GO
CREATE CLUSTERED INDEX f ON Kerplunk(foo)
CREATE INDEX f ON Kerplunk(foo)
GO
SET NOCOUNT ON
-- <<< populate data here! >>
-- hit Ctrl+K to see execution plan
-- it will show where scans/seeks are used...
SELECT * FROM Kerplunk WHERE foo = (SELECT MAX(foo) FROM Kerplunk)
SELECT * FROM Kerplunk WHERE bar = (SELECT MAX(bar) FROM Kerplunk)
SELECT * FROM Kerplunk WHERE raboof = (SELECT MAX(raboof) FROM Kerplunk)
GO
DROP TABLE Kerplunk
GO|||On Mon, 15 Nov 2004 15:18:06 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:
>> So a seek is better than a scan, anyway, and it can do this even for a
>> clustered index because the top index page has a last row. I just
>> wondered if it might do even better and cache the high value,
>No, I don't think individual column values can be cached the way you are
>describing.
Just the high and low.
>However, for your education, wouldn't it be relatively simple to set up a
>simple test?
Well, yeah, I guess, but sometimes it feels more productive to ask
than to do the reverse engineering, and some happy lurker might
benefit thereby.
>CREATE TABLE Kerplunk
>(
> foo INT,
> bar INT,
> raboof INT
>)
>GO
>CREATE CLUSTERED INDEX f ON Kerplunk(foo)
>CREATE INDEX f ON Kerplunk(foo)
on (bar), I suppose you meant.
>GO
>SET NOCOUNT ON
>-- <<< populate data here! >>
>-- hit Ctrl+K to see execution plan
>-- it will show where scans/seeks are used...
>SELECT * FROM Kerplunk WHERE foo = (SELECT MAX(foo) FROM Kerplunk)
>SELECT * FROM Kerplunk WHERE bar = (SELECT MAX(bar) FROM Kerplunk)
>SELECT * FROM Kerplunk WHERE raboof = (SELECT MAX(raboof) FROM Kerplunk)
>GO
>DROP TABLE Kerplunk
>GO|||> Just the high and low.
My statement remains as is.
> Well, yeah, I guess, but sometimes it feels more productive to ask
> than to do the reverse engineering, and some happy lurker might
> benefit thereby.
Perhaps, but unless we know every single detail about your environment,
you're only going to get educated guesses.
> on (bar), I suppose you meant.
Yes, I did.|||On Mon, 15 Nov 2004 17:41:19 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:
>> Just the high and low.
>My statement remains as is.
>> Well, yeah, I guess, but sometimes it feels more productive to ask
>> than to do the reverse engineering, and some happy lurker might
>> benefit thereby.
>Perhaps, but unless we know every single detail about your environment,
>you're only going to get educated guesses.
>> on (bar), I suppose you meant.
>Yes, I did.
CREATE TABLE Kerplunk
(
foo INT,
bar INT,
raboof INT
)
GO
CREATE CLUSTERED INDEX f ON Kerplunk(foo)
CREATE INDEX b ON Kerplunk(bar)
GO
-- <<< populate data here! >>
SET NOCOUNT ON
declare @.ix int
set @.ix = 0
while @.ix < 100000
begin
insert into Kerplunk values (@.ix, @.ix, @.ix)
set @.ix = @.ix + 1
end
-- hit Ctrl+K to see execution plan
-- it will show where scans/seeks are used...
SELECT * FROM Kerplunk WHERE foo = (SELECT MAX(foo) FROM Kerplunk)
-- .00640
SELECT * FROM Kerplunk WHERE bar = (SELECT MAX(bar) FROM Kerplunk)
-- .00650
SELECT * FROM Kerplunk WHERE raboof = (SELECT MAX(raboof) FROM
Kerplunk)
-- 1.13
GO
DROP TABLE Kerplunk
GO

max(keyfield)

I forget, does SQLServer have an internal optimization, such that if
you have a (clustered) index on fields A and B, and you do a select
where max(A) = 'X', SQLServer does NOT have to do a scan to figure out
the max?
Thanks.
J.A clustered index is an index but with the data at the leaf level. So if A
is the only or first column in the index (clustered or not) it can determine
what the max is with a seek and not a scan.
Andrew J. Kelly SQL MVP
"jxstern" <jxstern@.nowhere.com> wrote in message
news:pouhp01v0onje4a3tgserrtb1ijr8bljd7@.
4ax.com...
>I forget, does SQLServer have an internal optimization, such that if
> you have a (clustered) index on fields A and B, and you do a select
> where max(A) = 'X', SQLServer does NOT have to do a scan to figure out
> the max?
> Thanks.
> J.
>|||In addition to Andrew's points, I have a question. What kind of query is
[vbcol=seagreen]
' Do you expect this to return a row? How? MAX() is an aggregate
function that works on all rows, while WHERE is a clause that works on
individual rows. Maybe you can show us a more practical query ...
http://www.aspfaq.com/
(Reverse address to reply.)|||On Mon, 15 Nov 2004 14:33:57 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:
>In addition to Andrew's points, I have a question. What kind of query is
>
>' Do you expect this to return a row? How? MAX() is an aggregate
>function that works on all rows, while WHERE is a clause that works on
>individual rows. Maybe you can show us a more practical query ...
OK, that was a little terse, try something like:
select name
from mytable
where trxdate = (select max(trxdate) from mytable)
So a seek is better than a scan, anyway, and it can do this even for a
clustered index because the top index page has a last row. I just
wondered if it might do even better and cache the high value, but the
important thing is that you confirm it at least an avoid the full
scan. Though, now that I look at my real code again, I suspect it's
going to scan, anyway, oh well, at least I can improve my education
from this example.
Thanks.
J.|||> So a seek is better than a scan, anyway, and it can do this even for a
> clustered index because the top index page has a last row. I just
> wondered if it might do even better and cache the high value,
No, I don't think individual column values can be cached the way you are
describing.
However, for your education, wouldn't it be relatively simple to set up a
simple test?
CREATE TABLE Kerplunk
(
foo INT,
bar INT,
raboof INT
)
GO
CREATE CLUSTERED INDEX f ON Kerplunk(foo)
CREATE INDEX f ON Kerplunk(foo)
GO
SET NOCOUNT ON
-- <<< populate data here! >>>
-- hit Ctrl+K to see execution plan
-- it will show where scans/seeks are used...
SELECT * FROM Kerplunk WHERE foo = (SELECT MAX(foo) FROM Kerplunk)
SELECT * FROM Kerplunk WHERE bar = (SELECT MAX(bar) FROM Kerplunk)
SELECT * FROM Kerplunk WHERE raboof = (SELECT MAX(raboof) FROM Kerplunk)
GO
DROP TABLE Kerplunk
GO|||On Mon, 15 Nov 2004 15:18:06 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:
>No, I don't think individual column values can be cached the way you are
>describing.
Just the high and low.

>However, for your education, wouldn't it be relatively simple to set up a
>simple test?
Well, yeah, I guess, but sometimes it feels more productive to ask
than to do the reverse engineering, and some happy lurker might
benefit thereby.

>CREATE TABLE Kerplunk
>(
> foo INT,
> bar INT,
> raboof INT
> )
>GO
>CREATE CLUSTERED INDEX f ON Kerplunk(foo)
>CREATE INDEX f ON Kerplunk(foo)
on (bar), I suppose you meant.

>GO
>SET NOCOUNT ON
>-- <<< populate data here! >>>
>-- hit Ctrl+K to see execution plan
>-- it will show where scans/seeks are used...
>SELECT * FROM Kerplunk WHERE foo = (SELECT MAX(foo) FROM Kerplunk)
>SELECT * FROM Kerplunk WHERE bar = (SELECT MAX(bar) FROM Kerplunk)
>SELECT * FROM Kerplunk WHERE raboof = (SELECT MAX(raboof) FROM Kerplunk)
>GO
>DROP TABLE Kerplunk
>GO|||> Just the high and low.
My statement remains as is.

> Well, yeah, I guess, but sometimes it feels more productive to ask
> than to do the reverse engineering, and some happy lurker might
> benefit thereby.
Perhaps, but unless we know every single detail about your environment,
you're only going to get educated guesses.

> on (bar), I suppose you meant.
Yes, I did.|||On Mon, 15 Nov 2004 17:41:19 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:
>My statement remains as is.
>
>Perhaps, but unless we know every single detail about your environment,
>you're only going to get educated guesses.
>
>Yes, I did.
CREATE TABLE Kerplunk
(
foo INT,
bar INT,
raboof INT
)
GO
CREATE CLUSTERED INDEX f ON Kerplunk(foo)
CREATE INDEX b ON Kerplunk(bar)
GO
-- <<< populate data here! >>>
SET NOCOUNT ON
declare @.ix int
set @.ix = 0
while @.ix < 100000
begin
insert into Kerplunk values (@.ix, @.ix, @.ix)
set @.ix = @.ix + 1
end
-- hit Ctrl+K to see execution plan
-- it will show where scans/seeks are used...
SELECT * FROM Kerplunk WHERE foo = (SELECT MAX(foo) FROM Kerplunk)
-- .00640
SELECT * FROM Kerplunk WHERE bar = (SELECT MAX(bar) FROM Kerplunk)
-- .00650
SELECT * FROM Kerplunk WHERE raboof = (SELECT MAX(raboof) FROM
Kerplunk)
-- 1.13
GO
DROP TABLE Kerplunk
GO