Stairway to T-SQL DML Level 11: How to Delete Rows from a Table

Gregory Larsen

You may have data in a database that was inserted into a table by mistake, or you may have data in your tables that is no longer of value. In either case, when you have unwanted data in a table you need a way to remove it. The DELETE statement can be used to eliminate data in a table that is no longer needed. In this article you will see the different ways to use the DELETE statement to identify and remove unwanted data from your SQL Server tables. More »

SQL in the City - San Francisco 2012

Press Release from SQLServerCentral.com

The city by the bay welcomes Steve Jones, Grant Fritchey and more for a day of debate, discussion and learning about SQL Server. It's free. Just register and join us. More »

Improve Your Database Unit Testing Skills and Win Free Stuff

Additional Articles

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 »

From the SQLServerCentral Blogs - Master Data Services: Versioning

In Master Data Services (MDS), you can create multiple versions of the master data within a model.  When you create... More »

I hate wearing suits and ties. One of my early jobs in technology required them, and my least favorite part of that job was the dress code. Almost as bad was a company that required a uniform: same pants and shirt every day. I didn't mind a few jobs that required khaki pants and a collared shirt since I could have a little variety, but my ideal job, which I have now, lets me wear what I want. T-shirts, shorts, sandals, jeans, and more are acceptable in my office.

These days when I put on a collared shirt, it's usually one with with design of some sort on it. I have a nice collection of colorful shirts, and I often wear them to events. Can you find me in the picture below?

As we were planning the US tour of SQL in the City and talking about things we might giveaway, someone suggested adding a few shirts to the list of books, pens, balls, etc. I thought that if I were to give away some cool SQLServerCentral shirts to attendees, I ought to make sure that I was modeling one of the prizes all day. My boss agreed, and I got permission to go shopping.

At each of the US SQL in the City tour stops, I'll be wearing a new Hawaiian shirt. At the end of the day at each event, I'll also be giving away 3 of the same shirt to three lucky attendees. You can't win one if you don't come, so if you are in any of the cities we are visiting, be sure to register and come get a free day of presentations from Grant, me, and a number of other speakers. I've got a series of blog posts on the events, and if you want to run with me in the mornings before an event, drop me a note on Twitter.

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 - 22.9MB WMV
• Watch the iPod Video Podcast - 17.8MB MP4
• Listen to the MP3 Audio Podcast - 3.7MB 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:

Given the following code

create table #Test (ID int primary key);
insert into #Test values (2);
insert into #Test values (1);
select top 1 ID from #Test tablesample (10 percent)
drop table #Test

By Ole Kristian Velstadbråten Bangås

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

This question is worth 2 points 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.

SQL Server Hardware will provide the fundamental knowledge and resources you need to make intelligent decisions about choice, and optimal installation and configuration, of SQL Server hardware, operating system and the SQL Server RDBMS.

Pick up your copy of this great book from MVP Glenn Berry at Amazon today.

On an English SQL Server installed with the defaults, what is the output of the SELECT query?

CREATE TABLE test
(
 col1 INT,
 col2 CHAR(2)
) 
GO
INSERT INTO test
VALUES 
 (1,'AB'),
 (2,'Ab'),
 (3,'aB'),
 (4,'ab'),
 (5,'XY'),
 (6,'xy') 
GO
SELECT COUNT(*)
 FROM test
 WHERE col2 IN ( 'AB', 'aB', 'xy' ) 

By Lokesh Vij

Answer: 6

Explanation: The default collation setting for SQL Server is Case Insensitive and Accent Sensitive (SQL_Latin1_General_CP1_CI_AS). Hence the query will select all the records.

To make this query case sensitive we need to either set the collation level to Case Sensitive or use 'COLLATE SQL_Latin1_General_CP1_CS_AS' in the select query with col2 as shown below:

SELECT COUNT(*)
FROM   test
WHERE  col2  COLLATE SQL_Latin1_General_CP1_CS_AS  IN ( 'AB', 'aB', 'xy' )

Ref: COLLATE -

SQL Server 2012 Integration Services Design Patterns

SQL Server 2012 Integration Services Design Patterns is a book of recipes for SQL Server Integration Services (SSIS). Design patterns in the book show how to solve common problems encountered when developing data integration solutions. Because you do not have to build the code from scratch each time, using design patterns improves your efficiency as an SSIS developer. In SSIS Design Patterns, we take you through several of these snippets in detail, providing the technical details of the resolution.

Get your copy from Amazon today

Failover or Restart Results in Reseed of Identity - FIX

Gordon Beeming from SQLServerCentral.com

This script will automatically set all the identity columns to where they should be after a MS SQL 2012 Restart. More »

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

SQL Server 2005 : Administering

