INSERT INTO not working in SQL 2005 (x64)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

INSERT INTO not working in SQL 2005 (x64)

Author

Topic

umertahir
Posting Yak Master

154 Posts

Posted - 2008-08-19 : 11:47:10

Hi,

I am having a problem related to INSERT INTO on the following platform:
Platform 1:
Microsoft SQL Server 2005 - 9.00.3068.00 (X64) Feb 26 2008 23:02:54 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2).

However, if I use the same query on different platform, shown below, it works fine:
Platform 2:
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2).

My Query:
1) Drop and Create table
2) add index to its columns:


CREATE UNIQUE NONCLUSTERED INDEX [idxGroup4AndInseminationDate] ON [dbo].[tblForInseminationContainingHUKAndNMRDataWithNoDuplicates] 
(
	[herdBookNumber] ASC,
	[breedId] ASC,
	[IDType] ASC,
	[pedigreeStatus] ASC,
	[inseminationServiceDate] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

3) Insert data


INSERT INTO [DBO].[tblForInseminationContainingHUKAndNMRDataWithNoDuplicates]
SELECT *
FROM dbo.tblForInseminationContainingHUKAndNMRData AS tblA
WHERE	tblA.herdBookNumber	= 'E5854/00220'	AND
		tblA.breedId	= '1' AND
		tblA.IDType		= '2' AND
		tblA.pedigreeStatus	= '2' AND
		tblA.inseminationServiceDate= '2001-03-16 00:00:00.000'	
ORDER BY ISNULL(tblA.authenticSire, 'N') DESC, tblA.PDResult DESC

In expected result the order by works fine on the second platform but not on the first one, please help.

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-19 : 11:49:50

What is the Error?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-19 : 11:52:19

I think you have a "problem" with parallellism.
Check out the execution plans.

E 12°55'05.25"
N 56°04'39.16"

umertahir
Posting Yak Master

154 Posts

Posted - 2008-08-19 : 11:57:46

quote:
Originally posted by sodeep

What is the Error?

There is no such error but the data which should be inserted as the result of ORDER BY should be the one shown in 'expected row':

The unexpected result on platform1 is the following row:
3711 1205223 E5854/00220 1 2 2 F NULL NULL 3 2001-03-16 00:00:00.000 NULL 000009001000 01 0 1 1 1 NULL NULL HUK

Expected row is:
2963 539 E5854/00220 1 2 2 F 1 A 3 2001-03-16 00:00:00.000 Y 9001000 1 0 29 1 1 Y 1 NMR

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 12:01:18

why are you using this in order by?
ISNULL(tblA.authenticSire, 'N')

was your intention to bring NULL values to last?

umertahir
Posting Yak Master

154 Posts

Posted - 2008-08-19 : 12:01:37

quote:
Originally posted by Peso

I think you have a "problem" with parallellism.
Check out the execution plans.

E 12°55'05.25"
N 56°04'39.16"

Yes, the execution plan is different on both machines. How do I fix it?

umertahir
Posting Yak Master

154 Posts

Posted - 2008-08-19 : 12:02:36

quote:
Originally posted by visakh16

why are you using this in order by?
ISNULL(tblA.authenticSire, 'N')

was your intention to bring NULL values to last?

That's exactly what I want to do as i have either 'Y' or NULL in that column so I want to bring the one with 'Y' at the top.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-19 : 12:04:07

Hard to tell without seeing the execution plans.
Do a "SET SHOWPLAN_TEXT ON" and then execute the query and post the execution plan here between [ code ] tags.

Maybe a simple

OPTION (MAXDOP 1)

at the end of query will do?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-19 : 12:04:41

quote:
Originally posted by umertahir

That's exactly what I want to do as i have either 'Y' or NULL in that column so I want to bring the one with 'Y' at the top.

ORDER BY tblA.authenticSire DESC

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 12:08:17

quote:
Originally posted by umertahir

quote:
Originally posted by visakh16

why are you using this in order by?
ISNULL(tblA.authenticSire, 'N')

was your intention to bring NULL values to last?

That's exactly what I want to do as i have either 'Y' or NULL in that column so I want to bring the one with 'Y' at the top.

no need of using isnull. just do as Peso suggested.I was not sure what values will in the field thats why i asked.

umertahir
Posting Yak Master

154 Posts

Posted - 2008-08-19 : 12:10:09

quote:
Originally posted by Peso

quote:
Originally posted by umertahir

That's exactly what I want to do as i have either 'Y' or NULL in that column so I want to bring the one with 'Y' at the top.
ORDER BY tblA.authenticSire DESC

E 12°55'05.25"
N 56°04'39.16"

I have tried that but it doesn't work, for that reason I had to use ISNULL.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 12:15:12

quote:
Originally posted by umertahir

quote:
Originally posted by Peso

quote:
Originally posted by umertahir

That's exactly what I want to do as i have either 'Y' or NULL in that column so I want to bring the one with 'Y' at the top.
ORDER BY tblA.authenticSire DESC

E 12°55'05.25"
N 56°04'39.16"

I have tried that but it doesn't work, for that reason I had to use ISNULL.

why?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-19 : 14:27:02

Did you try the MAXDOP query hint?

E 12°55'05.25"
N 56°04'39.16"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-08-19 : 17:04:25

Are you actually getting the wrong row inserted or are you expecting the order that you INSERT to be the maintained when you do a SELECT out of the newly inserted table? I'm sort of confused because it looks like both rows (good and bad) both meet the selection critereon you specified on you INSERT statement.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-08-19 : 17:23:54

>>or are you expecting the order that you INSERT to be the maintained when you do a SELECT out of the newly inserted table?

exactly! If you want the table to store the data in some physical order then you need a CLUSTERED index. Even with a clustered index if you want to insure the order of a SELECT out of the table you still need an ORDER BY clause.

Be One with the Optimizer
TG

umertahir
Posting Yak Master

154 Posts

Posted - 2008-08-20 : 04:32:01

quote:
Originally posted by TG

>>or are you expecting the order that you INSERT to be the maintained when you do a SELECT out of the newly inserted table?

exactly! If you want the table to store the data in some physical order then you need a CLUSTERED index. Even with a clustered index if you want to insure the order of a SELECT out of the table you still need an ORDER BY clause.

Be One with the Optimizer
TG

Hey TG,

changing the index from NONCLUSTERED to CLUSTERED has done the job but I'm not sure how it works, can you please explain?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-08-20 : 08:26:16

For years the standard sql developer interview question has been, "what's the difference between a clustered and non-clustered index?"

. The most accurate answer I could give is to please read about it in Books Online. It will give a better answer than I can. But basically, a table without a clustered index is sort of like a big pile of unsorted data (a heap). A clustered index makes the table become an ordered stack of data. How you choose what indexes to create and which ONE should be the clustered is not trivial and should be geared towards what gives the best performance for the majority of requests - and the least negative impact on the rest. And again, it would be a mistake to rely on the clustered index to guarantee the order of a SELECT. Only an ORDER BY can do that.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources