The Strange Case OF Nulls And Case

Okay it is not Dr. Jekyll and Mr. Hyde but I had to come up with a title. This was a question someone asked yesterday in the sql programming group


http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/126735827b7ae667#

This person had a case statement like this

SELECT
CASE
WHEN COL1 IS NULL THEN 'UNK'
WHEN COL1 = 'SUGAR' THEN 'SGR'
WHEN COL1 = 'SALT' THEN 'SAL'
WHEN COL1 = 'PEPPER' THEN 'PEP'
END AS items
FROM #INV_ITEMS

This still returned NULLS. Can you spot the flaw? there is no else statement, if there is a value which is not sugar, salt or pepper then a NULL will be returned. let's take a look.

Create this table

CREATE TABLE #INV_ITEMS (COL1 varchar(23))
INSERT #INV_ITEMS VALUES('SUGAR')
INSERT #INV_ITEMS VALUES('SALT')
INSERT #INV_ITEMS VALUES('PEPPER')
INSERT #INV_ITEMS VALUES('WASABI')

Now run this
SELECT
CASE
WHEN COL1 IS NULL THEN 'UNK'
WHEN COL1 = 'SUGAR' THEN 'SGR'
WHEN COL1 = 'SALT' THEN 'SAL'
WHEN COL1 = 'PEPPER' THEN 'PEP'
END AS items
FROM #INV_ITEMS

Output
--------
SGR
SAL
PEP
NULL


So we get a NULL, but which row is that? We just add the column to find out


SELECT Col1,
CASE
WHEN COL1 IS NULL THEN 'UNK'
WHEN COL1 = 'SUGAR' THEN 'SGR'
WHEN COL1 = 'SALT' THEN 'SAL'
WHEN COL1 = 'PEPPER' THEN 'PEP'
END AS items
FROM #INV_ITEMS


Output
--------
SUGAR SGR
SALT SAL
PEPPER PEP
WASABI NULL

Aha, it is the wasabi.

Let's try again by including an ELSE


SELECT Col1,
CASE
WHEN COL1 IS NULL THEN 'UNK'
WHEN COL1 = 'SUGAR' THEN 'SGR'
WHEN COL1 = 'SALT' THEN 'SAL'
WHEN COL1 = 'PEPPER' THEN 'PEP'
ELSE 'UNK'
END AS items
FROM #INV_ITEMS

Output
--------
SUGAR SGR
SALT SAL
PEPPER PEP
WASABI UNK

There we go, it is correct now.

Now let's make it more interesting by inserting a blank, a space and a NULL


INSERT #INV_ITEMS VALUES('')
INSERT #INV_ITEMS VALUES(' ')
INSERT #INV_ITEMS VALUES(NULL)

We can use a combination of NULLIF and RTRIM to filter out blanks, spaces and NULLS

SELECT Col1,
CASE
WHEN NULLIF(RTRIM(COL1),'') IS NULL THEN 'NullOrBlank'
WHEN COL1 = 'SUGAR' THEN 'SGR'
WHEN COL1 = 'SALT' THEN 'SAL'
WHEN COL1 = 'PEPPER' THEN 'PEP'
ELSE 'UNK'
END AS items
FROM #INV_ITEMS

Output
--------
SUGAR SGR
SALT SAL
PEPPER PEP
WASABI UNK
NullOrBlank
NullOrBlank
NULL NullOrBlank

And there it is

DROP TABLE #INV_ITEMS

Retrace : A SQL Profiler Trace Swiss Army Knife

Have you ever needed to find your most expensive queries and quickly grew weary of writing T-SQL against trace tables to try to ferret them out? Have you ever had to wade through gigabytes of trace data just to find one ill-behaving query? Have you ever struggled to decide what performance metrics really matter when analyzing Profiler traces: duration, reads, writes, etc? Ken Henderson's post is about a new tool Bart and Ken wrote to help you do all this and more.



Check it out here: http://blogs.msdn.com/khen1234/archive/2007/12/12/a-sql-profiler-trace-swiss-army-knife.aspx

SQL Teaser: LEN vs DATALENGTH

This should trip up some people.....

Without running this code what do you think will LEN and DATALENGTH return?

DECLARE @i int
SELECT @i =' 123456789 '

SELECT @i,LEN(@i),DATALENGTH(@i)

Office 2007 Service Pack 1 Available For Download

Microsoft has just released their first service pack for Microsoft Office 2007, promising lots of bug fixes, performance enhancements, and improved security. The 2007 Microsoft Office suite Service Pack 1 delivers important customer-requested stability and performance improvements, while incorporating further enhancements to user security. This service pack also includes all of the updates released for the 2007 Office suite prior to December of 2007. You can get a more complete description of SP1, including a list of issues that were fixed, in the Microsoft Knowledge Base article 936982: Description of the 2007 Microsoft Office suite Service Pack 1.

Access 2007
For third-party applications that use Data Access Object code to synchronize replicated information in a Microsoft Office Access™ 2007 database, Office Access 2007 no longer returns a run-time error that causes the application to close or time out.

Communicator 2007
Microsoft Office Communicator 2007 now presents more accurate presence information and does so with consistent visual cues.

• The icons used to display presence are modified so that users who are red-green colorblind can determine people’s presence status.
  
• Office Communicator 2007 no longer causes presence icons to flicker when multiple people appear simultaneously.
  
• Presence information in Office Communicator 2007 and other Microsoft Office applications is consistent in all scenarios.
  
• Microsoft Office Outlook® 2007 no longer starts in the background along with Office Communicator 2007.

Excel 2007
Several stability, general usability, and compatibility improvements have been incorporated into Microsoft Office Excel® 2007.

• Office Excel 2007 no longer stops responding in some instances when opening a workbook containing calculations.
  
• Office Excel 2007 workbooks that are saved in Page Break Preview mode and opened in Microsoft Office Excel 2003 no longer produce an error when switched to Normal view.
  
• When inputting calculations that should result in numbers 65,534 through 65,536, Office Excel 2007 no longer returns the display error of 100,001.
  
• Office Excel 2007 now retains custom colors on shapes in Excel 2003 worksheets.
  
• Office Excel 2007 no longer misplaces or wraps labels in charts when the workbook is reopened.

Groove 2007
For Microsoft Office Groove® 2007, issues surrounding the activation of new and previously retired accounts have been resolved.

• Office Groove 2007 no longer places a 60-day limit on retired accounts that are stored in backup files.
  
• When the auto-activation feature is turned on and invited users open an invitation file (.grv), they are no longer prompted for an activation key or notified of an unconfigured account.
  InfoPath 2007 and InfoPath Forms Server
  Microsoft Office InfoPath® 2007 benefits from a more consistent user experience, especially with online forms.
  
• When users input data into an Office InfoPath 2007 form using a Web browser, the saved changes are now retained after closing and reopening the form.
  
• Font size no longer increases when pasting from one rich text box in Office InfoPath 2007 to another.

Outlook 2007
Microsoft Office Outlook 2007 benefits from a number of fixes and enhancements that make the popular messaging client more stable and easier to use.

• Office Outlook 2007 no longer closes unexpectedly when the junk e-mail rule is set to null.
  
• Body content in HTML-formatted e-mail messages now appears properly in the preview pane or when the message is opened.
  
• Items moved from an offline folder file (.ost) to a personal folder file (.pst) now display properly in the preview pane.
  
• In search folders, Office Outlook 2007 now shows the accurate count for unread items that are hidden.
  
• When scheduling a resource such as a meeting room in meeting requests, the public free/busy information for the resource is now complete and visible in Office Outlook 2007.
  
• When users forward plain-text e-mail messages with attachments, the body text is now retained.
  
• If the Mailbox Cleanup wizard starts while the user is dragging a message into a folder, Office Outlook 2007 no longer closes unexpectedly.

PowerPoint 2007
Fixes in Microsoft Office PowerPoint® 2007 make the presentation application even more reliable, especially when used in conjunction with Microsoft Office Outlook Web Access, Web sites that use forms authentication, or event macros.

• Users who have started an event macro in Office PowerPoint 2007 can now advance slides by right-clicking and then choosing Next or Previous.
  
• When users try to open a presentation in Office PowerPoint 2007 from a Web site using forms authentication, the presentation will now appear properly.
  
• Users can now open presentations in Office PowerPoint 2007 from e-mail attachments in Office Outlook Web Access.
  
• Office PowerPoint 2007 no longer crashes when the user programmatically accesses the parent property of the Hyperlinks Collection Object in the Office PowerPoint 2007 object model.
  
• Text formatting now behaves similarly to previous versions of PowerPoint, where new text retains the formatting of the deleted text instead of using the formatting of the character to the left.

Project 2007 and Project Server 2007
The 2007 Office system SP1 eliminates instances when either Microsoft Office Project 2007 or the accompanying Microsoft Office Project Server 2007 crashes due to a software error.

• Office Project 2007 no longer crashes when users click to view Windows account or Details in the Resources Information dialog box and then perform any other action.
  
• Office Project 2007 does not produce an error message when a link from one task accesses a task in a different project stored on Office Project Server 2007. This error only occurred when the linked project was renamed.
  
• Microsoft Office Project Web Access now makes task IDs available and allows users to enter a work estimate when creating a new task.

SharePoint Server 2007
Microsoft Office SharePoint® Server 2007 has improved manageability and compatibility.

• Office SharePoint Server 2007 now has the ability to be installed on Windows Server 2008 providing compatibility and support.
  
• ASP.NET AJAX has compatibility and support with Office SharePoint Server 2007 including examples for customer web parts build the AJAX 1.0 Control Toolkit and the AJAX 1.0 Extensions for ASP.NET (KB 941955)
  
• New manageability with STSADM commands for consolidating or repartitioning your content databases, renaming host named site collections, and the ability to scope the people picker to a specific active directory organizational unit.
  
• Advanced search results now include results with spaces in file names.
  
• The View by Modified Date function now works correctly in search results.
  
• Office SharePoint Server 2007 is now able to crawl case-sensitive Web content on computers not running the Windows® operating system.
  
• Office SharePoint Server 2007 can now index a public folder on a Microsoft Exchange Server with a backslash in the subject.

Visio 2007
Microsoft Office Visio® 2007 now properly redraws shapes when users apply a dynamic connector that is part of a group of shapes in Office Visio 2007.
Word 2007
By taking care of several small stability and usability details, Microsoft Office Word 2007 is a more useful and reliable tool for everyday document creation tasks.

• With Office Word 2007, users can now open and edit embedded objects that contain other embedded objects.
  
• When using smart documents in Office Word 2007, the Document Actions task pane no longer disappears when moving the pointer into the Reviewing pane. Additionally, task panes opened earlier no longer appear unexpectedly.
  
• Office Word 2007 properly creates and updates a table of contents in unprotected sections of documents that also contain sections protected for forms.
  
• Office Word 2007 no longer prints an extra page when users choose a printer with the duplex option enabled.

Download it here: http://www.microsoft.com/downloads/details.aspx?FamilyId=9EC51594-992C-4165-A997-25DA01F388F5&displaylang=en

The Sad State Of Programmers Part 2 : The In Person Interview

This is part two of a three part series. Part one was about the phone interview, this part is about the in person interview. Part three is about general tips

The point of the in person interview is to see if the person will fit in with the rest of the people in the organization. This is where you get asked such wonderful questions like:
How many gas stations are there in the state of New Jersey?
How would you move Mount Fuji?

These questions are usually asked by managers to see how you handle pressure. There is usually no real answer to these questions, you have to logically come up with an answer and solve this puzzle. For example there are 8 million people in New Jersey, these people have 6 million cars. A gas station gets between 3 and 4 cars per minute; this adds up to 4320 cars per day. You would need about 2000 gas stations to serve all these people. All these numbers could be completely wrong; the point is can you logically figure out the steps to ‘solve’ this problem. It doesn’t matter how brilliant you are, if the people that interview you feel that you are a mismatch personality wise you won’t get hired.

Some observations about the process
Dress in business attire
One person dressed very inappropriate, in fact when picking him up at the security desk we thought he was a delivery person. This is not a way to start the face to face interview. I will get into more detail in part 3 of what to wear and not to wear.

You are your own worst enemy
A person was given source code after the phone interview to study. When asked about the code during the face to face interview she mentioned that she just glanced over the code and did not really look at it. Why would you say something like that if you want to get hired? Now, would you consider hiring this person?

Be proper
Do not grimace like you have a squirrel chewing on your big toe while thinking about an answer. Do not chew on your glasses either while thinking about the answer. When you answer the question wrong, the interviewer gives the correct answer don’t say that is what I meant. If that is what you meant then you should have said so, there are of course exceptions but I am talking about real black and white scenarios.

Bring good code
When you bring source code make sure it is the best code you could possible bring. One person was asked about best practices and error handling; he seemed to know this pretty good. Then he offered to show his code, we noticed that he didn’t have any of these best practices in his code. His proc was called sp_ProcName, error handling was wrong and a bunch of other things. In this case it would have been better if the person did not volunteer to show the code.

And now the questions.

I decided to ask every person these exact questions in the face to face interview; depending on how these were answered I would ask a bunch more questions. There are a couple of questions which you couldn’t answer wrong and I did not count them as toward the total score. Here is a list of some of the questions (Alex don’t get mad), I will comment on these questions and give the answers.

How many bytes can you fit in a row, do you know why
How do you enforce that only values between 1 and 10 are allowed in a column
How to check for a valid date if it is passed in as a string
Can you name the 4 isolation levels in SQL Server 2000
How would you select all rows where the date is 20061127
Name some differences between isnull and coalesce
If you create a local temp table and then call a proc is the temp table available inside the proc
What is a SQL injection
Update trigger to capture changed information
Which naming conventions did you use?
Name some best practices which you implemented
Select 3/2
Decimal(6,5)
Create table with PK symbol 10 characters, price 8 digit precision
Add clustered index to that table
What does set xact_abort on
Select * from table where ID <> 1
Favorite SQL Server book
How do you keep your SQL skills up to date?



How many bytes can you fit in a row, do you know why?
I am looking for one of these answers
8K
8060 bytes
Same as a page
8K + overflow column/text columns
If a person does not know the answer to this question then that is ok.

How do you enforce that only values between 1 and 10 are allowed in a column?
I am looking for check constraint as the answer. A bunch of people didn’t answer anything, some answered trigger and one said you should never check in the DB but in the application itself.

How to check for a valid date if it is passed in as a string?
The answer I am looking for is ISDATE() Some people knew this answer, some people answered “convert to datetime and then check for the error” one person said “parse the string”. A bunch of people didn’t answer anything at all

Can you name the 4 isolation levels in SQL Server 2000?
Another nice to have question, if you don’t know it then is not the end of the world. I will also ask the default transaction level

How would you select all rows where the date is 20061127?
I will draw a table on the board with values like these
2007-12-06 15:36:10.293

2007-12-07 00:00:00.000
2007-12-07 15:36:10.293
2007-12-07 15:36:10.293

2007-12-08 00:00:00.000

I am looking for the 3 rows which start with 2007-12-07
The answer I want is this

WHERE date >= ‘20061127’
AND date < ‘20061128’ I don’t want between because it will grab the 2007-12-08 value also, I don’t want convert because that causes an index scan. Less than half the people get the correct answer. Some stuff to read: Do You Know How Between Works With Dates? http://sqlservercode.blogspot.com/2006/10/do-you-know-how-between-works-with.html

How Are Dates Stored In SQL Server?
http://sqlservercode.blogspot.com/2006/11/how-are-dates-stored-in-sql-server.html

The ultimate guide to the datetime datatypes
http://www.karaszi.com/SQLServer/info_datetime.asp

