Video: Microsoft SQL Server 2008 Nov CTP - Spatial (Part 1/3)

Head over to Channel 9 and watch the first part of a three part series about the spatial data type in SQL server 2008. Besides video formats there are also mp3 and wma versions in case you just want to listen to it

Watch it here: http://channel9.msdn.com/ShowPost.aspx?PostID=363495#363495

The Most Natural Set Option

SET ANSI_NULL_DFLT_OFF ON

this is what this means, in English....

Set the setting ANSI NULL Default Off to ON

Why not just SET ANSI_NULL_DFLT [ON|OFF]?

This is even worse than SET NOCOUNT ON

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.

EURO 2008 draw

My picks below

Group A: Switzerland, Turkey, Portugal, Czech Republic
1. Portugal, Czech Republic.

Group B: Austria, Poland, Germany, Croatia
1.Croatia, 2. Germany.

Group C: Holland, France, Romania, Italy
1. France, 2. Holland.

Group D: Greece, Russia, Spain, Sweden
1. Spain, 2. Greece.

Screencast: SQL Server 2008 - Table valued parameters

If you have to insert more than one row of data in a Table, you end up executing the insert statement / stored procedure as many times for each row, with Table-valued parameters you can send multiple rows of data to a single TSQL statement or stored procedure without creating temp tables or doing multiple round trips.

Table-valued parameters are a new parameter type in SQL Server 2008 and are declared by using user-defined table types.

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

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

Visual Studio 2008 Training on Channel 9

VS2008 Training Kit: Building Windows Presentation Foundation Applications in Visual Studio 2008 and Expression Blend
This session was presented by Jaime Rodriguez

Come and learn how to build Windows Presentation Foundation (WPF) applications with the help of the new WPF designer in Visual Studio 2008 This session focuses on the construction of a real-world application demonstrating how to get the best out of the designer's capabilities. Learn the basics of building WPF applications in the Visual Studio 2008 WPF designer; have a clear view on when you'll want to work in XAML Code and on the Visual Studio design surface to get your WPF application built; and see how you can use Microsoft Expression Blend in conjunction with Visual Studio 2008 WPF designer.

Watch the screencast here: http://channel9.msdn.com/ShowPost.aspx?PostID=359715#359715


VS2008 Training Kit: What's New in Windows Presentation Foundation 3.5
This session was presented by Kevin Moore

Learn about the work being done in the Orcas release of WPF, including improved 3D with UIElement3D, increased animation smoothness, better cookie and FireFox plug-in support for XBAPs, as well as support for AddInControl, LINQ, ADO.NET and much more!

Download it here: http://channel9.msdn.com/ShowPost.aspx?PostID=359713#359713


VS2008 Training Kit: Introduction to Silverlight
This session was presented by Adam Kinney

Silverlight is a cross platform UI framework for creating compelling Web experiences. Come learn the high-level architecture of Silverlight and the tools that are available to build Silverlight applications.

Download it here: http://channel9.msdn.com/ShowPost.aspx?PostID=359712#359712


VS2008 Training Kit: Building Web Applications with Visual Studio 2008
This session was presented by Ryan Dunn

This session covers the new design and development features in Visual Studio 2008 for creating rich web applications. Topics include the new CSS and HTML design tools, much improved Javascript Intellisense and debugging support, and the new ASP.NET 3.5 controls supporting LINQ and AJAX.

Download it here: http://channel9.msdn.com/ShowPost.aspx?PostID=359711#359711


VS2008 Training Kit: Introduction to ASP.NET AJAX
This session was presented by Steve Marx

In this session, you'll learn how to use ASP.NET AJAX to build a richer, more responsive user experience. Topics include partial rendering, web services, structured scripting, and integration with Silverlight.


Download it here: http://channel9.msdn.com/ShowPost.aspx?PostID=359706#359706

.NET Framework 3.5 Common Namespaces and Types Poster Available For Download

The .NET Framework 3.5 Common Namespaces and Types Poster is downloadable as XPS or PDF format. There is also an XPS format file which prints over 16 letter or A4 pages for easy printing. Some assembly is required if you choose this print method.

Get it here: http://www.microsoft.com/downloads/details.aspx?FamilyID=7b645f3a-6d22-4548-a0d8-c2a27e1917f8&DisplayLang=en

World Cup Soccer 2010 Qualification - UEFA draw, England And Croatia In The Same Group Again!

Group 1:
Portugal, Sweden, Denmark, Hungary, Albania, Malta

Group 2:
Greece, Israel, Switzerland, Moldova, Latvia, Luxembourg

Group 3:
Czech Rep., Poland, Northern Ireland, Slovakia, Slovenia, San
Marino

Group 4:
Germany, Russia, Finland, Wales, Azerbaijan, Liechtenstein

Group 5:
Spain, Turkey, Belgium, Bosnia-Herzegovina, Armenia, Estonia

Group 6:
Croatia, England, Ukraine, Belarus, Kazakhstan, Andorra

Group 7:
France, Romania, Serbia, Lithuania, Austria, Faroe Islands

Group 8:
Italy, Bulgaria, Ireland, Cyprus, Georgia, Montenegro

Group 9:
Netherlands, Scotland, Norway, Macedonia FYR, Iceland

Croatia and England again in the same group :-)
Group 7 doesn't look easy either

Whitepaper on Malware to Attack Databases

Brian Kelly on his blog mentiones a whitepaper by Cesar Cerrudo: Data0: Next generation malware for stealing databases. This whitepaper describes how malware could be crafted to steal information out of databases.

The attack will use the following techniques:

• Discovery
  
