A blog about SQL Server, Books, Movies and life in general
Thursday, December 27, 2007
The Sad State Of Programmers Part 3: General Tips
Part 1 The Phone Interview
Part 2 The face to face interview.
As far as the resume and interview tips go, I only focused on stuff I have encountered. You can find many tips on the internet and I did not want this post to be a copy of those.
Resume tips
Don’t repeat the same line
If you had 4 jobs and you did more or less the same thing then try to have a different description. If you have to read the same sentence 4 times it gets very boring fast. Try to emphasize what you did at one company versus another. Maybe you worked with a lot more data at one company, maybe your stored procedures had a lot more error checking or had complex business logic. If you list the same thing four times then you are not really differentiating yourself from other people to the prospective interviewer.
List variations of the same keyword
Some companies will feed your resume into a keyword matching program. So in addition to having SQL you also need T-SQL, Structured Query Language and Transact SQL. The first time a recruiter told me this I was baffled, “it is all the same” I told her, she then told me that they used programs and if you don’t score high enough they won’t even consider you.
Do not lie on your resume
If you don’t have experience in SQL Server 2005 then do NOT list it on your resume. It is better not to list it then to be asked about it and admitting you don’t know it. One person admitted he put Java on the resume because the recruiter told him so. Did this get him a job? Of course it did not. Once you have one thing that is not true on your resume the interviewer will wonder what else could be a lie.
Do not try to impress the interviewer on paper
If you have Impressive Object Oriented Skills listed on your resume then you can be sure the interviewer will ask all kinds of OOP stuff. If your skills are really not that impressive then it won’t look that good.
Don’t list your certification right below your name
I saw one resume where the person had the certification right below his name; a certification is not a Ph.D it doesn’t take a lot of money and years to get one. I did notice that the more certifications a person has the less the person knew. I don’t know why this is, maybe it is to compensate for lack of skills, and it is puzzling to me. Your certifications should be listed after your education.
Try to keep your resume concise
If you have a resume which spans 8 pages then try to make it into two or three pages if possible. You can accomplish this by using a smaller font, cutting out duplicate sentences and leaving out sentences that don’t really show any skills. A sentence like the following does not add anything to the resume at all: worked with third party development tools. What does that sentence tell someone? You can use an application or development tool, doesn’t everyone? If you are a web developer then do not list FrontPage on your resume, this will make you look like an amateur.
Use nice paper
Buy yourself some high-quality paper. Your resume is a summary of what you have accomplished so far, don’t use regular paper for that, be proud of your accomplishments use good paper! Keep your resume in pristine condition, buy a folder so that your resume doesn’t get wrinkled.
You worked at the same company for the past 15 years
List all the different positions you have held separately. Listing the positions separately will make the job progression within the company much more obvious.
Interviewing tips
Dress for success
I mentioned it before and I will mention it again: show up for the interview dressed in business attire.
Dress conservative
For women this means the following:
No miniskirts
No high heels or platform shoes
No revealing shirts
No excessive jewelry
No 80s hair styles
Don’t pour gallons of Chanel No 5 on yourself, some people are allergic to perfume and might cut the interview short.
Men should be equally conservative
A navy or black suit is your best bet. You should wear a white shirt; my wife who worked in the banking industry told me a story once about a perfect candidate who did not get hired because he wore a blue shirt. I know it sounds ridiculous but you never know who sits at the other end of the table. I do have friends who over the phone find out about the dress code and ask if they can come in without a suit. I wouldn’t do it; if you have a suit wear it.
No neon colored ties, pick a conservative color.
No excessive jewelry; a wedding band and a watch is all you need. Keep you Cartier love bracelet at home until you get hired.
Don’t be a walking perfume factory, this is not a date.
Shave and trim your hair, if you have long hair keep it out of your face, if you have a beard then keep it neat.
Do your research
Here are two true stories. One person asked if we made shampoos because she passed Johnson & Johnson on the way to our building, she assumed we were a subsidiary. Another person did not know we got acquired by News Corp. it is okay if you don’t know what we do and we did not ask you but do NOT ask what a company does, you should have looked that up before the interview. Do ask what direction the company is going to, how they plan to deal with competition etc.
Behave proper
What you consider normal might not be considered normal by other people. Some people have phobias; they don’t want to be touched for example. I asked a person to explain to me what a deadlock was, he told me to grab the phone then he grabbed my hand and told me to pick up the phone. He said I couldn’t pick up the phone because he locked it. Don’t chew gum. Don’t say “What?’ but ask the interviewer to repeat the question.
Ask questions
Don’t just answer question but also ask questions. Ask about the team, development style, growth of the department and anything else you deem important.
Technical skills and how to keep them up to date
Not everyone works with the latest versions of SQL Server or Visual Studio. Maybe you don’t have a MSDN subscription at work to download the latest versions. When asked if you used the latest version of SQL Server do not say “no because we don’t use it currently at my job”. Some companies will see this as a sign that you are doing the same stuff day in and day out. Download the latest CTP or free express versions, use it at home, build stuff on the weekends. This way you can say that even though you have not used it at work you were still exposed to it. You initiated this yourself and this is an indication that you are willing to learn even in your free time.
I always ask the candidate how the skills are kept up to date. A lot of people have a very tough time answering this question; it might be because they don’t keep it up to date unless they are sent to a training class by their current employer. Instead of paying $40 for a video game or the new HD DVD directors cut invest in a book, this $40 investment will pay itself back very fast (of course you need to read the book and not use it as a paper weight). When you are the one at work who people ask questions to then this will get noticed and you might get promoted sooner. If your employer does not reimburse you for books then keep the recites; if you itemize on your tax return you can use them to lower you marginal tax rate.
Get a RSS reader and subscribe to blogs. There is great content from the SQL Server team, from book authors and from trainers. SQLBlog is a great blog to subscribe to and also check out the roller (http://sqlblog.com/roller/roller.aspx) there are some great blogs there. Visit forums (fora) and newsgroups, here is the SQL server programming one
http://groups.google.com/group/microsoft.public.sqlserver.programming
If you are not comfortable answering question then lurk, read what the masters answer and you will remember it when you have the same problem later on. Don’t worry if you get flamed, I myself answered a question a long time ago and some MVP answered to my answer “Hello table scan anyone”. This brings you back to earth very fast and also makes you verify code for next time. It makes you a better programmer because who wants to get flamed or criticized every day? Not me.
If you have a thick skin then start a blog about programming. Post something bad and you will get comments; this also will make you a better programmer since you will be more careful later on. I remember when I started my blog and I had a post about DBCC PINTABLE, Hugo Kornelis posted a comment how that was being deprecated and should not be used. I could have easily deleted the post and the comment but I did not, this reminds me that I have to double check before I post if I don’t want to get some comments telling me that I am a n00b. Here is the link to that post
http://sqlservercode.blogspot.com/2005/09/put-tables-into-memory.html
This concludes this series, hopefully you learned something from it and look past the negativity to get something positive out of it(wow if that is not a self-help sentence then nothing is)
Happy New Year
Monday, December 17, 2007
Use XACT_STATE() To Check For Doomed Transactions
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
Sunday, December 16, 2007
EXISTS or COUNT(*)
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
Thursday, December 13, 2007
The Strange Case OF Nulls And Case
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
Wednesday, December 12, 2007
Retrace : A SQL Profiler Trace Swiss Army Knife
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
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)
Monday, December 10, 2007
The Sad State Of Programmers Part 2 : The In Person Interview
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.
Sunday, December 02, 2007
The Sad State Of Programmers Part 1 : The Phone Interview.
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.
Tuesday, November 27, 2007
Integer Math 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/@Val2Result 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
Wednesday, November 14, 2007
Microsoft Buiness 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
Tuesday, November 13, 2007
INTERCEPT In SQL Server 2005
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.
Friday, November 09, 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
Tuesday, November 06, 2007
Return Null If A Value Is A Certain Value
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
"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?
Monday, October 15, 2007
Interview With Kalen Delaney About Inside Microsoft SQL Server 2005 Query Tuning and Optimization
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.
Wednesday, October 10, 2007
SQL Teaser: Guess 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
Tuesday, October 02, 2007
How to find out the recovery model for all databases on SQL Server 2000, 2005 and 2008
On a SQL Server 2005/2008 box you can use the sys.databases view, the sys.databases view returns a column named recovery_model_desc.
On a SQL server 2000 box you will have to use the DATABASEPROPERTYEX function. The 2000 version will also work on 2000 and 2008 (I tested this with the July CTP)
--2005/2008 version
SELECT [name],
recovery_model_desc
FROM sys.databases
--2000/2005/2008 version
SELECT [name],
DATABASEPROPERTYEX([name],'Recovery') AS recovery_model_desc
FROM master..sysdatabases
Monday, October 01, 2007
SQL Teaser: Guess the output
SELECT d.c-d.b/d.a
FROM(SELECT 1,2,5)d(a,b,c)
Thursday, September 27, 2007
Screencast: SQL Server Analysis Services what's in it for me?
SQL Server Analysis Services Home
Microsoft Business Intelligence Home
SQL Server Analysis Services on MSDN
Watch the screencast(WMV)
Friday, September 21, 2007
SQL Teaser PASS Special: Table Size
What will be the outcome of this script?
First we create a table with a total of 6000 bytes
Next we increase col2 from 1000 to 2000 bytes, this will give us a total of 7000 bytes
Finally we add col3 which has 1000 bytes, this will give us a total of 8000 bytes
First run these two statements
--Total size = 6000
CREATE TABLE TestSize (Col1 char(5000),col2 char(1000))
GO
--total size = 7000
ALTER TABLE TestSize
ALTER COLUMN col2 char(2000)
GO
Now what do you think will happen when you run this?
--total size should be 8000 bytes (5000 + 2000 + 1000)
ALTER TABLE TestSize
ADD Col3 char(1000)
GO
Now for bonus points. What book have I been reading.
Hint: the author is at PASS