updating text datatype: defined

Showing posts with label defined. 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 calling methods of User Defined Types (udt)

Hello all,

I have scoured the internet looking for the answer to this question, but have come up blank, so I am making my very first post to a help site. Any ideas or solutions would be greatly appreciated.

I am running through some Samples in Sql Server 2005, and I am currently on the part about User Defined Types. I am running the "User-Defined Data Type (UDT) Sample" from this page: http://msdn2.microsoft.com/en-us/library/ms160738.aspx

So I'm using this code for a ComplexNumber type. The code works fine... mostly. I've compiled it and successfully loaded it into Sql Server 2005 (local instance). I can call all the functions like ToString() and what-not, but the problem comes when I try to call the CompareTo() function. If I pass in something other than a ComplexNumber type (such as a string), then the method correctly returns a -1. When I pass in a ComplexNumber type, I get the following error from the query window:

"Operand type clash: ComplexNumber is incompatible with sql_variant"

I am not declaring the variables as type sql_variant. Also, I have tried creating the second ComplexNumber type (that I am comparing it to) right inside the method call, as below:

DECLARE @.c ComplexNumber;
DECLARE @.c2 ComplexNumber;
DECLARE @.myInt int;
DECLARE @.vari sql_variant;
SET @.c = ComplexNumber::Parse('(1, 2i)');
SET @.c2 = ComplexNumber::Parse('(1, 3i)');

All of the following lines get the same error code:
SET myInt = @.c.CompareTo(@.C2);
SET myInt = @.c.CompareTo(ComplexNumber::Parse('(1, 3i)'));
@.vari = @.c;

That last one I just threw in there because I knew it had the same error code. Does anyone know why this is happening and what I can do to fix it? Even hints would be appreciated at this point (although solutions score bigger points!)

Banging my head against my cubicle,
Tim

I am not an expert, but I asked some, and the reply I got was:

"If he's implemented IComparable, the typical signature for CompareTo is:

public int CompareTo(object obj)

.NET object is treated as SQL_VARIANT, however, UDTs aren't compatiable with
SQL_VARIANT.

A better way to write the interface *should be*:

public int CompareTo(ComplexNumber obj)

This violates the IComparable interface, however: it requires object.

Since SQL Server doesn't use ICompareable for indexing, order by, group by,
implementing it is more or less useless. If I need to compare two instances,
I think its better to create a strongly-typed static method on the class
that does exactly that, returning SqlInt16 instead of int.

However, if you need IComparable for other reasons, then you have to live
with wart."

Does this help?

|||Wow, thanks man. All the examples I looked at (from Microsoft, no less) had implemented the IComparable. I had tried overloading the method before,only to find out that SQL Server doesn't support overloaded methods in UDTs. Anyways, since IComparable is useless, I took it out and it worked like a charm.

Thanks again!
Tim|||

I have a problem looks similar but it is little bit different. you are right that sqlserver is not using ICompareable.

but I using Data gridview Control with User defined Types. Problem is while doing sorting on this column it gives exception that Icomparable requires to be implemented. though i have implemented that Interface. but as SQL server is not using that method then who creates that exception. this is not problem with any other column of Datagridview. and i have created that User defined type with native serialization and also made IsByteOrdered to true.

Assembly is sucessfully deployed and i am able to insert and select data in datagridview. only problem while pressing column header of UDT column.

it seems that exception is thrown by sqlserver while comparision. here is the stack trace for exception.

" at System.Data.Common.SqlUdtStorage.CompareValueTo(Int32 recordNo1, Object value)\r\n at System.Data.Common.SqlUdtStorage.Compare(Int32 recordNo1, Int32 recordNo2)\r\n at System.Data.Index.CompareRecords(Int32 record1, Int32 record2)\r\n at System.Data.Index.IndexTree.CompareNode(Int32 record1, Int32 record2)\r\n at System.Data.RBTree`1.RBInsert(Int32 root_id, Int32 x_id, Int32 position)\r\n at System.Data.RBTree`1.Insert(K item)\r\n at System.Data.Index.InitRecords(IFilter filter)\r\n at System.Data.Index..ctor(DataTable table, Int32[] ndexDesc, IndexField[] indexFields, DataViewRowState recordStates, IFilter rowFilter)\r\n at System.Data.DataTable.GetIndex(IndexField[] indexDesc, DataViewRowState recordStates, IFilter rowFilter)\r\n at System.Data.DataView.UpdateIndex(Boolean force, Boolean fireEvent)\r\n at System.Data.DataView.UpdateIndex(Boolean force)\r\n at System.Data.DataView.SetIndex2(String newSort, DataViewRowState newRowStates, DataExpression newRowFilter, Boolean fireEvent)\r\n at System.Data.DataView.SetIndex(String newSort, DataViewRowState newRowStates, DataExpression newRowFilter)\r\n at System.Data.DataView.set_Sort(String value)\r\n at System.Data.DataView.System.ComponentModel.IBindingList.ApplySort(PropertyDescriptor property, ListSortDirection direction)\r\n at System.Windows.Forms.DataGridView.DataGridViewDataConnection.Sort(DataGridViewColumn dataGridViewColumn, ListSortDirection direction)\r\n at System.Windows.Forms.DataGridView.SortInternal(IComparer comparer, DataGridViewColumn dataGridViewColumn, ListSortDirection direction)\r\n at System.Windows.Forms.DataGridView.Sort(DataGridViewColumn dataGridViewColumn, ListSortDirection direction)\r\n at System.Windows.Forms.DataGridView.OnColumnHeaderMouseClick(DataGridViewCellMouseEventArgs e)\r\n at System.Windows.Forms.DataGridView.OnMouseClick(MouseEventArgs e)\r\n at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)\r\n at System.Windows.Forms.Control.WndProc(Message& m)\r\n at System.Windows.Forms.DataGridView.WndProc(Message& m)\r\n at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)\r\n at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)\r\n at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)\r\n at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)\r\n at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)\r\n at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)\r\n at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)\r\n at System.Windows.Forms.Application.Run(Form mainForm)\r\n at testAlarmType.Program.Main() in c:\\Manoj\\CSharp\\Work\\testAlarmType\\testAlarmType\\Program.cs:line 17\r\n at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)\r\n at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)\r\n at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()\r\n at System.Threading.ThreadHelper.ThreadStart_Context(Object state)\r\n at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)\r\n at System.Threading.ThreadHelper.ThreadStart()"

