Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Creating a clustered index on Getdate function Expand / Collapse
Author
Message
Posted Wednesday, January 12, 2011 11:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 24, 2014 12:59 PM
Points: 17, Visits: 253
Hi every one,
Here is my question:

I am having one table with one of the table column is having the records of get date function.
Is it possible or Is it encouraged to create a clustered index on that column containing the records of getdate() function.
Please explain.
Thank you.
Post #1046655
Posted Wednesday, January 12, 2011 11:20 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
It is definitely possible. Sometimes it's even the best column for that.

It'll depend on how you're using it. What does the column mean in the data?


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1046658
Posted Wednesday, January 12, 2011 11:25 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, August 8, 2014 2:24 PM
Points: 543, Visits: 1,654
I do it sometimes when the primary key is a uniqueidentfier.

I don't particularly like doing it, but sometimes I can't find a better column.
Post #1046665
Posted Wednesday, January 12, 2011 3:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 24, 2014 12:59 PM
Points: 17, Visits: 253
Thanks for the reply.
Post #1046852
Posted Wednesday, January 12, 2011 3:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 24, 2014 12:59 PM
Points: 17, Visits: 253
i created a table
CREATE TABLE [dbo].[a_temp](
[date] [date] NULL,
[batchid] [int] NULL,
[batchid1] [int] NULL
) ON [PRIMARY]

i inserted rows into the table using get date unction as
declare @i int;
set @i=0;
while(@i<=1000)
begin
insert into a_temp(date,batchid,batchid1)
values (getdate(),@i,@i+1);
set @i =@i +1;
end


when i check the data the date column is containing the same records
as
date
2011-01-12
2011-01-12
2011-01-12
2011-01-12
2011-01-12
2011-01-12


i cannot create clustered index on the date column with this similar records in the column.
can explain?
Thank you
Post #1046868
Posted Wednesday, January 12, 2011 3:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 24, 2014 12:59 PM
Points: 17, Visits: 253
i created a table
CREATE TABLE [dbo].[a_temp](
[date] [date] NULL,
[batchid] [int] NULL,
[batchid1] [int] NULL
) ON [PRIMARY]

i inserted rows into the table using get date unction as
declare @i int;
set @i=0;
while(@i<=1000)
begin
insert into a_temp(date,batchid,batchid1)
values (getdate(),@i,@i+1);
set @i =@i +1;
end


when i check the data the date column is containing the same records
as
date
2011-01-12
2011-01-12
2011-01-12
2011-01-12
2011-01-12
2011-01-12


i cannot create clustered index on the date column with this similar records in the column.
can explain?
Thank you
Post #1046870
Posted Thursday, January 13, 2011 6:43 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Are you just creating a clustered index, or are you creating a clustered-unique index? Or by "clustered index" do you mean "primary key"? The clustered index can have duplicate values in it, unless it's also either expressly unique, or being used as the primary key.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1047223
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse