Alien VS Predator picture

This is priceless

Alien VS Predator picture

In case you wonder which one the predator is, here is a hint
one of them preys on children :-)

help hisham to set up an online business, aka begging on the web

Posted here at the microsoft.public.sqlserver.programming forum

Hello world.
I had some problems with my business.
My name is Noor Hisham Bin Ahmad.
I,m from Malaysia.
I need some funds to support my blog because I want set up an online
business.

this is my account number.
Bank Simpanan Nasional
0210029816898886


via western union

How pathetic is that, maybe it should be called Begging 2.0

Screencast: SQL Server 2008 Change data capture

CDC or Change data capture is a new feature in SQL Server 2008, which is an ability to record changes to table data into another table without writing triggers or some other mechanism, Change data capture records the changes like insert, update, and delete to a table in SQL server thus making the details of the changes available in relational format.

Find more information on the Topic
http://www.microsoft.com/sql/2008/prodinfo/download.mspx https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5507

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

I am stuffed

Sole, Shrimp, White Wine, Herb de Provence, Tomato Paste, Shallots, Onion, Garlic, Parsley, Pepper and Salt make for a great dinner

SharePoint and SQL Server Reporting Services Posts

blah!bLaH!BLOG has a couple of posts about SharePoint and SQL Server Reporting Services


SharePoint and Reporting Services - Introduction
SharePoint and Reporting Services - Installation and Setup.
SharePoint and Reporting Services - Setup your database.
SharePoint and Reporting Services - Authoring a simple report.
SharePoint and Reporting Services - Enabling end users - the report model.

If you are doing any SharePoint and SQL Server Reporting Services development then do yourself a favor and visit the links above

Kids Might Become Overwhelmed Tomorrow

My wife let each of the kids open one present already because they might become overwhelmed tomorrow
This is the biggest nonsense I have ever heard, she just wanted to see their reaction when opening the toys. Oh well
Maybe I should open one of my presents also.

Stop The Press, Internet Explorer 8 Passes ACID 2 Test

Stop the press,

Internet Explorer 8 Passes ACID 2 Test
Britney Spear's sister is pregnant
Aliens seen crossing the border, these are not illegal and have green skin
Elvis spotted in Greenland
Duke Nukem Forever Teaser Released

So only 3 of these are real. Internet Explorer 8 finally passes the ACID 2 test. Of course some bonehead will say that this is because the Opera suit. Yeah right, they fixed all their stuff in one week.

Channel 9 has a video on their site with two people from the IE team.
The IE team has been very hard at work on IE 8 for the past several months and they hit a huge milestone last Friday evening. The IE dev team checked in a bunch of code that included several new features implemented in the core rendering engine that enable IE to pass the ACID 2 test! This is great news for web developers: IE 8 is going to be our most standards compliant browser to date. Passing ACID 2 is really a combined side effect of all the new features that have been developed for IE 8.

Listen to the podcast(MP3)
Listen to the podcast(WMA)
Download the Video
Watch the Video

Interview With Joe Celko About The Forthcoming Book Thinking In Sets

I noticed that Joe Celko has a new book coming out soon: Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL. I decided to send Joe an email to see if he would be willing to answer some questions I had about the book and SQL in general. To my surprise Joe was more than willing to accommodate my request. The question-and-answer session with Joe that follows was conducted via email.

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?
I would say intermediate level. You need to know enough SQL to do some programming in the language and be experienced enough to see that DDL is as important as DML.

What are the most important things a person can do to master SQL?
The most important thing is to make the leap from procedural programming to declarative programming, from sequential files to sets. The only declarative language that most programmers have seen is spreadsheets. They are nothing like SQL!

I assume you wrote this book because people when they first use a database tend to do the same thing they do in Java, C# or other procedural languages; get a bunch of rows and manipulate them one by one. Is this the number one mistake beginners do?
That is right up there in the top five, for sure! But I think that the classic error is in not knowing how to design the schema in the first place. A bad schema -- denormalized, bad data element names, no constraints, no proper keys, no referential integrity -- leads to trying to correct the flaws in DDL in the DML. If you have a good schema, then the queries, inserts, updates, and deletes are usually fairly easy. All of the "hard work" should be hidden in the database and not spread over the application code and DML.

