updating text datatype: primary

Showing posts with label primary. 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

Wednesday, March 28, 2012

Problem with Auto-increment of an ID

Hello,
I have a little problem with SQL Server 2005. I have chosen the option "auto-increment" for a column which includes the primary key. Moreover, I turned the option "Ignore double values" on. I get the datasets from a SSIS-Import-project. Unfortunately, the ID is getting incremented even if there are double values.
For example:
The first entries in the table have the IDs: 1, 2, 3, 4, 5
Then I start the SSIS-project which tries to write the first 5 entries again in the database. Of course, the entries do not appear again in the db but a new entry gets the ID 11 instead of 6. Is there a setting, that the ID won't get incremented if there double values?
Thanks
M-l-G

i dont't think its possible and when the column is primary key, it does not make sense also.

Madhu

Wednesday, March 21, 2012

Problem with a publication. Please help !

Dear friend:
I got this error message inmediatly after to create a suscription for a
publication.
Violation of PRIMARY KEY constraint 'PK__@.snapshot_seqnos__6B318B07'. Cannot
insert duplicate key in object '#6A3D66CE'.
Last Command = call sp_MSget_repl_commands(19, ?, 0, 7500000)
What should I do ? I drop the publication, but when I configure it again, I
got the same result.
Thanks in advance.
Hi Salvador,
This is a known issue which has been fixed in SQL2000 sp4, and here is the
download link from the microsoft web site:
http://www.microsoft.com/sql/downloads/2000/sp4.mspx
HTH
-Raymond
"Salvador De los Reyes" wrote:

> Dear friend:
> I got this error message inmediatly after to create a suscription for a
> publication.
> Violation of PRIMARY KEY constraint 'PK__@.snapshot_seqnos__6B318B07'. Cannot
> insert duplicate key in object '#6A3D66CE'.
> Last Command = call sp_MSget_repl_commands(19, ?, 0, 7500000)
> What should I do ? I drop the publication, but when I configure it again, I
> got the same result.
> Thanks in advance.
>
>
|||Perfect ! Thks
"Raymond Mak [MSFT]" <RaymondMakMSFT@.discussions.microsoft.com> escribi en
el mensaje news:B17ACDF1-BBD0-45BD-ADDA-5D2C79A8C432@.microsoft.com...[vbcol=seagreen]
> Hi Salvador,
> This is a known issue which has been fixed in SQL2000 sp4, and here is the
> download link from the microsoft web site:
> http://www.microsoft.com/sql/downloads/2000/sp4.mspx
> HTH
> -Raymond
> "Salvador De los Reyes" wrote:
|||Hi,
I have seen your email in www.codecomments.com, you said "This
problem is a bug", with sp4 sql server it is solucionated.
Last command{call sp_MSget_repl_commands(46, ?, 0, 7500000)}
Error Message Violation of PRIMARY KEY constraint
'PK__@.snapshot_seqnos__053E7DEC'. Cannot insert duplicate key in object
'#1685152A'.
Error details Violation of PRIMARY KEY constraint
'PK__@.snapshot_seqnos__053E7DEC'. Cannot insert duplicate key in object
'#1685152A'.
(Source: SQLA01 (Data source); Error number: 2627)
but, How the replication worked before sp4?
In my case, only appears the error when I select related tables, why?
Thanks,
Jose Luis
*** Sent via Developersdex http://www.codecomments.com ***

Problem with 2 tables

So i have these 2 tables:

Cars - has a primary key car

Car Accessories - has a foreign key car

When i insert a new car i has no accessories, so i dont have to insert a record into the car accessories table, but when
i want to add a car accessories record for a specific car i bump into a problem. If i want to add it i dont know which statement to use. If i use insert and the accessories record is already present, then ill get an error or if i use update and the accessories record is not present then it will not work again.

any ideas? maybe it is possible to use some kind of relationship which automatically creates the car accessories record when you insert a new car?

In SQL Server I use an Stored Procedure called xxx_AddUpdate, where xxx is some table or other friendly descriptive name

using the keys passed as parameters, I perform process as follows

if(exists(select <your Id> from [Car Accessories] where <your Id> = @.<your id> ))

-- perform update SQL

else

-- perform insert SQL

|||

Hey thanks for the reply, what you said will do the job just fine, i didnt know you can check if a record exists like that, thanks

I have some other question:

I have most of my sql commands stored in a xsd dataset, would there be any advantage of using stored procedures instead?

|||

For security and other reasons it is traditional to use stored procedures, unless you have other overriding factors in your design.

SP's provides a data access "protocol" that can be better managed from a security perspective, both in terms of what can be limited or exposed to a user for a process, but also in terms of limiting access by database and application roles

|||

Aah so u are saying if security is not a priority its not so important to use stored procedures.

In this case i am gonna stick the strongly typed (xds) dataset and the commands inside it, even though it has many flaws in my opinion, like for example having to make an instance of a dataset table adapter to use its commands, i find it just unnecessary.

|||

It is still worth implementing the suggested workflow in some of your projects as this gets you used to industry standard practice

Friday, March 9, 2012

problem when inserting

I want to insert a recorde into my table (2 column) : name and Id

