Limitationsof table variables

Refer this linkTable Variables

DECLARE @people TABLE
(
id INT,
name VARCHAR(32)
)

A table variable is created in memory, and so performs slightly better than #temp tables (also because there is even less locking and logging in a table variable). A table variable might still perform I/O to tempdb (which is where the performance issues of #temp tables make themselves apparent), though the documentation is not very explicit about this.

Table variables are automatically cleared when the procedure or function goes out of scope, so you don't have to remember to drop or clear the data (which can be a good thing or a bad thing; remember "release early"?). The tempdb transaction log is less impacted than with #temp tables; table variable log activity is truncated immediately, while #temp table log activity persists until the log hits a checkpoint, is manually truncated, or when the server restarts.

Table variables are the only way you can use DML statements (INSERT, UPDATE, DELETE) on temporary data within a user-defined function. You can create a table variable within a UDF, and modify the data using one of the above statements. For example, you could do this:

CREATE FUNCTION dbo.example1
(
)
RETURNS INT
AS
BEGIN
DECLARE @t1 TABLE (i INT)
INSERT @t1 VALUES(1)
INSERT @t1 VALUES(2)
UPDATE @t1 SET i = i + 5
DELETE @t1 WHERE i < 7

DECLARE @max INT
SELECT @max = MAX(i) FROM @t1
RETURN @max
END
GO

However, try that with a #temp table:

CREATE FUNCTION dbo.example2
(
)
RETURNS INT
AS
BEGIN
CREATE TABLE #t1 (i INT)
INSERT #t1 VALUES(1)
INSERT #t1 VALUES(2)
UPDATE #t1 SET i = i + 5
DELETE #t1 WHERE i < 7

DECLARE @max INT
SELECT @max = MAX(i) FROM #t1
RETURN @max
END
GO

Results:

Server: Msg 2772, Level 16, State 1, Procedure example2, Line 7
Cannot access temporary tables from within a function.

Or try accessing a permanent table:

CREATE TABLE table1
(
id INT IDENTITY,
name VARCHAR(32)
)
GO

CREATE FUNCTION dbo.example3
(
)
RETURNS INT
AS
BEGIN
INSERT table1(name) VALUES('aaron')
RETURN SCOPE_IDENTITY()
END
GO

Results:

Server: Msg 443, Level 16, State 2, Procedure example3, Line 8
Invalid use of 'INSERT' within a function.

