A community of more than 1,500,000 database professionals and growing

Can a Table Really Have a Clustered Index?

Hakim Ali from SQLServerCentral.com

A table doesn't so much "have" a clustered index as a table "is" a clustered index. More »

Rudolph The Red-Nosed Tester and Other Festive Arrangements

Additional Articles from SimpleTalk

A Christmas poem and a contest from Simple Talk. More »

Making the Case for a SQL Server Platform Refresh

Additional Articles from SimpleTalk

With the release of Windows Server 2012, SQL Server 2012, and the new generation of Sandy Bridge Xeon processors, your organization is likely to get many tangible benefits from upgrading your current database infrastructure with a complete platform refresh. More »

From the SQLServerCentral Blogs - On the Sixth Day…

Jason Brimhall from SQLServerCentral.com

What better way to kick off the sixth day of pre-Christmas than with six slices of foie-gras?  No animals have... More »

This editorial was originally published on Mar 3, 2008. It is being re-run as Steve is traveling.

Years ago I read Being Digital by Nicholas Negroponte and while I enjoyed the book, there was one very interesting story in there. Dr. Negroponte was crossing the border from the US to Canada and customs stopped him, checked his computer, and asked what the value was. He responded with "$2,000,000", which is a lot of money. The customs person seemed taken aback and had expected some sort of value closer to $2000-5000. He responded that the bits inside, meaning his writings and other data, were way more valuable than the hardware.

That's how I feel about my laptop and why I love my 1GB and 2GB flash drives. I rarely travel, but I back up my documents, which are mostly text documents, before I go and keep the flash drive separate from my laptop in case of problems. I can always buy a new laptop if I need it, but I can't easily replace the data.

Some of you might have seen this article where a woman is suing Best Buy for US$54M because they lied about her stolen laptop and deceived her. Now I think this is an outrageous amount and it's like the woman that received millions from McDonalds for being burned by their coffee. both ladies deserve compensation, but it should be in line with what happened. You can make the point without suing for an outrageous amount. Even $1M would make the point, and that would be too high. Sue them for $100,001 if you want attention. That might be more in line with the cover up that occurred.

This really brings to mind two different things to me. First, keep good backups of your important data. I try to organize my stuff into a few areas and then get that to fit onto media that I can physically separate from my first machine. My wife used to go so far as to keep a 2nd laptop hard drive in her suitcase that contained copies of everything. That way if her laptop was stolen or damaged, she should plug the second drive in another laptop and be off and running.

It's easy to think of the physical device as being valuable, but I think for most of us the digital information inside is way more valuable than the hardware. And I think this is a trend that will continue over time, with the bits value dwarfing the electronics.

And if you haven't backed up your data recently, you might want to stop and make a quick copy of the important stuff.

Steve Jones

The Voice of the DBA Podcasts

The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.

Overall RSS Feed: or now on iTunes!

• Windows Media Podcast - 39.5MB WMV
• iPod Video Podcast - 31.1MB MP4
• MP3 Audio Podcast - 6.4MB

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.

Steve Jones from SQLServerCentral.com

» Join the debate, and respond to today's editorial on the forums

» To submit an article, rant or editorial, log in to the Contribution Center

Today's Question:

You need to include a division in a view, and you want to make sure that, no matter what the input, the view will never result in a division by zero error. Which of the following scripts achieve that? (Choose 2)

-- View 1
CREATE VIEW dbo.View1
AS
SELECT KeyColumn, Dividend, Divisor,
       CASE WHEN Divisor <> 0 THEN Dividend / Divisor END AS Division
FROM   dbo.MyTable;
go

-- View 2
CREATE VIEW dbo.View2
AS
SELECT KeyColumn, Dividend, Divisor,
       NULLIF(Dividend / Divisor, 0) AS Division
FROM   dbo.MyTable;
go

-- View 3
CREATE VIEW dbo.View3
AS
SELECT KeyColumn, Dividend, Divisor,
       IF (Divisor <> 0) THEN Dividend / Divisor AS Divisor
FROM   dbo.MyTable;
go

-- View 4
CREATE VIEW dbo.View4
AS
SELECT KeyColumn, Dividend, Divisor,
       Dividend / CASE WHEN Divisor <> 0 THEN Divisor END AS Division
FROM   dbo.MyTable;
go

-- View 5
CREATE VIEW dbo.View5
AS
SELECT KeyColumn, Dividend, Divisor,
       Dividend / NULLIF(Divisor, 0) AS Division
FROM   dbo.MyTable;
go

-- View 6
CREATE VIEW dbo.View6
AS
SELECT KeyColumn, Dividend, Divisor,
       Dividend / IF (Divisor <> 0) THEN Divisor AS Divisor
FROM   dbo.MyTable;
go

By Hugo Kornelis

Think you know the answer? Click here, and find out if you are right.

This question is worth 1 point in this category: Views. We keep track of your score to give you bragging rights against your peers.

We'd love to give you credit for your own question and answer. To submit a QOD, simply log in to Contribution Center.

Microsoft SQL Server 2012 Master Data Services

Harness your master data and grow revenue while reducing administrative costs. Thoroughly revised to cover the latest MDS features, Microsoft SQL Server 2012 Master Data Services, Second Edition shows how to implement and manage a centralized, customer-focused MDS framework. See how to accurately model business processes, load and cleanse data, enforce business rules, eliminate redundancies, and publish data to external systems. Security, SOA and Web services, and legacy data integration are also covered in this practical guide.

Get your copy from Amazon today.

I have a table named #testAggregates

with column col1 of datatype int. Below are the values in the table:

What are the results from these individual select queries?

