Please pardon my ignorance here, but I'm a newbie with MS SQL Server.

I have a database that contains a table with 33.6 million records. When I
built the table I used Nvarchar in several of the fields. Now I'm thinking
that because the db size is 7.5 gb, that I could reduce that by changing the
datatype to Varchar.

Since I'm not changing languages or porting hardware platforms is there a
good reason why I should keep the nvarchar data type other that it will take
an eternity to save the new table structure?

Does anyone have any suggestions as to which would be faster, building a new
table and importing the records via a query, or just changing the existing
datatype and saving the table.

TIA!

Mark

While I agree that you might not need NVARCHAR based on your description,
I'm not sure that it's something worth worrying about. 7.5 gigs is not a
lot of disk space... Why the concern?


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


"MChrist" <MChrist@discussions.microsoft.com> wrote in message
news:5653976D-FA2D-4E0C-AECA-DDEF7640E124@microsoft.com...
> Please pardon my ignorance here, but I'm a newbie with MS SQL Server.
>
> I have a database that contains a table with 33.6 million records. When I
> built the table I used Nvarchar in several of the fields. Now I'm
> thinking
> that because the db size is 7.5 gb, that I could reduce that by changing
> the
> datatype to Varchar.
>
> Since I'm not changing languages or porting hardware platforms is there a
> good reason why I should keep the nvarchar data type other that it will
> take
> an eternity to save the new table structure?
>
> Does anyone have any suggestions as to which would be faster, building a
> new
> table and importing the records via a query, or just changing the existing
> datatype and saving the table.
>
> TIA!
>
> Mark
>
>

I guess I've always felt smaller & tighter dbs are more efficient than larger
dbs. It's also a slow and timely process to transfer the backup to my laptop
where I do some development on.

Thanks for the comments.

Mark

"Adam Machanic" wrote:

> While I agree that you might not need NVARCHAR based on your description,
> I'm not sure that it's something worth worrying about. 7.5 gigs is not a
> lot of disk space... Why the concern?
>
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>
> "MChrist" <MChrist@discussions.microsoft.com> wrote in message
> news:5653976D-FA2D-4E0C-AECA-DDEF7640E124@microsoft.com...
> > Please pardon my ignorance here, but I'm a newbie with MS SQL Server.
> >
> > I have a database that contains a table with 33.6 million records. When I
> > built the table I used Nvarchar in several of the fields. Now I'm
> > thinking
> > that because the db size is 7.5 gb, that I could reduce that by changing
> > the
> > datatype to Varchar.
> >
> > Since I'm not changing languages or porting hardware platforms is there a
> > good reason why I should keep the nvarchar data type other that it will
> > take
> > an eternity to save the new table structure?
> >
> > Does anyone have any suggestions as to which would be faster, building a
> > new
> > table and importing the records via a query, or just changing the existing
> > datatype and saving the table.
> >
> > TIA!
> >
> > Mark
> >
> >
>
>
>

"MChrist" <MChrist@discussions.microsoft.com> wrote in message
news:66C53E73-90D7-4196-AB6F-1FD09C880E33@microsoft.com...
>I guess I've always felt smaller & tighter dbs are more efficient than
>larger
> dbs. It's also a slow and timely process to transfer the backup to my
> laptop
> where I do some development on.
>

I have to agree here. Although 7.6g is really not much, a database half
that size is significantly easier to manage and will perform better
automatically.

David

"David Browne" <davidbaxterbrowne no potted meat@hotmail.com> wrote in
message news:ODq7FGE9FHA.444@TK2MSFTNGP11.phx.gbl...
>
> I have to agree here. Although 7.6g is really not much, a database half
> that size is significantly easier to manage and will perform better
> automatically.

True. Smaller data means fewer data pages to read into memory when
retrieving rows, which means fewer I/O operations, which means better
performance. But the flip side is how long it might take to convert the
entire database... and if there's no performance problem already, it may not
be worthwhile.


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

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.

MC


"Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message
news:OW60XmE9FHA.2264@tk2msftngp13.phx.gbl...
> "David Browne" <davidbaxterbrowne no potted meat@hotmail.com> wrote in
> message news:ODq7FGE9FHA.444@TK2MSFTNGP11.phx.gbl...
>>
>> I have to agree here. Although 7.6g is really not much, a database half
>> that size is significantly easier to manage and will perform better
>> automatically.
>
> True. Smaller data means fewer data pages to read into memory when
> retrieving rows, which means fewer I/O operations, which means better
> performance. But the flip side is how long it might take to convert the
> entire database... and if there's no performance problem already, it may
> not be worthwhile.
>
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>
>

Yeah, I'd like to know myself. I'm fairly new to SQL Server too and just
had a huge database thrown at me to manage. Some queries are now timing out
so performance is key. It's worth it to me to convert fields where it will
help.

Randall Arnold

"MC" <marko_culo#@#yahoo#.#com#> wrote in message
news:OVfaiQL9FHA.3636@TK2MSFTNGP09.phx.gbl...
> 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.
>
> MC
>
>
> "Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message
> news:OW60XmE9FHA.2264@tk2msftngp13.phx.gbl...
>> "David Browne" <davidbaxterbrowne no potted meat@hotmail.com> wrote in
>> message news:ODq7FGE9FHA.444@TK2MSFTNGP11.phx.gbl...
>>>
>>> I have to agree here. Although 7.6g is really not much, a database half
>>> that size is significantly easier to manage and will perform better
>>> automatically.
>>
>> True. Smaller data means fewer data pages to read into memory when
>> retrieving rows, which means fewer I/O operations, which means better
>> performance. But the flip side is how long it might take to convert the
>> entire database... and if there's no performance problem already, it may
>> not be worthwhile.
>>
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>>
>>
>
>

"MC" <marko_culo#@#yahoo#.#com#> wrote in message
news:OVfaiQL9FHA.3636@TK2MSFTNGP09.phx.gbl...
> 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
--

have you try to partition your table horizontally?
create 1 table with the most requested columns, and a second table with your
"slow" varchar columns.

The row size in the first table will be smaller, so you'll have more rows by
page.

but, because you ask under the data warehouse newsgroup, remember that
reducing the number of joins improve the response time.

"Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message
news:ehtCA5T9FHA.132@TK2MSFTNGP15.phx.gbl...
> "MC" <marko_culo#@#yahoo#.#com#> wrote in message
> news:OVfaiQL9FHA.3636@TK2MSFTNGP09.phx.gbl...
>> 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
> --
>
>

"Jéjé" <willgart@BBBhotmailAAA.com> wrote in message
news:OOvqXIN%23FHA.2472@TK2MSFTNGP12.phx.gbl...
> have you try to partition your table horizontally?
> create 1 table with the most requested columns, and a second table with
> your "slow" varchar columns.
>
> The row size in the first table will be smaller, so you'll have more rows
> by page.
>
> but, because you ask under the data warehouse newsgroup, remember that
> reducing the number of joins improve the response time.
>

This is almost always a bad idea, and in SQL Server 2005 (with indexes with
included columns) the "almost" goes away.

You can do better by adding commonly used columns to the clustered index
(still not always a good idea, but better than "horizontal partitioning".

David