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

Lessthandot.com
Mainsite
Database Blogs
Database Blogs RSS feeds
All Blogs
All Blogs RSS Feed

SQLBlog.com
Mainsite
My Blog
My Blog RSS feed

Personal Blog
URL
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
ASP.NET
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 * FROM

  • (

  • 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

  • UNION ALL

  • (

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

  • FROM payment_table

  • WHERE

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

  • GROUP BY customer_id) YTD_payments

  • UNION ALL

  • (

  • 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:
    http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3746751&SiteID=1
    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 GETDATE()
    GO
    SELECT 1/asasasas
    GO


    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

    SET NOEXEC ON
    GO

    SELECT GETDATE()
    GO
    SELECT 1/asasasas
    GO
    SET NOEXEC OFF
    GO



    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

    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

    Friday, June 06, 2008

    Puzzle: Friday the Thirteenths

    There is a puzzle on LessThanDot

    The goal is to identify all friday the thirteenths for a given timeframe. We'll use a relatively small number of years, like 10. This should make it a little easier in procedural languages.


    I posted my SQL solution, what about you?

    Go here: Friday the Thirteenths and show me what you got. Make sure to use the [hide][/hide] tags so you don't spoil it for other people

    Sunday, June 01, 2008

    Less Than Dot A New community Site Has Been Launched

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

    Tuesday, May 27, 2008

    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 have interviewed Erland Sommarskog, you can find that interview here: Interview With Erland Sommarskog About SQL Server and Transact SQL

    Enjoy

    Wednesday, May 21, 2008

    I have a question for you on DB development blunders!

    Last week Kalen Delaney wrote Did You Know? I have a question for you on DBA Blunders! I though the comments were very interesting, S we did the DBA part but what about developers? What are some of the worst blunders you have seen?

    Here are a couple of things

    Starting a begin tran, then some insert/update command, never commiting but minimizing the window.


    Here is my all time favorite, can you reduce this by 90%?


    declare
    @Token int
    select
    @Token = 51234567

    declare @val int


    if
    left(@Token,1) = 1
    select @val = 1
    else if left(@Token,1) = 2
    select @val = 2
    else if left(@Token,1) = 3
    select @val = 3
    else if left(@Token,1) = 4
    select @val = 4
    else if left(@Token,1) = 5
    select @val = 5
    else if left(@Token,1) = 6
    select @val = 6
    else if left(@Token,1) = 7
    select @val = 7
    else if left(@Token,1) = 8
    select @val = 8
    else if left(@Token,1) = 9
    select @val = 9
    else if left(@Token,1) = 0
    select @val = 0


    select @val


    Actually we put that on the whiteboard after we found it in our code and every time the developer wanted something we teased him about it...Oh you mean like that (pointing to the whiteboard)


    What about changing the datatype from smallint to int in the table but keeping the params smallint in the proc.....mmmm why do I get a conversion in the execution plan?





    So let's here some of what you have seen others write, we all know we couldn't write stuff like that ourselves right? :-)




    Monday, May 19, 2008

    Spring Cleaning, Getting Rid Of Some Books

    I did some spring cleaning yesterday, I got rid of some old or obsolete books. I asked some people at work if they want any of these books and I believe all the books except for the JCL book are about to be given away.

    I got rid of these books because:
    I got a new version
    I don't use the technology anymore
    The book is obsolete


    Click on the image and you can see a bigger version on my flickr page.


    Spring Cleaning

    Thursday, May 08, 2008

    SSMS Tools PACK 1.0 is now available

    Mladen Prajdić just released SSMS Tools PACK 1.0. SSMS Tools PACK is an Add-In (Add-On) for Microsoft SQL Server Management Studio and Microsoft SQL Server Management Studio Express.

    Here are some of the features

    Uppercase/Lowercase keywords.
    Run one script on multiple databases.
    Copy execution plan bitmaps to clipboard.
    Search Results in Grid Mode and Execution Plans.
    Generate Insert statements for a single table, the whole database or current resultsets in grids.
    Query Execution History (Soft Source Control).
    Text document Regions and Debug sections.
    Running custom scripts from Object explorer's Context menu.
    CRUD (Create, Read, Update, Delete) stored procedure generation.
    New query template.

    Check it out here: http://www.ssmstoolspack.com/Main.aspx

    How to log when a function is called?

    This question came up today and here is one way of doing it. It requires running xp_cmdshell so this is probably not such a good idea.
    The problem with functions is that you cannot just insert into any table. INSERT, UPDATE, and DELETE statements modifying table variables local to the function.
    EXECUTE statements calling an extended stored procedures are allowed.
    So with this in mind we know that we can call xp_cmdshell, from xp_cmdshell we can use osql
    Let's take a look
    We will be using tempdb


    --Create the table
    USE tempdb
    go
    CREATE TABLE LogMeNow (SomeValue varchar(50), SomeDate datetime default getdate())
    go

    --Here is the proc
    CREATE PROC prLog
    @SomeValue
    varchar(50)
    AS
    INSERT
    LogMeNow (SomeValue) VALUES(@SomeValue)
    go

    --And here is the function
    CREATE FUNCTION fnBla(@id int)
    RETURNS int
    AS
    BEGIN
    DECLARE
    @SQL varchar(500)
    SELECT @SQL = 'osql -S' +@@servername +' -E -q "exec tempdb..prLog ''fnBla''"'
    EXEC master..xp_cmdshell @SQL
    RETURN @id
    END

    Now call the function a couple of times

    SELECT
    dbo.fnBla(1)
    SELECT dbo.fnBla(2)
    SELECT dbo.fnBla(4)



    And look inside the table




    SELECT * FROM LogMeNow

    What if you were to run this?


    SELECT dbo.fnBla(4),* FROM sys.sysobjects


    See the problem? The function will be called for every row, if you have a big table this can be problematic!!!!!!!!


    I tested this on SQL 2000 and on SQL 2005(including a named instance). So there you have it, this is one way. does it smell kludgy and do I feel somewhat dirty now? yes it does indeed :-(

    SQL Teaser: @@ROWCOUNT

    Without running this what do you think will be printed?


    SET ROWCOUNT 0
    DECLARE
    @ int
    SET
    @ =6
    IF @@ROWCOUNT = 1
    PRINT 'yes'
    ELSE
    PRINT 'no'
    PRINT @@rowcount

    Wednesday, May 07, 2008

    Should SQLServer Have The CREATE [OR REPLACE] PROCEDURE Syntax?

    I was asked by a developer at work the other day why SQL Server does not have the create or replace syntax. I started thinking and there were some advantages and one big disadvantage

    First the advantages

    Advantage
    When scripting out a database you don’t have to generate if exists.....drop statements

    When modifying an object from a file in source control you don’t have to change back and forth between CREATE and ALTER. This is really annoying sometimes; usually you create a proc or script out a proc and store it in Subversion/SourceSafe. Now you take that file, make some changes to the proc and run it. Of course it will tell you that the proc already exists, in general you don’t want to drop the proc and then execute the CREATE proc statement because then you have to worry about the permissions.(not everyone is running as dbo you know)

    Disadvantage
    I can overwrite a proc without even knowing it. What if we both are working on a proc and somehow we pic the same name I create my proc, you work in a remote location, you create yours and mine is gone.

    Of course it is all what you are used to, I am sure the Oracle guys are not overwriting each other’s procs every day either

    So what do you think? Would you be in favor of this syntax or not? Can you think of more advantages or disadvantages?

    Tuesday, May 06, 2008

    What Would You Like To Ask Erland Sommarskog?

    In the Who do you want to see interviewed next?(sqlblog) and Who do you want to see interviewed next?(here) blog post I asked for some names of people who YOU would like to see interviewed. Erland Sommarskog's name was submitted in comments a couple of times. I contacted Erland and am happy to anounce that he has agreed to do this.

    The first time I heard of Erland Sommarskog was in the SQL Server programming newsgroups. I also read all the articles on his site. When I answer questions I usually give the link out to these 4 articles
    Arrays and Lists in SQL Server
    Implementing Error Handling with Stored Procedures
    Error Handling in SQL Server – a Background
    and of course The curse and blessings of dynamic SQL

    I always wondered why he didn't write a SQL book. Hey, now I can ask him that :-)

    Before you think of any questions please visit Erland's page first (http://www.sommarskog.se/index.html) and read some of the articles he wrote.

    You can submit questions until Monday May 12th and then I will forward the questions to Erland.

    What Would You Like To Ask Craig Freedman?

    In the Who do you want to see interviewed next?(sqlblog) and Who do you want to see interviewed next?(here) blog post I asked for some names of people who YOU would like to see interviewed. Craig Freedman's name was submitted in comments a couple of times. I contacted Craig and am happy to anounce that he has agreed to do this.

    Before you think of any questions please visit Craig's blog first(http://blogs.msdn.com/craigfr/) to get a feel of what kind of questions to ask. It would make sense to keep the questions focused on query processing, query execution, and query plans. I told Craig that if he doesn't like a question that he does not have to answer it.

    You can submit questions until Monday May 12th and then I will forward the questions to Craig

    Do you depend on sp_depends (no pun intended)

    HTML Source EditorWord wrap I answered this question on the MSDN forums: How can I search all my sprocs to see if any use a function?
    Several people suggested using sp_depends. You can't really depend on sp_depends because of deferred name resolution. Take a look at this

    First create this proc

    CREATE
    PROC SomeTestProc
    AS
    SELECT
    dbo.somefuction(1)
    GO


    now create this function

    CREATE
    FUNCTION somefuction(@id int)
    RETURNS int
    AS
    BEGIN
    SELECT
    @id = 1
    RETURN @id
    END
    Go


    now run this


    sp_depends
    'somefuction'

    result: Object does not reference any object, and no objects reference it.



    Most people will not create a proc before they have created the function. So when does this behavior rear its ugly head? When you script out all the objects in a database, if the function or any objects referenced by an object are created after the object that references them then sp_depends won't be 100% correct



    SQL Server 2005 makes it pretty easy to do it yourself



    SELECT

    specific_name,*

    FROM information_schema.routines

    WHERE

    object_definition(object_id(specific_name)) LIKE '%somefuction%'

    AND

    routine_type = 'procedure'

    BTW somefuction is not a type, I already had a somefunction but was too lazy to change more than one character




    Friday, May 02, 2008

    Testing for SQL Server Vulnerabilities

    I found this SQL Server Testing (not unit but vulnerability) page and decided I would post a link to it since it has some useful stuff. The link is below

    http://www.owasp.org/index.php/Testing_for_SQL_Server

    Here is what is covered. Enjoy (or live in fear over the weekend)


    1 Brief Summary
    2 Short Description of the Issue
    3 Black Box testing and example
    3.1 SQL Server Peculiarities
    3.2 Example 1: Testing for SQL Injection in a GET request.
    3.3 Example 2: Testing for SQL Injection in a GET request (2).
    3.4 Example 3: Testing in a POST request
    3.5 Example 4: Yet another (useful) GET example
    3.6 Example 5: custom xp_cmdshell
    3.7 Example 6: Referer / User-Agent
    3.8 Example 7: SQL Server as a port scanner
    3.9 Example 8: Upload of executables
    3.10 Obtain information when it is not displayed (Out of band)
    3.11 Blind SQL injection attacks
    3.11.1 Trial and error
    3.11.2 In case more than one error message is displayed
    3.11.3 Timing attacks
    3.11.4 Checking for version and vulnerabilities
    3.12 Example 9: bruteforce of sysadmin password
    4 References

    Thursday, May 01, 2008

    Who do you want to see interviewed next?

    I have interviewed a bunch of people already, some of them on this blog and some of them on SQLBlog

    Below is the list of all the people so far

    Louis Davidson
    Itzik Ben-Gan
    Ken Henderson
    Kalen Delaney
    Adam Machanic
    Interview With Stéphane Faroult About Refactoring SQL Applications
    Database Refactoring Interview With Scott W. Ambler
    Interview With Joe Celko About The Forthcoming Book Thinking In Sets

    Who else should I interview? I have some ideas but I would like you to participate. Here is my thinking, after we have a list of possible people, I will contact them and ask if they will agree to an interview. If they agree I will make a post here and ask you what you would like to ask this person. I usually have a good set of questions but there might be someone out there with some real good/interesting questions.
    So post the name of the person you would like to see interviewed and hopefully this person will agree.

    It would be nice if you did not pick a person who blogs at SQLBlog but if_you_really_wanted_to then I will not stop you :-0

    Please keep it database related, it doesn't have to be specific to SQL Server but it has to be applicable to SQL Server.

    Friday, April 25, 2008

    SQL Teaser: Some Things Are Not What They Seem

    This one is a little sneaky, don’t send me hate mail for it.

    What does this return?



    SELECT ISNUMERIC('+'),ISNUMERIC('–')

    Copy and paste it into QA/SSMS to verify :-0

    Tuesday, April 22, 2008

    How to rename a column in a SQL Server table without using the designer

    If you have a table and you want to rename a column without using the designer, how can you do that?

    First create this table

    CREATE TABLE TestColumnChange(id int)
    INSERT TestColumnChange VALUES(1)


    SELECT * FROM TestColumnChange

    As you can see the select statement returns id as the column name. you can use ALTER table ALTER Column to change the dataype of a column but not the name.

    Here is what we will do, execute the statement below

    EXEC sp_rename 'TestColumnChange.[id]', 'value', 'COLUMN'


    Now do the select, you will see that the column name has changed

    SELECT * FROM TestColumnChange

    That is it, very simple

    Monday, April 21, 2008

    Interview With Stéphane Faroult About Refactoring SQL Applications

    I decided to interview Stéphane Faroult, the author of The Art of SQL because it is one of my favorite SQL books. Stéphane told me he has a new book coming out soon, the name of this book is Refactoring SQL Applications and the majority of this interview is focused on that book.

    You can find that interview here: Interview With Stéphane Faroult About Refactoring SQL Applications

    Tuesday, April 15, 2008

    Solutions for Common T-SQL Problems Wiki Launched

    Volunteer Moderators and Answerers who support the Microsoft MSDN SQL Server Forums have launched a Wiki with Solutions for Common T-SQL Problems. Check it out here: http://code.msdn.microsoft.com/SQLExamples

    Monday, April 14, 2008

    Use IDENT_CURRENT() to return the last identity value generated in all tables in a SQL Server Database

    This is how you return all the tables with their last generated identity values in a SQL Server Database

    SELECT IDENT_CURRENT(table_name),*
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'base table'
    AND OBJECTPROPERTY(OBJECT_ID(table_name),'TableHasIdentity')=1

    Monday, April 07, 2008

    Video: SQL Server Data Services Architecture

    SQL Server Technical Evangelist Ryan Dunn sits down and talks turkey with two of the creators of SQL Server Data Services (SSDS), Architect Istvan Cseri and Development Manager Nigel Ellis. Istvan and Nigel dig into how to design applications for SSDS and cover a number of the interesting aspects of working with SSDS in terms of features, design, and security.

    Watch it here: http://channel9.msdn.com/ShowPost.aspx?PostID=395843#395843

    Saturday, April 05, 2008

    Links Of The Week 20080405

    Here are the links for this week

    Database
    Send Table or View as embedded HTML in an email – stored procedure
    How It Works: SQL Server 2005 DBCC Shrink* May Take Longer Than SQL Server 2000
    How It Works: Non-Yielding Resource Monitor
    Overhead of Row Versioning
    TempDB:: Table variable vs local temporary table
    sp_helpindex2 to show included columns (2005+) and filtered indexes (2008) which are not shown by sp_helpindex
    Yet Another Stored Procedure vs. Ad-hoc Query Discussion?
    Geek City: How SQL Server Detects the Correct Versioned Rows
    SQLIOSim is "NOT" an I/O Performance Tuning Tool
    Forensic Analysis of a SQL Server 2005 Database Server

    Non DB tech
    Microsoft Live Search Gains Market Share
    Unit Testing with Silverlight
    Using Spring.NET and NHibernate with ASP.NET MVC
    Enterprise Library 4.0 Community Technology Preview
    Hanselminutes Podcast 107 - Digital Photography Explained (for Geeks) with Aaron Hockley
    How do Extension Methods work and why was a new CLR not required?
    Core War: Two Programs Enter, One Program Leaves
    How to set an IIS Application or AppPool to use ASP.NET 3.5 rather than 2.0
    Let That Be a Lesson To You, Son: Never Upgrade.
    Silverlight 2 DIGG Sample Part I
    Silverlight 2 DIGG Sample Part II
    Silverlight 2 DIGG Sample Part III
    Google Developer Podcast: Picasa Web and Google Gears for Mobile
    Ted Neward: On Java and .NET, Software Academics versus Practioners, General Purpose Programming Languages
    Whirlwind 7: What's New in C# 3 - Lambda Expressions
    Google To Launch BigTable As Web Service To Compete With Amazon's SimpleDB

    Non tech
    SMCB: Charles Manson Pulls a Radiohead
    Possibly the best name ever.
    Thank God for Torrents (Pic)

    Wednesday, April 02, 2008

    Database Refactoring Interview With Scott W. Ambler

    Since I am doing scrum and other agile methodologies myself I decided to interview the authority on agile in the database world.

    Scott W. Ambler is the Practice Leader Agile Development for IBM Rational

    You can find more about Scott here: http://www.ibm.com/software/rational/bios/ambler.html

    I did not feel like duplicating the content here so you can read the interview here: Database Refactoring Interview With Scott W. Ambler

    Tuesday, April 01, 2008

    SQL Champ - A Quiz to Prove Your SQL Knowledge

    In case you are bored CodeProject has a small SQL test (7 questions)

    Take the test here: SQL Champ - A Quiz to Prove Your SQL Knowledge

    And did you get everything correct?

    And yes they wrote Knowlege instead of Knowledge :-)

    Not An April Fool’s Joke: SQL Server 2000 Mainstream Support Ends In A Week

    April 1st 2008 is the day on which support for SQL Server 2000 ends (and it is also my birthday)


    SQL 2000 SP4 - currently supported; support ends on either 12 months after SP5 is released or on 4/8/2008 whichever comes first. Extended support under which you get only break-fix assistance via Premier ends on 4/9/2013. I am not aware of release date for SP5 so far.

    SQL 2005 SP2 - currently supported; support ends on either 12 months after SP3 is released or on 4/12/2011 whichever comes first. Extended support under which you get only break-fix assistance via Premier ends on 4/13/2016


    More details can be found here: http://blogs.msdn.com/sqlreleaseservices/archive/2007/07/12/sql-server-support-lifetimes-customer-actions.aspx

    And here:
    http://support.microsoft.com/lifecycle/?p1=2852

    Sunday, March 30, 2008

    Links Of The Week 20080330

    Who needs SQL links when you have this gem?

    Check out (grand)ma in the background, she doesn't miss a beat.

    Friday, March 28, 2008

    How To Use COALESCE And NULLIF To Prevent Updating Columns When A Parameter IS NULL Or A Default Value

    A variation of this question popped up twice in the SQL programming newsgroup since yesterday, this means it is time for a blogpost.
    Let's say you have a proc like this




    CREATE PROC prUpdateTable
    @Salesman
    int = -1
    AS

    ..........

    If the user calls the proc like this exec prUpdateTable null then @Salesman will be null, if the user calls the proc like this exec prUpdateTable then the value of @Salesman will be -1. In both of this cases you don't want to change the value of the column. What can you do?
    You can use a combination of NULLIF and COALESCE to handle this. Your update statement would look like this




    UPDATE table
    SET
    Column = COALESCE(NULLIF(@variable,-1),Column)



    Here is some code to demonstrate that




    CREATE TABLE #foo (id int,salesman int)
    INSERT #foo VALUES(1,1)
    INSERT #foo VALUES(2,1)
    INSERT #foo VALUES(3,1)
    INSERT #foo VALUES(4,1)



    SELECT * FROM #foo
    -------------
    1 1
    2 1
    3 1
    4 1

    DECLARE @salesman int
    SELECT
    @salesman = 5

    --Column value will change to 5
    UPDATE #foo
    SET salesman = COALESCE(NULLIF(@salesman,-1),salesman)
    WHERE ID =1

    --Column value won't change
    SELECT @salesman = -1
    UPDATE #foo
    SET salesman = COALESCE(NULLIF(@salesman,-1),salesman)
    WHERE ID =2

    --Column value won't change
    SELECT @salesman = NULL
    UPDATE #foo
    SET salesman = COALESCE(NULLIF(@salesman,-1),salesman)
    WHERE ID =3

    --Column value will change to 3
    SELECT @salesman = 3
    UPDATE #foo
    SET salesman = COALESCE(NULLIF(@salesman,-1),salesman)
    WHERE ID =4

    --And here is the output, as you can see when @salesman was -1 or NULL the table did not get updated
    SELECT * FROM #foo
    -------------
    1 5
    2 1
    3 1
    4 3

    DROP TABLE #foo





    As you can see only the first and the last update statement changed the value of the salesman column
    Of course you would never do this if you were to update only one column, you would skip the update instead. If you have to update multiple columns then this is something you can use instead of writing a bunch of dynamic SQL or nested IF statements.