Hi,
I'm using multiple select statements in MS-Access with logical
expressions like (ID=10)*50 instead of an if statement. In this manner
i'm able to get the same results as with if statements (if ID=10 then
result=50) only faster (in MS-Access that is).
when trying to get the same sql statement running in SQL-server I
receive an Incorrect syntax near '=' error message.
Are there any solutions? I will try to avoid updating all queries
because MS-Access will also be used as backend next to a version for
SQL-server backend.
Maybe anyone can help me.You can use CASE statement
SELECT result =
CASE WHEN ID = 10 THEN 50
ELSE something END
"Niels Verkaart" wrote:
> Hi,
> I'm using multiple select statements in MS-Access with logical
> expressions like (ID=10)*50 instead of an if statement. In this manner
> i'm able to get the same results as with if statements (if ID=10 then
> result=50) only faster (in MS-Access that is).
> when trying to get the same sql statement running in SQL-server I
> receive an Incorrect syntax near '=' error message.
> Are there any solutions? I will try to avoid updating all queries
> because MS-Access will also be used as backend next to a version for
> SQL-server backend.
> Maybe anyone can help me.
>|||Thank you Jack,
now I only have to write a clever function to convert dynamic queries
like:
INSERT INTO _CalcAfzet_2394 ( ID, K001, K002, K003, K004 ) SELECT
VerkoopsoortID,Sum(-(Year([Datum])=2006)*[Aantal]),Sum(-(Year([Datum])=2
007)*[Aantal]),Sum(-(Year([Datum])=2008)*[Aantal]),Sum(-(Year([Datum])=2
009)*[Aantal]) FROM CalcAfzetPeriode WHERE CalcID=1528 AND
VerkoopsoortID NOT IN (0) GROUP BY VerkoopsoortID
That's a nice little problem for me. but i'll get there.
> You can use CASE statement
> SELECT result =
> CASE WHEN ID = 10 THEN 50
> ELSE something END
> "Niels Verkaart" wrote:
>|||Hi Niels,
This is a typical crosstab (FAQ). The solution is basically the same as
what Jack has posted:
INSERT INTO _CalcAfzet_2394 ( ID, K001, K002, K003, K004 )
SELECT VerkoopsoortID
, SUM(CASE Year(Datum) WHEN 2006 THEN Aantal END)
, SUM(CASE Year(Datum) WHEN 2007 THEN Aantal END)
, SUM(CASE Year(Datum) WHEN 2008 THEN Aantal END)
, SUM(CASE Year(Datum) WHEN 2009 THEN Aantal END)
FROM CalcAfzetPeriode
WHERE CalcID=1528
AND VerkoopsoortID NOT IN (0)
GROUP BY VerkoopsoortID
HTH,
Gert-Jan
Niels Verkaart wrote:
> Thank you Jack,
> now I only have to write a clever function to convert dynamic queries
> like:
> INSERT INTO _CalcAfzet_2394 ( ID, K001, K002, K003, K004 ) SELECT
> VerkoopsoortID,Sum(-(Year([Datum])=2006)*[Aantal]),Sum(-(Year([Datum])=2
> 007)*[Aantal]),Sum(-(Year([Datum])=2008)*[Aantal]),Sum(-(Year([Datum])=2
> 009)*[Aantal]) FROM CalcAfzetPeriode WHERE CalcID=1528 AND
> VerkoopsoortID NOT IN (0) GROUP BY VerkoopsoortID
> That's a nice little problem for me. but i'll get there.
>|||Great Gert-Jan,
thank you both Gert-Jan and Jack!
Gert-Jan Strik wrote:
> Hi Niels,
> This is a typical crosstab (FAQ). The solution is basically the same
> as what Jack has posted:
> INSERT INTO _CalcAfzet_2394 ( ID, K001, K002, K003, K004 )
> SELECT VerkoopsoortID
> , SUM(CASE Year(Datum) WHEN 2006 THEN Aantal END)
> , SUM(CASE Year(Datum) WHEN 2007 THEN Aantal END)
> , SUM(CASE Year(Datum) WHEN 2008 THEN Aantal END)
> , SUM(CASE Year(Datum) WHEN 2009 THEN Aantal END)
> FROM CalcAfzetPeriode
> WHERE CalcID=1528
> AND VerkoopsoortID NOT IN (0)
> GROUP BY VerkoopsoortID
> HTH,
> Gert-Jan
>
> Niels Verkaart wrote:
Showing posts with label statements. Show all posts
Showing posts with label statements. Show all posts
Friday, March 23, 2012
Incorrect Syntax
Hello all,
Newbie here.
SQL 2000, Windows 2000
I'm trying to alter tables in my SQL DB using statements like the following:
/* AD_GROUPS */
alter table AD_GROUPS alter column AD_GROUP_NAME nvarchar(64)not null
go
/* ARTICLES */
alter table ARTICLES add column CONTENTTYPE_REF int null
go
I get error messages like:
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'column'
I got the statements straight out of the Microsoft book "Inside Microsoft
SQL Server 2000"
Thanks in advance for helping to train this raw recruit!
JakeJust grop the "COLUMN" keyword from the ADD statement:
ALTER TABLE Articles ADD contenttype_ref INT NULL
Your ALTER COLUMN statement is correct. It's just a peculiarity of the
syntax that the word "COLUMN" isn't required after ADD.
--
David Portas
SQL Server MVP
--|||> Just grop the "COLUMN" keyword from the ADD statement:
> ALTER TABLE Articles ADD contenttype_ref INT NULL
> Your ALTER COLUMN statement is correct. It's just a peculiarity of the
> syntax that the word "COLUMN" isn't required after ADD.
And pardon the pun, but this missing part of the syntax won't be added
anytime soon, either. ;-)
--
http://www.aspfaq.com/
(Reverse address to reply.)|||> /* ARTICLES */
> alter table ARTICLES add column CONTENTTYPE_REF int null
> go
> I got the statements straight out of the Microsoft book "Inside Microsoft
> SQL Server 2000"
What page? I'd be interested to see a line like that, with the incorrect
column keyword where it is in your statement.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||I'd be interested to see that also. :-)
Not that there are absolutely no mistakes in the book, but I just did a
search of the electronic version of the book, and did not find this error.
In fact, I found this note, basically warning about the word 'column' not
being used when adding a new column:
NOTE
-----------------------
--
Notice the syntax difference between dropping a column and adding a new
column: the word COLUMN is required when dropping a column, but not when
adding a new column to a table.
My guess is that Jake pulled the ALTER TABLE ALTER COLUMN syntax out of the
book, and then changed ALTER COLUMN to ADD COLUMN.
--
HTH
------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OQBNC0cbEHA.3792@.TK2MSFTNGP09.phx.gbl...
> > /* ARTICLES */
> > alter table ARTICLES add column CONTENTTYPE_REF int null
> > go
> > I got the statements straight out of the Microsoft book "Inside
Microsoft
> > SQL Server 2000"
> What page? I'd be interested to see a line like that, with the incorrect
> column keyword where it is in your statement.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)|||> My guess is that Jake pulled the ALTER TABLE ALTER COLUMN syntax out of
the
> book, and then changed ALTER COLUMN to ADD COLUMN.
That was my guess too, but wanted to prod a bit more; maybe he found
something the rest of us missed. ;-)
A
Monday, March 12, 2012
Inconsistent Performance of Insert
I am experiencing inconsistent performance when running
insert statements.
Our application automatically generates and sequentially
executes SQL statements that build a results table. After
loading data and running this process, all statements
(about 125 in total) will execute in about 10 minutes. If
it is necessary to rerun the statements I truncate the
results table and rerun the job. In some cases this
second job runs in 10 minutes, and in other cases it runs
6 hours. In particular, one statement takes an inordinate
amount of time.
I've used PROFILER (although I'm not too experienced in
this) and can see that when statements are running
normally the log file updates in the 1000+ per second
range, but with the problematic statement updates are in
the 10 per second range. These updates seem to appear
after the commit statement is executed.
In various combinations I've tried restarting SQLServer,
updating statistics, defragging the hard drive, using
query optimizer, all with no luck.
Any ideas?
Here's one of the statements that seems to get hung up:
Table AV has 4 columns including a Record ID (RID), a
record name (FID) and a record value (Value). This table
will ultimately have approximately 4 million rows at the
completion of the job.
Table A contains a Record ID (RID) and a Process ID
(PID). This table has approx. 100,000 records.
INSERT INTO AV(RID, FID, Value) SELECT A.RID, 325, ISNULL
(ZZTEMPA.Value, 0)+ISNULL(ZZTEMPB.Value, 0)+ISNULL
(ZZTEMPC.Value, 0)+ISNULL(ZZTEMPD.Value, 0)+ISNULL
(ZZTEMPE.Value, 0) FROM A LEFT JOIN AV AS ZZTEMPA ON A.RID
= ZZTEMPA.RID AND ZZTEMPA.FID = 496 LEFT JOIN AV AS
ZZTEMPB ON A.RID = ZZTEMPB.RID AND ZZTEMPB.FID = 497 LEFT
JOIN AV AS ZZTEMPC ON A.RID = ZZTEMPC.RID AND ZZTEMPC.FID
= 499 LEFT JOIN AV AS ZZTEMPD ON A.RID = ZZTEMPD.RID AND
ZZTEMPD.FID = 502 LEFT JOIN AV AS ZZTEMPE ON A.RID = ZZTEMPE.RID AND ZZTEMPE.FID = 504 WHERE (A.PID IN (275,
276, 277, 278, 279, 280, 281))
I recognize that the same table AV is being joined
multiple times, but it's my understanding that this should
not be a problem.
Thanks for any guidance on this.Not much to go on but have you looked to see if you are being blocked when
this is happening? How about disk and cpu queues?
--
Andrew J. Kelly SQL MVP
"Chuck Hardy" <chardy@.coreprofit.com> wrote in message
news:16c501c4a62e$f11bbd90$a601280a@.phx.gbl...
> I am experiencing inconsistent performance when running
> insert statements.
> Our application automatically generates and sequentially
> executes SQL statements that build a results table. After
> loading data and running this process, all statements
> (about 125 in total) will execute in about 10 minutes. If
> it is necessary to rerun the statements I truncate the
> results table and rerun the job. In some cases this
> second job runs in 10 minutes, and in other cases it runs
> 6 hours. In particular, one statement takes an inordinate
> amount of time.
> I've used PROFILER (although I'm not too experienced in
> this) and can see that when statements are running
> normally the log file updates in the 1000+ per second
> range, but with the problematic statement updates are in
> the 10 per second range. These updates seem to appear
> after the commit statement is executed.
> In various combinations I've tried restarting SQLServer,
> updating statistics, defragging the hard drive, using
> query optimizer, all with no luck.
> Any ideas?
> Here's one of the statements that seems to get hung up:
> Table AV has 4 columns including a Record ID (RID), a
> record name (FID) and a record value (Value). This table
> will ultimately have approximately 4 million rows at the
> completion of the job.
> Table A contains a Record ID (RID) and a Process ID
> (PID). This table has approx. 100,000 records.
> INSERT INTO AV(RID, FID, Value) SELECT A.RID, 325, ISNULL
> (ZZTEMPA.Value, 0)+ISNULL(ZZTEMPB.Value, 0)+ISNULL
> (ZZTEMPC.Value, 0)+ISNULL(ZZTEMPD.Value, 0)+ISNULL
> (ZZTEMPE.Value, 0) FROM A LEFT JOIN AV AS ZZTEMPA ON A.RID
> = ZZTEMPA.RID AND ZZTEMPA.FID = 496 LEFT JOIN AV AS
> ZZTEMPB ON A.RID = ZZTEMPB.RID AND ZZTEMPB.FID = 497 LEFT
> JOIN AV AS ZZTEMPC ON A.RID = ZZTEMPC.RID AND ZZTEMPC.FID
> = 499 LEFT JOIN AV AS ZZTEMPD ON A.RID = ZZTEMPD.RID AND
> ZZTEMPD.FID = 502 LEFT JOIN AV AS ZZTEMPE ON A.RID => ZZTEMPE.RID AND ZZTEMPE.FID = 504 WHERE (A.PID IN (275,
> 276, 277, 278, 279, 280, 281))
> I recognize that the same table AV is being joined
> multiple times, but it's my understanding that this should
> not be a problem.
> Thanks for any guidance on this.
>
insert statements.
Our application automatically generates and sequentially
executes SQL statements that build a results table. After
loading data and running this process, all statements
(about 125 in total) will execute in about 10 minutes. If
it is necessary to rerun the statements I truncate the
results table and rerun the job. In some cases this
second job runs in 10 minutes, and in other cases it runs
6 hours. In particular, one statement takes an inordinate
amount of time.
I've used PROFILER (although I'm not too experienced in
this) and can see that when statements are running
normally the log file updates in the 1000+ per second
range, but with the problematic statement updates are in
the 10 per second range. These updates seem to appear
after the commit statement is executed.
In various combinations I've tried restarting SQLServer,
updating statistics, defragging the hard drive, using
query optimizer, all with no luck.
Any ideas?
Here's one of the statements that seems to get hung up:
Table AV has 4 columns including a Record ID (RID), a
record name (FID) and a record value (Value). This table
will ultimately have approximately 4 million rows at the
completion of the job.
Table A contains a Record ID (RID) and a Process ID
(PID). This table has approx. 100,000 records.
INSERT INTO AV(RID, FID, Value) SELECT A.RID, 325, ISNULL
(ZZTEMPA.Value, 0)+ISNULL(ZZTEMPB.Value, 0)+ISNULL
(ZZTEMPC.Value, 0)+ISNULL(ZZTEMPD.Value, 0)+ISNULL
(ZZTEMPE.Value, 0) FROM A LEFT JOIN AV AS ZZTEMPA ON A.RID
= ZZTEMPA.RID AND ZZTEMPA.FID = 496 LEFT JOIN AV AS
ZZTEMPB ON A.RID = ZZTEMPB.RID AND ZZTEMPB.FID = 497 LEFT
JOIN AV AS ZZTEMPC ON A.RID = ZZTEMPC.RID AND ZZTEMPC.FID
= 499 LEFT JOIN AV AS ZZTEMPD ON A.RID = ZZTEMPD.RID AND
ZZTEMPD.FID = 502 LEFT JOIN AV AS ZZTEMPE ON A.RID = ZZTEMPE.RID AND ZZTEMPE.FID = 504 WHERE (A.PID IN (275,
276, 277, 278, 279, 280, 281))
I recognize that the same table AV is being joined
multiple times, but it's my understanding that this should
not be a problem.
Thanks for any guidance on this.Not much to go on but have you looked to see if you are being blocked when
this is happening? How about disk and cpu queues?
--
Andrew J. Kelly SQL MVP
"Chuck Hardy" <chardy@.coreprofit.com> wrote in message
news:16c501c4a62e$f11bbd90$a601280a@.phx.gbl...
> I am experiencing inconsistent performance when running
> insert statements.
> Our application automatically generates and sequentially
> executes SQL statements that build a results table. After
> loading data and running this process, all statements
> (about 125 in total) will execute in about 10 minutes. If
> it is necessary to rerun the statements I truncate the
> results table and rerun the job. In some cases this
> second job runs in 10 minutes, and in other cases it runs
> 6 hours. In particular, one statement takes an inordinate
> amount of time.
> I've used PROFILER (although I'm not too experienced in
> this) and can see that when statements are running
> normally the log file updates in the 1000+ per second
> range, but with the problematic statement updates are in
> the 10 per second range. These updates seem to appear
> after the commit statement is executed.
> In various combinations I've tried restarting SQLServer,
> updating statistics, defragging the hard drive, using
> query optimizer, all with no luck.
> Any ideas?
> Here's one of the statements that seems to get hung up:
> Table AV has 4 columns including a Record ID (RID), a
> record name (FID) and a record value (Value). This table
> will ultimately have approximately 4 million rows at the
> completion of the job.
> Table A contains a Record ID (RID) and a Process ID
> (PID). This table has approx. 100,000 records.
> INSERT INTO AV(RID, FID, Value) SELECT A.RID, 325, ISNULL
> (ZZTEMPA.Value, 0)+ISNULL(ZZTEMPB.Value, 0)+ISNULL
> (ZZTEMPC.Value, 0)+ISNULL(ZZTEMPD.Value, 0)+ISNULL
> (ZZTEMPE.Value, 0) FROM A LEFT JOIN AV AS ZZTEMPA ON A.RID
> = ZZTEMPA.RID AND ZZTEMPA.FID = 496 LEFT JOIN AV AS
> ZZTEMPB ON A.RID = ZZTEMPB.RID AND ZZTEMPB.FID = 497 LEFT
> JOIN AV AS ZZTEMPC ON A.RID = ZZTEMPC.RID AND ZZTEMPC.FID
> = 499 LEFT JOIN AV AS ZZTEMPD ON A.RID = ZZTEMPD.RID AND
> ZZTEMPD.FID = 502 LEFT JOIN AV AS ZZTEMPE ON A.RID => ZZTEMPE.RID AND ZZTEMPE.FID = 504 WHERE (A.PID IN (275,
> 276, 277, 278, 279, 280, 281))
> I recognize that the same table AV is being joined
> multiple times, but it's my understanding that this should
> not be a problem.
> Thanks for any guidance on this.
>
Inconsistent Performance of Insert
I am experiencing inconsistent performance when running
insert statements.
Our application automatically generates and sequentially
executes SQL statements that build a results table. After
loading data and running this process, all statements
(about 125 in total) will execute in about 10 minutes. If
it is necessary to rerun the statements I truncate the
results table and rerun the job. In some cases this
second job runs in 10 minutes, and in other cases it runs
6 hours. In particular, one statement takes an inordinate
amount of time.
I've used PROFILER (although I'm not too experienced in
this) and can see that when statements are running
normally the log file updates in the 1000+ per second
range, but with the problematic statement updates are in
the 10 per second range. These updates seem to appear
after the commit statement is executed.
In various combinations I've tried restarting SQLServer,
updating statistics, defragging the hard drive, using
query optimizer, all with no luck.
Any ideas?
Here's one of the statements that seems to get hung up:
Table AV has 4 columns including a Record ID (RID), a
record name (FID) and a record value (Value). This table
will ultimately have approximately 4 million rows at the
completion of the job.
Table A contains a Record ID (RID) and a Process ID
(PID). This table has approx. 100,000 records.
INSERT INTO AV(RID, FID, Value) SELECT A.RID, 325, ISNULL
(ZZTEMPA.Value, 0)+ISNULL(ZZTEMPB.Value, 0)+ISNULL
(ZZTEMPC.Value, 0)+ISNULL(ZZTEMPD.Value, 0)+ISNULL
(ZZTEMPE.Value, 0) FROM A LEFT JOIN AV AS ZZTEMPA ON A.RID
= ZZTEMPA.RID AND ZZTEMPA.FID = 496 LEFT JOIN AV AS
ZZTEMPB ON A.RID = ZZTEMPB.RID AND ZZTEMPB.FID = 497 LEFT
JOIN AV AS ZZTEMPC ON A.RID = ZZTEMPC.RID AND ZZTEMPC.FID
= 499 LEFT JOIN AV AS ZZTEMPD ON A.RID = ZZTEMPD.RID AND
ZZTEMPD.FID = 502 LEFT JOIN AV AS ZZTEMPE ON A.RID =
ZZTEMPE.RID AND ZZTEMPE.FID = 504 WHERE (A.PID IN (275,
276, 277, 278, 279, 280, 281))
I recognize that the same table AV is being joined
multiple times, but it's my understanding that this should
not be a problem.
Thanks for any guidance on this.
Not much to go on but have you looked to see if you are being blocked when
this is happening? How about disk and cpu queues?
Andrew J. Kelly SQL MVP
"Chuck Hardy" <chardy@.coreprofit.com> wrote in message
news:16c501c4a62e$f11bbd90$a601280a@.phx.gbl...
> I am experiencing inconsistent performance when running
> insert statements.
> Our application automatically generates and sequentially
> executes SQL statements that build a results table. After
> loading data and running this process, all statements
> (about 125 in total) will execute in about 10 minutes. If
> it is necessary to rerun the statements I truncate the
> results table and rerun the job. In some cases this
> second job runs in 10 minutes, and in other cases it runs
> 6 hours. In particular, one statement takes an inordinate
> amount of time.
> I've used PROFILER (although I'm not too experienced in
> this) and can see that when statements are running
> normally the log file updates in the 1000+ per second
> range, but with the problematic statement updates are in
> the 10 per second range. These updates seem to appear
> after the commit statement is executed.
> In various combinations I've tried restarting SQLServer,
> updating statistics, defragging the hard drive, using
> query optimizer, all with no luck.
> Any ideas?
> Here's one of the statements that seems to get hung up:
> Table AV has 4 columns including a Record ID (RID), a
> record name (FID) and a record value (Value). This table
> will ultimately have approximately 4 million rows at the
> completion of the job.
> Table A contains a Record ID (RID) and a Process ID
> (PID). This table has approx. 100,000 records.
> INSERT INTO AV(RID, FID, Value) SELECT A.RID, 325, ISNULL
> (ZZTEMPA.Value, 0)+ISNULL(ZZTEMPB.Value, 0)+ISNULL
> (ZZTEMPC.Value, 0)+ISNULL(ZZTEMPD.Value, 0)+ISNULL
> (ZZTEMPE.Value, 0) FROM A LEFT JOIN AV AS ZZTEMPA ON A.RID
> = ZZTEMPA.RID AND ZZTEMPA.FID = 496 LEFT JOIN AV AS
> ZZTEMPB ON A.RID = ZZTEMPB.RID AND ZZTEMPB.FID = 497 LEFT
> JOIN AV AS ZZTEMPC ON A.RID = ZZTEMPC.RID AND ZZTEMPC.FID
> = 499 LEFT JOIN AV AS ZZTEMPD ON A.RID = ZZTEMPD.RID AND
> ZZTEMPD.FID = 502 LEFT JOIN AV AS ZZTEMPE ON A.RID =
> ZZTEMPE.RID AND ZZTEMPE.FID = 504 WHERE (A.PID IN (275,
> 276, 277, 278, 279, 280, 281))
> I recognize that the same table AV is being joined
> multiple times, but it's my understanding that this should
> not be a problem.
> Thanks for any guidance on this.
>
|||Thanks for replying Andrew.
I did forget to mention: when this occurs CPU usage is 100% with minor
disk access every few seconds. There is never any blocking. These jobs
are being run on a dedicated server with a single user.
The odd thing is that the same data and system conditions produce wildly
different results. In between runs with the same data I truncate the
results table. Is there a possibility that the truncate doesn't manage
indexes the same as a delete would?
Chuck
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Not quite sure why sometimes it takes much longer but it may have something
to do with the query plans.. If you are starting with a small or empty
table the query plan will probably be a scan. As you continue to insert the
rows the scans will get longer and longer. Do you have proper indexes on
the table to ensure an efficient plan? If so then you may want to try and
use an Index hint(s) to force seeks rather than scans if necessary.
Normally I don't advocate using hints but since this is a controlled process
it should not be a problem if you know they are the correct thing to do.
Andrew J. Kelly SQL MVP
"Chuck Hardy" <chardy@.coreprofit.com> wrote in message
news:ueOQ7otpEHA.3244@.tk2msftngp13.phx.gbl...
> Thanks for replying Andrew.
> I did forget to mention: when this occurs CPU usage is 100% with minor
> disk access every few seconds. There is never any blocking. These jobs
> are being run on a dedicated server with a single user.
> The odd thing is that the same data and system conditions produce wildly
> different results. In between runs with the same data I truncate the
> results table. Is there a possibility that the truncate doesn't manage
> indexes the same as a delete would?
> Chuck
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||I've used query optimizer to create indexes as required.
You're right though, the results table starts empty and fills up at
about 50-100K records per each of the 125+ statements that get executed.
Unfortunately, I don't have a lot of control over the way the queries
are built as a 3rd party tool to us does that. I can, however, control
the execution of batches to some degree. Would it make sense to run 1
or 2 smaller batches before the batch that causes the problem?
Also, is it possible that the number of inserts is causing index
fragmentation? (I haven't run DBCC SHOWCONTIG on the results table.)
Would index frag cause 100% CPU spin? BTW, I don't see an appreciable
change in memory usage or paging while this condition exists.
Andrew, thanks for all of your help.
Chuck
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||What does the actual DDL for the insert table look like, especially the
Clustered index?
Andrew J. Kelly SQL MVP
"Chuck Hardy" <chardy@.coreprofit.com> wrote in message
news:%23teT4expEHA.3712@.TK2MSFTNGP15.phx.gbl...
> I've used query optimizer to create indexes as required.
> You're right though, the results table starts empty and fills up at
> about 50-100K records per each of the 125+ statements that get executed.
> Unfortunately, I don't have a lot of control over the way the queries
> are built as a 3rd party tool to us does that. I can, however, control
> the execution of batches to some degree. Would it make sense to run 1
> or 2 smaller batches before the batch that causes the problem?
> Also, is it possible that the number of inserts is causing index
> fragmentation? (I haven't run DBCC SHOWCONTIG on the results table.)
> Would index frag cause 100% CPU spin? BTW, I don't see an appreciable
> change in memory usage or paging while this condition exists.
> Andrew, thanks for all of your help.
> Chuck
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hi Andrew,
I hope this is what you're looking for. I'd ask your guidance on the
fill factors, along with any other insights. Because each of the 125
SQL statements are inserting, (and I assume but am not sure) that not
all inserts are being placed at the end of the table perhaps the fill
factors should be decreased?
Thanks,
Chuck
/****** Object: Table [dbo].[AccountValues] Script Date: 10/1/2004
9:56:09 AM ******/
CREATE TABLE [dbo].[AccountValues] (
[AccountID] [int] NOT NULL ,
[IFormulaID] [int] NOT NULL ,
[FormulaValue] [decimal](30, 10) NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [AccountValues1] ON
[dbo].[AccountValues]([AccountID]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
CREATE INDEX [AccountValues3] ON [dbo].[AccountValues]([IFormulaID],
[AccountID]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi Andrew,
Two more things:
. The problem I'm experienceing is described almost exactly in MS
KB835864, except that I'm running a single CPU.
. I have noticed that the performance statistic Available Mbytes starts
high after a system reboot, but as I execute procedures or ad-hoc
queries this slowly decreases to 5-10Mb. Is there a way to make SQL
Server free up memory without stopping and restarting the server?
Thanks,
Chuck
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Sounds like you don't have a whole lot of memory to begin with. How much do
you have and are there other processes running on the server other than SQL
Server? You can use the MAX Memory setting to ensure the OS and other apps
always have some memory available.
Andrew J. Kelly SQL MVP
"Chuck Hardy" <chardy@.coreprofit.com> wrote in message
news:e0FxYv%23pEHA.3396@.tk2msftngp13.phx.gbl...
> Hi Andrew,
> Two more things:
> The problem I'm experienceing is described almost exactly in MS
> KB835864, except that I'm running a single CPU.
> I have noticed that the performance statistic Available Mbytes starts
> high after a system reboot, but as I execute procedures or ad-hoc
> queries this slowly decreases to 5-10Mb. Is there a way to make SQL
> Server free up memory without stopping and restarting the server?
> Thanks,
> Chuck
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Chuck,
Not sure if I understand exactly what you are trying to do with that query.
It seems as if you can get the desired result like this:
SELECT A.RID, 325, SUM(A.Value) AS Value
FROM A
WHERE A.PID IN (275, 276, 277, 278, 279, 280, 281)
AND a.FID IN (496,497,499,502,504
GROUP BY a.RID
In any case you need to determine what the query plan is that this is using
during the slow periods. You can run a trace that will show recompiles and
query plans to see if they change during the operation. You can also try
specifying hints to force the proper plans.
Andrew J. Kelly SQL MVP
"Chuck Hardy" <chardy@.coreprofit.com> wrote in message
news:eSP09s9pEHA.2696@.TK2MSFTNGP15.phx.gbl...
> Hi Andrew,
> I hope this is what you're looking for. I'd ask your guidance on the
> fill factors, along with any other insights. Because each of the 125
> SQL statements are inserting, (and I assume but am not sure) that not
> all inserts are being placed at the end of the table perhaps the fill
> factors should be decreased?
> Thanks,
> Chuck
> /****** Object: Table [dbo].[AccountValues] Script Date: 10/1/2004
> 9:56:09 AM ******/
> CREATE TABLE [dbo].[AccountValues] (
> [AccountID] [int] NOT NULL ,
> [IFormulaID] [int] NOT NULL ,
> [FormulaValue] [decimal](30, 10) NULL
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [AccountValues1] ON
> [dbo].[AccountValues]([AccountID]) WITH FILLFACTOR = 80 ON [PRIMARY]
> GO
> CREATE INDEX [AccountValues3] ON [dbo].[AccountValues]([IFormulaID],
> [AccountID]) WITH FILLFACTOR = 80 ON [PRIMARY]
> GO
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
insert statements.
Our application automatically generates and sequentially
executes SQL statements that build a results table. After
loading data and running this process, all statements
(about 125 in total) will execute in about 10 minutes. If
it is necessary to rerun the statements I truncate the
results table and rerun the job. In some cases this
second job runs in 10 minutes, and in other cases it runs
6 hours. In particular, one statement takes an inordinate
amount of time.
I've used PROFILER (although I'm not too experienced in
this) and can see that when statements are running
normally the log file updates in the 1000+ per second
range, but with the problematic statement updates are in
the 10 per second range. These updates seem to appear
after the commit statement is executed.
In various combinations I've tried restarting SQLServer,
updating statistics, defragging the hard drive, using
query optimizer, all with no luck.
Any ideas?
Here's one of the statements that seems to get hung up:
Table AV has 4 columns including a Record ID (RID), a
record name (FID) and a record value (Value). This table
will ultimately have approximately 4 million rows at the
completion of the job.
Table A contains a Record ID (RID) and a Process ID
(PID). This table has approx. 100,000 records.
INSERT INTO AV(RID, FID, Value) SELECT A.RID, 325, ISNULL
(ZZTEMPA.Value, 0)+ISNULL(ZZTEMPB.Value, 0)+ISNULL
(ZZTEMPC.Value, 0)+ISNULL(ZZTEMPD.Value, 0)+ISNULL
(ZZTEMPE.Value, 0) FROM A LEFT JOIN AV AS ZZTEMPA ON A.RID
= ZZTEMPA.RID AND ZZTEMPA.FID = 496 LEFT JOIN AV AS
ZZTEMPB ON A.RID = ZZTEMPB.RID AND ZZTEMPB.FID = 497 LEFT
JOIN AV AS ZZTEMPC ON A.RID = ZZTEMPC.RID AND ZZTEMPC.FID
= 499 LEFT JOIN AV AS ZZTEMPD ON A.RID = ZZTEMPD.RID AND
ZZTEMPD.FID = 502 LEFT JOIN AV AS ZZTEMPE ON A.RID =
ZZTEMPE.RID AND ZZTEMPE.FID = 504 WHERE (A.PID IN (275,
276, 277, 278, 279, 280, 281))
I recognize that the same table AV is being joined
multiple times, but it's my understanding that this should
not be a problem.
Thanks for any guidance on this.
Not much to go on but have you looked to see if you are being blocked when
this is happening? How about disk and cpu queues?
Andrew J. Kelly SQL MVP
"Chuck Hardy" <chardy@.coreprofit.com> wrote in message
news:16c501c4a62e$f11bbd90$a601280a@.phx.gbl...
> I am experiencing inconsistent performance when running
> insert statements.
> Our application automatically generates and sequentially
> executes SQL statements that build a results table. After
> loading data and running this process, all statements
> (about 125 in total) will execute in about 10 minutes. If
> it is necessary to rerun the statements I truncate the
> results table and rerun the job. In some cases this
> second job runs in 10 minutes, and in other cases it runs
> 6 hours. In particular, one statement takes an inordinate
> amount of time.
> I've used PROFILER (although I'm not too experienced in
> this) and can see that when statements are running
> normally the log file updates in the 1000+ per second
> range, but with the problematic statement updates are in
> the 10 per second range. These updates seem to appear
> after the commit statement is executed.
> In various combinations I've tried restarting SQLServer,
> updating statistics, defragging the hard drive, using
> query optimizer, all with no luck.
> Any ideas?
> Here's one of the statements that seems to get hung up:
> Table AV has 4 columns including a Record ID (RID), a
> record name (FID) and a record value (Value). This table
> will ultimately have approximately 4 million rows at the
> completion of the job.
> Table A contains a Record ID (RID) and a Process ID
> (PID). This table has approx. 100,000 records.
> INSERT INTO AV(RID, FID, Value) SELECT A.RID, 325, ISNULL
> (ZZTEMPA.Value, 0)+ISNULL(ZZTEMPB.Value, 0)+ISNULL
> (ZZTEMPC.Value, 0)+ISNULL(ZZTEMPD.Value, 0)+ISNULL
> (ZZTEMPE.Value, 0) FROM A LEFT JOIN AV AS ZZTEMPA ON A.RID
> = ZZTEMPA.RID AND ZZTEMPA.FID = 496 LEFT JOIN AV AS
> ZZTEMPB ON A.RID = ZZTEMPB.RID AND ZZTEMPB.FID = 497 LEFT
> JOIN AV AS ZZTEMPC ON A.RID = ZZTEMPC.RID AND ZZTEMPC.FID
> = 499 LEFT JOIN AV AS ZZTEMPD ON A.RID = ZZTEMPD.RID AND
> ZZTEMPD.FID = 502 LEFT JOIN AV AS ZZTEMPE ON A.RID =
> ZZTEMPE.RID AND ZZTEMPE.FID = 504 WHERE (A.PID IN (275,
> 276, 277, 278, 279, 280, 281))
> I recognize that the same table AV is being joined
> multiple times, but it's my understanding that this should
> not be a problem.
> Thanks for any guidance on this.
>
|||Thanks for replying Andrew.
I did forget to mention: when this occurs CPU usage is 100% with minor
disk access every few seconds. There is never any blocking. These jobs
are being run on a dedicated server with a single user.
The odd thing is that the same data and system conditions produce wildly
different results. In between runs with the same data I truncate the
results table. Is there a possibility that the truncate doesn't manage
indexes the same as a delete would?
Chuck
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Not quite sure why sometimes it takes much longer but it may have something
to do with the query plans.. If you are starting with a small or empty
table the query plan will probably be a scan. As you continue to insert the
rows the scans will get longer and longer. Do you have proper indexes on
the table to ensure an efficient plan? If so then you may want to try and
use an Index hint(s) to force seeks rather than scans if necessary.
Normally I don't advocate using hints but since this is a controlled process
it should not be a problem if you know they are the correct thing to do.
Andrew J. Kelly SQL MVP
"Chuck Hardy" <chardy@.coreprofit.com> wrote in message
news:ueOQ7otpEHA.3244@.tk2msftngp13.phx.gbl...
> Thanks for replying Andrew.
> I did forget to mention: when this occurs CPU usage is 100% with minor
> disk access every few seconds. There is never any blocking. These jobs
> are being run on a dedicated server with a single user.
> The odd thing is that the same data and system conditions produce wildly
> different results. In between runs with the same data I truncate the
> results table. Is there a possibility that the truncate doesn't manage
> indexes the same as a delete would?
> Chuck
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||I've used query optimizer to create indexes as required.
You're right though, the results table starts empty and fills up at
about 50-100K records per each of the 125+ statements that get executed.
Unfortunately, I don't have a lot of control over the way the queries
are built as a 3rd party tool to us does that. I can, however, control
the execution of batches to some degree. Would it make sense to run 1
or 2 smaller batches before the batch that causes the problem?
Also, is it possible that the number of inserts is causing index
fragmentation? (I haven't run DBCC SHOWCONTIG on the results table.)
Would index frag cause 100% CPU spin? BTW, I don't see an appreciable
change in memory usage or paging while this condition exists.
Andrew, thanks for all of your help.
Chuck
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||What does the actual DDL for the insert table look like, especially the
Clustered index?
Andrew J. Kelly SQL MVP
"Chuck Hardy" <chardy@.coreprofit.com> wrote in message
news:%23teT4expEHA.3712@.TK2MSFTNGP15.phx.gbl...
> I've used query optimizer to create indexes as required.
> You're right though, the results table starts empty and fills up at
> about 50-100K records per each of the 125+ statements that get executed.
> Unfortunately, I don't have a lot of control over the way the queries
> are built as a 3rd party tool to us does that. I can, however, control
> the execution of batches to some degree. Would it make sense to run 1
> or 2 smaller batches before the batch that causes the problem?
> Also, is it possible that the number of inserts is causing index
> fragmentation? (I haven't run DBCC SHOWCONTIG on the results table.)
> Would index frag cause 100% CPU spin? BTW, I don't see an appreciable
> change in memory usage or paging while this condition exists.
> Andrew, thanks for all of your help.
> Chuck
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hi Andrew,
I hope this is what you're looking for. I'd ask your guidance on the
fill factors, along with any other insights. Because each of the 125
SQL statements are inserting, (and I assume but am not sure) that not
all inserts are being placed at the end of the table perhaps the fill
factors should be decreased?
Thanks,
Chuck
/****** Object: Table [dbo].[AccountValues] Script Date: 10/1/2004
9:56:09 AM ******/
CREATE TABLE [dbo].[AccountValues] (
[AccountID] [int] NOT NULL ,
[IFormulaID] [int] NOT NULL ,
[FormulaValue] [decimal](30, 10) NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [AccountValues1] ON
[dbo].[AccountValues]([AccountID]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
CREATE INDEX [AccountValues3] ON [dbo].[AccountValues]([IFormulaID],
[AccountID]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi Andrew,
Two more things:
. The problem I'm experienceing is described almost exactly in MS
KB835864, except that I'm running a single CPU.
. I have noticed that the performance statistic Available Mbytes starts
high after a system reboot, but as I execute procedures or ad-hoc
queries this slowly decreases to 5-10Mb. Is there a way to make SQL
Server free up memory without stopping and restarting the server?
Thanks,
Chuck
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Sounds like you don't have a whole lot of memory to begin with. How much do
you have and are there other processes running on the server other than SQL
Server? You can use the MAX Memory setting to ensure the OS and other apps
always have some memory available.
Andrew J. Kelly SQL MVP
"Chuck Hardy" <chardy@.coreprofit.com> wrote in message
news:e0FxYv%23pEHA.3396@.tk2msftngp13.phx.gbl...
> Hi Andrew,
> Two more things:
> The problem I'm experienceing is described almost exactly in MS
> KB835864, except that I'm running a single CPU.
> I have noticed that the performance statistic Available Mbytes starts
> high after a system reboot, but as I execute procedures or ad-hoc
> queries this slowly decreases to 5-10Mb. Is there a way to make SQL
> Server free up memory without stopping and restarting the server?
> Thanks,
> Chuck
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Chuck,
Not sure if I understand exactly what you are trying to do with that query.
It seems as if you can get the desired result like this:
SELECT A.RID, 325, SUM(A.Value) AS Value
FROM A
WHERE A.PID IN (275, 276, 277, 278, 279, 280, 281)
AND a.FID IN (496,497,499,502,504
GROUP BY a.RID
In any case you need to determine what the query plan is that this is using
during the slow periods. You can run a trace that will show recompiles and
query plans to see if they change during the operation. You can also try
specifying hints to force the proper plans.
Andrew J. Kelly SQL MVP
"Chuck Hardy" <chardy@.coreprofit.com> wrote in message
news:eSP09s9pEHA.2696@.TK2MSFTNGP15.phx.gbl...
> Hi Andrew,
> I hope this is what you're looking for. I'd ask your guidance on the
> fill factors, along with any other insights. Because each of the 125
> SQL statements are inserting, (and I assume but am not sure) that not
> all inserts are being placed at the end of the table perhaps the fill
> factors should be decreased?
> Thanks,
> Chuck
> /****** Object: Table [dbo].[AccountValues] Script Date: 10/1/2004
> 9:56:09 AM ******/
> CREATE TABLE [dbo].[AccountValues] (
> [AccountID] [int] NOT NULL ,
> [IFormulaID] [int] NOT NULL ,
> [FormulaValue] [decimal](30, 10) NULL
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [AccountValues1] ON
> [dbo].[AccountValues]([AccountID]) WITH FILLFACTOR = 80 ON [PRIMARY]
> GO
> CREATE INDEX [AccountValues3] ON [dbo].[AccountValues]([IFormulaID],
> [AccountID]) WITH FILLFACTOR = 80 ON [PRIMARY]
> GO
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
Friday, February 24, 2012
INCLUDE option in CREATE INDEX
What is the difference in the following CREATE INDEX statements when using
the INCLUDE option?
CREATE INDEX index1 ON table1(col3) INCLUDE(col1, col2)
CREATE INDEX index1 ON table1(col3, col1, col2)
I have read BOL on the INCLUDE option and it says that it "Specifies the
nonkey columns to be added to the leaf level of the nonclustered index."
Isn't that also being done when creating a composite index without the
INCLUDE option?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1Consider this:
drop table a
go
create table a(i int, j int)
create unique index a1 on a(i) include(j)
insert into a values(1,2)
go
insert into a values(1,3)
(1 row(s) affected)
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.a' with unique index
'a1'.
the index guarantees uniqueness of i but also stores j on its leaf
level.
Why would I need that? For index covering. I can have one and the same
index guartantee uniqueness of i and cover a query select i,j from a
where i between 1 and 10.|||Hi cbrichards
1) Included columns are in the leaf level ONLY. The do not appear in the
higher levels, as the key column of an index do.
2) The included columns play no part in the ordering of the leaf level
rows.
You may have a composite key of lastname, firstname and an included
column of city
The leaf row for Smith, Jane in Orlando may come before or after the
leaf row for Smith, Jane in Seattle. There is no predicting it or
controlling it.
3) With Included columns, you can exceed the key size limit of 900 bytes
total, and 16 columns.
This is the biggest benefit. You can get a lot more covering index
situations if you can get around the 900 byte limit.
--
HTH
Kalen Delaney, SQL Server MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:622b4dffd8727@.uwe...
> What is the difference in the following CREATE INDEX statements when using
> the INCLUDE option?
> CREATE INDEX index1 ON table1(col3) INCLUDE(col1, col2)
> CREATE INDEX index1 ON table1(col3, col1, col2)
> I have read BOL on the INCLUDE option and it says that it "Specifies the
> nonkey columns to be added to the leaf level of the nonclustered index."
> Isn't that also being done when creating a composite index without the
> INCLUDE option?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1|||In addition to the other replies: because the included columns are not
part of the index branch pages, each branch page can carry more index
keys. This results in a smaller index and potentially in a better index
depth. The lower index depth saves logical reads for every index seek.
Also, it should be a little bit less expensive to process changes in
included column when compared to indexed columns, because only the leaf
page(s) will have to be updated.
So if you need the column, but will never be filtering its value, then
it is smart idea to include it instead of indexing it.
HTH,
Gert-Jan
"cbrichards via SQLMonster.com" wrote:
> What is the difference in the following CREATE INDEX statements when using
> the INCLUDE option?
> CREATE INDEX index1 ON table1(col3) INCLUDE(col1, col2)
> CREATE INDEX index1 ON table1(col3, col1, col2)
> I have read BOL on the INCLUDE option and it says that it "Specifies the
> nonkey columns to be added to the leaf level of the nonclustered index."
> Isn't that also being done when creating a composite index without the
> INCLUDE option?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1|||Gert-Jan Strik wrote:
> So if you need the column, but will never be filtering its value, then
> it is smart idea to include it instead of indexing it.
More to the point: even if you filter on it, but the first column(s) is
selective enough, including instead of indexing might be more
efficient.
Also consider a unique index on employee_id including email_address.
The query
select employee_id, email_address from ... where email_address like
'%stone'
will use the index all right. Makes sense?|||Correct. The point of the included columns is, that no bookmark lookup
in the base table is required to access the column's value.
Side note: often, situations such as employee_id for an email_address
are already covered, because often employee_id is be the clustered index
key (default index type for the primary key), and thus automatically
part of any nonclustered index on email_address.
Gert-Jan
Alexander Kuznetsov wrote:
> Gert-Jan Strik wrote:
> >
> > So if you need the column, but will never be filtering its value, then
> > it is smart idea to include it instead of indexing it.
> More to the point: even if you filter on it, but the first column(s) is
> selective enough, including instead of indexing might be more
> efficient.
> Also consider a unique index on employee_id including email_address.
> The query
> select employee_id, email_address from ... where email_address like
> '%stone'
> will use the index all right. Makes sense?|||I put that in a blog with better explanations:
http://sql-server-tips.blogspot.com/2006/06/yet-another-index-covering-tip.html
the INCLUDE option?
CREATE INDEX index1 ON table1(col3) INCLUDE(col1, col2)
CREATE INDEX index1 ON table1(col3, col1, col2)
I have read BOL on the INCLUDE option and it says that it "Specifies the
nonkey columns to be added to the leaf level of the nonclustered index."
Isn't that also being done when creating a composite index without the
INCLUDE option?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1Consider this:
drop table a
go
create table a(i int, j int)
create unique index a1 on a(i) include(j)
insert into a values(1,2)
go
insert into a values(1,3)
(1 row(s) affected)
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.a' with unique index
'a1'.
the index guarantees uniqueness of i but also stores j on its leaf
level.
Why would I need that? For index covering. I can have one and the same
index guartantee uniqueness of i and cover a query select i,j from a
where i between 1 and 10.|||Hi cbrichards
1) Included columns are in the leaf level ONLY. The do not appear in the
higher levels, as the key column of an index do.
2) The included columns play no part in the ordering of the leaf level
rows.
You may have a composite key of lastname, firstname and an included
column of city
The leaf row for Smith, Jane in Orlando may come before or after the
leaf row for Smith, Jane in Seattle. There is no predicting it or
controlling it.
3) With Included columns, you can exceed the key size limit of 900 bytes
total, and 16 columns.
This is the biggest benefit. You can get a lot more covering index
situations if you can get around the 900 byte limit.
--
HTH
Kalen Delaney, SQL Server MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:622b4dffd8727@.uwe...
> What is the difference in the following CREATE INDEX statements when using
> the INCLUDE option?
> CREATE INDEX index1 ON table1(col3) INCLUDE(col1, col2)
> CREATE INDEX index1 ON table1(col3, col1, col2)
> I have read BOL on the INCLUDE option and it says that it "Specifies the
> nonkey columns to be added to the leaf level of the nonclustered index."
> Isn't that also being done when creating a composite index without the
> INCLUDE option?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1|||In addition to the other replies: because the included columns are not
part of the index branch pages, each branch page can carry more index
keys. This results in a smaller index and potentially in a better index
depth. The lower index depth saves logical reads for every index seek.
Also, it should be a little bit less expensive to process changes in
included column when compared to indexed columns, because only the leaf
page(s) will have to be updated.
So if you need the column, but will never be filtering its value, then
it is smart idea to include it instead of indexing it.
HTH,
Gert-Jan
"cbrichards via SQLMonster.com" wrote:
> What is the difference in the following CREATE INDEX statements when using
> the INCLUDE option?
> CREATE INDEX index1 ON table1(col3) INCLUDE(col1, col2)
> CREATE INDEX index1 ON table1(col3, col1, col2)
> I have read BOL on the INCLUDE option and it says that it "Specifies the
> nonkey columns to be added to the leaf level of the nonclustered index."
> Isn't that also being done when creating a composite index without the
> INCLUDE option?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1|||Gert-Jan Strik wrote:
> So if you need the column, but will never be filtering its value, then
> it is smart idea to include it instead of indexing it.
More to the point: even if you filter on it, but the first column(s) is
selective enough, including instead of indexing might be more
efficient.
Also consider a unique index on employee_id including email_address.
The query
select employee_id, email_address from ... where email_address like
'%stone'
will use the index all right. Makes sense?|||Correct. The point of the included columns is, that no bookmark lookup
in the base table is required to access the column's value.
Side note: often, situations such as employee_id for an email_address
are already covered, because often employee_id is be the clustered index
key (default index type for the primary key), and thus automatically
part of any nonclustered index on email_address.
Gert-Jan
Alexander Kuznetsov wrote:
> Gert-Jan Strik wrote:
> >
> > So if you need the column, but will never be filtering its value, then
> > it is smart idea to include it instead of indexing it.
> More to the point: even if you filter on it, but the first column(s) is
> selective enough, including instead of indexing might be more
> efficient.
> Also consider a unique index on employee_id including email_address.
> The query
> select employee_id, email_address from ... where email_address like
> '%stone'
> will use the index all right. Makes sense?|||I put that in a blog with better explanations:
http://sql-server-tips.blogspot.com/2006/06/yet-another-index-covering-tip.html
Subscribe to:
Posts (Atom)