I tried to keep it a secret but it is all over the internet: I am a SQL Server MVP

So all over the internet is exaggerated,I became a SQL Server MVP today and was not going to do a blog post about it. However some other people had other ideas

Congratulations, Denis!
SQL Server MVP - Denis Gobo

and even an announcement on lessthandot

My profile is here: https://mvp.support.microsoft.com/profile=BCCF7416-DA4E-4D73-83E2-65FD61BAB16D

Stay tuned, I will have an interview with Jamie Thomson tomorrow

Working On SQL Admin Hacks, Tips and Tricks

I haven't posted for a while because I have been working on SQL Admin Hacks, Tips and Tricks lately. it is still a work in progress but below is what is on the wiki currently. It is not yet categorized but we will do that once we get more of these hacks done. To see what it will look like when it is done take a look at the SQL Server Programming Hacks

Can you think of any admin stuff you would like to see? This is what we have right now

Find Primary Keys and Columns Used in SQL Server
Get The Domain Name Of Your SQL Server Machine With T-SQL
Grant Execute/SELECT Permissions For All User Defined Functions To A User
Grant Execute Permissions For All Stored Procedures To A User
Kill All Active Connections To A Database
SQL Server 2008: When Was The Server Last Started?
Check If Auto Update Statistics Is Enabled By Using DATABASEPROPERTY
Three Way To List All Databases On Your Server
Generate A List Of Object Types By Using OBJECTPROPERTY
How to find all the tables and views in a database
Find Out Server Roles For a SQL Server Login
Which Service Pack Is Installed On My SQL Server
Test SQL Server Login Permissions With SETUSER
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2000
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
Compare Tables With Tablediff
Find All Tables Without Triggers In SQL Server
Find All Tables With Triggers In SQL Server
Create Stored Procedures That Run At SQL Server Startup
Cycle The SQL Server Error Log
How to read sql server error messages
Use OBJECT_DEFINITION To Track SQL Server Stored Procedure Changes
SQL Compare Without The Price Tag
How To Get The Database Name For The Current User Process
How To Find Out Which Columns Have Defaults And What Those Default Values Are
Fixing Cannot add, update, or delete a job that originated from an MSX Server Error after renaming a server

Interview With Erland Sommarskog About SQL Server and Transact SQL

I have interviewed Erland Sommarskog, you can find that interview here: Interview With Erland Sommarskog About SQL Server and Transact SQL

Enjoy

How to log when a function is called?

This question came up today and here is one way of doing it. It requires running xp_cmdshell so this is probably not such a good idea.
The problem with functions is that you cannot just insert into any table. INSERT, UPDATE, and DELETE statements modifying table variables local to the function.
EXECUTE statements calling an extended stored procedures are allowed.
So with this in mind we know that we can call xp_cmdshell, from xp_cmdshell we can use osql
Let's take a look
We will be using tempdb


--Create the table
USE tempdb
go
CREATE TABLE LogMeNow (SomeValue varchar(50), SomeDate datetime default getdate())
go

--Here is the proc
CREATE PROC prLog
@SomeValue varchar(50)
AS
INSERT LogMeNow (SomeValue) VALUES(@SomeValue)
go

--And here is the function
CREATE FUNCTION fnBla(@id int)
RETURNS int
AS
BEGIN
DECLARE @SQL varchar(500)
SELECT @SQL = 'osql -S' +@@servername +' -E -q "exec tempdb..prLog ''fnBla''"'
EXEC master..xp_cmdshell @SQL
RETURN @id
END

Now call the function a couple of times

SELECT dbo.fnBla(1)
SELECT dbo.fnBla(2)
SELECT dbo.fnBla(4)

And look inside the table

SELECT * FROM LogMeNow

What if you were to run this?

SELECT dbo.fnBla(4),* FROM sys.sysobjects

See the problem? The function will be called for every row, if you have a big table this can be problematic!!!!!!!!

