updating text datatype: clustered

Showing posts with label clustered. Show all posts

Friday, March 30, 2012

Problem with clustered index

[tempdb].[dbo].[#IndexTest]),
SEEK

[tempdb].[dbo].[#IndexTest].[Col1]=(1)) ORDERED FORWAR
D)
(1 row(s) affected)
StmtText
---
SELECT * FROM #IndexTest WHERE Col3 = 'C'
(1 row(s) affected)
StmtText
----
--
|--Clustered Index Scan(OBJECT

[tempdb].[dbo].[#IndexTest]),
WHERE

[tempdb].[dbo].[#IndexTest].[Col3]='C'))
(1 row(s) affected)
StmtText
----
SELECT * FROM #IndexTest WHERE Col1 = 1 AND Col2 = 20 AND Col3 = 'C'
(1 row(s) affected)
StmtText
----
----
---
|--Clustered Index Seek(OBJECT

[tempdb].[dbo].[#IndexTest]),
SEEK

[tempdb].[dbo].[#IndexTest].[Col1]=(1) AND
[tempdb].[dbo].[#IndexTest].[Col2]=(20) AND
[tempdb].[dbo].[#IndexTest].[Col3]='C') ORDERED FORWARD)
(1 row(s) affected)
Q.E.D.
Regards
robert|||Robert Klemme wrote:
> On 29.11.2006 14:24, Tracy McKibben wrote:
> You said:
>
> Then I wrote:
>
> The second SELECT does not use a set of /leading/ columns of the index!
> StmtText
> ---
> SELECT * FROM #IndexTest WHERE Col1 = 1
> (1 row(s) affected)
> StmtText
> ----
----
> |--Clustered Index Seek(OBJECT

[tempdb].[dbo].[#IndexTest])
,
> SEEK

[tempdb].[dbo].[#IndexTest].[Col1]=(1)) ORDERED FORW
ARD)
> (1 row(s) affected)
> StmtText
> ---
> SELECT * FROM #IndexTest WHERE Col3 = 'C'
> (1 row(s) affected)
> StmtText
> ----
---
> |--Clustered Index Scan(OBJECT

[tempdb].[dbo].[#IndexTest])
,
> WHERE

[tempdb].[dbo].[#IndexTest].[Col3]='C'))
> (1 row(s) affected)
> StmtText
> ----
> SELECT * FROM #IndexTest WHERE Col1 = 1 AND Col2 = 20 AND Col3 = 'C'
> (1 row(s) affected)
> StmtText
> ----
----
---
> |--Clustered Index Seek(OBJECT

[tempdb].[dbo].[#IndexTest])
,
> SEEK

[tempdb].[dbo].[#IndexTest].[Col1]=(1) AND
> [tempdb].[dbo].[#IndexTest].[Col2]=(20) AND
> [tempdb].[dbo].[#IndexTest].[Col3]='C') ORDERED FORWARD)
> (1 row(s) affected)
> Q.E.D.
> Regards
> robert
?
I don't really know what you're debating here. I said that if IDNumber
(the leading column) wasn't used in the WHERE clause, an index seek
wouldn't happen. You disagreed with me, but then posted an example that
proves my point exactly. What am I missing?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On 29.11.2006 18:54, Tracy McKibben wrote:
> Robert Klemme wrote:
> ?
> I don't really know what you're debating here. I said that if IDNumber
> (the leading column) wasn't used in the WHERE clause, an index seek
> wouldn't happen. You disagreed with me, but then posted an example that
> proves my point exactly. What am I missing?
You said "If this is the only index available, then /only/ queries that
include IDNumber in the WHERE clause will seek against this index."
(accentuation by me). I objected that because /also/ queries that
contain /more leading columns/ from the index do a seek which is nicely
demonstrated by the plans I posted.
robert

Problem with clustered index

Hi!
I have a table with 5 primary keys. I have clustered index defined on
all 5 columns. However, I can see that this index is only using three
columns and not five. Why is that?
Also,
I for some reason when I run queries I get index scan as opposed to
index seek.
Thanks,
T.It sounds like the indexes you have are not working for you.
For us to assist you, please post the table DDL, along with a query or two
that you believe do not properly use indexing.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"tolcis" <a.liberchuk@.verizon.net> wrote in message
news:1164740732.714927.282500@.j44g2000cwa.googlegroups.com...
> Hi!
> I have a table with 5 primary keys. I have clustered index defined on
> all 5 columns. However, I can see that this index is only using three
> columns and not five. Why is that?
> Also,
> I for some reason when I run queries I get index scan as opposed to
> index seek.
> Thanks,
> T.
>|||> I have a table with 5 primary keys.
You can only have one PK per table. I assume you mean you have one PK based on 5 columns.
> I have clustered index defined on
> all 5 columns.
You mean that the index that goes with the PK is the clustered index.
> However, I can see that this index is only using three
> columns and not five.
Where do you see this?
> I for some reason when I run queries I get index scan as opposed to
> index seek.
You never get a table scan when a table has a clustered index, so I assume you mean "clustered index
scan". We need to know the table structure, indexes and queries to determine whether the index can
support your queries.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tolcis" <a.liberchuk@.verizon.net> wrote in message
news:1164740732.714927.282500@.j44g2000cwa.googlegroups.com...
> Hi!
> I have a table with 5 primary keys. I have clustered index defined on
> all 5 columns. However, I can see that this index is only using three
> columns and not five. Why is that?
> Also,
> I for some reason when I run queries I get index scan as opposed to
> index seek.
> Thanks,
> T.
>|||This is the table DDL:
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[SERVICEW]') and OBJECTPROPERTY(id, N'IsUserTable') =1)
drop table [dbo].[SERVICEW]
GO
CREATE TABLE [dbo].[SERVICEW] (
[AppCode] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[IdNumber] [int] NOT NULL ,
[Family] [int] NOT NULL ,
[Sequence] [bigint] NOT NULL ,
[Source] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ApplicantType] [int] NULL ,
[Status] [int] NULL ,
[Type] [int] NULL ,
[UniqueExpansionId] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[UniqueIdNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Price] [real] NULL ,
[PriceOption] [int] NULL ,
[Quantity] [int] NULL ,
[Description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ProductCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[exported] [bit] NULL ,
[UPTODATE] [bit] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SERVICEW] WITH NOCHECK ADD
CONSTRAINT [PK_SERVICEW] PRIMARY KEY CLUSTERED
(
[IdNumber],
[Family],
[AppCode],
[Sequence],
[Source]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SERVICEW] ADD
CONSTRAINT [DF__SERVICEW__Sequen__11FF8BD8] DEFAULT (0) FOR
[Sequence],
CONSTRAINT [DF__SERVICEW__Applic__12F3B011] DEFAULT (0) FOR
[ApplicantType],
CONSTRAINT [DF__SERVICEW__Status__13E7D44A] DEFAULT (0) FOR [Status],
CONSTRAINT [DF__SERVICEW__Type__14DBF883] DEFAULT (0) FOR [Type],
CONSTRAINT [DF__SERVICEW__Unique__15D01CBC] DEFAULT (' ') FOR
[UniqueExpansionId],
CONSTRAINT [DF__SERVICEW__Unique__16C440F5] DEFAULT (' ') FOR
[UniqueIdNumber],
CONSTRAINT [DF__SERVICEW__Price__7DE38492] DEFAULT (0.0) FOR [Price],
CONSTRAINT [DF__SERVICEW__PriceO__6F2B50E7] DEFAULT (0) FOR
[PriceOption],
CONSTRAINT [DF__SERVICEW__Quanti__396371BC] DEFAULT (0) FOR
[Quantity],
CONSTRAINT [DF__SERVICEW__Descri__03C67B1A] DEFAULT ('') FOR
[Description],
CONSTRAINT [DF__servicew__Produc__1452B3F5] DEFAULT ('') FOR
[ProductCode],
CONSTRAINT [DF__SERVICEW__UPTODA__08211BE3] DEFAULT (1) FOR [UPTODATE]
GO
My query is pretty large and it doesn't only use this table it use
multiple but in the execution plan I see it uses clustered index scan
on this table. From what I remember it should only be table seek and
not scan.
Thanks,
T.
Arnie Rowland wrote:
> It sounds like the indexes you have are not working for you.
> For us to assist you, please post the table DDL, along with a query or two
> that you believe do not properly use indexing.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "tolcis" <a.liberchuk@.verizon.net> wrote in message
> news:1164740732.714927.282500@.j44g2000cwa.googlegroups.com...
> > Hi!
> > I have a table with 5 primary keys. I have clustered index defined on
> > all 5 columns. However, I can see that this index is only using three
> > columns and not five. Why is that?
> > Also,
> > I for some reason when I run queries I get index scan as opposed to
> > index seek.
> >
> > Thanks,
> > T.
> >|||tolcis wrote:
> ALTER TABLE [dbo].[SERVICEW] WITH NOCHECK ADD
> CONSTRAINT [PK_SERVICEW] PRIMARY KEY CLUSTERED
> (
> [IdNumber],
> [Family],
> [AppCode],
> [Sequence],
> [Source]
> ) ON [PRIMARY]
> GO
>
If this is the only index available, then only queries that include
IDNumber in the WHERE clause will seek against this index. For example:
This will "seek":
SELECT * FROM ServiceW WHERE IDNumber = 10
This will "scan":
SELECT * FROM ServiceW WHERE AppCode = 'X'
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On 28.11.2006 22:05, Tracy McKibben wrote:
> tolcis wrote:
>> ALTER TABLE [dbo].[SERVICEW] WITH NOCHECK ADD
>> CONSTRAINT [PK_SERVICEW] PRIMARY KEY CLUSTERED
>> (
>> [IdNumber],
>> [Family],
>> [AppCode],
>> [Sequence],
>> [Source]
>> ) ON [PRIMARY]
>> GO
> If this is the only index available, then only queries that include
> IDNumber in the WHERE clause will seek against this index. For example:
> This will "seek":
> SELECT * FROM ServiceW WHERE IDNumber = 10
> This will "scan":
> SELECT * FROM ServiceW WHERE AppCode = 'X'
I beg to differ: /all/ queries containing filters on any set of
/leading/ columns of the index should be doing an index seek - unless
the optimizer decides that a full scan is more efficient (for example
because criteria will return 90% of the rows anyway).
Kind regards
robert|||Robert Klemme wrote:
> I beg to differ: /all/ queries containing filters on any set of
> /leading/ columns of the index should be doing an index seek - unless
> the optimizer decides that a full scan is more efficient (for example
> because criteria will return 90% of the rows anyway).
> Kind regards
> robert
Use my example below. Compare the execution plans of the two SELECT
statements. Illustrates exactly the point I was trying to make:
CREATE TABLE #IndexTest
(
Col1 INT,
Col2 INT,
Col3 CHAR(1),
Col4 CHAR(1),
Col5 DATETIME,
PRIMARY KEY CLUSTERED (Col1, Col2, Col3, Col4, Col5)
)
INSERT INTO #IndexTest
(Col1, Col2, Col3, Col4, Col5)
VALUES
(1, 10, 'A', 'Z', DATEADD(dd, -1, GETDATE()))
INSERT INTO #IndexTest
(Col1, Col2, Col3, Col4, Col5)
VALUES
(2, 20, 'B', 'Y', DATEADD(dd, -2, GETDATE()))
INSERT INTO #IndexTest
(Col1, Col2, Col3, Col4, Col5)
VALUES
(3, 30, 'C', 'X', DATEADD(dd, -3, GETDATE()))
INSERT INTO #IndexTest
(Col1, Col2, Col3, Col4, Col5)
VALUES
(4, 40, 'D', 'W', DATEADD(dd, -4, GETDATE()))
INSERT INTO #IndexTest
(Col1, Col2, Col3, Col4, Col5)
VALUES
(5, 50, 'E', 'V', DATEADD(dd, -5, GETDATE()))
INSERT INTO #IndexTest
(Col1, Col2, Col3, Col4, Col5)
VALUES
(6, 60, 'F', 'U', DATEADD(dd, -6, GETDATE()))
INSERT INTO #IndexTest
(Col1, Col2, Col3, Col4, Col5)
VALUES
(7, 70, 'G', 'T', DATEADD(dd, -7, GETDATE()))
INSERT INTO #IndexTest
(Col1, Col2, Col3, Col4, Col5)
VALUES
(8, 80, 'H', 'S', DATEADD(dd, -8, GETDATE()))
INSERT INTO #IndexTest
(Col1, Col2, Col3, Col4, Col5)
VALUES
(9, 90, 'I', 'R', DATEADD(dd, -9, GETDATE()))
INSERT INTO #IndexTest
(Col1, Col2, Col3, Col4, Col5)
VALUES
(10, 100, 'J', 'Q', DATEADD(dd, -10, GETDATE()))
SELECT * FROM #IndexTest WHERE Col1 = 1
SELECT * FROM #IndexTest WHERE Col3 = 'C'
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On 29.11.2006 14:24, Tracy McKibben wrote:
You said:
> If this is the only index available, then only queries that
> include IDNumber in the WHERE clause will seek against this index.
Then I wrote:
> Robert Klemme wrote:
>> I beg to differ: /all/ queries containing filters on any set of
>> /leading/ columns of the index should be doing an index seek - unless
>> the optimizer decides that a full scan is more efficient (for example
>> because criteria will return 90% of the rows anyway).
> Use my example below. Compare the execution plans of the two SELECT
> statements. Illustrates exactly the point I was trying to make:
> CREATE TABLE #IndexTest
> (
> Col1 INT,
> Col2 INT,
> Col3 CHAR(1),
> Col4 CHAR(1),
> Col5 DATETIME,
> PRIMARY KEY CLUSTERED (Col1, Col2, Col3, Col4, Col5)
> )
> INSERT INTO #IndexTest
> (Col1, Col2, Col3, Col4, Col5)
> VALUES
> (1, 10, 'A', 'Z', DATEADD(dd, -1, GETDATE()))
> INSERT INTO #IndexTest
> (Col1, Col2, Col3, Col4, Col5)
> VALUES
> (2, 20, 'B', 'Y', DATEADD(dd, -2, GETDATE()))
> INSERT INTO #IndexTest
> (Col1, Col2, Col3, Col4, Col5)
> VALUES
> (3, 30, 'C', 'X', DATEADD(dd, -3, GETDATE()))
> INSERT INTO #IndexTest
> (Col1, Col2, Col3, Col4, Col5)
> VALUES
> (4, 40, 'D', 'W', DATEADD(dd, -4, GETDATE()))
> INSERT INTO #IndexTest
> (Col1, Col2, Col3, Col4, Col5)
> VALUES
> (5, 50, 'E', 'V', DATEADD(dd, -5, GETDATE()))
> INSERT INTO #IndexTest
> (Col1, Col2, Col3, Col4, Col5)
> VALUES
> (6, 60, 'F', 'U', DATEADD(dd, -6, GETDATE()))
> INSERT INTO #IndexTest
> (Col1, Col2, Col3, Col4, Col5)
> VALUES
> (7, 70, 'G', 'T', DATEADD(dd, -7, GETDATE()))
> INSERT INTO #IndexTest
> (Col1, Col2, Col3, Col4, Col5)
> VALUES
> (8, 80, 'H', 'S', DATEADD(dd, -8, GETDATE()))
> INSERT INTO #IndexTest
> (Col1, Col2, Col3, Col4, Col5)
> VALUES
> (9, 90, 'I', 'R', DATEADD(dd, -9, GETDATE()))
> INSERT INTO #IndexTest
> (Col1, Col2, Col3, Col4, Col5)
> VALUES
> (10, 100, 'J', 'Q', DATEADD(dd, -10, GETDATE()))
> SELECT * FROM #IndexTest WHERE Col1 = 1
> SELECT * FROM #IndexTest WHERE Col3 = 'C'
The second SELECT does not use a set of /leading/ columns of the index!
StmtText
---
SELECT * FROM #IndexTest WHERE Col1 = 1
(1 row(s) affected)
StmtText
------
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#IndexTest]),
SEEK:([tempdb].[dbo].[#IndexTest].[Col1]=(1)) ORDERED FORWARD)
(1 row(s) affected)
StmtText
---
SELECT * FROM #IndexTest WHERE Col3 = 'C'
(1 row(s) affected)
StmtText
------
|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#IndexTest]),
WHERE:([tempdb].[dbo].[#IndexTest].[Col3]='C'))
(1 row(s) affected)
StmtText
----
SELECT * FROM #IndexTest WHERE Col1 = 1 AND Col2 = 20 AND Col3 = 'C'
(1 row(s) affected)
StmtText
--------
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#IndexTest]),
SEEK:([tempdb].[dbo].[#IndexTest].[Col1]=(1) AND
[tempdb].[dbo].[#IndexTest].[Col2]=(20) AND
[tempdb].[dbo].[#IndexTest].[Col3]='C') ORDERED FORWARD)
(1 row(s) affected)
Q.E.D.
Regards
robert|||Robert Klemme wrote:
> On 29.11.2006 14:24, Tracy McKibben wrote:
> You said:
>> If this is the only index available, then only queries that
> > include IDNumber in the WHERE clause will seek against this index.
> Then I wrote:
>> Robert Klemme wrote:
>> I beg to differ: /all/ queries containing filters on any set of
>> /leading/ columns of the index should be doing an index seek - unless
>> the optimizer decides that a full scan is more efficient (for example
>> because criteria will return 90% of the rows anyway).
>> Use my example below. Compare the execution plans of the two SELECT
>> statements. Illustrates exactly the point I was trying to make:
>> CREATE TABLE #IndexTest
>> (
>> Col1 INT,
>> Col2 INT,
>> Col3 CHAR(1),
>> Col4 CHAR(1),
>> Col5 DATETIME,
>> PRIMARY KEY CLUSTERED (Col1, Col2, Col3, Col4, Col5)
>> )
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (1, 10, 'A', 'Z', DATEADD(dd, -1, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (2, 20, 'B', 'Y', DATEADD(dd, -2, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (3, 30, 'C', 'X', DATEADD(dd, -3, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (4, 40, 'D', 'W', DATEADD(dd, -4, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (5, 50, 'E', 'V', DATEADD(dd, -5, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (6, 60, 'F', 'U', DATEADD(dd, -6, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (7, 70, 'G', 'T', DATEADD(dd, -7, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (8, 80, 'H', 'S', DATEADD(dd, -8, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (9, 90, 'I', 'R', DATEADD(dd, -9, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (10, 100, 'J', 'Q', DATEADD(dd, -10, GETDATE()))
>> SELECT * FROM #IndexTest WHERE Col1 = 1
>> SELECT * FROM #IndexTest WHERE Col3 = 'C'
> The second SELECT does not use a set of /leading/ columns of the index!
> StmtText
> ---
> SELECT * FROM #IndexTest WHERE Col1 = 1
> (1 row(s) affected)
> StmtText
> ------
> |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#IndexTest]),
> SEEK:([tempdb].[dbo].[#IndexTest].[Col1]=(1)) ORDERED FORWARD)
> (1 row(s) affected)
> StmtText
> ---
> SELECT * FROM #IndexTest WHERE Col3 = 'C'
> (1 row(s) affected)
> StmtText
> ------
> |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#IndexTest]),
> WHERE:([tempdb].[dbo].[#IndexTest].[Col3]='C'))
> (1 row(s) affected)
> StmtText
> ----
> SELECT * FROM #IndexTest WHERE Col1 = 1 AND Col2 = 20 AND Col3 = 'C'
> (1 row(s) affected)
> StmtText
> --------
> |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#IndexTest]),
> SEEK:([tempdb].[dbo].[#IndexTest].[Col1]=(1) AND
> [tempdb].[dbo].[#IndexTest].[Col2]=(20) AND
> [tempdb].[dbo].[#IndexTest].[Col3]='C') ORDERED FORWARD)
> (1 row(s) affected)
> Q.E.D.
> Regards
> robert
?
I don't really know what you're debating here. I said that if IDNumber
(the leading column) wasn't used in the WHERE clause, an index seek
wouldn't happen. You disagreed with me, but then posted an example that
proves my point exactly. What am I missing?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On 29.11.2006 18:54, Tracy McKibben wrote:
> Robert Klemme wrote:
>> On 29.11.2006 14:24, Tracy McKibben wrote:
>> You said:
>> If this is the only index available, then only queries that
>> > include IDNumber in the WHERE clause will seek against this index.
>> Then I wrote:
>> Robert Klemme wrote:
>> I beg to differ: /all/ queries containing filters on any set of
>> /leading/ columns of the index should be doing an index seek -
>> unless the optimizer decides that a full scan is more efficient (for
>> example because criteria will return 90% of the rows anyway).
>> Use my example below. Compare the execution plans of the two SELECT
>> statements. Illustrates exactly the point I was trying to make:
>> CREATE TABLE #IndexTest
>> (
>> Col1 INT,
>> Col2 INT,
>> Col3 CHAR(1),
>> Col4 CHAR(1),
>> Col5 DATETIME,
>> PRIMARY KEY CLUSTERED (Col1, Col2, Col3, Col4, Col5)
>> )
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (1, 10, 'A', 'Z', DATEADD(dd, -1, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (2, 20, 'B', 'Y', DATEADD(dd, -2, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (3, 30, 'C', 'X', DATEADD(dd, -3, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (4, 40, 'D', 'W', DATEADD(dd, -4, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (5, 50, 'E', 'V', DATEADD(dd, -5, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (6, 60, 'F', 'U', DATEADD(dd, -6, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (7, 70, 'G', 'T', DATEADD(dd, -7, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (8, 80, 'H', 'S', DATEADD(dd, -8, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (9, 90, 'I', 'R', DATEADD(dd, -9, GETDATE()))
>> INSERT INTO #IndexTest
>> (Col1, Col2, Col3, Col4, Col5)
>> VALUES
>> (10, 100, 'J', 'Q', DATEADD(dd, -10, GETDATE()))
>> SELECT * FROM #IndexTest WHERE Col1 = 1
>> SELECT * FROM #IndexTest WHERE Col3 = 'C'
>> The second SELECT does not use a set of /leading/ columns of the index!
>> StmtText
>> ---
>> SELECT * FROM #IndexTest WHERE Col1 = 1
>> (1 row(s) affected)
>> StmtText
>> ------
>> |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#IndexTest]),
>> SEEK:([tempdb].[dbo].[#IndexTest].[Col1]=(1)) ORDERED FORWARD)
>> (1 row(s) affected)
>> StmtText
>> ---
>> SELECT * FROM #IndexTest WHERE Col3 = 'C'
>> (1 row(s) affected)
>> StmtText
>> ------
>> |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#IndexTest]),
>> WHERE:([tempdb].[dbo].[#IndexTest].[Col3]='C'))
>> (1 row(s) affected)
>> StmtText
>> ----
>>
>> SELECT * FROM #IndexTest WHERE Col1 = 1 AND Col2 = 20 AND Col3 = 'C'
>> (1 row(s) affected)
>> StmtText
>> --------
>> |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#IndexTest]),
>> SEEK:([tempdb].[dbo].[#IndexTest].[Col1]=(1) AND
>> [tempdb].[dbo].[#IndexTest].[Col2]=(20) AND
>> [tempdb].[dbo].[#IndexTest].[Col3]='C') ORDERED FORWARD)
>> (1 row(s) affected)
>> Q.E.D.
>> Regards
>> robert
> ?
> I don't really know what you're debating here. I said that if IDNumber
> (the leading column) wasn't used in the WHERE clause, an index seek
> wouldn't happen. You disagreed with me, but then posted an example that
> proves my point exactly. What am I missing?
You said "If this is the only index available, then /only/ queries that
include IDNumber in the WHERE clause will seek against this index."
(accentuation by me). I objected that because /also/ queries that
contain /more leading columns/ from the index do a seek which is nicely
demonstrated by the plans I posted.
robert|||Why such a big primary key? Is it really necessary?
Try to cover your queries with indexes. If you're not lookin up the
records using the leading column in your primay key index, you should
plane an nonclustered index to cover your query.sql

Problem With Cluster !

Hello to everyone
I have two dell Poweredge 2650 units and a 6 Shared Hard drives with format
RAID -5 . The two units are clustered together via windows clustering
services. Software called PowerSun on each machines along with the cluster
owner tells which has ownership of the SAN.
What I need:
2 clustered machines, 1 shared storage. I will install my application on
both machines and have the database( or actual application, whichever works )
contained on the shared storeage. When one machines goes down, I want the
other machine to automatically start the application up. IS THIS
POSSIBLE?
This is very important to me, and would really appreciate any help that is
given.
Dimos-T
Hi
When you install SQL 2000 Enterprize Edition on a cluster, it sets up that
automatically for itself.
If you want your application be be started, make sure that you application
is "cluster aware" (look on MSDN and TechNet for information on that).
SQL BOL and Windows BOL have a lot of information on clustering.
Regards
Mike
"DImos GRIZANO-TRIKALWN" wrote:

> Hello to everyone
> I have two dell Poweredge 2650 units and a 6 Shared Hard drives with format
> RAID -5 . The two units are clustered together via windows clustering
> services. Software called PowerSun on each machines along with the cluster
> owner tells which has ownership of the SAN.
> What I need:
> 2 clustered machines, 1 shared storage. I will install my application on
> both machines and have the database( or actual application, whichever works )
> contained on the shared storeage. When one machines goes down, I want the
> other machine to automatically start the application up. IS THIS
> POSSIBLE?
> This is very important to me, and would really appreciate any help that is
> given.
> Dimos-T
>
|||Hi Dimos_m,
Please note it's recommended not to put your SQL data and log files on the
shared quorum disk so you should have a seperate shared disk as the SQL
server disk resource to located the data and log files (even better is to
have two to give data and log a seperate disk each).
Regards,
Jago
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> When you install SQL 2000 Enterprize Edition on a cluster, it sets up that
> automatically for itself.
> If you want your application be be started, make sure that you application
> is "cluster aware" (look on MSDN and TechNet for information on that).
> SQL BOL and Windows BOL have a lot of information on clustering.
> Regards
> Mike
> "DImos GRIZANO-TRIKALWN" wrote:
|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:F6EF3879-BF1D-4887-ABA7-B5E12492EEB2@.microsoft.com...
> Hi
> When you install SQL 2000 Enterprize Edition on a cluster, it sets up that
> automatically for itself.
> If you want your application be be started, make sure that you application
> is "cluster aware" (look on MSDN and TechNet for information on that).
>
It's not 100% necessary that your app be cluster aware, but it can aid some
things.
We have a clustered pair of servers, one of which runs an FTP server (not
IIS).
When the cluster failovers over, it cleanly starts up on the new machine.

Wednesday, March 21, 2012

problem with a clustered index?

Hello,
I have a table with about 5 milion records, and the performance is un-acceptable (more than 45 seconds for a query). The table was imported from a text file. I have defined a primary key (two varchar(20) columns). Then I checked and the database has created a clustered index on these columns. But I suspect that something is wrong with the index: When I run a select query, the rows are returned in an arbitrary order, and not in the order defined by the index. Is it possible that something is wrong with the index? If yes, how can I fix that?
thanks, David
David
Did you spesify ORDER BY clause when you run SELECT statement.
What is your search criteria in the query?
"David" <dboaz@.bgumail.bgu.ac.il> wrote in message news:um00pGXdEHA.3148@.TK2MSFTNGP10.phx.gbl...
Hello,
I have a table with about 5 milion records, and the performance is un-acceptable (more than 45 seconds for a query). The table was imported from a text file. I have defined a primary key (two varchar(20) columns). Then I checked and the database has created a clustered index on these columns. But I suspect that something is wrong with the index: When I run a select query, the rows are returned in an arbitrary order, and not in the order defined by the index. Is it possible that something is wrong with the index? If yes, how can I fix that?
thanks, David
|||A clustered index does not determine the sort order of a query result, so
there is no reason to suspect any integrity problem. Use an ORDER BY clause
in your SELECT statement to fix the order of the returned rows.
Did you check the query plan to see what indexes are being used? Could you
post the query and a CREATE TABLE statement for the table to give us an idea
of what optimizations might be possible.
David Portas
SQL Server MVP
|||> When I run a select query, the rows are returned in an arbitrary order
A table is an unordered set of rows. What were you expecting? If you want
a defined order, use an ORDER BY clause.
I don't know where everyone gets the idea that the existence of a clustered
index means that is the order all SELECT queries will return the data. This
is NOT TRUE! It does happen more often that way, but it is not a law. The
plan will return the rows in the best way it sees fit, you could run the
query 10 times and it *could* return the rows 10 different ways. Usually
doesn't, but could.
I'll repeat: if you want a specific order, use an ORDER BY clause.
http://www.aspfaq.com/
(Reverse address to reply.)

problem with a clustered index?

Hello,
I have a table with about 5 milion records, and the performance is un-accept
able (more than 45 seconds for a query). The table was imported from a text
file. I have defined a primary key (two varchar(20) columns). Then I checked
and the database has created a clustered index on these columns. But I susp
ect that something is wrong with the index: When I run a select query, the r
ows are returned in an arbitrary order, and not in the order defined by the
index. Is it possible that something is wrong with the index? If yes, how ca
n I fix that?
thanks, DavidDavid
Did you spesify ORDER BY clause when you run SELECT statement.
What is your search criteria in the query?
"David" <dboaz@.bgumail.bgu.ac.il> wrote in message news:um00pGXdEHA.3148@.TK2
MSFTNGP10.phx.gbl...
Hello,
I have a table with about 5 milion records, and the performance is un-accept
able (more than 45 seconds for a query). The table was imported from a text
file. I have defined a primary key (two varchar(20) columns). Then I checked
and the database has created a clustered index on these columns. But I susp
ect that something is wrong with the index: When I run a select query, the r
ows are returned in an arbitrary order, and not in the order defined by the
index. Is it possible that something is wrong with the index? If yes, how ca
n I fix that?
thanks, David|||A clustered index does not determine the sort order of a query result, so
there is no reason to suspect any integrity problem. Use an ORDER BY clause
in your SELECT statement to fix the order of the returned rows.
Did you check the query plan to see what indexes are being used? Could you
post the query and a CREATE TABLE statement for the table to give us an idea
of what optimizations might be possible.
David Portas
SQL Server MVP
--|||> When I run a select query, the rows are returned in an arbitrary order
A table is an unordered set of rows. What were you expecting? If you want
a defined order, use an ORDER BY clause.
I don't know where everyone gets the idea that the existence of a clustered
index means that is the order all SELECT queries will return the data. This
is NOT TRUE! It does happen more often that way, but it is not a law. The
plan will return the rows in the best way it sees fit, you could run the
query 10 times and it *could* return the rows 10 different ways. Usually
doesn't, but could.
I'll repeat: if you want a specific order, use an ORDER BY clause.
http://www.aspfaq.com/
(Reverse address to reply.)sql

problem with a clustered index?

This is a multi-part message in MIME format.
--=_NextPart_000_0052_01C4758A.B87F1790
Content-Type: text/plain;
charset="windows-1255"
Content-Transfer-Encoding: quoted-printable
Hello,
I have a table with about 5 milion records, and the performance is = un-acceptable (more than 45 seconds for a query). The table was imported = from a text file. I have defined a primary key (two varchar(20) = columns). Then I checked and the database has created a clustered index = on these columns. But I suspect that something is wrong with the index: = When I run a select query, the rows are returned in an arbitrary order, = and not in the order defined by the index. Is it possible that something = is wrong with the index? If yes, how can I fix that?
thanks, David
--=_NextPart_000_0052_01C4758A.B87F1790
Content-Type: text/html;
charset="windows-1255"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hello,
I have a table with about 5 milion = records, and the performance is un-acceptable (more than 45 seconds for a query). The = table was imported from a text file. I have defined a primary key (two varchar(20) = columns). Then I checked and the database has created a clustered index = on these columns. But I suspect that something is wrong with the index: When I = run a select query, the rows are returned in an arbitrary order, and not = in the order defined by the index. Is it possible that something is wrong with = the index? If yes, how can I fix that?
thanks, = David
--=_NextPart_000_0052_01C4758A.B87F1790--This is a multi-part message in MIME format.
--=_NextPart_000_000B_01C4758B.8E928F10
Content-Type: text/plain;
charset="windows-1255"
Content-Transfer-Encoding: quoted-printable
David
Did you spesify ORDER BY clause when you run SELECT statement.
What is your search criteria in the query?
"David" <dboaz@.bgumail.bgu.ac.il> wrote in message =news:um00pGXdEHA.3148@.TK2MSFTNGP10.phx.gbl...
Hello,
I have a table with about 5 milion records, and the performance is =un-acceptable (more than 45 seconds for a query). The table was imported =from a text file. I have defined a primary key (two varchar(20) =columns). Then I checked and the database has created a clustered index =on these columns. But I suspect that something is wrong with the index: =When I run a select query, the rows are returned in an arbitrary order, =and not in the order defined by the index. Is it possible that something =is wrong with the index? If yes, how can I fix that?
thanks, David
--=_NextPart_000_000B_01C4758B.8E928F10
Content-Type: text/html;
charset="windows-1255"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

David
Did you spesify ORDER BY clause when =you run SELECT statement.
What is your search criteria in the query?
"David" =wrote in message news:um00pGXdEHA.3148=@.TK2MSFTNGP10.phx.gbl...
Hello,
I have a table with about 5 milion =records, and the performance is un-acceptable (more than 45 seconds for a query). =The table was imported from a text file. I have defined a primary key (two =varchar(20) columns). Then I checked and the database has created a clustered =index on these columns. But I suspect that something is wrong with the index: =When I run a select query, the rows are returned in an arbitrary order, =and not in the order defined by the index. Is it possible that something is =wrong with the index? If yes, how can I fix that?
thanks, David

--=_NextPart_000_000B_01C4758B.8E928F10--|||A clustered index does not determine the sort order of a query result, so
there is no reason to suspect any integrity problem. Use an ORDER BY clause
in your SELECT statement to fix the order of the returned rows.
Did you check the query plan to see what indexes are being used? Could you
post the query and a CREATE TABLE statement for the table to give us an idea
of what optimizations might be possible.
--
David Portas
SQL Server MVP
--|||> When I run a select query, the rows are returned in an arbitrary order
A table is an unordered set of rows. What were you expecting? If you want
a defined order, use an ORDER BY clause.
I don't know where everyone gets the idea that the existence of a clustered
index means that is the order all SELECT queries will return the data. This
is NOT TRUE! It does happen more often that way, but it is not a law. The
plan will return the rows in the best way it sees fit, you could run the
query 10 times and it *could* return the rows 10 different ways. Usually
doesn't, but could.
I'll repeat: if you want a specific order, use an ORDER BY clause.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Try a DBCC DBREINDEX or DBCC INDEXDEFRAG depending upon
your preference, then update the statistics.
It sometimes happends when you do a bulk insert that the
statistics need re-doing (in my opinion).
Peter
>--Original Message--
>Hello,
>I have a table with about 5 milion records, and the
performance is un-acceptable (more than 45 seconds for a
query). The table was imported from a text file. I have
defined a primary key (two varchar(20) columns). Then I
checked and the database has created a clustered index on
these columns. But I suspect that something is wrong with
the index: When I run a select query, the rows are
returned in an arbitrary order, and not in the order
defined by the index. Is it possible that something is
wrong with the index? If yes, how can I fix that?
>thanks, David

Monday, March 12, 2012

Problem when rebuild system databases for a clustered instance of

Has anyone rebuilt system databases for a SQL2005 clustered instance?
I have followed the code from "To rebuild system databases for a clustered."
section at http://msdn2.microsoft.com/en-us/library/ms144259.aspx, and keep
getting message saying to add more parameters. I added the parameter
whenever it required, "Group", then "addnode", even INSTALLSQLDATADIR = "S:\"
which data should go.
The summary.txt says "Setup succeeded with the installation". But in the
files\setup_*_core.log for both node2, I have this error:
Error: Action "LaunchLocalBootstrapAction" threw an exception during
execution. Error information reported during run:
"C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\setup.exe"
finished and returned: 0
Aborting queue processing as nested installer has completed
Message pump returning: 0
I want to know whether anyone has similar problem or succeed when rebuild
system DBs for a cluster.
What are all the parameters needed for rebuilding sys databases on a
cluster? Is there anything should be aware of?
Hi Julia
Did you manage to resolve this problem? I am experiencing exactly the
same thing

.
Cheers
Jesse
*** Sent via Developersdex http://www.codecomments.com ***
|||No. I have contacted Microsoft but no answer. I would recommend you report
this problem as well to them and get them to look into it.
"Jesse Easton" wrote:

>
> Hi Julia
> Did you manage to resolve this problem? I am experiencing exactly the
> same thing

.
> Cheers
> Jesse
> *** Sent via Developersdex http://www.codecomments.com ***
>

Monday, February 20, 2012

Problem upgrading clustered node to SP1

We just upgraded a sql 2K Active\Active 2 node cluster to SQL2005. The upgrade seemed to go OK.

We also managed to apply SP1 to Node 1 after an initial problem

But now when applying SP1 to NODE 2 we are getting this error

"A recently applied update, NULL, failed to install"

We had the same problem on NODE 1. Turns out after installing client connection tools and management tools SP1 succeeded. But it does not seem to work on Node2

And yes both nodes have been rebooted.

Any help is appreciated. thanks

Here is the error log

05/18/2006 11:39:57.414 ================================================================================
05/18/2006 11:39:57.414 Hotfix package launched
05/18/2006 11:39:57.430 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
05/18/2006 11:39:57.445 Successfully read registry key: CommonFilesDir, string value = C:\Program Files\Common Files
05/18/2006 11:39:57.461 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
05/18/2006 11:39:57.477 Successfully read registry key: ProgramFilesDir, string value = C:\Program Files
05/18/2006 11:39:57.602 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
05/18/2006 11:39:57.633 Successfully read registry key: CommonFilesDir, string value = C:\Program Files\Common Files
05/18/2006 11:39:57.633 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
05/18/2006 11:39:57.649 Successfully read registry key: ProgramFilesDir, string value = C:\Program Files
05/18/2006 11:39:57.664 Local Computer:
05/18/2006 11:39:57.664 Target Details: OSSQL12
05/18/2006 11:39:57.680 commonfilesdir = C:\Program Files\Common Files
05/18/2006 11:39:57.695 lcidsupportdir = x:\ae3976232fdba78db1a56fa98a9693\1033
05/18/2006 11:39:57.711 programfilesdir = C:\Program Files
05/18/2006 11:39:57.727 supportdir = \\OSSQL12\x$\ae3976232fdba78db1a56fa98a9693
05/18/2006 11:39:57.727 supportdirlocal = x:\ae3976232fdba78db1a56fa98a9693
05/18/2006 11:39:57.742 windir = C:\WINDOWS
05/18/2006 11:39:57.758 winsysdir = C:\WINDOWS\system32
05/18/2006 11:39:57.774
05/18/2006 11:39:57.789 Enumerating applicable products for this patch
05/18/2006 11:39:59.867 The patch installation could not proceed due to unexpected errors
05/18/2006 11:39:59.883
05/18/2006 11:39:59.883 Product Status Summary:
05/18/2006 11:40:00.024 Hotfix package closed

You can failover SQL resources to the second node and then try SP1 on that node. This is a peculiar problem and I doubt you can reproduce the problem.

Friday, March 30, 2012

Problem with clustered index

Problem with clustered index

Problem With Cluster !

Wednesday, March 21, 2012

problem with a clustered index?

problem with a clustered index?

problem with a clustered index?

Monday, March 12, 2012

Problem when rebuild system databases for a clustered instance of

Monday, February 20, 2012

Problem upgrading clustered node to SP1

updating text datatype

Blog Archive

About Me