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

Happy Holidays 2012

Steve Jones from SQLServerCentral.com

Merry Christmas and Happy Holidays from SQLServerCentral More »

Free ebook: Troubleshooting SQL Server: A Guide for the Accidental DBA

Press Release from SQLServerCentral.com

Merry Christmas from SQLServerCentral. We spend much of our working life helping solve SQL Server-related performance problems, hands-on, during consulting, or on online forums. We've seen a few weird-and-wonderful issues but, mainly, it’s the same problems and misconceptions time-and-again. This is our attempt to describe, diagnose, and solve the most common problems with SQL Server 2005, 2008, and 2008 R2. And it's free. More »

SQLServerCentral Best Practices Clinic: Part 1

Brad McGehee from SQLServerCentral.com

We exposed the SQLServerCentral cluster for monitoring with SQL Monitor. Just like other companies, we have constraints on resources, and we have more work that needs to be done. Help us configure SQLServerCentral’s database servers with your suggestions on what is the highest priority for a website database back end. More »

From the SQLServerCentral Blogs - Index Analysis: The Re-Return

StrateSQL from SQLServerCentral.com

A couple years back, I wrote a few blog posts about an indexing script that I use with clients from... More »

Merry Christmas and Happy Holidays to everyone in the SQLServerCentral community.

I hope very, very few of you are working on this holiday Tuesday. I'm off, spending the holidays in the mountains with my family, including my oldest back from Seattle for a week. It's a tradition for us to spend either Christmas or New Years skiing, and this year we've decided to spend a week in Steamboat Springs.

I've left you with some holiday bloopers to enjoy and I'll be back with some end of year thoughts later this week.

Bloopers

Download the WMV - 37.9MB

Download the MP4 (iPod) - 33.2MB

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:

Santa receives a list from you of things that you would like to receive this Christmas. The list has 7 items, and Santa decides that you have been good enough this year to receive 5 of them. In order for the elves to find the items that Santa has marked as approved, which index type is best chosen for the list of gifts that Santa has marked with an approved flag?

By Steve Jones

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

This question is worth 7 points in this category: humor. 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.

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

Answer:

• View 1
• View 4

Explanation: A CASE expression will only evaluate the THEN clause if the corresponding WHEN clause is true. Avoiding division by zero is one of the many use cases for a CASE expression. If no ELSE clause is present, the returned value will be NULL, causing the result of the division to be NULL in views 1 and 4. The NULLIF function is a shorthand for a CASE expression that returns NULL if the first and second argument are the same, otherwise returns the first argument. Avoiding division by zero is arguably the most common reason for using NULLIF. The NULLIF in view 5 will replace the 0 divisor by NULL, resulting in a NULL result of the division. In view 2, the division is computed first and then compared to 0, so the error is not avoided. IF is a control-flow statement. A view can contain only a single query, and control flow statements are not permitted in a view. Attempting to run the code to create view 3 or 6 will return an error. References: CASE - http://msdn.microsoft.com/en-us/library/ms181765.aspx NULLIF - http://msdn.microsoft.com/en-us/library/ms177562.aspx VIEWs - http://msdn.microsoft.com/en-us/library/ms187956.aspx IF ... ELSE - http://msdn.microsoft.com/en-us/library/ms182717.aspx

» Discuss this question and answer on the forums

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.

SQL Server Documenter

Rudy Panigas from SQLServerCentral.com

This script was designed to provide you with all the information needed to rebuild your server in case of a disaster. Current version work with 2005, 2008, 2008R2 and 2012. More »

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

SQL Server 2005 : Administering

SQLServerAgent could not be started (reason: Unable to connect to server ''XXX; SQLServerAgent cannot start). - Hi Everyone.... I am getting this issue in windows server 2003 server cluster....I try to bring agent node online but no...

Replication in sql 2000 - Hi Guys Whereas I did not find my problem here, I am posting the new one. Actually I set up a transactional...

