Tuesday, October 13, 2009

Win A Copy Of SQL Server 2008 Administration in Action

Rod Colledge was kind to give the readers of Less Than Dot a chance to win a pdf copy of his book SQL Server 2008 Administration in Action.

Here is how you can win this book, first read the Interview With Rod Colledge About The Book SQL Server 2008 Administration in Action post and tell me which question or answer you liked the most and why. Leave a comment on that post, do not leave it here. I will announce a winner this Friday at 12 PM EST here: Interview With Rod Colledge About The Book SQL Server 2008 Administration in Action

Wednesday, August 05, 2009

Some SQL Server posts I wrote lately and I now have a RSS feed just for my posts on lessthandot.com

The reason I am writing this post is that we now have RSS feeds on lessthandot for a specific author. In my case the RSS feed is the following: http://blogs.lessthandot.com/index.php/All/?tempskin=_rss2&author=4

This URL has all the blogposts I have written: http://blogs.lessthandot.com/index.php/All/?disp=authdir&author=4

Here is a list of the last 10 posts I wrote about SQL Server

And remember, if you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum

Monday, November 24, 2008

With this post I bid you all adieu

I have posted on this blog for a little over 3 years now and decided it is time to call it quits at this URL.
All SQL Server related post from me in the future will be on SQLBlog.com and on lessthandot.com.
All non tech post will be on denisgobo.blogspot.com, here you will also be able to connect with me on twitter, flickr, friendfeed and linkedin

below are some URLs

Database Blogs
Database Blogs RSS feeds
All Blogs
All Blogs RSS Feed

My Blog
My Blog RSS feed

Personal Blog
RSS Feed

See you there, don't forget to subsrcibe

Friday, October 31, 2008

SQL Server 2005 Service Pack 3 Beta and SQL Server 2008 Feature Pack October 2008 available for download

SQL Server 2005 Service Pack 3 Beta and SQL Server 2008 Feature Pack October 2008 are available for download

Download SQL Server 2005 Service Pack 3 - Beta here: http://www.microsoft.com/downloads/details.aspx?FamilyId=D22317E1-BC64-4936-A14B-7A632B50A4CA&displaylang=en

To obtain SQL Server 2005 SP3 Beta for Express Edition or Express Edition with Advanced Services go here: http://www.microsoft.com/downloads/details.aspx?FamilyId=80DFF99C-CC82-4639-92BB-BBDAFF677792&displaylang=en

Microsoft SQL Server 2008 Feature Pack October 2008 is a collection of stand-alone install packages that provide additional value for SQL Server 2008. It includes the latest versions of:

  • Redistributable components for SQL Server 2008.
  • Add-on providers for SQL Server 2008.
  • Backward compatibility components for SQL Server 2008.
Download it here: http://www.microsoft.com/downloads/details.aspx?FamilyID=228de03f-3b5a-428a-923f-58a033d316e1&DisplayLang=en

Thursday, October 16, 2008

today is a sad day

This was not the way I envisioned going back to Amsterdam. My mom past away today, two weeks shy of her 60th birthday
Since my passport was expired they issued me a temporary passport. My parents were visiting me in Princeton last month so I did see my mom recently. I just feel sad for my dad now who is all alone. :-(

Friday, October 03, 2008

Help me win this bet

You might know that some friends and myself have launched a site named LessThanDot, this is a site where users can collaborate via the wiki, ask questions in the fora and can read out blogs. It is a technical site but we do have a forum where you can post pretty much anything you want(not vulgar or offensive). Here are some examples
The Rage of the Previously Rich 
We're In Ur Bank, Bailing It Out 
Girls Habanero Eating Contest 

Of course we do have our technical forums, some examples
Microsoft SQL Server
Tech Rants

So I made a bet with the other owners of this site that I will have the most people sign up until November 1st. So please sign up and let your coworkers/friends know and let them sign up too, after all you don't want me to lose or do you?

Don't be scared to participate in the forums  :-)

My username is SQLDenis

Monday, August 25, 2008

Interesting Bug/Feature In SQL Server 2008 RTM

Someone had a problem with 8 year old procs which started to fail after moving to SQL Server 2008
Of course he should have used ints, but let's see what happens

Run this code on SQL Server 2005 and 2000

DECLARE @num_Passed Numeric(2, 0);
SET @num_Passed = -1;
SELECT @num_Passed

IF (@num_Passed = 0)
PRINT 'True';

No problem right?

Run just this part on SQL 2008

DECLARE @num_Passed Numeric(2, 0);
SET @num_Passed = -1;

SELECT @num_Passed

No problem either
Now run this whole thing

DECLARE @num_Passed Numeric(2, 0);
SET @num_Passed = -1;
SELECT @num_Passed

IF (@num_Passed = 0)
PRINT 'True';

Oops, this is what we get
Server: Msg 8115, Level 16, State 2, Line 7
Arithmetic overflow error converting expression to data type tinyint.

Change the -1 to 1

DECLARE @num_Passed Numeric(2, 0);
SET @num_Passed = 1;
SELECT @num_Passed

IF (@num_Passed = 0)
PRINT 'True';

No problem either.

Run this

IF (convert(Numeric(2, 0),-1) = 0)
PRINT 'True';

That fails
Let's make it numeric(3,0)

IF (convert(Numeric(3, 0),-1) = 0)
PRINT 'True';

No problem, that runs fine. So is this a bug because of implicit conversion to tinyint which can't hold negative values?

Sunday, August 17, 2008

Only In A Database Can You Get 1000% + Improvement By Changing A Few Lines Of Code

Take a look at this query.


  • (

  • SELECT customer_id, ‘MTD’ AS record_type, COUNT(*), SUM(), AVG()

  • FROM payment_table

  • WHERE YEAR(payment_dt) = YEAR(GETDATE())

  • and MONTH(payment_dt) = MONTH(GETDATE())

  • GROUP BY customer_id) MTD_payments


  • (

  • SELECT customer_id, ‘YTD’ AS record_type, COUNT(*), SUM(), AVG()

  • FROM payment_table


  • WHERE YEAR(payment_dt) = YEAR(GETDATE())

  • GROUP BY customer_id) YTD_payments


  • (

  • SELECT customer_id, ‘LTD’ AS record_type, COUNT(*), SUM(), AVG()

  • FROM payment_table) LTD_payments

  • ) payments_report

  • ORDER BY customer_id, record_type

  • Can you see the problem?
    A person had this query, it would run for over 24 hours. Wow, that is pretty bad, I don't think I had ever written something that ran over an hour, and the ones I did were mostly defragmentation and update statistics jobs.

    The problem is that the following piece of code

    where year(payment_dt) = year(getDate())
    and month(payment_dt) = month(getDate())

    is not sargable. First what does it mean to be sargable? A query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query (using index seeks, not covering indexes). The term is derived from a contraction of Search ARGument Able.

    This query is not sargable because there is a function on the column, whenever you use a function on the column you will not get an index seek but an index scan. The difference between an index seek and an index scan can be explained like this: when searching for something in a book, you go to the index in the back find the page number and go to the page, that is an index seek. When looking for something in a book you go from page one until the last page, read all the words on all the ages and get what you need, that was an index scan. Do you see how much more expensive in terms of performance that was?

    Let's get back to the query, what can we do to make this piece of code use an index seek?
    where year(payment_dt) = year(getDate())
    and month(payment_dt) = month(getDate())

    You would change it to this:
    where payment_dt >= dateadd(mm, datediff(mm, 0, getdate())+0, 0)
    and payment_dt < dateadd(mm, datediff(mm, 0, getdate())+1, 0)

    You can see the complete question on the MSDN forum site here:
    The Person said that his query went from over 24 hours to 36 seconds. Wow!! That is very significant. hardware cannot help you out if you have bad queries like that.

    The same exact day I answered a very similar question, take a look here: http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3752248&SiteID=1

    The person had this

    AND DATEDIFF(d, '08/10/2008', DateCreated) >= 0

    AND DATEDIFF(d, DateCreated, '08/15/2008') >= 0

    I told him to change it to this

    AND DateCreated >= '08/10/2008'
    and DateCreated <= '08/15/2008'

    And that solved that query. If you are interested in some more performance, I have written some Query Optimization items on the LessThanDot Wiki. Below are some direct links

    Case Sensitive Search
    No Functions on Left Side of Operator
    Query Optimizations With Dates
    Optimization: Set Nocount On
    No Math In Where Clause
    Don't Use (select *), but List Columns

    If you are interested in some blogposts about dates, take a look at these two which I wrote earlier
    How Are Dates Stored In SQL Server?
    Do You Know How Between Works With Dates?

    Wednesday, August 13, 2008

    SQL Tip, Compiling Your SQL Without Running It to See If It Would Run

    Let's say you have a big SQL script with a ton of code and you want to make sure it runs but you don't want to execute it because it updates tables, deletes data etc etc.
    Take this simple example

    SELECT 1/asasasas

    You can probably guess that the second statement is not valid, when you have a lot of code it is more difficult to spot these things.
    Execeute the code above and you will get this

    (1 row(s) affected)

    Server: Msg 207, Level 16, State 1, Line 1
    Invalid column name 'asasasas'.

    SQL server has the SET NOEXEC statement. From BOL:
    When SET NOEXEC is ON, Microsoft® SQL Server™ compiles each batch of
    Transact-SQL statements but does not execute them. When SET NOEXEC is OFF, all
    batches are executed after compilation.

    The execution of statements in
    SQL Server consists of two phases: compilation and execution. This setting is
    useful for having SQL Server validate the syntax and object names in
    Transact-SQL code when executing. It is also useful for debugging statements
    that would usually be part of a larger batch of statements.

    The setting
    of SET NOEXEC is set at execute or run time and not at parse time.

    So execute the code below


    SELECT 1/asasasas

    As you can see the output is the following:
    Server: Msg 207, Level 16, State 1, Line 1
    Invalid column name 'asasasas'.

    You never see the getdate. Parsing that code will not throw an error and because of deferred name resolution you can fat-finger table names and it will parse without a problem.

    Wednesday, August 06, 2008

    SQL Server 2008 is RTM

    that is right and you can already download it from MSDN

    Sunday, July 20, 2008

    Microsoft Visual Studio Team System 2008 Database Edition GDR July CTP Released

    Microsoft® Visual Studio Team System 2008 Database Edition GDR July CTP is available for download

    In addition to providing support for SQL Server 2008 database projects, this release incorporates many previously released Power Tools as well as several new features. The new features include distinct Build and Deploy phases, Static Code Analysis and improved integration with SQL CLR projects.

    Database Edition no longer requires a Design Database. Therefore, it is no longer necessary to install an instance of SQL Express or SQL Server prior to using Database Edition.

    Download it here: http://www.microsoft.com/downloads/deta ... laylang=en

    Monday, July 07, 2008

    sp_indexinfo an enhanced index information procedure

    Tibor Karaszi has created a very useful index information stored procedure for SQL Server 2005 and up.
    This stored procedure will tell you the following"

    What indexes exists for a or each table(s)
    Clustered, non-clustered or heap
    Columns in the index
    Included columns in the index
    Unique or nonunique
    Number rows in the table
    Space usage
    How frequently the indexes has been used

    Check it out here: http://www.karaszi.com/SQLServer/util_sp_indexinfo.asp

    Wednesday, July 02, 2008

    Awesome Collection Of ASP.NET Hacks, Tips and Tricks

    We added a bunch of ASP.NET Hacks, Tips and Tricks

    we have the following categories
    1 Applications
    2 Caching
    3 Controls
    4 Database
    5 Dates
    6 Debugging
    7 Email
    8 Encryption
    9 Files
    10 Images
    11 Javascript
    12 Objects and Classes
    13 Pages
    14 Sessions
    15 Strings
    16 Validation
    17 Visual Studio
    18 Web

    The URL to these hacks is here: http://wiki.lessthandot.com/index.php/ASP.NET_Hacks
    Bookmark that URL because we will be adding more hacks, tips and tricks

    Interview With SSIS Guru Jamie Thomson

    As promised yesterday, here is the link to the interview I did with jamie Thomson: Interview With SSIS Guru Jamie Thomson

    Tuesday, July 01, 2008

    I tried to keep it a secret but it is all over the internet: I am a SQL Server MVP

    So all over the internet is exaggerated,I became a SQL Server MVP today and was not going to do a blog post about it. However some other people had other ideas

    Congratulations, Denis!
    SQL Server MVP - Denis Gobo

    and even an announcement on lessthandot

    My profile is here: https://mvp.support.microsoft.com/profile=BCCF7416-DA4E-4D73-83E2-65FD61BAB16D

    Stay tuned, I will have an interview with Jamie Thomson tomorrow

    Thursday, June 26, 2008

    Working On SQL Admin Hacks, Tips and Tricks

    I haven't posted for a while because I have been working on SQL Admin Hacks, Tips and Tricks lately. it is still a work in progress but below is what is on the wiki currently. It is not yet categorized but we will do that once we get more of these hacks done. To see what it will look like when it is done take a look at the SQL Server Programming Hacks

    Can you think of any admin stuff you would like to see? This is what we have right now

    Find Primary Keys and Columns Used in SQL Server
    Get The Domain Name Of Your SQL Server Machine With T-SQL
    Grant Execute/SELECT Permissions For All User Defined Functions To A User
    Grant Execute Permissions For All Stored Procedures To A User
    Kill All Active Connections To A Database
    SQL Server 2008: When Was The Server Last Started?
    Check If Auto Update Statistics Is Enabled By Using DATABASEPROPERTY
    Three Way To List All Databases On Your Server
    Generate A List Of Object Types By Using OBJECTPROPERTY
    How to find all the tables and views in a database
    Find Out Server Roles For a SQL Server Login
    Which Service Pack Is Installed On My SQL Server
    Test SQL Server Login Permissions With SETUSER
    Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2000
    Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
    Compare Tables With Tablediff
    Find All Tables Without Triggers In SQL Server
    Find All Tables With Triggers In SQL Server
    Create Stored Procedures That Run At SQL Server Startup
    Cycle The SQL Server Error Log
    How to read sql server error messages
    Use OBJECT_DEFINITION To Track SQL Server Stored Procedure Changes
    SQL Compare Without The Price Tag
    How To Get The Database Name For The Current User Process
    How To Find Out Which Columns Have Defaults And What Those Default Values Are
    Fixing Cannot add, update, or delete a job that originated from an MSX Server Error after renaming a server

    Tuesday, June 10, 2008

    SQL Server 2008 Release Candidate 0 Available To The General Public For Download

    SQL Server 2008 Release Candidate 0 has been made available to the general public.

    There are 2 versions (and 3 flavors of each) ISO or DVD

    Download it here: http://www.microsoft.com/downloads/details.aspx?FamilyId=35F53843-03F7-4ED5-8142-24A4C024CA05&displaylang=en

    Don't forget to also visit the SQL Server 2008 Release Candidate 0 connect site here: https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5395