Table variables can lead to fewer stored procedure recompilations than temporary tables (see KB #243586 and KB #305977), and — since they cannot be rolled back — do not bother with the transaction log.

So, why not use table variables all the time? Well, when something sounds too good to be true, it probably is. Let's visit some of the limitations of table variables (part of this list was derived from KB #305977):

* Table variables are only allowed in SQL Server 2000+, with compatibility level set to 80 or higher.

* You cannot use a table variable in either of the following situations:

INSERT @table EXEC sp_someProcedure

SELECT * INTO @table FROM someTable

* You cannot truncate a table variable.

* Table variables cannot be altered after they have been declared.

* You cannot explicitly add an index to a table variable, however you can create a system index through a PRIMARY KEY CONSTRAINT, and you can add as many indexes via UNIQUE CONSTRAINTs as you like. What the optimizer does with them is another story. One thing to note is that you cannot explicitly name your constraints, e.g.:

DECLARE @myTable TABLE
(
CPK1 int,
CPK2 int,
CONSTRAINT myPK PRIMARY KEY (CPK1, CPK2)
)

-- yields:
Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'CONSTRAINT'.

-- yet the following works:
DECLARE @myTable TABLE
(
CPK1 int,
CPK2 int,
PRIMARY KEY (CPK1, CPK2)
)

* You cannot use a user-defined function (UDF) in a CHECK CONSTRAINT, computed column, or DEFAULT CONSTRAINT.

* You cannot use a user-defined type (UDT) in a column definition.

* Unlike a #temp table, you cannot drop a table variable when it is no longer necessary—you just need to let it go out of scope.

* You cannot generate a table variable's column list dynamically, e.g. you can't do this:

SELECT * INTO @tableVariable

-- yields:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@tableVariable'.

You also can't build the table variable inside dynamic SQL, and expect to use it outside that scope, e.g.:

DECLARE @colList VARCHAR(8000), @sql VARCHAR(8000)
SET @colList = 'a INT,b INT,c INT'
SET @sql = 'DECLARE @foo TABLE('+@colList+')'
EXEC(@sql)
INSERT @foo SELECT 1,2,3

-- this last line fails:

Server: Msg 137, Level 15, State 2, Line 5
Must declare the variable '@foo'.

This is because the rest of the script knows nothing about the temporary objects created within the dynamic SQL. Like other local variables, table variables declared inside of a dynamic SQL block (EXEC or sp_executeSQL) cannot be referenced from outside, and vice-versa. So you would have to write the whole set of statements to create and operate on the table variable, and perform it with a single call to EXEC or sp_executeSQL.

* The system will not generate automatic statistics on table variables. Likewise, you cannot manually create statistics (statistics are used to help the optimizer pick the best possible query plan).

* An INSERT into a table variable will not take advantage of parallelism.

* A table variable will always have a cardinality of 1, because the table doesn't exist at compile time.

* Table variables must be referenced by an alias, except in the FROM clause. Consider the following two scripts:

CREATE TABLE #foo(id INT)
DECLARE @foo TABLE(id INT)
INSERT #foo VALUES(1)
INSERT #foo VALUES(2)
INSERT #foo VALUES(3)
INSERT @foo SELECT * FROM #foo

SELECT id
FROM @foo
INNER JOIN #foo
ON @foo.id = #foo.id

DROP TABLE #foo

The above fails with the following error:

Server: Msg 137, Level 15, State 2, Line 11
Must declare the variable '@foo'.

This query, on the other hand, works fine:

SELECT id
FROM @foo f
INNER JOIN #foo
ON f.id = #foo.id


* Table variables are not visible to the calling procedure in the case of nested procs. The following is legal with #temp tables:

CREATE PROCEDURE faq_outer
AS
BEGIN
CREATE TABLE #outer
(
letter CHAR(1)
)

EXEC faq_inner

SELECT letter FROM #outer

DROP TABLE #outer
END
GO

CREATE PROCEDURE faq_inner
AS
BEGIN
INSERT #outer VALUES('a')
END
GO


EXEC faq_outer

Results:

letter
------
a

(1 row(s) affected)

However, you cannot do this with table variables. The parser will find the error before you can even create it:

CREATE PROCEDURE faq_outer
AS
BEGIN
DECLARE @outer TABLE
(
letter CHAR(1)
)

EXEC faq_inner

SELECT letter FROM @outer
END
GO

CREATE PROCEDURE faq_inner
AS
BEGIN
INSERT @outer VALUES('a')
END
GO

Results:

Server: Msg 137, Level 15, State 2, Procedure faq_inner, Line 4
Must declare the variable '@outer'.

For more information about sharing data between stored procedures, please see this article by Erland Sommarskog.

TSQL: Parsing delimited string into table

CREATE FUNCTION dbo.udf_ItemParse (
@Input VARCHAR(8000), @Delimeter char(1)='|'
)
RETURNS @ItemList TABLE (
Item VARCHAR(50) ,
Pos int
)
AS
BEGIN

DECLARE @Item varchar(50)
DECLARE @StartPos int, @Length int
DECLARE @Pos int
SET @Pos = 0
WHILE LEN(@Input) > 0
BEGIN
SET @StartPos = CHARINDEX(@Delimeter, @Input)
IF @StartPos < 0 SET @StartPos = 0
SET @Length = LEN(@Input) - @StartPos - 1
IF @Length < 0 SET @Length = 0
IF @StartPos > 0
BEGIN
SET @Pos = @Pos + 1
SET @Item = SUBSTRING(@Input, 1, @StartPos - 1)
SET @Input = SUBSTRING(@Input, @StartPos + 1, LEN(@Input) - @StartPos)
END
ELSE
BEGIN
SET @Pos = @Pos+1
SET @Item = @Input
SET @Input = ''
END
INSERT @ItemList (Item, Pos) VALUES(@Item, @Pos)
END
RETURN
END

SoapExtension for Logging Soap Exceptions

using System;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.IO;
using System.Xml;
using MIT.BizServices.MCUtil.Misc;

namespace MIT.Common.ExceptionHandlingSoapExtension
{
//

// SOAP extension to transfer full exception information from
// server to client in the event of an exception being thrown
// from a webmethod
//

//
// In the event of an exception being thrown during the execution
// of a webmethod on the server, this class steps in and writes detailed
// exception information to the output stream.
//
// Client side, if an exception is detected on the response from a
// webmethod call, this class retrieves the detailed exception
// information from the input stream and throws an exception
// containing the detailed information
//
public class ExceptionHandlingSoapExtension : SoapExtension
{

region "Initialisation methods - not used because this class has no state to maintain"
public override object GetInitializer(System.Type serviceType) {
return null;
}

public override object GetInitializer(System.Web.Services.Protocols.LogicalMethodInfo methodInfo, System.Web.Services.Protocols.SoapExtensionAttribute attribute) {
return null;
}

public override void Initialize(object initializer)
{
}
endregion

region "Stream chaining code"
private Stream oldStream;
private Stream newStream;

public override Stream ChainStream(Stream stream) {
oldStream = stream;
newStream = new MemoryStream();
return newStream;
}

//

// Copies the contents of one stream to another
//

private void StreamCopy(Stream source, Stream dest) {
StreamReader Reader = new StreamReader(source);
StreamWriter Writer = new StreamWriter(dest);
Writer.WriteLine(Reader.ReadToEnd());
Writer.Flush();
}
endregion

public override void ProcessMessage(System.Web.Services.Protocols.SoapMessage message)
{
switch (message.Stage)
{
case SoapMessageStage.BeforeSerialize:
return;

case SoapMessageStage.AfterSerialize:
//If exception present in message, write details
//to the new stream
if (message.Exception != null)
{
InsertExceptionDetails(message.Exception);
}

//Copy new stream to old stream
newStream.Position = 0;
StreamCopy(newStream, oldStream);
return;

case SoapMessageStage.BeforeDeserialize:
//Copy old stream to new stream
StreamCopy(oldStream, newStream);
newStream.Position = 0;
return;

case SoapMessageStage.AfterDeserialize:
//If exception present in message,
//get details from stream and throw to caller
if (message.Exception != null)
{
CheckExceptionDetails();
}
return;

default:
throw new ArgumentException("Invalid message stage");

}
}

//

// Insert details of the specified exception into the output stream

// <param name="ex">Exception to write details for
private void InsertExceptionDetails(Exception ex) {
//Read output stream into XML document
newStream.Position = 0;
XmlTextReader Reader = new XmlTextReader(newStream);
XmlDocument MessageDoc = new XmlDocument();
MessageDoc.Load(Reader);

XmlNamespaceManager NsMgr = new XmlNamespaceManager(MessageDoc.NameTable);
NsMgr.AddNamespace("soap", "http://schemas.xmlsoap.org/soap/envelope/");

//Construct string describing exception
string ErrorInfo;
if (ex.InnerException != null)
{
ErrorInfo = ex.InnerException.Message;
}
else
{
ErrorInfo = ex.Message;
}
//log exception to flat file
Helper.LogException(string.Empty, ex);

//Find existing soap:Fault node describing exception
XmlNode ExceptionNode;
ExceptionNode = MessageDoc.SelectSingleNode("//soap:Fault", NsMgr);

//Add extended exception detail node to Fault node
XmlElement ExceptionDetail;
ExceptionDetail = MessageDoc.CreateElement("ExtendedExceptionDetails");

ExceptionDetail.InnerText = ErrorInfo;

ExceptionNode.AppendChild(ExceptionDetail);

//Write XML document back to output stream
newStream = new MemoryStream();
MessageDoc.Save(newStream);
}

//

// Reads extra exception information from stream

// Details of any exception detail found in the input stream
private void CheckExceptionDetails()
{
//Read input stream into XML document
newStream.Position = 0;
XmlTextReader Reader = new XmlTextReader(newStream);
XmlDocument MessageDoc = new XmlDocument();
MessageDoc.Load(Reader);

XmlNamespaceManager NsMgr = new XmlNamespaceManager(MessageDoc.NameTable);
NsMgr.AddNamespace("soap", "http://schemas.xmlsoap.org/soap/envelope/");

//Find extended exception detail node
XmlNode ExceptionDetailNode;
ExceptionDetailNode = MessageDoc.SelectSingleNode("//soap:Fault/ExtendedExceptionDetails", NsMgr);
XmlNode exception = MessageDoc.SelectSingleNode("//soap:Fault/faultstring", NsMgr);

//Return detail text if found, empty string otherwise
if (ExceptionDetailNode != null)
{
if (exception != null)
{
if (exception.InnerText.Contains("WebServiceException"))
{
throw new WebServiceException(ExceptionDetailNode.InnerText);
}
else
{
throw new Exception(ExceptionDetailNode.InnerText);
}
}
}
}
}
}

Exception and Logging Application Block

Add reference to the following DLL's:
Microsoft.Practices.EnterpriseLibrary.Common.dll
Microsoft.Practices.EnterpriseLibrary.Logging
If you intend to log information to a database, you also need to reference the Microsoft.Practices.EnterpriseLibrary.Data.dll














For adding tracing in the method call use the following:
public DataSet GetPricingDataSet(string loanNumber)
{
using Tracer("Trace")
{
DoSomething();
//For additional logging use:
Logger.Write("message")
//or use:
Logger.Write(new LogEntry("message"))
}
}

Add a Trace listener in the Logging Application Block via the Enterprise Library Configuration tool and then specify a Category called "Trace" which logs to this newly configured Trace Listener.

For more info:
http://www.devx.com/dotnet/Article/31463/1954?pf=true

http://www.devx.com/dotnet/Article/35736?trk=DXRSS_DOTNET
http://www.devx.com/dotnet/Article/36184?trk=DXRSS_DOTNET - Enterprise Library 3.0
======================================================
For Exception Handling:
======================================================
Add references to the following DLL's:
ExceptionHandling.dll
ExceptionHandling.Logging.dll

public static void LogException(string policyName, Exception ex)
{
if (string.IsNullOrEmpty(policyName))
policyName = ConfigurationManager.AppSettings["ExceptionPolicy"];

ExceptionPolicy.HandleException(ex, policyName);
}

TSQL to concatenate column values into a single string

DECLARE @cols AS varchar(3000)
;
WITH YearsCTE
AS
(SELECT DISTINCT YEAR(OrderDate) as [Year] FROM Sales.SalesOrderHeader)

SELECT @cols = ISNULL(@cols + ',[', '[') + CAST([YEAR] AS varchar(4)) + ']'
FROM YearsCTE
ORDER BY [YEAR]

Note the use of ISNULL. This adds a additional "," to separate records. Note the isnull helps to add the additonal "," only to records numbered 2 or greater.

Create and deploy a Sharepoint Feature

Suppose you need to replace the SmallSearchInputBox on the sharepoint site with a new feature. Follow these steps:

step1 : create a copy of existing feature e.g. controlLightUp
Thus copy this folder from 12\Template\Features\CustomLightUp to 12\Template\Features\NewSearchArea

Step2: modify feature.xml to contain the new GUID and add a reference to the element.xml (within a subdirectory or the same directory as feature.xml, whichever you prefer) which describes the feature.
Specify if the feature should be HIDDEN or displayed in the Sharepoint site settings
Specify Title and Description if this feature is NOT Hidden

Step 3: then modify the elements.xml file in controls folder to have the correct settings. Scope can take one of the following values: FARM, Webapplication, Web and Site
Site is SiteCollection
Web is Site
Web Application is parent of Sitecollection
FARM is server wide enabling of the feature

Step4: suppose CustomLightUp feature is installed at FARM level snd it is referenced through a delagate control in the master page and its control id "SmallSearchInputBox", and you want to create a new feature at the Site scope and for the same delegate control
Then there are 2 ways to achioeve this

1: rename the Delegate control id from "SmallSearchInput" to "newSearch" then then make the modification in elements.xml to contain this new control id. then install the feature at Site level using stsadm utility

2. you can keep the control id the same i.e. "SmallSearchInput" but then modify the Sequence value in elements.xml to have a value less than 100.
(for more on this refer to my previous blog on delegate control) Then install the feature at the site level using stsadm
command is stsadm -o installfeature -filename NewSearchArea\feature.xml
stsadm -o activatefeature -filename NewSearch\feature.xml -url http://w.c.com

For automatic deployment of the feature read this:
http://www.codeplex.com/wspbuilder
Or
http://msdn.microsoft.com/msdnmag/issues/07/05/OfficeSpace/
and
http://msdn.microsoft.com/msdnmag/issues/07/08/OfficeSpace/

if you want to list all existing features then you need to extend the stsadm utility:
http://sharepointsolutions.blogspot.com/2006/09/extending-stsadmexe-with-custom.html
and
http://msdn2.microsoft.com/en-us/library/bb417382.aspx
and
http://www.andrewconnell.com/blog/articles/MossStsadmWcmCommands.aspx
and
http://stsadm.blogspot.com/2007/08/enumerate-features.html

Automating Dev, QA, Staging, and Production Web.Config Settings with VS 2005

One of the questions I get asked fairly regularly is: "how can I can easily change different configuration settings in my web.config file based on whether my application is in a dev, qa, staging or production mode?" The most common scenario for this is one where an application uses different database connection-strings for testing and production purposes.

It turns out you can easily automate this configuration process within the Visual Studio build environment (and do so in a way that works both within the IDE, as well as with command-line/automated builds). Below are the high-level steps you take to do this. They work with both VS 2005 and VS 2008.

1. Use ASP.NET Web Application Projects (which have MSBuild based project files)
2. Open the VS Configuration Manager and create new "Dev", "QA", "Staging" build configurations for your project and solution
3. Add new "web.config.dev", "web.config.qa", and "web.config.staging" files in your project and customize them to contain the app's mode specific configuration settings
4. Add a new "pre-build event" command to your project file that can automatically copy over the web.config file in your project with the appropriate mode specific version each time you build the project (for example: if your solution was in the "Dev" configuration, it would copy the web.config.dev settings to the main web.config file).

Once you follow these steps, you can then just pick the mode your solution is in using the configuration drop-down in the VS standard toolbar:

The next time you build/run after changing the configuration mode, VS will automatically modify your application's web.config file to pick up and use the web.config settings specific to that build configuration (so if you select QA it will use the QA settings, if you select Deploy it will use the Deploy settings).

The benefit with this approach is that it works well in a source control environment (everyone can sync and build locally without having to make any manual changes on their local machines). It also works on a build server - including with scenarios where you are doing automated command-line solution builds.

To learn more about the exact steps to set this up, please read the Managing Multiple Configuration File Environments with Pre-Build Events post that Scott Hanselman published earlier tonight. Also check out ScottGu's ASP.NET Tips, Tricks, and Gotchas page for other ASP.NET Tips/Tricks recommendations.

multi column sorting of a typed collection

Use the following One line code for multi-column or single column sorting of a typed collection:

List<Liability> liabList = new List<Liability>();

liabList.Sort(delegate(Liability l1, Liability l2)
{ int r = l1.Type.CompareTo(l2.Type);
if (r == 0 && l1.HolderName != null)
r = l1.HolderName.CompareTo(l2.HolderName);
if (r == 0)
r = l1.Amount.CompareTo(l2.Amount);
return r;
} );