By any chance, did you have a large amount of BLOB data that you've deleted? If so, the only way to reclaim that space is to BCP the data out...

Rereading your answer. You didn't say anything that a new column was added. You instead updated an already existing. Why do you think this will be stored separately?

I agree with Steve. But I would be very interested in a repro script.

Btw, did the "MS consultant" give an explanation?

Dynamic SQL

USE PUBS

DECLARE @stmt nvarchar(4000)

DECLARE @rowcount bigint

DECLARE @table nvarchar(255)

SET @table = 'authors'

SELECT @stmt = 'SELECT @count = COUNT(*) FROM ' + @table

EXEC sp_executesql...

There isn't a separate index on name. This column is at second position in another nonclustered indexes. So, I would say, this is somewhat expected behaviour.

Something along these lines?

declare @dt1 datetime, @dt2 datetime

select @dt1 = getdate(), @dt2 = getdate()-0.0833333333333333333 -- 2 hours earlier

select

 @dt1, @dt2,

 convert(varchar,dateadd(second,datediff(second,@dt2,@dt1),0),108)

Watch Gabor's code

declare @v-2 varchar(50)

set @v-2 = 'E'

select * from dbo.T_CUS_customer

where cus_code_char like @v-2 + '%'

There is no wildcard at the beginning, only at the end. So, if there...

You need to use SELECT not SET!

You can even assign more than one value to a variable at once

declare @a int, @b-2 int

select @a=min(Number), @b-2=max(Number) from dbo.Fruits

select @a, @b-2

If the table is a heap (no indexes) then the new column will be stored separately from the rest of the row, resulting in extra IO.

Can you expand on this?

(Re)Create the clustered index on the new filegroup and specify WITH DROP_EXISTING. That will move the table. In case of indexes you'll need to drop and recreate manually, AFAIK.

I know I found it just interesting that both cases return a different error message but yield the same result.

you can't have duplicate...

Sure, use IE and activate somewhere in your profile the use of the HTML Editor.

...or have a website you can link to.

It's interesting to see what happens when you change your script to

A int not null

constraint pk_test PRIMARY KEY NONCLUSTERED(a)

)

and let it run and

A int not null

constraint pk_test PRIMARY KEY NONCLUSTERED(a)

)

...

--CREATE...

Am I understanding you correctly?

SELECT * FROM table WHERE RIGHT(column,1)=CHAR(102)

or simply

SELECT * FROM table WHERE RIGHT(column,1)='f'

AND EntityPhoneNumbers.PhoneTypeID = 1

Since this is a column in the "unpreserved" table, it will turn the LEFT JOIN into an INNER JOIN and therefore doesn't yield the result you expect.