I have started a new article series at Simple Talk. It's all about the transition from procedural programming to declarative programming.
First article is found here
And it is already viewed 5500 times.
Today I had the opportunity to debug a system with a client. I have to confess it took a while to figure out the bug, but here it is
SELECT COUNT(*) OfflineData
Do you see the bug?
Yes, there should be a FROM clause before the table name. Without the from clause, SQL Server treats the name as an alias for the count column. And what do the COUNT always return in this case?
It returns 1.
So the bug had a severe implication. Now I now it's easy to forget to write a FROM in your query. How can we avoid these stupid mistakes?
Or do they just want to continue with their old habits? The reason for this blog post is that I the last week have tried to help people on several forums. Most of them just want to know how to solve their current problem and there is no harm in that. But when I recognize the same poster the very next day with a similar problem I ask myself; Did I really help him or her at all? All I did was probably to help the poster keep...
I got an email last friday telling me I was to keep my MVP status!
What do one say about that? Except "Thank you". To all that reads my articles and posts. To all who attends my presentations.
This years SQLBits occurred in Brighton. Although I didn’t have the opportunity to attend the full conference, I did a presentation at Saturday. Getting to Brighton was easy. Drove to Copenhagen airport at 0415, flew 0605 and arrived at Gatwick 0735. Then I took the direct train to Brighton and showed up at 0830, just one hour before presenting. This was the easy part. Getting home was much worse. Presentation ended at 1030 and I had to rush to the train station to get back to London, change to tube for Heathrow. Made it at the gate just...
A tale from a Stalker who licked his wounds and got back 9 months later...
A year or so back, I struggled with some consistency problems so I figured out I needed a way to "mount" backup files as a virtual database. At the time (SQL Server 2005 and SQL Server 2008) my choice fell on Idera's SQLvdb because it felt easy enough to use.
I used it a few times and it worked great. Some time later we upgraded to SQL Server 2008R2 and I didn't use SQLvbd for a long time. Until yesterday...
I was upset that suddenly SQLvbd took more than 2 hours to mount the backup file (if it succeeded at all). I...
will happen in just 4 days. The child part is taken care of. Multiple times.
Now it's time for the married part.
Me and my girlfriend Jennie will be married in Bjuv church at 3pm, Saturday the 28th of August 2010.
Pictures of Bjuv church can be found here.
After the ceromony we'll goto Hovs Hallar for dinner and party.
The famous parts (The chess scenes) of the movie The Seventh Seal by Ingmar Bergman was shot here. We'll sleep there since most of Jennie's family comes from Stockholm (300 miles away).
So I'll soon wear a ring on my finger. I tried the "rule them...
The built-in CHECKUM function in SQL Server is built on a series of 4 bit left rotational xor operations. See here in a previous forum post http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70832 for more explanation.
Today, I wanted to see how often a collision (or false positive) can occur.
Let's take a very simple CHECKSUM value, for example 123. Decimal 123 is "01111011" in binary representation.
Since CHECKSUM function rotates the iterative checksum value 4 bits to the left (same thing as multiplying by 16), how many permutations of two characters returns the same CHECKSUM value of 123? The answer is 16 permutations.
Let's investigate by writing down the solution of this...
As some of you know, I was awarded Microsoft Most Valuable Professional (MVP) for SQL Server in July last year. Now it's time to see if I get my MVP renewed, or if I lose the award.
I honestly believe it's a good thing MVP status only lasts for one year at a time. Knowledge is fresh. Things that worked in the past may not work any longer due to evolvement, and there are smarter ways to do things now, than before.
Being an MVP is a responsibility. It does mean you have more than average knowledge of SQL Server and how...
Let me start by writing I am a supreme VB6 programmer, but I have very little experience with VB.Net, so I think I still need some more time learning SmartAssembly.
SmartAssembly make obfuscating and merging dll files a piece of cake! With it's simple, straight forward and clean GUI I did make my tests work. With other obfuscators like Xenocode, Salamander etc which lets you (and in some cases forces you) control more advanced settings, you really have to know what you are doing.
Especially when it comes to protecting code that uses external dependencies.
My most annoying experience is that if you start checking radio buttons and activating...
In my past competition for Phil Factor 'Subscription List' SQL Problem, I presented a technique of Ordered CTE Update which is explained more in detail by Mladen Prajdic.
In the comments to the in-depth analysis follow-up article by MVP Kathi Kellenberger, there is now some sort of debate whether or not if it's responsible to present a technique like that.
The nay-sayers argument are valid
Ordered updates are not supported by Microsoft
It is an undocumented and unsupported feature which may break in next service pack or version of SQL Server
Most of the nay-sayers reference to the traditional "Quirky Update",...
Yesterday I got the brilliant idea to format my laptop and reinstall Windows 7 from scratch. I've had a few problem last month, mostly due to upgrading the preinstalled Vista to Windows 7.
Said and done, I boldly finished the installation and thought everything was ok. I mean, my email is IMAP so all email are stored at my ISP, all my relevant documents are on another partition on the harddrive and so on...
Now I have noticed I forgot to save my Favorites Links.
So now I need your help. I can try to remember all my favorites (about 80 of...
PASS Scania is a new PASS chapter in Sweden which will promote the interest, networking and knowledge for professional SQL Server-users such as developers, DBA's and BI-specialist in Skåne. The Chapter also has a business alliance with an existing usergroup SQLUG.
All developers who use SQL Server platform in their work is welcome to participate in the seminars with start february 4th 2010. Both consultants and employees are welcome.
Membership and seminars are free and activities are mainly sponsored events.
First meeting is held in feburary where new members have the opportunity to present themself, we will present the agenda for...
I created a small table with about 150,000 records in order to do some aggregations. There are some prerequisites to comply for, but that is easily done with replace function.
With Azure, it took almost 4 times as long time as SQL Server Express on my old development machine to complete the query.
With this simple test, I find the use for Azure very limited, as of today. It is excellent for small companies who is not hosting their own environment themself. For companies on a web hosting company which doesn't support Microsoft SQL Server it will do just fine.
But today, almost...
Today, it seems the transfer for the MSDN subscription I won for the Adam Machanic Grouped String Concatenation finally is done. It took almost 4 months with a lot of phone calls with Microsoft (I have spent more than 7 hours in this case).
I won the MSDN subscription before I was awarded the MVP SQL Server. With the MVP award you get a MSDN subscription, so there I was sitting with two almost identical subscriptions. I begun to feel uncomfortable with it, so I decided to give the MSDN subscription I won to the runner-up, Leonid Koyfman from Razorfish.
We had almost...
Since I became a two-time winner of the "Phil Factor SQL Speed Phreak" competition, I was asked to host the next competition.
You can find the competition here http://ask.sqlservercentral.com/questions/826/the-fifo-stock-inventory-sql-problem
The "Phil Factor SQL Speed Phreak" competitions are about everyday problems and to get people come together and give input on how to solve the current problem in best possible way.
Here is the lates addition to my family. In this picture the boy is 10 days old.
The project name is now decided as "Johan Samuel Peter", username Samuel.
Today our son was born. A healthy baby of 4.4 kg (9.8 lbs) and 53 cm (20.9 inches).
Both mother and son are well, and spend the night at the safety on the hospital.
Water broke 2:45 am this morning and we were admitted to the ward at 4:21. Our son was born without complications at 5:06 am.
Big sisters Isabelle (23 months) and Filippa (5.5 yr) are anxiously waiting for mother and new baby to get home.
Yesterday I updated my site. It was not that appealing.
This is how it looked before
and this is how it loooks now
I think it's a great improvement, even if there still are some things to fix.
Phew! Now it's finally done.
I haven't coded outside SQL Server since 2007 and that was with VB version 6.0.
Well, I felt the need to start code again (at least for SQLCLR), since there are many tasks that will be easier to do with a SQLCLR routine. To start easy, I coded a "least square regression" routine and you can download it from this location
Sample.sql http://regression.developerworkshop.net/Sample.sql (good linear regression)
Sample2.sql http://regression.developerworkshop.net/Sample2.sql (better linear regression)
Sample3.sql http://regression.developerworkshop.net/Sample3.sql (polynomial regression)
* I have created a homepage for this SQLCLR function http://regression.developerworkshop.net
For the Sample2 above, the result for c: drive on Server1 look like this
<dws bestfit="linear" r2="0.99" type="least square...
Some days ago I posted a solution for a simple problem on a forum about to delete multiple spaces in one statement (not using loop). My suggestion was
declare @s varchar(100)
set @s = 'xxxx yyyyy zzzzzz'
SELECT REPLACE(REPLACE(REPLACE(@s, ' ', ' ' + CHAR(2)), CHAR(2) + ' ', ''), CHAR(2), '')
I used CHAR(2) because that is not commonly used in normal texts. I then thought I could use CHAR(0) to be on the "safe" side, and now strange things begun to happen.
Run this on your own risk, as you will see soon.
SELECT REPLACE(REPLACE(REPLACE(@s, ' ', ' ' + CHAR(0)), CHAR(0) +...
Finally, I am on vacation.
I can't even remember when I had 4 week of contigous vacation last time!
I wish the best to all of you, and see you soon again. I will sporadically visit SQLTeam and the other forums during this period.
There will be some time left for me to do this after all my girls have gone to bed.
Today it happened. I received the Microsoft MVP Award for my contributions to the Microsoft SQL Server community. I am very honored by the award and I will continue to work hard for the community to keep their trust in me. I will continue to update this blog and help users out in the SQLTeam forums, SqlServerCentral forums, SQL Server Magazine forums and SQL Server Developer Center forums among a few other. Thank you to everyone who reads this blog and leave comments....
I've posted a feedback on Microsoft Connect about how to enhance the INSERT INTO syntax.
Especially for INSERT INTO ... EXEC ...
Sometimes when you need the result from a stored procedure, the SP itself returns two (or more) resultsets.
And it's only possibly to fetch and store the first resultset.
What I have suggested is an enhanced syntax for INSERT INTO ... EXEC, like this
INSERT INTO Table1 (Col1, Col2), Table2 (ColX, ColY, ColZ)
EXEC usp_MyStoredProcedure @Param1, @Param2
In this example, usp_MyStoredProcedure returns three resultsets, of which I want to store the two first.
First resultset has two columns, and second resultset has three columns.
Let Microsoft know...
I've found an issue with the Debugger for SQL Server 2008 Management Studio a while ago.
This is my way to ask you to endorse a fix
Please let Microsoft know what you think about this suggestion.
A few months back, Adam Machanic launched a competition about "Grouped string concatenenation" here http://sqlblog.com/blogs/adam_machanic/archive/2009/02/27/t-sql-challenge-grouped-string-concatenation.aspx
Now Adam has publish his results here http://sqlblog.com/blogs/adam_machanic/archive/2009/05/31/grouped-string-concatenation-the-winner-is.aspx and luckily my fourth suggestion was considered the overall winner!
When seeing my competitors, I am humble to see that my suggestion performed better than other suggestions made by such people as Itzik Ben-Gan, Remus Rusanu, Rob Farley and others.
Of course I am proud to win the MSDN Premium subscription, and now I have a hunch that next competition will be with Microsoft to get this baby activated ;-)
Thanks to all who participated. I have learned a few new tricks.