Name some differences between ISNULL and COALESCE
The main answer I am looking for is that ISNULL can only use 2 values while COALESCE can use a lot more. If the person knows other differences then that is also good. I was surprised at the number of people who never heard of COALESCE. Below is some code which shows the differences

There are three major differences besides being ANSI or not between COALESCE and ISNULL
1) COALESCE correctly promotes its arguments to the highest data type in the expression list, ISNULL does not
2) ISNULL can only work with 2 values while COALESCE can take a lot more
3) The alternate value takes the length of the first vale with ISNULL, with COALESCE this doesn't happen


Let's get started, run the following blocks of code

The result is 7, integer math

SELECT 15 / ISNULL(CONVERT(INT,NULL), 2.00)

The result is 7.5, which is correct

SELECT 15 / COALESCE(CONVERT(INT,NULL), 2.00)

You will see that the result is not the same ISNULL does integer math while COALESCE does not

COALESCE correctly promotes its arguments to the highest data type in the expression list.
ISNULL just looks at the first datatype, which is an integer (15) and makes everything an int
COALESCE looks at 2.00 and 15 and then promotes the integer to decimal

Another example is returning the first non null value, ISNULL can only take 2 values while COALESCE can take a whole lot more
Here we have 4 variables and all except for one are null


DECLARE @Var1 VARCHAR(20)
DECLARE @Var2 VARCHAR(20)
DECLARE @Var3 VARCHAR(20)
DECLARE @Var4 VARCHAR(20)
SELECT @Var4 = 'ABC'

--This will return ABC
SELECT COALESCE(@Var1,@Var2,@Var3,@Var4)

Last example.
ISNULL returns NOT while COALESCE returns Not There.

DECLARE @v VARCHAR(3)
SELECT COALESCE(@v,'Not There')
SELECT ISNULL(@v,'Not There')

The alternate value takes the length of the first value with ISNULL, with COALESCE this doesn't happen

If you create a local temp table and then call a proc is the temp table available inside the proc?
The answer is yes and the code is below

--create proc
CREATE PROC TestProc
AS

SET NOCOUNT ON

SELECT * FROM #temp
GO

--create our temp table
CREATE TABLE #temp (id int)

INSERT #temp VALUES(1)
INSERT #temp VALUES(2)
INSERT #temp VALUES(3)

--exec proc
EXEC TestProc

What is SQL injection?
People either knew or did not know about SQL injection, the ones who knew also knew what to do to prevent it. A little less than half the people interviewed knew what it was.

Some stuff to read:
SQL injection cheat sheet.
http://ferruh.mavituna.com/makale/sql-injection-cheatsheet/


The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html


Update trigger to capture changed information
In drew two tables on the whiteboard and explained to the people that I wanted to move data into a history table only when the value in a certain column changed. I was looking for several things
If the person knew about inserted and deleted tables
If the person would code the trigger for multi row statements and not assign the value to a variable
If the person knew about IF UPDATE(Column) and that this was true even if the table was updated with the same value
If the person would join inserted and deleted to make sure no row would be inserted if the value didn’t change

Some stuff to read:
Fun With SQL server Update Triggers
http://sqlservercode.blogspot.com/2005/12/fun-with-sql-server-update-triggers.html

What naming conventions did you use?
Here I wanted to hear if the person had any standards, I would also ask for an example of a proc name hoping sp_ProcName wouldn’t be answered

Some stuff to read:
The ISO organization has a document on their site. The one that deals with naming conventions is 11179-5 The link will point to a zip file which has a pdf file in it. The TOC of this pdf file is below Contents Foreword 1 Scope 2 Normative references 3 Terms and definitions 4 Data Identifiers within a registry 5 Identification 6 Names 6.1 Names in a registry 6.2 Naming conventions 7 Development of naming conventions 7.1 Introduction 7.2 Scope principle 7.3 Authority principle 7.4 Semantic principle 7.5 Syntactic principle 7.6 Lexical principle 7.7 Uniqueness principle Annex A (informative) Example naming conventions for names within an MDR registry Annex B (informative) Example naming conventions for Asian languages

What best practices did you implement?
There is a whole range of possible answers here and I won’t get into detail.

Select 3/2
When I wrote Select 3/2 on the board and ask what this would return several people looked at me if I was crazy. The answer is of course 1 because of integer math. I ask this question because we run reports which deal with calculation with integers. Take a look at this code to see what you can do to ‘fix’ the issue

Run this
--Integer math
DECLARE @Val1 INT,@val2 INT
SELECT @Val1 =3, @val2 =2
SELECT @Val1/@Val2
GO

It returns 1


Now run this
DECLARE @Val1 INT,@val2 INT
SELECT @Val1 =3, @val2 =2

--Implicit
SELECT @Val1/(@Val2*1.0)

--Explicit
SELECT CONVERT(DECIMAL(18,4),@Val1)/@Val2
GO

Both of those return 1.50000000000000

So what does @val2*1.0 do? well run this

DECLARE @Val1 INT,@val2 INT
SELECT @Val1 =3, @val2 =2
SELECT CAST(SQL_VARIANT_PROPERTY(@val2*1.0,'BaseType') AS VARCHAR(20)) + '(' +
CAST(SQL_VARIANT_PROPERTY(@val2*1.0,'Precision') AS VARCHAR(10)) + ',' +
CAST(SQL_VARIANT_PROPERTY(@val2*1.0,'Scale') AS VARCHAR(10)) + ')'

As you can see it is numeric(13,1)

Decimal(6,5)
What is the greatest value that Decimal(6,5) can hold? A lot of people answered 999999.99999 which is wrong of course. The total number of numbers is 6 and 5 of those are after the comma. So the answer is 9.99999. Run this to try it out for yourself.


Create table with PK symbol 10 characters, price 8 digit precision
I would tell people to create a table with 2 columns
1 with a column named symbol variable character 10 in length, it would also be a primary key
Price which was decimal and can hold a number as big as 999999.99999999
They had to do this on the whiteboard in T-SQL (create table…..)
Most people got the PK syntax wrong; some people got the decimal wrong. The reason I am asking this question is because we can’t go on a production machine with Enterprise Manager and start clicking. You might not have permissions to make changes, you need to supply a script which other people might run.

Add clustered index to that table
My next question would be to add a clustered index to that table. The correct answer would be you can’t since the table has a primary key which is a clustered index by default

What does set xact_abort on do
Only one person answered this question correctly, this was another nice to know question. I did explain to people why you would use this, sometimes you cannot trap the error and this statement will enable you to rollback everything up until that point.

Some stuff to read:
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html

Error Handling in SQL Server – a Background.
http://www.sommarskog.se/error-handling-I.html

Select * from table where ID <> 1
I would draw a table with three rows, the values being 1, 2 and null. Then I would ask what would be returned after running this query

SELECT * FROM #temp WHERE id <> 1

A lot of people chocked on this one, the answer is 2 of course, null will not be returned. Test it out for yourself


CREATE TABLE #temp (id int)
INSERT #temp VALUES(1)
INSERT #temp VALUES(2)
INSERT #temp VALUES(null)

SELECT * FROM #temp WHERE id <> 1

Some stuff to read:
NULL trouble In SQL Server Land
http://sqlservercode.blogspot.com/2006/01/null-trouble-in-sql-server-land.html

NULL - The database's black hole
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/07/06/null-ndash-the-database-rsquo-s-black-hole.aspx

The logic of three-valued logic
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/07/17/the-logic-of-three-valued-logic.aspx

Dr. Unknown, or how I learned to stop worrying and love the NULL
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/09/22/dr-unknown-or-how-i-learned-to-stop-worrying-and-love-the-null.aspx

What if null if null is null null null is null?
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/09/30/what-if-null-if-null-is-null-null-null-is-null.aspx

Favorite SQL Server book
Here something bizarre happened; there were several people who did not have a SQL Server book at all. I did not understand this; how do you not have a SQL server books as a developer? A bunch of people listed Ken Henderson’s Guru book, some of them listed Inside SQL Server and one person answered SQL Server 2005 unleashed (I have the 2000 version, I should check this one out). In part three I will give you a list of my favorite books.

