Sqlserver and XML

Find row count of one or more tables

Find row count of one or more tables

The following code can be used to retrieve the rowcount of each tables in the database. Let us create a function (TABLE VALUED) which returns the row count of all the tables.

CREATE FUNCTION dbo.GetRowCount()

RETURNS TABLE

RETURN

SELECT

st.Name,

SUM(

CASE

WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows

ELSE 0

END

) AS Rows

FROM sys.partitions p

INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id

INNER JOIN sys.tables st ON st.object_id = p.Object_ID

GROUP BY st.name

Execute the function as follows:

– list all tables and the number of rows

SELECT * FROM dbo.GetRowCount()

– find the rowcount of a single table

SELECT * FROM dbo.GetRowCount()

WHERE Name = ‘Invoices’

– find the rowcount of more than one table

SELECT * FROM dbo.GetRowCount()

WHERE Name IN (‘Invoices’,'Orders’)

If you have organized your tables into multiple schemas then you might need a different version of the function. Here is a modified version which retrieves the schema information too.

CREATE FUNCTION dbo.GetRowCount()

RETURNS TABLE

RETURN

SELECT

sch.name AS SchemaName,

st.Name AS TableName,

sch.name + ‘.’ + st.name AS QualifiedName,

SUM(

CASE

WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows

ELSE 0

END

) AS Rows

FROM sys.partitions p

INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id

INNER JOIN sys.tables st ON st.object_id = p.Object_ID

INNER JOIN sys.schemas sch ON sch.schema_id = st.schema_id

GROUP BY st.name, sch.name

Posted in Uncategorized |

Online DTD Validators

I had been creating some DTDs for a book that I am currently writing. The book is not about DTD. The title of the book is “XSD For SQL Server 2005 Developers” and it aims at helping SQL Server 2005 developers to make effective use of XML SCHEMA COLLECTIONS and TYPED XML. Though the book is on XSD, I have a few paragraphs that talk about DTDs. I had been creating some code samples to demonstrate in the book and wanted to have a quick way to validate the DTDs that I created.

After searching for a while, I came up with over a dozen web pages which validates an XML document against a DTD. Unfortunately, most of them were taking a URL as input and validates the doucument pointed by the URL. My case was different. I wanted to have a page that validates direct input text. I found a few validators that do this.

http://www.stg.brown.edu/service/xmlvalid/

http://www.xml.com/pub/a/tools/ruwf/check.html

http://validator.w3.org/#validate_by_input

Posted in Uncategorized |

VARCHAR/NVARCHAR (N) vs (MAX)

Prior to SQL Server 2005, it was hard dealing with large string values. NVARCHAR and VARCHAR data types had a limitation of 8000 bytes (VARCHAR(8000) and NVARCHAR(4000)). To store large values, most people used TEXT columns and others broke the value into multiple rows. Dealing with TEXT columns  was not easy. You cannot declare a variable of type TEXT. So reading information from a TEXT column was hard. Further, most of the string functions that we use regularly, do not support TEXT data type.

Life became easier with SQL Server 2005, when it introduced VARCHAR(MAX) and NVARCHAR(MAX) data types. VARCHAR(MAX) and NVARCHAR(MAX) can now store values up to 2 GB.

What does that mean? Can I declare a variable or column as VARCHAR(9000)?

The answer is NO. You can either declare a variable as VARCHAR(8000) or VARCHAR(MAX). But none in between. This leads to the question: “How do we restrict the length of the field then”?  This can be achieved by adding a CHECK constraint.

Well, we expected an error. But the insert statement did not produce an error. Why? Did SQL Server make a mistake? Why did it accept a value which is longer than 9000 characters?

Let us check the length of the data we just stored:

Oh…NO! Am I getting crazy?

The column has a restriction of 9000 characters. Our insert statement with 9001 characters successfully ran. The column has 8000 characters stored in it. All the statements conflict with each other.

Here is what happened. The REPLICATE function was expected to produce 9001 characters. But it returned only 8000 characters. All string functions that takes VARCHAR data type assumes the variable to be VARCHAR(8000) unless one of the parameters is VARCHAR(MAX). (in case of NVARCHAR, it is 4000). So, here is how we could generate a string of 9001 characters.

Let us see if the CHECK constraint we created works or not.

Yes, it is working!

Storage of NVARCHAR(MAX)/VARCHAR (MAX) values

VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX)  and XML data types are called Large Value Types. SQL Server stores each record in a storage location called a ‘data row’. The size of a row is 8060 bytes. When you store a Large Value Type to a column, if the total size of the row (including your Large Value Type) value is less than 8060 bytes, the value is stored “in row”. That means the value is stored in the same data row where other values of the same record are stored. If the Large Value Type is larger, it is stored “out of row” which means that the data is stored in another location and a pointer to the stored location will be added in the data row. Reading or writing values “out of the row” will need some additional processing and hence is not as fast as “in row” operations.

