[mssql] Data Type Performance Tuning Tips

Always specify the narrowest columns you can. The narrower the column, the less amount of data SQL Server has to store, and the faster SQL Server is able to read and write data. In addition, if any sorts need to be performed on the column, the narrower the column, the faster the sort will be.

*****

If you need to store large strings of data, and they are less than 8,000 characters, use a VARCHAR data type instead of a TEXT data type. TEXT data types have extra overhead that drag down performance.

*****

If you have a database running on SQL Server 7.0, 2000, or 2005 that used to run under version 6.5, and because of the limited row size had to split a column into two or more columns because the column width exceed what SQL Server version 6.5 was able to support, consider altering the table so that the multiple columns now fit back into one column again. This of course assumes that your column width is 8,000 characters or less for ASCII data. This will reduce server overhead and boost performance.

*****

Don't use the NVARCHAR or NCHAR data types unless you need to store 16-bit character (Unicode) data. They take up twice as much space as VARCHAR or CHAR data types, increasing server I/O and wasting unnecessary space in your buffer cache.

*****

If the text data in a column varies greatly in length, use a VARCHAR data type instead of a CHAR data type. The amount of space saved by using VARCHAR over CHAR on variable length columns can greatly reduce I/O reads cache memory used to hold data, improving overall SQL Server performance.

Another advantage of using VARCHAR over CHAR columns is that sorts performed on VARCHAR columns are generally faster than on CHAR columns. This is because the entire width of a CHAR column needs to be sorted.

*****

If a column's data does not vary widely in length, consider using a fixed-length CHAR field instead of a VARCHAR. While it may take up a little more space to store the data, processing fixed-length columns is faster in SQL Server than processing variable-length columns.

*****

Always choose the smallest data type you need to hold the data you need to store in a column. For example, if all you are going to be storing in a column are the numbers 1 through 10, then the TINYINT data type is more appropriate that the INT data type. The same goes for CHAR and VARCHAR data types. Don’t specify more characters in character columns that you need. This allows you to store more rows in your data and index pages, reducing the amount of I/O needed to read them. It also reduces the amount of data moved from the server to the client, reducing network traffic and latency. And last of all, it reduces the amount of wasted space in your buffer cache.

*****

If you have a column that is designed to hold only numbers, use a numeric data type, such as INTEGER, instead of a VARCHAR or CHAR data type. Numeric data types generally require less space to hold the same numeric value as does a character data type. This helps to reduce the size of the columns, and can boost performance when the columns is searched (WHERE clause), joined to another column, or sorted.

*****

Don't use FLOAT or REAL data types for primary keys, as they add unnecessary overhead that hurts performance. Use one of the integer data types instead.

*****

When specifying data types during table creation, always specify NULL or NOT NULL for each column. If you don't, then the column will default to NOT NULL if the ANSI NULL DEFAULT database option is not selected (the default), and will default to NULL of the ANSI NULL DEFAULT database option is selected.

For best performance, and to reduce potential code bugs, columns should ideally be set to NOT NULL. For example, use of the IS NULL keywords in the WHERE clause makes that portion of the query non-sargable, which means that portion of the query cannot use an index.

*****

If you are using fixed length columns (CHAR, NCHAR) in your table, do your best to avoid storing NULLs in them. If you do, the entire amount of space dedicated to the column will be used up. For example, if you have a fixed length column of 255 characters, and if you place a NULL in it, then 255 characters have to be stored in the database. This is a large waste of space that will cause SQL Server to have to perform extra disk I/O to read data pages. It also wastes space in the data cache buffer. Both of these contribute to reduced SQL Server performance.

Instead of using NULLs, use a coding scheme similar to this in your databases:

• NA: Not applicable
• NYN: Not yet known
• TUN: Truly unknown

Such a scheme provides the benefits of using NULLs, but without the drawbacks.

If you really must use NULLs, use a variable length column instead of a fixed length column. Variable length columns only use a very small amount of space to store a NULL.

*****

