Uninstalling CTP

Want to get rid of previous CTP installations easily?
Check out this post. The VS 2005 Diagnostic and Uninstall Tool does the sometimes difficult task for you!

Override isolation level

When setting the transaction isolation level it could be nice to be able to control this on table level regardless of the level specified. SQL Server supports this by adding a WITH in the FROM clause.

Eg.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

SELECT Field FROM table t1
INNER JOIN table2 t2 WITH (READUNCOMMITTED) ON t1.ID = t2.ID

This would cause serializable locks on t1 and read uncommitted on t2.

String Summary Statistics

I found this little interesting information in this article

SQL Server 2005 includes patented technology for estimating the selectivity of LIKE conditions. It builds a statistical summary of substring frequency distribution for character columns (a string summary). This includes columns of type text, ntext, char, varchar, and nvarchar. Using the string summary, SQL Server can accurately estimate the selectivity of LIKE conditions where the pattern may have any number of wildcards in any combination. For example, SQL Server can estimate the selectivity of predicates of the following form:

Column LIKE 'string%'
Column LIKE '%string'
Column LIKE '%string%'
Column LIKE 'string'
Column LIKE 'str_ing'
Column LIKE 'str[abc]ing'
Column LIKE '%abc%xy'

If there is a user-specified escape character in a LIKE pattern (i.e., the pattern is of the form LIKE pattern ESCAPE escape_character), then SQL Server 2005 guesses selectivity. This is an improvement over SQL Server 2000, which uses a guess for selectivity when any wildcard other than a trailing wildcard % is used in the LIKE pattern, and has limited accuracy in its estimates in that case. The String Index field in the first row set returned by DBCC SHOW_STATISTICS includes the value YES if the statistics object also includes a string summary. The contents of the string summary are not shown. The string summary includes additional information beyond what is shown in the histogram. For strings longer than 80 characters, the first and last 40 characters are extracted from the string and concatenated prior to considering the string in the creation of the string summary. Hence, accurate frequency estimates for substrings that appear only in the ignored portion of a string are not available.

MySQL

I've been playing with MySQL for a project of a friend of mine (http://www.yawn.be).
I honestly love SQL Server even more now :-) I don't want to talk bad about MySQL itself because I don't know enough about the product to do so. But the tools provided are really sad as opposed to the tools that are included with SQL Server. I just wanted to know the queries that were run and their io, cpu, duration etc (<3 Profiler<3) but apparently there is no way with the standard tools to determine this. Graphical Query plans or even plans like the Text Query Plans in SQL Server... couldn't find it :-(

MSDN users to get early access to Visual Studio 2005 tools

According to this article in Infoworld we will have access to a release candidate of SQL Server 2005 and Visual Studio 2005 in September! The final versions would be available mid to end October.

Very good news!

Using Sp_configure To Change a Value Will Issue DBCC FREEPROCCACHE

Another little known fact I guess but Brian Moran pointed out that issueing an sp_configure triggers the DBCC FREEPROCCACHE which removes all cached procedure plans.

SQL Server Magazine's Reader's Choice Awards

An the award goes to....... A First Look at SQL Server 2005 for Developers.

Congratulations to the authors!

Buy

Adding a non nullable column without a default

When trying to add a column with a default that is not nullable to an existing table you get an error message (even when there are no records in the table)

ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column 'NewColumn' cannot be added to table 'tbl_test' because it does not allow nulls and does not specify a DEFAULT definition.

There is however an easy workaround.

ALTER TABLE dbo.tbl_test
ADD NewColumn smalldatetime NOT NULL
CONSTRAINT DF_ToDrop DEFAULT ('2000-01-01')

ALTER TABLE dbo.tbl_test
DROP CONSTRAINT DF_ToDrop

Google Talk

Google released an IM GoogleTalk.
Looks like MSN could get a decent competitor

Ken Henderson

For those of you that don't have it already... buy the book!

sql_variant

sql_variant is one of the datatypes that I don't often see being used although it has it's advantages. It's kind of like a varchar where you would be able to store numeric characters, alphanumeric characters, dates, ... with the difference that on a sql_variant field you would be able to determine of which datatype the value is.

One tip I would give you is to always explicitly cast the value you insert (or update) to the datatype you want it to be. Apparently SQL Server uses 2 extra bytes to determine the extra information like the datatype etc. (eg. int would be 6 bytes).

The SQL_VARIANT_PROPERTY function gives you information about the variant itself like datatype, total bytes, precision ...

For more information visit:
sql_variant
SQL_VARIANT_PROPERTY

An interesting Q&A with Kimberly L. Tripp

Read it here

Back and already having fun

Aaah back from my 2 week holiday. I can't say I'm thrilled to be back but I can live with it ;-)

Especially when I find things like this :-)

Here's a nice article about fragmentation

Holiday

Hi,

I've got 2 weeks off so you might not read very much this week and the week to come :)
Take care

Give the New PIVOT and UNPIVOT Commands in SQL Server 2005 a Whirl

Nice article about PIVOT and UNPIVOT in SQL Server 2005

This must be painful

Paul Thurrott wrote an article where he urges us to boycott IE because of it's lack of support for standards. Now someone has written a nice reply to this...

Ouch ouch ouch

Let’s get into standards now. Guess what, Paul? Your site,
WinSuperSite.com currently has 124
validation errors, according to the W3C’s Markup Validation Service.
Even worse, the page which contains your “Boycott IE” story currently has 207
validation errors. Both pages don’t even define the page’s doctype,
which is almost always the first line of the web page.

Page splits

Page splits are not too good for performance and should be closely watched.
One way of determining whether you are having page splits and how many is to use the function ::fn_dblog (@StartingLSN, @EndingLSN). Passing NULL to the two parameters makes sure that the whole log is being read.

eg.

SELECT [Object Name], [Index Name], COUNT(*)
FROM ::fn_dblog(NULL, NULL)
WHERE Operation = 'LOP_DELETE_SPLIT'
GROUP BY [Object Name], [Index Name]
ORDER BY COUNT(*) DESC

You can reduce page splits by specifying a good fill factor. There is no real rule of thumb about fill factors. It all depends on the rowsize and number of rows inserted (between index rebuilds). So specifying a good fill factor and a DBCC DBREINDEX from time to time will minimize the number of page splits.

An unexpected error occurred in Report Processing. (rsUnexpectedError)

I'm creating one of the most important reports for the business and I'm having a weird problem. The report renders just fine in the browser but as soon as I try to export it to a PDF (which is basically what we do with all our reports - of course automatically through the webservice) it crashes with the above mentioned error.

After some surfing I came across an article in the Reporting Services newsgroup where one MattiasT had the same problem and he found the conditions for it to happen.

1. You are using a table.
2. The HideDuplicates property is used on a detail cell in this table.
3. The table contains a subreport.

And sure enough... when I remove the HideDuplicates the report generates just fine.

Unfortunately there seems to be no solution for the time being, except for changing the way you create the report because having all 3 conditions is a prerequisite.

TempDB and missing Device

Just had a nice little incident :-s

One of our development servers was used for tests with a SAN disk. They moved our TempDB to the SAN and after the tests removed the SAN disks. No problem, but they forgot to move the TempDB location to the local disks. Result? No more SQL Server :-x

In order to get it back up we did the following:
Start SQL Server from the command prompt with the following syntax
sqlserver -f -c -T3609 -T4022

-f: minimal configuration mode
-c: not as a service (console mode so you can follow the nice little thingies SQL Server does)
-T3609: skip tempdb creation at startup
-T4022: bypasses automatically started procedures

Then we updated sysdatabases and sysaltfiles (I know it's hardcore) with the correct file locations.

Restart SQL Server and GO GO GO

The Daily WTF (What The F*ck)

For those of you that don't know the site, this is really one you should visit every day ;-)
It's a website that gathers programming stupidity on a daily basis.