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

Stairway to XML: Level 2 - The XML Data Type

Rob Sheldon from SQLServerCentral.com

Robert Sheldon describes SQL Server's XML Data Type, and shows that it is as easy to configure a variable, column, or parameter with the XML data type as configuring one of these objects with any other datatype More »

SQL in the City - Seattle 2012

Press Release from SQLServerCentral.com

Start the week in Seattle off with a free day of training on Nov 5, 2012 with SQL in the City. Grant Fritchey, Steve Jones and more will be talking SQL Server in the Pacific Northwest. Join us and debate and discuss SQL Server the Red Gate Way. More »

Database Security Survey

Press Release from SQLServerCentral.com

A database security survey for a CS student. If you can spare a few minutes, I know he'd appreciate it.  More »

Find out which SQL Server instance is consuming most of the CPU

Additional Articles from MSSQLTips.com

We have a number of SQL Server hosts with multiple SQL Server instances. From time to time we have CPU issues, but we are not sure which instance is causing the issue. How do you find which SQL Server instance is causing CPU pressure on machine with multiple SQL Server instances? Check out this tip for ideas on how to find the correct SQL Server instance which is causing CPU pressure. More »

From the SQLServerCentral Blogs - Interviewing a DBA

Grant Fritchey from SQLServerCentral.com

I’m not a fan of trivia style interview questions. Yes, I ask a few because you have to in order... More »

I've never pair programmed, but I'm not sure I'd like it. As much as I like collaborating, I prefer alone time to work on problems, or think things through and experiment a bit on my own. I prefer brainstorming and discussion sessions to be limited in nature, with the chance to then go work on my own pieces along.

However pair programming has been touted as a way to improve software quality by many people. It's not as popular as some other methodologies, but it still exists out there. I ran across a programmer's reflections on nine months of pair programming that made for a thought provoking read. He looks back at the experience and lists some pros and cons. Better code, more productivity, and lots of knowledge transfer were some of the positives, and would lead many managers to try and push developers into working in pairs.

However there were downsides. As someone that speaks regularly, I think that the strain on my vocal chords would be hard if I had to speak constantly to someone else. The fact that the environment needs to be consistent for both people would also be a problems as I often find strong opinions from  developers in technology as to how they want an environment configured for their tasks. I know that watching someone else control a browser drives me a little crazy. I think I'd definitely need to have a separate machine for many tasks.

As with anything, pair programming makes sense at times. I know that I'd like to have senior T-SQL coders work with junior ones when developing complex queries and explaining how and why they solve problems with certain techniques. Doing this in real time might slow down the senior person, but I think the evolution of one's thoughts as they solve a problem is as important to learn as the inner workings of the actual code.

The Voice of the DBA Podcasts

We normally publish three versions of the podcast each day for you to enjoy. Today there is no podcast due to Steve being ill. Hope to have the podcasts return tomorrow.

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:

How many rows are returned?

CREATE TABLE #TABLE1
(
  Col1 INTEGER
)

CREATE TABLE #TABLE2
(
  Col2 INTEGER
)

INSERT INTO #TABLE1 VALUES (1), (1), (1), (1), (1)
INSERT INTO #TABLE2 VALUES (1), (1), (1), (1), (1)

SELECT Tab1.Col1, Tab2.Col2
 FROM #TABLE1 Tab1 
  INNER JOIN #TABLE2 Tab2 
   ON Tab1.Col1 = Tab2.Col2

By Amit Yenurkar

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.

Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012

There are three queries with DDL statements inside active transactions.

-- Query#1
BEGIN TRAN
CREATE TABLE tblTran
  (
     id INT
  )
ROLLBACK TRAN
GO

-- Query#2
BEGIN TRAN
CREATE DATABASE testDB
ROLLBACK TRAN
GO

-- Query#3
CREATE TABLE tblTruncate
  (
     id INT
  )
BEGIN TRAN
  TRUNCATE TABLE tblTruncate
ROLLBACK TRAN
GO

Which of them will be successful? (answer them in the sequence of execution)

By Lokesh Vij

Answer: Success, Fail, Success

Explanation: Few DDL statements are allowed to be executed inside the active transaction. It is a myth that transactions like TRUNCATE are not allowed inside active transaction.

Ref: http://msdn.microsoft.com/en-us/library/ms191544(SQL.105).aspx

» Discuss this question and answer on the forums

Troubleshooting SQL Server: A Guide for the Accidental DBA

Three SQL Server MVPs provide fascinating insight into the most common SQL Server problems, why they occur, and how they can be diagnosed using tools such as Performance Monitor, Dynamic Management Views and server-side tracing. The focus is on practical solutions for removing root causes of these problems, rather than "papering over the cracks". Grab your copy today from Amazon.

