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>

*/

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 (

DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd

)
SELECT

name,
x.value('CommandType[1]', 'VARCHAR(50)') AS CommandType,
x.value('CommandText[1]','VARCHAR(50)') AS CommandText

FROM (

select name,
CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
from ReportServer.dbo.Catalog

) 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.

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.

-- let us create a table

CREATE TABLE Customers (

    CustomerID INT,

    -- Other fields,

    Notes VARCHAR(MAX))

GO

 

-- let us add the check constraint

ALTER TABLE Customers

ADD CONSTRAINT CustomerNoteLength

CHECK (DATALENGTH(Notes) <= 9000)

GO

 

-- expect an error

INSERT INTO Customers( CustomerID, Notes )

SELECT 1, REPLICATE( 'a', 9001 )

 

/*

TOSHIBA-USER\SQL2005(TOSHIBA-USER\Jacob Sebastian): (1 row(s) affected)

!!!!!! NO ERROR !!!!!!!!

*/

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:

SELECT LEN(Notes) FROM Customers

 

/*

--------------------

8000

*/

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.

INSERT INTO Customers( CustomerID, Notes )

SELECT 3, REPLICATE( CAST('a' AS VARCHAR(MAX)), 9000 )

 

SELECT LEN(Notes) FROM Customers WHERE CustomerID = 3

/*

--------------------

9000

*/

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

INSERT INTO Customers( CustomerID, Notes )

SELECT 1, REPLICATE( CAST('a' AS VARCHAR(MAX)), 9001 )

/*

TOSHIBA-USER\SQL2005(TOSHIBA-USER\Jacob Sebastian): Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "CustomerNoteLength". The conflict occurred in database "master", table "dbo.Customers", column 'Notes'.

TOSHIBA-USER\SQL2005(TOSHIBA-USER\Jacob Sebastian):

The statement has been terminated.

*/

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

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

EXEC sp_addapprole 'JacobsApplication', 'jacob$$'

GO

 

DECLARE @cookie varbinary(8000);

EXEC sp_setapprole 'JacobsApplication', 'jacob$$'

    , @fCreateCookie = true, @cookie = @cookie OUTPUT;

-- The application role is now active.

 

SELECT USER_NAME();

-- This will return the name of the application role, JacobsApplication.

 

EXEC sp_unsetapprole @cookie;

-- The application role is no longer active.

-- The original context has now been restored.

GO

 

SELECT USER_NAME();

-- This will return the name of the original user.

GO

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

SQL Server Data Structure

I just found an interesting document which explains the basiscs of SQL Server Data Structure and a brief explanation of indexes. It does not go much deep into Data Storage but gives a very simple and clear explanation of the core concepts.

Read the article

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