MS SQL server Varchar and nvarchar, GMT_DATE

hi all,

is it say that nvarchar take up more space than varchar, how much more it takes?

other than GTM_DATE, another other DATE type which is smaller in size and can record GMT value ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
--Ok, I shall give you an example. Let's say you wanted to create an application where first name should be off 75 length at all the places.
--So for this there are 2 approaches of this.

1. At all places Put VARCHAR(75) like below but in the case any developer can increase / decrease accidently.

--

CREATE TABLE Info
(
	
	FirstName VARCHAR(75)
)

--

Open in new window


2. In 2nd approach we can specify the user defined type after the column name so the changes of length getting increase or decrease are none.

--

CREATE TYPE FN FROM VARCHAR(75) NOT NULL

CREATE TABLE Info
(
	
	FirstName FN
)

--

Open in new window


The length should be same across the entire application.

Hope it helps!
0
 
Daniel JonesConnect With a Mentor Data Research AnalystCommented:
is it say that nvarchar take up more space than varchar, how much more it takes?
Nvarchar stores unicode data and varchar stores ANSI (8-bit) data. Both function identically but nvarchar takes up twice as much space.
0
 
Pawan KumarDatabase ExpertCommented:
NVarchar[(n)] -- Unicode Variable Length character data type. It can store both non-Unicode and Unicode (i.e. Hindi, Chineese, Japanese, Korean ,, etc) characters.  

Example:

DECLARE @Name AS NVARCHAR(100)= N'पवन'
SELECT @Name

GO

Varchar[(n)] -- Non-Unicode Variable Length character data type.  

Example:

DECLARE @Name AS VARCHAR(100)= 'Pawan'
SELECT @Name
GO

 
Difference
-------------

DECLARE @Name1 AS NVARCHAR(100)= N'पवन'
DECLARE @Name2 AS VARCHAR(100)= N'पवन'
SELECT @Name1 , @Name2

Open in new window


Output
-------------

(No column name)      (No column name)
पवन                          ???


2nd question - other than GTM_DATE, another other DATE type which is smaller in size and can record GMT value is not clear ?



Is GTM_DATE DATETIME
and OtherDate DATE



Hope it helps !!
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
marrowyungSenior Technical architecture (Data)Author Commented:
Pawan Kumar Khowal,

"
Is GTM_DATE DATETIME
and OtherDate DATE

"
what is that mean ?

Daniel Jones,

" Both function identically but nvarchar takes up twice as much space."

but you agree on that:

NVarchar[(n)] -- Unicode Variable Length character data type. It can store both non-Unicode and Unicode (i.e. Hindi, Chineese, Japanese, Korean ,, etc) characters.  

right?
0
 
Pawan KumarDatabase ExpertCommented:
2nd question - other than GTM_DATE, another other DATE type which is smaller in size and can record GMT value is not clear ?

I have already given the example for the information I gave.  
You can also verify the information from Pinal's blog -  http://blog.sqlauthority.com/2015/02/25/sql-server-storing-data-in-hindi-chinese-gujarati-and-tamil-or-any-other-language-in-the-same-table/

Hope that helps !
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
I think the new EE question page is not clear, if you look above,  I see no link from you .

but that page is only about:

NVarchar[(n)] -- Unicode Variable Length character data type. It can store both non-Unicode and Unicode (i.e. Hindi, Chineese, Japanese, Korean ,, etc) characters.  

agree?
0
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
is it say that nvarchar take up more space than varchar, how much more it takes?
NVARCHAR takes the double size of a VARCHAR, i.e. a VARCHAR(1) takes 1 byte and a NVARCHAR(1) takes 2 bytes.

other than GTM_DATE, another other DATE type which is smaller in size and can record GMT value ?
There's no GTM_DATE data type. DATE is the smaller you can get and it stores GMT dates. If you want to explain what you need then we may help you better.
0
 
Pawan KumarDatabase ExpertCommented:
Just to append..
Varchar - Each character will take 1 Byte. If you have 5 characters then it will take 5 characters. i.e. VARCHAR(5)
NVarchar - Each character will take 2 Byte. If you have 5 characters then it will take 10 characters.  NVARCHAR(5)

