Solved

MS SQL server Varchar and nvarchar, GMT_DATE

Posted on 2016-11-07
23
48 Views
Last Modified: 2016-11-22
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 ?
0
Comment
Question by:marrowyung
  • 9
  • 9
  • 3
  • +2
23 Comments
 
LVL 3

Assisted Solution

by:Daniel Jones
Daniel Jones earned 83 total points
Comment Utility
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
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 83 total points
Comment Utility
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
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 83 total points
Comment Utility
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
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:marrowyung
Comment Utility
I just don't understand why we have the DATE type but still need this.
0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
i am sorry that I only focus on the GMT_DATE type we have,.
0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
I am not sure why we use GTM_DATE instead of DATE.
0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
"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
 
LVL 16

Accepted Solution

by:
Pawan Kumar Khowal earned 251 total points
Comment Utility
--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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 
LVL 1

Author Closing Comment

by:marrowyung
Comment Utility
tks all.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now