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

Merge, Metadata and the Data Mart ETL

Christopher Ross from SQLServerCentral.com

Here's a great method for managing ETL for data marts and small data warehouses with just T-SQL. More »

Database Deployment: The Bits - Database Version Drift

Additional Articles from SimpleTalk

When you are about to deploy a new version of a database by updating the current version, one of the essential pre-deployment checks is to make sure that the version in production is exactly what it should be. If changes have somehow slipped in, you'll need to understand them and deal with them before you can deploy. More »

From the SQLServerCentral Blogs - Red errors in the results pane

Kenneth Fisher from SQLServerCentral.com

Most DBAs have seen those nice clear red errors in the results pane. And I’m sure most DBAs have noticed... More »

This editorial was originally published on Mar 10, 2008. It is being re-run as Steve is on holiday.

If you work in an operational area, especially if you're a DBA, you typically have a fairly tedious routine. You definitely have work, but it's often the same types of things over and over. You are checking logs, ensuring backups work, testing restores, tuning servers, and more.

Now this type of work can be fairly boring and many people don't really like it. They don't handle the operational routine that well over time. Most often I've found that database people would rather be on the development side, solving problems, working on new things, and exercising their brains a little more.

However I really like the operational area and I've been much better at it in my career than the development area. I like the routine, much like the daily routines that I have around the ranch when my wife is out of town (check out the podcast for a look at some of what I do). I have to feed horses, muck out stalls, clean up the barn a little and more.

When I was a production DBA, I had to handle daily things on a regular basis, and it becomes somewhat tedious. However it's not always boring, just like it's not boring around the ranch.

Last year we had a horse get kicked and hurt by another horse. I wasn't really sure what to do other than call the vet, which is something my wife has decided I'm qualified to do. When he came over, he wasn't sure what was wrong, but he had an idea and the first stage was to lock up the horse, give him a few days to rest and re-evaluate. However along with that rest (and feeding/cleaning/etc. in the stall), I had to give the horse aspirin.

Now it's actually less fun than it sounds. Just like you probably wouldn't want to eat (as in chew) any aspirin from the bottle, horses don't like the pasty stuff we give them. So you have to force it.

And it's not like forcing your dog to take a pill. I have a 130lb dog and I don't think twice about forcing her to take medicine, but the thought of forcing a 1200 lb animal is something else. Especially when they're taller than you!

The vet laughed at me a little (and my wife a lot over the phone), but shower me how to bridle the horse, hold it's mouth and then stick in the tube and squirt some aspirin in the horse' mouth. Not much fun the first time, but it got easier.

Managing a crisis and learning new skills is definitely part of the DBA routine. If you can remember the first time you've had a production database fail and you had to restore it, you know what I mean. Hopefully not a daily part, but it can, should, and likely will, happen to you.

A daily routine however easy, does require a balance. You have to remember that there is life outside of work and that you have to get away and recharge. You have to break things up and remember what else you like in your life besides your career. You also sometimes have to let some tasks go for awhile to get that break.

Because if there's one thing I've learned in a couple decades of working, it's that the work never ends.

Steve Jones

The Voice of the DBA Podcasts

The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.

Overall RSS Feed: or now on iTunes!

• Windows Media Podcast - 32MB WMV
• iPod Video Podcast - 25MB MP4
• MP3 Audio Podcast - 5.2MB

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.

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.

Steve Jones from SQLServerCentral.com

» Join the debate, and respond to today's editorial on the forums

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

Today's Question:

What will be the output of the query in the "quantity" column?

declare @doctor table(
    doctor_id int identity(1,1),
    name nvarchar(50),
    primary key(doctor_id)
);

declare @service table(
    service_id int identity(1,1),
    name nvarchar(50),
    price int,
    primary key(service_id));

declare @doc_serv table(
    doctor_id int,
    service_id int
    primary key(doctor_id, service_id)
);

insert into @doctor values('doc1'),('doc2'), ('doc3'), ('doc4')

insert into @service values('serv1', 1000),('serv2',500),('serv3',700),('serv4',1200);

insert into @doc_serv values(1,3),(3,2),(3,3),(2,1),(2,4),(1,2);


select quantity = count(*)
from @service s
inner join @doc_serv ds on s.service_id = ds.service_id
right outer join @doctor d on ds.doctor_id = d.doctor_id and s.price < 1000

By T K

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

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

Securing SQL Server

Written by Denny Cherry, a Microsoft MVP for the SQL Server product, a Microsoft Certified Master for SQL Server 2008, and one of the biggest names in SQL Server today, Securing SQL Server, Second Edition explores the potential attack vectors someone can use to break into your SQL Server database as well as how to protect your database from these attacks. In this book, you will learn how to properly secure your database from both internal and external threats using best practices and specific tricks the author uses in his role as an independent consultant while working on some of the largest and most complex SQL Server installations in the world. This edition includes new chapters on Analysis Services, Reporting Services, and Storage Area Network Security.

Get your copy from Amazon today.

When you run the following code, what will be printed to the Messages pane?

SET NOCOUNT ON;

CREATE TABLE HeapTest (ID INT IDENTITY(1, 1), DESCR VARCHAR(255));

IF EXISTS(SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('HeapTest'), NULL, NULL, 'SAMPLED')
WHERE page_count > 0)
 PRINT 'Table Contains Pages After Create';

INSERT INTO HeapTest (DESCR)
SELECT TOP 20000 REPLICATE('X', 255)
FROM sys.objects o1
CROSS JOIN sys.objects o2;

IF EXISTS(SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('HeapTest'), NULL, NULL, 'SAMPLED')
WHERE page_count > 0)
 PRINT 'Table Contains Pages After Load';

DELETE FROM HeapTest;

IF EXISTS(SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('HeapTest'), NULL, NULL, 'SAMPLED')
WHERE page_count > 0)
 PRINT 'Table Contains Pages After Delete';

INSERT INTO HeapTest (DESCR)
SELECT TOP 20000 REPLICATE('X', 255)
FROM sys.objects o1
CROSS JOIN sys.objects o2;

IF EXISTS(SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('HeapTest'), NULL, NULL, 'SAMPLED')
WHERE page_count > 0)
 PRINT 'Table Contains Pages After Load';

TRUNCATE TABLE HeapTest;

IF EXISTS(SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('HeapTest'), NULL, NULL, 'SAMPLED')
WHERE page_count > 0)
 PRINT 'Table Contains Pages After Truncate';

DROP TABLE HeapTest;

By Sean Pearce

Answer: Table Contains Pages After Load, Table Contains Pages After Delete, Table Contains Pages After Load

Explanation: When rows are deleted from a heap the Database Engine may use row or page locking for the operation. As a result, the pages made empty by the delete operation remain allocated to the heap. When empty pages are not deallocated, the associated space cannot be reused by other objects in the database. To delete rows in a heap and deallocate pages, use one of the following methods.

Specify the TABLOCK hint in the DELETE statement. Using the TABLOCK hint causes the delete operation to take a shared lock on the table instead of a row or page lock. This allows the pages to be deallocated. For more information about the TABLOCK hint, see Table Hints (Transact-SQL).

Use TRUNCATE TABLE if all rows are to be deleted from the table.

Create a clustered index on the heap before deleting the rows. You can drop the clustered index after the rows are deleted. This method is more time consuming than the previous methods and uses more temporary resources.

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

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

The Ultimate Index Usage Reporter

Jesse Roberge from SQLServerCentral.com

Reports index stats, index size+rows, member seek + include columns as two comma separated output columns, and index usage stats for one or more tables and/or schemas. More »

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

SQL Server 2005 : Administering

The replication agent encountered an error and is set to restart within the job step retry interval. See the previous step history message or replication monitor for more information - Hi, Im new with SQL, please help me on how to trouble shoot this error "The replication agent encountered an error and...

sprocs executed but rolled back? is there any log? - if a sproc started executing and then the transaction was killed, is there a way to check which sproc it...

sp4 on cluster - ran the sqlserver2005 sp4 in windows server2008r2 cluster node1 and getting below error for Backward Compatability feature upgrade and except...

Replication in sql 2000 - Hi Guys Whereas I did not find my problem here, I am posting the new one. Actually I set up a transactional...

Modeling relational databases - Hi, At the company we are being Audited by some entity... This entity is asking for the ERD Model of each...

SQL Server 2005 : Business Intelligence

Data update from one database to another database (Insert and Update both) - Hi, I have 2 database tables. 1) From Production 2) From Dev. The task is to move the data from Production to Dev Server....

NAMED SETS - Help - Hi to all. I use SSAS 2008 R2 and inside a cube i need to insert a NAMED SET. I studied about...

SQL Server 2005 : SQL Server 2005 General Discussion

Insert Error: Column name or number of supplied values does not match table definition. - We are migrating from sqlserver 2000 to sql server 2005 and Now i am stcuk with this strange thing which...