How do you keep your SQL skills up to date?
I am looking for websites, books, blogs, conferences, webinars, podcasts etc. Some people answered Books On Line. One person answered certification (I will address certification and my dislike of them in part 3). I was surprised by the difficulty that people had in answering this question. SQL Server Central was a popular website as well as DevX and MSDN. Nobody answered podcast at all. I would ask people if they played around with the latest CTP of SQL Server 2008, most of them were not aware that this was available to the general public. I did interview a bunch of SQL Server authors and I asked them what they did or would do to master SQL. You can read those interviews by clicking on the links below

Louis Davidson
Itzik Ben-Gan
Ken Henderson
Adam Machanic
Kalen Delaney

That was it fort part two, I originally wanted to put more content here but I was afraid it would become too long, I moved some of that stuff to part three.

Video: Microsoft SQL Server 2008 Nov CTP - Spatial (Part 1/3)

Head over to Channel 9 and watch the first part of a three part series about the spatial data type in SQL server 2008. Besides video formats there are also mp3 and wma versions in case you just want to listen to it

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

The Most Natural Set Option

SET ANSI_NULL_DFLT_OFF ON

this is what this means, in English....

Set the setting ANSI NULL Default Off to ON

Why not just SET ANSI_NULL_DFLT [ON|OFF]?

This is even worse than SET NOCOUNT ON

The Sad State Of Programmers Part 1 : The Phone Interview.

This is going to be a three part series.
Part 1 The phone interview.
Part 2 The face to face interview.
Part 3 Some tips and observations

A while back I posted that we are looking for a SQL/.NET/FoxPro developer. I did this because we had a real hard time finding this person. I am happy to inform you that we did find this person and he will start in two weeks. Interestingly enough we hired the person with the least years of experience (on paper). This person knew more that people with three times his experience in years.

These days when looking for a programmer you have to do phone interviews if you don’t want to waste an incredible amount of time. A phone interview enables you to assess the skill set of a potential employee without wasting time by picking him up, getting a security badge, booking a conference room etc. A phone interview is also good for the candidate since he/she doesn’t have to travel or dress up to do the interview.

Some things are difficult to ask over the phone but if the candidate looks (or should that be sounds) good then you can ask those questions when you bring the person in. Some people will prepare for a phone interview by having all their books and notes in front of them. They will ask you to repeat the question and while you do so you can hear them flipping pages frantically. So you might be able to cheat on the phone interview but be assured that if you do not know your stuff that you will fall flat on your face on a face to face interview (no pun intended).

One thing I never understood is the fact that it takes a person one minute to answer a question. You either know or don’t know the question. Keep your answers concise, do not spend 3 minutes explaining to me what the difference is between a clustered and non clustered index.
I had to reword my questions slightly because when I asked a question like “Do you know what the difference is between a clustered index and a non clustered index?” some people would reply “yes”. Because of that I changed the question to “Describe what the difference is between a clustered index and a non clustered index?”

Do not shoot yourself in the foot by giving me additional information which is wrong. I asked for the fastest way to empty a table. Almost every single person who knew about truncate added that you cannot rollback a truncate statement. I wrote about that myth a couple of months ago: SQL Myth: Truncate Cannot Be Rolled Back Because It Is Not Logged

I tend to ask between 20 and 40 questions, if I see the candidate’s skill is not good enough I don’t ask everything. Some of the questions are esoteric but I simply ask these questions to get a feel of the overall skill level; it doesn’t matter if they answer these wrong. You can find a list of question here: How Well Do You Interview And Do You Use Wizard Driven Programming?

Here are some interesting answers from the interviews.
Almost every single person answered that an index scan is better than an index seek.
There were several people with SQL Server 2005 experience, these people couldn’t name one single new thing introduced in SQL Server 2005. I asked about windowing functions, DMVs, pivot, apply and more, this was all Greek to them. One person had on her resume that she developed an app in SQL Server 2005. When I asked about her experience she told me she just started to read about SQL Server 2005. This is a big show stopper, sometimes headhunters/recruiters will tell you to just add it to your resume, I wouldn’t do it because it makes you look bad. If the SQL Server 2005 experience is not true what else could be made up? One person had on his resume that he optimized complex stored procedures, when I asked how he did it, he replied that he only selected the rows he needed instead of the whole table. This obviously didn’t answer my question.

That is it for the phone interview, part 2 will be up in a day or two.

EURO 2008 draw

My picks below

Group A: Switzerland, Turkey, Portugal, Czech Republic
1. Portugal, Czech Republic.

Group B: Austria, Poland, Germany, Croatia
1.Croatia, 2. Germany.

Group C: Holland, France, Romania, Italy
1. France, 2. Holland.

Group D: Greece, Russia, Spain, Sweden
1. Spain, 2. Greece.

Screencast: SQL Server 2008 - Table valued parameters

If you have to insert more than one row of data in a Table, you end up executing the insert statement / stored procedure as many times for each row, with Table-valued parameters you can send multiple rows of data to a single TSQL statement or stored procedure without creating temp tables or doing multiple round trips.

Table-valued parameters are a new parameter type in SQL Server 2008 and are declared by using user-defined table types.

Watch the screencast(SWF)
Watch the screencast(WMV)

Integer Math In SQL Server

What do you think the following query will return in SQL Server?

SELECT 3/2

If you said 1.5 then you are wrong! The correct answer is 1, this is because when doing division with 2 integers the result will also be an integer.
There are two things you can do
1 multiply one of the integers by 1.0
2 convert one of the integers to a decimal


Integer math is integer result
DECLARE @Val1 INT,@val2 INT
SELECT @Val1 =3, @val2 =2

SELECT @Val1/@Val2

Result 1

Convert explicit or implicit to get the correct answer
DECLARE @Val1 INT,@val2 INT
SELECT @Val1 =3, @val2 =2

--Implicit
SELECT @Val1/(@Val2*1.0)
--Explicit
SELECT CONVERT(DECIMAL(18,4),@Val1)/@Val2

Result 1.50000000000000

Visual Studio 2008 Training on Channel 9

VS2008 Training Kit: Building Windows Presentation Foundation Applications in Visual Studio 2008 and Expression Blend
This session was presented by Jaime Rodriguez

Come and learn how to build Windows Presentation Foundation (WPF) applications with the help of the new WPF designer in Visual Studio 2008 This session focuses on the construction of a real-world application demonstrating how to get the best out of the designer's capabilities. Learn the basics of building WPF applications in the Visual Studio 2008 WPF designer; have a clear view on when you'll want to work in XAML Code and on the Visual Studio design surface to get your WPF application built; and see how you can use Microsoft Expression Blend in conjunction with Visual Studio 2008 WPF designer.

Watch the screencast here: http://channel9.msdn.com/ShowPost.aspx?PostID=359715#359715


VS2008 Training Kit: What's New in Windows Presentation Foundation 3.5
This session was presented by Kevin Moore

Learn about the work being done in the Orcas release of WPF, including improved 3D with UIElement3D, increased animation smoothness, better cookie and FireFox plug-in support for XBAPs, as well as support for AddInControl, LINQ, ADO.NET and much more!

Download it here: http://channel9.msdn.com/ShowPost.aspx?PostID=359713#359713


VS2008 Training Kit: Introduction to Silverlight
This session was presented by Adam Kinney

Silverlight is a cross platform UI framework for creating compelling Web experiences. Come learn the high-level architecture of Silverlight and the tools that are available to build Silverlight applications.

Download it here: http://channel9.msdn.com/ShowPost.aspx?PostID=359712#359712


VS2008 Training Kit: Building Web Applications with Visual Studio 2008
This session was presented by Ryan Dunn

This session covers the new design and development features in Visual Studio 2008 for creating rich web applications. Topics include the new CSS and HTML design tools, much improved Javascript Intellisense and debugging support, and the new ASP.NET 3.5 controls supporting LINQ and AJAX.

Download it here: http://channel9.msdn.com/ShowPost.aspx?PostID=359711#359711


VS2008 Training Kit: Introduction to ASP.NET AJAX
This session was presented by Steve Marx

In this session, you'll learn how to use ASP.NET AJAX to build a richer, more responsive user experience. Topics include partial rendering, web services, structured scripting, and integration with Silverlight.


Download it here: http://channel9.msdn.com/ShowPost.aspx?PostID=359706#359706

.NET Framework 3.5 Common Namespaces and Types Poster Available For Download

The .NET Framework 3.5 Common Namespaces and Types Poster is downloadable as XPS or PDF format. There is also an XPS format file which prints over 16 letter or A4 pages for easy printing. Some assembly is required if you choose this print method.

Get it here: http://www.microsoft.com/downloads/details.aspx?FamilyID=7b645f3a-6d22-4548-a0d8-c2a27e1917f8&DisplayLang=en

World Cup Soccer 2010 Qualification - UEFA draw, England And Croatia In The Same Group Again!

Group 1:
Portugal, Sweden, Denmark, Hungary, Albania, Malta

Group 2:
Greece, Israel, Switzerland, Moldova, Latvia, Luxembourg

Group 3:
Czech Rep., Poland, Northern Ireland, Slovakia, Slovenia, San
Marino

Group 4:
Germany, Russia, Finland, Wales, Azerbaijan, Liechtenstein

Group 5:
Spain, Turkey, Belgium, Bosnia-Herzegovina, Armenia, Estonia

Group 6:
Croatia, England, Ukraine, Belarus, Kazakhstan, Andorra

Group 7:
France, Romania, Serbia, Lithuania, Austria, Faroe Islands

Group 8:
Italy, Bulgaria, Ireland, Cyprus, Georgia, Montenegro

Group 9:
Netherlands, Scotland, Norway, Macedonia FYR, Iceland

Croatia and England again in the same group :-)
Group 7 doesn't look easy either

Whitepaper on Malware to Attack Databases

Brian Kelly on his blog mentiones a whitepaper by Cesar Cerrudo: Data0: Next generation malware for stealing databases. This whitepaper describes how malware could be crafted to steal information out of databases.

The attack will use the following techniques:

• Discovery
  
• Exploitation
  
• Escalate Privileges (if necessary)
  
• Cover Tracks

Print it out and read it while you wait in line on Black Friday

Just For My English Friends

England and Croatia in the same group again for World Cup 2010 qualifications:
Group 6: Croatia, England, Ukraine, Belarus, Kazakhstan, Andorra

Thanksgiving SQL Teaser COUNT

Here is a small simple Thanksgiving teaser. What do you think will the result be of the select count query?

USE tempdb
GO

CREATE TABLE Customer (CustomerID INT PRIMARY KEY)
INSERT Customer VALUES (1)
INSERT Customer VALUES (2)
INSERT Customer VALUES (3)
INSERT Customer VALUES (4)

SELECT COUNT(*) Customer

Visual Studio 2005 Support for SQL Server 2008, Community Technology Preview

This CTP resolves a problem that when you try to open a database connection to SQL Server 2008 by using Visual Studio 2005 design tools, you may receive the following error message:
“This server version is not supported. Only servers up to Microsoft SQL Server 2005 are supported.”
This CTP addresses this issue, and enables the following Visual Studio functionality for SQL Server 2008 CTP5 :

• Server Explorer successfully connects to SQL Server 2008, and database objects such as stored procedures and table data can be viewed and edited. Note that table schemas still cannot be viewed or edited in this release.
  
• SQL CLR projects that target SQL Server 2008 CTP5 can be created and deployed to the server.
  
• T-SQL and SQL CLR debugging are now enabled for SQL Server 2008 CTP5.
  Data binding features in Client and Web Projects are enabled.

This CTP does not support the following features for SQL Server 2008 Nov CTP:

• Creating and editing table schemas in Table Designer or Database Diagrams. The table designer feature in SQL Server Management Studio 2008 can be used to edit table schemas in SQL Server 2008 CTP5.

Download it here: http://www.microsoft.com/downloads/details.aspx?FamilyID=e1109aef-1aa2-408d-aa0f-9df094f993bf&displaylang=en

Surface Area Configuration Tool Is Discontinued, SQL-DMO Removed from Microsoft SQL Server 2008 Express

Just reading the readme file in SQL Server 2008 November CTP. I noticed that in addition to SQL Server Notification Services the following two things are also being removed.

SQL-DMO Removed from Microsoft SQL Server 2008 Express
Surface Area Configuration Tool Is Discontinued

The Surface Area Configuration Tool is discontinued for SQL Server 2008. The following table shows what you can use to configure settings, options, and component features in the November CTP.

Protocols, and connection and startup options
Use SQL Server Configuration Manager.
Database Engine features
Use Declarative Management Framework.
SSAS features
Use the property settings in SQL Server Management Studio.
SSRS features
Edit the RSReportServer.config configuration file.

Microsoft SQL Server 2008 CTP November 2007 Available For Download

Microsoft SQL Server 2008 CTP, November 2007 is available for download from the connect site.

There are several formats available.
DVD image files:
X86 DVD Image
X64 DVD Image
IA64 DVD Image

Self-extracting executables:
X86 Executable
X64 Executable
IA64 Executable
Express 2008 Executable

Get it here: http://www.microsoft.com/downloads/details.aspx?FamilyId=3BF4C5CA-B905-4EBC-8901-1D4C1D1DA884&displaylang=en

Visual Studio 2008 RTM Available On MSDN

Yes it is true, Visual Studio 2008 RTM is available on MSDN. But be warned the site is slower than ever.



Okay I downloaded it and installed it.

Visual Studio 2008 Available For Download Early Next Week

MSDN Subscriptions WebLog Has a post stating that Visual Studio 2008 will be available early next week. From the site:

Visual Studio 2008 is anticipated out early next week, with availability for Subscribers. Check out the "Top Subscriber Downloads" area on http://msdn2.microsoft.com/subscriptions for VS 2008 downloads.

Microsoft Buiness Intelligence Screen Casts

Channel 9 will be publishing a bunch of Business Intelligence Screen Casts.
This screencast is the first in a series BI Developer screencasts recorded as part of a workshop built on SQL Server 2005 that has been delivered around the globe by Microsoft and Microsoft Partners. This training event takes the student through the Microsoft BI Platform giving a BI Developer the introduction and basic comfort needed to tackle a BI project using Microsoft technology.

This first screencast will take you through the Microsoft BI Platform briefly touching on each of the technologies.
Microsoft BI - Platform Integration

This screencast shows how to use SQL Server Integration Services (SSIS), Microsoft's ETL tool, to send conditional email alerts during a load.
Microsoft BI - Developing a Query-Driven E-mail Delivery System

INTERCEPT In SQL Server 2005

I was writing a query and managed to mistype INTERSECT, I typed INTERCEPT and to my surprise the query ran, it returned 2 result set just as if INTERCEPT wasn't there at all
Try it yourself

CREATE TABLE testnulls (ID INT)
INSERT INTO testnulls VALUES (1)
INSERT INTO testnulls VALUES (2)
INSERT INTO testnulls VALUES (null)

CREATE TABLE testjoin (ID INT)
INSERT INTO testjoin VALUES (1)
INSERT INTO testjoin VALUES (3)


SELECT * FROM #testjoin
INTERSECT
SELECT * FROM #testnulls


SELECT * FROM #testjoin
INTERCEPT
SELECT * FROM #testnulls


Feature, Bug?

Okay, it actually doesn't matter what you type between the two statements

SELECT * FROM #testjoin
sdsdsdsdsd
SELECT * FROM #testnulls

That runs just as if you executed the query without sdsdsdsdsd
??????

WAKE UP!!! That acts as an alias, now where is my caffeine.

How Is Your Sensitive Data Encrypted In The Database?