VARCHAR(MAX)/NVARCHAR(MAX) columns are internally handled as TEXT columns. Though we can work with them as regular strings, under the covers there is some TEXT processing happening. Another important point to note about VARCHAR(MAX)/NVARCHAR(MAX) columns is that, you cannot create an index on those columns.

Additional reading on VARCHAR(MAX)/NVARCHAR(MAX)

http://msdn2.microsoft.com/en-us/library/ms189087.aspx

http://www.informit.com/articles/article.aspx?p=327394&seqNum=6&rl=1

http://blogs.conchango.com/christianwade/archive/2004/11/06/199.aspx

http://www.fotia.co.uk/fotia/DY.13.VarCharMax.aspx

http://www.fotia.co.uk/fotia/DY.13.VarCharMax.aspx

http://www.sql-server-helper.com/faq/sql-server-2005-varchar-max-p01.aspx

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1098157,00.html

Posted in Uncategorized |

Application Roles in SQL Server

Application roles are set of permissions that you can group together for a specific application. When a user connects to the database through the specific application, he or she will be able to perform the actions permitted under the specific application role.

I have not used application roles in any of my applications yet. I am not sure if I will ever use it in any of my applications. But a little bit of reading on application roles will be good. I recently found a good introductory article about application roles at www.sqlservercentral.com. You can find the article here.

How to find the application role that is currently active?

I recently found this question in one of the MSDN forums and did some search to find an ansser. I found that when an application role is activated, USER_NAME() will return the name of the application role. Here is a piece of code which demonstrates this. Part of this example is taken from this MSDN article.

– create an app role

Posted in Uncategorized |

How to find all stored procedures used by Report Server?

Report definitions [RDL files] are stored in the “catalog” table of ReportServer database. This table has a field “content” which stores the report definition as an image/text value.

The following query will extract a list of all reports and the stored procedures used by them, by querying the catalog table of report server.

;WITH XMLNAMESPACES (

)
SELECT

FROM (

) a
CROSS APPLY reportXML.nodes(‘/Report/DataSets/DataSet/Query’) r(x)
WHERE x.value(‘CommandType[1]‘, ‘VARCHAR(50)’) = ‘StoredProcedure’

Note that I have applied a filter for ‘StoredProcedure’. If you want to get all the information (including queries etc) you should remove this filter. you should also increase the size of the field to VARCHAR(MAX) to make sure that the text is not truncated.

Posted in Uncategorized |

Insert an XML variable into another

If you are using SQL Server 2008 November CTP, you can insert one XML variable into anther using the XQuery insert operator. Here is an example:

– declare an XML variable

DECLARE @x XML

SET @x = ‘<Root></Root>’

– create another XML variable

DECLARE @t XML

SELECT @t = (SELECT TOP 3 name FROM sys.tables FOR XML AUTO)

– insert the second XML variable to the first one

SET @x.modify( ‘

insert sql:variable(“@t”)

as last into (/Root)[1] ‘ )

– Let us check the results

SELECT @x

/*

<Root>

<sys.tables name=”spt_fallback_db”/>

<sys.tables name=”spt_fallback_dev”/>

<sys.tables name=”spt_fallback_usg” />

</Root>

*/

Posted in Uncategorized |

FOR XML EXPLICIT – Part 3

Part 1

Part 2

Having fixed the problem with the sort order, let us go ahead with the rest of the code. Let us add Addresses under the AddressCollection node and come up with the final version of the code. We need to add a new level, Tag 4. Note that I used AgentID * 102 to make sure that this record will come right below the AddressCollection row of each Agent.

Here is the complete listing of the code.

Posted in Uncategorized |

XML Workshop XII – Parsing a delimited string

I am pretty sure that parsing a delimited string is one of the basic string operation that all of us do. Any programmer must have used it at least once in his or her programming career. Based on the programming language he or she uses, there might be different methods or ways to do this.

