January 29, 2014 at 4:52 pm
Hi, I've got a Time look up table that has one column whose values are the datetime stamp for every minute of every day for the past two years and 10 years into the future. I use join to it from other tables.
This lookup table never gets modified and when I "SELECT *" from it I the time values appear to be physically stored in ascending order. Should I add a clustered index to it, anyway?
--Quote me
January 29, 2014 at 5:32 pm
Row ordering is never guaranteed on retrieval unless you explicitly specify ORDER BY.
It just so happens that the times are being retrieved in order because that's probably the order you initially inserted them. If you have a clustered INDEX on the time column, it increases the probability (to very high) that the rows will be returned in time sequence. This is a SQL implementation behavior so is not guaranteed to persist, and could be influenced by other INDEXes on the table.
Having said all of that, the clustered index is probably recommended so that queries that join to the time table can use it. This should convert a table scan into an index scan or seek.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 29, 2014 at 5:47 pm
polkadot (1/29/2014)
This lookup table never gets modified and when I "SELECT *" from it I the time values appear to be physically stored in ascending order. Should I add a clustered index to it, anyway?
That's... not exactly the right question to ask.
As Dwain pointed out above, if you're worried about the return order of data, include an ORDER BY in your query. That's your only guarantee. But I'm not sure that's what you're getting at.
The purpose of the clustered index isn't just to decide the storage order, but to assist in retrieval of the data. It's the key for all the non-clustered indexes to find rows. It's the search mechanism that all joins will use to determine if they're attaching to a particular portion or scanning the entire table looking for information.
With no indexing, you're always going to scan your table. This is non-optimal. If you only have non-clustered indexes, then you really want to go read up on heap optimization in SQL Server, as that's not the way that SQL Server really wants to work. It can, but you need to review the expectations.
In most circumstances, you want your clustered index to be on your most common join method to the table. In this case, your datetime field is your most likely range lookup, I would figure.
If you've got a non-clustered index already doing this, convert it to clustered.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 29, 2014 at 6:35 pm
Much gratitude for understanding what I was attempting to say. Difference between physically ordering the query results (ORDER BY) and optimizing (with Index) retrieval of time ranges pertinent to the join.
I will add clustered index even though there's just one column in the table and the rows appear ordered :laugh:
Also, I appreciate your signature quote Craig. Always learning from you guys. Thanks for being there.
**edit:
Wouldn't it be even better that I create a UNIQUE CLUSTERED INDEX? That way I am ensuring no duplicate datetime rows, which is what I want. Is that correct thinking?
CREATE UNIQUE INDEX UQ_n ON database.dbo.table(n) ASC WITH (ONLINE=ON);
--Quote me
January 29, 2014 at 11:08 pm
All tables (well, almost all) should have a primary key and should have a clustered index. Whether these are the same or not is another matter.
If you want an ordered resultset, you absolutely must use ORDER BY, regardless of indexes, keys, etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 30, 2014 at 5:50 am
are you suggesting that instead of Unique Index I make the column a Primary Key? I understand PKs enforce uniqueness and SQL Server will in response automatically create a Clustered Index on the table on the key column.
I'm looking for best practice and that sounds good to me.
--Quote me
January 30, 2014 at 6:06 am
polkadot (1/30/2014)
are you suggesting that instead of Unique Index I make the column a Primary Key?
If the table doesn't already have a primary key, yes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 30, 2014 at 1:23 pm
polkadot (1/30/2014)
are you suggesting that instead of Unique Index I make the column a Primary Key? I understand PKs enforce uniqueness and SQL Server will in response automatically create a Clustered Index on the table on the key column.I'm looking for best practice and that sounds good to me.
Primary Key <> Clustered Index. It defaults that way if you use the GUI, but it's not necessary.
The PK's purpose is to find a unique row. That's it. I have many tables with nonclustered PKs. They're not the usual access method to the table. They're so you can find *1* particular row when necessary. Having it as the PK saves the next guy from having to figure out how to do that.
Clustered Index controls storage and is the main index used when searching for information in a table. It's best if this is unique (under the hood it'll be made unique if it's not) but not necessary and in plenty of cases non-optimal.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply