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 DBA Bundle ‘Disturbing Development’
Grant Fritchey & the DBA Team present the latest installment of the Top 5 hard-earned lessons of a DBA – read it now.
 
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…

In This Issue

Custom Sequence Numbering

This article shows how to implement low cost custom sequence numbering logic via a cunning use of the the ROW_NUMBER() function More »


SQL Server Integration Services Package Restartability

I've tried using SSIS checkpoints, but have found them to be difficult to configure and, on occasion, unpredictable. Is there a better way to build in restartability in my SSIS packages? More »


From the SQLServerCentral Blogs - T-SQL Tuesday #43 – Plan Operators

This month’s TSQL-Tuesday is hosted by Rob Farley (blog | twitter), who became a Microsoft Certified Master this past December (congrats!).... More »


Editorial - Do You Want a Meritocracy at Work?

Professional sports tend to be a meritocracy in most cases. If your natural athletic talent lets you run faster, jump higher, or perform some other skill better than others, you tend to get more time to play, more money, more recognition, etc. It's not absolute, and at those high skill levels other factors come into play, but for the most part we find bars of skill that a person must exceed to make money at their sport.

In the technology business, I'm not sure that's true. However I want to know how you feel about the idea of skills being used to determine your career advancement.

Do you want to work in a meritocracy at your job?

Keep in mind that this doesn't mean the harder or longer you work, the more money you'll make, or the more promotions you'll get. It may mean that the newest, youngest person at your company might rise much faster than you simply because they're a better DBA or developer.

This doesn't mean you never get a raise, but if you've settled in your job, if you're a Dead Sea technologist, then you might be limited to cost of living increases and the dreary, less exciting work that no one else wants to do.

I know there are problems with determining "better" in many jobs, and it's hard to measure the productivity and value of people in relation to each other in an objective way. However, if we could, then I'm asking if that's what you'd prefer. Would you like to work in a place where the more talented people rise faster than the average worker, regardless of seniority, friendliness with management, or any other non-skill based measure.

Steve Jones

» 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


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. You can also follow Steve Jones on Twitter:

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. They have a great version of Message in a Bottle if you want to check it out.

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.


Question of the Day

Today's Question:

What will be the output of the select statement?

CREATE TABLE [dbo].[IndexTable](
[ID] [int] NOT NULL,
[Value] [varchar](50) NULL,
CONSTRAINT [PK_IndexTable] PRIMARY KEY CLUSTERED
([ID] ASC)
)
GO
INSERT INTO [dbo].[IndexTable]
 VALUES(1, 'value1'), (2, 'value2')
GO
ALTER INDEX [PK_IndexTable] ON [dbo].[IndexTable] DISABLE
Go
INSERT INTO [dbo].[IndexTable]
 VALUES(3, 'Value3') 
GO
ALTER INDEX [PK_IndexTable] ON [dbo].[IndexTable] REORGANIZE
GO
INSERT INTO [dbo].[IndexTable]
 VALUES(4, 'Value4') 
GO
ALTER INDEX [PK_IndexTable] ON [dbo].[IndexTable] REBUILD
GO
INSERT INTO [dbo].[IndexTable]
 VALUES(5, 'Value5') 
GO
DROP TABLE [dbo].[IndexTable]
SELECT * FROM indextable

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

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

SQL Server 2012 Query Performance Tuning

SQL Server 2012 Query Performance Tuning leads you through understanding the causes of poor performance, how to identify them, and how to fix them. You’ll learn to be proactive in establishing performance baselines using tools like Performance Monitor and Extended Events. You’ll learn to recognize bottlenecks and defuse them before the phone rings. You’ll learn some quick solutions too, but emphasis is on designing for performance and getting it right, and upon heading off trouble before it occurs. Delight your users. Silence that ringing phone. Put the principles and lessons from SQL Server 2012 Query Performance Tuning into practice today.

Get your copy from Amazon today.


Yesterday's Question of the Day

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

Answer: As the Showplan_xml is set to on, SQL Server will not execute the T-SQL statement

Explanation: When SET SHOWPLAN_XML is ON, SQL Server returns execution plan information for each statement without executing it, and Transact-SQL statements are not executed.

Ref: http://msdn.microsoft.com/en-IN/library/ms187757(v=sql.105).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

How Long Backups Will Take

When you are running a backup or a restore, this script gives you an estimate of completion time. 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

Will able to shrink MSDB - Log Shipping - Experts, In our production server MSDB database growing very huge can we shrinking the log and data files but in that...

SQL agent configured to call a SP - All, My problem scenarion is: "I have a SQL agent job setup that calls a SP which collects all data and sends...

SQL Server 2005 : Business Intelligence

There is a data source column with no name.Each data source column must have a name. - Hi every body, I am trying to get data in SSIS dataflow task from a strore procedure, but strangly i...

SQL Server 2005 : SQL Server 2005 Strategies

Mitigating concurrency issues when uploading files - Hello, I have a design question. In our project we are giving the users of our website the ability to upload...

SQL Server 2005 : SQL Server 2005 Integration Services

SSIS job failing intermittently, connectionmanager failing - I have an SSIS package running fine 90% of the time, but for stretches of the day it fails over...

How to read data from multiple sheets in excel file in SSIS - Hi, I'm beginner of SSIS. Hereby i wish to know how to create a package which read the data from...

SQL Server 2005 : T-SQL (SS2K5)

how can i select distinct values from 1 column in my query - I would like to select distinct values from 1 column in my query. If I was selecting just 1 column in...

SQL Server 2005 : SQL Server Newbies

Maximum count of allowed ODBC sessions are exceeded. - Hello, I have an application that connects to a SQL 2005 ENT Database. The app (running on a cluster like environment)...

SQL Server 2008 : SQL Server 2008 - General

sql statement containing loop - Trying to accomplish something like the following: Select (SELECT Begin the loop: LTRIM(RTRIM(RIGHT(coalesce(fielda,'') + coalesce(fieldb,'') + coalesce(fieldc,'')) ,LEN(coalesce(fielda,'') + coalesce(fieldb,'') + coalesce(fieldc,'')) ,-CHARINDEX('-',(coalesce(fielda,'') + coalesce(fieldb,'') + coalesce(fieldc,'')))))) when result = '1a' Then 145 when...

Dynamically choose source and destination - Hey Gurus, I have just started working with SSIS and have following task at hand and I feel lost. Can...

Crosstab query for date - I am learning crosstab query and create a sample. It works fine. Since my real project needs date range is mydate...

Get Notification when a new column added to flat file - I am working on sql server 2005. We have some SSIS packages which loads data from multiple flat files. Flat...

Verify Database was restored - Hello I need to verify that a database was restored from a particular backup. I used RESTORE HEADERONLY on the BAK an...

Client Statistics: Total exectuon time - Hi all So, I've got this batch (I hope it is correct to call it a batch, this is just a...

Get 2 column result from 1 field - Hi geniuses Let say I got a table: [code="sql"]Years | Value 2000 | 2 2001 | 4 2002 | 5 2003 | 3 2004 | 2[/code] I need to get the sum of the...

Phase of Recovery (Log Shipping) - Can anyone elaborate on the phases of recovery or point me to an article, for when you recover a database...

CLR function not returning results for GetRunningPackages - Hi there, I am working on creating a CLR that can return the list of running SSIS packages on a given...

Determine the row of data in a table through SSIS - Hi, I have a requirement based on 2 parts to determine a row of data in a table [b]through SSIS [/b]...

What Queries/SPs Are Running Right Now ? - Is there a way to identify actively running queries or stored procedures ? If I am in Activity Monitor, and click...

Three months before current month - I need to set [OrderDate] three months before current month. For example, as if June, [OrederDate] should be "Between '1/1/2013' and...

If one of two values exists they take priority over other values, otherwise select random - Hello, I have the below sql statement, where im trying to return a columns value depending on what value is...

how to caluculate half day - hi i want to caluclate half day my actual data is like this [size="1"] date-----------------------------eid---------------timein---------------------------timeout-----------spend-----excessshort------excess 2013-03-21 00:00:00.000---26446---2013-06-13 09:13:00.000--2013-06-13 3:46:00.000----06:33:00---- 02:27:00-------Short[/size] i want this data [size="1"] date--------------

how to caluculate half day - hi i want to caluclate half day my actual data is like this [size="1"] date-----------------------------eid---------------timein---------------------------timeout-----------spend-----excessshort------excess 2013-03-21 00:00:00.000---26446---2013-06-13 09:13:00.000--2013-06-13 3:46:00.000----06:33:00---- 02:27:00-------Short[/size] i want this data [size="1"] date------------

My row_number() is misbehaving or I'm an not a smart man - OK I've got some sample setup and data below; basically i need a row_number() i've aliased as [BuildingID], to be...

variable with multiple values from SP - hi All, I have a stored procedure where in I pass two paramaters StartDate and EndDate and return the start of...

Edit data in Excel - Just to let you know Excel Database Tasks (EDT) version 2.7 has been released : New features include : One click save Immediate...

Import CSV File - Hi, I have a 4 GB CSV file , which i am unable to import in SQL Server 2008 R2, even I...

Login History - who connected to a database? - Hello people, is there a way that we can display all the people who have connected to a certain database over...

Bulk insert issue - Hi All, I am having an issue with BULK INSERT command in sql. We are having inconsistent data in one of...

ORDER BY the total of a GROUP BY - I have a table which contains order information. I need to SUM up the quantities ordered on an account every...

BCP IN a csv file - Hi All, Couldn't find a plain BCP IN syntax for CSV files on the web or in here. Can someone tell me...

How to format the data in column in sql server - Hi, Please help me, In my table have a one date column. It contains data in different formats .I want...

Import flat files into SQL Server (apart from SSIS) - Which tool do you prefer to use to import flat files into SQL Server tables and why? We're trying to...

Exec msdb.dbo.sp_send_dbmail (zipped and password protected) issue - Is it possible to send a csv file password protected and zipped and sent with msdb.dbo.sp_send_dbmail?

Modifying a system stored procedure - I have inherited maintenance of a SQL Server (2008), and I want to modify some of the system stored procedures....

Need a help in backup Encyption Certificate - HI ..I am having trouble with keeping backup of encryption certificate. I created database encryption key with folowing query. [b]Use Test GO CREATE...

2008 R2 install (Sharepoint integrated mode) - How can set the sharepoint integrated mode (on) for Reporting Service if Reporting Service is already install on the server,...

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

How to connect with SQL Server using http based API - Hi, I am new to this. I want to connect SQL SERVER 2008 with a CRM called Salesforce (without using any...

Extended Events - Filtering based on sql_text - I need to leave a monitor runing long term to capture all calls to procs beginning with 'val_' Extended events seem...

Few tricky Questions about SQL Server 2008 that I came across... - Few Questions about SQL Server 2008 that I came across. [b]Q: Which of the following allows for the truncation of...

SQL Agent not able to start on SQL Server 2008 Developer Edition - Dear All, I have SQL Server 2008 Developer Edition (64-bit) SP1 installed on a Windows Server 2008 R2 machine. For some...

SQL Server 2008 : T-SQL (SS2K8)

Need help with an insert - Hello, I am trying to insert into a log table that stores the reason behind the non execution of a stored...

.sp_send_dbmail sending an XML File dashes in front of -<Label> ?? - Hi using .sp_send_dbmail to email an xml file New at XML files, but my sample to recreate looks like this: <CLUB> ...

Optimize SP - Hi! Next, we need to unify SP 2 has a size pretty damn considerably. =) Let the following scenario: Sp [A] Sp [B] ...

How To Get a row(date) for each month - Hi All, I need to get a minimum month and year from a table and then a row for every...

String to date conversion - Hi all experts, Is it possible in SQL Server to convert a string type to date like [code="sql"] DECLARE @OrgDate Nvarchar(50)='2013-06' select CAST(@OrgDate as...

Using between in where clause from subquery - I am trying to find out how to do the same thing I do in a join in a where...

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

SQL Server 2008 : SQL Server Newbies

Search for relationship - I have a table having company names and another table having Revenueid and amount of Revenue but dont seem t...

inserting a zip file into a varbinary column in a database - anyone know how to write an insert statement that will allow you to write a zip file into a table...

convert into second - Can you please help me converting datediff into seconds. I'm using SQL 2008 , the following command error out with pass...

Move Primary Key Nonclustered Constraint and Clustered Index to New Filegroup? - New to SQL and want to try my hand at moving tables/indexes to different filegroups. Can someone tell me the...

How to delete duplicates/triplicates etc. except for the latest record based on date? - Hi, I have a table (t_account_code) with the following three columns - AccountCode, StartDate and EndDate. The table does not have an...

Policy Management - Hi, I have not created the job syspolicy_purge_history, it has been created by default in sql server 2008. Can anyone please tell...

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

SQL Server 2008 : Security (SS2K8)

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

log shipping secondary database from standby to restoring mode - I am seeing high restore latency in log shipping. All the secondary databases are in standby recovery mode. We don't...

Database mirroring service account - Experts, In our production server we already configured mirroring with trusted domain service account and its working perfectly. currently service account and...

Will able to shrink MSDB - Log Shipping - Experts, In our production server MSDB database growing very huge can we shrinking the log and data files but in that...

Principal & Witness Log - With a Pricipal Server, Mirror and Witness Server, Do you have to have a .ldf on both Principal & Witness Server....

Large scale Transactional replication solution (1200+ Pub & Subs) - So I have been working on a large scale replication project that has suddenly come to a standstill and I’m...

Shrinking the Transaction Log on a database that is involved in Log Shipping - Hi, One of the Sharepoint databases that I Logship to our DR site has got a Transaction Log file that is...

Logshipping Configuration error:3201 - [b]Hi am configuring Logshipping on SQL Server 2008 R2 servers, my primary and secondary both servers are same editions and...

SQL Server 2008 : SQL Server 2008 Administration

Query to determine if SQL Agent is up? - To check for recent failures of maintenance jobs, a person can run a query such as -- Identify SQL Agent jobs...

To create updatable subscription in single side replication - Suppose i have to replicate a table from production to reporting server. Production is publisher and report server is subscriber....

Fragmented pages in tables not changing - I have several tables in a database that shows a high percentage in fragmentation. I created a maintenance plan and...

Bakcup failure with fulltext catalog - Hi , The fullbackup job failed with the below error : Message Executed as user: NT AUTHORITY \ SYSTEM. Allowed for a file or...

Help with Plan Cache Query - I have had times when I would like to get the Query Plan from the Plan Cache. I have Query 1...

Error Runnind DBCC CHECKDB('databasename') - I'm getting the following error when running dbcc on a database server containing three different databases. Two of the commands...

Importing SSIS Package fails with Invalid characters - I've recently installed SQL Server 2008R2 with SP2 and SSIS. When we try import SSIS packages from SQL 2005 we...

SSIS Job - Intermittent Login Timeout Failures - I have been running into some weird problem - I have SSIS jobs scheduled to run on a regular basis. Most...

Programming : General

Inserting result of multiple query into a row in a table - Hi, I have 2 queries that I need the result to appear in one table (one row) and I'm struggling on...

SQLServerCentral.com : Anything that is NOT about SQL!

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

Internet Explorer 10 and SSRS 2005 - I have Reporting Services 2005 running on Windows Server 2003 and IIS 6. Everything looks fine with Internet Explorer 9,...

Hide Item in a Legend based on conditions - Hello, I have a problem related to the legend you can make use of in an SSRS report. I have a...

Report deployment takes very long time - Hi Can someone please help me to fix this issue? When we deploy the SSRS report on new development server (SQL Server...

SSRS Report times out - I have a report that ran fine prior to upgrade to sql 2008 R2 SP1. It has 3 parameters. 1. to...

Report Showing Missing Images - I have a report that shows badge photo images for staff that is working using an external image source for...

Microsoft Access : Microsoft Access

call a SQL Server User Defined Function from Access query - Hi, can I call a UDF (Scalar-valued function) from Access2007 in a query window? I know it works with Stored Procedures,...