In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL DBA Bundle Top 5 hard-earned Lessons of a DBA
New! Part 4, ‘Disturbing Development’ by Grant Fritchey, features the return of Joe Deebeeay and a server-threatening encounter with ORMs - read it here.
 
SQL Developer Bundle 12 essential tools for database professionals
The SQL Developer Bundle contains 12 tools designed with the SQL Server developer and DBA in mind. Try it now.
 
SQL Backup Pro Want faster, smaller backups you can rely on?
Use SQL Backup Pro for up to 95% compression, faster file transfer and integrated DBCC CHECKDB. Download a free trial now.

In This Issue

Batch ETL of Multiple Data Files Using an SSIS Foreach Loop Container

An SSIS ETL package created with the Import/Export Wizard uses a Data Flow Task to process one data file at a time. If the need arises, however, the Data Flow Task generated by the wizard can be embedded inside a Foreach Loop Container to batch-process a continuous stream of data files. More »


Moving Data From Excel to SQL Server - 10 Steps to Follow

SQL Server Integration Services provide a versatile way of reading Excel files into SQL Server. A task like this illustrates the advantages of the graphical approach of SSIS. Andy Brown explains. More »


From the SQLServerCentral Blogs - Best practice recommendations for writing Dynamic SQL

Note this is not “Best Practices when USING Dynamic SQL”. These are just good habits I’ve come up with over... More »


Editorial - The Five Year Plan

IT departments won't exist in five years. A bold prediction from the CITE conference last week. It's not so much a prediction of an increase in cloud services, but more the idea that the centralized IT department is struggling with the consumerization of technology, the increasing demands of the business for faster turnaround on projects and prototypes, and the flexibility being demanded by an increasingly technology savvy workforce.

Like most predictions, I take this one with a grain of salt. I don't think five years is enough time to change the face of the enterprise IT groups. Five years ago the first iPhone had been out for a year and was being demanded by users to replace their Blackberries. These days many corporations support iPhones, along with other smartphones, and Blackberries have disappeared from many organizations. However IT has adapted, released some of their control, and even integrated some of their policies into new technologies. When I enabled corporate mail on my smartphone recently, I had to allow some remote management features to get mail, which was fine with me.

I do agree that we will start to see some of our IT functions, especially small projects and POC (proof of concepts) increasingly being driven in departments. There are too many projects, too many different technologies, and too little time to work on them for central IT departments to tackle all these projects. Departmental management won't wait for IT that is stretched too thin, especially if they have existing staff that have some technology skills. That latter factor is something becoming more and more common all the time.

However I can't see central IT disappearing. There are often quite a few systems that are complex, that are too large, too important to let someone manage part time. There are projects that cross departments, or even the entire company, and coordinating tasks and resources between departments is complex. Add to all of this the problems and hassles of hiring good technology people, and I can see projects rising in departments, but being turned over at times to IT to manage.

I do think that central IT groups need to be more flexible, and reduce some of their rules, or adapt them to new technologies and platforms. I expect people working in central staff to be "lent" to departments for projects, perhaps even multiple departments at the same time to help get projects completed, while also gaining some training on the various systems in order to provide support when other staff isn't available. Such as weekends.

» 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. Due to technical issues, we have no podcasts today. Actually, Steve's camera died and he is in the process of replacing it. Hopefully we'll be back tomorrow with the podcast.

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:

How many rows will be reurn by the select statement between SET showplan_xml on and off?

create table address_staging
(clientid int primary key,addressdetails varchar(250));
insert into address_staging
select 100,'hyderbad,india'
union all
select 101,'banglore,india'
union all
select 102,'banglore,india'
;
create table address_oltp 
(client_id int primary key,address_details varchar(250));
insert into address_oltp
select 104,'newyork,usa'
union all
select 101,'banglore,india'
union all
select 102,'banglore,india'
GO
SET SHOWPLAN_XML ON
GO
select * --This query return how many rows
from address_oltp OL
where OL.client_id in (select AST.clientid from address_staging AST WHERe AST.clientid = 101) 
GO
SET SHOWPLAN_XML OFF
GO
drop table address_oltp;
drop table address_staging;
GO

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

This question is worth 1 point in this category: Execution_plan. 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 High-Performance T-SQL Using Window Functions

Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks.

Get your copy from Amazon today.


Yesterday's Question of the Day

I am connected to a SQL Server 2012 RTM server with a database that has the READ_COMMITTED_SNAPSHOT option set to ON. I then execute this query:

SELECT *
FROM dbo.MyTable
EXCEPT
SELECT *
FROM dbo.MyTable
WHERE NOT (NumericColumn BETWEEN -1 AND -10);

In the result set, I get 5 rows. What is the most probable reason?

Answer: None of the above

Explanation: The expression "NumericColumn BETWEEN -1 AND -10" is defined as being equivalent to "NumericColumn >= -1 AND NumericColumn <= -10". This expression can never be true, not even for values that are between -1 and -10. So "NOT(NumericColumn BETWEEN -1 AND -10)" is always true for every value of NumericColumn, and one would expect this query to return no rows at all.

However, there is one exception, related to NULL and three-valued logic. If NumericColumn is NULL, both comparisons will evaluate to Unknown, and the combined expression is also unknown. NOT(Unknown) is still unknown, so for rows with a NULL in NumericColumn, SQL Server will evaluate the WHERE clause to Unknown, and eliminate the row in the result set. This means that the first query returns all rows from MyTable, the second only the rows with NumericColumn equal to NULL. The EXCEPT removes the second set from the first, leaving the rows with NumericColumn equal to NULL in the result set. Apparently, there are five rows with NumericColumn = NULL.

Concurrent updates could cause this to happen in the default readcommitted isolation level, but because the database has the READ_COMMITTED_SNAPSHOT option set to ON, the default isolation level is changed to READ_COMMITTED_SNAPSHOT. This isolation level guarantees that while a statement is running, concurrent changes made are not "seen" by that statement.

Finally, whereas one should never rule out the option of a SQL Server bug when you get unexpected output, it is very uncommon. The explanation above is far more probable.

References: BETWEEN: http://msdn.microsoft.com/en-us/library/ms187922.aspx
EXCEPT: http://msdn.microsoft.com/en-us/library/ms188055.aspx
READ_COMMITTED_SNAPSHOT: http://msdn.microsoft.com/en-us/library/ms189050.aspx
Three-valued logic: http://sqlblog.com/blogs/hugo_kornelis/archive/2007/07/17/the-logic-of-three-valued-logic.aspx

» Discuss this question and answer on the forums

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks.

Get your copy from Amazon today.


Featured Script

Generate N sequential numbers (fast)

Use ROW_NUMBER() to efficiently generate a tally table. 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

Table partition Vs FileGroup - should we create the equal number of filegroups for eqaul number of table partitions. like if one table have the...

