SQL Server 2008 - Wide Tables

Recently I navigated through the Maximum Capacity Specifications for SQL Server 2008

I found something interesting which says that,

Max columns per 'non wide' table : 1024
Max columns per 'wide' table : 30,000

Where non wide tables are regular database tables and wide table is a table which uses column sets and sparse columns. It still follows the same width restrictions per row i.e. 8019 bytes.  So you would typically use it only when most of your columns data in a particular row is NULL.

Refer to below link for more information.

Special Table Types

Reference: DP ( http://dptechnicalblog.blogspot.com/ )

T-SQL Exception Handling

Prior toSQL Server 2005 i.e. in SQL Server 2000 / 7.0, exception handling is a very premitive task with handful of limitations.
SQL Server 2005 architecture is now started using Visual Studio platform and based on Microsoft  .NET framework.
One of the features support is that now SQL Server can leverage on TRY..CATCH functionality provided by the .NET framework.

Following are the inbuilt methods supported by this TRY..CATCH functionality and should be used within CATCH block.

ERROR_NUMBER()
,ERROR_SEVERITY()
,ERROR_STATE()
,ERROR_PROCEDURE()
,ERROR_LINE()
,ERROR_MESSAGE()


For complete information refer to this good article written by Nakul Vachhrajani.

Reference: DP (http://dptechnicalblog.blogspot.com/ )

SQL Server 2008 - Table Valued Parameters

SQL Server 2008 introduced a nice feature called Table Valued Parameters created using user defined table type.

Table-valued parameters offer more flexibility and in some cases better performance than temporary tables or other ways to pass a list of parameters. There are tons of benefits, and here is the list provided by BOL:

• Do not acquire locks for the initial population of data from a client
• Provide a simple programming model
• Enable you to include complex business logic in a single routine
• Reduce round trips to the server
• Can have a table structure of different cardinality
• Table valued parameters are strongly typed
• Enable the client to specify sort order and unique ke

For more information refer to this link.

Reference: DP ( http://dptechnicalblog.blogspot.com/ )

SQL Server 2008 R2 : T-SQL Data Type Precedence

When an operator combines two different data types, the data type precedence rules says that the data type with lowest precedence converts to the data type with highest precedence.  If the conversion is not supported implicit conversion, an error is returned. If both the operands are of same data type, then the result is of same data type.

SQL Server uses the following order of data type precedence.

1. User defined data types (highest)
2. sql_variant
3. xml
4. datatimeoffset
5. datetime2
6. datetime
7. smalldatetime
8. date
9. time
10. float
11. real
12. decimal
14. money
15. small money
16. bigint
17. int
18. smallint
19. tinyint
20. bit
21. ntext
22. text
23. image
24. timestamp
25. uniqueidentifier
26. nvarchar (including nvarchar(max))
27. nchar
28. varchar (including varchar(max))
29. char
30. varbinary (including varbinary(max))
31. binary (lowest)

For complete details refer to the msdn link below:
 http://msdn.microsoft.com/en-us/library/ms190309.aspx 

Reference: DP (http://dptechnicalblog.blogspot.com/ )