Info about a running job step

Hi group,
I need to retrieve information about a running step, specifically its
status. When a step is being executed, the EM always knows and shows so
(under status showing Executing Job Step 'n (step name)'). However, I can
not find such information in the system tables. where does EM get the info
from? How can I access this info with a query, or better yet, within a
stored proc? Help is appreciated.
QuentinYou can use sp_help_job e.g.
exec msdb..sp_help_job
@.job_name = 'jobname ,
@.job_aspect = 'JOB'
Look at the current_execution_status and current_execution_step columns
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Quentin Ran" <ab@.who.com> wrote in message
news:OxXqrA3VDHA.612@.TK2MSFTNGP10.phx.gbl...
Hi group,
I need to retrieve information about a running step, specifically its
status. When a step is being executed, the EM always knows and shows so
(under status showing Executing Job Step 'n (step name)'). However, I can
not find such information in the system tables. where does EM get the info
from? How can I access this info with a query, or better yet, within a
stored proc? Help is appreciated.
Quentin|||Thanks Mr. Dentist.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:efTLfj4VDHA.1816@.TK2MSFTNGP09.phx.gbl...
> You can use sp_help_job e.g.
> exec msdb..sp_help_job
> @.job_name = 'jobname ,
> @.job_aspect = 'JOB'
> Look at the current_execution_status and current_execution_step columns
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Quentin Ran" <ab@.who.com> wrote in message
> news:OxXqrA3VDHA.612@.TK2MSFTNGP10.phx.gbl...
> Hi group,
> I need to retrieve information about a running step, specifically its
> status. When a step is being executed, the EM always knows and shows so
> (under status showing Executing Job Step 'n (step name)'). However, I can
> not find such information in the system tables. where does EM get the
info
> from? How can I access this info with a query, or better yet, within a
> stored proc? Help is appreciated.
> Quentin
>

Infinite time to query

