Mark Tremaine

Similar to the SELECT and WHERE clauses, the ORDER BY clause can also utilize CASE statements.  Within these CASE statements, the input parameter is evaluated and used to sort the result set as desired. For example, if you have a table with the following schema: 1: CREATE TABLE [CONTACT_TX_TB] ( 2: [CONTACT_SYSID] [int] IDENTITY(1,1) NOT [...]

This one’s short and sweet … add the following snippet to your web.config file (within the configuration > system.web > pages node): 1: <caching> 2: <sqlCacheDependency enabled="true"/> 3: </caching> .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, “Courier New”, courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode [...]

Common table expressions (CTEs) provide the significant advantage of being able to reference themselves, making recursion far simpler then earlier versions of SQL Server.  CTEs consist of three parts: a name, an optional column list and a query.  A recursive query also consists of three parts: an anchor member, a recursive member and a termination [...]

you can modify the .net sql role provider to be hierarchical in just a few simple steps … first you need to modify the aspnet_Roles table: 1: ALTER TABLE [dbo].[aspnet_Roles] ADD [ParentRoleId] [uniqueidentifier] NULL .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, “Courier New”, courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre [...]

There exists a new feature in Microsoft SQL Server 2005 called Service Broker.  With Service Broker, internal or external processes can send and receive guaranteed, asynchronous messaging by using extensions to Transact-SQL. Read More One way to utilize this feature is by implementing SQLCacheDependency within a SqlSiteMapProvider.  Thus allowing the SqlSiteMapProvider to update as changes [...]

among other improvements, SQL Server 2005 included a new XML datatype and enhanced XML functions … thus providing another solution for recursive queries … for example … if i want to get a list of all of the tables in my database with their associated columns …  i might write the following query: 1: SELECT [...]