You can verify how much space each takes using below example.

DECLARE @Name AS VARCHAR(50) = 'Pawan'
SELECT @Name Name, DATALENGTH(@Name) Lengthx
GO

Output
--------
Name      Lengthx
Pawan      5

DECLARE @Name AS NVARCHAR(50) = N'Pawan'
SELECT @Name Name, DATALENGTH(@Name) Lengthx
GO

Output
--------

Name      Lengthx
Pawan      10

Just an update if you need complete difference list between the 2 data types then refer below -

http://sqlhints.com/2011/12/23/difference-between-varchar-and-nvarchar/

Hope it helps!
0
 
ZberteocConnect With a Mentor Commented:
If you by GMT_DATE mean Universal Standard(Greenwich) Time then the function in SQL server is:

SELECT GETUTCDATE()

The local server time will be returned by:

SELECT GETDATE()

Both functions will return a date with time component. If you only need the date than you will have to cast:

SELECT CAST(GETDATE() as date)

respectively

SELECT CAST(GETUTCDATE() as date)

The results from these functions have nothing to do with varchar or nvarchar type but you can convert them to either:

SELECT
      convert(varchar(30), GETDATE(), 121) ,
      convert(Nvarchar(30), GETDATE(), 121),
      convert(varchar(30), GETUTCDATE(), 121),
      convert(Nvarchar(30), GETUTCDATE(), 121)
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
Victor,

that type is what we defined, see attached.

gmt-date-type.jpg
I am not sure how can we make a diff between the GMT_DATE and normal  DATE .
0
 
Pawan KumarDatabase ExpertCommented:
I am not sure how can we make a diff between the GMT_DATE and normal  DATE .

Can you explain how do you want to differentiate?
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
I just don't understand why we have the DATE type but still need this.
0
 
Pawan KumarDatabase ExpertCommented:
If you store only date without time  then you can use it.

Earlier we have DATETIME where we can store Date and time together.

In DATE data type we can only store Date.

Even in case of user defined data types you have to create user defined data type via system defined data types.

Hope it helps!
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
i am sorry that I only focus on the GMT_DATE type we have,.
0
 
Pawan KumarDatabase ExpertCommented:
GMT_DATE type is of DATETIME data type. It is a user defined data type - derived from Datetime system data type.

What's your question on that?
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
I am not sure why we use GTM_DATE instead of DATE.
0
 
Pawan KumarDatabase ExpertCommented:
GMT_DATE type is of DATETIME data type. It is a user defined data type - derived from Datetime system data type.

User-defined data types are used when multiple tables must store the same type of data in a column and we must ensure that these columns <<from multiple tables>> should have identical same data type, length, and Null functionality.

That is why the developer uses User-defined data types.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
that type is what we defined, see attached.
Oh man, you're talking about a user defined data type.

I am not sure why we use GTM_DATE instead of DATE.
You'll need to ask this question to who created the GTM_DATE. Btw, GTM_DATE is a datetime and not a date data type.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
"You'll need to ask this question to who created the GTM_DATE. Btw, GTM_DATE is a datetime and not a date data type."

ahhaha, know you will say this, I just want to get some sense here if this is normal,

from SQL SSMS, it said it is a user defined  date data type,... so I said that.

yeah, it is a datetime type all allow.

Pawan,

"GMT_DATE type is of DATETIME data type. It is a user defined data type - derived from Datetime system data type."

it if is user defined, we defined it, so no one should know it,  so are you team also use this too ?

"User-defined data types are used when multiple tables must store the same type of data in a column and we must ensure that these columns <<from multiple tables>> should have identical same data type, length, and Null functionality.
"

can't sure what exact it means, all table will have a chance to have a columns from data of other tables, right? this should not be the point of creating another type.

but I think it just make developer make up a type they want for their application, right ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
from SQL SSMS, it said it is a user defined  date data type,... so I said that.
SSMS doesn't lie. I'm not sure if I understand what's your doubt with this. Do you know what a user defined data type is, right?
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
tks all.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
yeah, that type just means we all reference the same type.

I just want to know it as I don't involve on any of this.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.