updating text datatype: columns

Showing posts with label columns. 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 CAST and CONVERT in SQL Server2000 converting decimal places from 4 to 2

All of my currency columns are only storing 2 decimal places when I insert into the database but when I pull out the data with a SELECT statement, I always get 4 decimal places instead of the 2 that were inserted.

For example:

Database Price SELECT statement Price

100.56 100.5600

I have tried to use the CAST and/or CONVERT commands but I cannot get the output to come out as 100.56. Has anyone had a similar problem?

Thanks

Paradise [ip]

Look at the STR() function in Books Online.

STR(FloatExpression,LengthOverall,PlacesToRightOfDecimal)

|||Usually this type of formatting is done on the client side. You can use any of the string.format functions.|||You application is having a precision problem, it happens in SQL Server, there are two solutions a third party driver that on the TDS(Tabular Data Stream) level correct it or do it the cheap and free way use Decimal or Numeric instead of money and your problems will go away. Numeric is bigger than money but you will not get that problem. Hope this helps.|||I have tried to use the decimal datatype convert but it is rounding up or down and I am trying to show an amount, i.e. price. I just need something that will allow me to show the value as it exists in the database. When I view the data in the table, I only see the two decimal places.|||

Try the format in these links. Hope this helps

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconformattingnumericdataforspecificculture.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstringsoutputexample.asp

Problem With Case Function

Hi! Guys
i have problem with order by clause.
i have table some T1 with two columns say Itemname and Status
and data like this
ItemName Status
T1 G
T2 D
T3 US
T4 S
T5 G
T6 NULL
T7 NULL
Now i want to show with status like
G
D
S
US
SELECT
CASE Status
WHEN 'G' THEN 0
WHEN 'NULL' THEN 0
WHEN 'D' THEN 1
WHEN 'S' THEN 2
WHEN 'US' THEN 3 END AS STATUSORDER
FROM T1
But it is not ececuting properly.it is not exact answer what i want.
plz , give solutionHi, shiva
You can use one of these queries:
SELECT
CASE
WHEN Status='G' THEN 0
WHEN Status IS NULL THEN 0
WHEN Status='D' THEN 1
WHEN Status='S' THEN 2
WHEN Status='US' THEN 3
END AS STATUSORDER
FROM T1
or:
SELECT
CASE Status
WHEN 'D' THEN 1
WHEN 'S' THEN 2
WHEN 'US' THEN 3
ELSE 0
END AS STATUSORDER
FROM T1
Razvan|||What does it mean "not properly" ?
CREATE TABLE #Test
(
col VARCHAR(2) NULL
)
INSERT INTO #Test VALUES ('G')
INSERT INTO #Test VALUES ('D')
INSERT INTO #Test VALUES ('US')
INSERT INTO #Test VALUES ('S')
INSERT INTO #Test VALUES ('G')
INSERT INTO #Test VALUES (null)
INSERT INTO #Test VALUES (null)
SELECT col FROM #TesT WHERE col IS NOT NULL
GROUP BY col
ORDER BY
CASE col
WHEN 'G' THEN 0
WHEN 'D' THEN 1
WHEN 'S' THEN 2
WHEN 'US' THEN 3 END
"shiva" <bany.shanker@.gmail.com> wrote in message
news:1133867516.797293.72880@.f14g2000cwb.googlegroups.com...
> Hi! Guys
>
> i have problem with order by clause.
>
> i have table some T1 with two columns say Itemname and Status
>
> and data like this
>
> ItemName Status
> T1 G
> T2 D
> T3 US
> T4 S
> T5 G
> T6 NULL
> T7 NULL
> Now i want to show with status like
> G
> D
> S
> US
> SELECT
> CASE Status
> WHEN 'G' THEN 0
> WHEN 'NULL' THEN 0
> WHEN 'D' THEN 1
> WHEN 'S' THEN 2
> WHEN 'US' THEN 3 END AS STATUSORDER
> FROM T1
> But it is not ececuting properly.it is not exact answer what i want.
> plz , give solution
>|||thanks Razvan,
it's working fine.
thanks a lot.