SQL Server Performance Problems - Hi Guys Occasionally at peak times of the day I get I/O errors on my SQL server (v 2005, 16 core...

SQL Server 2005 : Backups

Restore database to a remote server - Hello Everyone, I am fairly new to this forum and have some experience with SQL. I was recently assigned a project...

SQL Server 2005 : Business Intelligence

Data update from one database to another database (Insert and Update both) - Hi, I have 2 database tables. 1) From Production 2) From Dev. The task is to move the data from Production to Dev Server....

NAMED SETS - Help - Hi to all. I use SSAS 2008 R2 and inside a cube i need to insert a NAMED SET. I studied about...

SQL Server 2005 : SQL Server 2005 General Discussion

Where clause Case Statement and IN - How do I correct the following statement: Declare @Component smallint = -1 Select srcComment.Comment As Comment, t.NameFull As CommentType, parenttypeId As Component, ...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Sp performence. - Hi, I am facing one issue in SP at prod. which is running at QA and staging within 1-2 mins. But...

SQL Server 2005 : T-SQL (SS2K5)

LEFT JOIN vs EXCEPT - HI All, I have just come across the except tool in SQL 2005 and have been running some performance checks to...

SQL Server 2005 : SQL Server Newbies

Stripping out all non-numerical characters - SELECT accountid,New_Column = REPLACE(telephone1,SUBSTRING(telephone1,PATINDEX('[^0-9]',telephone1),1),'') INTO #YourNewResults FROM #TelephoneTable I have the script above which I thought would remove all non-numerical characters from the field...

creating a table to show month and year - I need to refer to a temp table which has all the months and dynamically shows the years. for example...

SQL Server 7,2000 : T-SQL

Trimming special characters - One of our developers created this UDF to trim out special characters...is there a better way to do this? ALTER FUNCTION...

SQL Server 2008 : SQL Server 2008 - General

How to find a history of replication setups and removals? - We had replication for several database that was setup some time ago. Now we found that one of these databases...

File Size - I have a Old database which was created way back in 2006, it had many data.. & that DB is been...

RDL Reports - Hi, I have a dashboard which contains many RDL reports but it takes about 30 minutes. How can improve the performance...

Script through which we cannot store data into ldf file - [b]Query:[/b]I delete a table in sql server 2008,and,i do not want this entry to go to our ldf(log files).In this...

SSIS PACKAGE DOUBT - Hi Guys, I need a small help, I need to execute package with input parameter. The command which i have...