What is so bad about attribute splitting (somehow these three tables come to mind: Squids, Automobiles and Britney Spears)?
NO, no, no! I coined the phrase a "Squids, Automobiles and Britney Spears" table or column to refer to a table or column which has more than one kind of entity or more than one kind of attribute in it. What makes that phrase so fun is that there is a web page which compared photos of a giant squid and Britney Spears after she cut off her hair. If you actually gave such tables or columns a meaningful name, then you would see that it is not a proper table or column. These nightmares would have names like "hat_or_shoe_size_depending_on_column_five" Attribute splitting is when you take an attribute and make it into two or more tables, columns or rows. The table example is the most common one. How often have you seen a table for each location (split on geography) or a table for each month (temporal split)? This mimics a tape file system, so newbies feel comfortable. When you ask them if they would split a Personnel table into MalePersonnel and FemalePersonnel (split on gender), they immediately see the fallacy. Unfortunately, splits need all kinds of code if they want any data integrity. This code usually re-assembles the data elements back to what they should have been in the first place.

In chapter two of your new book you talk about the new advances in hardware. I have noticed that somehow the amount of data I have to store out paces the advance in hardware and the queries don't run any faster. Will Solid-State Disks finally change that?
I have great hopes for solid state devices. Any solid thing that has to physically move is by definition slower than electricity or light. We are looking at nano-tech, better lasers and a ton of new technology almost every month.

I see you have a whole chapter on auxiliary tables, I am a big fan of those tables myself and I use them to create dates on the fly or split comma delimited strings. In your opinion what percentage of developers does not use them and why should you use them?
Not enough. SQL is a language meant to handle data and not to do computations. Auxiliary tables can be shared among sessions and accessed in parallel. Imagine a complicated but deterministic formula. In a procedural computational model, you hang in a loop and re-do the work for each record in a sequential file. Let me give you a real-world example of that. Corky's BBQ does a huge mail-order business at Christmas time. The pick lists need to include the size of the boxes to be used, so part-time help can do their jobs. When I got this problem, they had discovered that using weights was not right way to go. The approach being attempted was to play "3-D Tetris" with the products. Since that kind of thing is a bitch to program, they were getting nowhere. My approach was to look over a few years of shipping history and find out how many different orders they had shipped and what the smallest box use for each of those orders was. There were only about 5000 configurations and the majority were standard gift packages. Do a relational division and a table look-up to handle 99.98% of the cases and print the phrase "Hand pick this order" on the pick list for the exceptions! In the SQL model, you join an auxiliary table which has the parameters and the result value in each row. You can do this simple multi-column equi-join once in parallel. Wait until the multi-core chips make parallelism the only way to design a computer; then auxiliary table are going to really fly.

I noticed you have a big chapter on VIEWs; do you feel that VIEWs are not utilized enough by developers?
They are used either not enough or too much.

Why do you think we should not use bit or Boolean flags in SQL?
In SQL, to be a data type it must allow NULLs. What is the fundamental nature of a BIT? It is one or zero; there is no NULL concept here! This was a problem for SQL Server, when they made their BIT data type into a numeric that could be NULL-able. The change scrambled a lot of data when it was ported from one release to the next. Bits are low-level, hardware dependent concepts. Are you high end or low end hardware? Do you have 4, 8, 16, 32 or 64 bit machine words? You have use proprietary operators. This defeats the idea of machine independence. Finally, bit flags are used to destroy First Normal Form (1NF) and thus destroy data integrity. As an example, recently in as newsgroup someone wanted to use a 4-bit column to store all the possible colors for a product (red, green, blue, yellow) and get them out with a bit mask (his hardware has a nibble!). But how do you add purple? How do you set up a constraint that no item can be mad with both red and green options? In RDBMS, we discover the state of our data with predicates and not by setting flags at the hardware level.

I have your puzzles book and noticed that you have a paragraph on Sudoku and one on Bin Packing in the last chapter of this book. You have procedural solutions and SQL solutions for most of the material in the chapter; is the SQL solution faster?
There is a funny story on the Sudoku problem. Richard Romley is a retired DBA from Smith-Barney and he decided to play with Sudoku for recreation. He used SQL Server on his home machine and coded a solver in one SELECT statement. The procedure takes 81 parameters (the starting grid) and does an 81-way self-join. It produces ALL the valid answers -- bet you did not know that many published puzzles have multiple solutions! The code is straight forward and depends on the optimizer to handle the search condition logic. Even longer problems with tens of answers run in well under one second. The procedural solvers vary, but I have seen some that stop when they get to the first valid grid. If there is only one solution, they are very fast. But is the right answer actually the *set* of valid grids? Since I am an SQL person, I think so. The procedural solvers can get hung up by backtracking to the starting position when there are a few hundred answers and become very slow. I also strongly recommend getting the Japanese or Chinese editions if you read either language. My two translators cleaned up some old code and added new solutions as we went along.

