All Visual Studio blogs in one place

AddThis Social Bookmark Button
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. 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 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')
...(Read whole news on source site)



Enter your email address:

Delivered by FeedBurner

Increase your website traffic with


Anti-spam: How many eyes has a typical person?

Follow us on FB

Home : Blog List : Chintak's Blog : One more example - No order guaranteed if order by is not specified