Five Things a SQL Programmer/DBA Should Not Do

I read this very nice article here. Here's an excerpt of the entire list. I pick two of the most common case I encounter.

Performance Tuning Queries in Production

As tempting as the urge may be, it’s really not a good idea to query tune directly in your production environment. You’re making changes to your code base without actually knowing for sure, the consequences of your actions. Don’t even get me started on a DBA running Ad-hoc queries live on a production server.

So you’ve identified an index that will make a currently poorly performing query run like lightning, great! Now go away and test it properly in your performance tuning environment so you can see how well it plays with the other queries and be confident that there will be no adverse affects

Making Changes Without Testing (a.k.a Winging-it)

Even if you are a T-SQL Ninja, if you’re making changes to production that you have not tested then in my opinion that makes you a fool. You may think I’m being harsh but the experienced folk out there know that I speak the truth. If you are not testing your changes before they go to production then you are putting the data assets that you are ultimately responsible for at unnecessary risk. Doing so goes completely against your primary responsibility as a DBA. Why take the chance?

To view the complete list, please read this.

A special thanks to John Samson. You can read more about him on his blog.

~~ CK

Default Join Type

I'm always ask what the default join in SQL Server is, when no join type is specified. My immediate reaction is Google it. The problem is trying to find it among all the article that will show up as search result.

Now that I found it, I decided to put it here...

Here is the actual article (MSDN BOL) where I found it. And just in case Microsoft decides to remove it, here's an excerpt:

INNER
Specifies that all matching pairs of rows are returned. Discards unmatched rows from both tables. This is the default if no join type is specified.

LEFT [ OUTER ]
Specifies that all rows from the left table not meeting the specified condition are included in the result set in addition to all rows returned by the inner join. Output columns from the left table are set to NULL.

RIGHT [ OUTER ]
Specifies that all rows from the right table not meeting the specified condition are included in the result set in addition to all rows returned by the inner join. Output columns from the right table are set to NULL.

FULL [ OUTER ]
If a row from either the left or right table does not match the selection criteria, specifies the row be included in the result set, and output columns that correspond to the other table be set to NULL. This is in addition to all rows usually returned by the inner join.

~~ CK

Convert Rows into A Delimited String, Part 3

Here's another sample of dataset rows converted into delimited string based on a unique ID. Looking at the sample data is better than explaining how this works... Populate sample data:

declare @tbl_email table (id int, email varchar(20))
insert into @tbl_email
select 1, 'A'
union all
select 2, 'A0'
union all
select 2, 'A1'
union all
select 3, 'A2'
union all
select 3, 'A3'
union all
select 3, 'A4'
union all
select 4, 'A5'

select * from @tbl_email

id          email
----------- --------------------
1           A
2           A0
2           A1
3           A2
3           A3
3           A4
4           A5

Here's the required output:

id          email_list
----------- -----------
1           A
2           A0, A1
3           A2, A3, A4
4           A5

And the code...

;with Numbered
as 
   (
      select       
         rownum = row_number() over(partition by id order by id, email),
         id, email
      from @tbl_email
   )
,FirstRecords
as 
   (
      select rownum, id, email from Numbered where rownum = 1
   )
,ReturnAllRecords
as
   (
      select rownum, id, cast(email as varchar(500)) as email_list from FirstRecords  
      union all      
      select Numbered.rownum, Numbered.id,  cast(email_list + ', ' + Numbered.email as varchar(500))
      from Numbered
         inner join ReturnAllRecords 
            on ReturnAllRecords.id = Numbered.id and
               ReturnAllRecords.rownum + 1 = Numbered.Rownum
   )
select id, max(email_list) as email_list
from ReturnAllRecords
group by id

Comments are always welcome...


 ~~ CK