Should having good naming conventions such as 11179-5 be included in database courses?
Drop them in as soon as you start. If you grow up with good conventions, you will start doing it without thinking about it. When I teach RDBMS, I start with scales and measurement theory so that my students know what data is all about -- whether it is in a database or not.

When can we expect your new book to show up in bookstores?
It was supposed to be out in 2008 February, but we lucked up and it will be out in 2008 January. Production was faster than planned. I guess after seven books, and working with the same people, we have it down pretty well!

A bunch of questions not related to the book

Why do you write technical books?
I have no talent for fiction. I cannot get a plot or characters onto paper to save my life and my dialogues are awful chains of "he said-she said" stiff sentences. My grandfather wrote children's poems in Slovak, and I have even less talent for poetry. I have a number of friends who write detective novels, Science Fiction and Fantasy. They don't consider me a real author because I don't do fiction. I think about trying my hand at YA (Young Adult) books -- Danica McKellar (Winnie from the WONDER YEARS television show) just did a math book for girls, so maybe I could do "A Child's Garden of Normal Forms" or a juvenile detective series called "The Hardware Boys", then go on to a television show called "Query Eye for the Database Guy" or something.

Which of your books was the hardest to write and why?
First edition of SQL FOR SMARTIES! It was my first book and I thought that having written a few hundred magazine columns would make it easy. I was dead wrong -- completely different skill set. I was a year late in delivering the manuscript. After that, I had a system in place.

What is coming down the line? Any new books or updates to current ones?
I am trying to do at least one book per year -- more if I am unemployed and need the advances. My current thoughts are a book on the use of Standards in a database, and one on programming tricks with OLAP functions, CTEs, and other new features in the SQL-2003 Standards. My Morgan-Kaufmann books tend to follow a five year cycle, just like the ANSI/ISO Standards. I also get asked by vendors to do product specific books. I might self-publish something completely off-topic. I have a book on domino games based on my postings and I teach Texas-42 on Royal Carri bean Cruises -- it is a domino game only played in Texas. I also have a book on Pai Gow (a gambling game played with Chinese dominoes) which might sell 10 copies. I will also be doing some video classes, but I don't have details yet.

Which of all the SQL books that you wrote is your favorite?
DATA & DATABASES, which never got the sales of the others. It is more philosophical and concerned with the nature of data instead of programming.

What SQL Server books are on your bookshelf?
Anything I can get by Henderson, Machanic, Moreau, Ben Gan and Delaney. The SQL Server experts are pretty well-known and they publish. This is not true for other products, especially the open-source RDBMS products.

Why do you participate in newsgroups and do you think it is a good idea for beginners to ask questions in newsgroups?
To do some shameless self-promotion, of course :) Newsgroups are a good source of SQL problems and some clever answers that I can use in books and when I am consulting. I also have a pedantic streak I did to get out. And if I am available on a newsgroup, people don't fill up my mailbox at home. And, yes, beginners should use newsgroups for help. But not to have someone else do their job or their homework for them. I like to see the mindset of people who are just learning SQL. It is not enough to see that someone is making a mistake; you want to figure out what lead to that particular mistake. Remember Chernobyl? Everyone did just what they were supposed to do, but there were a few critical assumptions that lead to an event cascade.

What are Cowboy Coders and id-iots?
The term "cowboy coder" is an old one. It means someone who starts coding without any design phase, without any overview to the system as a whole, without any research for industry standards or a company data dictionary. They usually love dialect code and tricks that trade immediate performance for maintainability. The heavy dialect code also gives them job security, since they usually only know one product. An "ID-iot" is a newbie who has no RDBMS education and wants to have the comfort of a sequential file system. So he puts an IDENTITY column on every table as the PRIMARY KEY. Never mind that it is proprietary and non-relational; it is the familiar record number from a file system which can use to mimic pointer chains. He does not understand that rows are not records, tables are not files, columns are not fields and references are constraints and not pointers.

I have been working with Sybase IQ for a little bit; what is your opinion on columnar databases?
Sybase IQ is not the only game in town. I consulted with SAND (nee Marcus, nee Nucleus) years ago. It was one of the first such products. Later I ran into WX2 (nee White Cross) and I am looking at Stonebreaker's Verticia now. Their advantages in parallelism and compressing large amounts of data make them the best choice for Data Warehouses. I would also look at Teradata, which uses hashing. That will become more important as the research on minimal perfect hashing functions gets out of the lab and into products.