Do you store encrypted data? If you do then how is it encrypted? Do you use the built in capabilities of SQL Server 2005/2008. If you answered yes to the last question then here is another question. What would happen if someone stole the hard drive or even the whole database server? Could they decrypt that data easily with the stored procedures which you have written? Do these store procedures use the DecryptByPassphrase function?

So you probably think that I am crazy and no one would ever steal a database server. Wrong! C I Host a Chicago-based co-location got robbed 4 times since 2005. One company lost 20 servers in the latest heist. You can read more details about that here: http://www.theregister.co.uk/2007/11/02/chicaco_datacenter_breaches/

Without going into too much detail, this is what we are doing. Our data is encrypted by a corporate crypto tool which can only be accessed from within the DMZ. Keys are created for specific machines; these keys can easily be revoked at any time. Even if you would somehow steal our web and database server you would still be out of luck because of that. The data is encrypted by the tool and stored encrypted in the DB.

Identity theft will cost you in the future.
The Identity Theft Enforcement and Restitution Act of 2007 has been introduced and was scheduled for debate on November 1st; the Senate and the House still have to vote on it. This is a bill to amend title 18, United States Code, to enable increased federal prosecution of identity theft crimes and to allow for restitution to victims of identity theft.

Follow the developments here:
S. 2168: Identity Theft Enforcement and Restitution Act of 2007

SQL Teaser: Printing Without Using PRINT

Print the @SQL variable without using PRINT

DECLARE @SQL varchar(49)
SELECT @SQL = 'Print This Now ' + CONVERT(VARCHAR(30), GETDATE())
--Your Code Here

E-Learning: What's New in Microsoft SQL Server 2008

Microsoft leaning has made available a bunch of free SQL server 2008 lessons

In this 3 hour online collection, IT Professionals will learn about the new features in SQL Server 2008. Topics covered within these clinics include:
What's New in SQL Server 2008 for Enterprise Data Platform
What's New in SQL Server 2008 for Business Intelligence
What's New in SQL Server 2008 for Database Development Student

Below are the links to the three lessons:

Clinic 6188: What's New in Microsoft SQL Server 2008 for Enterprise Data Platform
Clinic 6189: What's New in Microsoft SQL Server 2008 for Business Intelligence
Clinic 6190: What's New in Microsoft SQL Server 2008 for Database Development

Return Null If A Value Is A Certain Value

You need to return NULL only if the value of your data is a certain value. How do you do this?
There are three different ways.

NULLIF
DECLARE @1 char(1)
SELECT @1 ='D'


SELECT NULLIF(@1,'D')


REPLACE
This should not really be used, I just added it here to demonstrate that you can in fact use it.

DECLARE @1 char(1)
SELECT @1 ='D'

SELECT REPLACE(@1,'D',NULL)


CASE
With case you can test for a range of values. You can test for example for values between A and D. If you reverse the logic then you also don't need to provide the ELSE part since it defaults to NULL anyway.

DECLARE @1 char(1)
SELECT @1 ='D'


SELECT CASE @1 WHEN 'D' THEN NULL ELSE @1 END

--No else needed
SELECT CASE WHEN @1 <> 'D' THEN @1 END

And this is how you test for a range.

--Null
DECLARE @1 char(1)
SELECT @1 ='D'

SELECT CASE WHEN @1 BETWEEN 'A' AND 'D' THEN NULL ELSE @1 END

--E
DECLARE @1 char(1)
SELECT @1 ='E'

SELECT CASE WHEN @1 BETWEEN 'A' AND 'D' THEN NULL ELSE @1 END

Christmas Is Coming Early This Year

Note from my manager:

"All,

It looks like we have some book money left for this year that we can spend.
Please select 2 books each that you have wanted and send the titles to me and I’ll get them on the list."

Very nice, I picked the following two books below.
The Art of SQL
SQL Server 2005 Practical Troubleshooting: The Database Engine

What would you have picked?

Microsoft Commits to November Release Date for Visual Studio 2008 and the .NET Framework 3.5

Today, during the keynote address at Microsoft TechEd Developers 2007, S. “Soma” Somasegar, corporate vice president of the Developer Division at Microsoft Corp., announced that Microsoft will release Visual Studio 2008 and the .NET Framework 3.5 by the end of November 2007. Visual Studio 2008 and the .NET Framework 3.5 enable developers at all levels to rapidly create connected applications that offer compelling user experiences for Windows Vista, Windows Server 2008, the 2007 Microsoft Office system, mobile devices and the Web. Soma also unveiled plans to open new opportunities for Visual Studio partners, as well as to deliver new tools and resources for developers, including a first Community Technology Preview (CTP) of the Microsoft Sync Framework and new capabilities for Popfly Explorer.

“The highly social and visual nature of the Web has fundamentally changed what users expect from all applications they interact with, regardless of whether it’s on a customer-facing Web site or Windows rich client application, or a desktop business application built using Microsoft Office,” said Somasegar. “Traditionally, organizations have been hard pressed to deliver the richer, more connected applications and services they need to boost productivity, drive revenue and stay ahead of the competition. With Visual Studio 2008 and the .NET Framework 3.5, it is easy for developers to use the skills they already have to build compelling applications that take advantage of the latest platforms.”

FWBS Ltd., Xerox Corp., Dell Inc. and K2 are just a few of the early adopter customers that are already experiencing the benefits of these releases. FWBS used Visual Studio, the .NET Framework and the Microsoft Office system to build an Office Business Application (OBA) for the law field. The application enables users to work within Microsoft Office — the tools they use every day — while also dramatically improving productivity and helping users respond quickly to changing business needs.

Xerox has also had early success developing applications with the new tools. “We’ve already seen significant advantages from using Visual Studio Team System 2008 and .NET Framework 3.5. With the first application we built, we easily saved 50 percent of the time and money it would have taken to create the same application with other tools,” said Eugene Shustef, feature design lead, Global Technology, Xerox. “That’s more than a savings to IT — it delivers a huge time-to-market advantage because it put the tool into the hands of our analysts six months sooner than they would have had it otherwise.”

Creating New Opportunities for Partners

Microsoft also announced plans to make additional investments in the Visual Studio partner ecosystem. In response to partner feedback and in order to provide better support for interoperability with other developer tools and cross-platform scenarios, Microsoft is today announcing plans to change licensing terms, no longer limiting partners to building solutions on top of Visual Studio for Windows and other Microsoft platforms only. This licensing change will be effective for the release of Visual Studio 2008 and the Visual Studio 2008 SDK.

“Integrating dynaTrace’s cross-platform application performance management and diagnostics product with Visual Studio has opened up additional commercial opportunities for our business and delivered a compelling solution for our customers. .NET and Visual Studio is a strategic platform for our business, and Microsoft’s additional investments in the partner ecosystem make it even more compelling,” said Klaus Fellner, senior director of product marketing at dynaTrace. “We’re looking forward to taking advantage of the new technology available with the launch of Visual Studio 2008 and the partner benefits available through the Visual Studio Industry Partner (VSIP) program.”

In addition, Microsoft announced plans to create a shared source licensing program for Premier-level partners in the VSIP program. The program will provide these partners with the ability to view Visual Studio IDE source code for debugging purposes, and simplify the process of integrating their products with Visual Studio 2008.

Tools for Today and Tomorrow

Microsoft also announced a number of additional resources for developers of all skill sets, enabling them to make the most out of their Microsoft tools investments to build great applications on the latest platforms:

• The first CTP of the Microsoft Sync Framework demonstrates Microsoft’s ongoing investments in synchronization and builds on the synchronization functionality available in Visual Studio 2008. With Visual Studio 2008, developers can rapidly take advantage of offline synchronization capabilities to sync-enable applications and services easily with rich designer support. The Microsoft Sync Framework extends the support featured in Visual Studio 2008 to also include offline and peer-to-peer collaboration using any protocol for any data type, and any data store. This is part of Microsoft’s long-term commitment to providing synchronization for partners and independent software vendors that can embed the Sync Framework into their applications easily to create rich sync-enabled ecosystems that allow any type of data to follow their customers wherever they go.



