Ken Henderson passed away this last Sunday. My condolences go out to his family, friends, and coworkers. I owe so much to Ken, if it wasn't for his books I would not be where I am today.
Ken, You will be missed.
Tuesday, January 29, 2008
Saturday, January 26, 2008
SQL Server 2008 To RTM In Q3 2008
Francois Ajenstat on his blog writes that
You can read the whole blog post here: http://blogs.technet.com/dataplatforminsider/archive/2008/01/25/microsoft-sql-server-2008-roadmap-clarification.aspx
"Microsoft is excited to deliver a feature complete CTP during the Heroes Happen Here launch wave and a release candidate (RC) in Q2 calendar year 2008, with final Release to manufacturing (RTM) of SQL Server 2008 expected in Q3."
You can read the whole blog post here: http://blogs.technet.com/dataplatforminsider/archive/2008/01/25/microsoft-sql-server-2008-roadmap-clarification.aspx
Friday, January 25, 2008
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
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
Some Links To Blogs By Some Friends Of Mine
Since I have nothing useful to post today I decided to post some links to some of my friends
The first one is from someone in Belgium, his name is Christiaan Baes and he seems to like ORM tools like NHibernate. His blog is mostly about .NET. I'll forgive the fact that he is born 100 miles south by mistake.
The second one is from Mark Smith and he blogs about ASP.NET, SQL Server, HTML, CSS and other random thoughts. Mark's other site is http://aspnetlibrary.com/
The third one is from Denny Cherry, Denny has worked with shops running hundreds of SQL Servers with over half a billion transactions per second through out the farm.
The fourth one is from Alex Cuse and I don't think Alex himself knows what he is blogging about :-)
The first one is from someone in Belgium, his name is Christiaan Baes and he seems to like ORM tools like NHibernate. His blog is mostly about .NET. I'll forgive the fact that he is born 100 miles south by mistake.
The second one is from Mark Smith and he blogs about ASP.NET, SQL Server, HTML, CSS and other random thoughts. Mark's other site is http://aspnetlibrary.com/
The third one is from Denny Cherry, Denny has worked with shops running hundreds of SQL Servers with over half a billion transactions per second through out the farm.
The fourth one is from Alex Cuse and I don't think Alex himself knows what he is blogging about :-)
Monday, January 21, 2008
SQL Server 2005 Best Practices Analyzer (January 2008) Available For Download
This download is the January 2008 release of SQL Server 2005 Best Practices Analyzer.
Get it here: http://www.microsoft.com/downloads/details.aspx?FamilyId=DA0531E4-E94C-4991-82FA-F0E3FBD05E63&displaylang=en
Get it here: http://www.microsoft.com/downloads/details.aspx?FamilyId=DA0531E4-E94C-4991-82FA-F0E3FBD05E63&displaylang=en
Sunday, January 13, 2008
The last two days have been the worst of my life
The last two days have been the worst of my life; it feels like he combination of a really bad hangover, the flu and a massive toothache. It all started on Wednesday afternoon; my tooth and the gum fell a little sore. Instead of going to the dentist like a normal person I decided to wait because it will go away. Thursday it fell worse and I had to ask for Motrin at work. I did not sleep at all Thursday night, I called my dentist at 6AM but he couldn’t see me until 12:20 PM. I figured he’ll give me a shot, clean out the tooth and I’ll be back at work at 2PM.
Well it turns out a root canal I had done 10 years ago acted up and they have to do a root end resection. My dentist doesn’t do root canals; he referred me to a specialist. The specialist is fully booked and doesn’t have an opening till Tuesday morning.
My dentist prescribed Oxycodone W/APAP (also known as Oxycontin or Roxicodone) and Amoxicillin. That stuff is pretty intense, your pain goes away but you are pretty much useless. The pain is gone but now my right cheek is swollen and I have to throw up every hour or so. I just hope all this ends by Tuesday, the fix the root canal and I can be at my desk by 12PM.
The reason I wrote this is that next time I say “I have no time to floss for five5 minutes I’ll do one minute instead” I can look back at this post and hopefully change my mind
Well it turns out a root canal I had done 10 years ago acted up and they have to do a root end resection. My dentist doesn’t do root canals; he referred me to a specialist. The specialist is fully booked and doesn’t have an opening till Tuesday morning.
My dentist prescribed Oxycodone W/APAP (also known as Oxycontin or Roxicodone) and Amoxicillin. That stuff is pretty intense, your pain goes away but you are pretty much useless. The pain is gone but now my right cheek is swollen and I have to throw up every hour or so. I just hope all this ends by Tuesday, the fix the root canal and I can be at my desk by 12PM.
The reason I wrote this is that next time I say “I have no time to floss for five5 minutes I’ll do one minute instead” I can look back at this post and hopefully change my mind
Thursday, January 10, 2008
Yet Another Date Teaser
It has been a while since my last teaser but here we go
What do you think the following returns?
SELECT CONVERT(datetime,'1/1/1') -CONVERT(datetime,1) + CONVERT(datetime,0)
How about this on SQL Server 2008
SELECT CONVERT(datetime2,'1/1/1'),CONVERT(datetime2,'01/01/01'),CONVERT(datetime2,'0001/01/01')
Now run this on SQL Server 2008
SELECT
ISDATE('1/1/1'),
ISDATE('01/01/01'),
ISDATE('001/01/01'),
ISDATE('0001/01/01')
Now just for fun run these 4 on SQL Server 2008, one of them will fail so run them one by one
SELECT CONVERT(datetime2,'1/1/1')
SELECT CONVERT(datetime2,'01/01/01')
SELECT CONVERT(datetime2,'001/01/01')
SELECT CONVERT(datetime2,'0001/01/01')
Compare the isdate output to the select statement, see the inconsistency?
What do you think the following returns?
SELECT CONVERT(datetime,'1/1/1') -CONVERT(datetime,1) + CONVERT(datetime,0)
How about this on SQL Server 2008
SELECT CONVERT(datetime2,'1/1/1'),CONVERT(datetime2,'01/01/01'),CONVERT(datetime2,'0001/01/01')
Now run this on SQL Server 2008
SELECT
ISDATE('1/1/1'),
ISDATE('01/01/01'),
ISDATE('001/01/01'),
ISDATE('0001/01/01')
Now just for fun run these 4 on SQL Server 2008, one of them will fail so run them one by one
SELECT CONVERT(datetime2,'1/1/1')
SELECT CONVERT(datetime2,'01/01/01')
SELECT CONVERT(datetime2,'001/01/01')
SELECT CONVERT(datetime2,'0001/01/01')
Compare the isdate output to the select statement, see the inconsistency?
Monday, January 07, 2008
Has Anyone Succeeded In Creating A Collision Between NEWID and NEWSEQUENTIALID
SQL Server 2005 introduced a new type of function to create a uniqueidentifier; the NEWSEQUENTIALID(). This new function has been created for performance reasons, each new value is greater than the previous value. In theory this means that the value will be inserted at the end of a page and not in the middle which can cause splits.
Let's run this code to see the difference
CREATE TABLE #TableSeqID (ColumnA uniqueidentifier DEFAULT NEWSEQUENTIALID(),
ColumnB uniqueidentifier DEFAULT NEWID())
INSERT #TableSeqID DEFAULT VALUES
INSERT #TableSeqID DEFAULT VALUES
INSERT #TableSeqID DEFAULT VALUES
INSERT #TableSeqID DEFAULT VALUES
INSERT #TableSeqID DEFAULT VALUES
GO
SELECT * FROM #TableSeqID
Output
-----------
BBF765FE-57BD-DC11-875F-000D5684F8D8 CE51B9E4-1640-47E2-87C6-6ADD46C63A87
BCF765FE-57BD-DC11-875F-000D5684F8D8 CA220BAB-462E-440E-829A-E0037CAF0D1F
BDF765FE-57BD-DC11-875F-000D5684F8D8 01748772-8155-4F83-A58F-CC3253DDD3F3
BEF765FE-57BD-DC11-875F-000D5684F8D8 9C4B2C87-AE33-4432-8310-3BE731179382
BFF765FE-57BD-DC11-875F-000D5684F8D8 1F84B827-F42A-4C47-8A1B-4B672B4402F1
As you can see ColumnB is random (Or at least pseudo-random) while ColumnA is not
Let's say you have a table with a billion rows, this table used NEWID() up till now. What will happen when you change the table to use NEWSEQUENTIALID(), could you get a duplicate?
I tried my best and filled up my 400GB External Seagate drive without success
Have you run into a collision, is it even possible?
Let's run this code to see the difference
CREATE TABLE #TableSeqID (ColumnA uniqueidentifier DEFAULT NEWSEQUENTIALID(),
ColumnB uniqueidentifier DEFAULT NEWID())
INSERT #TableSeqID DEFAULT VALUES
INSERT #TableSeqID DEFAULT VALUES
INSERT #TableSeqID DEFAULT VALUES
INSERT #TableSeqID DEFAULT VALUES
INSERT #TableSeqID DEFAULT VALUES
GO
SELECT * FROM #TableSeqID
Output
-----------
BBF765FE-57BD-DC11-875F-000D5684F8D8 CE51B9E4-1640-47E2-87C6-6ADD46C63A87
BCF765FE-57BD-DC11-875F-000D5684F8D8 CA220BAB-462E-440E-829A-E0037CAF0D1F
BDF765FE-57BD-DC11-875F-000D5684F8D8 01748772-8155-4F83-A58F-CC3253DDD3F3
BEF765FE-57BD-DC11-875F-000D5684F8D8 9C4B2C87-AE33-4432-8310-3BE731179382
BFF765FE-57BD-DC11-875F-000D5684F8D8 1F84B827-F42A-4C47-8A1B-4B672B4402F1
As you can see ColumnB is random (Or at least pseudo-random) while ColumnA is not
Let's say you have a table with a billion rows, this table used NEWID() up till now. What will happen when you change the table to use NEWSEQUENTIALID(), could you get a duplicate?
I tried my best and filled up my 400GB External Seagate drive without success
Have you run into a collision, is it even possible?
Sunday, January 06, 2008
I won't be flying on this sucker
FAA: Boeing's New 787 May Be Vulnerable to Hacker Attack
http://www.wired.com/politics/security/news/2008/01/dreamliner_security
Boeing's new 787 Dreamliner passenger jet may have a serious security vulnerability in its onboard computer networks that could allow passengers to access the plane's control systems, according to the U.S. Federal Aviation Administration.
The computer network in the Dreamliner's passenger compartment, designed to give passengers in-flight internet access, is connected to the plane's control, navigation and communication systems, an FAA report reveals.
http://www.wired.com/politics/security/news/2008/01/dreamliner_security
Saturday, January 05, 2008
The World Is Small, The Risk Of Your Data Being Stolen Is Not!
Remember the How Is Your Sensitive Data Encrypted In The Database? post I wrote a while back? A colleague just informed me that he got a letter from that same datacenter. The letter states that his personal data was on one of those servers which got stolen. I told him that this is the reason we encrypt our data and also why we encrypt outside of the DB. The world is small indeed.
Here is a pic of the letter
Wednesday, January 02, 2008
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'
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'
Top Ten Posts Of 2007
Here is the list of the ten most popular posts for 2007 in terms of pageviews according to Google Analytics
1 The Sysinternals Troubleshooting Utilities have been rolled up into a single Suite of tools
2 Ten SQL Server Functions That You Hardly Use But Should
3 Become a better developer in 6 months
4 You Should Never Use IN In SQL To JOIN With Another Table
5 The Sad State Of Programmers Part 1 : The Phone Interview.
6 This has to be one of the worst planned projects in recent Database history
7 Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
8 How Well Do You Interview And Do You Use Wizard Driven Programming?
9 Make Your Case Sensitive Searches 1000 Times Faster
10 The Sad State Of Programmers Part 2 : The In Person Interview
1 The Sysinternals Troubleshooting Utilities have been rolled up into a single Suite of tools
2 Ten SQL Server Functions That You Hardly Use But Should
3 Become a better developer in 6 months
4 You Should Never Use IN In SQL To JOIN With Another Table
5 The Sad State Of Programmers Part 1 : The Phone Interview.
6 This has to be one of the worst planned projects in recent Database history
7 Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
8 How Well Do You Interview And Do You Use Wizard Driven Programming?
9 Make Your Case Sensitive Searches 1000 Times Faster
10 The Sad State Of Programmers Part 2 : The In Person Interview
Top Ten Posts Of All Time
Here is the list of the ten most popular posts in terms of pageviews since I started collecting this info with google Analytics. I haven't updated this list since April or May
1 The Sysinternals Troubleshooting Utilities have been rolled up into a single Suite of tools
2 Ten SQL Server Functions That You Hardly Use But Should
3 Become a better developer in 6 months
4 SQL Query Optimizations
5 Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
6 You Should Never Use IN In SQL To JOIN With Another Table
7 The Sad State Of Programmers Part 1 : The Phone Interview.
8 Five Ways To Return Values From Stored Procedures
9 This has to be one of the worst planned projects in recent Database history
10 How Well Do You Interview And Do You Use Wizard Driven Programming?
Updated 2008-01-02
1 The Sysinternals Troubleshooting Utilities have been rolled up into a single Suite of tools
2 Ten SQL Server Functions That You Hardly Use But Should
3 Become a better developer in 6 months
4 SQL Query Optimizations
5 Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
6 You Should Never Use IN In SQL To JOIN With Another Table
7 The Sad State Of Programmers Part 1 : The Phone Interview.
8 Five Ways To Return Values From Stored Procedures
9 This has to be one of the worst planned projects in recent Database history
10 How Well Do You Interview And Do You Use Wizard Driven Programming?
Updated 2008-01-02
Tuesday, January 01, 2008
Wow, talking about a rant against Ruby On Rails
Take a look at this post: Rails Is A Ghetto
This is probably one of the longest rants I have ever seen, interesting but be warned NSFW
This is probably one of the longest rants I have ever seen, interesting but be warned NSFW
Subscribe to:
Posts (Atom)