INFORMATION_SCHEMA.ROUTINES LAST_ALTERED not changing

Why is the LAST_ALTERED datetime column in INFORMATION_SCHEMA.ROUTINES NOT
changing when I perform a ALTER PROCEDURE statement?
When would it change?
tia,
ChrisIt's not fully implemented yet in sql2k. Expect to see such audit in sql2k5.
-oj
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:6E5F64BE-3373-4107-99C7-A54D48FA026D@.microsoft.com...
> Why is the LAST_ALTERED datetime column in INFORMATION_SCHEMA.ROUTINES
> NOT
> changing when I perform a ALTER PROCEDURE statement?
> When would it change?
> tia,
> Chris

INFORMATION_SCHEMA.ROUTINES LAST_ALTERED not changing

Why is the LAST_ALTERED datetime column in INFORMATION_SCHEMA.ROUTINES NOT
changing when I perform a ALTER PROCEDURE statement?
When would it change?
tia,
ChrisIt's not fully implemented yet in sql2k. Expect to see such audit in sql2k5.
--
-oj
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:6E5F64BE-3373-4107-99C7-A54D48FA026D@.microsoft.com...
> Why is the LAST_ALTERED datetime column in INFORMATION_SCHEMA.ROUTINES
> NOT
> changing when I perform a ALTER PROCEDURE statement?
> When would it change?
> tia,
> Chrissql

INFORMATION_SCHEMA.ROUTINES LAST_ALTERED not changing

Why is the LAST_ALTERED datetime column in INFORMATION_SCHEMA.ROUTINES NOT
changing when I perform a ALTER PROCEDURE statement?
When would it change?
tia,
Chris
It's not fully implemented yet in sql2k. Expect to see such audit in sql2k5.
-oj
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:6E5F64BE-3373-4107-99C7-A54D48FA026D@.microsoft.com...
> Why is the LAST_ALTERED datetime column in INFORMATION_SCHEMA.ROUTINES
> NOT
> changing when I perform a ALTER PROCEDURE statement?
> When would it change?
> tia,
> Chris

Indexing Columns

If you have a table with 3 columns,

ID (Primary Key)

Col1

Col2

And you have to perform the following query frequently

Code Snippet

Select ID where Col1='SomeValue' and Col2='SomeOtherValue'

Is it a bad idea to define a non clustered index on "Col1, Col2, ID" or am I better off just having the default indexing on the the primary key "ID"

I have never had to define an index that included all the columns in a table before so I am not sure if this is a bad idea

If you are using SQL 2005, and this is a frequent or common query, you may wish to explore using the new 'INCLUDE' option.

You could create an INDEX on Col1, Col2, and include [ID].

Something like this:

CREATE NONCLUSTERED INDEX ix_MyTable_Col1Col2
ON MySchema.MyTable( Col1, Col2 )
INCLUDE ( [ID] );

This is a 'covered' index. The entire query is satisfied by the index.

|||Thanks Arnie.... I have to support both SQL 2005 and SQL 2000 for this application at the moment.

|||

For SQL 2000, if you use this query frequently, index all three columns.

|||

you can not define ID as non clustered since it is a PK.

ID can be clustered index and check the unique checkbox.

you could define col1 and col2 as non clustered and ID as included column but beware of space used by index.

|||

You should create clustered primary indexes based off the 80/20 rule. If you are accessing this table 80% of the time by col1 and col2, then create a clustered primary index over col1, col2, ID. Creating an index over just the ID column will almost always cause bookup lookups. Create primary key indexes based off of usage, not how fast can I load data.

|||Chances are that your ID column is part of the automatically created clustered index since it's the primary key.

If that's the case, remember that all columns in the clustered index are appended to all non-clustered indexes for that table.

So there is no reason to add ID to your non-clustered index since it will be there already.

I think that SQL Server is smart enough to just ignore the ID column in the index definition since it knows that it's part of the clustered index, but I'm not sure on that one.

Having all three columns part of an index (ID in the clustered, and Col1 and Col2 in the non-clustered) creates, as somebody else mentioned, a "covering" index.

Basically, a covering index is an index which includes all references columns in your query (from the SELECT, JOIN, and WHERE clauses) so that no bookmark loops are necessary to return all the data. This data can come entirely from indexes... which is much faster than having to go read additional data pages to snag the original row from the table.

|||<P align=left><FONT face=Arial size=2>Hi,</FONT></P>
<P align=left>&nbsp;</P>
<P align=left>&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;We could arrive at a decision of using the index on&nbsp;columns based on the recommendation of the SQL Profiler utility. The input for the profiler would be a database&nbsp;trace file. This trace file will capture the usage of the table by the users and using this profiler will decide whether to use index. Also in this scenario, the table has only 3 columns and all the three columns are accessed by the user frequently. So the choice would be going for the covering index where all the three columns will be covered under index.</P>
<P align=left>&nbsp;</P>
<P align=left>Thanks.</P>

Indexed Views Vs temp tables

With my understanding of indexed views and according to books I read
"indexed views" are supposed to perform much better than "temp tables"
(temp table having primary key and indexed view with clustered index
on the same keys).

But when I tried in my system I am getting opposite results. With
Indexed Views it takes 3 times more time.

Any body has any reasons for that? Or my understanding was wrong?

thanks
Raghu AvirneniIndexed views are expensive when adding data, since adding to the base table
also has to update the view's indexes as well as the base table.

It only helps on the retrieve (and only if the index on the view is used in
the query plan).

I have only used them for lookup tables that rarely change.

"Avirneni" <ravirneni@.trafficmp.com> wrote in message
news:15a48475.0410061606.24dd220d@.posting.google.c om...
> With my understanding of indexed views and according to books I read
> "indexed views" are supposed to perform much better than "temp tables"
> (temp table having primary key and indexed view with clustered index
> on the same keys).
> But when I tried in my system I am getting opposite results. With
> Indexed Views it takes 3 times more time.
> Any body has any reasons for that? Or my understanding was wrong?
>
> thanks
> Raghu Avirneni|||Temp tables and views are different animals. Like David mentioned, examine
the query plan to ensure the index on the view is actually being used.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Avirneni" <ravirneni@.trafficmp.com> wrote in message
news:15a48475.0410061606.24dd220d@.posting.google.c om...
> With my understanding of indexed views and according to books I read
> "indexed views" are supposed to perform much better than "temp tables"
> (temp table having primary key and indexed view with clustered index
> on the same keys).
> But when I tried in my system I am getting opposite results. With
> Indexed Views it takes 3 times more time.
> Any body has any reasons for that? Or my understanding was wrong?
>
> thanks
> Raghu Avirneni