SELECT SUM(distinct col1) as sum_col1
     , COUNT(DISTINCT col1) as cnt_col1
  FROM #testAggregates

SELECT SUM(distinct col1) as sum_col1
     , COUNT(DISTINCT col1) as cnt_col1
 FROM #testAggregates
 WHERE col1 IS NULL

SELECT ISNULL(SUM(NULL),0) as sum_col1
     , ISNULL(COUNT(NULL),0) as cnt_col1
 FROM #testAggregates

By P. Praveena

Answer: (3,3) , (NULL,0) , Error

Explanation: Aggregate functions do not take a null into account for calculations. When a null is directly given as input to an aggregate function, it gives an error.

Ref: No reference has been found, but apparently this is the way things work in SQL Server 2008 R2.

» Discuss this question and answer on the forums

SQL Wait Stats Joes 2 Pros

You learn a lot when thrown into a crisis situation, where everyone is looking to you for answers. The system is down, the CEO is pleading to you for answers as the each minute cuts the company’s bottom line. When you are in a hurry, you need some new ideas right away. This book is your helpful tool. When used correctly, you can determine solutions and even related solutions to simple (yet lesser known) areas of SQL Server. Learn what causes SQL tasks to wait or site in queues. Even if your system is not in trouble right now, you can use what you will learn in this book to view the history of wait statistics.

Pick up your copy of this great book from Amazon today.

Show Table, Index and Image/Text size.

Eder Dias

This is a simple script that show size from a table and yours related objects. More »

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2005 : T-SQL (SS2K5)

Find out the SQL Server restart date/time with TSQL - Hi, I am just wondering if there is way to figure out when SQL Server was last restarted using TSQL? Thanks.

SQL Server 2008 : SQL Server 2008 - General

big table insert advice - Hi I need to refresh data in a table from another table. It has about ~14 million records that need...

SQL Server Error 22007 - Dear Gurus, I am facing SQL server error in Production your valuable input is required urgently. please see attached sheet. Thanks Adeel Imtiaz

TLOG GROWING VERY LARGE - Hi, I have implemented mirroring (high safety without automatic failover) and replication (snapshot replication) simaltaneously on the same database. But my...

PPTR - is it possible to create a PPTR replication in one server(node). Both publisher and subscriber in one SQL 2008 server? Thanks.

Unwanted parameter truncation - I'm about ready to blow my brains out. I just spent almost a day chasing a weird error in a...

SQL 2008 CLustering - Team, I need to set-up clustering and need your thoughts and ideas whether i should have my primay server on...

SQL Server 2008 : T-SQL (SS2K8)

Something fissy about DELETE - I create a table as [code="sql"]CREATE TABLE DEMO (A INT)[/code] Then i deleted it as follow [code="sql"]DELETE DEMO[/code] I tried to again recreate...

select where searchvalues are in list - Hello all, I am trying to select row from a table where the column contains values from a list. I tried...

Need help in calculating the RUNTIME of my Job's ETL - Hi All, I have 58 sequentially run jobs as part of our Database ETL. So, I took the start time...

help!!! calculate average rolling 4 last weeks - Hi All, I need your help. Database: SQL SERVER 2008R2 I want to calculte for a year and a week the average of...

ORDER BY Should be same as my input in IN() - DECLARE @T TABLE(ID INT,NAME VARCHAR(10)) INSERT INTO @T VALUES(1,'PPP') INSERT INTO @T VALUES(2,'AAA') INSERT INTO @T VALUES(2,'ZZZ') INSERT INTO @T VALUES(3,'XYZ') INSERT INTO @T...

Time - Adding minutes and seconds - I have the 2 columns with time datatypes. select top 5 [Connect Time2],[totalTime2] from dbo.verizonDetails Connect Time2 totalTime2 08:05:44.0000000 00:13:00.0000000 08:05:57.0000000 00:01:00.0000000 09:07:42.0000000 00:03:00.0000000 09:07:46.0000000 00:09:00.0000000 09:08:08.0000000 00:01:00.0000000 I want to add the...

SQL Server 2008 : SQL Server Newbies

Backing up & Restoring a DB - Hello, I have a db which I would like to backup and restore. Currently I go to db, right click tasks and...

Permutations of Columns in a Table - Hello, I have several columns, such as: Coulmn A red green yellow black blue green pink Column B shirt jumper blouse coat t-shirt skirt Column C for women for men for girls extra large for boys one size fits all I need to...

Number weeks of work - Number weeks of work Hi there. I should check that the the number weeks of work for the five names is...

SQL Server 2008 : SQL Server 2008 Administration

TempDB and SSDs - Has anyone noticed a significant boost in performance from running their TempDB on SSDs?

JobName Not found error - HI All, I'm seeing a intresting error on one of my SQL Instance. Below is the script I'm running,, connecting to...

Index reorg VS rebuild, round 1 - So, I read alot of people saying Rebuild over 30% fill factor 80%! reorg under 30% only! What is the...

Reorganize Index - Dear Friends I have made a maintenance plan to reorganize index daily, but this error showed to me and I don't...

Using ::fn_dblog() to find who deleted the rows in a table. - Hi All, Recently some one deleted some rows from a table. I was asked to find out who did it. Since...

SQLServerCentral.com : Anything that is NOT about SQL!

Fantasy football 2012 - Only 2.5 short months to football, can you believe it? I'm not ready, hardly feels like it's summer time yet. You...

Today's Random Word! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...

Reporting Services : Reporting Services

Need help in Building a parameterized Report. - Hi All, I have a ETL design which has 58 Tasks that it does every day. Each ETL is assigned...