size

Estimate Backup Size In Sql Server

by
Facebooktwitterredditpinterestlinkedinmail

When you’re creating your database backup maintenance plans, you need to choose a drive that has enough space for your backup. So how much space will you actually need to backup your database? Figuring this out is actually a lot easier than you would expect.

Although it won’t be 100% accurate, SQL Server has a built in stored procedure that will get you pretty close. You can use the sp_spaceused stored procedure to show you the amount of space used in your database. Now keep in mind that a full database backup only stores the actual data/objects in the database. The unused space is not stored in the backup.

USE zoobase
GO
EXEC sp_spaceused @updateusage = N'TRUE'
GO

Estimate Backup Size In SQL Server

In the example above we call the sp_spaceused stored procedure from the database that we want to get the backup size for. This stored procedure will return a couple datasets with multiple columns. The column that you will look at is named reserved. This will show you a good estimate of how large your full backup file will be.

 
More Info
The @updateusage parameter, in the example above, tells the stored procedure to update the space usage statistics before returning the database size information. Passing TRUE in to this parameter will give us the most accurate size estimate.

Varchar Vs Nvarchar

by
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.

List Of Data Types And Their Sizes

by
Facebooktwitterredditpinterestlinkedinmail

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

[table caption=”DateTime Data Types” th=”0″ width=”500″ colwidth=”150|150″]
date,3 bytes
datetime,8 bytes
datetime2,6 – 8 bytes (depending on precision)
datetimeoffset,10 bytes
smalldatetime,4 bytes
time,5 bytes
[/table]

[table caption=”Numeric Data Types” th=”0″ width=”500″ colwidth=”150|150″]
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
[/table]

[table caption=”String” th=”0″ width=”500″ colwidth=”150|150″]
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
[/table]

[table caption=”Binary” th=”0″ width=”500″ colwidth=”150|150″]
binary,size defined in table
varbinary,2 bytes + data size
image,2 bytes + data size
[/table]

[table caption=”Other” th=”0″ width=”500″ colwidth=”150|150″]
bit,1 byte
hierarchyid,5 bytes
uniqueidentifier,16 bytes
[/table]

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