Is this index redundant?

Let's say I have a table:
create table example (
id int,
createtime datetime,
name varchar(20))
go
create index ix_createtime on example(createtime)
go
create index ix_createtime_name on example(createtime, name)
go
Is the index ix_createtime redundant because the createtime column is
part of the composite index ix_createtime_name. If someone whee to
seach based only on the createtime column would the composite index be
as useful as the index on just the createtime column?
Thanks<pshroads@.gmail.com> wrote in message
news:1148056676.401254.153400@.y43g2000cwc.googlegroups.com...
> Is the index ix_createtime redundant because the createtime column is
> part of the composite index ix_createtime_name. If someone whee to
> seach based only on the createtime column would the composite index be
> as useful as the index on just the createtime column?
Yes, it probably is redundant. There are a few very limited edge cases
where it might not be -- for instance, if you're doing a lot of analysis
based on only a single column and every I/O counts -- but they are very few
and very far between. The composite index will certainly work for a query
based only on the createtime column.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Index ix_createtime is leaner than index ix_createtime_name. As such,
it is slightly more useful. However, its advantage is not too
noticeable, and under most circumstances you can safely drop it. you
can figure it out yourself, run your own tests, such as
select * from example where createtime between '20060101' and
'20060202'
there might be ranges for which access via ix_createtime is still
cheaper than scanning the whole clustered index, but access via
ix_createtime_name is already more expensive than table scan.
Good luck!|||Unless the table is read-only, you also need to consider maintenance costs.
With the redundant index, you will have extra work to do for almost every
modification to the table, and you'll need to determine if that cost is
worth the small benefit of having the narrower index for some queries.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<pshroads@.gmail.com> wrote in message
news:1148056676.401254.153400@.y43g2000cwc.googlegroups.com...
> Let's say I have a table:
> create table example (
> id int,
> createtime datetime,
> name varchar(20))
> go
> create index ix_createtime on example(createtime)
> go
> create index ix_createtime_name on example(createtime, name)
> go
> Is the index ix_createtime redundant because the createtime column is
> part of the composite index ix_createtime_name. If someone whee to
> seach based only on the createtime column would the composite index be
> as useful as the index on just the createtime column?
> Thanks
>|||Kalen,
I agree maintenance costs need to be taken in account. Yet I don't see
why you are calling the benefit of having a lean index "small" without
knowing much about the OP's system. I think it depends on the workload.
Suppose there is a query that runs very frequently. As such, small 10%
savings in its real execution costs may result in, say, 6% reduction of
overall workload for the whole system...|||Thanks. Am I correct in thinking that it's only redundant because
statistics are maintained on only the first column of an index? So if
the composite index had been on (name, createtime) then it would not be
redundant because the optimizer would be unlikely to choose that index
when searching on createtime because there would be no statistics?|||How will said "lean" index be used? I don't think dropping 20 bytes from an
index like this will result in "10% savings" in 99% of the cases, because
most likely a query will involve more than just the datetime column. Keep
in mind that bookmark lookups are much more expensive, in most cases, than
the additional I/Os that would be incurred reading the larger index pages.
We of course don't know what the clustering key is, or any other information
about the use, so this is all speculation, but unless this is a very
specialized situation I'd venture a guess that having both indexes is not
beneficial.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1148061719.429023.48180@.i40g2000cwc.googlegroups.com...
> Kalen,
> I agree maintenance costs need to be taken in account. Yet I don't see
> why you are calling the benefit of having a lean index "small" without
> knowing much about the OP's system. I think it depends on the workload.
> Suppose there is a query that runs very frequently. As such, small 10%
> savings in its real execution costs may result in, say, 6% reduction of
> overall workload for the whole system...
>|||well I'm not speaking in terms of 99% of the cases or 99.999% of the
case or whatever else. I am speaking about one particular system we
know almost nothing about - the OP's system. Suppose that system is
very simple and the primary goal of that simple system is to run
something like this:
select count(distinct id) from example where createtime between
@.fromdate and @.todate
as fast as possible. In that case the savings would be substantial, as
there would be no bookmark lookups. However rare, such cases do exist.
That's why I was trying to encourage the original poster to benchmark
by himself rather than rely on our advices. I think in our trade all
rules have exceptions.
That's all I was trying to say...|||On 19 May 2006 09:37:56 -0700, pshroads@.gmail.com wrote:

>Let's say I have a table:
>create table example (
> id int,
> createtime datetime,
> name varchar(20))
>go
>create index ix_createtime on example(createtime)
>go
>create index ix_createtime_name on example(createtime, name)
>go
>Is the index ix_createtime redundant because the createtime column is
>part of the composite index ix_createtime_name. If someone whee to
>seach based only on the createtime column would the composite index be
>as useful as the index on just the createtime column?
If there are a million rows with the same time, and you might have a
query "select * from example where createtime = @.t and name like
'foo%', then, um, is SQLServer smart enough to do its work on the
index in a case like that?
Only reason I can think of you might want that second.
Josh|||On 19 May 2006 11:06:19 -0700, pshroads@.gmail.com wrote:

>Thanks. Am I correct in thinking that it's only redundant because
>statistics are maintained on only the first column of an index? So if
>the composite index had been on (name, createtime) then it would not be
>redundant because the optimizer would be unlikely to choose that index
>when searching on createtime because there would be no statistics?
Hi pshroads,
You are right that the index on (createtime) would not be redundant if
the composite index had been on (name, createtime), but you're wrong
about the reason.
Suppose I give you a stack of paper with one-paper sized biographies of
all famous people of the last millenium, ordered by birthdate. I'll also
give you a much smaller stack of paper that includes just the name and
the page number(s) where a person with that name is described, sorted by
last name, then first name. And I give you another index that holds just
the first name and the pages where someone with that first name is
found.
What would your strategy be if I asked you for some details about
someone with first name "Kathlyn"?
Hugo Kornelis, SQL Server MVP

Is this index redundant?

Let's say I have a table:
create table example (
id int,
createtime datetime,
name varchar(20))
go
create index ix_createtime on example(createtime)
go
create index ix_createtime_name on example(createtime, name)
go
Is the index ix_createtime redundant because the createtime column is
part of the composite index ix_createtime_name. If someone whee to
seach based only on the createtime column would the composite index be
as useful as the index on just the createtime column?
Thanks<pshroads@.gmail.com> wrote in message
news:1148056676.401254.153400@.y43g2000cwc.googlegroups.com...
> Is the index ix_createtime redundant because the createtime column is
> part of the composite index ix_createtime_name. If someone whee to
> seach based only on the createtime column would the composite index be
> as useful as the index on just the createtime column?
Yes, it probably is redundant. There are a few very limited edge cases
where it might not be -- for instance, if you're doing a lot of analysis
based on only a single column and every I/O counts -- but they are very few
and very far between. The composite index will certainly work for a query
based only on the createtime column.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Index ix_createtime is leaner than index ix_createtime_name. As such,
it is slightly more useful. However, its advantage is not too
noticeable, and under most circumstances you can safely drop it. you
can figure it out yourself, run your own tests, such as
select * from example where createtime between '20060101' and
'20060202'
there might be ranges for which access via ix_createtime is still
cheaper than scanning the whole clustered index, but access via
ix_createtime_name is already more expensive than table scan.
Good luck!|||Unless the table is read-only, you also need to consider maintenance costs.
With the redundant index, you will have extra work to do for almost every
modification to the table, and you'll need to determine if that cost is
worth the small benefit of having the narrower index for some queries.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<pshroads@.gmail.com> wrote in message
news:1148056676.401254.153400@.y43g2000cwc.googlegroups.com...
> Let's say I have a table:
> create table example (
> id int,
> createtime datetime,
> name varchar(20))
> go
> create index ix_createtime on example(createtime)
> go
> create index ix_createtime_name on example(createtime, name)
> go
> Is the index ix_createtime redundant because the createtime column is
> part of the composite index ix_createtime_name. If someone whee to
> seach based only on the createtime column would the composite index be
> as useful as the index on just the createtime column?
> Thanks
>|||Kalen,
I agree maintenance costs need to be taken in account. Yet I don't see
why you are calling the benefit of having a lean index "small" without
knowing much about the OP's system. I think it depends on the workload.
Suppose there is a query that runs very frequently. As such, small 10%
savings in its real execution costs may result in, say, 6% reduction of
overall workload for the whole system...|||Thanks. Am I correct in thinking that it's only redundant because
statistics are maintained on only the first column of an index? So if
the composite index had been on (name, createtime) then it would not be
redundant because the optimizer would be unlikely to choose that index
when searching on createtime because there would be no statistics?|||How will said "lean" index be used? I don't think dropping 20 bytes from an
index like this will result in "10% savings" in 99% of the cases, because
most likely a query will involve more than just the datetime column. Keep
in mind that bookmark lookups are much more expensive, in most cases, than
the additional I/Os that would be incurred reading the larger index pages.
We of course don't know what the clustering key is, or any other information
about the use, so this is all speculation, but unless this is a very
specialized situation I'd venture a guess that having both indexes is not
beneficial.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1148061719.429023.48180@.i40g2000cwc.googlegroups.com...
> Kalen,
> I agree maintenance costs need to be taken in account. Yet I don't see
> why you are calling the benefit of having a lean index "small" without
> knowing much about the OP's system. I think it depends on the workload.
> Suppose there is a query that runs very frequently. As such, small 10%
> savings in its real execution costs may result in, say, 6% reduction of
> overall workload for the whole system...
>|||well I'm not speaking in terms of 99% of the cases or 99.999% of the
case or whatever else. I am speaking about one particular system we
know almost nothing about - the OP's system. Suppose that system is
very simple and the primary goal of that simple system is to run
something like this:
select count(distinct id) from example where createtime between
@.fromdate and @.todate
as fast as possible. In that case the savings would be substantial, as
there would be no bookmark lookups. However rare, such cases do exist.
That's why I was trying to encourage the original poster to benchmark
by himself rather than rely on our advices. I think in our trade all
rules have exceptions.
That's all I was trying to say...|||On 19 May 2006 09:37:56 -0700, pshroads@.gmail.com wrote:

>Let's say I have a table:
>create table example (
> id int,
> createtime datetime,
> name varchar(20))
>go
>create index ix_createtime on example(createtime)
>go
>create index ix_createtime_name on example(createtime, name)
>go
>Is the index ix_createtime redundant because the createtime column is
>part of the composite index ix_createtime_name. If someone whee to
>seach based only on the createtime column would the composite index be
>as useful as the index on just the createtime column?
If there are a million rows with the same time, and you might have a
query "select * from example where createtime = @.t and name like
'foo%', then, um, is SQLServer smart enough to do its work on the
index in a case like that?
Only reason I can think of you might want that second.
Josh|||On 19 May 2006 11:06:19 -0700, pshroads@.gmail.com wrote:

>Thanks. Am I correct in thinking that it's only redundant because
>statistics are maintained on only the first column of an index? So if
>the composite index had been on (name, createtime) then it would not be
>redundant because the optimizer would be unlikely to choose that index
>when searching on createtime because there would be no statistics?
Hi pshroads,
You are right that the index on (createtime) would not be redundant if
the composite index had been on (name, createtime), but you're wrong
about the reason.
Suppose I give you a stack of paper with one-paper sized biographies of
all famous people of the last millenium, ordered by birthdate. I'll also
give you a much smaller stack of paper that includes just the name and
the page number(s) where a person with that name is described, sorted by
last name, then first name. And I give you another index that holds just
the first name and the pages where someone with that first name is
found.
What would your strategy be if I asked you for some details about
someone with first name "Kathlyn"?
Hugo Kornelis, SQL Server MVPsql