Separate one column in many columns - I have a table with the next structure: CREATE TABLE students ( ID_Student INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Name nchar(30), rate nchar(5) ...

SQL Server 2005 : Business Intelligence

The component metadata for the script component could not be upgraded to the newer version of the component - Hi , I have developed SSIS Package in 2008r2 and when i am trying to run the DTSX package in SQL...

Import Dynamic File Name with a Date/Time as the file type (YYYYMMDDHRMMSS) - For some unknown reason, the creator of this file set the naming convention to something I can't begin to fathom......

SQL Server 2005 : CLR Integration and Programming.

Problem with SQLCLR in VS 2012 / Net 4.5 - Hello guys, I have problem that I can't resolve .. I want to make simple store procedure using .net 4.5 and [b]I...

SQL Server 2005 : Development

Hierarchy of managers for specific employee - Hi All, I want to find Hierarchy of managers for specific employee. For example if I run query for David Then...

SQL Server 2005 : SQL Server 2005 General Discussion

calculate Excess/Short and show in one row - this is my data [size="1"] date---------------------------eid-------------timein-------------------------timeout--------------spend-----excess short 2013-01-04 00:00:00.000--26446--2013-06-11 09:44:00.000--2013-06-11 13:20:00.000--08:06:00------5:24 2013-01-04 00:00:00.000--26446--2013-06-11 13:56:00.000--2013-06-11 18:26:00.000--NULL------------4:30 [/size] i want to add excess short

Migrate from MySql to Sql Server -2005 - Hi, I would like to know that what's the best way to migrate the MySQL Database to Sql Server 2005. We have...

SQL Server 2005 : SQL Server 2005 Strategies

Table partition Vs FileGroup - should we create the equal number of filegroups for eqaul number of table partitions. like if one table have the...

SQL Server 2005 : SQL Server Express

using case in where clause having other conditions - i have a stored procedure where paramaters are passed from the application i need to apply case in where clause of...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Table partition Vs FileGroup - should we create the equal number of filegroups for eqaul number of table partitions. like if one table have the...

Execution plan shennanigans...... - Hey folks, I'm looking for some help in understanding why my query is behaving differently under certain circumstances. The query...

SQL Server 2005 : SQL Server 2005 Integration Services

convert datatype DT_STRING to DT_DBDATE in ssis - Hi ssis gurus, i have a source file it contains some colums and in this file one column is string(20061026) format...

Using Script Task to check if Folder Exists - I am creating a new directory/folder in my package, but before I do that I want to check if the...

SQL Server 2005 : T-SQL (SS2K5)

How to create a repeatable +ve integer from a nvarchar? - Hi, I need to create an id from a nvarchar value that is repeatable, sort of like a checksum or...

SQL Server 2005 : SQL Server Newbies

Querying two time columns for sum of datediff - Hi, I have a table with two columns of datetime. The first is considered the 'startdate' and the second the 'enddate'...

SQL Server 7,2000 : General

Index Scan vs Index Seek - What is the difference between index seek and index scan?

hyderabad dba institutes - Dear Friends, i'm working for a development company, as Junior DBA. for oracle concepts,Wilshire and SQL * are very famous in hyderabad. is there...

SQL Server 7,2000 : Replication

Cleaning up Replication Monitor - I have sucessfully removed an old publication and it subscriptions, but I am still seeing references to it in the...

SQL Server 2008 : SQL Server 2008 - General

SQL CLR C# Table Valued Function - only uses half the CPU cores? WHY? - Is there a way to force a SQL CLR C# TVF to use all CPU cores? MAXDOP currently set to...

Select in multiple levels... - Hi, I have a query that now actually does exactly what I want it to and performs well. But the site...

How to update base on parameter vaues in other table - Hi I have two table details and parameter. I want to update Type_score,Amount_score,Age_score acording to the criteria given in the #para...

Partitioning an existing table in sql server 2008 - Hi, I have a table of 600 million data. This table will grow monthly. We do monthly data load to this...

database in suspect mode - Hi i tried to put the database in emergency and then put into single user mode and ran dbcc check db...

Update stats - Hi , In Production server we have executed updatestats for a database manually(which contains 5 tables) on Wensday and it took...

SQL Query - Hi All, I have two parameters start and endweek which shows the measure column weekly from Monday -until sunday . Say 13/05 --- value...

hide databses for special user - Dear friend, i want define a login in server who are dbowner of x database and can not see other databases...

Isolation levels - Hi all, what is the default isolation level for sql server. Does it changed in newer versions. what is the use...

Find Schedule based on business rules - Hi Experts, I need your help on the case defined below. I need to find available schedule based on below defined...

How do you drag a table to view diagram Pane - There must be a way to drag a table into the design diagram pane of a view. What is the...

Seeking Feedback/Recomendations on Auditing, both Simple and Detailed - [b]SQL PLATFORM:[/b] 2008r2 Enterprise 64Bit [b]SIMPLE AUDITING =[/b] Capture the name and date/time for each row that is inserted or updated...

Finding a word\acronym in a field - Hi Guys, How would I go about finding a specific "word" in a field? What is the best way of returning records...

Production / Dev Design - There doesn't seem to be a good place to ask this in the 2008 section... so I am placing it...

Moving databases to new server. NEED HELP please please - Please help me here; I have to rebuild the clusters so the plan is i have to move the sql databases...

Cursor Logic causing Blocking ? - I'm trying to troubleshoot some inherited code that uses cursors and seems to be causing blocking. The cursor gets loaded from...

SSRS Memory utilisation - Hi Team, Reporting Services CPU ulilisation Please suggest

Move dbs Identify Jobs - I’m embarking on a project to migrate some dbs in my environment. Moving the db logins etc is straight forward....

Transaction log size far exceeds database size when running delete - we setup a purge process and had about 20GB of free log space but kept filling up the T-log. After breaking...

The size of a table. (space_used against calculated space). - With sp_space_used you get the 'gross' size of a table. (Used_size for data) Summing all fields with a datalength function also...

How to get results from an SP into a table. - Dear reader, Below is an example to get data from a stored procedure into a table. Is there a more generic method...

Support UTF-8 character encoding - Does SQL server 2008 R2 support UTF-8 character encoding ? Thanks.

SQL Server Corporate Standards - Automated Compliance Checking - Does anyone know of a flexible automated SQL Syntax checker? I want to be able to analyse scripts before they are...

SQL Server Express - automatic Backup - Hi, I have SQL Server 2008 R2 Express Edition on a server. I need to backup my database (small db) on a...

Error on Subquery - Please to resolve this issue. I have this query SELECT (select REB_TAX_RATE from PR_REB_TAX_RATE where EFF_DATE = (select MAX(EFF_DATE) from PR_REB_TAX_RATE group by...

SQL Server 2008 : T-SQL (SS2K8)

Calculate Previous Business Day Exclude Saturday, Sunday and Holiday - Hi, My name is Kashif, I am very new in sql server, I want create a UDF function that will return...

can any one give me the answer of my query why I am getting the error message - use [12] go CREATE TABLE Test1 ( TestID uniqueidentifier CONSTRAINT Test_TestID_Default DEFAULT newsequentialid(), Inserted datetime CONSTRAINT Test_Inserted_Default DEFAULT getdate() ) ERROR MESSAGE Msg 2714, Level 16, State...

Can we call stored Procedure inside a function - Hi, Can we call stored Procedure inside a function Thanks

Identify postcode from multiple fields - Hello, I have a table with five fields used to hold address data. Due to poor practices the users have been...

Simple MAX Query not working - Im confused on why this query is not working.. Data - vend_i dept tot_wgt 1 1 75 2 2 40 3 4 50 3 5...

qry options - I want to do this. select count(*) from ( select col1,col2,......col18 from REVemployee.tbemp group by col1,col2,......col18 HAVING COUNT(*)>1 )a is there a better way of...

Finding duplicate rows with multiple fields for comparison - I've taken a look at the scripts and forum posts on the site regarding finding duplicate rows, but I haven't...

Hi guys this urgent reg int to varchar conversion - I am a fresher dba i got a task where i need to convert int value to varchar please help...

using Correlated subQuery in Join not working - I have a large query that is returning multiple rows that I tracked down to my jornal table. It has 2...

Rollback statement in T-SQL - The functionality of Rollback statement in T-SQL is such a way it rollbacks to the outermost nested transaction. This is...

Select full month number (with 0) - Hi, I use [code="sql"]DATEPART(month, myDate)[/code] or [code="sql"]MONTH(myDate)[/code] the resut is: 2 (if myDate is 2013.02.03). I would like return: 02 Do you have any solution for...

SQL Server 2008 : SQL Server Newbies

SQL to split row by date (split into multiple rows) - I am looking for help with splitting a row into multiple rows based on dates overlapping. As an example, I have...

select not working - Dear All In procedure i am using Remark table to capture all the steps executed (instead of print). From another session...

Choosing the correct driver - I'm writing some documentation on how to connect Access to SQL via ODBC. I honestly always just choose "11.0" when...

Need Help with the Error 'Subquery returned more than 1 value'. - [quote][/quote]

SQL Server 2008 : Security (SS2K8)

Sql Serer Data Protection Tool - Hi, We are looking any Data Security tool for out existing Data for Protection, we would like to do the Data...

How to prevent ANY use of xp_CmdShell? - Please forget whether or not you're pro or con on the subject of the use of xp_CmdShell for just a...

SQL Server 2008 : SQL Server 2008 High Availability

Disaster recovery best practices - system databases - I'm currently working on a new design for DR for my SQL Servers; I have four servers and a total...

How to update LSN Number - Dear All, Request you to provide the some clarification on below issue. We have a backup files with LSN Number like Ex:5,6,7,8 When...

Migrating sql 2008r2 cluster to new cluster - Does anyone know of any good documentation and best practices for migrating to a new cluster?

T-Log Growth (sp_repldone not running) - Recently I encountered an issue where the transaction logs in my production databases have been growing up to 1 TB...

Msg 3013 acting as a mirror database - "Database cannot be opened. It is acting as a mirror database. Msg 3013, Level 16," After restarting the principle & mirror...

SQL Server 2008 : SQL Server 2008 Administration

Copy backup files - Hi Experts, I need to copy backup files of around 500GB from one location to another,need to check the existing file...

unable to connect to server ... - From time to time our front end application gets 'unable to connect to server: abc' error. The error just goes...

ShrinkFile on Varbinary column - Hi, I need a help. am doing shirnk on 160gb( but data is just 30gb) to claim space. This db has...

Different set of access to different users under same schema - I am working on a sql database which has multiple schemas. Lets say schema 'XXX' has 50 tables and i...

How to find out cause of deadlock? - Hi All, 2days ago we have a dead lock. how to find out, for what cause the dead lock got occured?

DDL Audit at Instance Level in SQL 2008 - Hi Everyone, Can you please guide and correct me if I'm wrong somewhere... We need to capture all DDL events (only) in...

SQL Server 2008 R2 Maintenance plans and backups - Hi, I have a customised maintenance plan that rebuilds the indexes and updates the statistics and then takes a full...

IP Address Doesnt turn from offline to online - :unsure: Hi I got a problem, I think it is a little bit weird kind of problem, I haven't find anything...

Having backup problems, and unable to change service accounts... - Two problems, possibly related, both giving me heartburn as I'm going away for 2 weeks and don't want to leave...

Career : Resumes and Job Hunters

SQL DBA Career Path Advice Please - I'd like some advice on a training path to becoming a SQL DBA. I’m not sure what employers are looking...

Programming : General

Inner Join with SubQuery - I am running a Inner Join to the same table I am creating a Subquery on and I don't get...

Programming : Powershell

Open Excel Error Using PowerShell in SQL Server Agent Job - I am trying to run a Powershell script, which opens and modifies an Excel spreadsheet, from a SQL Agent job...

SQLServerCentral.com : Anything that is NOT about SQL!

PolyServe replacement options - While it seems HP still has not made the official announcement, some of us have had conversations with HP representatives...

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

Display all the days in a months in a matrix - Hi, Can someone please suggest, how this is can be done. I need to produce all the days in the month, to...

Report Errors for a view employees on Report Server, Not in VS2008 - I have a report that prints earnings statements for over 4,000 employees. Of those 4,000, 110 are aborting with the...

Rectangle Auto Growing - I have a tablix inside a rectangle (Region 1). Below my report I have another table/rectangle(Region 2). If the number...

Data Warehousing : Integration Services

Unable to Import Null rows - Hello, I am importing the following data into a table. Country Acno ContCode SalesValue SalesDate FRA S000 6030 151.64 2013-06-10 NLD S001 NULL 1315.53 2013-06-10 FRA S003 6027 29.39 2013-06-10 If i run the package manually in Visual...

Data Warehousing : Strategies and Ideas

Help needed for creating the star schema - Hi, We are planning to data warehouse the 2 different projects , to implement this we have to create the star schema...

Data Warehousing : Analysis Services

Fact Dimension issue - Hi All, [b]requirement :[/b] "Fact dimension – load from LatencyDays" Do I need to create this dim separately or else while creating cube automatically...

Non Empty Counting Descendants SSAS vs. Essbase - Hi guys on SSAS 2012, my cube has this Dimension "Policies by Country" with 5 hierarchy layers Country -> Policy Group ->...