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

Stairway to T-SQL DML Level 12: Using the MERGE Statement

Gregory Larsen from SQLServerCentral.com

The final level of this stairway looks at the MERGE statement in detail, focusing on how to perform insert, update and delete logic using the MERGE statement. More »

Free Webinar: Statistics in Oracle and SQL Server

Press Release from Red-Gate

In their third live 'Oracle vs. SQL Server' discussion, Jonathan Lewis (Oracle Ace Director, OakTable Network) and Grant Fritchey (Microsoft SQL Server MVP) will look at statistics in Oracle and SQL Server. Expect a lively debate on Oracle vs. SQL Server from two leading RDBMS experts on Jan 23, 2013. More »

SQL Server T-SQL Tuning – NOT IN and NOT Exists

Additional Articles from SQL Server Performance

Following on from the previous TSQL performance tuning article in this series, we now focus on the important topic of T-SQL “Not IN” And “Not Exists “ More »

From the SQLServerCentral Blogs - Execution Plans on Azure SQL Database Portal

Grant Fritchey from SQLServerCentral.com

If you’ve been working with the Azure SQL Database portal, you know that you have a query window. Within the... More »

I had high hopes for Service Broker when it was introduced in SQL Server 2005, but it doesn’t seem that many people have bothered to architect their applications to take advantage of it. I do see some people starting to use it, but it hasn’t been anywhere near the levels of adoption that I would expect.

I ran across a piece on 10 uses for a message queue that points out a number of possible ways that queuing could help you. There are some great ideas, including a few suggestions for scalability and resiliency for your application. One of the more interesting ones to me is the idea of using a queue to buffer the slower processes that may be a bottleneck in your application.

I still think this is a great way to build applications, especially distributed ones, using queues instead of linked servers, ETL, etc. However until we get more people developing in a service oriented architecture, and getting experience, I think we will struggle to see message queuing gain widespread acceptance. This is a departure from the way most developers are comfortable with building an application, and the way that queues work in SQL Server certainly confuses many DBAs.

I'd challenge many of you to think about using queuing in any applications where you are moving data from one database to another, or trying to trigger an action on a remote machine. It's a great way to scale out your systems, and it's a very solid, reliable architecture for your 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.6MB WMV
• Watch the iPod Video Podcast - 16.3MB MP4
• Listen to the MP3 Audio Podcast - 3.3MB 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:

The TRY…CATCH construct cannot be used in a user-defined function.

By rhythm varshney

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.

Pro SQL Server 2012 Practices

Expert SQL Server Practices 2012 Edition is an anthology of high-end wisdom from a group of accomplished database administrators who are quietly but relentlessly pushing the performance and feature envelope of Microsoft SQL Server 2012. With an emphasis upon performance—but also branching into release management, auditing, and other issues—the book helps you deliver the most value for your company’s investment in Microsoft’s flagship database system.

Get your copy from Amazon today.

You have the following table and data in it:

CREATE TABLE Test (
    RowID INT CONSTRAINT PK_Test PRIMARY KEY CLUSTERED,
    Col1 CHAR(750)
);
-- Insert 15 records into the table.
INSERT INTO Test (RowID, Col1)
VALUES (1, 'One'),
       (2, 'Two'),
       (3, 'Three'),
       (4, 'Four'),
       (5, 'Five'),
       (6, 'Six'),
       (7, 'Seven'),
       (8, 'Eight'),
       (9, 'Nine'),
       (10, 'Ten'),
       (11, 'Eleven'),
       (12, 'Twelve'),
       (13, 'Thirteen'),
       (14, 'Fourteen'),
       (15, 'Fifteen');

Currently, the data resides on two data pages (the first page has the first 10 records, the second page has the remaining five). If you delete the first 12 records, how many data pages will the table have after the Ghost Cleanup process has been run against this table?

By Wayne Sheffield

Answer: 2

Explanation: The Ghost Cleanup process does not delete the last record from a page in order to avoid having to deallocate empty data or index pages.

Ref: http://www.sqlskills.com/blogs/paul/post/inside-the-storage-engine-ghost-cleanup-in-depth.aspx Demonstration example: http://blog.waynesheffield.com/wayne/archive/2012/12/ghost-cleanup-process/

» Discuss this question and answer on the forums

Pro SQL Server 2012 Practices

Expert SQL Server Practices 2012 Edition is an anthology of high-end wisdom from a group of accomplished database administrators who are quietly but relentlessly pushing the performance and feature envelope of Microsoft SQL Server 2012. With an emphasis upon performance—but also branching into release management, auditing, and other issues—the book helps you deliver the most value for your company’s investment in Microsoft’s flagship database system.

Get your copy from Amazon today.

Database details for Daily checks

Rahul from SQLServerCentral.com

Get database status,file details,free space in sql 2005 and 2008 sql versions More »

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

SQL Server 2005 : Administering

db autogrowth problems - On two occasions we have had to change or autogrowth setting for our database files from 5 or 6 % to...

Missing green arrow on the sql server icon in object explore - Hello everyone I have tried to Google this issue for quite awhile and can't find any definitive solution. Now in my...

SQL Server 2005 : Business Intelligence

How can I store the username and password without config file - How can I store the username and password not in the config file or SQL Server in SSIS, so that...

SSRS 2005 Multi-Value Filter. - Hi all, I met with a very tricky problem. I have a muti-value filter in my name whereby user are able...

same measure in 2 cubes, but not displaying data in one cube related reports only - Hi , I have a measure called "Stock Sales COGS Local Currency" in sales cube and monthly cube. product levels are 6...

How to Evaluate Performance Point 2010 - I would like to do some Proof of Concept Dashboards/Reports for my company using Performance point 2010. Do i need...

SQL Server 2005 : CLR Integration and Programming.

Regular Expressions - I am looking into writing a regular expression for validating emails. I am an absolute novice at this, could some...

SQL Server 2005 : Development

How call flat file from another server using dtsx ? - I have a problem to call file from another server with dtsx, any one know this?

SQL Server 2005 : SQL Server 2005 General Discussion

Maintenance plan job error - Hi, can anyone help me with this error? I suppose to have a backup plan running every 2 months but...

Optimizing XML import in SQL Server - I have this code to import a XML with 50mb, that contains around 26.280 entities. But this is taking to long,...

SQL Server 2005 : SQL Server 2005 Integration Services

Demanding and difficult logic qns PART 2. Sorry but i need help. - Hi all, Please read the following attached word doc. Inside there is a target table with 6 columns which is...

Script task using batch file parameters - Hi all, I have created a batch file to run my SSIS package. I will be able to pass parameters in...

SSIS Script task to change table name in data flow task - Hello, We need to change the table name of 'OLE DB Destination' task. I am not able to use variable due to...

SQL Server 7,2000 : Administration

Changes to subscriber db after publishing - Hi, Have published a db and several subscription have been made. Now I want to alter a table of all subscriber...

SQL Server 7,2000 : General

AS Cube Processing Fails - Hello First let me give some back ground information on this issue. My company has a very old server (1999/2000) that...

SQL DTS - "Invalid Pointer" - Hi All, I have two SQL servers, one is running SQLServer 7.0 (7.00.842) and the other is running SQLServer 2000 (8.00.760).  I've...

SQL Server 2008 : SQL Server 2008 - General

Populate SQL Table with Powershell script - Hi All, I'd like to use a powershell script to populate a table with fixed drive space info. here is the...