let me know if any one has any workaround.

Thanks in advance.

|||I have tried to look at this, and while it seems that IComparable is not used by SQL Server 2005 it is used when ordering DataTables, which seems to be an oversight when the requirements for UDTs were written. I write more in this thread.

Problem with calling methods of User Defined Types (udt)

Hello all,

I have scoured the internet looking for the answer to this question, but have come up blank, so I am making my very first post to a help site. Any ideas or solutions would be greatly appreciated.

"Operand type clash: ComplexNumber is incompatible with sql_variant"

I am not declaring the variables as type sql_variant. Also, I have tried creating the second ComplexNumber type (that I am comparing it to) right inside the method call, as below:

DECLARE @.c ComplexNumber;
DECLARE @.c2 ComplexNumber;
DECLARE @.myInt int;
DECLARE @.vari sql_variant;
SET @.c = ComplexNumber::Parse('(1, 2i)');
SET @.c2 = ComplexNumber::Parse('(1, 3i)');

All of the following lines get the same error code:
SET myInt = @.c.CompareTo(@.C2);
SET myInt = @.c.CompareTo(ComplexNumber::Parse('(1, 3i)'));
@.vari = @.c;

Banging my head against my cubicle,
Tim

I am not an expert, but I asked some, and the reply I got was:

"If he's implemented IComparable, the typical signature for CompareTo is:

public int CompareTo(object obj)

.NET object is treated as SQL_VARIANT, however, UDTs aren't compatiable with
SQL_VARIANT.

A better way to write the interface *should be*:

public int CompareTo(ComplexNumber obj)

This violates the IComparable interface, however: it requires object.

However, if you need IComparable for other reasons, then you have to live
with wart."

Does this help?

Thanks again!
Tim
|||

I have a problem looks similar but it is little bit different. you are right that sqlserver is not using ICompareable.

Assembly is sucessfully deployed and i am able to insert and select data in datagridview. only problem while pressing column header of UDT column.

it seems that exception is thrown by sqlserver while comparision. here is the stack trace for exception.

let me know if any one has any workaround.

Thanks in advance.

Problem with calling methods of User Defined Types (udt)

Hello all,

I have scoured the internet looking for the answer to this question, but have come up blank, so I am making my very first post to a help site. Any ideas or solutions would be greatly appreciated.

"Operand type clash: ComplexNumber is incompatible with sql_variant"

I am not declaring the variables as type sql_variant. Also, I have tried creating the second ComplexNumber type (that I am comparing it to) right inside the method call, as below:

DECLARE @.c ComplexNumber;
DECLARE @.c2 ComplexNumber;
DECLARE @.myInt int;
DECLARE @.vari sql_variant;
SET @.c = ComplexNumber::Parse('(1, 2i)');
SET @.c2 = ComplexNumber::Parse('(1, 3i)');

All of the following lines get the same error code:
SET myInt = @.c.CompareTo(@.C2);
SET myInt = @.c.CompareTo(ComplexNumber::Parse('(1, 3i)'));
@.vari = @.c;

Banging my head against my cubicle,
Tim

I am not an expert, but I asked some, and the reply I got was:

"If he's implemented IComparable, the typical signature for CompareTo is:

public int CompareTo(object obj)

.NET object is treated as SQL_VARIANT, however, UDTs aren't compatiable with
SQL_VARIANT.

A better way to write the interface *should be*:

public int CompareTo(ComplexNumber obj)

This violates the IComparable interface, however: it requires object.

However, if you need IComparable for other reasons, then you have to live
with wart."