If you use the CONVERT function to convert a value to a variable length datatype, such as VARCHAR, always specify the length of the variable datatype. If you do not, SQL Server assumes a default length of 30. Ideally, you should specify the shortest length to accomplish the required task. This helps to reduce memory use and SQL Server resources.

[mssql] About Data Type

Exact Numbers

• bigint Range: -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
  Space: 8 Bytes
  
  
• int Range: -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
  Space: 4 Bytes
  
  
• smallint Range: -2^15 (-32,768) to 2^15-1 (32,767)
  Space: 2 Bytes
  
  
• tinyint Range: 0 to 255
  Space: 1 Byte
  
  
• bit Range: 0 (FALSE) or 1 (TRUE)
  Space: 8 bit columns in a table, will be collectively stored as: 1 Byte
  9 - 16 bit columns in a table, will be collectively stored as: 2 Bytes, etc.
  
  
• decimal Declaration: decimal(p[,s]).
  p = Precision - total number of digits stored to both the left and right of the decimal point.
  s = Scale the maximum number of digits stored to the right of the decimal point (optional).
  
  Precision 1 - 9: Storage is 5 bytes
  Precision 10 - 19: Storage is 9 bytes
  Precision 20 - 28: Storage is 13 bytes
  Precision 29 - 38: Storage is 17 bytes
  
  Minimum Precision is 1 and Maximum Precision is 38. The Default Precision is 18.
  Note: Decimal is equivalent to Numeric.
  
  
• numeric Declaration: numeric(p[,s]).
  p = Precision - total number of digits stored to both the left and right of the decimal point.
  s = Scale the maximum number of digits stored to the right of the decimal point (optional).
  
  Precision 1 - 9: Storage is 5 bytes
  Precision 10 - 19: Storage is 9 bytes
  Precision 20 - 28: Storage is 13 bytes
  Precision 29 - 38: Storage is 17 bytes
  
  Minimum Precision is 1 and Maximum Precision is 38. The Default Precision is 18.
  Note: Numeric is equivalent to Decimal.
  
  
• money Range: -922,337,203,685,477.5808 to 922,337,203,685,477.5807
  Space: 8 bytes
  
  
• smallmoney Range: -214,748.3648 to 214,748.3647
  Space: 4 bytes

Approximate Numbers

• float Declaration: float(n).
  n = the number of bits used to store the floating point number.
  
  Range: -1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308
  
  n Value 1 - 24: Precision - 7 digits: Space - 4 bytes
  n Value 25 - 53: Precision - 15 digits: Space - 8 bytes
  
  
• real Range: -3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38
  Space: 4 bytes
  
  Note: Real is equivalent to float(24).

Date and Time

• datetime Range: January 1, 1753, through December 31, 9999
  Accuracy: 3.33 ms Space: 8 bytes (two 4 byte integers). First 4 bytes represent the number of days before or after Jan. 1, 1900. The Second 4 bytes store the time of day as a number 1/3000-second units after 12:00 AM (00:00:00).
  
  
• smalldatetime Range: January 1, 1900, through June 6, 2079
  Accuracy: 1 min Space: 4 bytes (two 2 byte integers). First 2 bytes represent the number of days after Jan. 1, 1900. The Second 2 bytes store the number of minutes after 12:00 AM (00:00:00).

Character Strings

• char Definition: Fixed-Length character string.
  Declaration: char(n).
  n = the number of characters.
  
  Space: n number of bytes
  
  Valid lengths for a char datatype are 1 through 8,000.
  
  
• varchar Definition: Variable-Length character string.
  Declaration: varchar(n | max).
  n = the number of characters.
  
  Space: characters actually used in datatype (1 byte per character) + 2 additional bytes
  
  Valid lengths for a varchar datatype are 1 through 8,000. Alternatively, MAX allows for a much larger maximum storage size (2^31-1 bytes - 2,147,483,647 characters).
