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

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

E-Learning: What's New in Microsoft SQL Server 2008

Microsoft leaning has made available a bunch of free SQL server 2008 lessons

In this 3 hour online collection, IT Professionals will learn about the new features in SQL Server 2008. Topics covered within these clinics include:
What's New in SQL Server 2008 for Enterprise Data Platform
What's New in SQL Server 2008 for Business Intelligence
What's New in SQL Server 2008 for Database Development Student

Below are the links to the three lessons:

Clinic 6188: What's New in Microsoft SQL Server 2008 for Enterprise Data Platform
Clinic 6189: What's New in Microsoft SQL Server 2008 for Business Intelligence
Clinic 6190: What's New in Microsoft SQL Server 2008 for Database Development

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

Christmas Is Coming Early This Year

Note from my manager:

"All,

It looks like we have some book money left for this year that we can spend.
Please select 2 books each that you have wanted and send the titles to me and I’ll get them on the list."

Very nice, I picked the following two books below.
The Art of SQL
SQL Server 2005 Practical Troubleshooting: The Database Engine

What would you have picked?

Microsoft Commits to November Release Date for Visual Studio 2008 and the .NET Framework 3.5

Today, during the keynote address at Microsoft TechEd Developers 2007, S. “Soma” Somasegar, corporate vice president of the Developer Division at Microsoft Corp., announced that Microsoft will release Visual Studio 2008 and the .NET Framework 3.5 by the end of November 2007. Visual Studio 2008 and the .NET Framework 3.5 enable developers at all levels to rapidly create connected applications that offer compelling user experiences for Windows Vista, Windows Server 2008, the 2007 Microsoft Office system, mobile devices and the Web. Soma also unveiled plans to open new opportunities for Visual Studio partners, as well as to deliver new tools and resources for developers, including a first Community Technology Preview (CTP) of the Microsoft Sync Framework and new capabilities for Popfly Explorer.

“The highly social and visual nature of the Web has fundamentally changed what users expect from all applications they interact with, regardless of whether it’s on a customer-facing Web site or Windows rich client application, or a desktop business application built using Microsoft Office,” said Somasegar. “Traditionally, organizations have been hard pressed to deliver the richer, more connected applications and services they need to boost productivity, drive revenue and stay ahead of the competition. With Visual Studio 2008 and the .NET Framework 3.5, it is easy for developers to use the skills they already have to build compelling applications that take advantage of the latest platforms.”

FWBS Ltd., Xerox Corp., Dell Inc. and K2 are just a few of the early adopter customers that are already experiencing the benefits of these releases. FWBS used Visual Studio, the .NET Framework and the Microsoft Office system to build an Office Business Application (OBA) for the law field. The application enables users to work within Microsoft Office — the tools they use every day — while also dramatically improving productivity and helping users respond quickly to changing business needs.

Xerox has also had early success developing applications with the new tools. “We’ve already seen significant advantages from using Visual Studio Team System 2008 and .NET Framework 3.5. With the first application we built, we easily saved 50 percent of the time and money it would have taken to create the same application with other tools,” said Eugene Shustef, feature design lead, Global Technology, Xerox. “That’s more than a savings to IT — it delivers a huge time-to-market advantage because it put the tool into the hands of our analysts six months sooner than they would have had it otherwise.”

Creating New Opportunities for Partners

Microsoft also announced plans to make additional investments in the Visual Studio partner ecosystem. In response to partner feedback and in order to provide better support for interoperability with other developer tools and cross-platform scenarios, Microsoft is today announcing plans to change licensing terms, no longer limiting partners to building solutions on top of Visual Studio for Windows and other Microsoft platforms only. This licensing change will be effective for the release of Visual Studio 2008 and the Visual Studio 2008 SDK.

“Integrating dynaTrace’s cross-platform application performance management and diagnostics product with Visual Studio has opened up additional commercial opportunities for our business and delivered a compelling solution for our customers. .NET and Visual Studio is a strategic platform for our business, and Microsoft’s additional investments in the partner ecosystem make it even more compelling,” said Klaus Fellner, senior director of product marketing at dynaTrace. “We’re looking forward to taking advantage of the new technology available with the launch of Visual Studio 2008 and the partner benefits available through the Visual Studio Industry Partner (VSIP) program.”

In addition, Microsoft announced plans to create a shared source licensing program for Premier-level partners in the VSIP program. The program will provide these partners with the ability to view Visual Studio IDE source code for debugging purposes, and simplify the process of integrating their products with Visual Studio 2008.

Tools for Today and Tomorrow

Microsoft also announced a number of additional resources for developers of all skill sets, enabling them to make the most out of their Microsoft tools investments to build great applications on the latest platforms:

• The first CTP of the Microsoft Sync Framework demonstrates Microsoft’s ongoing investments in synchronization and builds on the synchronization functionality available in Visual Studio 2008. With Visual Studio 2008, developers can rapidly take advantage of offline synchronization capabilities to sync-enable applications and services easily with rich designer support. The Microsoft Sync Framework extends the support featured in Visual Studio 2008 to also include offline and peer-to-peer collaboration using any protocol for any data type, and any data store. This is part of Microsoft’s long-term commitment to providing synchronization for partners and independent software vendors that can embed the Sync Framework into their applications easily to create rich sync-enabled ecosystems that allow any type of data to follow their customers wherever they go.



• A new release of Popfly Explorer will add new Web tools that provide Web developers and Popfly users an easy way to add Silverlight gadgets built in Popfly to their Web pages, as well as publish HTML Web pages directly to Popfly.


These latest releases are part of the broader Microsoft Application Platform, a portfolio of technology capabilities and core products that help organizations develop, deploy and manage applications and IT infrastructure. They also mark another major milestone leading up to the global launch of Windows Server 2008, Visual Studio 2008 and SQL Server 2008 on Feb. 27, 2008, in Los Angeles.

Product Information and Availability

Visual Studio 2008 and the .NET Framework 3.5 will be available by the end of November 2007. The .NET Framework 3.5 will also be available to end users via a free, optional download from Microsoft Update. A CTP of Microsoft Sync Framework is available today at http://msdn.microsoft.com/sync. Popfly Explorer is a hosted development environment available today at http://www.popfly.com. More information about all of these releases is available at http://www.msdn.microsoft.com/vstudio.