Does this help?

Thanks again!
Tim|||

I have a problem looks similar but it is little bit different. you are right that sqlserver is not using ICompareable.

Assembly is sucessfully deployed and i am able to insert and select data in datagridview. only problem while pressing column header of UDT column.

it seems that exception is thrown by sqlserver while comparision. here is the stack trace for exception.

let me know if any one has any workaround.

Thanks in advance.

Friday, March 23, 2012

Problem with adding record into database

When I try to build my solution, it tells me that "SQLCommand" is not defined.

What's the problem?

Sub submitButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
'If Page.IsValid Then
Try
Dim MySQL = "addCustomerSQL"
Dim cmd As New SQLCommand("addCustomerSQL", dbConn)
cmd.Commandtype() = CommandType.StoredProcedure
cmd.Parameters().Item("@.username").Value = userText.Text
cmd.Parameters.Item("@.password").Value = passText.Text
dbConn.Open()
cmd.ExecuteNonQuery()
dbConn.Close()

Catch ex As Exception

End Try
End SubMake sure you're importing System.Data.SqlClient in your class|||Where would I put that?|||At the top of your page type this

Imports System.Data.SqlClient

Public Class......|||To follow up, I was able to get the code to compile, but nothing is being transferred to the database table after clicking submit.


Private Sub submitButton_ServerClick(ByVal sender As System.Object, ByVal e As System.EventArgs) 'If Page.IsValid
 Try
 Dim MySQL = "addCustomerSQL"
 Dim dbConn As New System.Data.SqlClient.SqlConnection
 Dim cmd As New SqlCommand("addCustomerSQL", dbConn)
 cmd.CommandType = CommandType.StoredProcedure
 cmd.Parameters.Add(New SqlParameter("@.username", KuserTextLI.Text))
 cmd.Parameters.Add(New SqlParameter("@.password", KpassTextLI.Text))
 cmd.Parameters.Add(New SqlParameter("@.fullname", KfnameTextLI.Text))
 cmd.Parameters.Add(New SqlParameter("@.email_address", KemailTextLI.Text))
 cmd.Parameters.Add(New SqlParameter("@.phone_nbr", KphoneTextLI.Text))
 cmd.Parameters.Add(New SqlParameter("@.mailing_address", KaddressTextLI.Text))
 cmd.Parameters.Add(New SqlParameter("@.credit_card_name", KccNameTextLI.Text))
 cmd.Parameters.Add(New SqlParameter("@.credit_card_nbr", KccNumTextLI.Text))
 cmd.Parameters.Add(New SqlParameter("@.credit_card_expiry_date", KccExpTextLI.Text))
 cmd.Parameters.Item("@.username").Value = KuserTextLI.Text
 cmd.Parameters.Item("@.password").Value = KpassTextLI.Text
 cmd.Parameters.Item("@.fullname").Value = KfnameTextLI.Text
 cmd.Parameters.Item("@.email_address").Value = KemailTextLI.Text
 cmd.Parameters.Item("@.phone_nbr").Value = KphoneTextLI.Text
 cmd.Parameters.Item("@.mailing_address").Value = KaddressTextLI.Text
 cmd.Parameters.Item("@.credit_card_name").Value = KccNameTextLI.Text
 cmd.Parameters.Item("@.credit_card_nbr").Value = KccNumTextLI.Text
 cmd.Parameters.Item("@.credit_card_expiry_date").Value = KccExpTextLI.Text
 dbConn.Open()
 cmd.ExecuteNonQuery()
 dbConn.Close()
 Response.Redirect("http://localhost/KevinLiu/thankyou.aspx")
 Catch ex As Exception
 End Try
 End Sub

Is there anything I need to put under the Page_Load procedure? Or how about on my html page, do I have to call the event procedure from there?

The Database tables were created first in a .sql file, so is it necessary to include the attributes in my Parameters.Add lines?|||Hi,
im not sure... but i think that the main problem would be the parameter name must be the same as the input variable name in your stored procedure.

anyway your codes are somewhat redundant. The following 2 lines are doing the same thing.

cmd.Parameters.Add(New SqlParameter("@.username", KuserTextLI.Text))
cmd.Parameters.Item("@.username").Value = KuserTextLI.Text

this would be enought if you are adding a sql parameter of type VarChar in:

cmd.Parameters.add(new sqlparameter("@.username", kusertextLi.text))

and this would give you a sql parameter of type Numeric :

cmd.Parameters.add(new sqlparameter("@.age", cint(kagetextLi.text))

note that if your stored procedure has output variables you have to specify the direction of the parameters. Example:

dim param as new sqlparameter("@.username", kusertextLi.text))
param.direction = parameterdirection.input

Friday, March 30, 2012

Problem with clustered index

Problem with clustered index

Problem with calling methods of User Defined Types (udt)

Problem with calling methods of User Defined Types (udt)

Problem with calling methods of User Defined Types (udt)

Friday, March 23, 2012

Problem with adding record into database

updating text datatype

Blog Archive

About Me