• text Definition: Variable-Length character string in the code page of the server.
  
  Maximum length is 2,147,483,647 characters.
  Note: will be removed in future versions. Use varchar(max) instead.

Unicode Character Strings

• nchar Definition: Fixed-Length Unicode character string.
  Declaration: nchar(n).
  n = the number of characters.
  
  Space: n * 2 number of bytes
  
  Valid lengths for a char datatype are 1 through 4,000.
  
  
• nvarchar Definition: Variable-Length Unicode character string.
  Declaration: nvarchar(n | max).
  n = the number of characters.
  
  Space: characters actually used in datatype (2 bytes per character) + 2 additional bytes
  
  Valid lengths for a varchar datatype are 1 through 4,000. Alternatively MAX indicates that the maximum storage size is much larger (2^31-1 bytes - 2,147,483,647 characters).
  
  
• ntext Definition: Variable-Length character string in the code page of the server.
  
  Maximum length is 1,073,741,823 characters.
  Note: will be removed in future versions. Use nvarchar(max) instead.

Binary Strings

• binary Definition: Fixed-Length binary data.
  Declaration: binary(n).
  
  Space: n number of bytes.
  Maximum length is 8000 bytes.
  
  
• varbinary Definition: Variable-Length binary data.
  Declaration: varbinary(n | max).
  n = the number of characters.
  
  Space: actual number of bytes stored in datatype + 2 additional bytes
  
  Valid lengths for a varbinary datatype are 1 through 8,000. Alternatively, MAX allows for a much larger maximum storage size (2^31-1 bytes - 2,147,483,647 bytes).
  
  
• image Definition: Variable-Length binary data.
  
  Maximum length is 2,147,483,647 bytes.
  
  Note: will be removed in future versions. Use varbinary(max) instead.
  
  

[mssql] Datetime Data Type

SQL Server's datetime data type generates a lot of questions and confusion in the SQL Server community. Unlike some other major database platforms, SQL Server doesn't provide discrete date and time data types. Instead, SQL Server's datetime data type does the work of both. Here are answers to six commonly asked questions about how to use SQL Server's datetime data type.

6. How does SQL Server store the datetime data type?

SQL Server uses 8 bytes to store the datetime data type. The first 4 bytes make up an integer value that represents the number of days since January 1, 1900. The second 4 bytes are an integer value that represents the number of milliseconds since midnight.

5. How do I retrieve rows based on datetime values?

SQL Server recognizes date and time data enclosed in single quotes. You can couple date and time values together or use them independently. You can also combine character date formats ('May 15, 2004 4 am'), numeric date formats ('5/15/2004 04:30'), or contiguous string formats ('20040515') with standard <, >, or = operators, as the following example shows:

SELECT * FROM orders WHERE OrderDate < 'May 15, 2004'

4. How do I retrieve only the date or time portion of the data?

You can use T-SQL's DATEPART() function to return a subset of the values that SQL Server's datetime columns store. The DATEPART() function uses two arguments. The first argument specifies the portion of the date that you want, and the second value specifies the datetime column:

SELECT orderID, DATEPART(MM,OrderDate) AS OrderMonth FROM Orders

3. How do I insert a value into a datetime column?

To insert values into a datetime column, you need to enclose the values in single quotes, then use one of SQL Server's date formats to supply the date value that you want to insert. For example:

DECLARE @MyTable TABLE
(MyDateTime DATETIME)
INSERT INTO @MyTable VALUES ('May 15, 2004 11:25am')

2. How do I find the day of the week?

Using the weekday argument as its first parameter, SQL Server's DATEPART() function returns the day of the week, returning 1 for Sunday, 2 for Monday, and so on. The following example uses the GETDATE() function combined with the DATEPART() function to retrieve the current day value:

SELECT DATEPART(weekday, GETDATE())

1. How can I find the last day of the month?

You can combine T-SQL's DATEADD() and DATEDIFF() functions to calculate different date and time values. Subtract 5ms from the first day of the next month to find the last day of the current month:

SELECT DATEADD(ms,-5,DATEADD(mm, DATEDIFF(m,0,GETDATE()  )+1, 0))

[mssql] How to add a default value (constraint) to an existing column in SQL using transact SQL

I had an issue recently where rows were not being inserted into one of our SQL tables and I figured out that it was because one of the columns does not allow null values [for good reason]. When originally designed we did not expect the particular field to be submitted without a value, but now we have encountered just such a scenario. Instead of allowing null values in the column we decided it would be more appropriate to have a default value of "None."

We install everything via scripts so that means for me to roll out the fix to production I had to code the update in transact SQL. Add to that the fact that I cannot lose any data from the table made the process a little more interesting than I had expected. I can do this through the GUI in 10 seconds flat or via T-SQL when first creating the table column, but I've never had to do it to an existing table via T-SQL. Let me tell you...I had a hell of a time trying to figure out the syntax from SQL Books Online. In fact, I didn't.

My new peer, Shashi, found how buried in a comment to this post dated 11.7.2000. Go figure. [shrug]

Here's the exact syntax:

ALTER TABLE TableName_Here WITH NOCHECK ADD CONSTRAINT [ConstraintName] DEFAULT 'DefaultValue_Here' FOR ColumnName_Here

In our environment our installation scripts are run each time we put out a new or updated build which means that we have to make changes without upsetting current data. That also means that we have to check for the existence (or lack) of something before making changes. So here's the context in which I scripted it:

First I check to see if the table already has a default constraint on any of it's columns. Note that this will return 1 if ANY column has a default value set! In my case, I know that this is the only column in this table that will have a default value. If no default constraint is found (returning 0) then I run the ALTER TABLE line, otherwise I don't take any action.

IF OBJECTPROPERTY(OBJECT_ID('TableName_Here'), 'TableHasDefaultCnst') = 0

BEGIN

ALTER TABLE TableName_Here WITH NOCHECK ADD CONSTRAINT [ConstraintName] DEFAULT 'DefaultValue_Here' FOR ColumnName_Here

END

[system] Improving Your Windows Performance

1. DISABLE INDEXING SERVICES
Indexing Services is a small little program that uses large amounts of RAM and can often make a computer endlessly loud and noisy. This system process indexes and updates lists of all the files that are on your computer. It does this so that when you do a search for something on your computer, it will search faster by scanning the index lists. If you don’t search your computer often, or even if you do search often, this system service is completely unnecessary. To disable do the following:

1. Go to Start
2. Click Settings
3. Click Control Panel
4. Double-click Add/Remove Programs
5. Click the Add/Remove Window Components
6. Uncheck the Indexing services
7. Click Next


2. OPTIMISE DISPLAY SETTINGS
Windows XP can look sexy but displaying all the visual items can waste system resources. To optimise:

1. Go to Start
2. Click Settings
3. Click Control Panel
4. Click System
5. Click Advanced tab
6. In the Performance tab click Settings
7. Leave only the following ticked:
- Show shadows under menus
- Show shadows under mouse pointer
- Show translucent selection rectangle
- Use drop shadows for icons labels on the desktop
- Use visual styles on windows and buttons


3. SPEEDUP FOLDER BROWSING
You may have noticed that everytime you open my computer to browse folders that there is a slight delay. This is because Windows XP automatically searches for network files and printers everytime you open Windows Explorer. To fix this and to increase browsing significantly:

1. Open My Computer
2. Click on Tools menu
3. Click on Folder Options
4. Click on the View tab.
5. Uncheck the Automatically search for network folders and printers check box
6. Click Apply
7. Click Ok
8. Reboot your computer


4. IMPROVE MEMORY USAGE
Cacheman Improves the performance of your computer by optimizing the disk cache, memory and a number of other settings.
NOTE: This program is shareware and some features require activation.
Once Installed:

1.Go to Show Wizard and select All
2.Run all the wizards by selecting Next or Finished until you are back to the main menu. Use the defaults unless you know exactly what you are doing.
3.Exit and Save Cacheman
4.Restart Windows


