|
Home > Archive > MS SQL Data Warehousing > December 2005 > Varchar vs Nvarchar
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Varchar vs Nvarchar
|
|
| MChrist 2005-11-28, 11:23 am |
| 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
| |
| Adam Machanic 2005-11-28, 11:23 am |
| 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@microso
ft.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 2005-11-28, 11:23 am |
| 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@microso
ft.com...
>
>
>
| |
| David Browne 2005-11-28, 1:23 pm |
|
"MChrist" <MChrist@discussions.microsoft.com> wrote in message
news:66C53E73-90D7-4196-AB6F- 1FD09C880E33@microso
ft.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
| |
| Adam Machanic 2005-11-28, 1:23 pm |
| "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...
>
> 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
> --
>
>
>
| |
| Randall Arnold 2005-11-29, 8:23 pm |
| 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...
>
>
| |
| Adam Machanic 2005-11-29, 8:23 pm |
| "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...
>
>
>
> 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
> --
>
>
| |
| David Browne 2005-12-04, 11:23 am |
|
"Jéjé" < willgart@BBBhotmailA
AA.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
| |
| Adam Machanic 2005-12-04, 8:23 pm |
| "David Browne" <davidbaxterbrowne no potted meat@hotmail.com> wrote in
message news:%232u2stO%23FHA
.1988@TK2MSFTNGP12.phx.gbl...
>
> This is almost always a bad idea, and in SQL Server 2005 (with indexes
> with included columns) the "almost" goes away.
Don't forget row overflow! This still doesn't let us design tables with
an unlimited number of columns, but the number goes way up.
> You can do better by adding commonly used columns to the clustered index
> (still not always a good idea, but better than "horizontal partitioning".
I think you mean "vertical"? Horizontal, per my understanding, is the
kind supported by partitioned views in SQL Server 2000 and table/index
partitioning in SQL Server 2005 -- and that kind of partitioning is a good
thing, IMO.
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
| |
| David Browne 2005-12-04, 8:23 pm |
|
"Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message
news:u8D1jeR%23FHA.2708@TK2MSFTNGP12.phx.gbl...
> "David Browne" <davidbaxterbrowne no potted meat@hotmail.com> wrote in
> message news:%232u2stO%23FHA
.1988@TK2MSFTNGP12.phx.gbl...
>
> Don't forget row overflow! This still doesn't let us design tables
> with an unlimited number of columns, but the number goes way up.
>
>
>
> I think you mean "vertical"? Horizontal, per my understanding, is the
> kind supported by partitioned views in SQL Server 2000 and table/index
> partitioning in SQL Server 2005 -- and that kind of partitioning is a good
> thing, IMO.
>
>
Yes vertical. I was lying down when I wrote that.
David
|
|
|
|
|