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

Indx newbie: please help

Consider the following

--------------------
ASSIGNMENT
- Index : Integer, Unique, NotNull, Primary Key
- Start : DateTime, , NotNull,
- End : DateTime, , ,
- Other : ...

TASK
- Assignment : Integer, Unique, NotNull, Primary Foreign Key
- SubIndex : Integer, , NotNull, Primary Key
- Type : Char(3), , NotNull, Foreign Key
- Start : DateTime, , NotNull,
- End : DateTime, , ,
- Other : ...

TASK_TYPE
- Code : Char(3), Unique, NotNull, Primary Key
- Description : VarChar(20), , NotNull,
--------------------

As u can c the idea is simple...
...an assignment has an INDEX as PK, a start date, an end date and other fields;
...an assignment can have one or more tasks; the relationship is 1:N and is identifying (see next point)
...a task has a SUB INDEX inside the assignment; that is the PK is the assigment it belongs to (also a FK) and an index for that assignment
...a task has also a TYPE, which is a FK to the TASK_TYPE table

Consider also that...
...ASSIGNMENT contains > 1 millions rows
...TASK contains < 10 rows for each assignment (so an average of 5 millions rows)
...TASK_TYPE contains < 10 rows

As far as I know SQL-server creates a CLUSTERED INDEX for any PK, that is
ASSIGNMENT (Index)
TASK(Assigment, SubIndex)
TASK_TYPE(Code)

Do I have to add any other NON CLUSTERED INDEX? I would say I should add the following:
TASK(Assignment)
TASK(Type)

But of course TASK(Assignment) is already part of the CLUSTERED INDEX ASSIGNMENT(Assignment, SubIndex), so I shouldn't add it, right?
What about TASK(Type)?

Or maybe there's a completely different solution?

My main problem is due to the fact that TASK has a composite PK wher one field is also a FK.

Any advice is welcome.

thanks a lot :-)what, exactly, is your main problem? you didn't say what it was :)

i don't think you need any additional indexes, since TASK_TYPE should always be handled in memory (but then, i am not a DBA, eh ;))

by the way, it's not a good idea to name a column "index" as this is a reserved word in a lot of languages|||I'd simply like to know how where to put indexes :)|||okay, put an index on task.type

;)|||I would put the index in the database. Someone might need it, and it would be very embarrassing, if you left it at home.

Joking aside, is there a particular query that is giving problems? Remember that excessive indexing can hurt data modification processes.

Indexing datetime field for selecting dates ranges

Hi,
I have a table with a smalldatetime field. Some of the queries in my
application are using range searches over that smalldatetime field, such as
selecting all the records within a date range. None of these queries are
selecting records from a specific date/time. They all work on ranges (e.g.
using BETWEEN or operators like >=).
Is there any reason for indexing the smalldatetime field? Could that make
the queries run faster?
Regards,
Amir.Yes, indexes on those columns can be beneficial, just as indexes on any colu
mn. You need to make
sure that your query is written in a way so that those indexes can be used
(http://www.karaszi.com/SQLServer/info_datetime.asp), of course. And whether
the indexes then *will*
be used is dependent on a lot of factors (the query, the data, selectivity e
tc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Amir" <agamy@.actcom.co.il> wrote in message news:OCbLcq9FGHA.1032@.TK2MSFTNGP15.phx.gbl...[
color=darkred]
> Hi,
> I have a table with a smalldatetime field. Some of the queries in my appli
cation are using range
> searches over that smalldatetime field, such as selecting all the records
within a date range.
> None of these queries are selecting records from a specific date/time. The
y all work on ranges
> (e.g. using BETWEEN or operators like >=).
> Is there any reason for indexing the smalldatetime field? Could that make
the queries run faster?
> Regards,
> Amir.
>[/color]|||Thanks for the explanation!
Kind Regards,
Amir.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uOlH7mDGGHA.1424@.TK2MSFTNGP12.phx.gbl...
> Yes, indexes on those columns can be beneficial, just as indexes on any
> column. You need to make sure that your query is written in a way so that
> those indexes can be used
> (http://www.karaszi.com/SQLServer/info_datetime.asp), of course. And
> whether the indexes then *will* be used is dependent on a lot of factors
> (the query, the data, selectivity etc).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Amir" <agamy@.actcom.co.il> wrote in message
> news:OCbLcq9FGHA.1032@.TK2MSFTNGP15.phx.gbl...
>