5. OPTIMISE YOUR INTERNET CONNECTION
There are lots of ways to do this but by far the easiest is to run TCP/IP Optimizer.

1. Download and install
2. Click the General Settings tab and select your Connection Speed (Kbps)
3. Click Network Adapter and choose the interface you use to connect to the Internet
4. Check Optimal Settings then Apply
5. Reboot


6. OPTIMISE YOUR PAGEFILE
If you give your pagefile a fixed size it saves the operating system from needing to resize the page file.

1. Right click on My Computer and select Properties
2. Select the Advanced tab
3. Under Performance choose the Settings button
4. Select the Advanced tab again and under Virtual Memory select Change
5. Highlight the drive containing your page file and make the initial Size of the file the same as the Maximum Size of the file.

Windows XP sizes the page file to about 1.5X the amount of actual physical memory by default. While this is good for systems with smaller amounts of memory (under 512MB) it is unlikely that a typical XP desktop system will ever need 1.5 X 512MB or more of virtual memory. If you have less than 512MB of memory, leave the page file at its default size. If you have 512MB or more, change the ratio to 1:1 page file size to physical memory size.


7. SPEEDUP FOLDER ACCESS - DISABLE LAST ACCESS UPDATE
If you have a lot of folders and subdirectories on your computer, when you access a directory XP wastes a lot of time updating the time stamp showing the last access time for that directory and for ALL sub directories. To stop XP doing this you need to edit the registry. If you are uncomfortable doing this then please do not attempt.

1. Go to Start and then Run and type “regedit”
2. Click through the file system until you get to “HKEY_LOCAL_MACHINE\ System\CurrentControlSet\Control\FileSystem”
3. Right-click in a blank area of the window on the right and select ‘DWORD Value’
4. Create a new DWORD Value called ‘NtfsDisableLastAccessUpdate’
5. Then Right click on the new value and select ‘Modify’
6. Change the Value Data to ‘1′
7. Click ‘OK’


8. MAKE YOUR MENUS LOAD FASTER
This is one of my favourite tweaks as it makes a huge difference to how fast your machine will ‘feel’. What this tweak does is remove the slight delay between clicking on a menu and XP displaying the menu.

1. Go to Start then Run
2. Type ‘Regedit’ then click ‘Ok’
3. Find “HKEY_CURRENT_USER\Control Panel\Desktop\”
4. Select “MenuShowDelay”
5. Right click and select “Modify’
6. Reduce the number to around “100″
7. This is the delay time before a menu is opened. You can set it to “0″ but it can make windows really hard to use as menus will open if you just look at them - well move your mouse over them anyway. I tend to go for anywhere between 50-150 depending on my mood


9. IMPROVE XP SHUTDOWN SPEED
This tweak reduces the time XP waits before automatically closing any running programs when you give it the command to shutdown.

1. Go to Start then select Run
2. Type ‘Regedit’ and click ok
3. Find ‘HKEY_CURRENT_USER\ Control Panel\Desktop\’
4. Select ‘WaitToKillAppTimeout’
5. Right click and select ‘Modify’
6. Change the value to ‘1000′
7. Click ‘OK’
8. Now select ‘HungAppTimeout’
9. Right click and select ‘Modify’
10. Change the value to ‘1000′
11. Click ‘OK’
12. Now find ‘HKEY_USERS\ .DEFAULT\Control Panel\Desktop’
13. Select ‘WaitToKillAppTimeout’
14. Right click and select ‘Modify’
15. Change the value to ‘1000′
16. Click ‘OK’
17. Now find ‘HKEY_LOCAL_MACHINE\ System\CurrentControlSet\Control\’
18. Select ‘WaitToKillServiceTimeout’
19. Right click and select ‘Modify’
20. Change the value to ‘1000′
21. Click ‘OK’


9. IMPROVE SWAPFILE PERFORMANCE
If you have more than 256MB of RAM this tweak will considerably improve your performance. It basically makes sure that your PC uses every last drop of memory (faster than swap file) before it starts using the swap file.

