SQL Server Performance Tuning for the Bellingham .NET users group

Tonight I had the opportunity to present to the Bellingham .NET users group.  About 30 people showed up for the presentation.

 

SQL Server Performance Tuning and Advanced Query Tuning:

This presentation will include SQL Server performance tuning tips and recommendations for both the developer and the database administrator. Topics covered will include index usage details, query tuning, understanding the procedure cache, and best practices to improve the performance of your SQL Server, and the programs using your SQL Server.

 

Download SQL Server Performance Tuning.pdf

varchar(max) and varbinary(max) Questions and Answers

The following applies to SQL Server 2008.

How big of an allocation does max represent in the case of varchar(max) and varbinary(max)?

Up to 2 ^ 31 – 1 bytes or just about 2gb.

 

Can I specify a varchar(9000) or varbinary(12000)?

No. If you are going to specify an actual number instead of (max) then your limit is 8000. The size can be 1 to 8000, or max, there is nothing between 8000 and max.

 

Is a varchar(max) or varbinary(max) column stored in the data row?

If the size of the varchar or varbinary item is less than 8000 then it is stored in the data row.
If it is greater than 8000 then the item is moved out of the data row into a special storage location for large objects.

 

Should varchar(max) be used to replace the TEXT column type?

Yes. varchar(max) can handle larger strings than the text type can. varchar(max) is easier to update than text with standard SQL statements.