• A new release of Popfly Explorer will add new Web tools that provide Web developers and Popfly users an easy way to add Silverlight gadgets built in Popfly to their Web pages, as well as publish HTML Web pages directly to Popfly.


These latest releases are part of the broader Microsoft Application Platform, a portfolio of technology capabilities and core products that help organizations develop, deploy and manage applications and IT infrastructure. They also mark another major milestone leading up to the global launch of Windows Server 2008, Visual Studio 2008 and SQL Server 2008 on Feb. 27, 2008, in Los Angeles.

Product Information and Availability

Visual Studio 2008 and the .NET Framework 3.5 will be available by the end of November 2007. The .NET Framework 3.5 will also be available to end users via a free, optional download from Microsoft Update. A CTP of Microsoft Sync Framework is available today at http://msdn.microsoft.com/sync. Popfly Explorer is a hosted development environment available today at http://www.popfly.com. More information about all of these releases is available at http://www.msdn.microsoft.com/vstudio.

How many Java programmers does it take to round up to a power of two?

How many Java programmers does it take to round up to a power of two?
Well take a look:
http://forum.java.sun.com/thread.jspa?threadID=248212&start=0&tstart=0

Do you want to laugh or cry?

I don't know if I should laugh or cry after looking at this code. This could not have been written by a human right?

Anyway here it is, consider it a teaser. If you want to torture yourself and look at the whole query then go here: http://www.tek-tips.com/viewthread.cfm?qid=1421304&page=1



HAVING (((1 = 1)
AND (PP.Created >= ISNULL(NULL,'1/1/1900')
AND PP.Created < 1 =" 2)">= ISNULL(NULL,'1/1/1900')
AND (MIN(PV.Visit)) < DATEADD(d, 1, ISNULL(NULL,'1/1/3000')))
))



WHERE
(
(NULL IS NOT NULL
AND pv.DoctorId IN (NULL))
OR(NULL IS NULL)
)



CASE
WHEN '0' = 1 THEN df.ListName
WHEN '0' = 2 THEN df2.ListName
WHEN '0' = 3 THEN ic.ListName
ELSE NULL
END


Someone will red flag the post, here is the whole query.


/* New Patient Analysis */

SET NOCOUNT ON

CREATE TABLE #PatientTemp
(
[PatientProfileId] [int] NOT NULL ,
[Prefix] [varchar] (10) NULL ,
[First] [varchar] (30) NULL ,
[Middle] [varchar] (30) NULL ,
[Last] [varchar] (30) NULL ,
[Suffix] [varchar] (20) NULL ,
[Created] [datetime] NOT NULL
)

Insert #PatientTemp

SELECT PP.PatientProfileId, PP.Prefix, PP.First, PP.Middle, PP.Last, PP.Suffix,
CASE
WHEN 1 = 1 THEN PP.Created
WHEN 1 = 2 THEN Min(PV.Visit)
ELSE NULL
END As Created

FROM PatientVisit PV
INNER JOIN PatientProfile PP ON PP.PatientProfileId = PV.PatientProfileId

GROUP BY PP.PatientProfileId, PP.Prefix, PP.First, PP.Middle, PP.Last, PP.Suffix, PP.Created

HAVING (((1 = 1)AND
(PP.Created >= ISNULL(NULL,'1/1/1900') AND PP.Created < DATEADD(d, 1, ISNULL(NULL,'1/1/3000')))
))
OR
((1 = 2)AND
((MIN(PV.Visit) >= ISNULL(NULL,'1/1/1900') AND (MIN(PV.Visit)) < DATEADD(d, 1, ISNULL(NULL,'1/1/3000')))
))

Order By PP.First, PP.Last