SQL Server 2005 : SQL Server 2005 Security

SSMS Job\SSIS due to error 0x80070003 "The system cannot find the path specified." - Hi All, I am logging on to a production M$ 2003 server using a domain administrator account xxxx\administrator. Then starting BIDS...

SQL Server 2005 : SQL Server 2005 Strategies

How implement object orientation features in sql server? - I know features of object orientation could not implemented, but i want to find a way to implement them. For...

READ_COMMITTED_SNAPSHOT & TRUNCATE TABLE - Hi, when I use the READ_COMMITTED_SNAPSHOT and use DELETE FROM table the other session is not locked when SELECTING data. However when...

SQL Server 2005 : SQL Server 2005 Integration Services

Possible to determine the datetime range of loaded data to staging table? - Hi all, I have created a SSIS package. In the control flow i have a Execute SQL task which will truncate...

How to make group of continuous tasks to execute based on single condition - Hello All, Could you please help me out here, i am totally stuck here, i have total 20 tasks in my ssis...

How to know which DTSX file was deployed in a server. - Hi all!!! I inherited an interface process (Oracle text file to SQL Server table) which uses a SSIS 2005 package...

SQL Server 2005 : SQL Server Newbies

Stripping out all non-numerical characters - SELECT accountid,New_Column = REPLACE(telephone1,SUBSTRING(telephone1,PATINDEX('[^0-9]',telephone1),1),'') INTO #YourNewResults FROM #TelephoneTable I have the script above which I thought would remove all non-numerical characters from the field...

creating a table to show month and year - I need to refer to a temp table which has all the months and dynamically shows the years. for example...

SQL Server 7,2000 : In The Enterprise

Log shipping a distribution server - Hi, I'm trying to get log shipping to work with a distribution server used in transactional replication. Shipping the distribution...

SQL Server 7,2000 : SQL Server Agent

How to migrate sql scripts from 08 to 05 - [font="Times New Roman"] Hi, We have developed some scripts in SQL Server 2008. Now, we need to migrate them to SQL Server...

SQL Server 2008 : SQL Server 2008 - General

Keeping all three environment(Dev,Stag,Prod) in sync - In our office we run jobs to keep these environment in sync. We get data from outside source and run...

Index question - I am trying to speed up a Query joining two tables. For simplicity sake, we'll just put it as I...

How do I clear out old Backups? - I change the days to retain on the Backup job from 14 to 3 before I left last Friday. I...

Using a Project to collect documents besides scripts - Is there any way to attach PDF files with SQL Scripts in either a SQL project in SSMS or in...

SSIS Question/Help - I have a very large data file set that routinely comes in for multiple clients I am managing. The header...

Needing help capturing mon th end and transactions posted > month - 90 - I need some help with SQL to capture transactions posted 90 days prior to month end for the last 12...

Concurrency handling - Hi All I have a table with identity column as PK.It is accessed by millions of users at a given time....

Table documentation advice - Hi, First of all I hope everyone has had a good Christmas! Just wanted to know if anyone documents there table...

Stress test SQL Physical and VM - Hello Does anyone have any recomendations for a tool to stress test SQL Server in a physical and a VM environment? My...

Need Suggestions on creating new table - Hi, I am having a condition that i need to create tables on run time that is Say for customer 1, if...

Distinct Data but order by on Priority Column Which is not present in select result. - Hi, Can you please help me how to get result in following format :- Scenario is three tables :- Client Table ---------------- autClientId vchName 1-----------C1 2-----------C2 3-----------C3 Order Table :- Each...

Monthly balance from two tables - I have following information with two tables [code="sql"] CREATE TABLE [dbo].[voucherCr]( [srno] [int] IDENTITY(1,1) NOT NULL, [vouchertype] [nvarchar](50) NULL, [voucherprefix] [nvarchar](50) NULL, [voucherno] [nvarchar](50) NULL, [crparty] [int]...

Scripts of logins are not done properly - Hi Friends, When am generating scripts of my database in sql 2008r2 am not getting the given permission of sql logins...

SQL Question - I am using Sql 2008 server and Dreamweaver cs4 and wanting to use asp.net. Or how to connect to Sql...

Query balance of account from two or more tables - I am having two tables as follows voucherCr srno vouchertype voucherprefix voucherno crparty cramount 1 PURCHASE P 1 2 55000 2 PAYMENT R 1...

Script through which we cannot store data into ldf file - [b]Query:[/b]I delete a table in sql server 2008,and,i do not want this entry to go to our ldf(log files).In this...

SSIS PACKAGE DOUBT - Hi Guys, I need a small help, I need to execute package with input parameter. The command which i have...

Design suggestion - Hi, I have to keep in a login history table the user's operating system / operating system version / browser / browser version / device...

big table insert advice - Hi I need to refresh data in a table from another table. It has about ~14 million records that need...

Len did not display right size - I use len function to find out varchar column data size but did not display right size. For example, in some...

Query Help - Hello I need to develop one logic. Please help me in that. Here is table and data [code="sql"] create table #temp (cStudentID VARCHAR(12), cCurrentGradeCode VARCHAR(12), cAnticipatedGradeCode VARCHAR(12), ) insert into...

Blocked Process Alert issues - This is really beginning to annoy me. I have Blocked Process Alerts on every single server in our environment. The alerts...

Adding a new Node on the existing SQL Server two nodes cluster. - I currently have a two nodes SQL Server cluster. Both nodes have identical hardware (dell R900 servers). I want to...

tempdb fileplacement - Hi all, I have a 1T, high I/O database in a SAN environment on which I have planned the database files...

SQL Server 2008 : T-SQL (SS2K8)

Creating 1 Select Query without Cursor, Function, temp object or Loop - Hi All I have a query that need to Rewrite it without cursor, function or loop to produce same result and...

Stored Procedure looses execution plan. - I have a stored procedure and when I run it First time it creates an execution plan and it takes...

Traversing a tree - I have different groups of items, some items are common in groups. I need a query that can combine groups...

NOT EXISTS - tortoise or horse ? - All, I have often worked on the below requirement. There are two tables and fetch the record from TableA which doesn't exists...

How often are indexes updated? - When an index is created with STATISTICS_NORECOMPUTE = OFF, how frequently is the index updated? Is it updated every time the...

Data type precedency ntext and hierarchyid - Tinyint and bit have a higher precedence than ntext. Why is this? Couldn't ntext we complex alphanumeric values? Tinyint and...

t-sql 2008 avoid a cartesian product - For a customer, I need to load data from excel 2010 spreadsheets into a sql server 2008 r2 database for...

Retrieve results from table with latest timestamp - Hi, I have a table with syn_trans with below cols: ( Id integer(10), Name varchar2(100), seq_no integer(10), seq_name varchar2(100), gp_name varchar2(100) ) There is no unique/primary key on the...

Finding a quote in a string - What can I use to find if a string contains a '? I know how to replace the quote with a...

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

SQL Server 2008 : Working with Oracle

www.goshoesonlineshop.com SDFEW02556 - Ed Hardy often uses embroidery, washing, splash-ink and other techniques, to create a feeling of decadence. Pick ed hardy sale...

SQL Server 2008 : SQL Server Newbies

Unable to access table in database diagrams. Receive message marked for deleation. - Hi, I am trying to access a table in the Database Diagrams however when I add it I get a message...

SQL Server 2012 Developer - I have a book that uses SQL Server 2008 and requires at least a developer edition to run SSIS. My...

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

Backing up & Restoring a DB - Hello, I have a db which I would like to backup and restore. Currently I go to db, right click tasks and...

Number weeks of work - Number weeks of work Hi there. I should check that the the number weeks of work for the five names is...

SQL Server 2008 : Security (SS2K8)

editting data in an encrypted column - Hi all, I'm attempting (unsuccessfully) to reset a password, the issue is that the column with the passwords in is...

SQL Server 2008 : SQL Server 2008 High Availability

Is database mirroring used much in the industry? - I'm curious because some features of SQL Server are not used that frequently in businesses. What types of business environments...

Suspending Database Mirroring - Hi All Does anybody know if suspending db mirroring prevents automatic failover should the primary fail ? I know that Suspending stops...

SQL Server 2008 : SQL Server 2008 Administration

ALTER AUTHORIZATION hangs and causes blocking - Hi, I'm trying to change the owner of a database using a command like the following, but it doesn't complete and...

Fragmentation size - Dear Friends 1-1-How to know the fragmentation size for whole database not only for certain table, to decide if it exexceeds...

Login failed - Hey guys, Ran into a problem this morning that I have never seen before... I created this login, SQL Server Authentication, mapped...

Ask for help in index - Hi, I'm a beginner in index. I've table and data as following, [code="sql"] CREATE TABLE [dbo].[Zip]( [City_Cd] [varchar](100) NULL, [Zip_Cd] [varchar](10) NULL ) ON [PRIMARY] GO SET ANSI_PADDING...

Define CPU terms/keywords ? - i am bit confused in following CPU keywords 1) dual-core 2) quad-core 3) [b]n[/b]-core processor 4) sockets 5) "number of processors" means ? It's...

