CREATE INDEX DateTime

Hi,

Please kindly assist how can we properly set indexes for a date and time column data type?

i.e.
CREATE  INDEX [dtstart] ON [dbo].[testtable]([DTStart]) WITH  FILLFACTOR = 90 ON [PRIMARY]

is this fill factor correct? what other parameters are needed to set an index properly? clustered? unique?


Thanks.
DexterJonesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
read this article
http://sqlnerd.blogspot.com/2005/06/index-fill-factor-performance.html
and then put the proper fillfactor, If you dont' have the proper idea go for fillfactor of 70
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Aneesh RetnakaranDatabase AdministratorCommented:
Your query will create a Non Clustured index
0
Aneesh RetnakaranDatabase AdministratorCommented:
In order to create a unique index
CREATE UNIQUE INDEX [dtstart] ON [dbo].[testtable]([DTStart]) WITH  FILLFACTOR = 90 ON [PRIMARY]
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DexterJonesAuthor Commented:
aneeshattingal,

nice blog site, thanks!
0
Aneesh RetnakaranDatabase AdministratorCommented:
0
DexterJonesAuthor Commented:
how do i know if the new data beign appended adds to the end of the row?

>>
Remember, if your clustering key ever increasing and the new data coming is “appended” to the end of the table – both logically and by clustering key definition – then you only need the fillfactor setting on the nonclustered indexes.

<<
0
Aneesh RetnakaranDatabase AdministratorCommented:
Data will be stored in  the same order of a CI.
create a clustured index on an identity column and insert the vaues, then u can c the data will be appending to the end

for example
create table testTable ( i int Identity(1,1), j int)
GO
insert into testTable SELECT '1'
insert into testTable SELECT '2'
insert into testTable SELECT '3'

SELECT * from testTable

insert into testTable SELECT '14'

SELECT * from TestTable

now u can c that the data has been appending, since we have created an index on an incrementing column
0
DexterJonesAuthor Commented:
aneeshattingal,

what if the incremental column doesn't have index set? is this where it scatters the data everywhere?

I have an identity column but it's not index and a datetime column with is set to non clustered index.



Thanks.
0
Aneesh RetnakaranDatabase AdministratorCommented:
Sorry I forgot to put the Primary key
create table testTable ( i int Identity(1,1) primary key, j int)
Now the coluumn i  will have a clustured index (  by default ).

I will give you another example

create table testTable1 ( i int , j int)
GO
create table testTable2 ( i int primary key  , j int)

go
insert into testTable1 SELECT '2', '1'
insert into testTable1 SELECT '5','2'
insert into testTable1  SELECT '1', '3'
SELECT * from testTable1  
GO

insert into testTable2 SELECT '2', '1'
insert into testTable2 SELECT '5','2'
insert into testTable2  SELECT '1', '3'
SELECT * from testTable2

now you can c the difference of both these outputs

For the first statement, the output will be scattered and for the second it will be sorted

i.e. for every insert statement, sql server will search for an appropriate place to put the data.
So if you have an identity column as i shown in my previous post where the next place to be inserted will be always at the end of table.
0
DexterJonesAuthor Commented:
thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.