Hi,
I am trying to create index on view. First of all in design view I
cannot use Manage Indexes command.(it is disabled)
Then if I try to add Create Index... command in the View Properties I
cannot use statement "GO"
System is prompting me: incorrent syntaxt near 'GO'
If I remove Go everything works well.
I was trying that on different views but I cannot use GO anywhere.
Thank you for help
ArekGO is not a T-SQL command. It is a command for Query Analyzer and OSQL
client apps, to tell them when to fininsh a bach. Look more about batches in
Books OnLine.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112854192.939546.198960@.o13g2000cwo.googlegroups.com...
> Hi,
> I am trying to create index on view. First of all in design view I
> cannot use Manage Indexes command.(it is disabled)
> Then if I try to add Create Index... command in the View Properties I
> cannot use statement "GO"
> System is prompting me: incorrent syntaxt near 'GO'
> If I remove Go everything works well.
> I was trying that on different views but I cannot use GO anywhere.
> Thank you for help
> Arek
>|||You can't put multiple statements in a view definition and GO isn't a
TSQL command anyway - it's a batch separator.
Does the view conform to the rules for indexed views? Maybe that
explains why the index management option is disabled. Query Analyzer is
a much better place to make schema changes so use QA rather than
Enterprise Manager.
--
David Portas
SQL Server MVP
--|||Thank you.
I was looking for all the rules, and couldn't create that indexed view
in Query Analazer anyway. I will read more about those rules.
Showing posts with label index. Show all posts
Showing posts with label index. Show all posts
Monday, March 26, 2012
Incorrect syntax near 'Go'
Hi,
I am trying to create index on view. First of all in design view I
cannot use Manage Indexes command.(it is disabled)
Then if I try to add Create Index... command in the View Properties I
cannot use statement "GO"
System is prompting me: incorrent syntaxt near 'GO'
If I remove Go everything works well.
I was trying that on different views but I cannot use GO anywhere.
Thank you for help
Arek
GO is not a T-SQL command. It is a command for Query Analyzer and OSQL
client apps, to tell them when to fininsh a bach. Look more about batches in
Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112854192.939546.198960@.o13g2000cwo.googlegr oups.com...
> Hi,
> I am trying to create index on view. First of all in design view I
> cannot use Manage Indexes command.(it is disabled)
> Then if I try to add Create Index... command in the View Properties I
> cannot use statement "GO"
> System is prompting me: incorrent syntaxt near 'GO'
> If I remove Go everything works well.
> I was trying that on different views but I cannot use GO anywhere.
> Thank you for help
> Arek
>
|||You can't put multiple statements in a view definition and GO isn't a
TSQL command anyway - it's a batch separator.
Does the view conform to the rules for indexed views? Maybe that
explains why the index management option is disabled. Query Analyzer is
a much better place to make schema changes so use QA rather than
Enterprise Manager.
David Portas
SQL Server MVP
|||Thank you.
I was looking for all the rules, and couldn't create that indexed view
in Query Analazer anyway. I will read more about those rules.
I am trying to create index on view. First of all in design view I
cannot use Manage Indexes command.(it is disabled)
Then if I try to add Create Index... command in the View Properties I
cannot use statement "GO"
System is prompting me: incorrent syntaxt near 'GO'
If I remove Go everything works well.
I was trying that on different views but I cannot use GO anywhere.
Thank you for help
Arek
GO is not a T-SQL command. It is a command for Query Analyzer and OSQL
client apps, to tell them when to fininsh a bach. Look more about batches in
Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112854192.939546.198960@.o13g2000cwo.googlegr oups.com...
> Hi,
> I am trying to create index on view. First of all in design view I
> cannot use Manage Indexes command.(it is disabled)
> Then if I try to add Create Index... command in the View Properties I
> cannot use statement "GO"
> System is prompting me: incorrent syntaxt near 'GO'
> If I remove Go everything works well.
> I was trying that on different views but I cannot use GO anywhere.
> Thank you for help
> Arek
>
|||You can't put multiple statements in a view definition and GO isn't a
TSQL command anyway - it's a batch separator.
Does the view conform to the rules for indexed views? Maybe that
explains why the index management option is disabled. Query Analyzer is
a much better place to make schema changes so use QA rather than
Enterprise Manager.
David Portas
SQL Server MVP
|||Thank you.
I was looking for all the rules, and couldn't create that indexed view
in Query Analazer anyway. I will read more about those rules.
Incorrect syntax near 'Go'
Hi,
I am trying to create index on view. First of all in design view I
cannot use Manage Indexes command.(it is disabled)
Then if I try to add Create Index... command in the View Properties I
cannot use statement "GO"
System is prompting me: incorrent syntaxt near 'GO'
If I remove Go everything works well.
I was trying that on different views but I cannot use GO anywhere.
Thank you for help
ArekGO is not a T-SQL command. It is a command for Query Analyzer and OSQL
client apps, to tell them when to fininsh a bach. Look more about batches in
Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112854192.939546.198960@.o13g2000cwo.googlegroups.com...
> Hi,
> I am trying to create index on view. First of all in design view I
> cannot use Manage Indexes command.(it is disabled)
> Then if I try to add Create Index... command in the View Properties I
> cannot use statement "GO"
> System is prompting me: incorrent syntaxt near 'GO'
> If I remove Go everything works well.
> I was trying that on different views but I cannot use GO anywhere.
> Thank you for help
> Arek
>|||You can't put multiple statements in a view definition and GO isn't a
TSQL command anyway - it's a batch separator.
Does the view conform to the rules for indexed views? Maybe that
explains why the index management option is disabled. Query Analyzer is
a much better place to make schema changes so use QA rather than
Enterprise Manager.
David Portas
SQL Server MVP
--|||Thank you.
I was looking for all the rules, and couldn't create that indexed view
in Query Analazer anyway. I will read more about those rules.
I am trying to create index on view. First of all in design view I
cannot use Manage Indexes command.(it is disabled)
Then if I try to add Create Index... command in the View Properties I
cannot use statement "GO"
System is prompting me: incorrent syntaxt near 'GO'
If I remove Go everything works well.
I was trying that on different views but I cannot use GO anywhere.
Thank you for help
ArekGO is not a T-SQL command. It is a command for Query Analyzer and OSQL
client apps, to tell them when to fininsh a bach. Look more about batches in
Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112854192.939546.198960@.o13g2000cwo.googlegroups.com...
> Hi,
> I am trying to create index on view. First of all in design view I
> cannot use Manage Indexes command.(it is disabled)
> Then if I try to add Create Index... command in the View Properties I
> cannot use statement "GO"
> System is prompting me: incorrent syntaxt near 'GO'
> If I remove Go everything works well.
> I was trying that on different views but I cannot use GO anywhere.
> Thank you for help
> Arek
>|||You can't put multiple statements in a view definition and GO isn't a
TSQL command anyway - it's a batch separator.
Does the view conform to the rules for indexed views? Maybe that
explains why the index management option is disabled. Query Analyzer is
a much better place to make schema changes so use QA rather than
Enterprise Manager.
David Portas
SQL Server MVP
--|||Thank you.
I was looking for all the rules, and couldn't create that indexed view
in Query Analazer anyway. I will read more about those rules.
Friday, March 9, 2012
Inconsistant query results with Count(*)... bad index?
I have a table that seems to have a bad index. When I do the following
query I get inconsistant and needless to say incorrect results.
query I get inconsistant and needless to say incorrect results.
select count(*) from mytable where mycolumn = 1
If I remove the index from "mycolumn" the query works correctly. If I
add the index back (even with a new name etc...) it doesn't work
right.
Has anyone ran into this? or does anyone know how I can fix this
problem?
It seems that removing the index is not really removing everything
because when I add a new one I get this same problem... btw, this is
isolated to this column on this table. all other indexes within the
database are fine.
Any help would be appreciated.
Thanks,
dharperDharper:
Are you able to recreate this using a simple sample with DDL and data that
you can post?
"dharper" <dharper@.ablesoftonline.com> wrote in message
news:a1ffc0e2.0312180711.505ff5d9@.posting.google.c om...
> I have a table that seems to have a bad index. When I do the following
> query I get inconsistant and needless to say incorrect results.
> select count(*) from mytable where mycolumn = 1
> If I remove the index from "mycolumn" the query works correctly. If I
> add the index back (even with a new name etc...) it doesn't work
> right.
> Has anyone ran into this? or does anyone know how I can fix this
> problem?
> It seems that removing the index is not really removing everything
> because when I add a new one I get this same problem... btw, this is
> isolated to this column on this table. all other indexes within the
> database are fine.
> Any help would be appreciated.
> Thanks,
> dharper
incomplete Full text index for large dbs in sql 2005?
we have created a full text catalogue on an Addresses table with exactly same
configuration on 3 databases. Two of them are on SQL 2005 and one on SQL 2000.
My Addresses table has 13 million rows and the full text was on the Full
Address column.
db is around 250GB
FT Index on SQL 2005 database was created very quickly (within 2 hours)
while SQL 2000 FT took 30 hours.
But my issue is SQL 2005 index does not contain all the words. For an
example following 2 queries gives me the exactly same number on the SQL 2000
database while on SQL 2005, first query only return 11 and the 2nd one 274
(274 is same on 2000 server/database as well)
SELECT count(*)
FROM [dbo].[Addresses]
WHERE CONTAINS(FullAddress, 'WARMINSTER')
GO
SELECT count(*)
FROM [dbo].[Addresses]
WHERE FullAddress like '%WARMINSTER%'
Is this a bug?
When i extracted the records which should have retrived in to a seperate
table (few hundred records) and created a FT index on that it works fine.
CTS DBA
I've had problems on very large databases with this. I think we were over
400,000,000 rows when we first noticed it. A reorganize would normally help.
Can you first run this query?
SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
'%WARMINSTER%' and not like '% WARMINSTER %'
See if perhaps you are getting hits to Warministershireontheavon or
something like that. Note that most punctuation will be ignored.
If you are trully missing hits can you check the gatherer log to see if
there are any error messages?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
news:0E0072FB-B92A-4E2C-9915-99D0667C0BCA@.microsoft.com...
> we have created a full text catalogue on an Addresses table with exactly
> same
> configuration on 3 databases. Two of them are on SQL 2005 and one on SQL
> 2000.
> My Addresses table has 13 million rows and the full text was on the Full
> Address column.
> db is around 250GB
> FT Index on SQL 2005 database was created very quickly (within 2 hours)
> while SQL 2000 FT took 30 hours.
> But my issue is SQL 2005 index does not contain all the words. For an
> example following 2 queries gives me the exactly same number on the SQL
> 2000
> database while on SQL 2005, first query only return 11 and the 2nd one 274
> (274 is same on 2000 server/database as well)
> SELECT count(*)
> FROM [dbo].[Addresses]
> WHERE CONTAINS(FullAddress, 'WARMINSTER')
> GO
> SELECT count(*)
> FROM [dbo].[Addresses]
> WHERE FullAddress like '%WARMINSTER%'
> Is this a bug?
> When i extracted the records which should have retrived in to a seperate
> table (few hundred records) and created a FT index on that it works fine.
>
> --
> CTS DBA
|||Thanks Hilary,
SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
'%WARMINSTER%' and not like '% WARMINSTER %'
The Query returns only 4 records which means still so many records missing
in the SQL 2005 FT catalog. I have started the re-organize (ALTER FULLTEXT
CATALOG cat_Addresses REORGANIZE) and update you with the results when it’s
completed.
thanks
CTS DBA
"Hilary Cotter" wrote:
> I've had problems on very large databases with this. I think we were over
> 400,000,000 rows when we first noticed it. A reorganize would normally help.
> Can you first run this query?
> SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
> '%WARMINSTER%' and not like '% WARMINSTER %'
> See if perhaps you are getting hits to Warministershireontheavon or
> something like that. Note that most punctuation will be ignored.
> If you are trully missing hits can you check the gatherer log to see if
> there are any error messages?
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
> news:0E0072FB-B92A-4E2C-9915-99D0667C0BCA@.microsoft.com...
>
>
|||Re-organize has been completed. But still no luck. And there is nothing in
the gatherer logs as well. See below from the gatherer logs for the entries
from re-organize and re-building I did today.
RE_BUILDING
2006-11-29 09:28:15.58 spid20s Informational: Full-text Full population
completed for table or indexed view '[CI_InvTest].[dbo].[Addresses]' (table
or indexed view ID '101575400', database ID '41'). Number of documents
processed: 10038019. Number of documents failed: 0. Number of documents need
retry: 0.
2006-11-29 09:28:15.58 spid20s Changing the status to MERGE for
full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41). This
is an informational message only. No user action is required.
2006-11-29 09:28:16.89 spid20s Informational: Full-text Auto population
initialized for table or indexed view '[CI_InvTest].[dbo].[Addresses]' (table
or indexed view ID '101575400', database ID '41'). Population sub-tasks: 1.
RE-ORGANIZING
2006-11-29 09:38:21.04 spid56 Changing the status to MERGE for
full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41). This
is an informational message only. No user action is required.
CTS DBA
"CTS_DBA" wrote:
[vbcol=seagreen]
> Thanks Hilary,
> SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
> '%WARMINSTER%' and not like '% WARMINSTER %'
> The Query returns only 4 records which means still so many records missing
> in the SQL 2005 FT catalog. I have started the re-organize (ALTER FULLTEXT
> CATALOG cat_Addresses REORGANIZE) and update you with the results when it’s
> completed.
> thanks
>
> --
> CTS DBA
>
> "Hilary Cotter" wrote:
|||It looks like all the rows were successfully processed. My concern still is
that perhaps there are some preceding or trailing characters which might
account for these rows not showing up. Can you confirm this?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
news:C30E750E-A7CD-45F0-8699-8D96E32DDFAB@.microsoft.com...[vbcol=seagreen]
> Re-organize has been completed. But still no luck. And there is nothing in
> the gatherer logs as well. See below from the gatherer logs for the
> entries
> from re-organize and re-building I did today.
> RE_BUILDING
> 2006-11-29 09:28:15.58 spid20s Informational: Full-text Full
> population
> completed for table or indexed view '[CI_InvTest].[dbo].[Addresses]'
> (table
> or indexed view ID '101575400', database ID '41'). Number of documents
> processed: 10038019. Number of documents failed: 0. Number of documents
> need
> retry: 0.
> 2006-11-29 09:28:15.58 spid20s Changing the status to MERGE for
> full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41).
> This
> is an informational message only. No user action is required.
> 2006-11-29 09:28:16.89 spid20s Informational: Full-text Auto
> population
> initialized for table or indexed view '[CI_InvTest].[dbo].[Addresses]'
> (table
> or indexed view ID '101575400', database ID '41'). Population sub-tasks:
> 1.
>
>
> RE-ORGANIZING
> 2006-11-29 09:38:21.04 spid56 Changing the status to MERGE for
> full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41).
> This
> is an informational message only. No user action is required.
>
> --
> CTS DBA
>
> "CTS_DBA" wrote:
|||yep, cause as mentioned earlier same DB, sql 2000 FT returns the expected
result
the other thing I'm worried is that sql 2005 took 2 hours and 2000 took
around 30 hours, huge different, and of course servers are , you can sya same
hard ware.
CTS DBA
"Hilary Cotter" wrote:
> It looks like all the rows were successfully processed. My concern still is
> that perhaps there are some preceding or trailing characters which might
> account for these rows not showing up. Can you confirm this?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
> news:C30E750E-A7CD-45F0-8699-8D96E32DDFAB@.microsoft.com...
>
>
|||What if you run
SELECT fulladdress FROM [dbo].[Addresses] WHERE
FullAddress LIKE '%WARMINSTER%'
AND NOT CONTAINS(FullAddress, 'WARMINSTER')
do the fulladdress results look funny in any way, or is WARMINSTER at
the start of a word? Maybe a non-ASCII character in there somehow?
Ben Strackany
CTS_DBA wrote:[vbcol=seagreen]
> Thanks Hilary,
> SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
> '%WARMINSTER%' and not like '% WARMINSTER %'
> The Query returns only 4 records which means still so many records missing
> in the SQL 2005 FT catalog. I have started the re-organize (ALTER FULLTEXT
> CATALOG cat_Addresses REORGANIZE) and update you with the results when it's
> completed.
> thanks
>
> --
> CTS DBA
>
> "Hilary Cotter" wrote:
|||This says everything processed successfully.
I am really not sure how to advice you on where to go from here.
One of my lingering concerns is that this query
SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
'%WARMINSTER%' and not like '% WARMINSTER %'
returns 4 results - what are they?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
news:C30E750E-A7CD-45F0-8699-8D96E32DDFAB@.microsoft.com...[vbcol=seagreen]
> Re-organize has been completed. But still no luck. And there is nothing in
> the gatherer logs as well. See below from the gatherer logs for the
> entries
> from re-organize and re-building I did today.
> RE_BUILDING
> 2006-11-29 09:28:15.58 spid20s Informational: Full-text Full
> population
> completed for table or indexed view '[CI_InvTest].[dbo].[Addresses]'
> (table
> or indexed view ID '101575400', database ID '41'). Number of documents
> processed: 10038019. Number of documents failed: 0. Number of documents
> need
> retry: 0.
> 2006-11-29 09:28:15.58 spid20s Changing the status to MERGE for
> full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41).
> This
> is an informational message only. No user action is required.
> 2006-11-29 09:28:16.89 spid20s Informational: Full-text Auto
> population
> initialized for table or indexed view '[CI_InvTest].[dbo].[Addresses]'
> (table
> or indexed view ID '101575400', database ID '41'). Population sub-tasks:
> 1.
>
>
> RE-ORGANIZING
> 2006-11-29 09:38:21.04 spid56 Changing the status to MERGE for
> full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41).
> This
> is an informational message only. No user action is required.
>
> --
> CTS DBA
>
> "CTS_DBA" wrote:
configuration on 3 databases. Two of them are on SQL 2005 and one on SQL 2000.
My Addresses table has 13 million rows and the full text was on the Full
Address column.
db is around 250GB
FT Index on SQL 2005 database was created very quickly (within 2 hours)
while SQL 2000 FT took 30 hours.
But my issue is SQL 2005 index does not contain all the words. For an
example following 2 queries gives me the exactly same number on the SQL 2000
database while on SQL 2005, first query only return 11 and the 2nd one 274
(274 is same on 2000 server/database as well)
SELECT count(*)
FROM [dbo].[Addresses]
WHERE CONTAINS(FullAddress, 'WARMINSTER')
GO
SELECT count(*)
FROM [dbo].[Addresses]
WHERE FullAddress like '%WARMINSTER%'
Is this a bug?
When i extracted the records which should have retrived in to a seperate
table (few hundred records) and created a FT index on that it works fine.
CTS DBA
I've had problems on very large databases with this. I think we were over
400,000,000 rows when we first noticed it. A reorganize would normally help.
Can you first run this query?
SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
'%WARMINSTER%' and not like '% WARMINSTER %'
See if perhaps you are getting hits to Warministershireontheavon or
something like that. Note that most punctuation will be ignored.
If you are trully missing hits can you check the gatherer log to see if
there are any error messages?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
news:0E0072FB-B92A-4E2C-9915-99D0667C0BCA@.microsoft.com...
> we have created a full text catalogue on an Addresses table with exactly
> same
> configuration on 3 databases. Two of them are on SQL 2005 and one on SQL
> 2000.
> My Addresses table has 13 million rows and the full text was on the Full
> Address column.
> db is around 250GB
> FT Index on SQL 2005 database was created very quickly (within 2 hours)
> while SQL 2000 FT took 30 hours.
> But my issue is SQL 2005 index does not contain all the words. For an
> example following 2 queries gives me the exactly same number on the SQL
> 2000
> database while on SQL 2005, first query only return 11 and the 2nd one 274
> (274 is same on 2000 server/database as well)
> SELECT count(*)
> FROM [dbo].[Addresses]
> WHERE CONTAINS(FullAddress, 'WARMINSTER')
> GO
> SELECT count(*)
> FROM [dbo].[Addresses]
> WHERE FullAddress like '%WARMINSTER%'
> Is this a bug?
> When i extracted the records which should have retrived in to a seperate
> table (few hundred records) and created a FT index on that it works fine.
>
> --
> CTS DBA
|||Thanks Hilary,
SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
'%WARMINSTER%' and not like '% WARMINSTER %'
The Query returns only 4 records which means still so many records missing
in the SQL 2005 FT catalog. I have started the re-organize (ALTER FULLTEXT
CATALOG cat_Addresses REORGANIZE) and update you with the results when it’s
completed.
thanks
CTS DBA
"Hilary Cotter" wrote:
> I've had problems on very large databases with this. I think we were over
> 400,000,000 rows when we first noticed it. A reorganize would normally help.
> Can you first run this query?
> SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
> '%WARMINSTER%' and not like '% WARMINSTER %'
> See if perhaps you are getting hits to Warministershireontheavon or
> something like that. Note that most punctuation will be ignored.
> If you are trully missing hits can you check the gatherer log to see if
> there are any error messages?
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
> news:0E0072FB-B92A-4E2C-9915-99D0667C0BCA@.microsoft.com...
>
>
|||Re-organize has been completed. But still no luck. And there is nothing in
the gatherer logs as well. See below from the gatherer logs for the entries
from re-organize and re-building I did today.
RE_BUILDING
2006-11-29 09:28:15.58 spid20s Informational: Full-text Full population
completed for table or indexed view '[CI_InvTest].[dbo].[Addresses]' (table
or indexed view ID '101575400', database ID '41'). Number of documents
processed: 10038019. Number of documents failed: 0. Number of documents need
retry: 0.
2006-11-29 09:28:15.58 spid20s Changing the status to MERGE for
full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41). This
is an informational message only. No user action is required.
2006-11-29 09:28:16.89 spid20s Informational: Full-text Auto population
initialized for table or indexed view '[CI_InvTest].[dbo].[Addresses]' (table
or indexed view ID '101575400', database ID '41'). Population sub-tasks: 1.
RE-ORGANIZING
2006-11-29 09:38:21.04 spid56 Changing the status to MERGE for
full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41). This
is an informational message only. No user action is required.
CTS DBA
"CTS_DBA" wrote:
[vbcol=seagreen]
> Thanks Hilary,
> SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
> '%WARMINSTER%' and not like '% WARMINSTER %'
> The Query returns only 4 records which means still so many records missing
> in the SQL 2005 FT catalog. I have started the re-organize (ALTER FULLTEXT
> CATALOG cat_Addresses REORGANIZE) and update you with the results when it’s
> completed.
> thanks
>
> --
> CTS DBA
>
> "Hilary Cotter" wrote:
|||It looks like all the rows were successfully processed. My concern still is
that perhaps there are some preceding or trailing characters which might
account for these rows not showing up. Can you confirm this?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
news:C30E750E-A7CD-45F0-8699-8D96E32DDFAB@.microsoft.com...[vbcol=seagreen]
> Re-organize has been completed. But still no luck. And there is nothing in
> the gatherer logs as well. See below from the gatherer logs for the
> entries
> from re-organize and re-building I did today.
> RE_BUILDING
> 2006-11-29 09:28:15.58 spid20s Informational: Full-text Full
> population
> completed for table or indexed view '[CI_InvTest].[dbo].[Addresses]'
> (table
> or indexed view ID '101575400', database ID '41'). Number of documents
> processed: 10038019. Number of documents failed: 0. Number of documents
> need
> retry: 0.
> 2006-11-29 09:28:15.58 spid20s Changing the status to MERGE for
> full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41).
> This
> is an informational message only. No user action is required.
> 2006-11-29 09:28:16.89 spid20s Informational: Full-text Auto
> population
> initialized for table or indexed view '[CI_InvTest].[dbo].[Addresses]'
> (table
> or indexed view ID '101575400', database ID '41'). Population sub-tasks:
> 1.
>
>
> RE-ORGANIZING
> 2006-11-29 09:38:21.04 spid56 Changing the status to MERGE for
> full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41).
> This
> is an informational message only. No user action is required.
>
> --
> CTS DBA
>
> "CTS_DBA" wrote:
|||yep, cause as mentioned earlier same DB, sql 2000 FT returns the expected
result
the other thing I'm worried is that sql 2005 took 2 hours and 2000 took
around 30 hours, huge different, and of course servers are , you can sya same
hard ware.
CTS DBA
"Hilary Cotter" wrote:
> It looks like all the rows were successfully processed. My concern still is
> that perhaps there are some preceding or trailing characters which might
> account for these rows not showing up. Can you confirm this?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
> news:C30E750E-A7CD-45F0-8699-8D96E32DDFAB@.microsoft.com...
>
>
|||What if you run
SELECT fulladdress FROM [dbo].[Addresses] WHERE
FullAddress LIKE '%WARMINSTER%'
AND NOT CONTAINS(FullAddress, 'WARMINSTER')
do the fulladdress results look funny in any way, or is WARMINSTER at
the start of a word? Maybe a non-ASCII character in there somehow?
Ben Strackany
CTS_DBA wrote:[vbcol=seagreen]
> Thanks Hilary,
> SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
> '%WARMINSTER%' and not like '% WARMINSTER %'
> The Query returns only 4 records which means still so many records missing
> in the SQL 2005 FT catalog. I have started the re-organize (ALTER FULLTEXT
> CATALOG cat_Addresses REORGANIZE) and update you with the results when it's
> completed.
> thanks
>
> --
> CTS DBA
>
> "Hilary Cotter" wrote:
|||This says everything processed successfully.
I am really not sure how to advice you on where to go from here.
One of my lingering concerns is that this query
SELECT fulladdress FROM [dbo].[Addresses] WHERE FullAddress like
'%WARMINSTER%' and not like '% WARMINSTER %'
returns 4 results - what are they?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
news:C30E750E-A7CD-45F0-8699-8D96E32DDFAB@.microsoft.com...[vbcol=seagreen]
> Re-organize has been completed. But still no luck. And there is nothing in
> the gatherer logs as well. See below from the gatherer logs for the
> entries
> from re-organize and re-building I did today.
> RE_BUILDING
> 2006-11-29 09:28:15.58 spid20s Informational: Full-text Full
> population
> completed for table or indexed view '[CI_InvTest].[dbo].[Addresses]'
> (table
> or indexed view ID '101575400', database ID '41'). Number of documents
> processed: 10038019. Number of documents failed: 0. Number of documents
> need
> retry: 0.
> 2006-11-29 09:28:15.58 spid20s Changing the status to MERGE for
> full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41).
> This
> is an informational message only. No user action is required.
> 2006-11-29 09:28:16.89 spid20s Informational: Full-text Auto
> population
> initialized for table or indexed view '[CI_InvTest].[dbo].[Addresses]'
> (table
> or indexed view ID '101575400', database ID '41'). Population sub-tasks:
> 1.
>
>
> RE-ORGANIZING
> 2006-11-29 09:38:21.04 spid56 Changing the status to MERGE for
> full-text catalog "cat_Addresses_AI" (7) in database "CI_InvTest" (41).
> This
> is an informational message only. No user action is required.
>
> --
> CTS DBA
>
> "CTS_DBA" wrote:
Wednesday, March 7, 2012
Included columns in index.. performance implications ?
How detrimental is it to create multiple indexes that have included columns
as opposed to creating composite indexes that include that all those columns
?
I want to add the index with included columns but also concerned about
slowness in performance on writes. Either way I want to create the index..
the only difference will be whether I decide to have those included columns
or not..
ThanksIt is not really a performance decision. It depends if those columns will be
used, for example, on the WHERE clause or just on the SELECT clause. If thes
e
columns will be listed only in the SELECT clause then use included columns.
If you are going to use those columns as a search criteria then included
columns would not help.
Regarding performance, include columns are better because they are only
stored at the leaf level of the index.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:
> How detrimental is it to create multiple indexes that have included column
s
> as opposed to creating composite indexes that include that all those colum
ns
> ?
> I want to add the index with included columns but also concerned about
> slowness in performance on writes. Either way I want to create the index.
.
> the only difference will be whether I decide to have those included column
s
> or not..
> Thanks
>
as opposed to creating composite indexes that include that all those columns
?
I want to add the index with included columns but also concerned about
slowness in performance on writes. Either way I want to create the index..
the only difference will be whether I decide to have those included columns
or not..
ThanksIt is not really a performance decision. It depends if those columns will be
used, for example, on the WHERE clause or just on the SELECT clause. If thes
e
columns will be listed only in the SELECT clause then use included columns.
If you are going to use those columns as a search criteria then included
columns would not help.
Regarding performance, include columns are better because they are only
stored at the leaf level of the index.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:
> How detrimental is it to create multiple indexes that have included column
s
> as opposed to creating composite indexes that include that all those colum
ns
> ?
> I want to add the index with included columns but also concerned about
> slowness in performance on writes. Either way I want to create the index.
.
> the only difference will be whether I decide to have those included column
s
> or not..
> Thanks
>
Included columns in index.. performance implications ?
How detrimental is it to create multiple indexes that have included columns
as opposed to creating composite indexes that include that all those columns
?
I want to add the index with included columns but also concerned about
slowness in performance on writes. Either way I want to create the index..
the only difference will be whether I decide to have those included columns
or not..
Thanks
It is not really a performance decision. It depends if those columns will be
used, for example, on the WHERE clause or just on the SELECT clause. If these
columns will be listed only in the SELECT clause then use included columns.
If you are going to use those columns as a search criteria then included
columns would not help.
Regarding performance, include columns are better because they are only
stored at the leaf level of the index.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:
> How detrimental is it to create multiple indexes that have included columns
> as opposed to creating composite indexes that include that all those columns
> ?
> I want to add the index with included columns but also concerned about
> slowness in performance on writes. Either way I want to create the index..
> the only difference will be whether I decide to have those included columns
> or not..
> Thanks
>
as opposed to creating composite indexes that include that all those columns
?
I want to add the index with included columns but also concerned about
slowness in performance on writes. Either way I want to create the index..
the only difference will be whether I decide to have those included columns
or not..
Thanks
It is not really a performance decision. It depends if those columns will be
used, for example, on the WHERE clause or just on the SELECT clause. If these
columns will be listed only in the SELECT clause then use included columns.
If you are going to use those columns as a search criteria then included
columns would not help.
Regarding performance, include columns are better because they are only
stored at the leaf level of the index.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:
> How detrimental is it to create multiple indexes that have included columns
> as opposed to creating composite indexes that include that all those columns
> ?
> I want to add the index with included columns but also concerned about
> slowness in performance on writes. Either way I want to create the index..
> the only difference will be whether I decide to have those included columns
> or not..
> Thanks
>
Included columns in index.. performance implications ?
How detrimental is it to create multiple indexes that have included columns
as opposed to creating composite indexes that include that all those columns
?
I want to add the index with included columns but also concerned about
slowness in performance on writes. Either way I want to create the index..
the only difference will be whether I decide to have those included columns
or not..
ThanksIt is not really a performance decision. It depends if those columns will be
used, for example, on the WHERE clause or just on the SELECT clause. If these
columns will be listed only in the SELECT clause then use included columns.
If you are going to use those columns as a search criteria then included
columns would not help.
Regarding performance, include columns are better because they are only
stored at the leaf level of the index.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:
> How detrimental is it to create multiple indexes that have included columns
> as opposed to creating composite indexes that include that all those columns
> ?
> I want to add the index with included columns but also concerned about
> slowness in performance on writes. Either way I want to create the index..
> the only difference will be whether I decide to have those included columns
> or not..
> Thanks
>
as opposed to creating composite indexes that include that all those columns
?
I want to add the index with included columns but also concerned about
slowness in performance on writes. Either way I want to create the index..
the only difference will be whether I decide to have those included columns
or not..
ThanksIt is not really a performance decision. It depends if those columns will be
used, for example, on the WHERE clause or just on the SELECT clause. If these
columns will be listed only in the SELECT clause then use included columns.
If you are going to use those columns as a search criteria then included
columns would not help.
Regarding performance, include columns are better because they are only
stored at the leaf level of the index.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:
> How detrimental is it to create multiple indexes that have included columns
> as opposed to creating composite indexes that include that all those columns
> ?
> I want to add the index with included columns but also concerned about
> slowness in performance on writes. Either way I want to create the index..
> the only difference will be whether I decide to have those included columns
> or not..
> Thanks
>
Friday, February 24, 2012
INCLUDE option in CREATE INDEX
Consider 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 droptable.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 droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1|||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 droptable.com
http://www.droptable.com/Uwe/Forum...server/200606/1|||Consider 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 droptable.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 droptable.com
> http://www.droptable.com/Uwe/Forum...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 droptable.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 droptable.com
> http://www.droptable.com/Uwe/Forum...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 droptable.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 droptable.com
> http://www.droptable.com/Uwe/Forum...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:
> 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?|||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?
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 droptable.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 droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1|||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 droptable.com
http://www.droptable.com/Uwe/Forum...server/200606/1|||Consider 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 droptable.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 droptable.com
> http://www.droptable.com/Uwe/Forum...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 droptable.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 droptable.com
> http://www.droptable.com/Uwe/Forum...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 droptable.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 droptable.com
> http://www.droptable.com/Uwe/Forum...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:
> 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?|||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?
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
In(..) and Like xxxx% performance
Hi,
I am experiencing strange SQL server behavior,
I am experiencing strange SQL server behavior,
The table has an index on (filed1, field2) - which is primary key.
if I do select where field1 = 'a' and field2 like '123%' runs fast.
if I do select where field1 = 'b' and field2 like '123%' runs fast
too.
if I do select where field1 in ('a','b') and field2 like '123%' the
damn thing runs forever.
What is interesting, the SQL plan is perfectly using primary index and
selection is translated into "good" ranges for field2. It fills like
server does not take in consideration that field2 is in index.
Any ideas what's going on? I'd hate to use two selects to avoid
problem.
Thank you.
M.Q.Mark Quest (nospamhere2@.yahoo.com) writes:
> I am experiencing strange SQL server behavior,
> The table has an index on (filed1, field2) - which is primary key.
> if I do select where field1 = 'a' and field2 like '123%' runs fast.
> if I do select where field1 = 'b' and field2 like '123%' runs fast
> too.
> if I do select where field1 in ('a','b') and field2 like '123%' the
> damn thing runs forever.
> What is interesting, the SQL plan is perfectly using primary index and
> selection is translated into "good" ranges for field2. It fills like
> server does not take in consideration that field2 is in index.
> Any ideas what's going on? I'd hate to use two selects to avoid
> problem.
Sounds a bit funny, but without knowledge of the table and query plan,
it's a little bit difficult to say what is going on.
If you can demonstrate the situation with a script that anyone could
copy and paste and run on his machine, that would be something to bring
up with Microsoft. But I would guess that it is not that simple to
reproduce.
But maybe you could post the CREATE TABLE and CREATE INDEX statements
for the table, and also give some indication of sizes and data distribution?
And the exact text of the slow query as well as the output from
SET SHOWPLAN_ALL ON for the query.
A faint possibility is that there is blocking or corruption. Check the
latter with DBCC CHECKDB.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
In What Order Index Sorts the Data?
Hi,
I want to ask a basic question, that is
IN WHAT ORDER A CLUSTERED INDEX SORT THE DATA IN THE COLUMN?
Somewhere in the MSDN library I read the following line:
"A clustered index physically sorts the table's contents in the order of the specified index columns"
But Sorting means it will be in ASCENDING ORDER (ASC) or It will be in DESCENDING ORDER (DESC)
So my question is lets suppose a column on which the cluistered index is defined and it contains character data liek abcd so in wht order it will sort the data alphabetically ASC or DESC
or
If the same above case with integer type of values, if column having integer values then in wht order the data in the table will be sorted.
?
Thanks..!!!
You are the one that has to speciy it. By default SS will use ASC.
Code Snippet
use tempdb
go
create table dbo.t1(
c1 int identity not null,
constraint pk_t1 primary key clustered (c1 ASC))
go
drop table dbo.t1
go
create table dbo.t1(
c1 int identity not null,
constraint pk_t1 primary key clustered (c1 DESC))
go
drop table dbo.t1
go
create table dbo.t1(
c1 int identity not null
)
go
create unique clustered index t1_c1_u_c_ix
on dbo.t1(c1 ASC)
go
drop index t1_c1_u_c_ix on dbo.t1
go
create unique clustered index t1_c1_u_c_ix
on dbo.t1(c1 DESC)
go
drop table dbo.t1
go
AMB|||Ascending by default, but you can specify descinding if you'd like
|||
Hi,
I want to ask a basic question, that is
IN WHAT ORDER A CLUSTERED INDEX SORT THE DATA IN THE COLUMN?
Somewhere in the MSDN library I read the following line:
"A clustered index physically sorts the table's contents in the order of the specified index columns"
But Sorting means it will be in ASCENDING ORDER (ASC) or It will be in DESCENDING ORDER (DESC)
So my question is lets suppose a column on which the cluistered index is defined and it contains character data liek abcd so in wht order it will sort the data alphabetically ASC or DESC
or
If the same above case with integer type of values, if column having integer values then in wht order the data in the table will be sorted.
?
Thanks..!!!
--
PRASHANT PANDEY
Aargh!
Prashant,
Unfortunately, this information is simply wrong. There is absolutely no way to do anything at all in SQL Server to guarantee the physical order of the data. This is a good thing. If the data could be kept this way, you might have to move many gigabytes of data just to insert one row in the middle of a huge table. This would be terrible. "Ok, everyone move over to make room for the new person."
A clustered index does optimize the data storage for retrieval in the order of the clustered key columns, but if you want to be certain that data from a SELECT query comes back in a particular order, you absolutely must include an ORDER BY clause for that SELECT.
You may discover that without the order by, it works for years, and for millions of repetitions, but nevertheless, the order is not guaranteed, and suddenly when your table gets large, or you move to a different storage or processor configuration, or you apply a service pack, you may find you no longer see data in the order you expect.
Just in case all you were asking is in what logical order the data is organized when a column is specified as a key column, it is ascending order unless you specify otherwise with DESC.
Steve Kass
Drew University
http://www.stevekass.com
Sunday, February 19, 2012
In what order does a clustered index store data?
Hi
I was going through the book by Kalen Delaney where she has mentioned the following paragpraph in Chapter 7 (Index Internals):
Many documents describing SQL Server indexes will tell you that the clustered index physically stores the data in sorted order. This can be misleading if you think of physical storage as the disk itself. If a clustered index had to keep the data on the actual disk in a particular order, it could be prohibitively expensive to make changes. If a page got too full and had to be split in two, all the data on all the succeeding pages would have to be moved down. Sorted order in a clustered index simply means that the data page chain is logically in order.
Then I read the book on SQL Server 2000 (on Perf Tuning) by Ken England. He says the clustered index stores data in physical order and any insert means moving the data physically. Also the same statement is echoed on the net by many articles.
What is the truth? How are really clustered index stored? What does physical order in the above statement really mean?
Regards
SanjaySi
It is an 'Apples and Oranges' difference.
Kalen is referrring to the actual physical disk sectors, where Ken is referring to the pages where SQL Server stores indexes.
The disk is NOT kept in perfect sequential order. Files can become fragmented from daily ordinary use. For indexes, SQL Server keeps a 'chain' of logical order, so that even where there is a page split, the chain is unbroken.
The 'Logical' order of clustered indexes is indeed kept in sequetial order. (Simplistic view coming...)
The index may be located on pages 1, 2, 3, 4, 5, 6, 27, 55, etc. The logical order 'chain', after page splits may be 1, 2, 55, 3, 27, 4, 5, 6, etc. And due to file fragmentation, the file may be stored in disk sectors 1,2, 454, 455, 1036, 1037, etc.
Subscribe to:
Posts (Atom)