1. Go to Start then Run
2. Type “msconfig.exe” then ok
3. Click on the System.ini tab
4. Expand the 386enh tab by clicking on the plus sign
5. Click on new then in the blank box type”ConservativeSwapfileUsage=1″
6. Click OK
7. Restart PC


10. ENSURE XP IS USING DMA MODE
XP enables DMA for Hard-Drives and CD-Roms by default on most ATA or ATAPI (IDE) devices. However, sometimes computers switch to PIO mode which is slower for data transfer - a typical reason is because of a virus. To ensure that your machine is using DMA:

1. Open ‘Device Manager’
2. Double-click ‘IDE ATA/ATAPI Controllers’
3. Right-click ‘Primary Channel’ and select ‘Properties’ and then ‘Advanced Settings’
4. In the ‘Current Transfer Mode’ drop-down box, select ‘DMA if Available’ if the current setting is ‘PIO Only’

[mssql] Get First Date of Previous Month

CREATE FUNCTION dbo.udf_FirstDayofPreviousMonth (@pDate datetime)
RETURNS datetime
AS
BEGIN
 DECLARE @dFirstDayPrefMonth AS datetime

 SET @dFirstDayPrefMonth =
       convert(datetime,
                  left(convert(varchar(8),
                  dateadd(MM,-1, @pDate), 112), 6) + '01', 112)

 RETURN @dFirstDayPrefMonth
END
GO

[mssql] Choosing SQL Server 2000 Data Types

Introduction

Choosing an appropriate data type is very important, because the errors made in a table design can result in large performance degradation. These problems often surface later, when a large amount of data is inserted. In this article, I want to tell you about built-in and user-defined data types, how SQL Server 2000 stores data on a data page, and show some general tips to choose an appropriate data type.

Built-in data types

In Microsoft SQL Server 2000, each object (such as column, variable, or parameter) has a related data type, which is an attribute that specifies the type of data that the object can hold.

SQL Server 2000 ships with 27 built-in (system) data types. They are:

Data Types	Description
bigint	Integer data from -2^63 through 2^63-1
int	Integer data from -2^31 through 2^31 - 1
smallint	Integer data from -2^15 through 2^15 - 1
tinyint	Integer data from 0 through 255
bit	Integer data with either a 1 or 0 value
decimal	Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1
numeric	Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1
money	Monetary data values from -2^63 through 2^63 - 1
smallmoney	Monetary data values from -214,748.3648 through +214,748.3647
float	Floating precision number data from -1.79E + 308 through 1.79E + 308
real	Floating precision number data from -3.40E + 38 through 3.40E + 38
datetime	Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of 3.33 milliseconds
smalldatetime	Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute
char	Fixed-length character data with a maximum length of 8,000 characters
varchar	Variable-length data with a maximum of 8,000 characters
text	Variable-length data with a maximum length of 2^31 - 1 characters
nchar	Fixed-length Unicode data with a maximum length of 4,000 characters
nvarchar	Variable-length Unicode data with a maximum length of 4,000 characters
ntext	Variable-length Unicode data with a maximum length of 2^30 - 1 characters
binary	Fixed-length binary data with a maximum length of 8,000 bytes
varbinary	Variable-length binary data with a maximum length of 8,000 bytes
image	Variable-length binary data with a maximum length of 2^31 - 1 bytes
cursor	A reference to a cursor
sql_variant	A data type that stores values of various data types, except text, ntext, timestamp, and sql_variant
table	A special data type used to store a result set for later processing
timestamp	A database-wide unique number that gets updated every time a row gets updated
uniqueidentifier	A globally unique identifier

Some of these data types (bigint, sql_variant, and table) are only available in SQL Server 2000, while some were supported under the previous SQL Server versions.

User-defined data types

