SQL SERVER – Comma Separated Values (CSV) from Table Column

I use following script very often and I realized that I have never shared this script on this blog before. Creating Comma Separated Values (CSV) from Table Column is a very common task, and we all do this many times a day. Let us see the example that I use frequently and its output.

USE AdventureWorks
GO
-- Check Table Column
SELECT Name
FROM HumanResources.Shift
GO
-- Get CSV values
SELECT SUBSTRING(
(
SELECT ',' + s.Name
FROM HumanResources.Shift s
ORDER BY s.Name
FOR XML PATH('')),2,200000) AS CSV
GO

I consider XML as the best solution in terms of code and performance. Further, as I totally prefer this option, I am not even including the linka to my other articles, where I have described other options.


Do you use any other method to resolve this issue? Can you find any significant difference in performance between these options? Please leave your comment here.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

SQL SERVER – Practical SQL Server XML: Part One – Query Plan Cache and Cost of Operations in the Cache

I am very fortunate that I have friends like Michael Coles. Michael Coles is SQL Server and XML expert and have written many books on SQL Server as well XML. He has previously written book which I have reviewed on this blog SQLAuthority News – Book Review – Pro T-SQL 2005 Programmer’s Guide (Paperback). I am currently reading his latest book Pro SQL Server 2008 XML (Hardcover) which can be found on amazon. I will be writing review of the book once I am done reading it.

Michael Coles and I met last at Microsoft MVP Summit 2009 at Seattle and I come to know he very much young and dynamic person who is friend of everybody. His knowledge of SQL Server is exception and I had requested him to write one practical demonstration of XML and he took up this challenge very well and the out come of that challenge is I am going to publish in this blog as it is.

Michael Coles and Pinal Dave

I spoke to Pinal Dave at the MVP Summit this week, and he challenged me to provide his readers with a practical demonstration of using XML in the database.  I took up the challenge, and decided to go one better by giving two practical demonstrations.

This post is the first demonstration, which queries the SQL Server query plan cache and returns the cost of operations in the cache.  The query simply shreds the cached XML query plans and grabs all the RelOp nodes, which represent all the operators used by the query plan. In the second post at http://sqlblog.com/blogs/michael_coles/archive/2009/03/03/practical-sql-server-xml-part-2.aspx I’ll demonstrate passing XML parameter “sets” into a stored procedure.  As a bonus, SQL MVP Jacob Sebastian has agreed to add a third post on his blog, at http://blog.beyondrelational.com/2009/03/practical-sql-server-xml-part-three.html that will give a practical example of using XML Schema in SQL Server.

The XML Query plans look like this in the cache (note that I simplified the XML and this is only a small portion that’s relevant):

<QueryPlan xmlns:p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"
CachedPlanSize="112"
CompileTime="500"
CompileCPU="53"
CompileMemory="1968">

<RelOp NodeId=“0”
PhysicalOp=“Sequence Project”
LogicalOp=“Compute Scalar”
EstimateRows=“227.951”
EstimateIO=“0”
EstimateCPU=“1.82361e-005″
AvgRowSize=“5202”
EstimatedTotalSubtreeCost=“930.972” >

<RelOp NodeId=“1”
PhysicalOp=“Segment”
LogicalOp=“Segment”
EstimateRows=“227.951”
EstimateIO=“0”
EstimateCPU=“4.55901e-006″
AvgRowSize=“5202”
EstimatedTotalSubtreeCost=“930.972” >

</RelOp>

</RelOp>
</
QueryPlan>

The equivalent graphical execution plan looks like this (note this is a partial screenshot):

(Click on image to see enlarged image)

(Note that you can save your graphical execution query plans as XML also – just right-click in the graphical execution query plan window and save it as a .sqlplan file. The .sqlplan file is in XML format – you can verify this by opening it in NotePad).

Since SQL Server 2005 and 2008 store the query plans in the cache as XML we can get all of the same information in relational format.  The sample query below returns attributes of each operator in each query plan including physical and logical operators, estimated cost, estimated IO, estimated CPU cost, and an estimated number of rows returned.

WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
CachedPlans
(
ParentOperationID,
OperationID,
PhysicalOperator,
LogicalOperator,
EstimatedCost,
EstimatedIO,
EstimatedCPU,
EstimatedRows,
PlanHandle,
QueryText,
QueryPlan,
CacheObjectType,
ObjectType)
AS
(
SELECT
RelOp.op.value(N'../../@NodeId', N'int') AS ParentOperationID,
RelOp.op.value(N'@NodeId', N'int') AS OperationID,
RelOp.op.value(N'@PhysicalOp', N'varchar(50)') AS PhysicalOperator,
RelOp.op.value(N'@LogicalOp', N'varchar(50)') AS LogicalOperator,
RelOp.op.value(N'@EstimatedTotalSubtreeCost ', N'float') AS EstimatedCost,
RelOp.op.value(N'@EstimateIO', N'float') AS EstimatedIO,
RelOp.op.value(N'@EstimateCPU', N'float') AS EstimatedCPU,
RelOp.op.value(N'@EstimateRows', N'float') AS EstimatedRows,
cp.plan_handle AS PlanHandle,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan,
cp.cacheobjtype AS CacheObjectType,
cp.objtype AS ObjectType
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY qp.query_plan.nodes(N'//RelOp') RelOp (op)
)
SELECT
PlanHandle,
ParentOperationID,
OperationID,
PhysicalOperator,
LogicalOperator,
QueryText,
CacheObjectType,
ObjectType,
EstimatedCost,
EstimatedIO,
EstimatedCPU,
EstimatedRows
FROM CachedPlans
WHERE CacheObjectType = N'Compiled Plan';

The results look similar to the following:

(Click on image to see enlarged image)

Since the XML query plan is hierarchical in structure, with operations nested within other operations, I’ve added the columns ParentOperationID and OperationID.  The ParentOperationID values point to the current operator’s parent operator.  I added the arrows in the results above to demonstrate.

So what’s the practical use of this?  Well, once you have the XML query plan information in relational format it’s relatively easy to manipulate.  You can determine, for instance, the Top 10 most costly queries in your cache and the operators in those queries are the most expensive. You can even log this information to a table or send it out in other formats.

In part 2 of this series I’ll talk about another practical use for SQL Server-based XML, at http://sqlblog.com/blogs/michael_coles/archive/2009/03/03/practical-sql-server-xml-part-2.aspx.  SQL Server MVP Jacob Sebastian will finish the series with part 3 on his blog at http://blog.beyondrelational.com/2009/03/practical-sql-server-xml-part-three.html.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Simple Example of Reading XML File Using T-SQL

In one of the previous article we have seen how we can create XML file using SELECT statement SQL SERVER – Simple Example of Creating XML File Using T-SQL. Today we will see how we can read the XML file using the SELECT statement.

Following is the XML which we will read using T-SQL:

Following is the T-SQL script which we will be used to read the XML:

DECLARE @MyXML XML
SET @MyXML = '<SampleXML>
<Colors>
<Color1>White</Color1>
<Color2>Blue</Color2>
<Color3>Black</Color3>
<Color4 Special="Light">Green</Color4>
<Color5>Red</Color5>
</Colors>
<Fruits>
<Fruits1>Apple</Fruits1>
<Fruits2>Pineapple</Fruits2>
<Fruits3>Grapes</Fruits3>
<Fruits4>Melon</Fruits4>
</Fruits>
</SampleXML>'

SELECT
a.b.value(‘Colors[1]/Color1[1]‘,‘varchar(10)’) AS Color1,
a.b.value(‘Colors[1]/Color2[1]‘,‘varchar(10)’) AS Color2,
a.b.value(‘Colors[1]/Color3[1]‘,‘varchar(10)’) AS Color3,
a.b.value(‘Colors[1]/Color4[1]/@Special’,‘varchar(10)’)+‘ ‘+
+
a.b.value(‘Colors[1]/Color4[1]‘,‘varchar(10)’) AS Color4,
a.b.value(‘Colors[1]/Color5[1]‘,‘varchar(10)’) AS Color5,
a.b.value(‘Fruits[1]/Fruits1[1]‘,‘varchar(10)’) AS Fruits1,
a.b.value(‘Fruits[1]/Fruits2[1]‘,‘varchar(10)’) AS Fruits2,
a.b.value(‘Fruits[1]/Fruits3[1]‘,‘varchar(10)’) AS Fruits3,
a.b.value(‘Fruits[1]/Fruits4[1]‘,‘varchar(10)’) AS Fruits4
FROM @MyXML.nodes(‘SampleXML’) a(b)

Please note in above T-SQL statement XML attributes is read the same as XML Value.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Simple Example of Creating XML File Using T-SQL

