Varchar Vs Nvarchar

Facebooktwittergoogle_plusredditpinterestlinkedinmail

People often want to know the difference between varchar vs nvarchar. If you are new to nvarchar, I don’t blame you for not understanding the difference… as they are very similar.

The major difference is that nvarchar stores data as Unicode, where varchar does not. This uses more space, but provides flexibility to handle foreign characters that would not fit in a varchar field.

 
How Varchar And Nvarchar Are Similar

  • They are both used to store text/string data in them
  • The amount of space that both use depends on the size of the data that you are putting in it. It grows with every character that you store.

 
How Varchar And Nvarchar Are Different

  • Nvarchar stores data as Unicode. Varchar stores data as non-Unicode.
  • Varchar stores data at 1 byte per character. Nvarchar stores data at 2 bytes per character.
  • Varchar supports up to 8000 characters in the field definition. Nvarchar only supports up to 4000 characters. Varchar(max) and nvarchar(max) not included.

 
My Thoughts On Which One To Use
Where I am at, I deal with a lot of data. Although it would be nice to be able to support all foreign characters in the database, it is likely that I will never get it. My philosophy is that unless you need to support these extended Unicode characters, just make the field varchar. Nvarchar uses double the space as varchar. Unless your business has a requirement for this, just save the space.

SQL Server Rename Column

Facebooktwittergoogle_plusredditpinterestlinkedinmail

This article will cover how to rename a column in SQL Server. The function of a SQL Server rename column is not performed too often. Usually we do this when we roll out a new product and we want to make a column more generic.

 
SQL Server Rename Column (SQL Server 2005 and beyond)

They make this super simple. Just remember to put to single quotes around the parameters.

The above SQL Server rename column code snippet has a few parts to it. Here is the breakdown:

  • sp_rename – This is a system stored procedure will rename different types of objects in SQL Server.
  • ‘Animals.AnimalName’ – This is the tablename and column that I would like to rename.
  • ‘AnimalRealName’ – This is the new name that I would like to call the column.
  • ‘COLUMN’ – This is the type of object that you would like to rename is. To rename a column, always leave this value ‘COLUMN’.

 
If you would like to know how to do a SQL Server rename column for SQL Server versions prior to 2005, please leave a note in the comments.

 
 
Reference: http://msdn.microsoft.com/en-us/library/ms188351(v=sql.90).aspx

SQL Server 2014 Feature – Non-Clustered Indexes For Table Variables

Facebooktwittergoogle_plusredditpinterestlinkedinmail

This is truly an amazing feature to add to SQL Server 2014. I use table variables all the time and this was the only thing I didn’t like about them. Up to this point, SQL Server did not support having non-clustered indexes on table variables (the one with the name that starts with the @). If you wanted to do this, you had to create/use a temp table (the one with the name that starts with #).

With SQL Server 2014, they changed this and now allow the table variables to have non-clustered indexes as well. To do this, we simply add a little bit of extra code after the column declaration.

In the sample above, we create a table variable called @AnimalTableVar. Then when we declare the column AnimalName, we add an index called IX_AnimalTempTable_AnimalName.

Here is how selecting from this table shows up in the execution plan!
Non-Clustered Indexes For Table Variables

Reference: http://msdn.microsoft.com/en-us/library/ms188927(v=sql.120).aspx

How To Create A Date Table

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Many times when developing SQL Server databases you have the requirement to show all dates in a range. One way to do this is with a date table. One of the most common reasons that I use a date table is when I have a reconciliation report. In a reconciliation report I want to show all of the days in a date range, even if they don’t have data on those days. SQL Server doesn’t have a function to generate dates in a range. So to accomplish this, I create a date table using the following script.

 

List Of Data Types And Their Sizes

Facebooktwittergoogle_plusredditpinterestlinkedinmail

The below table shows the storage sizes of the different data types in SQL Server.

DateTime Data Types
date 3 bytes
datetime 8 bytes
datetime2 6 – 8 bytes (depending on precision)
datetimeoffset 10 bytes
smalldatetime 4 bytes
time 5 bytes

 

Numeric Data Types
decimal 5 – 17 bytes
numeric 5 – 17 bytes
float 4 or 8 bytes
real 4 bytes
bigint 8 bytes
int 4 bytes
smallint 2 bytes
tinyint 1 byte
money 8 bytes
smallmoney 4 bytes

 

String
char size defined in table
varchar 2 bytes + data size
text data size
nchar 2 times size defined in table
nvarchar 2 bytes + 2 times data size
ntext 2 times data size

 

Binary
binary size defined in table
varbinary 2 bytes + data size
image 2 bytes + data size

 

Other
bit 2 bytes
hierarchyid 5 bytes
uniqueidentifier 16 bytes

Reference: http://msdn.microsoft.com/en-us/library/ff848794.aspx