SQL Server 2000 supports user-defined data types too. User-defined data types provide a mechanism for applying a name to a data type that is more descriptive of the types of values to be held in the object. Using user-defined data type can make it easier for a programmer or database administrator to understand the intended use of any object defined with the data type. The user-defined data types are based on the system data types and can be used to predefine several attributes of a column, such as its data type, length, and whether it supports NULL values. To create a user-defined data type, you can use the sp_addtype system stored procedure or you could add one using the Enterprise Manager. When you create a user-defined data type, you should specify the following three properties:

• Data type's name.
• Built-in data type upon which the new data type is based.
• Whether it can contain NULL values.

The following example creates a user-defined data type based on money data type named cursale that cannot be NULL:

EXEC sp_addtype cursale, money, 'NOT NULL' GO

Both system and user-defined data types are used to enforce data integrity. It is very important that we put forth a lot of effort while designing tables: the better you design your tables, the more time you can work without any performance problems. In an ideal case, you never will update the structure of your tables.

Tips to choose the appropriate data types

SQL Server 2000 stores data in a special structure called data pages that are 8Kb (8192 bytes) in size. Some space on the data pages is used to store system information, which leaves 8060 bytes to store user's data. So, if the table's row size is 4040 bytes, then only one row will be placed on each data page. If you can decrease the row size to 4030 bytes, you can store two rows within a single page. The less space used, the smaller the table and index, and the less the I/O SQL Server has to perform when reading data pages from disk. So, you should design your tables in such a way as to maximize the number of rows that can fit into one data page. To maximize the number of rows that can fit into one data page, you should specify the narrowest columns you can. The narrower the columns, the less data that is stored, and the faster SQL Server is able to read and write data.

Try to use the following tips when choose the data types:

• If you need to store integer data from 0 through 255, use tinyint data type. The columns with tinyint data type use only one byte to store their values, in comparison with two bytes, four bytes and eight bytes used to store the columns with smallint, int and bigint data types accordingly. For example, if you design tables for a small company with 5-7 departments, you can create the departments table with the DepartmentID tinyint column to store the unique number of each department.
• If you need to store integer data from -32,768 through 32,767, use smallint data type. The columns with smallint data type use only two bytes to store their values, in comparison with four bytes and eight bytes used to store the columns with int and bigint data types accordingly. For example, if you design tables for a company with several hundred employees, you can create an employee table with the EmployeeID smallint column to store the unique number of each employee.
• If you need to store integer data from -2,147,483,648 through 2,147,483,647, use int data type. The columns with int data type use only four bytes to store their values, in comparison with eight bytes used to store the columns with bigint data types. For example, to design tables for a library with more than 32,767 books, create a books table with a BookID int column to store the unique number of each book.
• Use smallmoney data type instead of money data type, if you need to store monetary data values from 214,748.3648 through 214,748.3647. The columns with smallmoney data type use only four bytes to store their values, in comparison with eight bytes used to store the columns with money data types. For example, if you need to store the monthly employee payments, it might be possible to use a column with the smallmoney data type instead of money data type.
• Use smalldatetime data type instead of datetime data type, if you need to store the date and time data from January 1, 1900 through June 6, 2079, with accuracy to the minute. The columns with smalldatetime data type use only four bytes to store their values, in comparison with eight bytes used to store the columns with datetime data types. For example, if you need to store the employee's hire date, you can use a column with the smalldatetime data type instead of datetime data type.
• Use varchar/nvarchar columns instead of text/ntext columns whenever possible. Because SQL Server stores text/ntext columns on the Text/Image pages separately from the other data, stored on the Data pages, it can take more time to get the text/ntext values.
• Use char/varchar columns instead of nchar/nvarchar if you do not need to store unicode data. The char/varchar value uses only one byte to store one character, the nchar/nvarchar value uses two bytes to store one character, so the char/varchar columns use two times less space to store data in comparison with nchar/nvarchar columns.

See SQL Server Optimization Tips for more Microsoft SQL Server optimization tips.

Source: http://www.databasejournal.com/features/mssql/article.phpr/2212141