Is this index redundant?

Let's say I have a table:
create table example (
id int,
createtime datetime,
name varchar(20))
go
create index ix_createtime on example(createtime)
go
create index ix_createtime_name on example(createtime, name)
go
Is the index ix_createtime redundant because the createtime column is
part of the composite index ix_createtime_name. If someone whee to
seach based only on the createtime column would the composite index be
as useful as the index on just the createtime column?
Thanks<pshroads@.gmail.com> wrote in message
news:1148056676.401254.153400@.y43g2000cwc.googlegroups.com...
> Is the index ix_createtime redundant because the createtime column is
> part of the composite index ix_createtime_name. If someone whee to
> seach based only on the createtime column would the composite index be
> as useful as the index on just the createtime column?
Yes, it probably is redundant. There are a few very limited edge cases
where it might not be -- for instance, if you're doing a lot of analysis
based on only a single column and every I/O counts -- but they are very few
and very far between. The composite index will certainly work for a query
based only on the createtime column.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Index ix_createtime is leaner than index ix_createtime_name. As such,
it is slightly more useful. However, its advantage is not too
noticeable, and under most circumstances you can safely drop it. you
can figure it out yourself, run your own tests, such as
select * from example where createtime between '20060101' and
'20060202'
there might be ranges for which access via ix_createtime is still
cheaper than scanning the whole clustered index, but access via
ix_createtime_name is already more expensive than table scan.
Good luck!|||Unless the table is read-only, you also need to consider maintenance costs.
With the redundant index, you will have extra work to do for almost every
modification to the table, and you'll need to determine if that cost is
worth the small benefit of having the narrower index for some queries.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<pshroads@.gmail.com> wrote in message
news:1148056676.401254.153400@.y43g2000cwc.googlegroups.com...
> Let's say I have a table:
> create table example (
> id int,
> createtime datetime,
> name varchar(20))
> go
> create index ix_createtime on example(createtime)
> go
> create index ix_createtime_name on example(createtime, name)
> go
> Is the index ix_createtime redundant because the createtime column is
> part of the composite index ix_createtime_name. If someone whee to
> seach based only on the createtime column would the composite index be
> as useful as the index on just the createtime column?
> Thanks
>|||Kalen,
I agree maintenance costs need to be taken in account. Yet I don't see
why you are calling the benefit of having a lean index "small" without
knowing much about the OP's system. I think it depends on the workload.
Suppose there is a query that runs very frequently. As such, small 10%
savings in its real execution costs may result in, say, 6% reduction of
overall workload for the whole system...|||Thanks. Am I correct in thinking that it's only redundant because
statistics are maintained on only the first column of an index? So if
the composite index had been on (name, createtime) then it would not be
redundant because the optimizer would be unlikely to choose that index
when searching on createtime because there would be no statistics?|||How will said "lean" index be used? I don't think dropping 20 bytes from an
index like this will result in "10% savings" in 99% of the cases, because
most likely a query will involve more than just the datetime column. Keep
in mind that bookmark lookups are much more expensive, in most cases, than
the additional I/Os that would be incurred reading the larger index pages.
We of course don't know what the clustering key is, or any other information
about the use, so this is all speculation, but unless this is a very
specialized situation I'd venture a guess that having both indexes is not
beneficial.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1148061719.429023.48180@.i40g2000cwc.googlegroups.com...
> Kalen,
> I agree maintenance costs need to be taken in account. Yet I don't see
> why you are calling the benefit of having a lean index "small" without
> knowing much about the OP's system. I think it depends on the workload.
> Suppose there is a query that runs very frequently. As such, small 10%
> savings in its real execution costs may result in, say, 6% reduction of
> overall workload for the whole system...
>|||well I'm not speaking in terms of 99% of the cases or 99.999% of the
case or whatever else. I am speaking about one particular system we
know almost nothing about - the OP's system. Suppose that system is
very simple and the primary goal of that simple system is to run
something like this:
select count(distinct id) from example where createtime between
@.fromdate and @.todate
as fast as possible. In that case the savings would be substantial, as
there would be no bookmark lookups. However rare, such cases do exist.
That's why I was trying to encourage the original poster to benchmark
by himself rather than rely on our advices. I think in our trade all
rules have exceptions.
That's all I was trying to say...|||On 19 May 2006 09:37:56 -0700, pshroads@.gmail.com wrote:
>Let's say I have a table:
>create table example (
> id int,
> createtime datetime,
> name varchar(20))
>go
>create index ix_createtime on example(createtime)
>go
>create index ix_createtime_name on example(createtime, name)
>go
>Is the index ix_createtime redundant because the createtime column is
>part of the composite index ix_createtime_name. If someone whee to
>seach based only on the createtime column would the composite index be
>as useful as the index on just the createtime column?
If there are a million rows with the same time, and you might have a
query "select * from example where createtime = @.t and name like
'foo%', then, um, is SQLServer smart enough to do its work on the
index in a case like that?
Only reason I can think of you might want that second.
Josh|||On 19 May 2006 11:06:19 -0700, pshroads@.gmail.com wrote:
>Thanks. Am I correct in thinking that it's only redundant because
>statistics are maintained on only the first column of an index? So if
>the composite index had been on (name, createtime) then it would not be
>redundant because the optimizer would be unlikely to choose that index
>when searching on createtime because there would be no statistics?
Hi pshroads,
You are right that the index on (createtime) would not be redundant if
the composite index had been on (name, createtime), but you're wrong
about the reason.
Suppose I give you a stack of paper with one-paper sized biographies of
all famous people of the last millenium, ordered by birthdate. I'll also
give you a much smaller stack of paper that includes just the name and
the page number(s) where a person with that name is described, sorted by
last name, then first name. And I give you another index that holds just
the first name and the pages where someone with that first name is
found.
What would your strategy be if I asked you for some details about
someone with first name "Kathlyn"?
--
Hugo Kornelis, SQL Server MVP|||I suppose it would be to look up the name Kathlyn in the third index
and get the page number and then go to that page number in the large
stack. Perhaps I'm being dense but I still need the reason explained :)
Thanks!|||On 19 May 2006 16:12:32 -0700, pshroads@.gmail.com wrote:
>I suppose it would be to look up the name Kathlyn in the third index
>and get the page number and then go to that page number in the large
>stack. Perhaps I'm being dense but I still need the reason explained :)
>Thanks!
Hi pshroads,
The first stack of paper equates to the table. The second stack equates
to an index on (last name, first name). And the third stack equates to
an index on only (first name). You'll use the index on (first name) to
find all people with first name Kathlyn, and you'll use the index on
(last name, first name) to find all people with last name Harrison, or
all people named George Washington. Both indexes are useful, without
being redundant.
Now replace the index on (first name) with an index on (last name) [i.e.
a list of last names and page numbers where these last names are
mentioned]. You'll still use the (last name, first name) index for
finding George Washington. You could use the new (last name) index to
locate every Mr or Mrs Harrison, but you can still do that with the
(last name, first name) index as well. And when looking for Kathlyn,
your only option is to sit down and flip each and every page of the
biggest stack of papers (the infamous table scan). So now, you have an
index that is only usable for a kind of search that can also be done
with the other index - clearly the (last name) index is redundant.
Database searches work exactly the same as yoour searches in those
stacks of paper. The only difference is that the computer turns the
pages somewhat faster. :-)
--
Hugo Kornelis, SQL Server MVP