Tuesday, March 20, 2012

Problem with "Not in " operator

I have a table with 2 columns
The cloumn cp_lot_status can be null or 'PL','RE'
The following shows the current data

SQL> SELECT CP_LOT_NUMBER,CP_LOT_STATUS FROM CP_LOTS_GENERATED ;

CP_LOT_NUMBER CP
----- --
203750001
103750001

SQL> SELECT * FROM CP_LOTS_GENERATED WHERE CP_LOT_STATUS NOT IN ('PL','RE') AND
2 CP_LOT_STATUS IS NULL;

no rows selected

SQL> DESC CP_LOTS_GENERATED
Name Null? Type
---------------- --- -------
CP_LOT_NUMBER NOT NULL NUMBER(12)
CP_LOT_STATUS VARCHAR2(2)

So my question is does "Not in " operator not show the null values while doing the above query.IF so what could i do to show the null value rowsNULLs are tricky. They are never equal to, not equal to, IN or NOT IN anything. So this predicate:

WHERE CP_LOT_STATUS NOT IN ('PL','RE')

is neither true nor false when cp_lot_status is NULL - it evaluates to NULL. The query only returns rows where all predicates evaluate to TRUE.

As for what you could do - well, just remove that predicate, it was redundant anyway:

SELECT * FROM CP_LOTS_GENERATED
WHERE CP_LOT_STATUS IS NULL;|||But the values 'PL' and 'RE' may occur at some point of time.....

Originally posted by andrewst
NULLs are tricky. They are never equal to, not equal to, IN or NOT IN anything. So this predicate:

WHERE CP_LOT_STATUS NOT IN ('PL','RE')

is neither true nor false when cp_lot_status is NULL - it evaluates to NULL. The query only returns rows where all predicates evaluate to TRUE.

As for what you could do - well, just remove that predicate, it was redundant anyway:

SELECT * FROM CP_LOTS_GENERATED
WHERE CP_LOT_STATUS IS NULL;|||Well, if they did you would never see them with a query that contained the predicate "AND CP_LOT_STATUS IS NULL".

Maybe what you want is

WHERE (CP_LOT_STATUS NOT IN ('PL','RE')
OR CP_LOT_STATUS IS NULL);|||yeah this worked out

thanx.................

Originally posted by andrewst
Well, if they did you would never see them with a query that contained the predicate "AND CP_LOT_STATUS IS NULL".

Maybe what you want is

WHERE (CP_LOT_STATUS NOT IN ('PL','RE')
OR CP_LOT_STATUS IS NULL);

Problem whith insert into SQL server from webservice

My webservice is suppoed to insert a row into my SQL server database. Al columns are varchar(String). When we insert with digits everything is ok, but when we try letters as parameters i get this error message:

"An unhandled exception of type 'System.Web.Services.Protocols.SoapException' occurred in system.web.services.dll

Additional information: Server was unable to process request. --> The name 'Bob' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted."

Any idea?Show the code that is creating the INSERT statement. Presuming Bob is a string you are trying to insert, are you either using parameters (best solution) or alternately, enclosing the variable in quotes in the resulting string.|||I solved the problems by using parameters instead. Thank you! :)|||Glad to hear it. Parameters eliminate all sorts of problems, in addition to making your application much more secure.

Friday, March 9, 2012

Problem when moving index with included columns to another filegroup

Hi,

This may be a GUI bug. When I manually create and then execute a script to move an index with included columns to another filegroup, it works.

When I try to edit the storage property of the index through the GUI, index properties, then when I ask to generate the script for the change to the clipboard, I get an error

Could not add [FirstIncludedColumn] to the collection because it already exist. Microsoft.sqlserver.Smo

it works if the index does not includes addtl columns.

Is that a bug? It really looks it works when done manually.

Thanks, Philippe

Moving this thread to the Tools forum since this is not a TSQL/DDL issue.|||

This sounds like a bug to me. Please file a defect report here: http://lab.msdn.microsoft.com/productfeedback/Default.aspx