Maintaince paln jobs failed - In The maintaince paln jobs Backup job was failed due to below ERROR Error: 2012-12-25 01:00:14.83 Code: 0xC00291EC Source: {0533C234-CC46-4024-A106-945C567A9ABF}...

Resource database is read-only - Even though the resource database is read-only, it is updated by SQL Server, right? Does sys.dm_db_missing_index_details have information that results in...

Large objects in SQL Server - I know large objects aren't stored in rows (e.g., varchar(max) and BLOB data). Filestream data is stored directly on the...

MAXDOP setting - I know that this is slighty a 'depends' question but our server is heaver user transactional server. Our SQL 2008R2 server...

Log File Size Issue - Hi, One my DB Log File Grown to large and now user were getting "transaction Log Full" error. When i checked server...

Hardware or Bad Queries = Performance issues? - Help Please!! How can one identify whether performance issues on a SQL 2008 R2 server are being caused by Hardware or...

JobName Not found error - HI All, I'm seeing a intresting error on one of my SQL Instance. Below is the script I'm running,, connecting to...

Using ::fn_dblog() to find who deleted the rows in a table. - Hi All, Recently some one deleted some rows from a table. I was asked to find out who did it. Since...

Career : Certification

MeasureUp practice test for 70-462 - Has anyone used the MeasureUp practice test to prepare for 70-462 - Administering Microsoft SQL Server 2012 Databases? [url=http://www.measureup.com/70-462-Administering-Microsoft-SQL-Server-2012-Databases-P3487.aspx]http://www.measureup.com/70-462-Administering-Microsoft-SQL-Server-2012-Databases-P3487.aspx[/url] In the past, MeasureUp...

We need the help of somebode with passed exam 70-432 - we have to take various examinations every year. We assumed that our sql exam expires at the end of February...

Good training center for SQL Server ? - I want to learn SQL Server 2012 and looking forward to MCSA and MCSE certifications. Can anyone tell me a...

Career : Employers and Employees

adivce about career - Hi, my self sudarshan,I am currently working in automation company from last 6 moths on sql server 2008, we generate...

Money vs Job Satisfaction, where is the balance? - Im currently in a job i really enjoy. As a consultant for a small specialized database consultancy firm I get...

Programming : Powershell

Query local SSMS server group with Powershell? - This may be a totally off the wall question, but it seems like it should be possible to do. Just...

SQLServerCentral.com : Anything that is NOT about SQL!

How's this for a Job Description - So I recently got this job description in the mail and at first glance it looks pretty good, but at...

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

Reporting Services : Reporting Services

Need help building hyperlink - We have report PDF Report descriptions in a subfolder below the Home page of our Report Server site. I want...

Can't view vertical axis on bar graph - Hi all, I'm new with charts in SSRS, but I've been getting better and better with your help. I threw a...

Data Warehousing : Integration Services

ssis 2012 package - I have successfully deployed my first package to sql 2012. The package runs fine in visual studio but when deployed...

SQL Server Integration and Browser services were stopped by its own - Hi all, In our environment Integration and Browser services were stopped by its own.I dono why its happening daily and manually...

Data Warehousing : Analysis Services

Failed Cube Process - Help - Hi! Help to problem.... SELECT [dbo_VW_Fato_Inspecao_Barra_Defeito].[dbo_VW_Fato_Inspecao_Barra_DefeitoQtdBarrasDefeito0_0] AS [dbo_VW_Fato_Inspecao_Barra_DefeitoQtdBarrasDefeito0_0],[dbo_VW_Fato_Inspecao_Barra_Defeito].[dbo_VW_Fato_Inspecao_Barra_DefeitoBitola_Inspecao0_1] AS [dbo_VW_Fato_Inspecao_Barra_DefeitoBitola_Inspecao0_1],[dbo_VW_Fato_Inspecao_Barra_Defeito].[dbo_VW_Fato_Inspecao_Bar

Microsoft Access : Microsoft Access

Damn Workgroup Administrator! - We've been using Workgroup for more years than I have toes. In Access 2010 when I use the DoCmd.RunCommand acCmdWorkgroupAdministrator...