Where can we expect to see you in 2008? Any conferences, seminars, trade shows or classrooms perhaps?
I will hopefully be doing some more "SQL Saturdays!" on my weekends. I want to do more webcasts, but I am not sure if I am ready for YouTube. My other travel goal for 2008 is to get to Australia or Japan; I have never gone past Hawaii.

Some of Joe Celko's Books:
SQL for Smarties
SQL Programming Style
Trees and Hierarchies in SQL
SQL Puzzles and Answers
Data and Databases

Use XACT_STATE() To Check For Doomed Transactions

Let's say you have the following transaction

BEGIN TRANSACTION TranA
BEGIN TRY
DECLARE @cond INT;
SET @cond = 1
END TRY
BEGIN CATCH
PRINT 'a'
END CATCH;
COMMIT TRAN TranA

This first transaction runs without a problem


Now change value of @cond from 1 to 'A' and run this code below.
BEGIN TRANSACTION TranA
BEGIN TRY
DECLARE @cond INT;
SET @cond = 'A';
END TRY
BEGIN CATCH
PRINT 'a'
END CATCH;
COMMIT TRAN TranA



This transaction will blow up with the following message
Server: Msg 3930, Level 16, State 1, Line 15
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Server: Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.


We can use XACT_STATE() to check if we need to rollback or not without blowing up. I also used the ERROR_MESSAGE() function to print the error

BEGIN TRANSACTION TranA
BEGIN TRY
DECLARE @cond INT;
SET @cond = 'A';
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE();
END CATCH;
IF XACT_STATE() =0
BEGIN
COMMIT TRAN TranA
END
ELSE
BEGIN
ROLLBACK TRAN TranA
END

After running that we can see that the following message was printed

Conversion failed when converting the varchar value 'A' to data type int.

We trapped the error, rolled back the transaction and the transaction did not blow up

EXISTS or COUNT(*)

Do you use this

IF (SELECT COUNT(*) FROM SomeTable
WHERE SomeColumn = SomeValue ) > 0

Or do you use this

IF EXISTS (SELECT * FROM SomeTable WHERE SomeColumn = SomeValue )

If you answered COUNT(*) then maybe you should take a look these two articles

Andrew Kelly has a nice post on SQLBlog
http://sqlblog.com/blogs/andrew_kelly/archive/2007/12/15/exists-vs-count-the-battle-never-ends.aspx

Matija Lah has a good post on his snaps & snippets blog
http://milambda.blogspot.com/2006/10/exists-or-not-exists-that-is-question.html

GNET: Even Google Screws Up By Showing A Bunch Of MySQL Messages

When I visited http://www.gnet.com/ today I got greeted by the following error messages. These messages should never be seen by users. What about a friendly message instead? Anyway the mess is below.


Warning: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /home/gnet/public_html.v2/libs/adodb/drivers/adodb-mysql.inc.php on line 359

Warning: MySQL Connection Failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /home/gnet/public_html.v2/libs/adodb/drivers/adodb-mysql.inc.php on line 359

Warning: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /home/gnet/public_html.v2/libs/adodb/drivers/adodb-mysql.inc.php on line 359

Warning: MySQL Connection Failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /home/gnet/public_html.v2/libs/adodb/drivers/adodb-mysql.inc.php on line 359

Warning: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /home/gnet/public_html.v2/libs/adodb/drivers/adodb-mysql.inc.php on line 359

Warning: MySQL Connection Failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /home/gnet/public_html.v2/libs/adodb/drivers/adodb-mysql.inc.php on line 359

Warning: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /home/gnet/public_html.v2/libs/adodb/drivers/adodb-mysql.inc.php on line 359

Warning: MySQL Connection Failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /home/gnet/public_html.v2/libs/adodb/drivers/adodb-mysql.inc.php on line 359

Would You Put Your Database In The Cloud?

Amazon launched a database in the cloud.

Amazon SimpleDB is a web service for running queries on structured data in real time. This service works in close conjunction with Amazon Simple Storage Service (Amazon S3) and Amazon Elastic Compute Cloud (Amazon EC2), collectively providing the ability to store, process and query data sets in the cloud. These services are designed to make web-scale computing easier and more cost-effective for developers.

