July 2009 - Posts

You can now submit sessions for the next SQLBits on the 19th-21st November this year.

If you didn't know SQLBits is going west this time to Newport Wales. We are going to the Celtic Manor an amazing venue and the location for the 2010 Ryder Cup.

The event is going to run over 3 days enabling those with busy weekends to still come along.

We have Donald Farmer doing a pre con and speaking on the Friday about Gemini and SQL Server 2008 R2.

Its going to be bigger and better than last time so if you are interested in speaking then submit as session.

Start by completing your speaker profile and then submit sessions here


-
Posted by simonsabin | 1 comment(s)

If you've searched for the answer to a question, you must have found results that appear on the site http://Experts-Exchange.com whats annoying is that you go to the site and you have to pay to see the answer.

Well not quite, do you wonder how the results appear so well in the search engine, its because Experts Exchange provide the search engine with all the text of the answer.

They do this by putting all the answer right at the bottom of the page.

Try this, do the following search

http://www.bing.com/search?q=Parse+XML+using+T-SQL

The first result should be on Experts Exchange .

If you follow the link, you will find the page that looks something like this, not I am not logged in and it says we need to start a trial to view the solution.

However if you look at the scroll bar you will see the page is very long.

If you scroll to the bottom, passed all the different categories you will find the answer, completely readable.

Whats interesting though is that if you go to Experts Exchange directly and then navigate to an answer the answer won't be displayed. Clearly Experts Exchange are doing something different probably to improve their [SEO].

However you can still get the answer. To do this, take the title of the question and put that in your search engine. When you do the search the EE page should appear in the results, if you click on it you will be taken to what appears to be the same page but you will amazingly find the answer at the bottom. They must be doing something with the referrer, i.e. where you clicked the link, because the URL is the same.

So there you go, you can now get all Experts Exchange content for free

 


-
Posted by simonsabin | 2 comment(s)

Erland Sommarskog (http://www.sommarskog.se/) was recently investigating delimited strings and was finding odd behaviour, which he wanted verifying. The behaviour was that he was getting recompiles with certain functions he was using for string splitting.

I had a look and found exactly the same behaviour.

I started dissecting the code and found the issue was with string concatenation and use of tables. These two sps identify the problem

create   procedure usp_CollationBug

  @param nvarchar(max)

 as

 SELECT top 1 @param +N','  AS output

   FROM sys.objects 

go

create procedure usp_CollationBugTest

as

  dbcc freeproccache

  exec usp_CollationBug N'1,2,3'

  SELECT o.name,*

  FROM sys.dm_exec_cached_plans cp

OUTER APPLY (sys.dm_exec_query_plan(cp.plan_handle) qp

             LEFT JOIN sys.objects o ON qp.objectid = o.object_id)

  WHERE qp.dbid = db_id()

    and o.name = 'usp_CollationBug'

  ORDER BY o.name

If you execute usp_CollationBugTest then you will find that there is no record returned in the last query, i.e. there isn't a cached query plan.

So what is causing it. Well if you take off the string concatenation (+ N',') or the FROM sysobjects you will get a query plan cached

alter  procedure usp_CollationBug

  @param nvarchar(max)

 as

 SELECT @param +N','  AS output

 go

exec usp_CollationBugTest

go

alter  procedure usp_CollationBug

  @param nvarchar(max)

 as

 SELECT top 1 @param AS output

 from sys.objects 

go

exec usp_CollationBugTest

go

Both of these should return a cached plan.

So I thought about using a CTE, using CHAR to append the string, or using varchar and not Nvarchar

alter  procedure usp_CollationBug

  @param nvarchar(max)

 as

 ;with param as(select @param + ',' value)

 SELECT top 1 param.value  AS output

 from sys.objects 

 cross join param

go

exec usp_CollationBugTest

go

alter  procedure usp_CollationBug

  @param nvarchar(max) 

 as

 SELECT top 1 @param + char(44) AS output

 from sys.objects 

 go

exec usp_CollationBugTest

go

alter  procedure usp_CollationBug

  @param varchar(max)

 as

 SELECT top 1 @param +',' AS output

 from sys.objects 

 go

exec usp_CollationBugTest

go

But unfortunately none of these work.

So far I've found two solutions, one is to force the collation of the expression and the second is to not use MAX datatypes

alter  procedure usp_CollationBug

  @param nvarchar(max)

 as

 SELECT top 1 @param + N',' COLLATE SQL_Latin1_General_CP1_CI_AS  AS output

 from sys.objects 

 go

exec usp_CollationBugTest

go

alter  procedure usp_CollationBug

  @param nvarchar(4000)

 as

 SELECT top 1 @param + N',' COLLATE SQL_Latin1_General_CP1_CI_AS  AS output

 from sys.objects 

 go

exec usp_CollationBugTest

go

I thought about this more and thought about using STUFF, I came up with the following solution that does result in a cached plan

alter  procedure usp_CollationBug

  @param nvarchar(max)

 as

 SELECT top 1 stuff(@param,LEN(@param),1, right(@param,1) + N',') AS output

 from sys.objects 

 go

exec usp_CollationBugTest

go

I then thought about my post when is varchar(max) not a varchar(max) This highlights that you have to make sure your input variables are of a max type to get a max type out. So I tried the following, and found it worked.

alter  procedure usp_CollationBug
  @param nvarchar(max)
 as
 SELECT top 1 @param + cast(N',' as nvarchar(max))  AS output
 from sys.objects  
 go
exec usp_CollationBugTest
go

Many people use MAX data types as an easy opt out, DONT as it will lead to this problem if you are concatenating

The forcing of the collation should not be a problem as you have to specify it for your server, database and columns

So now you know the solution, why is it important. Query plans are essential for the performance of queries that are regularly executed, to avoid the compile time, this is especially true in OLTP systems.

If you do have a query that is executed 100s of times per second , avoiding compilation will more than likely improve performance

 


-
Posted by simonsabin | 5 comment(s)

I've recently started on a project to move a system from one database to another. The system consists of normal database objects, integration services packages and SQL agent jobs.

As part of the excercise I'm trying to identify dependencies so we make sure when we move, we move all the objects we need.

For database objects its not too bad. I am using SQL 2005 and so sqldependencies can give me these dependencies (sort of, not 100% reliable). When I get to SQL Agent jobs and SSIS packages I'm stuck. It comes down to painful had checking and lots of "Find in Files" to find dependencies

I would love a product that I could point at a table and build the dependency across jobs, packages as a minimum.


-
Posted by simonsabin | 1 comment(s)

I've just been installing VS a fresh and went looking for the Visual Studio Database Professional GDR and found that a GDR R2 had been released that supports SQL 2008. You can download it here http://www.microsoft.com/downloads/details.aspx?familyid=BB3AD767-5F69-4DB9-B1C9-8F55759846ED&displaylang=en#filelist

So on top of all the great things that you get with GDR you also get support for all the new features in SQL 2008, including the types, the TSQL Statements, Certificates etc.

And don't forget that if you have Visual Studio Team System Developer Edition you can get the Database edition for free. In Visual Studio 2010 they will be combined into a single product.


-
Posted by simonsabin | with no comments

We'll be announcing the date and venue for the next SQLBits on Monday

So keep posted, we've got some really exciting news.

I am really excited about the whole event. Its going to be bigger and better than the others.


-
Posted by simonsabin | with no comments