where freaks met the fashion establishment beneath the spinning mirrorball of Burberry Outlet - [url=http://www.louisvuittonoutlethandbagsdiscount.us]Louis Vuitton Outlet[/url] where freaks met the fashion establishment beneath the spinning mirrorball of the club of the moment,At $48....

Someone must be careful while lifting inferior quality bags and Yves Discount Louis Vuitton - [url=http://www.louisvuittonoutlethandbagsdiscount.us]www.louisvuittonoutlethandbagsdiscount.us[/url] Someone must be careful while lifting inferior quality bags and Yves Saint Laurent is a brand name in the...

has made a virtue out of top to toe leather dressing www.burberryscarfmore.us decades - [url=http://www.louisvuittonoutlethandbagsdiscount.us]Louis Vuitton Handbags[/url] has made a virtue out of top-to-toe leather dressing for decades, there you are. I'm sure I'd...

she says she was initially denied medical care for blisters on Burberry Outlet - [url=http://www.louisvuittonoutlethandbagsdiscount.us]Louis Vuitton Outlet[/url] she says she was initially denied medical care for blisters on her fingers,New and just in time...

Design suggestion - Hi, I have to keep in a login history table the user's operating system / operating system version / browser / browser version / device...

New login is created but user cant login using credientials ? - I created users with login option in security tab in sql server 2008 R2 running on windows 7 successfully and...

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

Dynamically adding the schema name or populating the schema name - We have a script that we run on various different databases of customers. so for example it may have a line...

Unable to shrink a 50GB log db file - Hello there, I'm trying to shrink a 50GB log db file down to GB using the following script. USE [master] GO...

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

tempdb fileplacement - Hi all, I have a 1T, high I/O database in a SAN environment on which I have planned the database files...

SQL Server 2008 : T-SQL (SS2K8)

t-sql 2008 avoid a cartesian product - For a customer, I need to load data from excel 2010 spreadsheets into a sql server 2008 r2 database for...

Linked server query performance - Hello, not really sure if this is the right part of the forum for this question but I have a...

Retrieve results from table with latest timestamp - Hi, I have a table with syn_trans with below cols: ( Id integer(10), Name varchar2(100), seq_no integer(10), seq_name varchar2(100), gp_name varchar2(100) ) There is no unique/primary key on the...

matching user to password - can anyone tell me the basic T-SQL for looking up a user and their password in a loging I'm creating?...

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...

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...

Finding a quote in a string - What can I use to find if a string contains a '? I know how to replace the quote with a...

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...

T-SQL query to add multiple valued report parameter - SELECT Persons.P_id ,Persons.LastName ,Persons.FirstName ,Persons.Address ,Persons.City FROM Persons where Persons.P_id IN (@P_id) the above query consider only one parameter when I try to...

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

force input of another field when putting a value of 1 in field - I have a field to indicate a delete which will be a text char of "1" in a field called...

SQL Server consuming 90% Memory - Hello Masters, My sql server has 12GB of RAM, I got alerts from windows team that SQL Server consuming about 90%...

download sql server management studio RTM 9.00 1399.00 - Can anybody show me the download link sql server management studio RTM 9.00 1399.00 (not express version).

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...

Create Table and Bulk Insert - Hi guys, I have a .csv file wich has the following information (exactly like this): List Name: User Format Version:1.2.5.0 Date:12/11/2012 12:00:34...

SQL Server 2008 : Security (SS2K8)

editting data in an encrypted column - Hi all, I'm attempting (unsuccessfully) to reset a password, the issue is that the column with the passwords in is...

SQL Server 2008 : SQL Server 2008 High Availability

While executing below update_statistics script on SQL 2008 R2 i'm getting table captured_columns does not exist. - --DROP TABLE STATS_TAB --drop proc UPD_STATISTICS --Syntax for Usage --exec upd_statistics 'PUBS' OR --exec upd_statistics 'PUBS' ALTER PROC UPD_STATISTICS (@DBNAME...

SQL Server 2008 : SQL Server 2008 Administration

MAXDOP setting - I know that this is slighty a 'depends' question but our server is heaver user transactional server. Our SQL 2008R2 server...

Log File Size Issue - Hi, One my DB Log File Grown to large and now user were getting "transaction Log Full" error. When i checked server...

Hardware or Bad Queries = Performance issues? - Help Please!! How can one identify whether performance issues on a SQL 2008 R2 server are being caused by Hardware or...

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...

Programming : Powershell

Powershell execution error - Greetings, I have a script that was executing a powershell script from within a batch file with the following syntax: "“powershell.exe -NoLogo...

Query local SSMS server group with Powershell? - This may be a totally off the wall question, but it seems like it should be possible to do. Just...

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...

SQLServerCentral.com : Tag Issues with Content

can not access master stored proc - Hi, I have the below code declare @ret int DECLARE @XMLFile varchar(255) SET @XMLFile = 'C:\myfile.xml' exec @ret = master..xp_FileExists @XMLFile When I execute this its throwing...

SQLServerCentral.com : Podcast Feedback

Too many gifts to count - [i][b][size="2"]I want to thank all the staff and contributing members of SQL Server Central for the wealth of knowlege and...

Reporting Services : Reporting Services

Any alternative to Reporting Services designer? - Hallo has anyone experienced an alternative to the standard SSRS designer? I mean something more similar to an Adobe Illustrator, with more...

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...

Microsoft Access : Microsoft Access

Damn Workgroup Administrator! - We've been using Workgroup for more years than I have toes. In Access 2010 when I use the DoCmd.RunCommand acCmdWorkgroupAdministrator...