SQLServerCentral - www.sqlservercentral.com

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

Featured Contents

Featured Script

The Voice of the DBA

Testing Skills

This editorial was originally published on May 11, 2009. It is being re-run as Steve is on vacation.

How can you measure someone's skills? Is there a good way to actually assess how well a DBA or developer will do in your company? If you have one, there is some money to be made by passing the information along.

Traditional interviews where you ask lots of questions typically haven't necessarily proven to be a great way to do this.  Some people give tests, but they often are thrown together, and might contain multiple choice answers to make them easy to grade. However the other day someone posted a technique they used that I liked.

This person suggested that a VM be configured with Windows and SQL Server and then with a certain number of things wrong with the install. Or at least things that would be seen as wrong in that particular  environment. You then give it to a candidate and ask them to tell you what's wrong. You could let them work through it in a stream of consciousness way, telling you what was wrong as they found it. You could also give them some time, say an hour, and then come back and let them explain what is wrong.

Actually I'd recommend you give the candidate the choice since some people work better alone.

I've thought about asking them to accomplish certain tasks before, but after considering this technique, I like it a bit more. Just asking them "what's wrong?" or "what would you change?" is a good way to see how people think. Interacting with them, getting them to dig into a real system, is a great way to assess not only technical skills, but also some interaction. See how they debate, argue, or question a co-worker is important, at least it's important to me.

Is this is a better way to interview people and assess skills? Would you like to be interviewed like this? Maybe this would even be a fun competition at somewhere like the PASS Summit.

Steve Jones

Steve Jones from SQLServerCentral.com

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


The Voice of the DBA Podcasts

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there.

You can also follow Steve Jones on Twitter

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.

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.


ADVERTISEMENT

Hate explaining your database in meetings?

SQL Doc quickly documents your entire database schema so that you can easily present it to others. "This tool is embarrassingly easy to use." David Hayden, DavidHayden.com. Download a free trial now.

ADVERTISEMENT
SQL Source Control

Get your SQL Server database under version control now!

Version control is standard for applications, but databases haven’t caught up. So how can you bring database development up to speed? Why should you start? Find out…

SQL DBA Bundle

The seven tools in the SQL DBA Bundle support your core SQL Server database administration tasks.

Make backups a breeze! Enjoy trouble-free troubleshooting! Make the most of monitoring! Download a free trial now.

Featured Contents

 

SQL CLR Multi-Threaded File Copy

Steve Ledridge from SQLServerCentral.com

A CLR Procedure that performs multi-threaded fie copies without external command line tools. More »


 

SQL Server Index Properties in Management Studio

Additional Articles from MSSQLTips.com

Understanding indexes and how they work can be complicated enough for a Jr. DBA, but throw in all the different options and properties and an index can soon be overwhelming. Brady Upton takes an introductory look at creating an index in SQL Server using SQL Server Management Studio. He explains what each index property is meant for and the various options presented. More »


 

From the SQLServerCentral Blogs - SYS.PARTITIONS: Better Than X-Ray Vision

Tracy McKibben from SQLServerCentral Blogs

Originally posted 2011-09-09 21:27:00. Republished by Blog Post Promoter Over the past few months, I’ve been doing a lot of work... More »

Question of the Day

Today's Question (by Ford Fairlane):

True or False: In SQL Server 2014 you have the ability to set the following off.

  • SET ANSI_NULLS OFF and ANSI_NULLS OFF database option
  • SET ANSI_PADDING OFF and ANSI_PADDING OFF database option
  • SET CONCAT_NULL_YIELDS_NULL OFF and CONCAT_NULL_YIELDS_NULL OFF database option
  • SET OFFSETS.

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


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

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

ADVERTISEMENT

SQL Server Hardware will provide the fundamental knowledge and resources you need to make intelligent decisions about choice, and optimal installation and configuration, of SQL Server hardware, operating system and the SQL Server RDBMS.

Pick up your copy of this great book from MVP Glenn Berry at Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jacobs):

What is the resultset from the query below?