SELECT dbo.FormatName(#PatientTemp.Prefix, #PatientTemp.First, #PatientTemp.Middle, #PatientTemp.Last, #PatientTemp.Suffix) AS Name,
CASE
WHEN 1 = 1 THEN #PatientTemp.Created
WHEN 1 = 2 THEN Min(pv.Visit)
ELSE NULL
END As Created,
COUNT(*) AS [Number Of Visits],
SUM(pva.OrigInsAllocation + pva.OrigPatAllocation) AS Charges,
SUM(pva.InsPayment + pva.PatPayment) AS Payments,
CASE
WHEN '0' = 1 THEN df.ListName
WHEN '0' = 2 THEN df2.ListName
WHEN '0' = 3 THEN ic.ListName
ELSE NULLA
END As Grouping

FROM PatientVisit pv
INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
INNER JOIN #PatientTemp ON pv.PatientProfileId = #PatientTemp.PatientProfileId
INNER JOIN DoctorFacility df ON pv.DoctorId = df.DoctorFacilityId
LEFT JOIN DoctorFacility df1 ON pv.ReferringDoctorId = df1.DoctorFacilityId
LEFT JOIN DoctorFacility df2 ON pv.FacilityId = df2.DoctorFacilityId
LEFT JOIN InsuranceCarriers ic ON ic.InsuranceCarriersId = pv.PrimaryInsuranceCarriersId

WHERE
(
(NULL IS NOT NULL AND pv.DoctorId IN (NULL)) OR
(NULL IS NULL)
)
AND
(
(NULL IS NOT NULL AND pv.ReferringDoctorId IN (NULL)) OR
(NULL IS NULL)
)
AND
(
(NULL IS NOT NULL AND pv.FacilityId IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on insurance carrier
(
(NULL IS NOT NULL AND ic.InsuranceCarriersId IN (NULL)) OR
(NULL IS NULL)
)

GROUP BY #PatientTemp.Created,dbo.FormatName(#PatientTemp.Prefix, #PatientTemp .First, #PatientTemp.Middle, #PatientTemp.Last, #PatientTemp.Suffix),
pva.PatientProfileId,
CASE
WHEN '0' = 1 THEN df.ListName
WHEN '0' = 2 THEN df2.ListName
WHEN '0' = 3 THEN ic.ListName
ELSE NULL
END

HAVING (((1 = 1)AND
(#PatientTemp.Created >= ISNULL(NULL,'1/1/1900') AND #PatientTemp.Created < DATEADD(d, 1, ISNULL(NULL,'1/1/3000')))
))
OR
((1 = 2)AND
((MIN(pv.Visit) >= ISNULL(NULL,'1/1/1900') AND (MIN(pv.Visit)) < DATEADD(d, 1, ISNULL(NULL,'1/1/3000')))
))

ORDER BY dbo.FormatName(#PatientTemp.Prefix, #PatientTemp.First, #PatientTemp.Middle, #PatientTemp.Last, #PatientTemp .Suffix), #PatientTemp.Created
Drop table #PatientTemp

Sort Values Ascending But NULLS Last

This is a frequent request in newsgroups and fora. People want to sort the column in ascending order but don't want the NULLS at the beginning.
Oracle has this syntax: ORDER BY ColumnName NULLS LAST;
SQL Server does not have this. But there are 2 ways to do this. The first one is by using case and the second one by using COALESCE and the maximum value for the data type in the order by clause.

The 2 approaches with a datetime data type

DECLARE @Temp table(Col datetime)
INSERT INTO @Temp VALUES(getdate())
INSERT INTO @Temp VALUES('2007-10-19 09:54:03.730')
INSERT INTO @Temp VALUES('2006-10-19 09:54:03.730')
INSERT INTO @Temp VALUES('2005-10-19 09:54:03.730')
INSERT INTO @Temp VALUES('2006-10-19 09:54:03.730')
INSERT INTO @Temp VALUES('2004-10-19 09:54:03.730')
INSERT INTO @Temp VALUES(NULL)
INSERT INTO @Temp VALUES(NULL)

SELECT *
FROM @Temp
ORDER BY COALESCE(Col,'9999-12-31 23:59:59.997')

SELECT *
FROM @Temp
ORDER BY CASE WHEN Col Is NULL Then 1 Else 0 End, Col

The 2 approaches with an integer data type

DECLARE @Temp table(Col int)
INSERT INTO @Temp VALUES(1)
INSERT INTO @Temp VALUES(555)
INSERT INTO @Temp VALUES(444)
INSERT INTO @Temp VALUES(333)
INSERT INTO @Temp VALUES(5656565)
INSERT INTO @Temp VALUES(3)
INSERT INTO @Temp VALUES(NULL)
INSERT INTO @Temp VALUES(NULL)

SELECT *
FROM @Temp
ORDER BY COALESCE(Col,'2147483647')

SELECT *
FROM @Temp
ORDER BY CASE WHEN Col Is NULL Then 1 Else 0 End, Col

Interview With Kalen Delaney About Inside Microsoft SQL Server 2005 Query Tuning and Optimization

I am a big fan of Inside Microsoft SQL Server 2005 The Storage Engine so when I saw that yet another book got published in this series I just had to check it out. Inside Microsoft SQL Server 2005: Query Tuning and Optimization is very well written gets to the point and give you the answers that you need. I decided to contact Kalen to see if she would be willing to do an interview, by now you know that the answer is yes of course ;-)


The question-and-answer session with Kalen that follows was conducted via email.

What is the audience for this book, is it the enterprise user or can a small department benefit from the tips in this book?
Because this book deals with query tuning, anyone who writes SQL queries for SQL Server can benefit. Very little in the book is geared towards system tuning, so the size of the machine doesn’t really matter. Now of course, if you have a very small system with very small tables, you won’t get as much benefit out of tuning your queries. However, if you have any tables of more than a few thousand rows, and you do any joins, you will need to tune your queries. In addition, the issues of blocking and concurrency control can impact any system, no matter how small.

What new technologies in SQL Server 2005 do you think are the most beneficial for performance?
For very large databases, the best new technology is partitioning. For any size system, if you have had serious performance problems due to blocking, you might find a big performance benefit by using one of the snapshot-based isolation levels, but you really need to understand the resource costs that come along with the improved performance. For your individual queries, I think the new optimizer hints and query level recompiles can make a big difference. For indexes, the ability to add included columns to nonclustered indexes can give some of your hard-to-tune queries a major performance boost.

What will a person who reads this book gain in terms of understanding how to performance tune a server?
The focus of this book is not so much on tuning the server, but on tuning queries. There is more in Inside SQL Server 2005: The Storage Engine on server issues such as memory and processor management. The biggest server wide issues are covered in Chapter 5, when I talk about managing the plan cache, and how and when query plans are reused.

Is the book geared towards a beginner/intermediate level user or do you have to be an advanced user to really utilize the information in this book?
The book is not geared towards beginners, but everyone should be able to get something out of it. It’s primarily geared to SQL Server developers and DBA’s who have been working with SQL Server for a while, and have encountered performance problems that they are trying to find solutions for.

With all the changes in SQL Server 2005, how critical has the tempdb become in regards to performance?
Tempdb has always been important. In SQL Server 2005, if you are using one of the snapshot-based isolation levels, you are going to have to be more aware of the demands placed on tempdb, both in the sizing requirements and the additional I/O resources needed. Fortunately, SQL Server 2005 provides tools to monitor tempdb, including a dozen new performance monitor counters, and a dynamic management view, sys.dm_db_file_space_usage, that keeps track of how much space in tempdb is being used for each of the different kind of object stored in tempdb.

I understand that this is the first time you wrote with a team of other writers; can you tell us something about that experience?
I initially thought that not having to write the whole volume by myself meant that I could get it done sooner, but that was not the case. Everyone had their own schedule and their own way of writing. The personal aspect of working with the other authors was great. I deeply respect all of the others and it was an honor to be working so closely with them. I had some concerns about the depth of coverage and I wondered whether all the chapters would end up being as deep as I hoped for, but that turned out not to be a major problem. The only real issues were agreeing on a common terminology and coding style, and even that wasn’t that big of an issue, because I got to do a final editing pass on everyone’s chapters.

What SQL Server books are on your bookshelf?
All of the Inside SQL Server books are there, of course, and all of Ken Henderson’s books. Bob Beauchemin’s book is in my car, to read while I am waiting for the ferryboat, and while on the ferry. I also have technical books that aren’t SQL Server specific, such as Jim Gray’s Transaction Processing, Russinovich’s and Solomon’s Windows Internals, Chris Date’s Introduction to Database Systems and Mike Stonebraker’s Readings in Database Systems.

Why do you write technical books?
I love working with SQL Server and trying to find out all I can about it. When I found that I could explain difficult concepts in a training environment, I thought I could do the same thing in a written format, and reach more people that way. I have always loved explaining things, ever since I was a teaching assistant for High School Math.

Will you be updating your books for SQL Server 2008?
I have just started meeting with my editors at Microsoft Press about SQL Server 2008, and it looks like a revision is in the plans. We’re really looking at it as just a revision, with the same structures as the current books, with straightforward changes and the inclusion of new features.

Name three things that are new in SQL Server 2005 that you find are the most valuable?
Dynamic Management Views, Dynamic Management Views, and Dynamic Management Views!
Oh, you wanted three different things? ;-) How about XML query plans and optimization hints. (I’m also very fond of many of the new TSQL constructs, but I was only talking about things that I cover in my new book.)

Name three things which are coming in SQL Server 2008 that you are most excited about?
You’ll have to ask me this next time. I have actually been avoiding SQL Server 2008 while I was getting my Query Tuning and Optimization book finished, because I didn’t want to get distracted.

Can you list any third party tools that you find useful to have as a SQL Server developer/admin?
I’ve tried a few other products, but usefully I find that it is much easier to just stick with the Microsoft line and use the tools provided with the product.

Name some of your favorite non-technical books.
Oh, I love to read. It would be impossible to list my favorite books, but I can tell you my favorite authors, most of whom write science fiction: Lois McMaster Bujold, Ursula LeGuin, Sheri Tepper, Orson Scott Card, Octavia Butler, Elizabeth Moon. I also love to read historical fiction like Leon Uris.

SQL Server 2008 Whitepapers

There are some good katmai whitepapers appearing on microsoft.com.
The list is available here http://www.microsoft.com/sql/2008/learning/whitepapers.mspx.

Performance and Scale http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_perfandscale.mspx

Manageability http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_manageability.mspx

Delivering location intelligence with spatial data http://www.microsoft.com/sql/techinfo/whitepapers/spatialdata.mspx

SQL Server 2008 Reporting Services http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_ssrs.mspx

What's new for XML in SQL Server 2008 http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_xml.mspx

Managing unstructured data http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_unstructured.mspx

Server consolidation http://www.microsoft.com/sql/techinfo/whitepapers/hosting_sql_srv_consol.mspx

Data warehousing
http://www.microsoft.com/sql/techinfo/whitepapers/sql-for-datawarehousing_2008.mspx

Data Programmability http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_dp.mspx

SQL Teaser: Guess the output

What do you think will be the output?


DECLARE @d datetime
SET @d = '20071010'

SELECT DATEADD(yy, DATEDIFF(yy, 0, @d)+1, -1)

After that run this to see how to get first and last days for years, quarters, months and weeks(be careful of ISO weeks!)

DECLARE @d datetime
SET @d = '20071010'

SELECT DATEADD(yy, DATEDIFF(yy, 0, @d), 0) as FirstDayOfYear,
DATEADD(yy, DATEDIFF(yy, 0, @d)+1, -1) as LastDayOfYear,
DATEADD(qq, DATEDIFF(qq, 0, @d), 0) as FirstDayOfQuarter,
DATEADD(qq, DATEDIFF(qq, 0, @d)+1, -1) as LastDayOfQuarter,
DATEADD(mm, DATEDIFF(mm, 0, @d), 0) as FirstDayOfMonth,
DATEADD(mm, DATEDIFF(mm, 0, @d)+1, -1) as LastDayOfMonth,
DATEADD(wk, DATEDIFF(wk, 0, @d), 0) as FirstDayOfWeek,
DATEADD(wk, DATEDIFF(wk, 0, @d)+1, -1) as LastDayOfWeek