We help IT Professionals succeed at work.

CREATE  INDEX DateTime

DexterJones
DexterJones asked
on
Medium Priority
350 Views
Last Modified: 2008-03-17
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.
Comment
Watch Question

Database Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
Your query will create a Non Clustured index
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
In order to create a unique index
CREATE UNIQUE INDEX [dtstart] ON [dbo].[testtable]([DTStart]) WITH  FILLFACTOR = 90 ON [PRIMARY]

Author

Commented:
aneeshattingal,

nice blog site, thanks!
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:

Author

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.

<<
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
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

Author

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.
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
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.

Author

Commented:
thanks!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.