SQL Saturday #96 - SQL Learning

Come to SQL Saturday #96, Saturday November 5, 2011 at Microsoft MTC 5404 Wisconsin Ave Suite 700 Chevy Chase MD 20815. This is a free event and has quite a few good sessions and quality speakers. I was fortunate enough to have two sessions selected for the event! I will be presenting automating database source control and introduction to Oracle Databases for SQL Server DBAs.

This will be my second SQL Saturday event this year, and it looks to be a great one. There are a few events that are close enough to drive to from Morgantown, WV. I am looking forward to meeting some new people and some of the MVPs that will be at the event. Here are a few of the well known speakers for teh event Thomas LaRock, Andy Lenorad, Allan Hirt. I follow some of them on twitter, which can be a great resource to find out new and interesting information in real-time.

If you are close by or a three hour drive away, come get your learn on at the event. This will be a great opportunity to network and receive some free training. There is even food too! I hope to see a good turn out for the event.

Thanks SQL community, sponsors, and Matthew Velic for organizing and putting the event together!

When 2 = 1 in data type bit

So interesting issue, of tracking down why a filter stored procedure was not working. I first went through and captured a trace with SQL Server Profiler and extracted the call being made to the database. So pasting this into the query window and then using the Debug feature in SSMS 2008 to step into the stored procedure (sp).

In stepping through the sp I discovered the variable value was transformed into 1 in my where clause. Wow, I know it was 2, back to checking why this was happening. I checked the type and it was a BIT, not an INT. My user interface was sending a value of 2.  However, the behavior was not to give a value out of range, or implicit conversion error, it was to convert it to 1. I was under the impression the only valid values were NULL, 0, or 1. Be careful anticipating the behavior of languages. Let's illustrate this behavior take the following code sample and results:



declare @SearchValue bit
select @SearchValue N'as no value'
set @SearchValue = 0 
select @SearchValue N'as 0'
set @SearchValue = 1
select @SearchValue N'as 1'
set @SearchValue = 2
select @SearchValue N'as 2'

SQL Saturday #80 wrap-up

It was a great free event for networking and training at SQL Saturday #80 in Wheeling, WV. I did better than expected in my presentation. I was really pleased with the turn out and audience participation. It was a great experience.

I learned some at others sessions too. I was able to sit in on the NOSQL session by Craig Purnell and glean some good overview information. The speakers dinner the night before the event was also another great opportunity to network and share. Thanks to all the organizers and volunteers for making this event a success.

Below is a link to my slides and scripts from the presentation

Automating SQL Source Control

Here is an updated paper that Red Gate has on continous integration for SQL Databases

Continuous integration for databases

SQL Saturday 80 in Wheeling, WV