How to speed up backup process. - Hello, Apart from [b]Backing up to multiple locations and compressing the backup file[/b], is there any other way to SPEED UP...

Logshipping restore job failed - Hi Experts, In one of my prod server I have configured logshipping for two databases in a single server (SQL Server...

SQL2005 client components (SSMS, MS Visual Studio 2005,...) not compatible with windows 64bit??!! - Can anyone please confirm that installation of SQL2005 Client Components is not possible on windows 64bit? I tried to install SQL2005...

Communications to SQL server "freeze", then resume. - I am looking to see if anyone else has a few ideas of where to start in looking for problems...

Partitioned tables - We've a very large table and I'm thinking to partition it. Either one of these 3 main columns(appid, date or...

Lazy Spool - How can i improve/avoid this? - About 75 percent of cost is being used by "Table Spool(Lazy Spool)" in the execution plan. The inputs on this...

Update Stats with full scan on all tables in all databases - I run the following code which worked sql2k but does not work in sql2k5 and get the following error. I...

SQL Server 2005 : Backups

how to explore/ retrieve SQL Server Transaction Log information - Can anyone advise me on how to explore/ retrieve SQL Server Transaction Log information e.g. interprete record of table/ row...

SQL Server 2005 : Business Intelligence

mirroring & Encription - Hi Guys I use sql server 2008 R2 and using two servers, for security I need to do enkirpsi data, the...

Need list of COlumns/Table Names used in SSIS/SSRS - Hi All, Recently we are planning to do some schema level changes to few tables, so now i have to find...

Date Range filter in Performancepoint 2010 - is there any method to implement date range filter( fromdate and todate) in performancepoint 2010? I can implement the same in...

Looking for good books/resources for SQL 2k8 data warehouse - All, ** I think this is the appropriate forum but please let me know if not and I'll move it ** My...

process - Hi All, in my solution we need to process weekly sasles cube through a ssis package. for this purpose there is a...

SQL Server 2005 : SQL Server 2005 General Discussion

IF statement / Variables.. problems! - Hello I am trying to write a view and have a column that looks for a value in a column, if...

Need to list all columns,table names used in T-SQL Query - Hi All, Recently we are planning to do some schema level changes to few tables, so now i have to find...

Delete Constraint rule Set Default problem - I just want to ask if Set Default works with columns that are string related? I've tried setting up my a...

Determining Result of a job started with sp_start_job - I have an SSIS package that I have added as a job in SQL Server 2005. The SSIS package runs...

SQL Server 2005 : SQL Server 2005 Performance Tuning

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

SQL Server 2005 : SQL Server 2005 Integration Services

SSIS package insertion statement - Hi Guys, How to design SSIS Package for my below requirement please share some idea's and view to achieve this problem 1....

SQL Server 2005 : T-SQL (SS2K5)

If Update() in a trigger - I have a question that I thought I knew the answer to but I am now not sure. When in...

Selecting Query Takes so much time - This is structure of the Table. CREATE TABLE [dbo].[recent_items]( [id] [int] IDENTITY(1,1) NOT NULL, [user_name] [varchar](50) NULL, [sid_ids] [varchar](1500) NULL, [p_ids] [varchar](1500) NULL, [contact_ids] [varchar](1500) NULL, [client_ids]...

Unpivot Help - This should be easy but I'm not getting this to work. I have a number of rows that look something like...

database stuck in single user and no spid - Hi i have a dev database that is stuck in single user mode when i try and access the database i...

SQL Server 2005 : SQL Server Newbies

Quickest Way to Find an Index - Hi Folks can comeone tell me the quickest way to find an index in some 100 plus databases ? is there a...

column to calculate the balance stock dynamically - I have a table as shown below and would like to have the balance quantity which should be dynamically calucalted...

SQL Server 7,2000 : T-SQL

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

No PK or FK's in Warehouse - Good Morning, Afternoon and Evening (Depending on where in the world you are) This is not a query just something which...

Problem in foreach loop - Hi friends, I have problem in foreach loop.foreach loop doesn't show the for each file enumerator and foreach item enumerator...

job for collecting data - I need a script to copy data from one table(source) to anotehr table (destination), i want to sheduling this task for...

Mastering SSRS Expressions - Hey everyone, I am interested in studying the language used to write SSRS expressions. I have heard this characterized as...

Problem with saving to VARBINARY field - Dear Expert, I have a wierd requirements which I have been struggling for awhile now. here is the code [code="sql"]declare @Value2Convert...

OPTIMIZE FOR ADHOC WORKLOADS question - I recently turned on OPTIMIZE FOR ADHOC WORKLOADS on one of my servers. My understanding is that after this flag...

"An error occurred within the report server database" - I'm not sure the best place to post this. 2 out of my 5 report subscriptions didn't fire this morning....

Unable to open named instance - I have just installed a named instance for SQL Server 2008 R2. The name of the instance is "sport". When I am...

Partitioning large tables ONLINE - We have a very big table we are trying to get partitioned on all of our servers. All of the testing...

Saving query results to CSV file with query in job - I am using SQL server 2008 R2 with no SSIS capabilities. I have set up a job in SQL Server...

IN Vs INNER JOIN - I want to know which query is more optimized? SELECT 1 FROM ABC WHERE Id IN (SELECT Id FROM ABC1) =================================================== SELECT 1...

DELETE statement won't complete - This is an odd one. A DELETE statement with a single filter, deletes around 1.5 million records from a table...

NOLOCK/Isoloatin Level for READONLY database - Hello, We have a sole readonly database which is updated monthly at night during maintenance window. The db has several stored...

Query plan - largest cost - This query takes about 45 seconds to complete (down from 10+ minutes by adding clustered index to table). Looking at...

Insert record in DATABASE SNAPSHOT seems to block server - In my server I have a few 10's of databases and on some of them I created a snapshot to...

ways to refresh development environment - Greetings -- I'm trying to not reinvent the wheel here, and could use some help from those of you that might...

Calculating the total for the month - Hi Everyone I have a table in SQL Server that contains sales performance by items sold by the week i which...

UniqueIdentifier as a Primary Key - Is it ever considered a best practice to use a UniqueIdentifier(GUID) as a primary key? Is there ever a case where...

how to best summarize data - I’ve got the following data and I’m trying to get to the following results. I’m assuming I need to use...

Learning SQL for a beginner - Hi Guys, Im looking to learn SQL server and database management but with a wealth of resources, websites and books im...

SSMS 2008 Export/Import of Aliases and Registered Servers can fail - Every Microsoft DBA either has to build their support workstations from time-to-time, or perhaps help a new member of a...

Linked Server Security - I do not really understand linked server security in sql server 2008. I have a SQL Server logon for the...

SQL Server 2008 : T-SQL (SS2K8)

"Record" SQL Server workload... - Hi, Sorry if this is a dumb question... Our application is installed on several customers, some of which have performance problems. Is...

Taking Backup Of table - Hi all, I have a table which has some 10 million records. Now i want to take backup of this table...

Help with this Query - I have a date query as below: [code="sql"]SET DATEFIRST 1 -- Sets Monday as First Day of Week ;with mycte as ( select cast('2011-01-01' as...

Combining multiple rows of data from the Source table into just one single row of data with additional columns in the Destination table - Below are some of the sample SQL scripts and the given scenarios. USE [db_test] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON --Create SourceTable GO CREATE...

how to find duplicate default constraints in sql server - how to find duplicate default constraints in a table ,example DF_abc1 is a default constraint FOR column Effective date and...

how to find duplicate indexes in all the tables in a database - how to find duplicate indexes in a table ,example a nonclustered index names FX_LOCATION_ID is created on columns a,b and...

PROD ISSUE : Conversion failed when converting the varchar value 'DT-1205-006049' to data type int. - The issue is that the query runs in test and doesnt run in prod. We have 2 fields in my example:...

Need Help In Query . - Could you please help me find records described as per below scenario. Campus code,address,CampusID(PK) Data:- DEL,ABC,1 CHN,DDJ,2 MUM,PQR,3 BNG,WRT,4 PUN,BNM,5 Enroll StudId,CampusID,EnrollDate,LstAttendDate,Enrolid(PK) 1,2,15-06-2012,NULL,1001 1,5,31-05-2011,31-05-201,1002 ChangeStatus StudId,Enrolid,NewStatId,PrevStatId,ChangeID(PK) 1,1001,1,0,5001 1,1001,2,1,5002 1

T-SQL Help Needed - CREATE TABLE Issue ( id int not null identity(1,1) primary key, name varchar(10) ) CREATE TABLE Jrn ( id int not null identity(1,1) Primary Key, issue_id int, created_dt datetime ) ALTER...

Datatype truncation - Can anyone tell why ltrim and rtrin function truncate value of float datatype values ?

Improving query performance to detect first duplicate - I need to query some tables looking for All Sales that are active at the same time and have at...

SQL Server 2008 : SQL Server Newbies

Student Multiple attendances - Hi All, new here so be kind....:) I have a task to identify all students in a dataset of 49K that have...

unique patients from duplicate patient row entries - I have one table "patients"...one row per patient. Patient.ID, Patient.Name, Patient.address, Patient.gender 1/Jim/123 elm/M I have another table of patient diagnosis codes. It...

Setting a Primary key to subsequential Data (Header - Line) - Hello everyone!!! I have a file with these Data --> H;2334;8989;90;000 L;XXX;YYY;90;000 L;UUU;PPP;78;000 H;4445;855989;90;000 L;AAA;BBB;90;000 L;YYY;TTT;78;000 I want to set a primary Key (gid) to each row in...

Multi database to SQL Server at record level - Hi All I have created a touchscreen data collection system that stores its information on local Access 2007 databases. The reason...

Bulk insert of large files (more then 450MB) - Hi everyone I have to populate a table with a pretty large text file and I'm happily using the very good...

How do I fix Damaged allocation pages - I get this result from dbcc checktable DBCC results for 'RELATED_PARTY'. Msg 8946, Level 16, State 3, Line 1 Table error: Allocation page...

SQL Server 2008 : Security (SS2K8)

Granting database access - When I use the GRANT CONNECT statement to grant database access, it only works with a Windows login. USE database GRANT...

SQL Server 2008 : SQL Server 2008 High Availability

Logshipping out of sync - Guys, I have logshipping configured in SQL Server 2008 boxes, some times this goes out of sync for some of databases...

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

Sync Type-Replication Support Only - Hi, I have setup replication with sync type-replication support only and it's working fine. If I need to reinitialize the subscriptions...

Backups & Restore.. - I know how to take Full backuo & differential backup.. But the problem here is how do i go and automate the...

2 node failover cluster, SQL and DTC rescources lye on seperate nodes - If we have 2 resource groups 1. MS-DTC 2. SQL Server And the MSDTC resources lyes on Node A, and the SQL Server...

SQL Server 2008 : SQL Server 2008 Administration

Index Rebuild Question? - Hi All, I am doing some R&D on Indexes. I have a table as follows: CREATE TABLE SINGLEINTVARCHAR ( ID INT IDENTITY(1,1) PRIMARY KEY ,VARCHARNAME CHAR(5) ) and...

Disable 'remote access' - impact - Due to a Security Hardening exercise, I have been advised disable 'remote access' option on our SQL Environment. What is...

Gathering Perfmon counters on multiple servers similtaneously - Generally when reviewing a server we capture a bunch of perfmon counters using either SQLDiag, or just perfmon either via...

Database going into suspected mode !!! Urgent - Primary server databases are working fine but my secondary databases are going into suspected mode. No of databases 20 all are...

Reporting services+sharepoint - I am using SQL Server Reporting Sevices 2008 R2, and is in SharePoint Integrated Mode, whenver trying to deploy it...

SQL Jobs monitoring tool - We have a large number of jobs running on SQL DB servers. Is there a tool to find out what...

Backup taking MUCH longer on prod HELP:) - I have two environments: DEV: VM 4 virtual cores 16 GB Mydatabase 137242.19 MB Attached SAN storage SQL 2008 R2 standard PROD: Physical 32 Core 2 Processor 64 GB RAM Mydatabase...

SQL Agent - SQLSTATE 21000 error - I have found weird error in my SQL Agent which is produced about every 30 seconds. The message reads as...

Career : Certification

Passed 70-448 going for 70-452. What's Next ? - Hi Experts, I am working as a Microsoft Business Intelligence Developer and had already passed the MCTS: 70-448 exam 2yrs back. i...

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 : XML

How to filter the namespace from the xml in SQLServer..plz help - i have fetched a big xml from db.there is one namespace attached with it.i want to remove the namespace.plz help

Programming : Service Broker

Delay in Asynchronous Procedure Calls - I would like to call stored procs asynchronously, but there seems to be a delay in service broker so that...

SQLServerCentral.com : Anything that is NOT about SQL!

Attended an Interview - Recently I attended an interview for a role of Senior SQL Developer and I wondered the kind of questions panel...

Suggestions on anything to bring for SQL Saturday in Chicago? - Laptop with SQL, 12yr old Appleton rum for the presenters, $1 bills to put in the presenters shorts, that sort...

Is it OK to ask salary range before applying? - Hi all, I came across a Jr. DBA position with no salary attached. A google search of the company didn't turn...

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 2005 Development

How to access the current value within a list control? - I have a list control which groups on currency. Within the list control I display the detailed rows of all...

Data Warehousing : Integration Services

Notifyicon en script task ssis 2008 - Buenas a tod@s, Tengo un problema al tratar de mostrar un mensaje en un NotifyIcon a traves de un script...

Suggestions on data compare using SSIS? - Hi, Perhaps someone can throw some suggestions my way on how to perform a data compare on very large tables. The...

Notification Services : Administration

Sql server agent jobs - Hi, Could anybody please tell me how to run sql server agent jobs using control-M? I am new to Control-M so I...