In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Compare Compare and sync databases with SQL Compare
“SQL Compare is fast, extremely easy to use, full-featured and affordable. I wouldn't bother messing around with anything else.” Adam Machanic, SQL Server MVP. Download a 14-day free trial.
 
SQL Monitor Get alerts within 15 seconds of SQL Server issues
SQL Monitor checks performance data every 15 seconds, so you can fix issues before your users even notice them. Start monitoring with a free trial.
 
SQL Prompt Make working with SQL a breeze
SQL Prompt 5 is the effortless way to write, edit, and explore SQL. It's packed with features such as code completion, script summaries, and SQL reformatting, that make working with SQL a breeze. Try it now.

In This Issue

The Elusive Conditional WHERE Clause

What's this, a conditional WHERE clause that doesn't use dynamic SQL? More »


SQL Server 2012 Build List

A list of the builds of SQL Server 2012 through CU#4. More »


Disaster Recovery in the Enterprise – Paying the Price to Avoid Extra Costs

Data Protection and Disaster Recovery (DR) are IT tasks that seldom get the same level of attention as development… until disaster strikes. Only if planning is adequate can an organisation be resilient in the face of unexpected problems. There are several steps that are needed to achieve an adequate DR process and the ability to restore business operations after a disaster. More »


From the SQLServerCentral Blogs - Run DBCC CHECKDB on a regular basis

Problem One common task as DBA is to run DBCC CHECKDB on a regular basis to detect and correct potential corruption... More »


From the SQLServerCentral Blogs - Set DAC port with a specific number

Usually named instance can uses dynamic port, if firewall setup on the server, you can use SQL Server Configuration Manager... More »


Editorial - Asking for Interview Questions

Recently someone posted a question on SQLServerCentral asking for potential interview questions. A few people were annoyed by this and their annoyance showed in their replies. I completely understand that feeling, with fewer jobs than candidates, people looking for jobs do not want anyone with less knowledge to get any help.

However there are other sides to this question, and I'm torn as to how I feel. If you haven't interviewed in awhile, or if this is your first interview for a database job, wouldn't you want to get an idea of what somoen might ask you? I wouldn't want anyone to misrepresent their skills, but I'd also want them to be able to talk about the skills they have had. Perhaps realizing that they should think about their experience in T-SQL more than their experience with replication might be valuable information.

There's another side to this. Plenty of managers, developers, and even DBAs that are tasked with interviewing candidates for a position don't necessarily realize what questions might help them evaluate a candidate's skills. This is especially true when you have a staff of people trying to find a senior level person because their only talented person resigned. Hiring someone to interview for you, especially for the first or second interview, just isn't cost effective.

Many of us work in companies, and as much as we'd like to think we are gaining new knowledge and experience every year, it's very, very easy to get caught up in our daily work and accumulate many years of similar experience. Without an effort to branch out, take on new responsibilities, and try new technologies, it's easy to become very specialized in the skills needed for our job, even if those aren't a broad range of skills.

I am highly torn on how to handle requests and content on interview questions, but ultimately I think that putting more information out there is the best way to handle things and allow people looking for jobs, as well as those hiring, to educate themselves.

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


The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.

Everyday Jones

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed: or now on iTunes!

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.

You can also follow Steve Jones on Twitter:

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


Question of the Day

Today's Question:

What results do we get from this code?

create table #T1(id1 int );
create table #T2(id2 int );
create table #T3(id3 int );

insert into #T1 values(1);
insert into #T2 values(1);
insert into #T2 values(2);
insert into #T3 values(1);
insert into #T3 values(2);
insert into #T3 values(3);

SELECT 
  sum(id3)
, COUNT(id3)
 FROM #T1, #T2,#T3;

drop table #T1,#T2,#T3;

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

This question is worth 1 point in this category: T-SQL. 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.

Pro SQL Server 2012 Practices

Expert SQL Server Practices 2012 Edition is an anthology of high-end wisdom from a group of accomplished database administrators who are quietly but relentlessly pushing the performance and feature envelope of Microsoft SQL Server 2012. With an emphasis upon performance—but also branching into release management, auditing, and other issues—the book helps you deliver the most value for your company’s investment in Microsoft’s flagship database system.

