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:
1 2012-03-14 00:00:00.000 Treasury Extracts Dly
2 2012-03-14 00:00:00.000 Treasury Extracts Dly
3 2012-03-14 00:00:00.000 Treasury Extracts Dly
4 2012-03-15 00:00:00.000 Treasury Extracts Dly
5 2012-03-15 00:00:00.000 Treasury Extracts Dly
6 2012-03-15 00:00:00.000 Treasury Extracts Dly
13 2012-03-20 00:00:00.000 Treasury Extracts Dly
14 2012-03-20 00:00:00.000 Treasury Extracts Dly
15 2012-03-20 00:00:00.000 Treasury Extracts Dly
19 2012-03-22 00:00:00.000 Treasury Extracts Dly
20 2012-03-22 00:00:00.000 Treasury Extracts Dly
21 2012-03-22 00:00:00.000 Treasury Extracts Dly
31 2012-03-28 00:00:00.000 Treasury Extracts Dly
32 2012-03-28 00:00:00.000 Treasury Extracts Dly
33 2012-03-28 00:00:00.000 Treasury Extracts Dly
37 2012-03-30 00:00:00.000 Treasury Extracts Dly
38 2012-03-30 00:00:00.000 Treasury Extracts Dly
39 2012-03-30 00:00:00.000 Treasury Extracts Dly
7 2012-03-16 00:00:00.000 Treasury Extracts Dly
8 2012-03-16 00:00:00.000 Treasury Extracts Dly
9 2012-03-16 00:00:00.000 Treasury Extracts Dly
25 2012-03-26 00:00:00.000 Treasury Extracts Dly
26 2012-03-26 00:00:00.000 Treasury Extracts Dly
27 2012-03-26 00:00:00.000 Treasury Extracts Dly
10 2012-03-19 00:00:00.000 Treasury Extracts Dly
11 2012-03-19 00:00:00.000 Treasury Extracts Dly
12 2012-03-19 00:00:00.000 Treasury Extracts Dly
16 2012-03-21 00:00:00.000 Treasury Extracts Dly
17 2012-03-21 00:00:00.000 Treasury Extracts Dly
18 2012-03-21 00:00:00.000 Treasury Extracts Dly
22 2012-03-23 00:00:00.000 Treasury Extracts Dly
23 2012-03-23 00:00:00.000 Treasury Extracts Dly
24 2012-03-23 00:00:00.000 Treasury Extracts Dly
28 2012-03-27 00:00:00.000 Treasury Extracts Dly
29 2012-03-27 00:00:00.000 Treasury Extracts Dly
30 2012-03-27 00:00:00.000 Treasury Extracts Dly
34 2012-03-29 00:00:00.000 Treasury Extracts Dly
35 2012-03-29 00:00:00.000 Treasury Extracts Dly
36 2012-03-29 00:00:00.000 Treasury 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