Varchar Vs Nvarchar

Facebooktwitterredditpinterestlinkedinmail

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

Facebooktwitterredditpinterestlinkedinmail

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.

EXEC	sp_rename 'Animals.AnimalName', 'AnimalRealName', 'COLUMN'

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

How To Create A Date Table

Facebooktwitterredditpinterestlinkedinmail

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.

CREATE
TABLE	DateTable
		(DateValue	DATETIME)

GO

DECLARE @StartDate		DATETIME = '01/01/2014'
DECLARE @NumberOfDays	INT = 3000

INSERT
INTO	DateTable
		(DateValue)
SELECT	DATEADD(DAY, RowNum, @StartDate) AS DateValue
FROM	(SELECT	TOP (@NumberOfDays)
				p1.PARAMETER_ID,
				ROW_NUMBER() OVER (ORDER BY p1.PARAMETER_ID) - 1 AS RowNum
		 FROM	sys.all_parameters p1
		 CROSS	JOIN sys.all_parameters p2) NumberTable

 

SQL Server Synonyms – What They Are And How To Use Them

Facebooktwitterredditpinterestlinkedinmail

What Is A Synonym?

A synonym in SQL Server can be thought of like having another name for a table.  The table doesn’t even have to be in your own database.  The intent of having synonyms is for an easy way to reference objects in another database.  It basically gives you a short alias to call server objects that live in another database.  You can create a synonym for tables, views, stored procedures, and functions!

 
Example Without Synonym:

SELECT	*
FROM	FarmAnimals.dbo.SoundsOfAnimals

 
Example With Synonym:

SELECT	*
FROM	AnimalSounds

In the example above, the synonym AnimalSounds represents FarmAnimals.dbo.SoundsOfAnimals.

 
NOTE: Because the synonym is basically an alias for a different server object, INSERT, UPDATE, SELECT, DELETE operations happen directly on the base table and not on the synonym.

 
Why Would You Use A Synonym?

There are 2 main reasons for using synonyms.  The first reason is to make the code a little easier to read.  It makes all of the remote objects look like local objects.  The second reason is that it creates a layer of abstraction.  If the base table changes, then you only need to change the synonym.  If you did not use a synonym, then you would need to go in to every stored procedure, view, function, etc… and change the reference to the new table.

 
Real-Life Use

So… although it is a neat concept, I really don’t use synonyms very much.  The one application where I see it very useful is when you do not own the database that you are referencing (like if you bought a 3rd party product).  Product updates could change the table/stored procedure names… if you use a synonym to reference the database tables, then it would only take 1 change on your side to get your program working again.

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