Get your copy from Amazon today.


Yesterday's Question of the Day

SELECT [Values].[Prefix] + [Values1].[Suffix] AS [Name]
     , [Values].[Number]
 FROM (VALUES ('a', 1),                        
              ('de', 2))   AS [Values] ( [Prefix], [Number] ),
      (VALUES ('pple'),
              ('ttach'),
              ('manda')) AS [Values1] ( [Suffix] )

Will this query run successfully?

Answer: Yes. I will get apple, attach and dettach as Name from the output resultset

Explanation: The values clause can be used as a derived table.

Ref: http://msdn.microsoft.com/en-us/library/dd776382.aspx
http://www.sql-server-helper.com/sql-server-2008/row-value-constructor-as-derived-table.aspx

» Discuss this question and answer on the forums

Pro SQL Server 2012 Practices

Expert SQL Server Practices 2012 Edition is an anthology of high-end wisdom from a group of accomplished database administrators who are quietly but relentlessly pushing the performance and feature envelope of Microsoft SQL Server 2012. With an emphasis upon performance—but also branching into release management, auditing, and other issues—the book helps you deliver the most value for your company’s investment in Microsoft’s flagship database system.

Get your copy from Amazon today.


Featured Script

Get Database Backup Details on SQL Server

DB Backup status Details  More »


Database Pros Who Need Your Help!

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

SQL Server 2005 : Administering

Shrink log file - I have a Database (about 1GB) with 2 log files (one at about 4GB and one at about 13 GB)...

Linked Server - Error - There is a Linked Server call to DB2 from inside an Stored proc that fails with the following error : Cannot...

Can you move a table to new Filegroup/File without removing PK? - These are the steps I was going to take: --step 1 ALTER TABLE Orders.dbo.tbl_OrdersArchive DROP CONSTRAINT [PK_tbl_OrdersArchive] WITH (MOVE TO SECONDARY) --step 2 ALTER TABLE...

SQL Server service account locked - Hello, We had a case where in the domain account which is running SQL server got locked. Database connectivity through management studio...

SQL Server 2005 : Backups

full and differential backup - hi when i am taking full backup and i am restoring full back up i am getting error like "the database...

SQL Server 2005 : Business Intelligence

SSRS Dashboard - Hello everyone, can anyone help me on how to [b]drill down [/b]as well as [b]drill up[/b] in ssrs report? Please provide some...

Why can´t I see all dates when creating attribute relationship the "adviced way"? - I have a problem with my time dimension, I can only see some of the dates, why is this? [img]http://www.sqlservercentral.com/Forums/Attachment13505.aspx[/img] [img]http://www.sqlservercentral.com/Forums/Attachment13506.aspx[/img] When I...

Error while importing multiple sheets of same excel into one table using ssis. - Hi, I am importing multiple sheets of same excel into one table using ssis. I am following below flow. Data flow task->Excel Source->Look...

