Jacob's Blog

The XQuery implementation of SQL Server 2005 supported only a limited number of XQuery functions. SQL Server 2008 added support for two more XQuery functions: upper-case() and lower-case()

Both these functions are pretty simple to understand and self explanatory. The following example demonstrates them.

DECLARE @x XML
SELECT @x = '<Employee name="Jacob"/>'

SELECT
    x.value('@name','VARCHAR(20)') AS Name,
    x.value('upper-case(@name)','VARCHAR(20)') AS UpperName,
    x.value('lower-case(@name)','VARCHAR(20)') AS LowerName
FROM @x.nodes('/Employee') e(x)    
/*
Name                 UpperName            LowerName
-------------------- -------------------- --------------------
Jacob                JACOB                jacob
*/

I am not sure how many times you might want to forcefully close all the active connections and drop a database. However, this is a very interesting question.

One option to do this is to take the database in SINGLE USER mode and then issue a DROP DATABASE command.

USE master;
GO
ALTER DATABASE dbname 
SET SINGLE_USER 
WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE dbname;

I am exploring the FILESTREAM data type these days for my next book. So expect a number of blog posts on FILESTREAM in the next few weeks. Yes, I am back to SQL Server blogging after a short break.

I would like to start with clarifying a common misunderstanding on how FILESTREAM is implemented in SQL Server. Contrary to what some people think and what you could see in some resources on Internet, FILESTREAM is not a data type. FILESTREAM is an attribute that you can assign to a VARBINARY(MAX) column. When this attribute is set, SQL Server will store the content of the column in disk files, instead of storing it in the database file itself.

I have been looking for whitepapers on FILESTREAM and could find two of them.

Managing Unstructured Data with SQL Server 2008

FILESTREAM Storage in SQL Server 2008

Tech-ED India 2009 is over. It was a great experience meeting a lot of technology experts, product team and developers from all around the country. I met many readers of my blogs and articles and had a great time interacting with them. It was really interesting.

From the learning point of view, the most exciting activity was the ‘Advanced Data Recovery and Troubleshooting’ session by Vinod Kumar.

Vinod Kumar presenting on ‘Advanced Data Recovery and Troubleshooting’ (apologies for the low image quality)

Satya, my friend and SQL Server MVP, who travelled all the way from Scotland to India to speak on Tech-ED presented a wonderful session on High Availability. It was a great learning experience. He had a bunch of stuff to share with as, as he has worked with very large SQL Serer installations around the world. He manages huge databases with terra bytes of data and large database deployments with thousands of databases. He mentioned that the largest database deployment he worked contained 9500 databases. Satya is a SQL Server consultant and regular speaker at SQL Server conferences across the world.

Satya, presenting on ‘High Availability’.

There was an MVP – Product Team meet in the evening which was really wonderful. We (SQL Server MVPs) had a very long discussion with the SQL Server Product team (manageability and CE) and I think that was the most valuable take away from the Tech-ED this year.

SQL Server MVP – Product Product Team discussion

Tech-ED India 2009 is over and I am heading for my home town in Kerala. I will be back at work in 4 days.

During Tech-ED, I interviewed a number of people (SQL Server Exports, MVPs and Product Team members) and I have a lot of stuff to share with you, especially on the ‘Beyond Relational’ area. I will start posting them very soon.

Tech-ED is rocking and the second day was quite interesting. The day started with Keynote Session by Resul Pookutty (Oscar Award Winner – Film Sound Designer)

Keynote Session by Resul Pookutty

Resul gave a detailed overview of how technology is used in sound designing, recording, mixing etc. He played several clips from his films to explain his points.

One of the most interesting sessions I attended today was that of Vinod Kumar who explained ‘Data Compression in SQL Server 2008’.

Vinod Kumar presenting on ‘Data Compression in SQL Server 2008’

Another very interesting SQL Server session that worth mentioning was the presentation by Praveen Srivasta on ‘SQL Server 2008 - Beyond Relational’.

Praveen Srivasta presenting on ‘SQL Server 2008 – Beyond Relational’

I will be interviewing a number of SQL Server Experts (MVPs, Speakers etc) tomorrow. I will post the interviews early next week.

Jacob in the community zone (thank you Mahesh for taking this photograph)

For those of you who are eager to know what is happening with me and with Tech-ED in Hyderabad, here is a quick update.

The main attraction of the day was the Keynote session by Microsoft CEO Steve Ballmer.

Keynote Auditorium

Keynote by Steve Ballmer.

Stills from my presentation “XML Support in SQL Server 2008”

Some of you must have noticed that I became a bit irregular in blogging for the past few months. I was busy working on a number of critical assignments and most of my free time was dedicated to building the new website www.beyondrelational.com (which is still in progress).

www.beyondrelational.com

I am working with a graphic team to build the graphics and layout for www.beyondrelational.com and I think we will be able to show you something very soon. I am very excited to make this website available to the SQL Server community, especially for the people interested in the ‘beyond relational’ aspects of SQL server. We will focus on the ‘beyond relational’ side, without ignoring the ‘relational side’ of SQL Server. In essence, www.beyondrelational.com will focus on the ‘relational and beyond relational’ sides of SQL Server.

Tech-ED India 2009

Another news I would like to share with you is about Tech-ED India 2009. Tech-ED India 2009 will be held in Hyderabad from 13th May to 15th May.

I will be at Tech-ED and would be very happy to meet you all. I will be speaking on ‘XML Support in SQL Server 2008’ . If you are attending Tech-ED and would like to meet me, please send me a note at jacob@beyondrelational.com.

TSQL Challenges

Did you all notice the series of TSQL Challenges we are publishing for the last few months?  If you have not seen any of the challenges so far, I would recommend reading this page to get a basic idea of it: http://beyondrelational.com/blogs/tc/about.aspx

So far we published 6 challenges and a number of TSQL enthusiasts participated in the challenges. TSQL Challenges demonstrate very interesting and  efficient ways of solving many of the common database tasks. The latest challenge is live and I would like to invite all my readers to go ahead and participate. http://beyondrelational.com/blogs/tc/archive/2009/05/04/tsql-challenge-6.aspx