Solved

MS SQL server Varchar and nvarchar, GMT_DATE

Posted on 2016-11-07
23
63 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 4

Assisted Solution

by:Daniel Jones
Daniel Jones earned 83 total points
ID: 41878134
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 24

Expert Comment

by:Pawan Kumar
ID: 41878158
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
ID: 41878191
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 24

Expert Comment

by:Pawan Kumar
ID: 41878197
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
ID: 41878204
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 24

Expert Comment

by:Pawan Kumar
ID: 41878207
0
 
LVL 46

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 83 total points
ID: 41878253
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 24

Expert Comment

by:Pawan Kumar
ID: 41878267
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
ID: 41880672
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
ID: 41883217
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 24

Expert Comment

by:Pawan Kumar
ID: 41883225
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 1

Author Comment

by:marrowyung
ID: 41883227
I just don't understand why we have the DATE type but still need this.
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41883229
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
ID: 41883272
i am sorry that I only focus on the GMT_DATE type we have,.
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41883280
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
ID: 41883283
I am not sure why we use GTM_DATE instead of DATE.
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41883301
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 46

Expert Comment

by:Vitor Montalvão
ID: 41883414
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
ID: 41887318
"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 24

Accepted Solution

by:
Pawan Kumar earned 251 total points
ID: 41887332
--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 46

Expert Comment

by:Vitor Montalvão
ID: 41887992
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
ID: 41897182
tks all.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41897183
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

910 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

16 Experts available now in Live!

Get 1:1 Help Now