I tested this on SQL 2000 and on SQL 2005(including a named instance). So there you have it, this is one way. does it smell kludgy and do I feel somewhat dirty now? yes it does indeed :-(

How to rename a column in a SQL Server table without using the designer

If you have a table and you want to rename a column without using the designer, how can you do that?

First create this table

CREATE TABLE TestColumnChange(id int)
INSERT TestColumnChange VALUES(1)


SELECT * FROM TestColumnChange

As you can see the select statement returns id as the column name. you can use ALTER table ALTER Column to change the dataype of a column but not the name.

Here is what we will do, execute the statement below

EXEC sp_rename 'TestColumnChange.[id]', 'value', 'COLUMN'


Now do the select, you will see that the column name has changed

SELECT * FROM TestColumnChange

That is it, very simple

Solutions for Common T-SQL Problems Wiki Launched

Volunteer Moderators and Answerers who support the Microsoft MSDN SQL Server Forums have launched a Wiki with Solutions for Common T-SQL Problems. Check it out here: http://code.msdn.microsoft.com/SQLExamples

Use IDENT_CURRENT() to return the last identity value generated in all tables in a SQL Server Database

This is how you return all the tables with their last generated identity values in a SQL Server Database

SELECT IDENT_CURRENT(table_name),*
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'base table'
AND OBJECTPROPERTY(OBJECT_ID(table_name),'TableHasIdentity')=1

Not An April Fool’s Joke: SQL Server 2000 Mainstream Support Ends In A Week

April 1st 2008 is the day on which support for SQL Server 2000 ends (and it is also my birthday)


SQL 2000 SP4 - currently supported; support ends on either 12 months after SP5 is released or on 4/8/2008 whichever comes first. Extended support under which you get only break-fix assistance via Premier ends on 4/9/2013. I am not aware of release date for SP5 so far.

SQL 2005 SP2 - currently supported; support ends on either 12 months after SP3 is released or on 4/12/2011 whichever comes first. Extended support under which you get only break-fix assistance via Premier ends on 4/13/2016


More details can be found here: http://blogs.msdn.com/sqlreleaseservices/archive/2007/07/12/sql-server-support-lifetimes-customer-actions.aspx

And here:
http://support.microsoft.com/lifecycle/?p1=2852

Tip: Find all The Rows Where Any Of The Columns Is Null Or Zero Or Both

This question is asked every now and then so I decided to do a little blog post. How can you quickly without writing a bunch of OR statements determince if any columns have a NULL value, a value of 0 or if the value is 0 or NULL.
To test for NULL is very easy, you just concatenate the columns since NULL + anything else is always NULL. Okay that also depends on a setting.

Run this

SET CONCAT_NULL_YIELDS_NULL ON
SELECT NULL + '1' --NULL

SET CONCAT_NULL_YIELDS_NULL OFF
SELECT NULL + '1' --1

As you can see if CONCAT_NULL_YIELDS_NULL is OFF then the result is 1

Now take a look at this


SET CONCAT_NULL_YIELDS_NULL ON
SELECT NULL + 1 --NULL



SET CONCAT_NULL_YIELDS_NULL OFF
SELECT NULL + 1 --NULL


So with numeric values it behaves differently. Either way by default CONCAT_NULL_YIELDS_NULL is set to on
To test for NULLS or zeroes you use NULLIF
To test for zeros you can combine COALESCE and NULLIF

Here is the code which shows all of that

CREATE TABLE #test(column1 int,column2 varchar(4),column3 float)

INSERT #test VALUES(2,'2',2)
INSERT #test VALUES(0,'1',0)
INSERT #test VALUES(null,'1',0)
INSERT #test VALUES(1,null,0)
INSERT #test VALUES(0,'1',null)
INSERT #test VALUES(null,null,null)



--Any column is Null
SELECT * FROM #test
WHERE column1 + column2+column3 is null

Output
------------
NULL 1 0.0
1 NULL 0.0
0 1 NULL
NULL NULL NULL



--Any column is Null or zero
SELECT * FROM #test
WHERE NULLIF(column1,0) + NULLIF(column2,0)+NULLIF(column3,0) is null

Output
-------------------
0 1 0.0
NULL 1 0.0
1 NULL 0.0
0 1 NULL
NULL NULL NULL




--Any column is zero
SELECT * FROM #test
WHERE NULLIF(COALESCE(column1,1),0) +
NULLIF(COALESCE(column2,1),0)+
NULLIF(COALESCE(column3,1),0) is null

Output
-------------------
0 1 0.0
NULL 1 0.0
1 NULL 0.0
0 1 NULL


DROP TABLE #test

Use the *1 trick to do math with two varchars, this prevents the Invalid operator for data type. Operator equals subtract,type equals varchar message

Someone had code like this on the tek-tips forum

DECLARE @v varchar(24)
SELECT @v ='06029202400250029'

SELECT RIGHT(@v,4) -SUBSTRING(@v,10,4)

If you run this code, you will get the following message
Server: Msg 403, Level 16, State 1, Line 4
Invalid operator for data type. Operator equals subtract, type equals varchar.

Instead of casting to integers you can also use this little trick. You basically multiply one of the values by 1

DECLARE @v varchar(24)
SELECT @v ='06029202400250029'

SELECT RIGHT(@v,4) *1 -SUBSTRING(@v,10,4)



Another example. This doesn't work
SELECT '2' - '1'

This does work
SELECT '2' * 1 - '1'

A year in review, The 21 + 1 best blog posts on SQLBlog

These are the best posts according to me, it might have been the content or it might have been the comments. Either way here are the 21 + 1 posts I enjoyed the most. But wait the year is not over yet. Doesn't matter no one will write anything good till next year anyway (prove me wrong and I WILL update this post).

The first post is really from last year, I mean really, who writes a post on '2006-12-31T13:49:00.000' (yes that is ISO8601)? Since I did not see the post until 2007 I have included it in the list. I tried not to include more than 2 or 3 posts per blogger, I would have included all Hugo's NULL posts otherwise. What is with the 21 + 1 title? The +1 one is my post about using bitwise logic, it is the last link

Below is the list, let me know if I missed anything you really enjoyed and Happy New Year



The Beatles versus the Stones
How Many Data Files Should I Create for a User Database?
[Editorial] Get rid of the bad apples in IT?
NULL - The database's black hole
Performance Impact of Procedure Calls without Owner Qualification -- SQL Server 2000
Performance Impact of Procedure Calls without Owner Qualification
Did You Know? Run a batch multiple times
Want to Control the Procedure Cache?
Is statistics over non-indexed columns updated by index rebuild?
Xp_cmdshell and permissions
The Perils of Hyperthreading for SQL Server
5 Things every DBA should know like the back of their hand...
Filegroups Part I - Dividing Data for Performance
Did You Know? SP2 does NOT limit the amount of plan cache you can have
Sorted views...
2008: Initializing Table Data with Row Constructors
2008: Row Constructor or Table-Valued Parameter
Anti-Patterns and Malpractices, Volume 1: Tumbling Data
10 lessons from 35K tps
What’s wrong with SELECT * ?
Backup compression in SQL Server 2008


This one is mine


Speed Up Performance And Slash Your Table Size By 90% By Using Bitwise Logic

Red Gate SQL Data Generator 1.0 Beta Available For Download

Red Gate have released Red Gate SQL Data Generator 1.0 Beta

From the site:

This tool is aimed at generating test data for SQL Server databases. It can either generate data from scratch or import from existing sources of data.(Like SQL tables or CSV files.)
Features:
- Full SQL Server 2000 and 2005 support
- All data types supported except CLR types
- Pre & Post Scripts execution
- Command-line access version
- Import data from CSV or SQL tables
- Customizable generator settings that allow configuring the amount of nulls, unique values, minimum and maximum values, etc..
- Diverse range of inbuilt generators
The product is not yet complete, and we are looking for user feedback about what features and or functionality you would like in the final product.
*The beta version is set to work until 11 Apr 2008. "

This is a free open beta with the application due to expire on 11 April 2008 with a planned final released sometime before then.

What we really want now is for everyone to use the software and provide us with feedback for the final version. Please let us know on the forums what you like and don't like about the software and what we could do to solve all of your data generation problems!

Visit http://www.red-gate.com/MessageBoard/viewtopic.php?t=6140 for the announcement and download URL

Forums: http://www.red-gate.com/MessageBoard/viewforum.php?f=76

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

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

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)

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.

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

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

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

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

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

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

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

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

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

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

Integer Math In SQL Server

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

SELECT 3/2

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


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

SELECT @Val1/@Val2

Result 1

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

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

Result 1.50000000000000

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

Return Null If A Value Is A Certain Value

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

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


SELECT NULLIF(@1,'D')


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

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

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


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

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


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

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

And this is how you test for a range.

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

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

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

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