id is a primary key and this is the error MSG im getting everytime im trying to insert a record

Error:Cannot insert explicit value for identity column in table 'Customer' when IDENTITY_INSERT is set to OFF.

what is the problem

here is the code :

protectedvoid Button2_Click(object sender,EventArgs e)

{

try

{

string str ="data source=.;integrated security=true; initial catalog=NH_Project2";

SqlConnection conn =newSqlConnection(str);

SqlCommand comm =newSqlCommand("insert into [Customer](Name,Id) values(@.custName,@.custId)", conn);

comm.CommandType =CommandType.Text;

comm.Parameters.AddWithValue("@.custName", TextBox1.Text);comm.Parameters.AddWithValue("@.custId", TextBox2.Text);

conn.Open();

int rowaff = (int)comm.ExecuteNonQuery();

if (rowaff == 1)

{

Label4.Visible =true;Label4.Text ="one customer has added to our data base";

}

else

{

Label4.Visible =false;Label4.Text ="not added try again";

}

catch (Exception ex)

{

Response.Write("Error:" + ex.Message);

}

You need to understand IDENTITY columns. Please read up books online. If you want to explicitly insert a value into it you need to use SET IDENTITY_INSERT <Table> ON before the INSERT and set it to OFF after the insert. IF you want to let SQL Server handle the Id's you should remove the column from your INSERT list so SQL Server can do it for you.

|||

"IF you want to let SQL Server handle the Id's you should remove the column from your INSERT list so SQL Server can do it for you."

how can I do that ? do you mean to call stored procedure and have it insert into that column?

thanks for your advice, any specific books that you would recommend ?

Tongue Tied

|||modify your command definition line to:SqlCommand comm = new SqlCommand("insert into [Customer](Name) values(@.custName)", conn); and pass only customer name parametercomm.Parameters.AddWithValue("@.custName", TextBox1.Text)IF you do this ID value will be assigned by database itselfIf you would like to use you logic you have to allow to insert identities into table by running:SqlCommand comm = new SqlCommand("SET IDENTITY_INSERT dbo.[customer] ON insert into [Customer](Name,Id) values(@.custName,@.custId) SET IDENTITY_INSERT dbo.[customer] OFF", conn); but you have to be sure that Identity you try to insert does not exists in table before you do insert.|||

No just remove it from your INSERT list..Just insert the name, the ID will be inserted by SQL Server.

insert into [Customer](Name) values(@.custName)"

|||

Yes

that was helpful

|||

The problem is that your IDENTITY COLUMN is inserted automatically! If so the only thing you need is:
insert into [Customer](Name) values(@.custName)

When inserted, the new Customer is given an ID automatically!

SuperJB

Saturday, February 25, 2012

Problem using linked tables instead of ADP

Hi
I know I probably shouldn't be doing this but...
I have a table on an MSDE database that has a bigInt for the primary key. If
I try to link to this table from Access (XP) using an ODBC connection the
table links ok. The first time I opened the table it was ok. Since then the
primary key data type is interpretted as text and all the records (though
still there) have "#deleted" in each column of every row? I have tried
relinking the table and still get he same problem.
Any ideas whats going on?
Thanks in advance
Mark
Have you linked the table such that Access recognizes the primary key?
"Mark" wrote:

> Hi
> I know I probably shouldn't be doing this but...
> I have a table on an MSDE database that has a bigInt for the primary key. If
> I try to link to this table from Access (XP) using an ODBC connection the
> table links ok. The first time I opened the table it was ok. Since then the
> primary key data type is interpretted as text and all the records (though
> still there) have "#deleted" in each column of every row? I have tried
> relinking the table and still get he same problem.
> Any ideas whats going on?
> Thanks in advance
> Mark
>
>
|||Hi Monte

> Have you linked the table such that Access recognizes the primary key?
>
As far as I know, yes? I wasn't prompted to select a primary key and looking
at the table design from within access shows the primary key present but as
text instead of number.
Cheers
Mark
|||I have also seen this problem occur when I have "bit" type fields in the SQL
table, and don't have a default value (say zero) and allow nulls checked.
"Mark" wrote:

> Hi Monte
>
> As far as I know, yes? I wasn't prompted to select a primary key and looking
> at the table design from within access shows the primary key present but as
> text instead of number.
> Cheers
> Mark
>
>
|||"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:46D395BA-E150-4C96-9EDE-AF4776FBC18A@.microsoft.com...
>I have also seen this problem occur when I have "bit" type fields in the
>SQL
> table, and don't have a default value (say zero) and allow nulls checked.
Monte
Scary stuff hey :o{
I'm not going to worry about it too much as I was just playing to see the
difference in performance etc. Would still be interested to know why, may
have another look later...
Cheers
Mark

Friday, March 30, 2012

Problem with clustered index

Problem with clustered index

Wednesday, March 28, 2012

Problem with Auto-increment of an ID

Wednesday, March 21, 2012

Problem with a publication. Please help !

Problem with 2 tables

Friday, March 9, 2012

problem when inserting

Saturday, February 25, 2012

Problem using linked tables instead of ADP

updating text datatype

Blog Archive

About Me