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

Stairway to T-SQL DML Level 10: Changing Data with the UPDATE Statement

Gregory Larsen

Unless you are working on a reporting-only application you will probably need to update tables in your SQL Server database. To update rows in a table you use the UPDATE statement. In this level we will be discussing how to find and update records in your database, and discuss the pitfalls you might run into when using the UPDATE statement.  More »

Webinar: Temporary Tables in Oracle and SQL Server

Additional Articles from Red-Gate

Once again Jonathan Lewis (Oracle Ace Director, OakTable Network) and Grant Fritchey (Microsoft SQL Server MVP) will host a live discussion on Oracle and SQL Server, this time in relation to temporary tables. Will they agree on some common ground? Or will it be an out and out argument? Either way, be prepared for a lively exchange that will not only entertain, but will teach you key concepts on Oracle and SQL Server. More »

Improve Your Database Unit Testing Skills and Win Free Stuff

Additional Articles from Other

As the SQL Developer community grows to embrace the benefits of test-driven development for databases, so the importance of learning to do it properly increases. One way of learning effective TDD is by the use of code kata – short practice sessions that encourage test-first development in baby steps. I have a limited number of licences for SQL Test to give away free – just for practicing a bit of TDD and telling me about it. More »

PowerShell script to find files that are consuming the most disk space

Additional Articles from MSSQLTips.com

As you know, SQL Server databases and backup files can take up a lot of disk space. When disk is running low and you need to troubleshoot disk space issues, the first thing to do is to find large files that are consuming disk space. In this article I will show you a PowerShell script that you can use to find large files on your disks.  More »

I love sports, and every year I'm amazed to see how many highly touted athletes, those picked early by their teams, fail to perform at a high level. Many of them go on to average careers, but quite a few fail out of sports within a few years, becoming just another person that didn't live up to their potential. I thought this was mostly a sports-type phenomenon, but that might not be true.

Are employers and managers looking at potential as well? Do they see high scores in school, or glowing recommendations as reasons to hire a technologies? I think this was definitely the case a ten or fifteen years ago when there were few candidates and many open positions. Managers tended to view anyone with a certification as having the potential to solve any problem with their computing systems. That still might be the case in some areas today, but as business people become more technology savvy, they realize that there's a substantial different between different platforms. We can't assume someone talented in one area is an expert in others.

I think we are drawn to potential, often because many of us are optimists. We see the best in new ideas and people and want to believe they will succeed. I think this is also one of our problems in security because we see the potential benefits and don't bother to look deeply enough into possible flaws. However as we are burned, and employment decisions burn many managers, we learn to look deeper at candidates and consider their past experiences, successes and failures, with an eye towards choosing someone that has a record of achievement.

Then there's a cloud hype. That might turn all of these ideas upside down and bring potential back to the forefront. Maybe doing a little project on Azure or AWS will convince your next interviewer that you have the potential to build an application that scales like the Netflix video delivery systems.

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 - 16.8MB WMV
• Watch the iPod Video Podcast - 17.7MB MP4
• Listen to the MP3 Audio Podcast - 3.6MB MP3

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

Today's Question:

begin try

Declare @ctr int

select @ctr = 1

while @ctr <= 10
 begin
  select '1'
  if @ctr = 4
    select 10/0.00001

  select @ctr = @ctr+1
 end
end try
begin catch

select @ctr

end catch

select @ctr 'Counter'

By SKR V

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.

Performance Tuning with SQL Server Dynamic Management Views

This is the book that will de-mystify the process of using Dynamic Management Views to collect the information you need to troubleshoot SQL Server problems. It will highlight the core techniques and "patterns" that you need to master, and will provide a core set of scripts that you can use and adapt for your own requirements. Grab your copy today from Amazon!

In SQL Server 2008 R2 SP1 or SQL Server 2012, the auto updating of statistics rules are:

1. Minimum threshold of 500 rows in the table.
2. Fixed rate of change with a 20 percent threshold.
3. Uses a default dynamic sample rate based on the # of rows. 

Is there a method / setting that will alter the fixed rate of change threshold ?

By bitbucket

Answer: Yes

Explanation: According to http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx there is. From the blog: In SQL Server 2008 R2 SP1 and in the next release of SQL Server, we released a traceflag which will change the fixed rate of the 20% threshold for updatestatistics into a dynamic percentage rate. The higher the number of rows in atable, the lower the threshold will become to trigger an update of thestatistics. For example, if the trace flag is activated it means an updatestatistics will be triggered on a table with 1 billion rows when 1 millionchanges occur. If the trace flag is not activated, then the same table with 1billion records would need 200 million changes before an update statistics istriggered. In order to activate this new logic, you need to enable traceflag 2371. As a result the threshold to trigger update statistics will be calculatedbased on the number of rows in the table. However, the table still needs to have a minimum of 500 rows as the minimum row threshold did not change. As before the update of the statistics will apply the default sample rate which is dynamic and based on the # of rows.

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

Job Summary

Adnan Korkmaz from SQLServerCentral.com

This script lists the name, running and success status, last executed/currently executing step name, Average and Last duration, next run time and other related information about Jobs. More »

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

SQL Server 2005 : Administering

shrinkfile and LOB data - We've moved most of the large indexes out of our primary filegroup, out into new files/luns in other filegroups. As...

SSRS 2005 report manager link not coming up - On my SQL Server 2005 installation. I am able to view the report server URL but I am unable to...

Differential backup failure - Hi Team, I am facing an issue with differential back up . The differential backup is failed only on sunday . we...

SQL Server on Domain Controller - Hi friends Well, i have been told to never install a sql server instance on a windows server domain controller, but...

SQL Server Patching - My client has around 200 SQL Server instances running on SQL Server 2000, 2005 and 2008. We have agreement to...

SQL Server 2005 : Business Intelligence

ProcessInput() method - Hi, I have a requirement to see if any rows are present in the input. If they exist, I just have...

MDS ERROR - Executing SSIS package i am getting following error. [Execute SQL Task] Error: Executing the query "exec proc_BusinessRulesValidation @UserName = ?, @..." failed with the...

Notify icon Script Task help - Hi all, I have a problem when trying to display a message in a NotifyIcon through a script task of SISS...

SQL Server 2005 : SQL Server 2005 General Discussion

Advantages in Multiple Files per Filegroup per Spindle ? - In some of the databases I'm taking over ( in the 1.5 TB range ) I'm finding multiple files per filegroup on...

SQL Server 2005 : SQL Server 2005 Security

SQL Server 2005 Express - Hello all, to say that I am new to SQL would be an understatement. At my company, I have been...

SQL Server 2005 : SQL Server 2005 Strategies

Trigger with OSQL - Hi, I have a requirement as below, 1) whenever a column in a table gets updated with a value then i have...

SQL Server 2005 : SQL Server Express

Is it possible to do database maintenance using an older version of SSMS? - This question isn't so much about SQL Server 2005, as it is about SQL Server 2008 R2 Express. My colleague...

SQL Server 2005 : SQL Server 2005 Integration Services

stored procedure process with ssis - this post is really meant for ssis 2008 but there is no ssis 2008 spot, just a 2005. :( so pleas...

Merge Join transformation failing - Destination Column does not allow NULLS - Hi, I am using a MERGE JOIN SSIS transformation to join the results of 2 queries, which already contain joins in...

Getting Error for Dynamic file name - Hey I am also working on same task like this In SSIS ftp task... Here I created a variable for getting...

SQL Server 2005 : T-SQL (SS2K5)

How to join four tables using left outer join - Hi All, I want to show data from four tables on the basis of left outer join.All tables contain common column...

How to Build a single row with groups of fields from several rows per key - i have several rows like: Customer sn val1, val2, Val3 ..... (sn is a seq# from 1 to 15) A 1 31 54 A...

what is the difference between ODBC and OleDB? - Hi Friends what is the difference between ODBC and OleDB?

Find sub-tree nodes - Hi, I have a tree structure represented by a simple table that references itself: [code] CREATE TABLE [dbo].[AframeGroup] ( [GroupID] [int] IDENTITY (1, 1)...

SQL Server 7,2000 : Administration

initconfig: Error 32(The process cannot access... - Hi everyone , please help!!! Since yesterday, our SQL started to thowing out this error: Event Type: Error Event Source: MSSQLSERVER Event Category: (2) Event ID:...

SQL Server 7,2000 : SQL Server Newbies

Count duplicate occurances in relational tables - I am fairly new to SQL and pressed for time. I don't think this is difficult to do but I...

SQL Server 7,2000 : T-SQL

determine if any error occurs in trigger from out of trigger - i have an Insert command on Table in which fires a trigger. i wanna know if any error occurs in...

Csv list in column to separate columns - Hi all, Can I ask please if I have a row and one column has a csv list in it. Is...

SQL Server 2008 : SQL Server 2008 - General

Unable to update SQL Server database through code - I am writing a vb.net code to update SQL SERVER database. When I run the query through the SSMS, I...

Recursive function - sql2008 - Hi, I am new to sql server 2008 functions, I am facing an issue and request, if some can help. I have...

Query Help - Hello Friends i have one task, in that i need to delete records from tables and keep only 5th rows data. so...

Grand total help - Hey guys This is my first post on this website, so please be gentle i need some help producing a...

CHECK CONSTRAINT vs. TRIGGER - Hi, If I have a CommonInformation table that can store other entities data, and entities can be Customers, Suppliers, whatever.. the...

A severe error occurred on the current command - Hi Everyone, I have a fairly simple query... it's sums a value from a single table and inner joins to a...

restore log backups to another server - Hi every body I have a database in server A and i have a friday job that takes a full and...

compatible between sql server 2008 and sql server 2008r2 - hello all. I back up data base from sql server 2008r2 and i want to restor this back up in sql...

Memory utilization - SQL Server (2008) is using 92% of the memory. out of 56 GB SQL using 51.6 GB How to find out...

how to insert values to table only if the same row not exists - hay.... I want insert to table (table name is: "portfolioForExpert") 2 values: 1) expertID 2) portfolioID (value of "1" represent: "stocks",...

Dying to solve this problem :-( "SINGLE QUOTATION" - it's only about ( SINGLE QUOTATION) in ASP.NET here is my SQL Query: [code="sql"]SELECT ID, Title FROM viewTopTitles WHERE Title = ' you're most welcome '[/code] but...

Clustered index, mandatory or optional?! - Hi, I'm reviewing our database tables and found some "bad" practices like Clustered index with Uniqueidentifier columns using NewId - support for...

Using case in updating column - ok i have a table called "weights" that has following columns [code="other"] id shipweight1 shipweight2 shipweight3 [/code] Right now i have the following query [code="sql"] @id...

What would be the major concerns for running WCF services and xp_cmdshell on SQL Server? - All, The development group at my new company is using a WCF service running on the SQL Server to call a...

Weird view issue - I have a view whose underlying query will run indefinitely. The query ran within 15 seconds yesterday and today it...

update table with next value - hi i have 2 tables in 1 i have the following rateN1 rateN2 rateN3 rate a aa aaa 1 b bb bbb 2 c...

SQL Process hung with PREEMPTIVE_OS_PIPEOPS as lastwaittype - :w00t: This is my Prod Server , I know this is P1, I was checking the disk space by using xp_cmdshell which...

linked server using JDBC? - ca I create a linked server in MSSQL 2008 using a JDBC driver (located in a file)? How?

Check for the existence of a file BEFORE processing in SQL Server - SOLUTION - I thought I should share this with SQLServerCentral.com because this site has helped me out so much. I ran across...

SSIS Package Configuration - Hi, I have created an SSIS package that extracts data from a flat file source, then loads the data into...

package configurations - Hi, I have created an SSIS package that extracts data from a flat file source, then loads the data into...

Currpted Data Recovery - I have a database which is suspected ( Master database) I tried many times to recover but fail, how to recover the data?

Backup script - Hi, I'm using ola backup script and want to have all backups in the given backup directory NOT to create Instancename\databaseName\BackupType...

Compare two Databases - Hi, i have to solve the following problem. We used DoubleTake for the event of a disaster in SQL Server 2008...

Database change policy and procedure - Presently we have no defined process or policy for changes to SQL Server instances and databases in our environment. I'm...

MDF and LDF locations - MDF and LDF in different locations make any sense in performance of SQL?

SQL Server 2008 : T-SQL (SS2K8)

Propagating Data Fowards Through Time - Hi SSC, I have a table containing prices for certain stock prices, and dates upon which their shares outstanding changed. I...

BULK Insert zwischen Datenbanken - Hallo, ich lade mir einen Katalog (ca. 800.000 Datensätze) aus einer anderen, entfernten DB (über Verbindungsserver). Das Wiederherstellungsmodell steht auf FULL....

Doing date math where the user defines which days to count and which to skip - I have a query I'm trying to write where we have a scheduled event that occurs x number of days....

Using MERGE for Audting - Hi I'm looking at using Merge which I've never used. I've achieved Auditing before with an After trigger that writes...

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

SQL Server 2008 : SQL Server Newbies

SQL Server Conference/Seminar in the UK. - Hi all, Does anyone know of any useful SQL Server conferences/seminars that a relative SQL newbie (ie me!) could attend within...

ASP.NET App and SQL Server identity - Well, I've just written my first ASP.NET (C#) app using Entity Framework and SQL Server (2008). It's a simple thing,...

SQL query statement for copying data from different rows to different rows - I have a table (dbo.ACCOUNT_INF) with fields company_code (secondary key), accounts_code (unique) and other columns. I like to copy rows...

Transposing, filtering, most recent record - Hi all, A user at a PC help forum told me to try here. I have a SQL database with a lot...

Certifications for beginner - Hi ALL, I am currently working with SQL server 2005 database and would like to pursue certifications. From microsoft site, i...

Best Performance? SELECT for a 'Snapshot' (Current Data) - We have a 3rd party ERP System. I develop solutions for information using Access Reports and exporting query results to...

UPDATE Does Nothing (Executing Query displays in status) - I am stumped and perplexed. The following statement executes correctly returns 2 Rows: [code="sql"]select JobNum, RefreshDate, RecID from ClosedJobsSumms[/code] [b]When this statement is...

Connect to sql express 2008 database with VB.NET 2010 - I hope I am in the right forum. I am running windows 7, 6 gig ram on 64 bit platfrom(laptop)....

SQL Server 2008 : SQL Server 2008 High Availability

Question Regarding Backup Policy - Hi All, If you have mirroring or logshipping setup as a DR solution......Do you need have a backup policy/plan on top...

Adding Primary Key Columns to tables for Transactional Replication - I have written the following 2 scripts to add Primary Key Columns to all tables in a Database that don't...

Changing Locations of DataFile - Hi All, As i see some of the servers are having databases online... Now this Database have data file & log file on...

setting up Clustering - SQL Server 2008 R2 - I have a couple of questions about installing SQL Server 2008 R2 Standard in a 2 node active\passive cluster First, a...

SQL Server 2008 : SQL Server 2008 Administration

SQL 2008 R2 Service Accounts - Hi All I've tried to find some documentation on this but I can't seem to get anywhere We have SQL 2008 installed...

Service Broker - I am a newbie as far as service broker is concerned. The problem is with message forwarders. Forwarders are receiving...

What is a read ? - Hi All When using DMV's to assess your SQL instance, what is a read? For example, when dealing with DMV's like: sys.dm_exec_query_stats sys.dm_exec_requests There are...

Problem With Moving MSDB? - Hey Pals! I moved My MSDB database to another location. after that i tried to start my server but unfortunately it...

import - export of dts package - Hi I am trying to import data from oracle to sql 2000 using dts package but unable to get all...

Mgt Studio can see & login to other servers but not (local) - Built a new Windows 2008 R2 x64 server, and installed SQL Server 2008 on it, as a test version of...

Career : Certification

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

Programming : Connecting

Website can't connect. 500 error - For a better understanding. Everything was on "oldserver" (DC, exchange, sql2008, website, etc). I moved Exchange, DC and website to...

Programming : XML

normalizing XML data via transformation - I'm a newbie with XML, but i was wondering if somebody could get me started down the path of how...

SQLServerCentral.com : Anything that is NOT about SQL!

Ryder Cup 2012 - So, the Ryder Cup's just started over at Medinah... Any golfing DBA's on here & will you be following the competition over...

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

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

SQLServerCentral.com : Contests!

intersting sql puzzle - Independence Day - II The All Nations United Force (ANUF), formed to protect the human race against alien attacks, maintains a database of...

SQLServerCentral.com : Editorials

Remote DBAs - There's still hope for all you DBAs out there looking for a telecommuting job that allows you to work in...

SQLServerCentral.com : Suggestions

Do not require registration to just read articles - I would humbly suggest it would be beneficial to not require registration for merely reading articles. I know that registration...

Reporting Services : Reporting Services

What are the negative points when we migrate BO reports to SSRS reports? - Plz let me know do we have any pblms while we are converting our BO reports to SSRS reports? Because in...

In one SSRS report, can we sync data from both table and SSAS cube? - 1. can I take 1 source from table and other source from SSAS cube and do the "Report Server Project" 2....

RDL - Visual Studio Report Designer omitting certain tags - From my reading of the Report Definition Language Specification, tag nesting down to the Body element should look like the...

[rsLookupOfInvalidExpressionDataType] !!? - Hi geniuses. I have a column in my report with an expression: =Lookup(Fields!ProjectGUID.Value,Fields!PROJ_UID.Value,Fields!STAGE_NAME.Value, "DataSet2") I'm not getting the right data. BIDS gives me a...

Customizing Reports Manager (SSRS 2008 R2) - All, I'd like to make some small cosmetic changes to my install of Reports Manager (SSRS 2008 R2). Maybe change the...

Performance issue with Multi value parameter in SSRS - Hi, I have a multi value parameter in SSRS report which has a list of values around 600..When the user selects...

Lookup - Hi All, Can someone help me with this problem? I have a table with product Id and another table with product...

SSRS report error : Multi value parameter report error only with Select All - Hi, I have an issue to report paramters I have a report which group by data based on 2 paramters and also...

Reporting Services : Reporting Services 2005 Development

SSRS Dashboard-Passing Parameters in the same page - Hi All, I have to create a SSRS dashboard which would contain many reports in the dashboard main page. Say, I have...

Data Warehousing : Integration Services

Dynamic file name for FTP task. - Hey I am also working on same task like this In SSIS ftp task... Here I created a variable for getting...

Data Warehousing : Analysis Services

MDX query help IF/IIF statement - I'm converting a report from a TSQL dataset to use a SSAS dataset. The report has a status parameter to choose...