--Query 1
CREATE TABLE #temp ( a CHAR(1), b VARCHAR(10) );
--Query 2
INSERT INTO #temp
        ( a
        , b
        )
        SELECT 'A'
              , NULL
        UNION ALL
        SELECT 'A'
              , 'Hello'
        UNION ALL
        SELECT 'C'
              , '12345'
        UNION ALL
        SELECT 'B'
              , 'Hello'
        UNION ALL
        SELECT 'D'
              , '54321'
        UNION ALL
        SELECT 'E'
              , NULL
        UNION ALL
        SELECT 'D'
              , '11111'
        UNION ALL
        SELECT 'B'
              , '54321'
        UNION ALL
        SELECT NULL
              , NULL;
--Query 3
SELECT a
    FROM ( SELECT a
              , b
            FROM #temp
         ) AS t1 PIVOT ( MAX(b) FOR b IN ( #temp ) ) AS t2
    ORDER BY CASE WHEN a IS NULL THEN 1
                  ELSE 0
             END;
--Query 4
DROP TABLE #temp;

Answer: A,B,C,D,E,NULL

Explanation:

Answer is

A,B,C,D,E,NULL

This is a very simple example of "PIVOTING."  I'm only selecting column a as my returned resultset and is in ascending order (by default) and of course, the pivot on the table itself via column 'b' (finding MAX 'b' value) to perform the aggregate.  Please note the way I am ordering my NULL values.

PIVOT:  http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx


» Discuss this question and answer on the forums

Featured Script

Powershell script to interactively move cluster resources

Rolando Hernandez from SQLServerCentral.com

Every time Microsoft releases an important security OS patch I have to apply the same to some 16 clusters (32 nodes), even thouhg I have order list to patch the servers it becomes somehow complex to log on each node and failover/failback the nodes or the cluster resource groups.  

So that I wrote this script to interactively move the resource between cluster nodes, this has added the value of logging each step and also confirmation of what pieces need to go where.

This is how to do it:

1.- Create a file list called "clusterlist.txt" and place it in the same folder as the script is.   This file should contain the clusters virtual names to work on.

2.- Please enable delegation execution by using the following command :  

enable-wsmancredssp -role client -delegatecomputer server.windowsdomain.com

See screenshot below of illustrative issue when the previous feature is not enabled.

3.- Once CreSSP is enabled execute the script from powershell like:

powershell.exe -command ".\clustermove.ps1"

4.- It will read the list of clusters from the file, if this does not exists, will read the cluster list from the active directory.   You need to have read permissios to browse cluster names, from your AD or network.

5.- Follow interactive instructions on moving resource groups between nodes.

Note : When the options of "move all resource groups at once", they will be moved to the next available node.  When choosing a particular group, you will get the list of nodes available so that you can select where to put the resource.

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 2014 : Development - SQL Server 2014

Microsoft SQL Temp Tables (without declaring columns – like Informix)? - I recently changed positions, and came from an Informix database environment, where I could use SQL statements to select one...


SQL Server 2012 : SQL 2012 - General

Service Not Starting after upgrade of Sql Server 2012 to SP1 - We recently upgraded out Sql Server 2012 RTM instance to SP1 base configuration. After the upgrade, the Sql Server Service...

Selecting rows based on Timestamp column - I have source and target tables, both having Timestamp column in them. I would like to select all the rows...

Getting an error when attempting to insert a record into a table. - I've written a Windows Communication Foundation (WCF) component which is used to put data into a SQL Server 2012 database....

Using SSMS 2008 with SQL 2012 - I'm struggling to adjust my practices to the changes in SSMS 2012. There are a lot of things that I...

Mass Delete Just Hangs - I am trying to upgrade to SQL 2012 from SQL 2005. I have a job that deletes expired records from...

SSIS Export different tables to different files in one SSIS Package - Hi All, thanks for looking at my question . at the moment, i need to do something at work which specified...

Interview Question - Can you please let me know what would be the right answer for the following? 1. What should be strategy...

Breaking 3NF to save a join - should I do it? - Guys, I have the following situation for my new Data Warehouse: A table called ENTITY [code="sql"] CREATE TABLE Entity ( ID int identity(1,1), EntityType int not null, NaturalKey...

Auditing Database usage - I have a request to audit database usage. Database usage is defined as anytime someone accesses a database for anything,...

How can I use output of a stored procedure dynamiclly? - Hello I have a stored procedure as GENERATE that generates a html file and I have another one as SHOW that...

Problem changing account for job execution - SQL Server 2012, some jobs are centralized from another server running SQL Server 2008 R2. I'm having a problem getting jobs...

An error while copying database - I am trying to use Copy database wizard to copy databases from 2005 to 2012 sql server, but I'm getting...

Output a query as an Excel file - Hi all, I'm a newbie to SQL. I'm using SQL Server 2012 on my local machine and I need to find...

loading data dynamically SSIS? - Hello, I do not know the best place for this question. Problem: I have multiple txt files in different formats as they...

Change initial sixe of transaction log file - Dear All We have a db with Full recovery Model. Daily full backup is taken and every hour transaction back up...

DB Offline - Hi In event log i can see 'Database option changed from Offline to On'. Whant to chekc why did the db...

Log Shipping broken by Veeam - WE are moving into SQL 2012 and concurrently moving to Veeam for Virtual Server backup. We have found that our...


SQL Server 2012 : SQL Server 2012 - T-SQL

Strange SQL Results. Example Included. - A coworker brought this to my attention and I can't figure out what is happening. I was hoping someone here...

Pivot query needed - Hi, I need some help on making the right SQL-query to get the desired results. I have a table, named tblCalls...

2 records at a time? - Hi everyone, I'm not even sure how to word this succinctly enough to search for this topic, so I apologize in...

Combine Multiple Queries into a CTE? - Hello all, I am looking to build a query with a result set that combines multiple queries to appear as...

SQL Report Builder - Hi, Not sure where to put this... Im working in SQL Report Builder (sorry dont know which version) and am having trouble...


SQL Server 2008 : SQL Server 2008 - General

Problem with my procedure - Hi Professional. I have a procedure which creates a file with column headings like so [code] alter procedure [dbo].[extractnewheadings] @importedquery nvarchar(1000) as begin Declare @sCmd varchar(256) SET @sCmd...

Distributed Transaction Coordinator behavior? - I'm in the middle of tuning a process so that it manhandles fewer rows from a linked server than when...

Alert when TempDB is growing rapidly - I had a issue where tempdb grew rapidly in a short period of time. Tempdb file growth is restricted so...

Is point-in-time possible after restore - Scenario: SQL Server 2008, Database Full recovery model, full backup every night, taking approx. 2.5 hours to complete. log backups every...

creating disk drives for sql install - i need to install new sql server 2008 r2 i want to take a decision on creating Disk drives for...

Adjusting column wiidths; SP_SEND_DBMAIL - Hello All, I am working on a small project using SP_SEND_DBMAIL. The question I have is, how do you you set...

redgate SQL search - can anyone here confirm that redgate SQL search will search for character strings in the DATA? I have an as yet...

RedGate SQL Monitor Value - Quick question: Is SQL Monitor from Redgate worth the money? I've never used it, but I wanted to know other's...

Cascading Deletes - Hello, We currently have an application database that contains several main tables which all contain approximately 3-4 million records. Currently the...

script needed - Report of all users who have access to the database including their roles and privileges assigned - including administrators, and any...

Update query with & without null values - Hi All, I have a grid with checkbox, where users can select multiple rows and edit at the same time and...

Databases in vendor applications and indexes - What's best practice with regard to indexes in vendor applications? I assume it's not done to add your own indexes?...

Write to text file - Hi Professionals I am trying to write a variable with column headings to a text file but it does not seem...

how to hide schema? - hello :) please help me. i am not DB admin, and have no idea how to solve my problem: in a...

Cursor fetch loops endlessly - I am having a cursor which I use to update a 2nd table containing unique IDs from an identity column. The...

How to make database back to standby mode - Hi All, before my database was in standby i make it online using restore database <name> with recovery but now again i...

Text to XML data type for existing table - Hi I have a table of 2 million rows + that has been around some while (not quite dawn of time...

Error for checkdb for master database - When i run dbcc checkdb on master database i get error like Msg 5030, Level 16, State 12, Line 1 The database...

Transactional Replication Performance Issues After Migration From 2000 to 2008 - We currently have 140 SQL 2000 publications replicating to a single 2008 server with about 25 articles. 5 of the...

Mirroring Failover/Failback history - Hello, I am a SQL DBA newbie, and I am trying to get the failover/failback history for one of our mirrored...

NT AUTHORITY\SYSTEM - Hi. Sorry if this is a bit of a silly question, but how can I find out what minimum permissions...

Why 10.52.4000 - Dear Gurus, Why i am getting Version. [b][i]2013-09-17 17:38:42 Slp: Package ID sql_common_core_Cpu32: Installed - Version: 10.50.1600.1 2013-09-17 17:38:42 Slp: Package ID sql_common_core_loc_Cpu32_1028: NotInstalled 2013-09-17...


SQL Server 2008 : T-SQL (SS2K8)

Need to parse values from XML type into SQL View - Hello I have a table (JatinApplication) which has a field (properties type NTEXT), which contains string like this: <ResourceGroupSpec xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.municipalsoftware.com/AppSpec" version="0"...

T-SQL Query - Hi all, i have a trandate and tranvalue,customer,product now i need to poulate a table with trandate,tranvalue(sales amount),customer,product,py_tranvalue and i need...

delete certain nodes - How do you delete

T sql for patient arrivals, transfers In and Out and discharges and census on a hospital floor by hour - Hi, I am trying to create a SP which lists the total number of patients Arrivals, Total Discharges,Total transfers and total...

Crystal Formula to T-SQL - Hi, I have been trying to convert the Crystal formula below into T-SQL, but I'm not able to do that. Anyone...

T-SQL puzzler - given a cell in an N-dimensional space, return the cells that are inline with the given cell along each axis - The cells in an N-dimensional space are modelled with the 2 tables below. A script is needed that takes a...

Most efficient way to get a single unique combined record for distributed information corresponding to same ID - I have a scenario like this: [code="sql"] create table #temp(id int, name varchar(50), age int, contact_number varchar(50)) insert into #temp(id,name) select 1,'John' go insert into #temp(id,age) select...

Age - I have a table called member which contains memberid and DOB. How can I calculate age to date based on...


SQL Server 2008 : SQL Server Newbies

Migrate a SQL 2000 database to a different server - I need to migrate an old SQL 2000 database, setting on a windows 2000 server, to a SQL 2008 setting...

CASE WHEN and Update of Table - I am looking at updating a table with a trigger and have attached the code - but am getting basic errors...


SQL Server 2008 : Security (SS2K8)

DTS Package SAVE AS - Possible security vulnerability? - I am posting this not only to point out what appears to be a security vulnerability but to hear opinions...


SQL Server 2008 : SQL Server 2008 High Availability

Error installing SP2 CU1 sqlserver 2008 R2 SP1 CU1 - When I install the SQL SERVER 2008R2 SP2 [b]CU1[/b] offline disks fails and leaves the failed instances (clustered). I roll online...


SQL Server 2008 : SQL Server 2008 Administration

Server side trace question - I've started monitoring on my SQL 2000 servers by running server side trace through SQL job. The steps of the job...

SQL Server and App documentation tool - Looking for a tool (s) that would help in documenting SQL Server databases and objects along with their relations to...

Transaction Log Management - Hi, I'm new to backing up and restoring databases, if you could shed some light for me on the correct way...

Email Notification for Transaction log full - How can we get an email notification when db transaction log is full rather than production getting affected. it would...

Error while installing SQL Server 2008 - Hi, I am trying to install SQL 2008 R2 Standard 64 Bits on a box which already has SQL 2005 Standard,...

Corrupt Backup File ? - [size="1"]I got this message when trying to restore a DIIF backup Msg 3183, Sev 16, State 2, Line 91 : RESTORE detected...


SQL Server 2008 : SQL Server 2008 Performance Tuning

Performance problem with Views defined against Linked Server data sources - I have a problem with a data retrieval query that joins three SQL Views, each view based upon a remote...

Incorrect syntax near '.' - I have just copied following query from 'Scripts' from our site only. when i execute it i get Error : Incorrect...

On which column to create index if no where clause is present ? - Hi Experts, On which column should one create index if no WHERE clause is specified. example [code="sql"] Select * from table_name [/code] No...

Execution plan uses CONVERT_IMPLICLIT , But i have mention CAST in where clause - Hi Experts, This is kind of wired behaviour on part of SQL Server. 1. I have a query whose execution plan...

(RVO) script runs 5 sec with hard-coded value - 45 sec using parameter! - This script below runs 5 sec with hard-coded value and 45 sec using parameter! guys, any ideas why? Hard-coded version: [code="sql"] select gl_acct_detail.* from gl_acct_detail inner...

Index creation help - I was analysing an execution plan before and after creation of an index. Disk IO decreased considerably after creation of...


SQL Server 2005 : Administering

Audit tasks - Hi, I want to create a database that log into a table this type of operations: 1) t-SQL script passed againt the...


SQL Server 2005 : Business Intelligence

Cannot Acquire Excel Connection - Hi guys, Hoping someone can help me out with an issue. I have a package that is a step in a...

align paragraph in ssrs report - Hi, I have a query regarding aligning a paragraph in SSRS We are using sql server 2008 R2 and in teh reports...


SQL Server 2005 : SQL Server 2005 General Discussion

DB Documenter - Hello to all. I am looking for a free database documenter.  One that I can have the result set in a...


SQL Server 2005 : SQL Server 2005 Integration Services

Expression --need 2 digit not 4 - I am trying to create an expression in ssis as follows: "capp"+(DT_STR,4,1252) DatePart("yy",getdate()) + Right("0" + (DT_STR,2,1252) DatePart("m",getdate()),2) + Right("0" + (DT_STR,2,1252) DatePart("d",getdate()),2) + ".txt" Displays capp20080124.txt What I...


SQL Server 2005 : T-SQL (SS2K5)

filter behaving differently from query analyser versus when called through sproc - I am at my wits end to figure out why when i run the below code from within SSMS it...

Subtract value with pervious record - Dear All, I need the to subtract the value from the previous record and insert into another table Declare @temp Table( [DeviceID]...

replacement of format(0,"percent") in SQL - do we have a replacment for replacement of format(0,"percent") in SQL?


Reporting Services : Reporting Services

Multiple Tablix with One to Many Relationships - Hello! I am hoping someone can help me with this - I am quite certain it can be done, I just...

integrating RS reports cross servers - Does anyone have experience with integration reports from reporting services with external applications? We have the majority of our reports running...

Passing table variables to sub reports - We have a report designed using SSRS and this report has a sub report. We pass few fields from the...


Reporting Services : Reporting Services 2005 Development

#Error - I have a report that works perfectly in Visual Studio but when I deploy it to Report Manager running on...


Reporting Services : Reporting Services 2008 Development

SSRS matrix group by error - I have an ssrs matrix with total in rows and months as columns .. something like this . [code="other"] Jan Feb March Total1 Total2...

Preview report without savings - Hi, I use SSRS 2008 R2 Developer. I want to change a report and preview changes but then close without saving them. The...


Programming : General

How would I create this SQL? - I have a table that has a customer number and a date(among other info). I have multiple customer number entries...

Help with this Where Clause! (challenge) - hi, we have ten lines, numbered 1-10. #1 through #5 are bus #6 through #8 are rail #9 and #10 are bus i have a...

Execute a stored procedure from a batch file - Hi, I have to execute an SP from .bat file. What I have done is, I have created a .bat file...

Return a value if record does not exist - Hi, Is is it possible to retun a value from a select statement if the record does not exist in the...


Programming : XML

XPath query against a query plan - xmlns giving me problems - Hi all, I am trying to return the parameters a stored proc was compiled with by doing an XPath query...

XML extraction from database - Dear all, I am very new to XML programming and i have stuck with the format which i need to generate.Not...


Programming : Powershell

Powershell Script help for Scripting - Hello , Please assists me if any one has idead about this . I have script in powershell which script out SP...


Data Warehousing : Integration Services

SSIS PDF - Can I generate PDF extracts while extracting data from SQL databases? I have been able to create .doc,.txt and .html files...

SSIS performance tuning Default Buffer Max Rows - Just a quick question. when you set the default buffer max rows - should this value be based on a) the record...

Convert SQL server datetime to SSIS String using SQL only? - I am getting a datetime column from SqlServer (SS). I want it to appear as a "String" column to SSIS...

Maximum size of SQL variable in SSIS ? - I have a string variable which stores a big set of sql statements, ie insert statements. The insert statement intends...

ssis script task throws error when run from sql agent job - When I run this package from Visual Studio, it works fine, but when I run it from the sql agent...

Import from .Excel .xls failis on 2012 - ( Origally posted in the 2012 forum, but I haven't had any replies. apologies!) Hi, I am seeking some assistance in troubleshooting a...


SQLServerCentral.com : Anything that is NOT about SQL!

Fantasy football 2013 - I renewed the league, you should be getting an email soon. At the moment, there are no open spots, but...

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


SQL Server 7,2000 : Replication

merge replication agent error - Hi, I have a merge replication between 5 servers. Since last weekend, the agent fail for one of the server (located...

To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2013 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com