Re: Varchar vs Nvarchar

"MC" <marko_culo#@#yahoo#.#com#> wrote in message
news:OVfaiQL9FHA.3636@xxxxxxxxxxxxxxxxxxxxxxx
> To get back to the original question. If he decides to change datatype,
> what is faster? Altering the table or creating new/importing data?
> My guess would be that altering the table would be a better solution.



Well, the answer according to this -very simple- test is that creating
the new table is somewhat faster (but this test doesn't take things like
indexes into account -- so YMMV):

---
use tempdb
go

SET NOCOUNT ON

create table x (blah nvarchar(400))
go

insert x (blah)
select top 5000
replicate('x', 400)
from
master..spt_values a,
master..spt_values b
go

declare @starttime datetime
set @starttime = getdate()

alter table x
alter column blah varchar(400)

PRINT datediff(ms, @starttime, getdate())
GO

DROP TABLE x
GO

create table x (blah nvarchar(400))
go

insert x (blah)
select top 5000
replicate('x', 400)
from
master..spt_values a,
master..spt_values b
go

declare @starttime datetime
set @starttime = getdate()

create table y (blah nvarchar(400))

insert y (blah)
select blah
from x

drop table x

exec sp_rename 'y', 'x'

PRINT datediff(ms, @starttime, getdate())
GO

drop table x
go
---


--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--


.

Relevant Pages

  • Re: Varchar vs Nvarchar
    ... > create table x (blah nvarchar(400)) ... > declare @starttime datetime ... > alter column blah varchar ... > PRINT datediff(ms, @starttime, getdate()) ...
    (microsoft.public.sqlserver.datawarehouse)
  • [C#] How to measure the time of your codes execution
    ... TimeSpan duration; ... startTime = DateTime.Now; ... //Blah, blah ... - Sigmund Freud ...
    (comp.software.testing)
Loading