I couldn't find any newsgroup for MS Indexing engine.. so, I am posting
it here.
Please let me know the correct group if there is any.
I am trying to query the Indexing Engine. Added a linked server
"FileSystem" and then created a view.
View definition is given below:
CREATE VIEW FileView
AS
SELECT * FROM OPENQUERY(FileSystem, 'SELECT FileName,
Characterization FROM SCOPE() ')
Any query to this view takes an infinite time. The query never stops.
Even "Count(*)" never returns. SQL profiler (when used in conjunction
with a long query containing join on this view) shows the query going
into an infinite recursion.
Any pointers to solve this issue would be very helpful.
Cheers,
Gaurav Vaish
http://mastergaurav.org
http://mastergaurav.blogspot.com
Hi,
Does the simple SELECT using four part naming convention work?
For Ex:
SELECT * FROM <linked_server_name>.<DB_Name>.<User>.<Table_Name>
Btw, What Is SCOPE()?
- - - - - - - - -
Thanks
Yogish
"MasterGaurav" wrote:

> I couldn't find any newsgroup for MS Indexing engine.. so, I am posting
> it here.
> Please let me know the correct group if there is any.
> I am trying to query the Indexing Engine. Added a linked server
> "FileSystem" and then created a view.
> View definition is given below:
> CREATE VIEW FileView
> AS
> SELECT * FROM OPENQUERY(FileSystem, 'SELECT FileName,
> Characterization FROM SCOPE() ')
>
> Any query to this view takes an infinite time. The query never stops.
> Even "Count(*)" never returns. SQL profiler (when used in conjunction
> with a long query containing join on this view) shows the query going
> into an infinite recursion.
>
> Any pointers to solve this issue would be very helpful.
>
>
> Cheers,
> Gaurav Vaish
> http://mastergaurav.org
> http://mastergaurav.blogspot.com
> --
>
|||MasterGaurav
What is SCOPE() ? Is that function?
SELECT <column list> FROM FileSystem.Database.dbo.Table/Function
"MasterGaurav" <gaurav.vaish@.gmail.com> wrote in message
news:1120729619.181576.238440@.g47g2000cwa.googlegr oups.com...
> I couldn't find any newsgroup for MS Indexing engine.. so, I am posting
> it here.
> Please let me know the correct group if there is any.
> I am trying to query the Indexing Engine. Added a linked server
> "FileSystem" and then created a view.
> View definition is given below:
> CREATE VIEW FileView
> AS
> SELECT * FROM OPENQUERY(FileSystem, 'SELECT FileName,
> Characterization FROM SCOPE() ')
>
> Any query to this view takes an infinite time. The query never stops.
> Even "Count(*)" never returns. SQL profiler (when used in conjunction
> with a long query containing join on this view) shows the query going
> into an infinite recursion.
>
> Any pointers to solve this issue would be very helpful.
>
>
> Cheers,
> Gaurav Vaish
> http://mastergaurav.org
> http://mastergaurav.blogspot.com
> --
>
|||Yogish/Uri:
SCOPE() defines the scope on the file system. The query is, as I
said, related to Indexing Engine. The query is executed on the Indexing
Engine in the defined catalog.
SCOPE() defines all directories and subdirectories and files.
SCOPE can be something like SCOPE("D:\") and it will search for files
in only D-drive.
You may want to have a look at:
http://msdn.microsoft.com/library/de...filedatats.asp
Uri:
I'm refering to the query to the catalog in indexing engine. No
table/function here as in direct SQL server.
Cheers,
Gaurav Vaish
http://mastergaurav.org
http://mastergaurav.blogspot.com

Infinite time to query

I couldn't find any newsgroup for MS Indexing engine.. so, I am posting
it here.
Please let me know the correct group if there is any.
I am trying to query the Indexing Engine. Added a linked server
"FileSystem" and then created a view.
View definition is given below:
CREATE VIEW FileView
AS
SELECT * FROM OPENQUERY(FileSystem, 'SELECT FileName,
Characterization FROM SCOPE() ')
Any query to this view takes an infinite time. The query never stops.
Even "Count(*)" never returns. SQL profiler (when used in conjunction
with a long query containing join on this view) shows the query going
into an infinite recursion.
Any pointers to solve this issue would be very helpful.
Cheers,
Gaurav Vaish
http://mastergaurav.org
http://mastergaurav.blogspot.com
--Hi,
Does the simple SELECT using four part naming convention work?
For Ex:
SELECT * FROM <linked_server_name>.<DB_Name>.<User>.<Table_Name>
Btw, What Is SCOPE()?
--
- - - - - - - - -
Thanks
Yogish
"MasterGaurav" wrote:

> I couldn't find any newsgroup for MS Indexing engine.. so, I am posting
> it here.
> Please let me know the correct group if there is any.
> I am trying to query the Indexing Engine. Added a linked server
> "FileSystem" and then created a view.
> View definition is given below:
> CREATE VIEW FileView
> AS
> SELECT * FROM OPENQUERY(FileSystem, 'SELECT FileName,
> Characterization FROM SCOPE() ')
>
> Any query to this view takes an infinite time. The query never stops.
> Even "Count(*)" never returns. SQL profiler (when used in conjunction
> with a long query containing join on this view) shows the query going
> into an infinite recursion.
>
> Any pointers to solve this issue would be very helpful.
>
>
> Cheers,
> Gaurav Vaish
> http://mastergaurav.org
> http://mastergaurav.blogspot.com
> --
>|||MasterGaurav
What is SCOPE() ? Is that function?
SELECT <column list> FROM FileSystem.Database.dbo.Table/Function
"MasterGaurav" <gaurav.vaish@.gmail.com> wrote in message
news:1120729619.181576.238440@.g47g2000cwa.googlegroups.com...
> I couldn't find any newsgroup for MS Indexing engine.. so, I am posting
> it here.
> Please let me know the correct group if there is any.
> I am trying to query the Indexing Engine. Added a linked server
> "FileSystem" and then created a view.
> View definition is given below:
> CREATE VIEW FileView
> AS
> SELECT * FROM OPENQUERY(FileSystem, 'SELECT FileName,
> Characterization FROM SCOPE() ')
>
> Any query to this view takes an infinite time. The query never stops.
> Even "Count(*)" never returns. SQL profiler (when used in conjunction
> with a long query containing join on this view) shows the query going
> into an infinite recursion.
>
> Any pointers to solve this issue would be very helpful.
>
>
> Cheers,
> Gaurav Vaish
> http://mastergaurav.org
> http://mastergaurav.blogspot.com
> --
>|||Yogish/Uri:
SCOPE() defines the scope on the file system. The query is, as I
said, related to Indexing Engine. The query is executed on the Indexing
Engine in the defined catalog.
SCOPE() defines all directories and subdirectories and files.
SCOPE can be something like SCOPE("D:\") and it will search for files
in only D-drive.
You may want to have a look at:
http://msdn.microsoft.com/library/d...r />
atats.asp
Uri:
I'm refering to the query to the catalog in indexing engine. No
table/function here as in direct SQL server.
Cheers,
Gaurav Vaish
http://mastergaurav.org
http://mastergaurav.blogspot.com
--

Infinite time to query

I couldn't find any newsgroup for MS Indexing engine.. so, I am posting
it here.
Please let me know the correct group if there is any.
I am trying to query the Indexing Engine. Added a linked server
"FileSystem" and then created a view.
View definition is given below:
CREATE VIEW FileView
AS
SELECT * FROM OPENQUERY(FileSystem, 'SELECT FileName,
Characterization FROM SCOPE() ')
Any query to this view takes an infinite time. The query never stops.
Even "Count(*)" never returns. SQL profiler (when used in conjunction
with a long query containing join on this view) shows the query going
into an infinite recursion.
Any pointers to solve this issue would be very helpful.
Cheers,
Gaurav Vaish
http://mastergaurav.org
http://mastergaurav.blogspot.com
--Hi,
Does the simple SELECT using four part naming convention work?
For Ex:
SELECT * FROM <linked_server_name>.<DB_Name>.<User>.<Table_Name>
Btw, What Is SCOPE()?
--
- - - - - - - - -
Thanks
Yogish
"MasterGaurav" wrote:
> I couldn't find any newsgroup for MS Indexing engine.. so, I am posting
> it here.
> Please let me know the correct group if there is any.
> I am trying to query the Indexing Engine. Added a linked server
> "FileSystem" and then created a view.
> View definition is given below:
> CREATE VIEW FileView
> AS
> SELECT * FROM OPENQUERY(FileSystem, 'SELECT FileName,
> Characterization FROM SCOPE() ')
>
> Any query to this view takes an infinite time. The query never stops.
> Even "Count(*)" never returns. SQL profiler (when used in conjunction
> with a long query containing join on this view) shows the query going
> into an infinite recursion.
>
> Any pointers to solve this issue would be very helpful.
>
>
> Cheers,
> Gaurav Vaish
> http://mastergaurav.org
> http://mastergaurav.blogspot.com
> --
>|||MasterGaurav
What is SCOPE() ? Is that function?
SELECT <column list> FROM FileSystem.Database.dbo.Table/Function
"MasterGaurav" <gaurav.vaish@.gmail.com> wrote in message
news:1120729619.181576.238440@.g47g2000cwa.googlegroups.com...
> I couldn't find any newsgroup for MS Indexing engine.. so, I am posting
> it here.
> Please let me know the correct group if there is any.
> I am trying to query the Indexing Engine. Added a linked server
> "FileSystem" and then created a view.
> View definition is given below:
> CREATE VIEW FileView
> AS
> SELECT * FROM OPENQUERY(FileSystem, 'SELECT FileName,
> Characterization FROM SCOPE() ')
>
> Any query to this view takes an infinite time. The query never stops.
> Even "Count(*)" never returns. SQL profiler (when used in conjunction
> with a long query containing join on this view) shows the query going
> into an infinite recursion.
>
> Any pointers to solve this issue would be very helpful.
>
>
> Cheers,
> Gaurav Vaish
> http://mastergaurav.org
> http://mastergaurav.blogspot.com
> --
>|||Yogish/Uri:
SCOPE() defines the scope on the file system. The query is, as I
said, related to Indexing Engine. The query is executed on the Indexing
Engine in the defined catalog.
SCOPE() defines all directories and subdirectories and files.
SCOPE can be something like SCOPE("D:\") and it will search for files
in only D-drive.
You may want to have a look at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/filedatats.asp
Uri:
I'm refering to the query to the catalog in indexing engine. No
table/function here as in direct SQL server.
Cheers,
Gaurav Vaish
http://mastergaurav.org
http://mastergaurav.blogspot.com
--

Indexing etiquette

I'm using Idera's SQL Diagnostic Manager and its showing me my index is
using the File Group "Primary" (which I recently read is bad) and I
have 3 index levels with some Data values having in excess of 700K
rows.
Is this bad and should I be worried? Is there some housekeeping I
should do in these situations?
TIA
Robrcamarda (rcamarda@.cablespeed.com) writes:
> I'm using Idera's SQL Diagnostic Manager and its showing me my index is
> using the File Group "Primary" (which I recently read is bad)

There are situations where you can split up databases on several
file groups, and for instance have non-clustered index on a separate
volume. Note that if you relocate the clustered index, you relocate
the data as well.

But this should only be done if you have a clear understand of what you
win. None of our customer's databases have more than the two files
each database is born with. (And thus only one file group.)

> and I have 3 index levels with some Data values having in excess of 700K
> rows.
> Is this bad and should I be worried? Is there some housekeeping I
> should do in these situations?

It's a good idea to run DBCC DBREINDEX on your tables, if they tend
to fragment. Whether they fragment, can be concluded by using
DBCC SHOWCONTIG.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In books online there is an example which checks for fragmentation
above a level of 30% and runs the reindex function. Is this a good
number ot use or is it one of those "depends" kind of things?|||pb648174 (google@.webpaul.net) writes:
> In books online there is an example which checks for fragmentation
> above a level of 30% and runs the reindex function. Is this a good
> number ot use or is it one of those "depends" kind of things?

It's not a bad number. We ship our maintenance job that uses the output
from DBCC SHOWCONTIG, and if a table is fragmented enough, we run DBCC
DBREINDEX. And the bar where we reindex is, as far as I recall, precisely
30%...

What we have adding recently, and me and our admin-kind-of-guy has not
really arrived on the best strategy for, is to run UPDATE STASTISTICS
WITH FULLSCAN on table we don't reindex. Table that don't get defragmented
despite heavy insertion traffic, probably has a monotonic clustered
index, so statistics will be inaccurate after a while.

Then as always there are cases where you may want to deviate. For instance,
clustered index an guids is often said to be recipe for quick fragmentation.
However, SQL Server MVP Greg Linwood pointed out to me, that this can
be used to your advantage. You define the index with a relatively low
fill factor, say 50%. What will happen now is that insertion will happen
all over the place, but page splits will be rare, since all pages have
room to spare. So with design, framgmenation actually decreases as time
goes. Up to a certain point that is, once you are starting to fill up
more and more pages, page split will rage here and there. The idea is
that you monitor the state of the database closely, and that you have a
maintenance window where you again can reindex to 50%.

It goes without saying that this strategy is nothing for the left-hand
DBA, but requires thorough understanding and most of all, daily
monitoring of the state of the database.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Do you have an example of that maintenance job somewhere? We don't look
at the database daily and have many installations so it needs to be
scripted and run on its own without any supervision or intervention.

I'm right handed and not a DBA...

Indexes on separate Filegroups

Two questions. Let's suppose I have two file groups, Primary and Datagroup.
The Primary group is mirrored and has my system tables and transaction log
(Drive letter "D"). My tables and indexes are in the Datagroup on a separate
RAID 5 disk configuration (Drive letter "E"). Let us suppose I have available
a third RAID 5 disk configuration (Drive letter "F").
1. I just wanted to verify a thought: If all my tables have clustered indexes,
and since clustered indexes reside on the data pages themselves, then it
would NOT make sense to put my clustered indexes on a separate file or disk
from my data file. True or False? My guess is that the answer is true. Please
verify.
2. In the case of non-clustered indexes (where all the corresponding tables
have clustered indexes), there could possibly be a performance gain by
placing the non-clustered indexes on a separate file (say, Drive letter "F")
from my data. True or False? My guess is that the answer is true. Please
verify.
Message posted via http://www.droptable.com
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:5e60b49711dd2@.uwe...
> Two questions. Let's suppose I have two file groups, Primary and
> Datagroup.
> The Primary group is mirrored and has my system tables and transaction log
> (Drive letter "D"). My tables and indexes are in the Datagroup on a
> separate
> RAID 5 disk configuration (Drive letter "E"). Let us suppose I have
> available
> a third RAID 5 disk configuration (Drive letter "F").
> 1. I just wanted to verify a thought: If all my tables have clustered
> indexes,
> and since clustered indexes reside on the data pages themselves, then it
> would NOT make sense to put my clustered indexes on a separate file or
> disk
> from my data file. True or False? My guess is that the answer is true.
> Please
> verify.
>
True. That would just move the tables to the other filegroup.

> 2. In the case of non-clustered indexes (where all the corresponding
> tables
> have clustered indexes), there could possibly be a performance gain by
> placing the non-clustered indexes on a separate file (say, Drive letter
> "F")
> from my data. True or False? My guess is that the answer is true. Please
> verify.
False. Seperating tables and indexes rarely helps, and is essentially an
obsolete micro-optimization of the physical database design. The large
memory size of modern servers makes physical IO on your non-clustered
indexes rare and unpredictable enough that you shouldn't dedicate a physical
IO channel to your indexes. A better idea would be to spread all your
objects across both disks by adding additional files to your data filegroup,
or monitor your physical IO and move objects to the other disk to roughly
balance the traffic.
David

Indexes on separate Filegroups

Two questions. Let's suppose I have two file groups, Primary and Datagroup.
The Primary group is mirrored and has my system tables and transaction log
(Drive letter "D"). My tables and indexes are in the Datagroup on a separate
RAID 5 disk configuration (Drive letter "E"). Let us suppose I have availabl
e
a third RAID 5 disk configuration (Drive letter "F").
1. I just wanted to verify a thought: If all my tables have clustered indexe
s,
and since clustered indexes reside on the data pages themselves, then it
would NOT make sense to put my clustered indexes on a separate file or disk
from my data file. True or False? My guess is that the answer is true. Pleas
e
verify.
2. In the case of non-clustered indexes (where all the corresponding tables
have clustered indexes), there could possibly be a performance gain by
placing the non-clustered indexes on a separate file (say, Drive letter "F")
from my data. True or False? My guess is that the answer is true. Please
verify.
Message posted via http://www.droptable.com"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:5e60b49711dd2@.uwe...
> Two questions. Let's suppose I have two file groups, Primary and
> Datagroup.
> The Primary group is mirrored and has my system tables and transaction log
> (Drive letter "D"). My tables and indexes are in the Datagroup on a
> separate
> RAID 5 disk configuration (Drive letter "E"). Let us suppose I have
> available
> a third RAID 5 disk configuration (Drive letter "F").
> 1. I just wanted to verify a thought: If all my tables have clustered
> indexes,
> and since clustered indexes reside on the data pages themselves, then it
> would NOT make sense to put my clustered indexes on a separate file or
> disk
> from my data file. True or False? My guess is that the answer is true.
> Please
> verify.
>
True. That would just move the tables to the other filegroup.

> 2. In the case of non-clustered indexes (where all the corresponding
> tables
> have clustered indexes), there could possibly be a performance gain by
> placing the non-clustered indexes on a separate file (say, Drive letter
> "F")
> from my data. True or False? My guess is that the answer is true. Please
> verify.
False. Seperating tables and indexes rarely helps, and is essentially an
obsolete micro-optimization of the physical database design. The large
memory size of modern servers makes physical IO on your non-clustered
indexes rare and unpredictable enough that you shouldn't dedicate a physical
IO channel to your indexes. A better idea would be to spread all your
objects across both disks by adding additional files to your data filegroup,
or monitor your physical IO and move objects to the other disk to roughly
balance the traffic.
David

Indexes on separate Filegroups

Two questions. Let's suppose I have two file groups, Primary and Datagroup.
The Primary group is mirrored and has my system tables and transaction log
(Drive letter "D"). My tables and indexes are in the Datagroup on a separate
RAID 5 disk configuration (Drive letter "E"). Let us suppose I have available
a third RAID 5 disk configuration (Drive letter "F").
1. I just wanted to verify a thought: If all my tables have clustered indexes,
and since clustered indexes reside on the data pages themselves, then it
would NOT make sense to put my clustered indexes on a separate file or disk
from my data file. True or False? My guess is that the answer is true. Please
verify.
2. In the case of non-clustered indexes (where all the corresponding tables
have clustered indexes), there could possibly be a performance gain by
placing the non-clustered indexes on a separate file (say, Drive letter "F")
from my data. True or False? My guess is that the answer is true. Please
verify.
--
Message posted via http://www.sqlmonster.com"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:5e60b49711dd2@.uwe...
> Two questions. Let's suppose I have two file groups, Primary and
> Datagroup.
> The Primary group is mirrored and has my system tables and transaction log
> (Drive letter "D"). My tables and indexes are in the Datagroup on a
> separate
> RAID 5 disk configuration (Drive letter "E"). Let us suppose I have
> available
> a third RAID 5 disk configuration (Drive letter "F").
> 1. I just wanted to verify a thought: If all my tables have clustered
> indexes,
> and since clustered indexes reside on the data pages themselves, then it
> would NOT make sense to put my clustered indexes on a separate file or
> disk
> from my data file. True or False? My guess is that the answer is true.
> Please
> verify.
>
True. That would just move the tables to the other filegroup.
> 2. In the case of non-clustered indexes (where all the corresponding
> tables
> have clustered indexes), there could possibly be a performance gain by
> placing the non-clustered indexes on a separate file (say, Drive letter
> "F")
> from my data. True or False? My guess is that the answer is true. Please
> verify.
False. Seperating tables and indexes rarely helps, and is essentially an
obsolete micro-optimization of the physical database design. The large
memory size of modern servers makes physical IO on your non-clustered
indexes rare and unpredictable enough that you shouldn't dedicate a physical
IO channel to your indexes. A better idea would be to spread all your
objects across both disks by adding additional files to your data filegroup,
or monitor your physical IO and move objects to the other disk to roughly
balance the traffic.
David

Indexes and File groups

Something strange.

I have a database(SQL2000) with two file group(on seperate physical
drives).
One is meant for table data[PRIMARY] and one for indexes [INDEX].

So i create a table on the [PRIMARY] file group, and fill in
data.

Next I build a clustered index on the table, on the [INDEX] filegroup.

Once the index is built, the database now indicates that the filegroup
for the table [INDEX]! and not [PRIMARY] as i originally set it up for!

My question it then: Has the table been moved or is this somehow an
error in SQL server?
I would really appreciate any thought anyone might have on this?Jens

A clustered index is the table (Well not quiet, but close enough). It
is impossible to have a clustered index on a different filegroup from
the data. You can build non-clustered on a seperate filegroup.

I suggest you rebuild your clustered index on your primary filegroup.
Regards

John|||Aha! Solved some mysteries for me :-). Thank you very much.
I guess i didnt quite understand how clustered indexes worked.

Actually i have a bunch of tables with clustered indexes which
currently reside my file group for indexes. The good news is,if I
understand you correctly,
the if I simply rebuild the clustered index on my data file group
the table data will be moved back.|||Jens

Yes, rebuilding the clustered index will move the table. You can also
do it through enterprise manager, using design table, this rebuilds the
index for you.

Regards

John|||Im planning to recreate the clustered index like this:

CREATE
CLUSTERED INDEX [idx-clusteredindex]
ON
[dbo].[TABLE_NAME]([COLOUMN_NANE])
WITH
DROP_EXISTING,
FILLFACTOR = 90
ON
[PRIMARY]

As I understand this will alse cause all non-clustered index
on the table to be rebuilt/recalculated as well.
Is this infact the case of do I have to
do i have to do it explicitly afterwards like:

DBCC DBREINDEX ([dbo].[TABLE_NAME],[idx-nonclustered],90)

johnbandettini@.yahoo.co.uk wrote:
> Jens
> Yes, rebuilding the clustered index will move the table. You can also
> do it through enterprise manager, using design table, this rebuilds
the
> index for you.
>
> Regards
>
> John

Indexed Views - Group By Redundancy

I have a table that I want to have a precalulcate length on a character field
and group and sum up. Thought I could do this by creating a view with a group
by clause that includes the sum function. Unfortunately, the compiler
complains with:

A clustered index cannot be created on the view 'MyView' because the index
key includes columns which are not in the GROUP BY clause.

Wish I could verbalize the problem a little better, but the following pared
down example should serve as a demonstration:

SET ANSI_WARNINGS ON
SET ANSI_PADDING ON
SET ANSI_NULLS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO

CREATE TABLE myTable(
myID INT NOT NULL,
RecNum INT NOT NULL,
TestString VARCHAR(80) NOT NULL)
GO

INSERT INTO myTable VALUES(1, 1, 'a')
INSERT INTO myTable VALUES(1, 2, 'ab')
INSERT INTO myTable VALUES(2, 2, 'abc')
GO

CREATE VIEW dbo.MyView WITH SCHEMABINDING AS
SELECT
myID = myID,
slen = SUM(LEN(TestString)),
recn = COUNT_BIG(*)
FROM dbo.myTable
GROUP BY myID
GO

CREATE UNIQUE CLUSTERED INDEX IX_MyView ON MyView(myID, slen)
-- A clustered index cannot be created on the view 'MyView' because
-- the index key includes columns which are not in the GROUP BY clause.
GO

DROP VIEW MyView
GO

DROP TABLE myTable
GO

Thanks,
Chris Rathman[posted and mailed, please reply in news]

ChrisRath (chrisrath@.aol.com) writes:
> Wish I could verbalize the problem a little better, but the following
> pared down example should serve as a demonstration:

Very good repro, thanks.

As for your problem, I think the cure is simple. Just change:

> CREATE UNIQUE CLUSTERED INDEX IX_MyView ON MyView(myID, slen)

to

> CREATE UNIQUE CLUSTERED INDEX IX_MyView ON MyView(myID)

Alas, this rewards with a new message:

Server: Msg 8662, Level 16, State 1, Line 1
An index cannot be created on the view 'MyView' because the view
definition includes an unknown value (the sum of a nullable expression).

A little funny, since the expression is not nullable. But alright, change
the definition to:

CREATE VIEW dbo.MyView WITH SCHEMABINDING AS
SELECT
myID = myID,
slen = SUM(coalesce(LEN(TestString), 0),
recn = COUNT_BIG(*)
FROM dbo.myTable
GROUP BY myID
GO

But the message is the same.

At this point I will have to admit defeat, but I ask around with my
MVP colleauages and Microsoft contacts, to find out if this is a bug
or by design (with an improper error message).

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||[posted and mailed, please reply in news]

ChrisRath (chrisrath@.aol.com) writes:
> I have a table that I want to have a precalulcate length on a character
> field and group and sum up. Thought I could do this by creating a view
> with a group by clause that includes the sum function.

I have yet to get answer if the error message I got was by design or due
to a bug. However, my MVP colleage Steve Kass offered a working solution,
although it requires an auxillary table:

SET ANSI_WARNINGS ON
SET ANSI_PADDING ON
SET ANSI_NULLS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO

CREATE TABLE myTable(
myID INT NOT NULL,
RecNum INT NOT NULL,
TestString VARCHAR(80) NOT NULL,
blafs int NOT NULL)
GO
SELECT TOP 8000 Number = IDENTITY(int, 1, 1)
INTO Numbers
FROM pubs..authors t1, pubs..authors t2, pubs..authors t3
go
INSERT INTO myTable VALUES(1, 1, 'a', 9)
INSERT INTO myTable VALUES(1, 2, 'ab', 9)
INSERT INTO myTable VALUES(2, 2, 'abc', 12)
GO

CREATE VIEW dbo.MyView WITH SCHEMABINDING AS
SELECT
myID = m.myID,
slen = SUM(N.Number),
recn = COUNT_BIG(*)
FROM dbo.myTable m
JOIN dbo.Numbers N ON N.Number = LEN(m.TestString)
GROUP BY m.myID
GO

CREATE UNIQUE CLUSTERED INDEX IX_MyView ON MyView(myID)
-- Server: Msg 8662, Level 16, State 1, Line 1
-- An index cannot be created on the view 'MyView' because the view
-- definition includes an unknown value (the sum of a nullable
-- expression).
GO

DROP VIEW MyView
GO
DROP TABLE myTable
GO
DROP TABLE Numbers
GO

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I've got a pivot table I use for other purposes that will come in handy. Seems
to compile ok. Will run some performance tests on the target to see if I get
the desired boost.

Thanks for the help. And thank your colleague for me for providing a creative
solution. :-)

> CREATE VIEW dbo.MyView WITH SCHEMABINDING AS
> SELECT
> myID = m.myID,
> slen = SUM(N.Number),
> recn = COUNT_BIG(*)
> FROM dbo.myTable m
> JOIN dbo.Numbers N ON N.Number = LEN(m.TestString)
> GROUP BY m.myID
> GO