June 2009 - Posts
I am just back from Tech-Ed Trivandrum. Both I and Pinal left for Trivandrum on Friday noon and reached Trivandrum by 5.30 PM. K-Mug had organized a small get together where we had a great time meeting other speakers and community leads.
I was hoping to get a copy of the all the photographs from the event photographers, and did not click too many photos by myself. Unfortunately, the memory card they had in their cameras did not work with my laptop and I could not get those photos yet. I will be getting them soon and will upload them in a follow-up blog post.
The conference center was pretty good and was equipped with world class conference facilities.
Attendees were very active and responsive. I had a great time doing my session very interactively.
Well, this is Jacob speaking. Borrowed from the photos uploaded by another MVP friend.
Pinal’s session on SQL Server Best Practices
I had taken with me a few copies of my recent book ‘The Art of XSD’ and gave away to a few attendees interested in XML Schema collections.
Tech-Ed Trivandrum was very interesting and we had a great time there. Pinal is still travelling and will make a more detailed post once he is back. Keep a watch on his blog. I will make a follow-up post soon with more photos (once I get it from the event photographers).
K-MUG is organizing Tech-ED Trivandrum on 27th June 2009. Along with Tech-ED, they are also launching an official PASS Chapter in Trivandrum. You can find the agenda of the event here.
I will be at Tech-ED and look forward to meet many of you there. My presentation is about ‘Logical Query Execution Flow’ where we will discuss how a given TSQL query is processed and executed. A good understanding of the query execution flow will help us to solve several common problems and will help us to answer a number of questions that we come across often.
Pinal Dave, SQL Server MVP and my very close friend is also travelling with me and he will be presenting on ‘SQL Server Best Practices’. This will be a ‘must-attend’ session for all SQL Server developers. He will be showing a number of SQL Server tips, tricks and best practices that will help you to be more productive.
I will be bringing a few copies of my recent book, ‘The Art of XSD – SQL Server XML Schema Collections’ and some of you can grab a copy of it. Since the book is on a completely different topic (from the topic of my presentation), I wont be giving them away during my presentation. Since I have limited copies of the book, we will do the following:
- If you want to get a copy of the book, send me an email along with a note describing your exposure to XML Schema Collections.
- If I receive more requests than the number of books I have, I will select a few people using a ‘custom logic’ :-)
- You will get the book only if you are present at Tech-ED and attend all the sessions
- If anyone wants to meet me and discuss about the topics in the book, or SQL Server stuff in general, I will be available. Just send me a note in advance.
- If you wanted to get a copy of the book, but could not get it because too many people requested, you can download the FREE ‘ebook’ version from http://beyondrelational.com/blogs/jacob/archive/2009/04/26/my-latest-book-the-art-of-xsd-sql-server-xml-schema-collections-available-for-free-download.aspx
- If you don’t like the FREE ebook version, you can get a printed copy from amazon.com. http://www.amazon.com/Art-XSD-SQL-Server-schemas/dp/1906434174/ref=ntt_at_ep_dpt_1
Hope to see many of you in Trivandrum!
For those of you who are eager to know what happened with Tech-Ed event in Ahmedabad, here is a quick summary. Pinal has written a very detailed version in his blog site.
Tech-ED Ahmedabad was a great event and was very successful from all the aspects. Over 200 people turned in from various parts of the state. There were also a number of people who came from far-off places like Mumbai and Delhi. We really appreciate their enthusiasm towards technology.
Registration
Busy registration desk!
Most people arrived at the venue much ahead of the time. We were surprised to see people who arrived at the venue as early as 2-3 hours prior to the event schedule. Aryaman Desai and his Event Management team did a wonderful job by making all the required arrangements to make everyone comfortable.
Fortunately, we managed to move to a larger conference room in the hotel, to accommodate the unexpected number of people who walked in to attend TechED.
Kick-Start
Microsoft Student Partner, Dipen Shah introducing PASS.
Microsoft Student Partner Dipen Shah kept the attendees ‘involved’ till the sessions began. He started with a ‘technology quizz’ and then moved to a Q&A session and then a community discussion where Pinal, Jacob and other attendees participated and shared their views on various topics.
Dipen did a wonderful introduction of Tech-ED and explained what user groups are. He gave an overview of PASS, PASS activities and PASS summit.
Keynote
Keynote by Pinal
Pinal started the Keynote and took the audience over the history of Windows. He showed images of Windows starting from the first version to the recent version, Windows 7. He then went over the history of SQL Server, quickly explained highlights of each version.
Keynote by Jacob
I spoke about the current and future data storage and management requirements and explained that SQL Server is fully equipped to handle it. We briefly examined the new features of SQL Server 2008, especially FILESTREAM and SPATIAL support.
SQL Server Best Practices by Pinal Dave
Right after the Keynote and a short break, we started with the sessions. Pinal started with ‘SQL Server Best Practices’, one of his favorite subjects.
I have attended his presentation on ‘SQL server best practices’ a number of times in the past (we do travel and present sessions together). However, every time I attend his session, I find a completely new and interesting set of ‘tips, tricks and best practices’ in his presentation. The examples were real-life and were easy to understand and it was one of the best SQL Server sessions I have attended so far.
Pinal’s session on ‘SQL Server Best Practices’
Logical Query Execution Flow by Jacob Sebastian
My session was all about the logical query execution plan and we examined a number of common mistakes, questions, misunderstandings and performance problems. A detailed examination of the logical query execution flow answered all those questions, solved the problems and clarified some of the common mistakes and misunderstandings.
Logical Query Execution Flow by Jacob
“The Art of XSD – SQL Server XML Schema Collections”
Pinal was very excited about my recent book, “The art of XSD” and he always wanted to have a printed copy. Fortunately, I got a few printed copies right before the Tech-ED event and I gifted him a copy of the book at the end of my presentation.
Pinal receiving a copy of ‘The Art of XSD’ from Jacob
TSQL Challenges Winner Certificate
Many of you must be aware of the series of puzzles that we run at www.tsqlchallenges.com. Pinal is one of the winners and I handed over a printed certificate to him during the event.
Pinal receiving ‘TSQL Challenges Winner award’ from Jacob
A few people you should know
Pinal and Kaushal Parik, ASP.NET MVP
Kaushal Parik is an ASP.NET MVP from Ahmedabad. He is working as a Senior Software Engineer in Gateway Technolabs Pvt. Ltd. He has 4+ years of hands-on experience on .NET Technologies and has developed projects based on .NET and Microsoft server technologies like Commerce Server and BizTalk Server. He spends a lot of time helping the communities on Microsoft technologies. He blogs at http://dotnetslackers.com/community/blogs/kaushalparik/.
Pinal and Ritesh Shah
Ritesh Shah runs a website http://www.sqlhub.com/ and has written a number of technical articles. He is working as a Project Lead and DBA at a leading IT company. He has got over 9 years of experience.
Pinal, Aryaman Desai and Jacob
Aryaman Desai is one of the key persons who contributed quite a lot for the success of this event. His firm, ‘Arya Events’ took care of the event management. His team did a wonderful job by making sure that everything was well planned, executed and managed. While everyone would expect from an event management company to manage the planned activities, Arya Events is exceptional, as they could handle anything that would happen ‘unplanned’ and ‘unexpected.
Thank you!
I would like to thank everyone for their enthusiasm and support to make this event very successful.
I heard this question a number of times in the SQL Server forums. I used to suggest people to create DDL triggers (from SQL Server 2005 onwards) to audit schema changes. However, this can capture only changes applied after the DDL trigger is configured. The next best option was to buy a third party tool that can read the transaction log and display the information.
Paul S Randal published a wonderful blog post that explains how to use an undocumented function ::fn_dblog() to read this information from the transaction log. Read his post here: http://www.sqlskills.com/BLOGS/PAUL/post/Finding-out-who-dropped-a-table-using-the-transaction-log.aspx
My session abstract on “XML Schema Collections” is approved by the PASS Summit Program Committee and I will be getting the ‘great’ opportunity to speak at PASS Summit 2009, Seattle, Washington, during the first week of November this year.
The title of my presentation is “SQL Server 2008 - Create powerful XML Schema collections to validate your XML documents” and I will focus on explaining how effectively XML Schema Collections can be used to validate XML documents that a database application exchanges (sends and receives).
My presentation will be based on my recent book “The Art of XSD – SQL Server XML Schema Collections” which is available as a free download. It is a 485 page book that covers XSD and XML Schema Collections from basic to advanced levels.
Hope to see many of you in Seattle.
If your like to meet me and discuss your comments/questions about the points discussed in the book, you might find me in the Red-Gate booth during the summit and I will be very happy to talk to you.
We getting a lot of interesting feedback on 'TSQL Challenges' and the story of French MVP Aurelien is very interesting. http://beyondrelational.com/blogs/tc/archive/2009/06/14/aurelien-and-matthieu-have-fun-with-tsql-challenges.aspx
The whole idea of 'TSQL Challenges' is to inspire people to think differently (from the way one used to) and write better TSQL Queries. I am very excited to see that 'TSQL Challenges' indeed does that.
If you find TSQL Challenges interesting please support it by spreading the word (through your blog, website, tweets etc)
Thank you for your continuous support
We have seen a number of string manipulation examples using XML. Here is yet another example that uses FOR XML to convert a VARBINARY value to VARCHAR.
Some one asked me this question in one of the forums. The story is like this. The OP did a data migration project and the original data from the ORACLE database is imported to a SQL Server database. After the migration, he noticed that one of the VARCHAR columns in the source database is imported as VARBINARY column in SQL Server. Now he wanted to convert the VARBINARY value back to VARCHAR.
Here is a simple example that demonstrates the case.
DECLARE @x VARBINARY(10)
SELECT @x = CAST('10' as VARBINARY(10))
SELECT @x AS VarBinaryValue
/*
VarBinaryValue
----------------------
0x3130
*/
The original value was ‘10’ which was converted to VARBINARY and the result is ‘0x3130’. We need to write a query to convert ‘0x3130’ back to ‘10’.
I could not find an easy method to achieve this. I wrote a quick and dirty piece of code using FOR XML to achieve this.
DECLARE @x VARBINARY(10)
SELECT @x = CAST('10' as VARBINARY(10))
SELECT @x AS VarBinaryValue
/*
VarBinaryValue
----------------------
0x3130
*/
SELECT (
SELECT
CHAR(SUBSTRING(@x,number,1)) AS 'text()'
FROM master..spt_values
WHERE type = 'P'
AND Number BETWEEN 1 AND LEN(@x)
FOR XML PATH('')
) AS TextValue
/*
TextValue
-------------------------------------------
10
*/
Update on 14 June 2009
RBarry commented that this can be achieved by a simple CAST back to VARCHAR. Yes, it works! I wonder what went wrong when I tested it initially and could not get back the original value. Anyway, the XML approach is not needed any more. A simple cast will do the trick as shown in the below example.
DECLARE @x VARBINARY(10)
SELECT @x = CAST('10' as VARBINARY(10))
SELECT
@x AS VarBinaryValue,
CAST(@x AS VARCHAR(10)) AS VarcharValue
/*
VarBinaryValue VarcharValue
---------------------- ------------------------------
0x3130 10
*/
FOR XML Tutorials
Count Down has started for the much awaited Tech-ED Ahmedabad (IT Pro – SQL Server). Just 9 days left for the event. For event details and registration, visit: http://techedahmedabad.eventbrite.com/
Pinal has launched a competition to spread the word in the community about the event. Read his post: http://blog.sqlauthority.com/2009/06/11/sqlauthority-news-registration-and-competition-teched-on-road-ahmedabad-june-20-2009-saturday/
For those of you who missed Tech-ED India 2009 last month, there is a reason to rejoice. Tech-ED is coming to Ahmedabad on 20th June 2009. Mark your calendars and be at Hotel Rock Regency, CG Road, Ahmedabad on 20th June 2009 by 1.30 PM.
We will have a number of SQL Server sessions: SQL Server Best Practices by Pinal Dave, A closer look into the Query Execution Flow along with some optimization tips by Jacob Sebastian and a joint session by Pinal and Jacob on the new Data Compression feature introduced in SQL Server 2008.
We will also have a quick look into Exchange Server 2010, Windows Server 2008 and Virtualization.
In addition to the technical sessions, we will have a number of interesting activities, gifts and snacks :-).
Click here to Register
Hope to see you at Tech-ED Ahmedabad!
The Unattended installer can take two additional parameters (optional) to configure FILESTREAM as part of the installation process.
/FILESTREAMLEVEL
This parameter can be used to specify the FILESTREAM Access Level. This parameter can take one of the following values to configure FILESTREAM with the required level of access.
- 0 =Disable FILESTREAM support
- 1=Enable FILESTREAM for Transact-SQL access.
- 2=Enable FILESTREAM for Transact-SQL and file I/O streaming access.
- 3=Allow remote clients to have streaming access to FILESTREAM data.
/FILESTREAMSHARENAME
You need to specify a Windows Share Name while configuring FILESTREAM. This parameter can be used to specify the windows share name. This parameter is required if /FILESTREAMLEVEL has a value greater than 1.
Additional Reading: http://msdn.microsoft.com/en-us/library/ms144259.aspx
I am very glad to announce the news that we have finally created a logo for BeyondRelational.com. Here is the new ‘beyondrelational’ logo.
TSQL Challenges, a popular channel we run at beyondrelational.com has got a new logo as well.
We are working very hard on finalizing the layout and theme of the website. I will keep you posted about the progress we make on this front.
SERVERPROPERTY() function can be used to retrieve the Windows Share name configured for FILESTREAM access. Here is an example:
SELECT
SERVERPROPERTY ('FILESTREAMShareName') AS FileSreamShareName
/*
FileSreamShareName
--------------------
KATMAIFS
*/
I saw this question recently in one of the forums and wrote a small piece of code to retrieve this.
SELECT
name,
CASE
WHEN is_percent_growth = 0
THEN LTRIM(STR(growth * 8.0 / 1024,10,1)) + ' MB, '
ELSE
'By ' + CAST(growth AS VARCHAR) + ' percent, '
END +
CASE
WHEN max_size = -1 THEN 'unrestricted growth'
ELSE 'restricted growth to ' +
LTRIM(STR(max_size * 8.0 / 1024,10,1)) + ' MB'
END AS Autogrow
FROM sys.database_files
/*
name Autogrow
-------------------- -------------------------------------------------
test 1.0 MB, restricted growth to 100.0 MB
test_log By 10 percent, restricted growth to 2097152.0 MB
*/
To learn more about sys.sysdatabases, see http://msdn.microsoft.com/en-us/library/ms174397.aspx
In XQuery Lab 10 we saw how to insert an attribute to an XML document and in XQuery Lab 11, we say how to insert an element to an XML Document. We have not seen an example that inserts an XML fragment to another XML document. So, let us see how to do this.
SQL Server 2005 does not allow inserting XML fragments into an XML document. SQL Server 2008 enhanced the “modify()” method of XML data type to be able to insert XML fragments into an XML document. Here is an example that demonstrates that.
DECLARE @emp XML
SELECT @emp = '
<Employee>
<FirstName>Jacob</FirstName>
<LastName>Sebastian</LastName>
</Employee>'
DECLARE @ph XML
SELECT @ph = '<Phone>+91 9979882144</Phone>'
SET @emp.modify('
insert sql:variable("@ph")
as last into (/Employee)[1]')
SELECT @emp
/*
<Employee>
<FirstName>Jacob</FirstName>
<LastName>Sebastian</LastName>
<Phone>+91 9979882144</Phone>
</Employee>
*/