Let us look a second at TSQL. There are many ways to parse a delimited string. There are many ways widely used and discussed and you can find several articles on Internet which explains those methods. The most common approach that I have seen is using a PATINDEX in a WHILE loop. I have recently written an article at SQLServerCentral which shows yet another way of parsing a delimited string. The approach I presented at SQLServerCentral uses an XML based approach for parsing the string.

You can find the article here.

Posted in Uncategorized |

Passing a Data Table to a SQL Server 2005 Stored Procedure

One of the readers at SQLServerCentral forum asked me about passing a DataTable to a stored procedure. He wanted to get an XML representation of the DataTable and then pass it to the stored procedure. I tried to answer the question and came up with a complete sample code, which I think would be useful to others too.

Here is what the sample code does.

1. Opens a connection to a database
2. Executes a stored procedure which returns a DataSet with 2 DataTables
3. Get the XML out of the first table
4. Pass the XML to a stored procedure

Before we have a look at the code, we need to create a database and create a couple of tables. Then we need to populate the tables with some sample data. Here is the code.

Now let us create a stored procedure which returns two result sets.

Let us create the next stored procedure which accepts an XML parameter. This procedure will insert the data from the XML parameter, into the Employee Table.

Now Let us see the VB.NET code. I have created a VB.NET console application which performs the 4 steps mentioned above.

Note: The VB.NET code presented here may not be the best possible code. The intension of writing this code is to present the best code to perform the given operation. The idea is to present a basic code which WORKS!

Posted in Uncategorized |

FOR XML EXPLICIT – Part 2

Continued from Part 1
Let us move ahead. Under each agent, we need a node named AddressCollection. Let us add the code for that.

SELECT

1 AS Tag,

NULL AS Parent,

NULL AS ‘Agents!1!’,

NULL AS ‘Agent!2!AgentID’,

NULL AS ‘Agent!2!Fname!Element’,

NULL AS ‘Agent!2!SSN!Element’,

NULL AS ‘AddressCollection!3!Element’

UNION ALL

SELECT

2 AS Tag, 1 AS Parent,

NULL, AgentID, Fname, SSN,

NULL

FROM @agent

UNION ALL

SELECT

3 AS Tag, 2 AS Parent,

NULL, NULL, NULL, NULL,

NULL

FROM @agent

FOR XML EXPLICIT

We added a new level for AddressCollection element. I used FROM @agent because we need an AddressCollection element for each agent record. Here is the output.

Vimal

123-23-4521

Jacob

321-52-4562

Tom

252-52-4563

Wait a second! we have a problem. Note that the 3 AddressCollection elements were created as part of the last node. Why does this happen? To understand that we need to look at the query results that we passed to FOR XML EXPLICIT. Let us run the query without FOR XML EXPLICIT.

Note the rows in yellow . These are the records with tag 3. Note that they appear at the bottom of the result set. That is the reason why they appear at the bottom of the XML result. So to fix this, we need to change the order of the rows. So to get the correct XML we need to have the query results in the following order.

Tag Parent Agents!1! Agent!2!AgentID Agent!2!Fname Agent!2!ssn AddressCollection!3 1 NULL NULL NULL NULL NULL NULL 2 1 NULL 1 Vimal 123-23-4521 NULL 3 2 NULL NULL NULL NULL NULL 2 1 NULL 2 Jacob 321-52-4562 NULL 3 2 NULL NULL NULL NULL NULL 2 1 NULL 3 Tom 252-52-4563 NULL 3 2 NULL NULL NULL NULL NULL

So at this stage, we need to write some kind of code to alter the sort order of the records. There might be different ways to do that. What I did was to add a calculated column for the sort order based on the AgentID. Here is the new code.

SELECT

1 AS Tag,

NULL AS Parent,

0 AS Sort,

NULL AS ‘Agents!1!’,

NULL AS ‘Agent!2!AgentID’,

NULL AS ‘Agent!2!Fname!Element’,

NULL AS ‘Agent!2!SSN!Element’,

NULL AS ‘AddressCollection!3!Element’

UNION ALL

SELECT

2 AS Tag, 1 AS Parent,

AgentID * 100 AS Sort,

NULL, AgentID, Fname, SSN,

NULL

FROM @agent

UNION ALL

SELECT

3 AS Tag, 2 AS Parent,

AgentID * 100 + 1 AS Sort,

NULL, NULL, NULL, NULL,

NULL

FROM @agent

ORDER BY Sort

Posted in Uncategorized |