April 2, 2012 at 1:08 pm
Hi All,
On my table there was one identity column with (1,1)
In that table daily 3 new records are added
But when I quering the table i am gettig below result
id column1 column2
1
2
3
4
5
6
13
14
15
19
20
21
31
32
33
37
38
39
7
8
9
25
26
27
10
11
12
16
17
18
22
23
24
28
29
30
34
35
36
there is some missing order on identity column
How can I reslove that problem
Thanks,
Mark
April 2, 2012 at 1:18 pm
Identity columns don't guarantee sequentiality. They can skip around. If an insert fails, or gets rolled back, the identity goes up, but the row won't be in there.
- 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
April 2, 2012 at 1:18 pm
Before any help can be given please post the code to create and query the table. Remember that an identity column is not always used as a key field and that no order is guaranteed in a select query unless an order by is used.
Fitz
April 2, 2012 at 1:20 pm
And to ensure order by the id column, you need to include an ORDER BY id in your query.
April 2, 2012 at 1:21 pm
sqlserver8650 (4/2/2012)
How can I reslove that problem
With an ORDER BY clause on your select query.
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
April 2, 2012 at 1:48 pm
Hi All,
Thanks for giving the replay
Is there any chance to get order with out order by clause
April 2, 2012 at 1:51 pm
CREATE TABLE [dbo].[SSIS_RunTimeMetrics](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Load_Date] [datetime] NULL,
[JobName] [varchar](max) COLLATE Latin1_General_CI_AS NULL,
) ON [PRIMARY]
select * from ssis_runtimemetrics
Result:
12012-03-14 00:00:00.000Treasury Extracts Dly
22012-03-14 00:00:00.000Treasury Extracts Dly
32012-03-14 00:00:00.000Treasury Extracts Dly
42012-03-15 00:00:00.000Treasury Extracts Dly
52012-03-15 00:00:00.000Treasury Extracts Dly
62012-03-15 00:00:00.000Treasury Extracts Dly
132012-03-20 00:00:00.000Treasury Extracts Dly
142012-03-20 00:00:00.000Treasury Extracts Dly
152012-03-20 00:00:00.000Treasury Extracts Dly
192012-03-22 00:00:00.000Treasury Extracts Dly
202012-03-22 00:00:00.000Treasury Extracts Dly
212012-03-22 00:00:00.000Treasury Extracts Dly
312012-03-28 00:00:00.000Treasury Extracts Dly
322012-03-28 00:00:00.000Treasury Extracts Dly
332012-03-28 00:00:00.000Treasury Extracts Dly
372012-03-30 00:00:00.000Treasury Extracts Dly
382012-03-30 00:00:00.000Treasury Extracts Dly
392012-03-30 00:00:00.000Treasury Extracts Dly
72012-03-16 00:00:00.000Treasury Extracts Dly
82012-03-16 00:00:00.000Treasury Extracts Dly
92012-03-16 00:00:00.000Treasury Extracts Dly
252012-03-26 00:00:00.000Treasury Extracts Dly
262012-03-26 00:00:00.000Treasury Extracts Dly
272012-03-26 00:00:00.000Treasury Extracts Dly
102012-03-19 00:00:00.000Treasury Extracts Dly
112012-03-19 00:00:00.000Treasury Extracts Dly
122012-03-19 00:00:00.000Treasury Extracts Dly
162012-03-21 00:00:00.000Treasury Extracts Dly
172012-03-21 00:00:00.000Treasury Extracts Dly
182012-03-21 00:00:00.000Treasury Extracts Dly
222012-03-23 00:00:00.000Treasury Extracts Dly
232012-03-23 00:00:00.000Treasury Extracts Dly
242012-03-23 00:00:00.000Treasury Extracts Dly
282012-03-27 00:00:00.000Treasury Extracts Dly
292012-03-27 00:00:00.000Treasury Extracts Dly
302012-03-27 00:00:00.000Treasury Extracts Dly
342012-03-29 00:00:00.000Treasury Extracts Dly
352012-03-29 00:00:00.000Treasury Extracts Dly
362012-03-29 00:00:00.000Treasury Extracts Dly
Please observe the id column output
Please give some suggestions how to reset the id column
April 2, 2012 at 1:54 pm
sqlserver8650 (4/2/2012)
Hi All,Thanks for giving the replay
Is there any chance to get order with out order by clause
No. To ensure order you need an ORDER BY clause.
April 2, 2012 at 1:54 pm
sqlserver8650 (4/2/2012)
Hi All,Thanks for giving the replay
Is there any chance to get order with out order by clause
SQL Server will never guarantee an order without an order by clause.
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
April 2, 2012 at 2:42 pm
sqlserver8650 (4/2/2012)
Please give some suggestions how to reset the id column
There's no resetting needed. The results are not in order because you have not specified and order by clause and without an order by there is no guarantee of order.
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
April 2, 2012 at 6:40 pm
Lynn,
To ensure order you need an ORDER BY clause.
How about this case?
DECLARE @n TABLE (n INT)
INSERT @n (n)
SELECT 5
UNION ALL SELECT 1
UNION ALL SELECT 4
UNION ALL SELECT 2
UNION ALL SELECT 6
UNION ALL SELECT 3
UNION ALL SELECT 8
UNION ALL SELECT 7
SELECT * FROM @n
;WITH CTE AS (
SELECT n, ROW_NUMBER() OVER (ORDER BY n) AS r
FROM @n
)
SELECT n1.n
FROM @n n1
INNER JOIN CTE ON n1.n=r
I ran across this recently. The window function was causing an ORDER BY without using that clause explictly. Granted it is kind of contrived. But in some cases it does seem to work.
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
April 2, 2012 at 10:20 pm
dwain.c (4/2/2012)
Lynn,To ensure order you need an ORDER BY clause.
How about this case?
DECLARE @n TABLE (n INT)INSERT @n (n)
SELECT 5
UNION ALL SELECT 1
UNION ALL SELECT 4
UNION ALL SELECT 2
UNION ALL SELECT 6
UNION ALL SELECT 3
UNION ALL SELECT 8
UNION ALL SELECT 7
SELECT * FROM @n
;WITH CTE AS (
SELECT n, ROW_NUMBER() OVER (ORDER BY n) AS r
FROM @n
)
SELECT n1.n
FROM @n n1
INNER JOIN CTE ON n1.n=r
I ran across this recently. The window function was causing an ORDER BY without using that clause explictly. Granted it is kind of contrived. But in some cases it does seem to work.
Think about it, you are explicitly using an order by. This is how the dynamic tally table works.
April 3, 2012 at 1:25 am
dwain.c (4/2/2012)
The window function was causing an ORDER BY without using that clause explictly. Granted it is kind of contrived. But in some cases it does seem to work.
In some cases a SELECT * FROM table will work. Your ordering is still not guaranteed. Likely yes, but not guaranteed
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
April 3, 2012 at 3:05 am
Think about it, you are explicitly using an order by. This is how the dynamic tally table works.
Yes I know. But then why does removing the INNER JOIN cause the ordering to be removed?
I did look at the query execution plans. In the case with the INNER JOIN, the plan shows a sort. Removing the INNER JOIN removes the sort from the plan.
I'm just saying I think the behavior is a bit odd and I'd be much more comfortable if a general explanation was available.
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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply