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

Disaster Recovery Week

Steve Jones from SQLServerCentral.com

This week we highlight the practice of being prepared for disasters at SQLServerCentral. More »

Disaster In The Real World - #2

Andy Warren from SQLServerCentral.com

Back in April Steve Jones wrote up a disaster at work. Andy had one this week and wrote up the story too. Copy cat! Pretty soon everyone will be having a disaster and writing a story about it! Give these guys credit for letting you see what happens when it ALL goes bad. Disaster recovery is hard to sell and hard to do, reading the article might give you an idea that will save you some time and/or data one day.  More »

Win a Collection of SQL and .NET Books. And a Bookshelf

Press Release from SQLServerCentral.com

After winning a number of awards for our software, Red Gate is giving away books to 300 people as a celebration. More »

Planning for Disaster

Additional Articles from SimpleTalk

There is a certain paradox in being advised to expect the unexpected, but the DBA must plan and prepare in advance to protect their organisation's data assets in the event of an unexpected crisis, and return them to normal operating conditions. To minimise downtime in such circumstances should be the aim of every effective DBA. To plan for recovery, It pays to have the mindset of a pessimist.  More »

Database Deployment Cribsheet

Additional Articles from SimpleTalk

As part of Simple-Talk's long-running Cribsheet series, they asked William Brewer to write a guide to deployment that described in general terms what is involved in the deployment of a database application, and the sort of issues you're likely to come up against. More »

Years ago the company I worked for would patch the majority of our servers one Friday night each month. The Microsoft patches for the month, and other software patches, would be bundled up into SMS (Systems Management Server) packages and deployed to thousands of servers. We had an amazing administrator who built these packages, and it was quite an experience to walk into the data center and hear thousands of servers shut down and fans spin down for a moment before rebooting.

That was the smoothest deployment system for vendor patches, but I worked in another place that deployed changes to a web application (the system that generated all our revenue and paid our salaries) every Wednesday night. We did this for over 18 months, over 70 deployments, pushing out changes on a consistent basis. We only rolled back three times, but we did roll back three times.

Other jobs have had various levels of success at deploying changes. Many of the companies worked with the ad hoc, patch one machine at a time manually, process. Not very efficient, and probably not even possible at the numbers of systems many companies have today. I wanted to ask you this week how successful your company is.

How often do you have problems during the deployment of some software change?

Do you think that you have issues more often than not? Do you roll back when you have issues? I doubt that. In my experience, even broken deployments are often pushed forward, with the expectation that developers, vendors, or admins will fix things over the next few days. I've never thought that was a good plan, since we often fine broken features limping along for months or years, but organizational momentum can be hard to slow down.

Let us know if you think you work inside of a smooth, strong deployment process, or one that's more fragile and brittle.

Steve Jones from SQLServerCentral.com

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

• Watch the Windows Media Podcast - 20.1MB WMV
• Watch the iPod Video Podcast - 18.3MB MP4
• Listen to the MP3 Audio Podcast - 3.7MB MP3

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.

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

Today's Question:

I have the following table:

CREATE TABLE [dbo].[Customer](
     [CustomerID] [int] IDENTITY(1,1) NOT NULL,
     [FirstName] [varchar](25) NOT NULL,
     [LastName] [varchar](25) NOT NULL,
     [PhoneNumber] [varchar](15) NOT NULL
) ON [PRIMARY]

It contains the following data.

CustomerID FirstName  LastName   PhoneNumber

1          jeff       Jones      555-440-2323

2          Steve      Moden      555-440-2323

I have the following settings

SET ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,
QUOTED_IDENTIFIER,ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF

I create the following VIEW

CREATE VIEW V1 WITH SCHEMABINDING
AS
SELECT CustomerID,FirstName,LastName,PhoneNumber
FROM dbo.Customer
GO

CREATE UNIQUE CLUSTERED INDEX VnameInd ON V1 (LastName) 

I then execute the following:

SET NUMERIC_ROUNDABORT ON

UPDATE V1
 SET PhoneNumber = '999-888-4343'
 WHERE CustomerId = 2

Is the phone number for Steve Moden Updated?

By bitbucket

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

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

What is the count returned by last two SELECT statements respectively?

CREATE TABLE TblA1
  (
     col1 INT IDENTITY,
     col2 VARCHAR(10)
  );
GO

CREATE TABLE TblA2
  (
     col1 INT IDENTITY,
     col2 VARCHAR(10)
  );
GO

INSERT INTO TblA1(col2)
VALUES      ('A'),('B'),('C'),('D'),('E'),('F');
GO

INSERT INTO TblA2(col2)
VALUES      ('A'),('B'),('C'),('D'),('E'),('F');
GO

SET IDENTITY_INSERT TblA1 ON;
GO
SET IDENTITY_INSERT TblA2 ON;
GO

INSERT INTO TblA1
            (col1,
             col2)
VALUES      (7,
             'G');
GO

INSERT INTO TblA2
            (col1,
             col2)
VALUES      (7,
             'G');
GO

SET IDENTITY_INSERT TblA1 OFF;
GO
SET IDENTITY_INSERT TblA2 OFF;
GO           

SELECT COUNT(*) from TblA1;
SELECT COUNT(*) from TblA2;

By Lokesh Vij

Answer: 7,6

Explanation: IDENTITY_INSERT property is set to ON only for table TblA1 as only one table in a session can have the IDENTITY_INSERT property set to ON.

Hence, insert statement(inserting values col1=>7,col2=>'G') will be successful when it tries to insert in TabA1 and on the other hand fails to insert into TabA2 with the following error message:

"Cannot insert explicit value for identity column in table 'TblA2' when IDENTITY_INSERT is set to OFF."

Ref: http://msdn.microsoft.com/en-us/library/aa259221(v=sql.80).aspx

» Discuss this question and answer on the forums

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

Overview data from all tables

Tony Trus from SQLServerCentral.com

Updated script to gather overview data from all tables More »

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

SQL Server 2005 : Administering

TempDB Growing MDF - Hi I am responsible for a SQL Server in an emergency Organization in Austria. We use MS Sql Server Version 2005 (9.00.3054.00) Since...

changing assigned sql memory - any impact? - I was in an impression that if i change the assigned sql memory it shouldn't impact any online operation. However...

Dynamic SQL, Ownership Chaining, and EXECUTE AS - Hi all, I've got a procedure which is using dynamic SQL to construct an SQL statement, and then execute the statement...

Intermittent Connectivity/DB availability issue - Hi All, I've got a problem intermittent Connectivity/DB availability issue Here's quick low-down on the system Windows 2008 R2 SP1 SQL 2005 SP4...

How to notify when SQL Server Agent stopped automatically? - Hello all, I have many jobs running and for some reason sql server agents stops automatically not sure why..Can someone tell...

Find out why a maint plan failed - Hi all, a maintence plan failed last night and im trying to find out why. now apart from the fact that...

Getting Ntfs error - Hi, We have 2 node cluster setup on Windows 2003 x64 with SP2 and we are ready to install SQL Server...

SQL Server 2005 : Backups

Netapp SnapManager for SQL - Hello, Is anyone using Netapp's Snapmanager for SQL to do backups/restores? Have you had any issues with it? All comments are...

SQL Server 2005 : Business Intelligence

SSAS Issue : Partial data showing up when I browse - Hi, This could be a silly issue. I have a location dimension attached to my sales cube. When I browse the...

ssis scheduling package prob - hi friends i have a small doubt in ssis plz tell me how to solve issuse. i developed one package in...

SSIS to get external data, Security risk? - Hi All, I want to use a SSIS package to grab data from an external suppliers mySQL database. I have read...

MDX Date ranges - Hi, I've got a query that i'm working on that's giving me a sore head. I need to display the first 3...

SQL Server 2005 : Data Corruption

CHKDSK on a SAN Volume - I need a little direction. I have a clustered SQL db (Sql 2000 / Win2k). I have errors in the log for corruption...

SQL Server 2005 : SQL Server 2005 General Discussion

Need help on creating SQL statement for deriving Consecutive worked days. - Hi all, Anyone can help me in deriving the sql statement to achieve the new column CONSECUTIVE_D? Consecutive days should be solely...

Invoked Schedule Id of the Job : Urgent! - Hello: When a job step is being executed from SQL Server Agent, I need to determine the "schedule_id" that invoked the...

SQL Server 2005 : SQL Server 2005 Strategies

How to notify through DB MAIL when sql server agent stopped automatically? - Hello all, Can someone tell me how can we send email when my sql sevrer agent stops automatically, i have my...

SQL Server 2005 : SQL Server 2005 Integration Services

need to create ssis package to run crystal report - need to create ssis package to run crystal report(s); I want to read a sql table and return values as...

SQL Server 2005 : T-SQL (SS2K5)

Delete trigger - I'm not sure what is going on here - if my brain has gone on Christmas holiday early or what, but...

How to return a certain percentage of rows (NOT TOP PERCENT QUESTION) - I want to return all values that represent x% of the total values within a table. Here's some test data: [code]create...

GREATEST and LEAST function - Hi, I'm looking for a function in T-SQL for getting the biggest or the smallest value between two or more columns...

SQL Server 7,2000 : T-SQL

Update a table by compare values in two rows from a second table - Couldn't think of a more specific subject for my query so apologies in advance. Would really appreciate help with this. I have...

Retriving host IP Address via T-SQL - Guys/Gal, I need help, I am going nuts... I am trying to find a query which will retrieve the IP-Address of...

SQL Server 2008 : SQL Server 2008 - General

index legth - let say that we have a varchar(100) column and a index on that column in mysql I can set the index...

When AutoShrink will occur in SQL server 2008? - When AutoShrink will occur in SQL server 2008? When autoshrink set true... But in SQL server 2000, we can schedule the...

remove users group from drive:\Program Files\Microsoft SQL Server folder - Hi Guys, sql server 2008r2 windows 2008 Is it recommended to remove user groups from drive:\Program Files\Microsoft SQL Server folder and data folder? It's...

Exporting a TEXT (or long varchar) column to an Excel file with the OPENROWSET command. - Hi, I'm using the OPENROWSET command to export data from a table into an Excel file. I'm using this command because...

Request - I hope I am not being too cheeky posting this here, but I would appreciate some page views on my...

Need Help in SSIS - while loading 10000 records if the SSIS package got failed at 9000th record,how do we reload from 9001 record ,can...

xp_cmdshell - Hello there, i would like to delete all the files in a directory in another server using xp_cmdshell EXEC xp_cmdshell...

How to update sql server 2008 database from sql server 2000 database on windows server 2008 ? - I have a database developed using sql server 2000 and need to have a same mirror image of database on...

Best Way to avoid duplicate rows insertion - Hi, I am using sql server 2008 enterprise edition. I need to insert large number of rows to my table frequently....

Transaction is SSIS - Hi Friends, I am trying to implement transaction in SSIS for the first time. We are going to migrate few records...

Restrict insert or update when taking backup - Hi, Is there any way to restrict insert or update or delete operation while taking backup.. Thanks in advance..

sql server traceflags - Hi, does anyone know where on Microsoft site could we find a completed list of traceflags for SQL Server 2008...

Not in sql error - Hi guys plz help I am getting below error when i include not in (6,8) in my sql statement. Msg 8180,...

Deadlock on stored procedure - I have a stored procedure(parent) which include to many if cases and in each case calls another stored procedure (child). In...

Query Performance - Advice needed on small query -- plan attached - I'm not sure why it is using index scans and not seeks. I have indexes on all the tables. [code="sql"] SELECT T.TXID,...

Purging Data Increases IO performance? - TLDR: I purged about 50% of the data in a data warehouse during an upgrade and 3 days later, the...

Using Find and Replace with Regular Expressions to match one word and exclude another - I am using Find and Replace in SSMS to search for any line in my T-SQL code that contains a...

Loop through table to pass variables? - I'm looking for a way to run the each of the values in a table to populate a single SELECT...

Enhanced Variable Declaration in SQL Server 2005 Mode - In SQL Server 2008 the new enhanced variable declaration functionality is working nicely (allowing you to assign a value to...

Checking Backup Files - Hello, Having read an article about restoring from your backups and the things you can do to minimize potential problems therein,...

Select only mismatching column NEED HELP ! - Dear all , I just need to select only the columns that don't match the parent row eg row1 id col1 col2 col3...

Report to have servername as parameter display data - Hi all, Iam designing report to display the index stat of the server we choose from the dropdown list box. I...

Need assitance interpreting Execuation plan - Hi, I have a query that needs to be optimized. I have inserted a Tally table to untilize its indexes but...

delete top(8000) takes 1 second, delete top(9000) takes 42 seconds !! - Hi, Delete top(8000) takes 1 second, but delete top(9000) takes 42 seconds, any ideas why? In management studio, if I do the...

Optimization advice for huge tables - Hello, I need tuning advices for large tables query performance. I have 3 big tables and I need to join these 3...

Problem in Configuring Mirroring - Dear, I am trying to configure mirroring. Principal server is [b]Serv1[/b] and Mirror server is [b]Serv2[/b]. Both servers are running SQL...

Distributing the greatest value by rank in SQL - This seems as though it would be terribly simple...but I am stuck. I need to distribute as "average sales" value among...

SQL Agent Job succeeds even though step should fail - Does anyone know why a Transact-SQL job step that executes a stored procedure and returns a "Query timeout" response continues...

SQL Server 2008 : T-SQL (SS2K8)

how to access data base table from two different servers - hi, In my work environment i have faced problem with getting data from two different sql servers like below server A(ip:1.1.1.1) server...

TSQL question - - I have a question for which I want to know how it works internally Lets consider below query Select * from Student where...

select for update - Hello, I have a problem with selecting rows which I want to update. Select sets a read lock on the rows and...

FULL text search - Do we get any benefit if we choose to use full text search over like operator. see the below queries...

Simular to PIVOT Table, but not - Hello Everyone I am having a real time with this one. [code="sql"] --===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#LabValues','U')...

Query for a plus sign - Hello Everyone I am attempting to query a column that the data has a plus sign. I know there is data,...

ORDER BY Should be same as my input in IN() - DECLARE @T TABLE(ID INT,NAME VARCHAR(10)) INSERT INTO @T VALUES(1,'PPP') INSERT INTO @T VALUES(2,'AAA') INSERT INTO @T VALUES(2,'ZZZ') INSERT INTO @T VALUES(3,'XYZ') INSERT INTO @T...

Filter rows based on two columns - Hi there, Apologies for the elusive subject title. I'm trying to filter only rows on one field only when another field...

NPV and IRR in SQL - Years ago I wrote a piece on the Net Present Value (NPV) function in SQL. If you don't know or...

Dynamic SQL - Hi, has anyone got an example of a Dynamic SQL and what is it and why it would benefit using this? Thanks

Update statement question - I'm doing a DB review and have always been taught to write updates with a Join like using this syntax. [code="sql"] Update...

merge statement - to get a row count - I use a merge statement to do incremental load to the table. I use it in a stored procedure that is...

SQL Server 2008 : SQL Server Newbies

Applying Service Packs to SQL server - Hi, I want to apply SP3 to our current SQL Server 2008 instances which are currently on SP1. Our server is currently...

need help with Xml file - Hi All, I just started this job liike half an year as a Data Analyst. I have a xml file...

pattern matching? - Hi all, I have a task to clean up data in one of the tables. The column name I need to...

Receiving an error with replication - Hey Guys, I'm running SQL server 2008 R2. The Programming department wants to have a database replicate to a different...

Reduce database size - Have been asked by a customer to reduce the database size to latest 10% of its data. Not sure how...

SQL Server 2008 : SQL Server 2008 High Availability

Criteria for switching between synchronizing and synchronized state in database mirroring - Hi Everyone, We have a Asynchronous Mirroring in SQL SERVER 2008 R2. What is the criteria that causes switching between synchronizing...

SQL MIrroring - I recently configured on database in mirroring with automatic failover.I want to push 10 GB of data into primay server...

transactional Replication- Two log files - For some reason we have two log files on one of our production database. We have transactional replication setup on...

Vertical Partitioning with transactional replication - I have a publisher and a subscriber database under transactional replication. The schemas are identical. One of the tables has...

SQL Server 2008 : SQL Server 2008 Administration

Can a cluster instance be changed from SQL Server 2008 Enterprise to Standard? - One of our DBA's installed an instance of SQL Server 2008 R2 Enterprise Edition onto our multi instance cluster. It...

fileshare script - Can someone provide a script(any type batch,powershell....etc) for share the all files of a drive . Example: A user will not have...

Deadlock due to intra-query parallelism - Hi We have been having deadlocks a lot and to track them I enabled Trace Flags 1204 and 1222. The output...

DB Mail issues with DLL - Dear All, I have been facing the problem when I am sending mail , in example in Test mail I am getting...

Career : Certification

Should I go ahead with 70-432 - I have been preparing for MCTS 70-432 for the past 1 year. I was planning to take the certification exam...

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

Microsoft Certified Master - There's been a lot of debate recently about whether certifications are worth doing and whether they provide you or your...

SQLServerCentral.com : Anything that is NOT about SQL!

Fantasy football 2012 - Only 2.5 short months to football, can you believe it? I'm not ready, hardly feels like it's summer time yet. You...

Talking baseball - Okay, a topic that has NOTHING to do with SQL . . . Came across [url=http://www.sqlservercentral.com/Forums/Topic447796-4-1.aspx]this link[/url] describing the SSC point scoring descriptions,...

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

Creating Optional Filters in SSRS 2005 - Hi all, In my report i would like to create an optional multivalue filter for @accessVar. However my biggest problem is...

How to create date range parameter in RS2005? - Hi folks, I have created to parameter. @StartDate and @EndDate withe this statement: Select * From table where ENTRY_D BETWEEN @StartDate and @EndDate. Firstly...

no calendar icon on RS 2008 date parameters - Hi, I'm having a problem on one of our clients PC. Seems like the calendar icon on the date parameter is [b]not...

Remove 'select a Value' from drop down of report manager - Hi experts, Is it possible to remove 'select a Value' from dropdown in report manager?. I set the default value for parameter...

Database Design : Hardware

Lots of Memory or Fusion I/O - My first post so hello to everyone for the first time :) I work for a company who make a large...

Data Warehousing : Integration Services

Package fails with MaxConcurrentExecutables > 1 - Hi Guys, I have a package that runs 21 file loads. The process is run an SQL task to reset some...

to check file exist or not in sharepoint using script task in ssis 2008 r2 - Hi All, I have sql server 2008 R2 installed on my system. I am trying to check if file exist...

SSIS 2012 what are main differences with SSIS 2008 R2 ??? - Hi to all. I currently use SSIS 2008 R2. I have to change to NEXT VERSION 2012. I have a lot of SSIS...

SSIS Package Won't Stop Running and Locking Files - We are running SQL Server 2008 R2 on a 64bit machine, I have several SSIS package that are run via...

SSIS SQL 2012 OLEDB Source defaults all Date columns to DT_WSTR - Hi, I have a data flow task that moves date from one SQL 2012 Database to another SQL 2012 database using...

Importing files in multiple folders at the same time using ssis - I have 10 folders each receiving a .csv file on a daily basis. I would like to create a package...

Can I use the container name in an expression for a control flow task - Hi guys, I have an SSIS package that currently bulk inserts 21 files using SQL Tasks that all call a stored...

Article Discussions by Author : Discuss Content Posted by Peter He

Performance using CTE vs temp tables - Hi All, I have replaced 3 temp tables in my batch with multiple CTEs. I see remarkable 50% benefit in REads and...