• Exploitation
  
• Escalate Privileges (if necessary)
  
• Cover Tracks

Print it out and read it while you wait in line on Black Friday

Just For My English Friends

England and Croatia in the same group again for World Cup 2010 qualifications:
Group 6: Croatia, England, Ukraine, Belarus, Kazakhstan, Andorra

Thanksgiving SQL Teaser COUNT

Here is a small simple Thanksgiving teaser. What do you think will the result be of the select count query?

USE tempdb
GO

CREATE TABLE Customer (CustomerID INT PRIMARY KEY)
INSERT Customer VALUES (1)
INSERT Customer VALUES (2)
INSERT Customer VALUES (3)
INSERT Customer VALUES (4)

SELECT COUNT(*) Customer

Visual Studio 2005 Support for SQL Server 2008, Community Technology Preview

This CTP resolves a problem that when you try to open a database connection to SQL Server 2008 by using Visual Studio 2005 design tools, you may receive the following error message:
“This server version is not supported. Only servers up to Microsoft SQL Server 2005 are supported.”
This CTP addresses this issue, and enables the following Visual Studio functionality for SQL Server 2008 CTP5 :

• Server Explorer successfully connects to SQL Server 2008, and database objects such as stored procedures and table data can be viewed and edited. Note that table schemas still cannot be viewed or edited in this release.
  
• SQL CLR projects that target SQL Server 2008 CTP5 can be created and deployed to the server.
  
• T-SQL and SQL CLR debugging are now enabled for SQL Server 2008 CTP5.
  Data binding features in Client and Web Projects are enabled.

This CTP does not support the following features for SQL Server 2008 Nov CTP:

• Creating and editing table schemas in Table Designer or Database Diagrams. The table designer feature in SQL Server Management Studio 2008 can be used to edit table schemas in SQL Server 2008 CTP5.

Download it here: http://www.microsoft.com/downloads/details.aspx?FamilyID=e1109aef-1aa2-408d-aa0f-9df094f993bf&displaylang=en

Surface Area Configuration Tool Is Discontinued, SQL-DMO Removed from Microsoft SQL Server 2008 Express

Just reading the readme file in SQL Server 2008 November CTP. I noticed that in addition to SQL Server Notification Services the following two things are also being removed.

SQL-DMO Removed from Microsoft SQL Server 2008 Express
Surface Area Configuration Tool Is Discontinued

The Surface Area Configuration Tool is discontinued for SQL Server 2008. The following table shows what you can use to configure settings, options, and component features in the November CTP.

Protocols, and connection and startup options
Use SQL Server Configuration Manager.
Database Engine features
Use Declarative Management Framework.
SSAS features
Use the property settings in SQL Server Management Studio.
SSRS features
Edit the RSReportServer.config configuration file.

Microsoft SQL Server 2008 CTP November 2007 Available For Download

Microsoft SQL Server 2008 CTP, November 2007 is available for download from the connect site.

There are several formats available.
DVD image files:
X86 DVD Image
X64 DVD Image
IA64 DVD Image

Self-extracting executables:
X86 Executable
X64 Executable
IA64 Executable
Express 2008 Executable

Get it here: http://www.microsoft.com/downloads/details.aspx?FamilyId=3BF4C5CA-B905-4EBC-8901-1D4C1D1DA884&displaylang=en

Visual Studio 2008 RTM Available On MSDN

Yes it is true, Visual Studio 2008 RTM is available on MSDN. But be warned the site is slower than ever.



Okay I downloaded it and installed it.

Visual Studio 2008 Available For Download Early Next Week

MSDN Subscriptions WebLog Has a post stating that Visual Studio 2008 will be available early next week. From the site:

Visual Studio 2008 is anticipated out early next week, with availability for Subscribers. Check out the "Top Subscriber Downloads" area on http://msdn2.microsoft.com/subscriptions for VS 2008 downloads.

Microsoft Buiness Intelligence Screen Casts

Channel 9 will be publishing a bunch of Business 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

INTERCEPT In SQL Server 2005

I was writing a query and managed to mistype INTERSECT, I typed INTERCEPT and to my surprise the query ran, it returned 2 result set just as if INTERCEPT wasn't there at all
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.

How Is Your Sensitive Data Encrypted In The Database?

Do you store encrypted data? If you do then how is it encrypted? Do you use the built in capabilities of SQL Server 2005/2008. If you answered yes to the last question then here is another question. What would happen if someone stole the hard drive or even the whole database server? Could they decrypt that data easily with the stored procedures which you have written? Do these store procedures use the DecryptByPassphrase function?

So you probably think that I am crazy and no one would ever steal a database server. Wrong! C I Host a Chicago-based co-location got robbed 4 times since 2005. One company lost 20 servers in the latest heist. You can read more details about that here: http://www.theregister.co.uk/2007/11/02/chicaco_datacenter_breaches/

Without going into too much detail, this is what we are doing. Our data is encrypted by a corporate crypto tool which can only be accessed from within the DMZ. Keys are created for specific machines; these keys can easily be revoked at any time. Even if you would somehow steal our web and database server you would still be out of luck because of that. The data is encrypted by the tool and stored encrypted in the DB.

Identity theft will cost you in the future.
The Identity Theft Enforcement and Restitution Act of 2007 has been introduced and was scheduled for debate on November 1st; the Senate and the House still have to vote on it. This is a bill to amend title 18, United States Code, to enable increased federal prosecution of identity theft crimes and to allow for restitution to victims of identity theft.

Follow the developments here:
S. 2168: Identity Theft Enforcement and Restitution Act of 2007