Is this bug with Convert?

I was trying to debug some DateTime.Now in a C# project and while debugging I found this.

In your Sql Management Studio, type this:

The 916 becomes 917. Why does my millisecond get screwed?

select convert(datetime, '2007-06-29 15:22:31:921') -- prints 2007-06-29 15:22:31.920

select convert(datetime, '2007-06-29 15:22:31:916') -- print 2007-06-29 15:22:31.917

That is because of the precision of the datetime data type 1/300 of a second. Check BOL for more info about datetime data type.

select convert(datetime, '2007-06-29 15:22:31:998')

go

AMB

Is This a Correct Method ?

Hi All,
While inserting date value I wish to take only date part So I tried
this
Create Table JTrial
(
XYZ int,
d datetime default convert(varchar,getdate(),112)
)
insert into JTrial(XYZ) values(1)
insert into JTrial(XYZ) values(2)
insert into JTrial(XYZ) values(3)
insert into JTrial(XYZ) values(4)
select * from JTrial
Is there any better alternative. Check constraint like this
check ( d = convert(varchar,d,112) )
Will not allow me to insert row
insert into JTrial(XYZ,d) values(5,getdate()) -- Because here date has
time part
Is writing A trigger better alternative ?
Please guide me on this ?
With warm regards
Jatinder SinghYes, that is the method that I prefer (although I always specify a length fo
r varchar, see your
convert function). I also like to have a check constraint instead of a trigg
er. I have elaborated a
bit on this topic in http://www.karaszi.com/SQLServer/info_datetime.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1124441020.097121.43310@.f14g2000cwb.googlegroups.com...
> Hi All,
> While inserting date value I wish to take only date part So I tried
> this
> Create Table JTrial
> (
> XYZ int,
> d datetime default convert(varchar,getdate(),112)
> )
> insert into JTrial(XYZ) values(1)
> insert into JTrial(XYZ) values(2)
> insert into JTrial(XYZ) values(3)
> insert into JTrial(XYZ) values(4)
> select * from JTrial
> Is there any better alternative. Check constraint like this
> check ( d = convert(varchar,d,112) )
> Will not allow me to insert row
> insert into JTrial(XYZ,d) values(5,getdate()) -- Because here date has
> time part
> Is writing A trigger better alternative ?
> Please guide me on this ?
> With warm regards
> Jatinder Singh
>|||Hi Tibor,
The default constraint work with a value (fix sort of/ not entered by
user) of date and Check constraint does not let it pass if it has any
time other than (00:00:00), Both in what your article suggest and what
I tried.
Thanks for giving valuable advice , I purposed Trigger because I
would storage there.
Create Table JTrial
(
XYZ int,
d datetime default convert(varchar,getdate(),112)
)
Go
Create trigger trg1 on JTrial for Insert
as
Begin
Update JTrial set d=convert(varchar,d,112)
-- I should have take a cross join with the Inserted table
End
Declare @.aDate datetime
select getdate()
set @.aDate = '2005-08-19 18:17:09.607'
insert into JTrial(XYZ,d) values(1,getdate())
insert into JTrial(XYZ,d) values(2,@.aDate) -- User entered value
insert into JTrial(XYZ) values(3) -- Default will be stored
insert into JTrial(XYZ) values(4)
select * from JTrial
Drop table JTrial
With warm regards
Jatinder Singh
Tibor Karaszi wrote:
> Yes, that is the method that I prefer (although I always specify a length
for varchar, see your
> convert function). I also like to have a check constraint instead of a tri
gger. I have elaborated a
> bit on this topic in http://www.karaszi.com/SQLServer/info_datetime.asp.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
> news:1124441020.097121.43310@.f14g2000cwb.googlegroups.com...|||Jatinder,
If I understand you correctly, you are saying that a trigger has the possibl
e advantage of changing
the datetime value that the user entered so that it always has 00:00:00 as t
he time portion. Where a
check constraint will produce an error.
Yes, that is a correct observation. You can't say that one approach is alway
s correct. I prefer the
check constraint, as you will catch where applications is sending an invalid
datetime value and fix
the application. IMO, that is a better approach to just changing the value w
ithout the user or
client application programmer knowing you have changed it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1124455793.019933.100900@.g43g2000cwa.googlegroups.com...
> Hi Tibor,
> The default constraint work with a value (fix sort of/ not entered by
> user) of date and Check constraint does not let it pass if it has any
> time other than (00:00:00), Both in what your article suggest and what
> I tried.
> Thanks for giving valuable advice , I purposed Trigger because I
> would storage there.
> Create Table JTrial
> (
> XYZ int,
> d datetime default convert(varchar,getdate(),112)
> )
> Go
> Create trigger trg1 on JTrial for Insert
> as
> Begin
> Update JTrial set d=convert(varchar,d,112)
> -- I should have take a cross join with the Inserted table
> End
> Declare @.aDate datetime
> select getdate()
> set @.aDate = '2005-08-19 18:17:09.607'
> insert into JTrial(XYZ,d) values(1,getdate())
> insert into JTrial(XYZ,d) values(2,@.aDate) -- User entered value
> insert into JTrial(XYZ) values(3) -- Default will be stored
> insert into JTrial(XYZ) values(4)
> select * from JTrial
> Drop table JTrial
> With warm regards
> Jatinder Singh
> Tibor Karaszi wrote:
>|||Sorry Typo
inner join in trigger
With warm regards
Jatinder Singh|||Tibor,
Thanks for giving your time. Is speed /performance an issue
here means using trigger v/s Conversions at client side.
With warm regards
Jatinder Singh|||Yes, passing in the correct data to begin with will give better performance
compared to having a
trigger which goes back to the modified rows and alter the value to the desi
red value.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1124457867.638474.309950@.g14g2000cwa.googlegroups.com...
> Tibor,
> Thanks for giving your time. Is speed /performance an issue
> here means using trigger v/s Conversions at client side.
> With warm regards
> Jatinder Singh
>

