Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Welcome to my blog. I work as Database lead at Synaptic Digital. Hope you find some interesting stuff here.
Browse by Tags · View All
BRH 17
SQL Server 15
#SQL Server 11
#BI 10
#TSQL 8
TSQL 8
BI 7
SSRS 6
#SQLServer 6
SSRS 2008R2 5

Archive · View All
January 2011 6
December 2010 5
September 2012 4
May 2012 4
March 2011 4
November 2012 2
October 2012 2
January 2012 2
February 2011 2
November 2010 2

One more example - No order guaranteed if order by is not specified

Apr 13 2012 7:43PM by Chintak Chhapia   

As we know that, No order guaranteed if order by is notf  explcitly specified. Below is one more example of this.

Below is the code to pupolate dummy table.

If OBJECT_ID('dbo.testTable') is not null 
	drop table dbo.testTable
Go
Create table dbo.testTable
( c1 int not null,
c2 int not null,
c3 int not null,
c4 int not null)
go
Alter table dbo.testTable add constraint pk_testTable primary key (c1)
go
declare @i int
select @i = 1

set nocount on;
begin tran

while (@i < 1001)
begin
	insert into testTable (c1,c2,c3,c4)
	select @i, @i/2, 10000-(@i/4), @i%2

	select @i = @i + 1
end
commit
go

Now, when we run below query, have a look at rows returned

select * from testTable

Below is the truncated result of this query.

Order By Example

Now, if we create covering below index and again look at the results, rows are returned in different orders.

Create index idx_testTable_c3 on testTable(c3) include (c1,c2,c4) 
go 
select * from testTable

Below is the result returned after creating index

Order By example 2

Actually, SQL choose the plan which is the more efficient, when we run below queries, we are able to find the reason.

set statistics io on 
go 
select * from testTable with(index=0) 
go 
select * from testTable go

Even we can look into why there are less pages when nonclustered index is used, with help of below query

select * from sys.dm_db_index_physical_stats(db_id(),object_id('testTable'),NULL,NULL,'DETAILED')

Tags: sql, order by,


Chintak Chhapia
40 · 5% · 1457
5
 
0
Lifesaver
 
 
0
Learned
 
0
Incorrect



Submit

2  Comments  

  • This is a very common misconception - Thanks for bringing this up.

    commented on Apr 14 2012 10:36AM
    Jacob Sebastian
    1 · 100% · 32004
  • Nice post Chintak. Thanks for sharing

    commented on May 2 2012 10:52PM
    Hardik Doshi
    20 · 9% · 2839

Your Comment


Sign Up or Login to post a comment.

"One more example - No order guaranteed if order by is not specified" rated 5 out of 5 by 5 readers
One more example - No order guaranteed if order by is not specified , 5.0 out of 5 based on 5 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]