May I create not unique clustered index?

Hi,
When create clustered index, the column must be unique? Or
I can do Create Clustered index index_name ON table
(column_name)?
Any input much appreciate!
JennyThere is nothing that stops you from having a clustered index on a
non-unique column. By having your clustered index on a unique column we get
a narrowed selectivity when quering on that column ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Jenny" <jyu@.iseoptions.com> wrote in message
news:0cfc01c3a473$01686900$a301280a@.phx.gbl...
> Hi,
> When create clustered index, the column must be unique? Or
> I can do Create Clustered index index_name ON table
> (column_name)?
> Any input much appreciate!
> Jenny|||The column does not need to be unique.
"Jenny" <jyu@.iseoptions.com> wrote in message
news:0cfc01c3a473$01686900$a301280a@.phx.gbl...
> Hi,
> When create clustered index, the column must be unique? Or
> I can do Create Clustered index index_name ON table
> (column_name)?
> Any input much appreciate!
> Jenny|||no, they do not need to be unique
CREATE CLUSTERED INDEX IXNAME ON TABLENAME(COLUMNLIST)
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Jenny" <jyu@.iseoptions.com> wrote in message
news:0cfc01c3a473$01686900$a301280a@.phx.gbl...
> Hi,
> When create clustered index, the column must be unique? Or
> I can do Create Clustered index index_name ON table
> (column_name)?
> Any input much appreciate!
> Jenny

Maximum number of columns in an index

I am upgrading from Access, where you can only have 10 fields in a primary key or unique index. Is this also the limit in SQL Server? If not, what is the limit?

Thanks for any help on this.

From Books Online, Topic: 'Create Index'

"Up to 16 columns can be combined into a single composite index key."

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