Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

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.
0
DexterJones
Asked:
DexterJones
  • 6
  • 4
1 Solution
 
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now