Index Defragmentation

MUTHUKKUMARAN KALIYAMOORTHY from SQLServerCentral.com

Index Defragmentation is one of the most important DBA tasks. This will significantly improve query performance.  More »

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

SQL Server 2005 : Administering

Table partition - Multiple filegroups vs Primary filegroup? - Hi All, Can someone tell me the advantage of doing the table partition on multiple filegroups or on primary filegroup? Thanks in...

Is there any way to cancel redo logs in mirror server ? - Hello, Safety = FULL. No witness. Principal goes down, and there's a long redo queue in mirror and will take long time to...

Monitoring scheduled backup jobs - Good afternoon, Apologies for this long post but I would really appreciate some feedback on my approach to solving the following...

SQL Server Memory - HI Experts, I have a small concern again on SQL Server Memory issue as one of my prod server runs on...

select query - i need a select query for below. there is 3 date colums in a table. how to find out the greatest value...

SQL Server 2005 : Backups

Backup scripts vs. Maint Plans - Hi all, What are advantages and disadvantages of Maintenence Plans for backups vs backup scripts ? Our version is 2008. Thanks

Could not locate file 'SAML' for database 'SAML' in sys.database_files - Good Morning - I know this topic has been beaten to death over the years but I can't shrink a large...

SQL Server 2005 : Business Intelligence

Adding an ESRI Map to an SSRS Report - Hi I'm using Windows XP Pro with Office 2007 & have installed SQL Server Mgmt Studio 2008 R2 with Business Intelligence Development...

SQL Server 2005 : Data Corruption

DBCC CHECKDB with Data_Purity consistency errors - DBCC CHECKDB with Data_Purity consistency errors This is the first time I ran this command and had errors. I am new...

SQL Server 2005 : SQL Server 2005 General Discussion

Data Migration Options - We have SQL Server databases located on different servers for our hosted web application. Each database can house multiple 'customers' along...

SQL Server 2005 : SS2K5 Replication

Distribution database is too big; - Distribution database is 28 GB and growing 1GB per day. I have changed the retention period from subscription never expires to...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Self referring join condition (tblx.col1 = tblx.col1) alters perf. - WHY? - Can someone explain [b]WHY[/b] adding the self referring join condition on acct.BillingType decreases the Estimated number of rows by a...

SQL Server 2005 : SQL Server 2005 Integration Services

Export To Excel - Hi Guys, I am new to SSIS,Could you please help me to implement(step by step) below task. Task: I need to export data...

Transfer Data from one database table to Another database table - Hi, I have two databases – DBSample1,DBSample2 Both databases contains same table names(TabSample1 and TabSample11) and same design Need to copy the...

No Progress while Update - Hi Friends, I am about to update some rows so i use OLEDB command component. When i do this, my...

SQL Server 2005 : T-SQL (SS2K5)

Insert, Select on same table - I have two jobs running. Job2 is kicked off after 3 mins of start of job1. Job1 has a long...

SQL Server 2005 : SQL Server Newbies

SQL Error problems - Hello, I'm not computer expert either understand programing language. I want to understand why I'm often encountered SQL error connection...

SQL Server 7,2000 : Globalization

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. - Hi All, We have developed a web appln using ASP.Net 2.0 / Sql Server 2005. In a web form, while inserting a...

SQL Server 7,2000 : T-SQL

load sql server table data to flat file with out applications - Hi, This is vivek, i have one requirement load sql server table data to flat file(text file) with out applications.please any...

Getting the last entry based on a previous entry. - Hello, We are still on SQL 2000 and I need help with a query. We have a table named Reporthistory that records...

Match firstname lastname - Hi, Could anyone help me with T-SQL to match names. I have to match names from one database to bank account...

SQL Server 2008 : SQL Server 2008 - General

Fetching data from an SQL Server in to an Access database - Hi Everybody, I am trying to fetch some data from an SQL Server Database. I have a query, but it keeps prompting...

cannot get sql intellisense working after vs2010 update - from scouring the net, this seems to be a big problem, but i've yet to be able to cure it...

Database analisys tool - Hi, I'm planing on doing a database analysis tool to deploy to our customers and determine their problems. We have customers complaining...

How To Master a Database in SQL Server 2008 - Hi All, I am having few Databases in which i have to go through each and every table and stored procs...

Creating dynamic Stored Procedures(changing dates) - Hi Guys, I have an MIS system where I have to run 15 queries and update 15 sheets which takes a...

Internals of data insertion into the table - Hi, When we insert the data into a table, what happened exactly(process)? How the data will be inserted? For any modifications sql...

SQL server in loop Evaluation - Hi, I have an assignment to check the consistency of SQL server database for different conditions.I am having some 200+ columns...

SQL server in loop Evaluation - Hi, I have an assignment to check the consistency of SQL server database for different conditions.I am having some 200+ columns...

excel help...in ssis... - Hi all, i have to move invalid records(data type mismatch) from excel into error log and have to process only...

Job History clearing - Hi there I have a SQL Server that clears it's Job History every morning sometime after 5 am. it clears out...

db_owner and sysadmin unexpected behavior - I noticed specifically with SharePoint databases that just because an account is sa doesn't necessarily mean it's also db_owner I know...

DatabaseMail problem - I have 2 instances set up on a cluster. On one of the instances database mail is working correctly on...

Is this a bad idea? sp_addserver 'namedInstanceAlias' - So, we have a situation where we had to add a named instance, but set up a DNS alias to...

Understanding the difference between Join and Where filters - SQL 2008R2 - I'm new to the TSQL world (coming from visual basic) and am working with large tables (125+ million rows) and...

Behavior of "NOT IN" - Hi Here is sample script: create table a(id int, nm varchar(200)) insert into a select 1,'a' insert into a select 1,NULL select * from a where nm not...

How to download SQL 2008 - Hi, I would like to download SQL 2008 x64 for test CRM Application. But,in official website has an SQL 2012. How...

System tables - hello to all, i would like to ask if ever there are ways to copy the data from system tables...

Reindex daily Job.. - 1) How can I check the Fragmentation of the indexes. 2)How the scanDensity and Fragmentation are related? Thanks

Maint. Question...Alter Index THEN UPDATE STATISTICS FULLSCAN, COLUMNS? - I have probably a silly question but I wanted to throw it out there anyways... In developing a methodology of reindexing...

Converting DTS to Stored Procedures - Hi, I have DTS packages and i need to convert those DTS packages to Stored Procedures in SQL Server 2008 R2. Some...

SSIS V's Jitterbit - We have a couple of in-house servers running Jitterbit along side SQL Server 2008 R2 and was wondering if anyone...

How to get the resultset datatypes of a stored procedure ? - Hi, How to get the resultset datatypes of a stored procedure ?

SQL Server 2008 : T-SQL (SS2K8)

CLR Funtion To split the cama separated string. - Hi All, I need a CLR function to split the "," separated string. Ex: string like 1,2,3,4, Result: Value ---------- 1 2 3 4 Thank’q

Restart runningtotal when predetermined value is reached - Hi, Im trying to create a running total based on FieldToSum that will restart everytime runningTotal <= 25 and count...

Performace Improvement in Table Valued Function - Hi Guys, Could you Please help me to improve the function Performance.This Function is used in allmost all procedures in...

sum every two number combination - Let's say your coworker gave you the results of a query, and noted he/she did not include SalesOrderID numbers 43674...

Consolidating records - TSQL problem - Good afternoon, I've been working on a project to eliminate duplicates from a record set containing contact and address details. (ChrisM@Work/Home...

Get one record on each category - Hi. i have two table Projects and ProjectCategory. They are related with each other. Projects Table ProjectId PK Name CategoryId .. .. ProjectCAtegory Table ProjectCategoryId PK CategoryName .. i want to get...

Multiple Space into one + 65,33,17,9,5,3,2 - All, I have recently read Jeff Moden's Multiple space into one space article and i saw M's reply on this topic. He...

select rows where values appear consecutively - Ok, I have a question. I need to select data from a table where a specific value appears for a...

SSMS Query Logging - We have a user who has come from an environment (SAS) where all of the queries she ran were logged,...

NEED HELP IN PIVOT - I HAVE below TABLE with colum names proposer,approver,designation PROPOSER APPROVER DESIGNATION ---------------------------------- kiran ARUN CFO kiran kranthi HOD kiran PHANI CEO kiran SANGEET...

SQL query eliminating rows problem - Hi all! I am trying to solve an issue and wondered if you could help, I'm relatively new to t-SQL and...

Addition Of Digits - Hi, This was the question ask to one of my friend during an interview. He was ask to perform the addition...

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

SQL Server 2008 : Working with Oracle

MSSQL Linked server to Netsuite cloud - Hi all, I'm having an issue to create a linked server connection to netsuite on the cloud. They supplied me with an...

SQL Server 2008 : SQL Server Newbies

Question about Shrinking - Hello all, I have read loads of articles about the danger of shrinking and I am under the impression that...

Problem with Grouping and max() - Basically the data is access data to a building. I want to establish if the person is in or out...

Creating dynamic SP(date changes) - Hi Guys, I have an MIS system where I have to run 15 queries and update 15 sheets which takes a...