Is there such thing as cross-table index or something?

CREATE TABLE [SalesForecast] (
[SaleDate] [datetime] NOT NULL ,
[CustID] [varchar] (10) NOT NULL ,
[F1] [money] NOT NULL
) ON [PRIMARY]
CREATE TABLE [Sales] (
[SaleDate] [datetime] NOT NULL ,
[CustID] [varchar] (10) NOT NULL ,
[S1] [money] NOT NULL
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX SalesForecast_CustID ON SalesForecast (CustID)
CREATE NONCLUSTERED INDEX Sales_CustID ON Sales (CustID)
CREATE NONCLUSTERED INDEX SalesForecast_SaleDate ON SalesForecast (SaleDate)
CREATE NONCLUSTERED INDEX Sales_SaleDate ON Sales (SaleDate)
When I mix a query with both tables like this its really slow:
SELECT A.S1 * B.F1
FROM Sales A, SalesForecast B
WHERE A.SaleDate = B.SaleDate
AND A.CustID = B.CustID
AND A.SaleDate > '20050101'
What am I doing wrong, this seems to be correct..It would help to have a clustered index on the date column. You don't look
like you have any clustered indexes at all, which is not a good practice.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Rich" <no@.spam.invalid> wrote in message
news:44HGe.53673$4o.23499@.fed1read06...
> CREATE TABLE [SalesForecast] (
> [SaleDate] [datetime] NOT NULL ,
> [CustID] [varchar] (10) NOT NULL ,
> [F1] [money] NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [Sales] (
> [SaleDate] [datetime] NOT NULL ,
> [CustID] [varchar] (10) NOT NULL ,
> [S1] [money] NOT NULL
> ) ON [PRIMARY]
>
> CREATE NONCLUSTERED INDEX SalesForecast_CustID ON SalesForecast (CustID)
> CREATE NONCLUSTERED INDEX Sales_CustID ON Sales (CustID)
> CREATE NONCLUSTERED INDEX SalesForecast_SaleDate ON SalesForecast
> (SaleDate)
> CREATE NONCLUSTERED INDEX Sales_SaleDate ON Sales (SaleDate)
>
> When I mix a query with both tables like this its really slow:
> SELECT A.S1 * B.F1
> FROM Sales A, SalesForecast B
> WHERE A.SaleDate = B.SaleDate
> AND A.CustID = B.CustID
> AND A.SaleDate > '20050101'
> What am I doing wrong, this seems to be correct..
>|||What are the keys in each case? Do you have any? A unique key could
make a significant difference.
David Portas
SQL Server MVP
--|||Do not use the proprietary MONEY data type. Declare a primary key for
the tables. Most codes are fixed length to make them easier to validate
so I find it hard to believe that your customer id is really VARCHAR(n)
CREATE TABLE SalesForecast
(sale_date DATETIME NOT NULL,
cust_id CHAR(10) NOT NULL,
forecast_amt DECIMAL (12,4) NOT NULL,
PRIMARY KEY (sale_date, cust_id));
CREATE TABLE Sales
(sale_date DATETIME NOT NULL,
cust_id CHAR(10) NOT NULL,
sales_amt DECIMAL (12,4) NOT NULL,
PRIMARY KEY (sale_date, cust_id));
This gives you a covering index for your query. If the join is still
slow, use a clustered option on the keys.|||Thanks I'll give that a try. CustID is anywhere from 6 to 10 characters
long.. Most of the time it is 6, but sometimes it is 9 or 10! Should I still
use char instead of varchar?
Thanks.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1122732638.691124.46520@.g14g2000cwa.googlegroups.com...
> Do not use the proprietary MONEY data type. Declare a primary key for
> the tables. Most codes are fixed length to make them easier to validate
> so I find it hard to believe that your customer id is really VARCHAR(n)
> CREATE TABLE SalesForecast
> (sale_date DATETIME NOT NULL,
> cust_id CHAR(10) NOT NULL,
> forecast_amt DECIMAL (12,4) NOT NULL,
> PRIMARY KEY (sale_date, cust_id));
> CREATE TABLE Sales
> (sale_date DATETIME NOT NULL,
> cust_id CHAR(10) NOT NULL,
> sales_amt DECIMAL (12,4) NOT NULL,
> PRIMARY KEY (sale_date, cust_id));
> This gives you a covering index for your query. If the join is still
> slow, use a clustered option on the keys.
>

Is there an equivalent Replicate() function in Access

Hi,

I am trying to build a string from a datetime datetype and want to make sure that if a day or month is only one digit long then it is padded with a leading 0.

In SQL I can do it like this;

declare @.datetime datetime

select @.datetime = getdate()

select 'TPR'

+ replicate('0', 2 - len(datepart(dd,@.datetime))) + convert(varchar(2),datepart(dd,@.datetime))

+ replicate('0', 2 - len(datepart(mm,@.datetime))) + convert(varchar(2),datepart(mm,@.datetime))

+ convert(varchar(4),datepart(yyyy,@.datetime))

Do you know how I can do the same thing in Access?

Thanks for your help

It would be SO-O-O-O much easier to do it in this manner:

SELECT right( '00' + cast( datepart( day, @.DateTime ) AS varchar(2) ), 2 )

(Should work in Access AND SQL Server.)

But really, wouldn't something like this be a better solution: (this is SQL)

SELECT ( 'TPR' + SELECT replace( convert( varchar(10), @.DateTime, 101 ), '/', '' ))

For Access, you would have to use the FORMAT function instead of convert. Refer to the help for FORMAT().

|||

Yes Buddy.. You have it..

STRING(<number>, <Char>)

example:

String(5,"M") => MMMMM