If possible, please include the T-SQL to create the index that causes problems. It also helps if you can include really detailed instructions for what you clicked/typed in the index dialog to make the storage change.

Thanks,
Steve

Problem when exporting data into Excel file from SSIS

I have problems when exporting data into Excel file from SSIS. It all works fine with numeric columns but an apostrophe is attached at the beginning of each text cell. I tried using derived columns and data conversions but it didn't work. It seems to me that problem is in 'excel destination' task... I saw many people had this kind of problems too... Is there any solution possible?

Thanks.

I too am experiencing this problem. Have you discovered a solution?|||Export that data to a CSV file from Excel and you'll find that the tick does not exist. I believe you'll find that the 'tick' is just an Excel display feature. It's not really part of the data.|||

This inconvenient behavior of the Excel driver is mentioned in the BOL topic on the Excel Destination, and appears to be an attempt by the driver to force the output to be treated as string data (since the destination column does not really have a built-in data type), using the convention of the single quote that dates from Lotus 1-2-3.

-Doug

Problem when exporting data into Excel file from SSIS

Thanks.

-Doug

Monday, February 20, 2012

Problem uploading data into SQL Server with SSIS

I designed a package in SSIS that loads data from an Excel source into a oledb SQL database table.

The problem I have is that two of the columns that are supposed to be nvarchar type columns sometimes contain numerical strings. These values causes an error when 'Allow Null' is not set and loads as NULL if 'Allow Null' is set.

How can I force these values into the table?

I have a script transformation object in the package with this code:

If IsNumeric(Row.OCCode) Then

Row.ItemCode = CStr(Row.OCCode)

Else

Row.ItemCode = Row.OCCode

End If

ItemCode is of type unicode string and the column in the SQL DB Table it's mapped to is of type nvarchar

What is the source data type set to for those columns in the source connection?|||

I don't know how to check that, but the input columns into the transformation from the source are unicode strings.

I Also tried Forcing the Excel columns to Text.

|||In the source, the columns are set to Unicode string [DT_WSTR]|||Yeah, I don't understand what NULLs have to do with numeric data appearing in a string field. Can you shed more light on this?|||

It seems impossible to upload Text and numeric values into one Text column in the SQL database. The excel source picks up the source data as unicode string if its alphanumeric values and Numeric type when it's numeric value. if I make all the source columns in the source Unicode String it stops the running package as soon as it comes accross a numeric value with this Error Message:

There was an error with input column "ItemDescription" (889) on input "OLE DB Destination Input" (248). The column status returned was: "The value violated the integrity constraints for the column

If I make the column accept NULL values in the database, the package completes the data load, but make all the numeric values NULL.

I can enter data manually into this database to take numeric values in the nvarchar field, but I don't know how to set the SSIS package to upload the numeric values as Text.

|||Take a look here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1277733&SiteID=1|||

I managed to upload the data, thanks. The link you added mentioned that numerical strings in the excel file need to be re-entered after the column format is changed to Text. Seeing that I had 54000 rows, I had to perform a text-function operation on all the relative cells. Something like =trim(A2) and then copy these cells back to the original columns as values.

Thanks

|||The important thing in that link was the setting of the IMEX parameter.|||

Yeah I saw the code, but don't know where to add it or how to change the IMEX property. I had to get the data in.

It will help a lot if you can explain where to change this property. I can change the registry entries.

Problem updating table1 with data from table 2

Hi,

I'm trying to loop through 2 tables and match the records from table1 to table2 and update a couple of columns in table2 if there is a match. The below code updates everyone in table2 with the same data from only 1 record. How can I keep looping through both tables?

USE GradData

Declare @.ssn nvarchar(10)

Declare @.testdesc nvarchar(10)

Declare @.testcode nvarchar(10)

Declare @.testdate datetime

Declare @.testscore nvarchar(50)

Declare @.testsource nvarchar(10)

Declare @.testsortkey nvarchar(10)

Declare @.fcode nvarchar(10)

Declare @.ssn2 nvarchar(10)

Declare @.testdesc2 nvarchar(10)

