T-Sql

Many times when we normalize our database, we end up storing multi-values attributes as separate rows in table. For eg. Consider we are storing Photos and a set of tags associated with the photos.

Assume the following table structure

Photo_ID   Tag
  1	   Beach
  1	   Sand
  2	   Mountain
  2	   Waterfall
  3	   Island
  3	   White Sand
  3	   Blue waters

Now while retrieving the tags for photos we need only one record per photo with all the tags as comma separated in the output.

In one of my earlier post Creating a Delimited list from a column of a table, we had seen how to generate CSV (demilited string) from column values in a table using XML PATH and COALESCE method. These methods though solved our problem, but are not generic.

In this post we will look at how to generalize the solution by using SQLCLR aggregates.

Before we look at the actual CLR aggregate let’s look at what a SQLCLR aggregate comprises of. A SQLCLR Aggregate is defined as a STRUCTURE in .NET. It consists of 4 methods

To support multi-value params, many times we use XML data type as input param.

There are multiple ways to parse XML data in SQL Server. Lets have a look at them

We will use the following XML data

<ShoppingCart>
<Purchase ProductID="7" Price="10.00" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
<Purchase ProductID="99" Price="25.00" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
<Purchase ProductID="32" Price="12.00" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
<Purchase ProductID="11" Price="90.00" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
<Purchase ProductID="7" Price="50.00" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
<Purchase ProductID="8" Price="67.35" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
<Purchase ProductID="45" Price="29.99" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
<Purchase ProductID="54" Price="49.49" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
</ShoppingCart>

OPENXML
To parse the XML using OPENXML we use the following code

In this script post, we will look at different ways to get the list of user defined tables in a particular database, along with their rowcount and size information.

Method 1: Using count(*)

In this method we will get the rowcount for each table using the sp_msforeachtable in combination with count(*) function. Note using this method we will only get the rowcount for each table.

IF OBJECT_ID('tempdb..#tableRowCount') IS NOT NULL
DROP TABLE #tableRowCount
CREATE TABLE #tableRowCount
(
name       VARCHAR(512),
ROWS       INT
)
insert into #tableRowCount
exec sp_msforeachtable 'select ''?'' ,COUNT(*) FROM ?'
select * from #tableRowCount

Method 2: Using sp_spaceused

Here, we will get the rowcount for each table using the sp_msforeachtable in combination with sp_spaceused stored procedure. Using this method we will get both the rowcount as well as spaceused in KB for all the tables.

There are three levels of data modeling. They are conceptual, logical, and physical. This section will explain the difference between the three, the order with which each one is created, and how to go from one level to the other.

Conceptual Data Model

Features of the conceptual data model include:

• Includes the important entities and the relationships among them.
• No attribute is specified.
• No primary key is specified.

At this level, the data modeler attempts to identify the highest-level relationships among the different entities.