read multiple fields with common delimited data into either a temp table or table variable without using cursors - if I have a table with fields which contain common delimited data in them, is there a way I can...

How Do You Keep The "Pieces" Of A Complex Statement "Organized" - I just finished a complex SQL statement; and, yes, I know there are even more complex ones. When I am faced...

SQL Server 2008 : Security (SS2K8)

Local group permissions issues - We restore Production backups to our UAT server nightly to keep data refreshed. The devs are given access through a...

SQL Server 2008 : SQL Server 2008 High Availability

Index !!!! - Now Daily at morning 6:00 am the server (Database) does not have any transactions.. i mean its on IDLE state...

HA on Active / ACtive and DR on DB Mirroring - I have 2 SQL Servers in HA set up in Active / Active cluster where we have one DB - named XYZ....

Active /Active configuration - Hi All, I configured SQL cluster configuration successfully as Active /Passive but i am not understand how do i configure...

OS Error when configuring Log Shipping - I am attempting to set up Log Shipping, and after going through the configuration wizard, I continually get the following...

Mirror and witness becomes disconnected, won't reconnect - Hello all. I have several setups with principal, mirror and witness set to High safety/auto failover. On rare occaisions, the...

replication or monitor breaks after changing sa password - Good Morning I am about to open a case on this with MS but thought I would post here first. (great...

SQL Server 2008 : SQL Server 2008 Administration

Selecting Collation for International application - We are installing a new SQL Server 2012 Enterprise that is to be deployed in different countries around the world....

Non-Clustered index updates - Hi All, Basic question is: When does the non-clustered index update (when we do any updates on table)? We have created a...

Question about sys.dm_os_process_memory - Is this a valid query to tell the total amount of memory used by the sqlservr.exe process regardless of whether...

Move DB from 2008R2 Enterprise to 2008 Standard - I am moving a database via backup/restore from a 2008r2 Enterprise server to a 2008 Standard server. Since it is...

SQL server performance-- memory / I/Ocaching - hello nerds, I have a request.. Can anyone help me in getting the large queries being run on sql server by...

How to know SQL Server Memory Utilization - Hi All, We have SQL 2008R2 Standard Ed.(64 bit) installed on Windows 2008 R2. Physical RAM on Server is 48GB. We have...

SQL Server Audit - I have a requirement 1. when a user logon I would need to log the below information in a table....

Network IO Waits threshold... - Can anyone guide me on how much threshold sholud I set for Network IO Waits... I need to setup alerts if...

On SQL 2008 Replication Monitor Refreshing Distributor takes long time - We have SQL 2008 instances on Windows 2008 OS, one of our production db has 4 publications and configured with...

Career : Certification

70-461 - Hi, Id like to take this exam and looking for some good prep.. My T-SQL skills are basic at present...

Programming : Powershell

French characters into SQL Server - Hi all, I use Powershell to deploy DB scripts. In one script file, I have French accented characters. When I call Invoke-sqlcmd...

SQLServerCentral.com : Anything that is NOT about SQL!

The SQL Saturday Thread - I figured I'd start a thread (I don't think anyone's done so) for all things [url=http://www.sqlsaturday.com/]SQL Saturday[/url]! I'm heading down to...

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

Help with SSRS - I'm new to SSRS and T-SQL reporting. The below query works fine but I want the field R.FLDDATEDUE multiple times...

AutoRefresh fails after midnight on reports with date parameter - Hi We use some reports in our NOC which have date parameters with defaults set to the current day. These reports...

BarCode Creation - How to Create Postnet code in Rdlc Report (Visual Studio 2010)..

Page Break problem in reporting services when I use matrix - Hi, I have the blank page problem in my report. In the report I have a table with contains 50 rows so the...

Database Design : Design Ideas and Questions

Link table with datetime field - Hello, I seem to recall from my database design classes a while back, being told that a link table with just...

Data Warehousing : Integration Services

SSIS Error - Recently we upgraded SQL 2005 to SQL 2008 R2 , while executing the package we are getting the below error Microsoft...

How to log "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". - Hi All, I am using custom error loging method to log SSIS errors. I am using "System::ErrorDescriptionsystem variable for logging the...

One common Question regarding SSIS Interview - Its been while have not given any interview SSIS.Just preparing my future course of action One common questions that comes...

SSIS - Connection manager Variable file name - Hi Guys, I'm new to SSIS & I need some assitance around the Flat File Manager concept. I've designed my whole package and...

SSIS - Connection Problem when outputting to an Excel Binary Workfile (.xlsb) - Hello All, I'm just about trearing my hair out with a Connection problem when I'm writing to an Excel Binary Workfile....