Declare @.testcode2 nvarchar(10)

Declare @.testdate2 datetime

Declare @.testscore2 nvarchar(50)

Declare @.testsource2 nvarchar(10)

Declare @.testsortkey2 nvarchar(10)

Declare @.fcode2 nvarchar(10)

Declare mycursor Cursor For

Select ssn,testDesc1,testCode1,testDate1,testScore1,testSource1,Fcode1,sortkey

From TestScoresRpt

Open mycursor

FETCH NEXT FROM mycursor

INTO @.ssn,@.testDesc,@.testCode,@.testDate,@.testScore,@.testSource,@.Fcode2,@.testsortkey

Declare mycursor2 Cursor For

Select ssn,test_Desc,test_Code,test_Date,test_Score,test_Source_code,Fcode,test_sort_key

From ScoresAll

Open mycursor2

FETCH NEXT FROM mycursor2

INTO @.ssn2,@.testDesc2,@.testCode2,@.testDate2,@.testScore2,@.testSource2,@.Fcode2,@.testsortkey2

WHILE @.@.FETCH_STATUS = 0

BEGIN

IF @.ssn <> @.ssn2

BEGIN

FETCH NEXT FROM mycursor2

INTO @.ssn2,@.testDesc2,@.testCode2,@.testDate2,@.testScore2,@.testSource2,@.Fcode2,@.testsortkey2

END

IF @.ssn = @.ssn2 and @.testsortkey = @.testsortkey2

BEGIN

FETCH NEXT FROM mycursor2

INTO @.ssn2,@.testDesc2,@.testCode2,@.testDate2,@.testScore2,@.testSource2,@.Fcode2,@.testsortkey2

END

IF @.ssn = @.ssn2 and @.testsortkey <> @.testsortkey2

Update TestScoresRpt

set ssn2=@.ssn2,testDesc2=@.testDesc2,testCode2=@.testCode2,testDate2=@.testDate2,testScore2=@.testScore2,testSource2=@.testSource2,Fcode2=@.fcode2,sortkey2=@.testsortkey2

FETCH NEXT FROM mycursor

INTO @.ssn,@.testDesc,@.testCode,@.testDate,@.testScore,@.testSource,@.Fcode2,@.testsortkey

END

CLOSE mycursor

CLOSE mycursor2

DEALLOCATE mycursor

DEALLOCATE mycursor2

If you are updating all rows where (@.ssn = @.ssn2 and @.testsortkey <> @.testsortkey2), then you would have improved performance using a Set based operation.

Please verify that the end result is two (2) virtually duplicate rows.

|||

THE END RESULT WILL BE A SSN WITH 2 DIFFERENT SORT KEYS FOR EACH SSN IN THE TABLE. WHAT DO YOU MEAN BY SET BASED OPERATION?

THANKS,

|||

My mistake for not completely reading your code.

A Set based operation would be making these updates in one query.

Try something like:

UPDATE t
SET
t.ssn2 = s.ssn,
t.testDesc2=s.testDesc2,
t.testCode2=s.testCode2,
t.testDate2=s.testDate2,
t.testScore2=s.testScore2,
t.testSource2=s.testSource2,
t.Fcode2=s.fcode2,
t.sortkey2=s.testsortkey2
FROM TestScoresRpt t
JOIN ScoresAll s
ON t.ssn = s.ssn
WHERE t.testsortkey <> s.testsortkey2

(This is untested, so please test, test.)

Friday, March 30, 2012

Problem with clustered index

Problem with clustered index

problem with CAST and CONVERT in SQL Server2000 converting decimal places from 4 to 2

Problem With Case Function

Tuesday, March 20, 2012

Problem with "Not in " operator

Problem whith insert into SQL server from webservice

Friday, March 9, 2012

Problem when moving index with included columns to another filegroup

Problem when exporting data into Excel file from SSIS

Problem when exporting data into Excel file from SSIS

Monday, February 20, 2012

Problem uploading data into SQL Server with SSIS

Problem updating table1 with data from table 2

updating text datatype

Blog Archive

About Me