What is your environment? Windows version/edition, SQL Server version/edition? 32 or 64 bit etc?

What is your environment? Windows version/edition, SQL Server version/edition? 32 or 64 bit etc?

Are you sure it is the % Privileged Time >90%? That means CPU is busy in kennel mode, e.g. using operating system services, processing IOs, Paging Memories to disk, etc.

What...

To humbleDBA,

You are right. INSERT ... EXEC works for table variable too in SQL Server 2005. In SQL 2000, it does not work. Thanks.

Jeff, Gail, and Chris,

Thanks for the...

One more problem is network traffic. For SP, client just needs to pass the SP name and the parameter values; For DAL, the whole query TSQL command blocks, which are...

In OO world, encapsulation is a fundamental rule. Developers need to create private members and wrap them by SET / GET as properties. They donot expose the members, insdead, they...

CROSS APPLY is used to "JOIN" a table (A) with the second rowset, which is produced by a rowset function (TVF, xml nodes, etc) by taking A's columns as parameters....

;WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume')

SELECT myResume.xxx.value('.','varchar(max)')as OrgName

FROM [HumanResources].[JobCandidate] J CROSS APPLY J.[Resume].nodes('/Resume/Employment/Emp.OrgName') myResume (xxx)

WHERE J.JobCandidateID = 3

This works:

Declare @x xml;

With XMLNAMESPACES ('http://www.test.namespace.com/ns/1.0' as c)

SELECT @x = (SELECT 'value' [c:test] For xml path(''),Elements, type)

Select @x

OK. I can't paste the xml here. It's the same as the file you attached.

Based on the xml you posted, the following code works:

DECLARE @x xml;

set @x=N'

'

Select T.c.value('AgentID[1]','int') as AgentID,

T.c.value('AgentName[1]','nvarchar(100)') as AgentName

from @x.nodes('row') T(c)

SQL Server 2008 has better support for EAV by sparse columns/columns set

I found your loop and CTE are not equivalent. In the loop, you have the code

WHERE ...

and c2.structureid not in

...

I have dbmail setup using my exchange account. It works fine.

SQL Server does not have hard limit for rows in a table if you have enough disk space.