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