I always want to learn SQL Server and XML. Let us go over very simple example today about how to create XML using SQL Server.

Please also read related article here SQL SERVER – Simple Example of Reading XML File Using T-SQL.

Following is the XML which we want to create:

T-SQL Script to generate above XML:

SELECT ( SELECT 'White' AS Color1,
'Blue' AS Color2,
'Black' AS Color3,
'Light' AS 'Color4/@Special',
'Green' AS Color4,
'Red' AS Color5
FOR
XML PATH('Colors'),
TYPE
),
(
SELECT 'Apple' AS Fruits1,
'Pineapple' AS Fruits2,
'Grapes' AS Fruits3,
'Melon' AS Fruits4
FOR
XML PATH('Fruits'),
TYPE
)
FOR XML PATH(''),
ROOT('SampleXML')
GO

Every XML has two elements.

1) Attributes and 2) Value.

In my above example color4 has attribute along with value. Make sure to specify attribute before the value is defined otherwise it will give error. We will talk about this in other article.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Technical Articles – Performance Optimizations for the XML Data Type in SQL Server 2005

I always wanted to learn XML and its usage. My friend and fellow MVP Jacob Sebastian is expert in XML, so if you are interested in XML please visit his blog here. If you are interested in performance optimization for XML Data type in SQL Server following article is must read for you.

Performance Optimizations for the XML Data Type in SQL Server 2005 by  Shankar Pal, Babu Krishnaswamy, Vasili Zolotov, and Leo Giakoumakis – Microsoft Corporation

Articles covers following subjects.

  • Introduction
  • Data Modeling with the XML Data Type
  • Bulk Loading XML Data
  • Indexing XML Data
  • Query and Data Modification
  • Conclusion

You can download the same article in word format from here.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2008 – Interview Questions and Answers Complete List Download

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

Download SQL Server 2008 Interview Questions and Answers Complete List

UPDATE: This article series has been updated with new interview questions and answers series.

Interview is very important event for any person. A good interview leads to good career if candidate is willing to learn. I always enjoy interview questions and answers series. This is my very humble attempt to write SQL Server 2008 interview questions and answers. SQL Server is very large subject and not everything is usually asked in interview. In interview what matters the most is conceptual knowledge and learning attitude.

I have listed all the series in this post so that it can be easily downloaded and used. All the questions are collected and listed in one PDF which is here to download. If you have any question or if you want to add to any of the question please send me mail or write a comment.

SQL SERVER – 2008 – Interview Questions and Answers – Part 1

SQL SERVER – 2008 – Interview Questions and Answers – Part 2

SQL SERVER – 2008 – Interview Questions and Answers – Part 3

SQL SERVER – 2008 – Interview Questions and Answers – Part 4

SQL SERVER – 2008 – Interview Questions and Answers – Part 5

SQL SERVER – 2008 – Interview Questions and Answers – Part 6

SQL SERVER – 2008 – Interview Questions and Answers – Part 7

SQL SERVER – 2008 – Interview Questions and Answers – Part 8

Download SQL Server 2008 Interview Questions and Answers Complete List

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – XML – Split a Delimited String – Generate a Delimited String

SQL Server MVP and my very good friend Jacob Sebastian has written two wonderful articles about SQL Server and XML. I encourage to read this two articles to anybody who are interested in learning SQL and XML.

How to generate a Delimited String using FOR XML PATH

XQuery Lab 19 – How to parse a delimited string?

I have previously written article about operation of creating delimited string using COALESCE and no XML but as per telephonic conversation with Jacob COALESCE is only good for one column where as XML can do many more magics.

Jacob has already explained articles in detail so I suggest to read them carefully and digest. If you are not much big on XML, you do not have to spend time on learning this XML. Just take the XML script and use it for your need.

If you are wondering what about two article do, here is simple explanation. First article explains how to convert Sample 1 to Sample 2 and later one explains how to convert Sample 2 to Sample 1.
Sample 1:
/*

CompanyID CompanyCode
———– ———–
1 1
1 2
2 1
2 2
2 3
2 4
3 1
3 2

*/

Sample 2:
/*

CompanyID CompanyString
———– ————————-
1 1|2
2 1|2|3|4
3 1|2

*/

XML is very powerful and when combined with SQL Server it always unveils new side of RDBMS and DBMS.

Reference : Pinal Dave (http://blog.SQLAuthority.com)