SQL Server Code,Tips and Tricks, Performance Tuning

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?

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?

Sunday, January 06, 2008

I won't be flying on this sucker

FAA: Boeing's New 787 May Be Vulnerable to Hacker Attack

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'

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

You know What I Am Trying To Say :-)

Friday, December 28, 2007

The Best Geek Story Ever Told

Go to DotNetRocks, download show number 300, fast forward to 57:45 and start listening. It is the funniest geek story I have ever heard. You will not regret it

Here is the link to the show: http://www.dotnetrocks.com/default.aspx?showNum=300

BTW the whole podcast is very interesting in terms of computer history, I highly recommend listening to it.

Thursday, December 27, 2007

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

The Sad State Of Programmers Part 3: General Tips

This is the final part of this series. You can find the first two parts here

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

Wednesday, December 26, 2007

Alien VS Predator picture

This is priceless

Alien VS Predator picture

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

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

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

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

this is my account number.
Bank Simpanan Nasional
0210029816898886

via western union

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

Tuesday, December 25, 2007

Screencast: SQL Server 2008 Change data capture

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

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

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

Monday, December 24, 2007

I am stuffed

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

SharePoint and SQL Server Reporting Services Posts

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

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

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

Kids Might Become Overwhelmed Tomorrow

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

Wednesday, December 19, 2007

Stop The Press, Internet Explorer 8 Passes ACID 2 Test

Stop the press,

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

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

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

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