Traditionally, this type of functionality has been accomplished with a clustered relational database that requires a sizable upfront investment, brings more complexity than is typically needed, and often requires a DBA to maintain and administer. In contrast, Amazon SimpleDB is easy to use and provides the core functionality of a database - real-time lookup and simple querying of structured data - without the operational complexity. Amazon SimpleDB requires no schema, automatically indexes your data and provides a simple API for storage and access. This eliminates the administrative burden of data modeling, index maintenance, and performance tuning. Developers gain access to this functionality within Amazon's proven computing environment, are able to scale instantly, and pay only for what they use.

Amazon Simple Storage Service is a pretty nice service they provide. But would you move your database to Amazon?

You can read more about Amazon SimpleDB here: http://www.amazon.com/b/ref=sc_fe_c_1_3435361_1?ie=UTF8&node=342335011&no=3435361&me=A36L942TSJ2AJA

They also have a developer guide, getting started guide, FAQs and pricing.

What do you think?

SQL Server Business Intelligence Screencasts

Channel 9 has posted a lot of Business Intelligence screencasts. In case you are bored below is the list. Strangely enough they skip numbers

Business Intelligence #01a: Introduction to Microsoft BI
This screencast provides an overview of Business Intelligence including basic database design concepts, ETL, reporting and analytics.


Business Intelligence #01b: Introduction to SQL Server 2005 Tools and Sample Databases
This screencast introduces the tools within the Microsoft Business Intelligence platform as well as a look at the Adventure Works OLTP and OLAP databases.


Business Intelligence #03a: Loading and Integrating Data from Multiple Sources
This screencast introduces the concept of Data Flow within SQL Server Integration Services (SSIS) including the use of Merge Join and Derived columns.

Business Intelligence #04a: Identifying and Importing New Customers by Using Fuzzy Lookup
This screencast introduces the concept of fuzzy lookup and highlights SQL Server Integration Services (SSIS) support within a Data Flow for performing these tasks.

Business Intelligence #04b: Implementing Slowly Changing Dimensions in the Data Flow
This screencast covers one of the most common scenarios when building a data warehouse. It introduces the concept of Slowly Changing Dimensions (SCD) and how to solve that challenge with SQL Server Integration Services (SSIS).

Business Intelligence #04c: Advanced Package Design
This screencast quickly covers some of the more advanced concepts such as Event Handlers, Logging and Package Configurations using SQL Server Integration Services (SSIS).

Business Intelligence #05a: Designing a Basic Report
This screencast covers the basics of using SQL Server Reporting Services (SSRS) including configuration of data sources and building basic reports. A few small tip, tricks and best practices are also covered.

Business Intelligence #05b: Adding Interactivity to a Report
This screencast shows the viewer how to add parameters and drill through to reports developed using SQL Server Reporting Services (SSRS).

Business Intelligence #06a: Developing a Report Model and Designing a Report Builder Report
This screencast cover building a Report Model in Business Intelligence Development Studio and show how to connect a report using SQL Server Reporting Services (SSRS).

Business Intelligence #07a: Deploying and Accessing Reports
This screencast shows how to embed and deploy SQL Server Reporting Services (SSRS) reports into your applications.


Business Intelligence #09a: Designing Dimensions
This screencast covers building dimensions on a cube built using SQL Server Analysis Services (SSAS).

Business Intelligence #09b: Advanced Dimension Design
This screencast covers optimization of the dimensions built in the previous screencast using SQL Server Analysis Services (SSAS).

Business Intelligence #09c: Using the Cube Wizard
This screencast covers the use of the Cube Wizard including some tips and tricks in SQL Server Analysis Services (SSAS).


Business Intelligence #09d: Introducing Cube Calculations
His screencast adds to the three previous screencasts by adding calculations to an SQL Server Analysis Services (SSAS) cube.


Business Intelligence #10a: Designing Reports on the UDM
This screencast covers optimization of the dimensions built in the previous screencast using SQL Server Analysis Services (SSAS).

Business Intelligence #10b: Exploring Analysis Features of Microsoft Excel 2007
This screencast covers the use of Excel as a tool for viewing data within a cube built with SQL Server Analysis Services (SSAS).

Business Intelligence #11a: More Analysis with Microsoft Excel 2007
This screencast covers using Excel as a powerful tool drill deeper into your information.


Business Intelligence #12a: Introducing Data Mining
This screencast covers general Data Mining techniques using SQL Server Analysis Services (SSAS).

Business Intelligence #12b: Introducing the Table Analysis Tool for Excel 2007
This screencast cover the use of Data Mining Add-Ins for Office using SQL Server Analysis Services (SSAS) as the data mining engine on table data within Excel.

Business Intelligence #14a: Building a PerformancePoint Scorecard
This screencast covers building and publishing scorecards with PerformancePoint.

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