SQL Server And XML

Tuesday, May 06, 2008

SQL Server TSQL Wiki

Some of the SQL Server MVPs, MSDN Moderators and Answerers are working together to set up a Wiki site to provide solutions to most common TSQL problems. It is a great location to find answers to many of your TSQL problems. It is still at the early stages and there is much content coming up. (I have not contributed anything significant yet, but I will do after I am done with my book.)

http://code.msdn.microsoft.com/SQLExamples

Saturday, April 12, 2008

Im Back!

It is quite a long time since I blogged. I was very busy at work and could not spent much time on writing, though I wanted to write about a few XML stuff. I have just become an MVP (SQL Server) and would like to thank everyone who has helped me to reach there directly or indirectly. Special thanks to Steve Jones, Jeff Moden, Arnie Rowland and Kent Waldrop for helping me and grooming me.

Thanks Guys!

Friday, January 25, 2008

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>

Wednesday, January 16, 2008

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.

Saturday, January 12, 2008

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.