Adding column in where condtion dynamically - Hi, I have following tables product customer agent we have column by name "state code" in product and customer. i have sql like select * from table name(we...

Disabling job schedules - Is there an way to disable all jobs schedules? I dont want to stop agent.

Autoreply SMS application - Hello, I am trying to create an application which will send a reply back once a customer (registered user) sends a...

how top clause works - I found that TOP clause works little randomly with Select TOP 50 percent * from Tablename as it is not giving exact...

how to find dependent objects on Default or Rule in sql 2008 - I am able to see the list of columns or table which is bound to my rule or default using...

how to find out number of processor core? - how to find out number of processor core available to instance of SQL Server?

Excel 64 bit driver - Hi all, I am running the following query in sql server 2008 r2(64-bit), MS office 2007 excel file. SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel...

SQL Server 2008 - SqlServerSQLAgent$TOMMY$MSSQLSERVER2008 folder ACL - Hi Guys, I created 2 local windows accounts in my laptop, both accounts are in users group. I installed SQL Server 2008...

transaction log working - Would like to know whether DCL and DDL statements are logged in sql log file before hardening on data file? can...

index architecture - I want to get the Idea what exactly Index configuration means? and what exactly is the the size of index pages...

How to join Values - Hi Professional , Create Table, [code="sql"]CREATE TABLE abc (num int)[/code] Insert Into [code="sql"]insert into abc values (1010) insert into abc values (111) insert into abc values...

steps to be followed to migrate t-sql code to pl-sql ? - Hi All, I wanted to convert t-sql code to Pl-sql(oracle 11g). Can any body guide what are the steps to...

How to make DAO recodset updatable to add a new record to SQL Server 2008 table. - Hi, I am trying to add a recod to a SQL Server 2008 table using DAO.AddNew. But (). this is giving me...

tempdb per processor cores? - Any perfromance difference in these type of configuration? How we can set these ?

Trouble shooting a Update, Insert TRIGGER...what's wrong with this syntax ? - Hi folks...Im trying to update the Last Modified cell in Table B, when Last Modified cell in Table A changes,...

trigger that will update one table when a record is updated in another - I'm trying to write a trigger that will update one table [tblTransactions] when a record is updated in another [tblBulkPurchases].......

Archiving - MSSQL2008 with 1 TB Harddisk space Databases: DB1 - data for 2012. DB1_Arch - data for 2011. *** Inherited this server; looks like he idea...

Trunacte MSDB tables. - Can these 2 tables in the msdb database, simply be truncated.They have grown quite large and simply need some sort...

Insert multiple lines in a single row - Dear, I want to insert multiple lines in a single row in SQL Server 2008 R2. I have tried. But it's...

Does Change Data Capture require a table have a primary key - or can it record before and after column changes based on the LSN only? An extract from a file based legacy...

SQL Query - Hi, I have an issue with the SQL query I have created. The result that this query should generate should be...

NT AUTHORITY\ANONYMOUS LOGON intermittent failures - Hi, I've created an SSIS package that executes a stored procedure on a linked server. I need to capture the results...

Insert Value From Column into String Value of Another Column: Query - Hello, This is my first post to this forum. Thanks in advance to anyone who takes the time to study this...

SQLServer2008 R2 BPA - Hi All, I'm not able to install SQLServer2008 R2 BPA. ERROR Log Name: Application Source: MsiInstaller Event ID: 11722 Task Category: None Level:...

Connection Pooling Error - SQL 2008R2 - We just migrated our from sql 2005 to sql 2008R2. Occasionally we do see the following error message and timeouts...

BULK INSERT failing. - Hello, I have a situation where I am trying to do a bulk insert of a file that's on a network...

SQL Server 2008 : T-SQL (SS2K8)

cross tab query - hi all, how do i achieve this result division 2012 2013 2014 2015 2016 xyz 1 0 0 0 10 abcd 11 0 0...

How to combine two tables when one table is in different database and this table will determine the value to be shown on the first table. - Dear members, Hello, a pleasant day to everyone. I would want to ask help about this query that I need for reporting. Let’s...

string extraction help - Hi, I have some code in c# which extracts the text from a string that complies with these wildcards ([A-Za-z]{3,6}_?[0-9]{4,7}) to break this...

CTE or while loop or an other? - Hello, I am new to this board so thanks in advance for any help that can be provided here...:-) I would like...

Pivot to produce a timetable - Hi everyone, I'm looking for a fix for an issue I'm working on. I've got a list of locations, start and...

Pivot problem - Hi I have the following table [code="sql"] CREATE TABLE #Test ( ProductID int, MainMasterFeatureID int, --MasterFeatureValue Varchar(100), ChilFeatureName varchar(100), ParentFeatureName varchar(100) ) INSERT INTO #Test SELECT 40,1,,'Pack','Type' UNION ALL SELECT 40,0,'Laminate','Technology' UNION ALL SELECT...

Has to Be a Better Way - Good Morning Everyone I am performing a code review before sending any thing into QA and on up. I ran across...

Better Way to Perform this Query - Hello Everyone I hope that everyone is having a very good day. I need to write a query to select row counts,...

Reverse Of Number without Using reverse() - Hi, How will i reverse the number in SQL Server with out using reverse()

Flattening a Parent Child Hierarchy - Hi, We are starting work on a BI project that is given data in a traditional Parent-Child Hierarchy format. The Problem is...

SQL Server 2008 : Working with Oracle

SQL 2008 R2 linked server to 11g - I've been tasked with setting up an Oracle linked server on a new SQL 2008 R2 install. I have installed...

SQL Server 2008 : SQL Server Newbies

Rowversion Duplication - Hi All Most off our tables have rowversion columns. We whant this row version to be unique through out the DB....

UDF Table - Hi i have little experience with sql server and i have just been thrown into the fire at my job...

Torn page issue - Hello Masters, Can torn page issue occurs due to lack of disk space ? I know torn page is the page that...

ORDER BY non-sequential number sequence - I have a table where a certain column contains a status id as an integer. I want to sort on...

Structure of WHERE clause using "and" / "or" in a multiple criteria query - Hi I have a set of data (example script below) and i have to exclude certain rows based on the rules...

View not working since moving database from SQL 2000 to SQL 2008 - Hi All I moved a simple database from SQL Server 2000 to SQL server 2008. It has a couple of Tables...

SQL Server consuming 90% Memory - Hello Masters, My sql server has 12GB of RAM, I got alerts from windows team that SQL Server consuming about 90%...

SQL Server 2008 : SQL Server 2008 High Availability

Question about database mirroring with "high-safety mode without automatic failover" - Hi, In database mirroring, for "high-safety mode without automatic failover" it is mention that : "Every transaction committed on the principal database...

which configuration suggested for aspnet & sql server ? - dear friends, I need sql server up and running nomatter what happens. I need to host a aspnet web application with...

SQL Server 2008 : SQL Server 2008 Administration

UCP and Clustered Instances - I am running UCP across our estate and all is running fine - except for clustered instances and the Volume chart....

Size of the ldf file increasing - I have some sql server database for which recovery model is set to full.Now for some databases the ldf size...

In-place upgrade to 2012 - I had a quick query regarding an in-place upgrade to 2012 which I'm not in a position to test right...

Rebuild system database failure - I have a situation where all system database (.mdf, .ldf) were wiped out after system recovery from a major outage....

Backup and restore databases and I/O usage - Does backup and restore databases use a lot of I/O? And which one uses more I/O, backup or restore? Thanks

Default Port - Hey all, A quick question. I recently joined an organization and I see here all the production environments are on...

Large system coming, looking for advice of how to maintain, over 2TB. - We have a new system coming that will hold our restaurant polling detail and we will hold 18 months in...

SQL job error - Anyone have any idea why I receive this error on SQL job? Step ID 1 Server SRIN_SVRA Job Name 1 Year Backup Maintain BDSBPM.Subplan_1 Step Name Subplan_1 Duration 00:00:34 Sql Severity 0 Sql...

Index optimization failed for User databases - Hi , Index optimization job is failing with the below error :: Message Executed as user: XXXXXXXXX. Incorrect syntax near '-'. [SQLSTATE 42000]...

Job Step Properties of SQL Server Agent job in SSMS does not match generated TSQL code - The Job Step Properties of SQL Server Agent job in SSMS does not match generated TSQL code when I select "Script...

Programming : Powershell

Help me Error SSIS Error (Agent Service) - An invalid reporting option was specified. Only E, W, I, C, D, P, V, and N are allowed Import Data Source...

SQLServerCentral.com : Anything that is NOT about SQL!

A reason to get a better-paying job... - So I can put one of these: [url=http://www.dailytech.com/article.aspx?newsid=29641]2014 C7 Corvette Stingray[/url] in my garage... :-) Know anyone who's hiring? ;-)

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

Reporting Services : Reporting Services

Deploying and running reports succeed without the custom assembly being installed on the server - Hi, I've been trying to replace a dev custom assembly on a server with the fully functional production version .Replacing the...

SSRS Email Subscription having Issue. - Hi All, I am using SQL Server 2012 Reporting Service. I have 5 Reports those need to deliver on email with...

Change hyperlink text - Using SSRS 2008 Standard. I'm not seeing a way to manipulate the hyperlink text. What I get is an entire URL...

Report Parameters Selected Shown in Report and/or Footer - Hi, I'm reasonably new to Reporting Services and still learning so A) bear with me and B) please excuse any...

Issue with Small matrix spilling over 2 pages - I have a small matrix that measures 3.5 on the ruler. my report is set as landscape. but it seems...

Use subdomain to resolve Reports Manager - Using Server 2012 / SQL2012 how would i go about directing subdomain.domain.com to the actual "http://computername/reports" ? I already have the subdomain...

barcode generation problem in RDLC local report - I am trying to encode [url=http://www.aspper.com/barcode-rdlc/tutorial.html]barcodes in RDLC reports[/url], I am using vs 2008 and C# class library. It works well...

Reporting Services : Reporting Services 2005 Development

Possible to add in column from another data source in the same report? - Hi all, A burning qns. Currently my report displays 7 column from a datasource A. Is it possible to add the...

Database Design : Design Ideas and Questions

Building my first data warehouse. Advice welcomed :) - Hi all, I've been brought on board to help a company start to build their first business intelligence setup. I'm a...

Data Warehousing : Integration Services

How can I achieve this Task? - Hi All, How to create a Table dynamically based on the metadata of Oracle Source Table In SSIS(I get the meta...

Script Component and Querying Database - I need help understand how to use the Script Component to create a connection to the database, submit a SQL,...

configure OLE DB Destination Error Output - I have an ssis Job (SSIS 2008) that loads data from a flat file to an SQL server 2008 table. I...

SSIS Package Not Writing all the Rows to OLE DB Destination Although shows the complete numbers on dataflow Path - my SSIS package loads data from excel file to SQL server table. The excel file has 5000 rows. When I execute...

Notification Services : Administration

changed some Passwords, database mail seems to have stopped - i've seen there's a few threads about database mail, here, but none seem to apply to me. I've changed a couple...