So, I volunteered a session for SQL Saturday 80 in Wheeling, WV and it was accepted. Now after reading Brent Ozar's blog (http://www.brentozar.com/archive/2011/01/how-rehearse-presentation/) about presenting and John Sterrett's blog (http://johnsterrett.com/2010/12/09/you-dont-have-to-be-an-expert-to-speak/), I was fired up to try and get something going. I did and then I did the slides about a hundred times updating bullet points and notes. Then there was the demo portion, which I am still not 100% sure about! I did run through it at least two times and corrected syntax and spelling.

So I am worried, but happy I made it to the hotel, and to the speaker’s dinner and back to the hotel... there are so many things. Getting ready for a presentation at the away field is hard. It reminds me of sports and when you play away there is always that uncertainty that you forgot something and that there is kryptonite around the corner. One thing is for sure, the SQL Server community is great and always very helpful. I like that everyone is very eager to help and so nice.

I have to think and speak slowly, that is the key for me in my presentation. There are some many points I could cover but I also want the audience to be involved. If they get something out of the presentation then I have succeeded. I prepare for demos a lot for work but it is not the same as you are at your home field and have all the infrastructure in place and personnel to back you up if you venture into a danger spot.

Well this is my first outside of work presentation and I am going to make it a good one and get some good takeaways too. I want to present a few times a year as I find it great to give back something you learn to the others. I find myself a lot of times wondering if I could have done that presentation or if I would have done as well. I guess we will see at SQL Saturday #80 in Wheeling, WV.

I figured it was my home state, so I should try and add something to the event as I am from WV and have a solid background in databases. This is the 2nd SQL Saturday event in Wheeling and the event has more attendees than last year. John Sterrett has done a great job at getting this event started and organized. I know there are many others behind the scenes too, so thanks to all of you who I have not met yet.
Good luck to all the presenters and attendees!

Comma Separated List - TSQL

There are times when it is useful to be able to display a list of fields in a comma separated list using just TSQL. In this post I will show my favorite way to make a quick comma separated list. There are a variety of ways to make a list in TSQL, for this example we will make use of the STUFF() function, XML and the AdventureWorks 2008 sample database.

The first step was looking at the ERD to find a one-to-Many relationship that made sense to use a list for. In the ERD we can see that a Person can have multiple phone numbers. So let's use this set of tables for our demonstration.










USE AdventureWorks2008

GO

/* Add multiple phone numbers for a person */

INSERT  INTO person.PersonPhone

        ( BusinessEntityID, PhoneNumber, PhoneNumberTypeID, ModifiedDate )

VALUES  ( 121,'412-555-1212',1, GETDATE()),

        ( 121,'412-432-1212',2, GETDATE()),

        ( 121,'412-278-1919',2, GETDATE())

/* Show the person with phone numbers in a comma separated list */        

SELECT  p.BusinessEntityID

       ,P.FirstName

       ,P.MiddleName

       ,P.LastName

       ,( SELECT    ( STUFF(( SELECT    ', ' + PP.PhoneNumber + ' ' + PT.Name

                              FROM      person.PersonPhone PP

                              JOIN      person.PhoneNumberType PT ON PP.PhoneNumberTypeID = PT.PhoneNumberTypeID

                              WHERE     PP.BusinessEntityID = P.BusinessEntityID

                            FOR

                              XML PATH('') ), 1, 2, '') ) ) AS [Phone Numbers]

FROM    Person.Person P

WHERE   P.BusinessEntityID = 121

This returns the following result:

SSMS RTFM findings

Using other database client tools like SQL*Plus I learned the ins and outs of it, but with SQL Server Management Studio (SSMS) it is much more complex with things hidden in plain sight. For the first case in point, templates and the shortcut CTRL-Shift-M. What is so great about that? Well we can have scripts that makes use of a template and we press our the magic key stroke, we can fill in the variables in the template and then F5 to execute.

Templates hiding from your view, press CTRL-ALT-T and presto they appear. Take note, there are quite a few that Microsoft has included. These can be used or create your own and save the SQL script to C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql for SSMS 2008.

Object browser hiding try pressing F8 in SSMS to bring it back into focus.


Want a new query window to start with the same set of statements each time you open a new query? This is easy once you find the file to edit. The file is 'SQLFile.sql' and is located in the directory C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql

Edit this file and save it, and then each new query window opened will have you favorite text / commands in it.


Some  other useful key strokes are

• CRTL-F for find and replace
• CTRL-G for go to specific line number
• CTRL-K,CTRL-W for bookmarks these are really helpful when looking at long SPs
• CRTL-I for an incremental search

In fact there is a great list of these at http://msdn.microsoft.com/en-us/library/ms174205.aspx

Learning how to use the envronement effectively will make you more productive and be very helpful when working with others during troubleshooting.

Case statement , NULLS and ANSI_NULLS ON | OFF

After changing the ANSI null settings in stored procedures and functions so that they all corresponded, I found some code that was depending on the SET ANSI_NULLS OFF setting to interpret the results using a case statement. The case statement was comparing NULL and returning 0 or 1 as the result. 

The offending code was similar to:

CASE sub1.[EmpID]
        WHEN NULL THEN 0
        ELSE 1
    END AS Selected

This only works when SET ANSI_NULLS OFF is specified. 

This can be fixed to work correctly no matter the ANSI_NULLS setting ( ON | OFF ) by changing the case statement to the following:

CASE
        WHEN sub1.[EmpID] IS NULL THEN 0
        ELSE 1
    END AS Selected

.

Here is a quick example showing the issue:

/* ANSI NULL example with case statement */

/* Setup some tables in the test */

CREATE SCHEMA test ;

GO

CREATE TABLE test.emp

    (

     empID INT IDENTITY(1, 1)

    ,FirstName NVARCHAR(32)

    ,LastName NVARCHAR(32)

    ,CONSTRAINT [PK_emp] PRIMARY KEY CLUSTERED ( [empID] ASC ) )

ON  [PRIMARY]

GO

CREATE TABLE test.EmpGroup

    (

     EmpGroupID INT IDENTITY(1, 1)

    ,EmpGroupName NVARCHAR(32)

    ,CONSTRAINT [PK_EmpGroup] PRIMARY KEY CLUSTERED ( [EmpGroupID] ASC ) )

ON  [PRIMARY]

GO

CREATE TABLE test.EmpGroupLink

    (

     EmpID INT

    ,EmpGroup INT

    ,CONSTRAINT [PK_EmpGroupLink] PRIMARY KEY CLUSTERED ( EmpID, EmpGroup ASC ) )

ON  [PRIMARY]

GO

ALTER TABLE test.EmpGroupLink ADD CONSTRAINT FK_EmpGroupLink_Emp FOREIGN KEY (EmpID)

REFERENCES test.emp (empID)

ALTER TABLE test.EmpGroupLink ADD CONSTRAINT FK_EmpGroupLink_EmpGroup FOREIGN KEY (EmpGroup)

REFERENCES test.EmpGroup(EmpGroupID)

GO

/* add some records for our testing */

INSERT  INTO test.emp

        ( FirstName, LastName )

VALUES  ( 'Samir', 'Nagheenanajar' ),

        ( 'Mike', 'Bolton' ),

        ( 'Peter', 'Gibbons' ),

        ( 'Bill', 'Lumbergh' ),

        ( 'Milton', 'Waddams' ) ;

       

GO

INSERT  INTO test.EmpGroup

        ( EmpGroupName )

VALUES  ( 'Development' ),

        ( 'Management' ),

        ( 'Operations' )

GO

INSERT  INTO test.EmpGroupLink

        ( EmpID, EmpGroup )

VALUES  ( 1, 1 ),

        ( 2, 1 ),

        ( 3, 1 ),

        ( 4, 2 ),

        ( 5, 3 );

GO     

SET ANSI_NULLS OFF

go

       

SELECT  E.empID

       ,E.FirstName

       ,E.LastName

       ,CASE sub1.[EmpID]

          WHEN NULL THEN 0

          ELSE 1

        END AS Selected

FROM    test.emp E

LEFT JOIN ( SELECT  EmpID

            FROM    test.EmpGroupLink EGL

            JOIN    test.EmpGroup EG ON EGL.EmpGroup = EG.EmpGroupID

            WHERE   EG.EmpGroupName = 'Management' ) sub1 ON E.empID = sub1.EmpID

GO

/* If ANSI_NULLS ON returns unintended results*/

SET ANSI_NULLS ON

GO

SELECT  E.empID

       ,E.FirstName

       ,E.LastName

       ,CASE sub1.[EmpID]

          WHEN NULL THEN 0

          ELSE 1

        END AS Selected

FROM    test.emp E

LEFT JOIN ( SELECT  EmpID

            FROM    test.EmpGroupLink EGL

            JOIN    test.EmpGroup EG ON EGL.EmpGroup = EG.EmpGroupID

            WHERE   EG.EmpGroupName = 'Management' ) sub1 ON E.empID = sub1.EmpID

/* changed to handle NULLS correctly no matter the ANSI settings */

SET ANSI_NULLS OFF

GO

SELECT  E.empID

       ,E.FirstName

       ,E.LastName

       ,CASE WHEN sub1.[EmpID] IS NULL THEN 0

             ELSE 1

        END AS Selected

FROM    test.emp E

LEFT JOIN ( SELECT  EmpID

            FROM    test.EmpGroupLink EGL

            JOIN    test.EmpGroup EG ON EGL.EmpGroup = EG.EmpGroupID

            WHERE   EG.EmpGroupName = 'Management' ) sub1 ON E.empID = sub1.EmpID

GO

SET ANSI_NULLS ON

GO

SELECT  E.empID

       ,E.FirstName

       ,E.LastName

       ,CASE WHEN sub1.[EmpID] IS NULL THEN 0

             ELSE 1

        END AS Selected

FROM    test.emp E

LEFT JOIN ( SELECT  EmpID

            FROM    test.EmpGroupLink EGL

            JOIN    test.EmpGroup EG ON EGL.EmpGroup = EG.EmpGroupID

            WHERE   EG.EmpGroupName = 'Management' ) sub1 ON E.empID = sub1.EmpID

GO                

As we can see developers can sometimes use null comparisons incorrectly. 

Mixed ANSI Null setting driving me Nuts!

I inherited a SQL database that had a nasty habit started before someone could make all developers follow a few simple rules. Rules, such as all tables must have a primary key and also most should have a clustered index. Another issue I ran into was when I tried to created an indexed view. It was great until I tried to save a record using a save SP that had a different ANSI setting. 

The issue was I have some SPs and functions using ANSI null off explicitly and some using ANSI null on. I have been working around this issue for the last 7 months, waiting for the go ahead to take all the SPs and functions and have them all have ANSI null on. The game grew old this week when for the 'unteenth' time I get a text message and e-mail with the error message could not save due to ANSI null setting.

SET ANSI_NULLS ON

My solution while not the best was effective.

1. Find all objects with ANSI_NULLS OFF
2. Script them to a file
3. Make a backup copy of the file
4. Search and replace ANSI_NULLS OFF to ANSI_NULLS ON
5. save the file
6. Make a backup
7. Execute the file
8. Verify all objects have the same setting
9. Repeat on Dev, Test, and Production
10. If there are any issues, restore the backup.

I used the query

select object_name(object_id) from sys.sql_modules where uses_ansi_nulls=0
order by  object_name(object_id)

to identify all the objects with ANSI NULL OFF

For Stored procedures I was able to use SQL Server Management Studio Object Explorer Details. I selected all the user defined SPs and selected drop and create script results to a file. Make sure you have script permissions set to yes. When dropping and recreating the permissions are lost.

Then, you wait, go get some coffee or something because scripting 500+ Sps takes some time. I made sure to have the permissions included also. If you have less objects then it maybe faster but be patient. After this completes do the same for the functions. Once completed, open the files with a good editor such as notepad++, or whatever you favorite search and replace editor is. Once this is done we can combine the files as they will have 'GO' statements in them separating each object into a batch. If something fails we have a point of reference as we can click the error and SSMS will take use to the offending object. This could happen if a table or view has changed and the function or SP was not recompiled.


Make sure to save these changes in source control too.

Finding duplicates

A question I get often by my C# developers is what is the TSQL to find repeated entries in table x? The one solution I return to over and over is :

SELECT 

       FirstName

       ,COUNT(*)

from Person.Contact

GROUP BY FirstName HAVING COUNT(*) > 1

Using the AdventureWorks database  as the source to execute the query we can quickly determine how many times a person has the same first name. This was a simple example but can be applied to many examples.