Maximum Row Size Limitation

Is the maximum row size per table really limited to
8K bytes? Or is this the internal organization of SQL
data structures. I have imported a legacy table that
wound up having over 100 columns that came in as
NVARCHAR(255) or roughly 25K + in size. Any comments?
ThanksIt will allow you to create the object with the warning something as
The table % has been created but its maximum row size (%) exceeds the
maximum number of bytes per row (8060)
Ex:
create table test_size
(i1 nvarchar(255),
i2 nvarchar(255),
i3 nvarchar(255),
i4 nvarchar(255),
i6 nvarchar(255),
i8 nvarchar(255),
i9 nvarchar(255),
i10 nvarchar(255),
i11 nvarchar(255),
i12 nvarchar(255),
i14 nvarchar(255),
i19 nvarchar(255),
i20 nvarchar(255),
i21 nvarchar(255),
i22 nvarchar(255),
i23 nvarchar(255),
i24 nvarchar(255),
i30 nvarchar(255),
i31 nvarchar(255),
i32 nvarchar(255),
i33 nvarchar(255))
This is because you are using nvarchar datatype which is variable length
data type. Hence it will give you
error at runtime if actual data that you are going to insert goes beyond
8060 bytes. Also point to be noted
that nvarchar table 2 bytes to store a single character.
So SQL server is happy to create a table though a table crosses limit of
8060 bytes per row so far you use
variable data type like varchar/nvarchar but it will throw you an error
while inserting/updating data if actual
row size that a row will occupy goes beyond this limit.
--
-Vishal
"wringland" <bill@.tiati.com> wrote in message
news:001001c34fa3$3447a540$a401280a@.phx.gbl...
> Is the maximum row size per table really limited to
> 8K bytes? Or is this the internal organization of SQL
> data structures. I have imported a legacy table that
> wound up having over 100 columns that came in as
> NVARCHAR(255) or roughly 25K + in size. Any comments?
> Thanks|||And Dinesh is referring to the "hard" limit of the row size. When you have
varchar columns, you need to figure in the "soft" factor: the actual limit
is applied to the actual size of content. If you have 100 nvarchar(255)
columns and each column has only 1 character in it, the row size would be
(100 (columns) x 1 (character each column) x 2 (for Nvarchar) + 9 (row
overhead) ) = 209 bytes.
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:OsEUMR6TDHA.3700@.tk2msftngp13.phx.gbl...
> Bill,
> The maximum row size is 8060 bytes except for blob data types as TEXT ,
> IMAGE etc.That means your 25K table wont fit in.Moreover, you are using
the
> unicode datatype, NVARCHAR, which itself take twice as much storage space
as
> non-unicode data types.
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "wringland" <bill@.tiati.com> wrote in message
> news:001001c34fa3$3447a540$a401280a@.phx.gbl...
> > Is the maximum row size per table really limited to
> > 8K bytes? Or is this the internal organization of SQL
> > data structures. I have imported a legacy table that
> > wound up having over 100 columns that came in as
> > NVARCHAR(255) or roughly 25K + in size. Any comments?
> >
> > Thanks
>|||Thanks for the reply. Can you perhaps explain why the
table was imported into SQL and the table design show the
proper format and size for the columns. All does seem to
be well with the data.
Thanks
>--Original Message--
>Bill,
>The maximum row size is 8060 bytes except for blob data
types as TEXT ,
>IMAGE etc.That means your 25K table wont fit in.Moreover,
you are using the
>unicode datatype, NVARCHAR, which itself take twice as
much storage space as
>non-unicode data types.
>--
>Dinesh.
>SQL Server FAQ at
>http://www.tkdinesh.com
>"wringland" <bill@.tiati.com> wrote in message
>news:001001c34fa3$3447a540$a401280a@.phx.gbl...
>> Is the maximum row size per table really limited to
>> 8K bytes? Or is this the internal organization of SQL
>> data structures. I have imported a legacy table that
>> wound up having over 100 columns that came in as
>> NVARCHAR(255) or roughly 25K + in size. Any comments?
>> Thanks
>
>.
>|||> Thanks for the reply. Can you perhaps explain why the
> table was imported into SQL and the table design show the
> proper format and size for the columns. All does seem to
> be well with the data.
Because the data is not "full" - you will only get an error if you attempt
to put more than 8,060 bytes of *actual data* into the table. I am guessing
that many of your nvarchar(255) columns are NULL or blank.|||Bill,
As mentioned in other posts, SQL server will let you create the table with a
warning about data truncation.While inserting data, the moment a particular
rowsize exceeds 8060, the rest would be truncated and you wouldnt even
know.Means while querying the table, you may be looking at incomplete and
thus false data.
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"wringland" <bill@.tiati.com> wrote in message
news:009201c34fa7$6db8dbb0$a401280a@.phx.gbl...
> Thanks for the reply. Can you perhaps explain why the
> table was imported into SQL and the table design show the
> proper format and size for the columns. All does seem to
> be well with the data.
> Thanks
> >--Original Message--
> >Bill,
> >
> >The maximum row size is 8060 bytes except for blob data
> types as TEXT ,
> >IMAGE etc.That means your 25K table wont fit in.Moreover,
> you are using the
> >unicode datatype, NVARCHAR, which itself take twice as
> much storage space as
> >non-unicode data types.
> >
> >--
> >Dinesh.
> >SQL Server FAQ at
> >http://www.tkdinesh.com
> >
> >"wringland" <bill@.tiati.com> wrote in message
> >news:001001c34fa3$3447a540$a401280a@.phx.gbl...
> >> Is the maximum row size per table really limited to
> >> 8K bytes? Or is this the internal organization of SQL
> >> data structures. I have imported a legacy table that
> >> wound up having over 100 columns that came in as
> >> NVARCHAR(255) or roughly 25K + in size. Any comments?
> >>
> >> Thanks
> >
> >
> >.
> >|||> As mentioned in other posts, SQL server will let you create the table with
a
> warning about data truncation.While inserting data, the moment a
particular
> rowsize exceeds 8060, the rest would be truncated and you wouldnt even
> know.
Actually in this case, wouldn't you get the "string or binary data would be
truncated" error message?|||I have five fields each varchar(5000) and about
8 other columns between 50 and 100 bytes each.
so in answer to your question, none is > 8060.
>--Original Message--
>What is your current table structure? Are there one or
two columns forcing
>it beyond 8,060, or do you have 100 columns that are 255
characters?
>
>
>"Alice" <mygth@.hotmail.com> wrote in message
>news:056401c34fce$de321fb0$a101280a@.phx.gbl...
>> I am running into similar situation,
>> I am creating new table with rowsize > 8060,
>> my rowsize is 25000, is there any way by which
>> I can increase the rowsize to this limit?
>> I read on microsoft site that this limiation existed
>> on 6.5 and u.s. pack 2 fixed it. Any ideas?
>> BTW, I am on Sql 7.
>
>.
>|||Alice,
>> Then why do they say US patch 4 fixes it for sql 6.5?
What article are you talking about?
Thanks!
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Alice" <mygth@.hotmail.com> wrote in message
news:04a101c34fda$1dcc50e0$a401280a@.phx.gbl...
> well, I was thinking on those lines, but I am surprised
> that we cannot have > 8060 characters in a row.
> Then why do they say US patch 4 fixes it for sql 6.5?
> unless I am mistaken.
> >--Original Message--
> >> I have five fields each varchar(5000) and about
> >> 8 other columns between 50 and 100 bytes each.
> >>
> >> so in answer to your question, none is > 8060.
> >
> >If you expect to ever fill more than 8,060 characters
> across the row, then
> >you're going to have to make a decision. You could use
> text columns, or you
> >could put each varchar(5000) in its own table with a
> foreign key to this
> >tables pk. Are you expecting to use all 5000 characters
> in any row/column
> >combination? If not, you might consider decreasing the
> size to reduce the
> >number of columns you'll have to store off-row.
> >
> >
> >.
> >

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