how to generate records automatically from csv to sqlserver table using ssis etl - Hi All, i have one requirement in ssis. I have one csv file (source) having one column called UOM (Unit of...

simple web based reporting - I’m looking for a simple web based tool that can be used to generate tabular reports. Our application has a...

SSIS 2005 Creating filenames from a SQL Task - Good afternoon I'm in the process of building an SSIS package which will be used to export telephony data on a...

How to set user for deploying reports? - When I deploy my report from BIDS (2008) it doesn't ask me for a user name, it picks a user...

SQL Server 2005 : Development

Query tuning - Hi, I had a performance issue in our stored procedure. Later I found out that the below simple query took 36...

Automatic Update from Excel to SQL - I need to load the data from excel to a table in SQL Server 2008 and automate the updates.ie., If...

SQL Server 2005 : SQL Server 2005 General Discussion

SQL 2005 login time out - Hi Team, Not able to connect to sql from mgment studio and using sqlcmd. Also attached error from mgment studio Getting below error...

SQL Server 2005 : SS2K5 Replication

Merge Replication across internet, "The merge process was unable to deliver the snapshot to the Subscriber. " - Hi, I am getting the following error message while configuring replication across internet, please suggest. The merge process was unable to deliver...

SQL Server 2005 : SQL Server 2005 Performance Tuning

so many indexes indexes - what if i create so many indexes in a database, on each and every table small or large...? any bad...

Join's and Index's - I am fuzzy on this.. so looking to see if my thoughts are correct... When looking at the execution plan,...

SQL Server 2005 : SQL Server 2005 Integration Services

ActiveX Script to Script Task - I am just a beginner in SSIS and having a problems to convert this ActiveX script task code to Script...

SQL Server 2005 : T-SQL (SS2K5)

Excel VBA call to execute stored procedure. - How do you surround a stored procedures parameters so that any with a single quote don’t cause a crash. Excel VBA...

INFORMATION_SCHEMA.COLUMNS reference - How do you reference INFORMATION_SCHEMA.COLUMNS from a linked server? When I try doing that, I get the below error message: select * from [server_2000].dbst.information_schema.columns Msg...

SQL Server 2005 : SQL Server Newbies

selecting all attributes for a loan from a loan table - Hi all, I have a student loan table that holds details such as the loan#, SSN and their dob(birth date).The...

SQL Server 7,2000 : Administration

require db monitor script for sql 2000 - Hi, Need help in writing a script for sql server 2000 to check the status of all db's in the instance...

SQL Server 7,2000 : In The Enterprise

Calling MySQL stored procedures viua a linked server - Does anyone know if it is possible to call MySQL stored procedures via a linked server setup? I've searched around...

SQL Server 7,2000 : Replication

Initial snapshot for publication is not yet available - I'm not sure why my transactional replication is giving me:  "The initial snapshot for publication 'XYZ' is not yet available."  ...

SQL Server 2008 : SQL Server 2008 - General

Pros & Cons- Table Value Function vs Summary Table - For discussion... I need to provide summary information on records in a table such as total amounts of varying types and...

Large Object in buffer cache - Hello We are using Redgates SQL Monitor tool and I am getting 'Large object in buffer cache' alert message - I have...

Update statement really slow but only on the first execution - Hi, I have a standard update statement that updates one row at a time. The table size is 340,000 rows, so...

CMDExec Proxy account not working with Domain Service Account... - I'm working on migrating an existing Server 2003 / SQL 2005 system to Server 2008 R2 / SQL 2008 R2, and am...

HOW TO MAKE DYNAMIC PIVOT FOR THIS TABLES WHEN I PASS ONLY ONE PARAMETER @DID INT? - here iam having two table @DETAILS and @CONTACTDETAILS in this two table @DETAILS is a static table in which cotactid...

Transactional Replication with updatable subscribers over the internet - I have created a publisher on server A. I need to create a subscriber on server B over the internet....

AD name change Old name still persists - After a user change of name in AD. SQl still shows the user as the previous credentials Ie Domain\Usernameold Domain\Usernamenew Although the users...

Compare scripts B4 and After a Performance Fix ! - "How can I"/"what are all the ways I can" compare a query/SP before and after a Performance Fix, to know...

Substract End Dates - Hi Professional I need to substract the end date as like below 2-1,3-2,4-3, and showing in new column like datediif_days if...

Creating array in sql server - A string contains 3,4 suppose I want to store 3 in one parameter and 4 in another parameter similar in...

configuring alerts - Any one help how to configure alerts on sql like log growth , backup failure ,disk space . login failed etc as...

how to do pivot - Hi Guys, actually my table data looks Key value firstname surya firstname rakhi firstname venkat firstname shankar firstname dany I want to disply like firstname surya rakhi venkat shankar dany i write the below...

how to find the file in user define filegroup - Hi , when i used alter database MYDB addd file (name ='mydb_1', filename='c:ew\mydb_1.ndf, size=10mb, maxsize=200mb, filegrowth=10 mb) to filegroup FG1 then file showing in FG1 but when we...

Logic creation - Hi all, I have to write a script for a scenario but don't know how to implement it... CREATE TABLE [dbo].[GV_STNDetails]( [STNID] [int]...

Strange datetime conversion issue - I have been using a query that joins several raw tables (nvarchar datatypes), has several conversions, nested queries, CASE WHEN...

view more efficient than before or not? - I have a view, if we runs it individually in SSMS, it runs ok, but we have a batch process...

List Partitioning in SQL Server 2012 - Hi I got a requirement to partition the table on year, the column datatype is int and the values are...

Assigning a fieldname with rollup - I have two tables that have racing data, and I need to add up the number of cancelled races and...

Considering Moving to SQL Server - My company currently uses Access 2007 and wants me to look at moving to SQL Server 2008/2012. My question is...

Count number of visits by week, ending on Saturday - I am trying to get a count of visits by week with the week ending on Saturday. My problem is...

Checkpoint Background Process? - Hi All, I'm new to SQL DBA tasks. I noticed in sp_who2 output a database is showing checkpoint as shown...

Data Import // MS Query // Header Missing - Hi Guys, i wonder if anybody can assist me this is doing my head-in. I am using Query and linking it...

No of rows in Temporary Table - What is the maximum number of rows can be stored in a emporary table?

Is there a better way to insert data than Access? - Is there a better tool to insert data into SQLserver than Access ? For managers. Small number of people. Very limited...

Weekly report through whole year - Hi there I have 2 tables and sample data: -------------------------------------------------------------------------------------------------------------------------------------------------------- CREATE TABLE [dbo].[tblProduct]( [ProductID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NOT NULL, CONSTRAINT [PK_tblProduct] PRIMARY...

Third party backup tool breaking the backupset LSN chain - I am attempting to develop a workaround to this issue and was wondering if it is possible to modify the...

SQL Server 2008 : T-SQL (SS2K8)

Show Results When more than just my result shows up. - Hey Guys, I'm trying to Show All location changes in our system in the last 5 days, now all active...

Stored Procedure execution with parameters - While checking our production plan cache, I noticed that over half of the entries were like the following: exec mt_amstask7 'AMS','78609072','1045458320','20130417...

How do I use group by on one column which is having many entries? - Hi, I want to use group by on one column which is having many entries table_a Name price AAA 12 BBB 13 ...

Compare date? - Hi All, Is it possible to copare dete in format 10:30 AM. for example i want to find out the date which...

loading massive files - I'm loading server large data files into a db. Some records in some table have carrage returns in the data...

Wildcard DB name in VIEW of Procedure - Possible? - Hi there, I've been trying to figure out whether it's possible to have a Wildcard DB name in a SELECT query....

Storing Spatial Data - Hi All, I work with geospatial specialists who provides me with polygons which i save it to my geography field. And...

Can someone help with a TSQL date sorting issue? - I would like to retrieve data for all the fields listed in the query below. The problem is that I...

Multiple DMLS in one when clause of MERGE Statement - Hi All, I want to use 2 DMLS in When not Matched by target clause ie. first insert and then update.But...

CTE - UNION ALL - GROUP BY - Today I got a mail from one of our developers. It is a little complicated to explain and I cannot...

Need advice on this string manipulate scalar UDF - Dear all, [b]Can you please comment on this UDF, is there any better way to do it? For example CLF scalar UDF...

SQL Server 2008 : SQL Server Newbies

How to find values in a comma delimeted string with len>3 - Hi everyone, can you please help me with the following? I have a nvarchar column with data like this: ', 55,85,1,4,9888,6587,' How...

How to add variables to a table in SSIS - Hi all, I'm in need to import several flat files into a SQL table. I'm used to importing one at...

Slows system performance - Dear All I am running a process (updation/deletion/insert) of 2 million records. After some time this process makes my system very...

SQL Server 2008 : SQL Server 2008 High Availability

Installing SPs & CUs in Clusters - [url=http://support.microsoft.com/kb/958734]This Knowlege Base article[/url] describes the procedure for installing Service Packs & Cumulative Updates on SQL Clustered instances. Is says that before...

Duplicate IP6 address in SQL Group - Hi I have a 2 Node SQL Cluster; Windows 2008 Enterprise R2 Sp1 , 128gb, with SAN storage Admittedly it is 2012, but...

Windows or Database Clustering? - Hi All, if my windows in clustering mode, is my database in SQL server also in Clustering? or I will have...

Changing Owner or Mirrored DBs - How would I go about changing owners of a database that are within a mirror? Jonathan

SQL Server 2008 : SQL Server 2008 Administration

Why powershell? - Hi all experts, I have being reading about Powershell a lot this days. What i came to know about powershell is...

Moving server to different zone in Datacenter - I am involved in moving some servers to different zone within the same data-center. There are no IP changes, so...

CU installed or not? - We single node cluster running SQL 2008 R2 SP2. We noticed the Application Even Log contianing thousands of messages like Information...

Transactional Replication - What are the permission required to configure Transactional Replication? :-P

Restore Transactional Log Backup - Hi, I am new to this forum.Could anyone help me with this questions please. I am not able to resore my...

No performance gain on queries on different servers with considerable hardware change - I have a dedicated Server for SQL Server. SQL Server runs on SQL Server 2008 Standard edition with SP2 applied....

Create DML trigger for single table in SQL 2008 and send email alert to group - Hi, i have a requirement to Create DML trigger for single table ( check for any updates ) in SQL 2008 and send...

Commvault Simpana 9 SQL Backups - Hi, We have an ongoing battle with our Infrastructure Architect, where they would like to incorporate all SQL Backups in the...

Programming : Connecting

Connecting to Excel Macro Connection via Windows 7 Task Scheduler - Hello, I am currently running an SSIS package that connects to an Excel Macro enabled file and loads these records to...

Programming : Powershell

Can't script the schema for a FK - Hi, I use the below script to script out FKs in a database: #DECLARE TIMESTAMP FOR THE FILES $timestamp = Get-Date -Format yyyy-MM-dd #SCRIPT SL SQLSERVER:\SQL\'MyServer'\DEFAULT\Databases\'MyDB'\Tables $so...

Pass parameter into XML file using Powershell - Hi, I have a project to automate SQL server restore.........use restore db's using third party tool called Simpana, Commvault.....we would like...

SQLServerCentral.com : Anything that is NOT about SQL!

how to find whether "Microsoft visual source safe" is installed or not ?? - Hi, How can i check whether "Microsoft visual source safe" is installed or not on my machine?? I have Microsoft visual...

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

SSRS 2008 Snapshots for data driven subscription - I have a business unit client who needs to produce 5 reports each month for each of 120 users (total...

Drop down list/filter not working - Hi all, first post! Ok i dont know if im just missing something obvious but i cant for the life...

Populate tablix from same dataset based on switch - CREATE TABLE #Enabled ( State VARCHAR(15), DaysToEnable VARCHAR(10), NumberOfDeliveries INT, NumberofAccountNumbers INT ) INSERT INTO #Enabled (State,DaysToEnable,NumberOfDeliveries,NumberofAccountNumbers) SELECT 'Enabled',NULL,410,157 UNION SELECT 'Enabled','0-4',298,99 UNION SELECT 'Enabled','10-14',35,21 UNION SELECT 'Enabled','15-19',6,5 UNION SELECT 'Enabled','

Reporting Services : Reporting Services 2005 Administration

Site Settings and Security Tabs are missing from Reports Manager link - To the SQL forum readers. From our SQL server 2005 Reporting Server page the tabs for Security and Site Settings are missing....

Data Warehousing : Integration Services

Merge Statement using Execute SQL Task - A deployment issue - Hi friends, [b]Background:[/b]I'm facing the same problem across different projects and I’m not able to find an appropriate solution: When I want...

Data Warehousing : Strategies and Ideas

Data Warehouse Models - Someone from my company's strategy has said that the Data Warehouse model needs to be: - Atomic - 3rd NF - Relational But I...