nvarchar vs varchar

Question

Post reply

nvarchar vs varchar

fec

SSC Eights!

Points: 941
More actions
May 12, 2008 at 5:31 pm

#182239

I am trying to get some more performance out of an existing stored procedure.
It uses some dynamic sql to alter a temp table and add some additional columns (only known at runtime).
My question is this - why use nvarchar? I am certain that it will only consist of english characters.
^{DECLARE @rateString nvarchar(4000)}
DECLARE @DynamicSQL NVARCHAR(4000)
CREATE TABLE #tblraw(
[LoadDate] [smalldatetime] NULL,
[LoadTime] [nvarchar](50) NULL
)
SET @DynamicSQL = 'ALTER TABLE #tblraw ADD ' + @DataloggerColumsSQL
EXEC(@DynamicSQL)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

A better question is why would anyone use a character based datatype to store a "Load time"?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

jaypee_s Right there with Babe Points: 793 More actions · Answer 2

Hi

Check this link...

http://qa.sqlservercentral.com/Forums/Topic183187-5-1.aspx#bm183979

Thanks

jaypee.s

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

Jeff Moden (5/12/2008)
A better question is why would anyone use a character based datatype to store a "Load time"?

And why one would want to store a data and a time in separate columns, when a datetime contains both.

Paul: As written, there's no reason for nvarchar. Perhaps sometime previously the developer was using sp_executesql instead of EXEC. sp_executesql takes a parameter of nvarchar.

Could also be a case of bigger = better

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Heh... can't wait for the mess folks are gonna make in 2k8 with the separate DATE and TIME datatypes...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

Jeff Moden (5/13/2008)
Heh... can't wait for the mess folks are gonna make in 2k8 with the separate DATE and TIME datatypes...

Agreed. There are legit cases to store just one of the other, but they're rare cases

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

fec SSC Eights! Points: 941 More actions · Answer 6

I completely agree, i had never known of any cases where the datetime had to be split - but for what ever reason (unknown to me) this database has it all over the place. Which makes dealing with dates and times harder work.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

GilaMonster (5/13/2008)
Jeff Moden (5/13/2008)
Heh... can't wait for the mess folks are gonna make in 2k8 with the separate DATE and TIME datatypes...
Agreed. There are legit cases to store just one of the other, but they're rare cases

Tell me when storing just one or the other would be better... I can see "using" just one or the other, but not storing them that way.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 8

One place I've used a time where I didn't want a date was in job monitoring. I have the latest time that a job may be completed before alerts get raised.

eg, if JobA has not completed by 3am, alert. I don't care which day at 3am, I just care about 3am.

It's doable with datetime, but would be easier if I could have just a time that I could compare.

As I said, rare case though.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

abhijeetv SSCommitted Points: 1945 More actions · Answer 9

talking about seperation of date and time datatypes(SQL 2008)

I am sure everybody's visit to http://qa.sqlservercentral.com/articles/News/3253/

in this regard......