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

Targeted Index Performance Improvements

Ian Stirk from SQLServerCentral.com

This article identifies which indexes are used when T-SQL code runs, and proposes ways of improving these targeted indexes, leading to better performing code. More »

RegexClean Transformation

Additional Articles from sqlis.com

Use the power of regular expressions to cleanse your data right there inside the Data Flow. This transformation includes a full user interface for simple configuration, as well as advanced features such as error output configuration. Two regular expressions are used, a match expression and a replace expression. The transformation is designed around the named capture groups or match groups, and even supports multiple expressions. More »

From the SQLServerCentral Blogs - Exporting Data Using BCP

BCP, or bulk copy program, has been around in SQL Server for a long time. It is a great way... More »

One of the interesting facts I saw a few years ago talked about storage in enterprise environments. There was research that showed many enterprise applications had 6 or 7 copies of their large databases inside the organization. In addition to the production copy, there were many other copies in use, resulting in an explosion of growth. That wasn't surprising, and it was one of the drivers for implementing compression in many databases.

While the cost of storage is constantly coming down, it's still expensive for enterprise class hardware, especially in a large SAN device. Today I wanted to ask those of you that work on real world systems to make a quick count of your own system, and let us know. I can't decide if 6 copies of a production database is high, or low.

How many copies, on average, of your production databases are in your company?

I suppose you could count backups as a copy, since it's disk space usage and you have to pay for it. If you count backups, let us know, but I'm thinking just about the test systems, development systems, HA or DR systems that might receive copies of the data. Some of those secondary systems might be in use for other purposes, such as reporting from readable secondaries in an AlwaysOn scenario. Whether they are or not, they are still copies of your database.

I used to think that four or five copies would be a lot, but with the advances in technology that allow different DR options, and the cheap local storage available on today's desktops and laptops, I wonder if seven or eight copies might be more accurate.

Take a count today; you might surprise yourself with the results.

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 - 23.5MB WMV
• Watch the iPod Video Podcast - 19.8MB MP4
• Listen to the MP3 Audio Podcast - 4.0MB MP3

The podcast feeds are available at sqlservercentral.mevio.com. You can also follow Steve Jones on Twitter:

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com. They have a great version of Message in a Bottle if you want to check it out.

I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.

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

Today's Question:

How many rows should the four execute queries return? (The answers specify the number of rows returned in each result set)

By Jamsheer M

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.

SQL Wait Stats Joes 2 Pros

You learn a lot when thrown into a crisis situation, where everyone is looking to you for answers. The system is down, the CEO is pleading to you for answers as the each minute cuts the company’s bottom line. When you are in a hurry, you need some new ideas right away. This book is your helpful tool. When used correctly, you can determine solutions and even related solutions to simple (yet lesser known) areas of SQL Server. Learn what causes SQL tasks to wait or site in queues. Even if your system is not in trouble right now, you can use what you will learn in this book to view the history of wait statistics.

Pick up your copy of this great book from Amazon today.

I execute the following two (2) T-SQL statements

Statement # 1

SELECT OrderID, OrderDate AS 'D4 between results' 
 FROM D4
 WHERE OrderDate BETWEEN '20110701' AND '20110731'

Statement # 2

SELECT OrderID, OrderDate AS 'D7 between results'
 FROM D7
 WHERE OrderDate BETWEEN '20110701' AND '20110731'

The question: How many rows are returned by each T-SQL statement

By bitbucket

Answer: Statment #1 returns 4 rows statement #2 returns 3 rows

Explanation: The precision is changed in each table, resulting in a different value for the insert based on the space used in the datetime2 type.

Ref: DateTime2 - http://msdn.microsoft.com/en-us/library/bb677335.aspx

» Discuss this question and answer on the forums

SQL Wait Stats Joes 2 Pros

You learn a lot when thrown into a crisis situation, where everyone is looking to you for answers. The system is down, the CEO is pleading to you for answers as the each minute cuts the company’s bottom line. When you are in a hurry, you need some new ideas right away. This book is your helpful tool. When used correctly, you can determine solutions and even related solutions to simple (yet lesser known) areas of SQL Server. Learn what causes SQL tasks to wait or site in queues. Even if your system is not in trouble right now, you can use what you will learn in this book to view the history of wait statistics.

Pick up your copy of this great book from Amazon today.

BackUp_DBs.sql

@fracionero from SQLServerCentral.com

Make backup several databases with a cursor More »

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

SQL Server 2005 : Administering

Freeing up some room - We have a DB that seems like it's way larger than it should be. So I ran sp_spaceused. Results: [code="sql"]database_name database_size unallocated space ULTIPRO_WSI 93386.63...

return parent child relation - PRNT CHLD POS 1050000 1000000 1 1050000 5000000 2 1050001 1000004 1 1050001 5000002 2 .............................................. .............................................. 1000000

Script Login with Permissions to User Databases - I need to Script a Login's Permissions to include permissions to each User Database. In this case I only need it...

stored proc occasionally hangs? - i have seen some strange behaviour with this sproc...this sproc has bunch of select/insert/ update statements. Occasionally this sproc hangs...

Unable to open SQL ErrorLog (SQL 2005) - Hi, On one of our SQL2005 Server Standard edition with SP3 (9.0.4220), I am not able to open SQL error log...

SQL Server 2005 : Backups

Restore Error!!! - Hi, I restore a database MyDB from Production SQL Server 2005 EE to a query server, sql server 2005 developer edition....

SQL Server 2005 : Business Intelligence

Implementing Surrogate Key - How to implement surrogate key ?

cognos 10 with SSAS: issues - Hi We're considering implementing a Cognos 10 reporting layer on top of SSAS 2008 R2 and were wondering if there are...

SQL Server 2005 : Development

How to get current week as 0 for any year using week - Hi, I need to display current week as 0 for any year in the report......using week column in week table. I have...

SQL Server 2005 : SQL Server 2005 General Discussion

execute a built command at command line from SSIS without using xp_cmdshell? Must I build a .bat? - I'm hoping I haven't developed my way into a corner. I'm tasked with deleting a file from a remote server...

out of memory - hello, we are on: Microsoft SQL Server 2005 - Ent Ed 32-bit SP4 mutltiple dbs running used for web applications On Jun-9: - SP4...

SQL Server 2005 High Memory Usage gradually over time - Hi guys, I have a sql server 2005 running on a 2003 R2 64bit server Lately I have noticed that...

BCP Command out/queryout Issue. - Can you please let me know the Answer ALTER Procedure BCP_Text_File ( @table varchar(100), @Cusip varchar(100), @direction varchar(15), @FileName varchar(100) ) as If exists(Select * from...

SQL Server 2005 : SS2K5 Replication

Database Mirroring - Hi Gurus, Due to some network glitch mirroring connection is disable. Is it possible establish the connection automatically once network connected...

Dealing with a database corruption on a merge replication subscriber - Merge replication is being used to provide inter-site high availability for a 7*24 database with 99.999% availability requirements (we have...

SQL Server 2005 : SQL Server 2005 Integration Services

SSIS 2008 question - I'm working with a DataFlow Task and have the souce and destinations set. The destination has Field1, Field2, Field3 and a...

Problem using expression in SSIS - Hi, Below is the expression i am trying to make work : [b] substring ("Archive_Full_20120731200002",1, (FINDSTRING( "Archive_Full_20120731200002","Full_", 1)-2))[/b] Whe I use the exact string(Archive_Full_20120731200002)...

SSIS Decrypt 7zip - Recently I downloaded 7zip to use to decrypt archives. Has anyone an example of how to pass in the password...

Blocking on server when running package - I have 2 packages package1 and package2. package2 contains 3 Data Flow Tasks. When I execute the package2 from BIDS, it...

SQL Server 2005 : T-SQL (SS2K5)

Select from stored proc for many records where single record ID being passed in - I'm stumped.. I have a fairly complex sproc that is expecting a single record identifier to be passed in, and...

Select from stored proc for many records where single record ID being passed in - I'm stumped.. I have a fairly complex sproc that is expecting a single record identifier to be passed in, and...

Flatten rows in a join - I have a stored procedure that currently brings back one row per policy number. A new table has been introduced...

XACT_ABORT with recursive stored proc - Hi, we use XACT_ABORT to rolback if statements failed. I have a scenario where I have a stored proc that call other...

Working Out Exact Age with DateDiff - Hi There, I am using the following in my SELECT Statement - DATEDIFF (year,dbo.[CORE_CO-PERSON].[D-O-B],GETDATE())AS Age, It is pulling me back the age...

Compare Table structure in two databases in sql server 2005 - Hi, I have several databases which are having same tables and table structures, but recently we have made several changes to...

SQL Server 2005 : SQL Server Newbies

Please Provide Me with Best Solution - This is an issue we had to solve for a customer a few weeks back. Imagine you had list of 10,000...

SQL Server 7,2000 : Backups

Determine Physical Device Name for a backupset. - I am running the folowing query: SELECT TOP 1 M.physical_device_name FROM msdb..backupset S JOIN msdb..backupmediafamily M ON M.media_set_id=S.media_set_id WHERE S.database_name = 'Gecko' AND s.type = 'D' ORDER...

SQL Server 7,2000 : Data Corruption

How to repair the table - when i running this... DBCC CHECKDB('PMSdb') WITH NO_INFOMSGS Server: Msg 8966, Level 16, State 1, Line 1 Could not read and latch page...

SQL Server 7,2000 : T-SQL

sql time intervals problem - Hi, I want to sum the times for each machine state for each shift. state0-machine stooped state1-machine with alarm state2-machine running I have table...

SQL Server 7,2000 : SQL Server Agent

SQL Agent Job "hangs" due to a wait type PREEMPTIVE_OS_PIPEOPS - We have a custom back end process we use to process batch jobs asynchronously through a queuing table. Many of...

SQL Server 2008 : SQL Server 2008 - General

Using Shared Datasources with SQL Server 2008 - HI Is there any way of using a shared datasource with an SQL Server 2008 TargetServerVersion? As far as i can see...

Please help!!! XACT_E_CONNECTION_DENIED!!!! - Msg 8510, Level 20, State 3, Line 17 Enlist operation failed: 0x8004d01d(XACT_E_CONNECTION_DENIED). SQL Server could not register with Microsoft Distributed Transaction...

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

Reporting source which way do you prefer it - When building the query for a reporting datasource how do you prefer to do it? examples might be, a stored proceedure,...

SQL 2008 R2 - peer to peer replication - Hi, We are setting up a POC of SQL replication between two indentical SQL server (including hardware). The concurrent updates (same...

Can you prent a DBA from viewing the data ? - Good Day , our IT Auditors suggested the database administrators should not be able to view the Production data . Is there...

UPDATE ERROR ON TABLE WITH NEW COLUMN - Hi All I have a problem I cant work out. Yesterday, I added a column to the end of a table....

Need Help with Automating Sliding Window Partition. - I need to implement something similar to this: http://www.sqlservercentral.com/articles/Partitioning/71655/ However, there are some differences between that article, and what I need to...

How to identify the list of tables and indexes that frequently most fragmented - Hi, I need to plan for Index reorg/rebuild on a large database. I do not want to do index reorg/rebuild on entire...

Automated Database Extraction with validation - First off let me preface this with I'm not a DBA, I'm decent with databases and I sling some code...

DB Down Alert - Hi All, Can you please help me out from this.. Client is asking me to setup some scripts on SQl Server 2000...

How to display current week as 0 for each year - Hi, I have a report requirement where I need to display current week as 0 for any season year user selects....like...

SSIS package fails in the scheduled job- How to add Decrypt password in command line - Any Example syntax please - Hi Experts, I am trying to schedule a job with ssis package.. The error message is: SQL Server Execute Package Utility Version...

keeping dev and live databases in sync - Does anyone know of any ways to keep a dev database up-to-date with changes on a live database without doubling...

UPGRADE SQL SERVER 2000 to 2008 R2 - Plan to upgrade (side by side) v. 2000 to 2008 R2 and set compatibility level to 90 (not 100) - all...

Record linkage advice required– Long read (YOU HAVE BEEN WARNED!) - Hi, I have the following table structure: [code="sql"]create table dbo.PreMerge ( master_id int, Title varchar(20), Forename varchar(50), Surname varchar(50), CompanyName varchar(200), CompanyName2 varchar(200), Address1 varchar(200), Address2 varchar(200), Address3 varchar(200), Address4 varchar(200), Town varchar(100), County varchar(100), Postcode

publish fact tables - what would be the best way to publish changes in a large fact table including deletes? we create one in SQL...

Database Redesign - I have to redesign the database based on the new business requirements Could someone please help me with the steps...

Is there an joiner much like the 8kSplitter? - I'm a fan of Jeff's 8k Splitter function, works a charm, but we have an application which feeds in a...

Providing datasets to customers - is replication the best approach? What is most commonly used? - Our product has one OLTP MSSQL database per customer, all using the same schema. We are in the early phases...

Running Total - Hi all I have a table with columns in that I want to do a running total on. I want...

DTS Package Maintance Plan... - One of my DTS Package Maintance Plan is getting fail. I investigated the job owner is having full permission and...

How do I remove my stored password/username in SSMS 2008? - Hi everyone, I entered a wrong password for my ssms login and I am unable to delete it. SSMS remembers this...

MS SQL + MS Visual Studio VS Wherescape RED - Have you ever heard of or used Wherescape RED to do the data warehouse developing? The fact is it is...

Merge & Transactional Publications on the same DB - Hello, I've setup a Merge Publication and Subscriber between Server A (Publisher) and Server B (Subscriber) that works perfectly. But now...

Errors when Enable the SSIS Package Configuration - Conenction refused using SQL Authentication - Hi all I have a problem when I use SSIS Package Configuration and Conenctions using SQL Authentication. I develop simple to complex...

SQL Server 2008 : T-SQL (SS2K8)

Update sortorder column to +1 - Hi Guys, I have a table like this Id----------Type--------ConditionId-------isActive------SortOrder 1001-------860----------2----------------1------------1 1002-------860----------3----------------1------------0 1003-------860----------5----------------1------------Null 1004-------860----------1----------------0------------Null 1005-------860----------4----------------0------------Null

trying to add alias for value generated by Where clause - Unless I comment out the alias 'AS RunningTotal' the query fails, but I need an alias for the column the...

Populate Quarters table - Hi All, I want to populate a table that will contain start and end dates for each quarter. My definition...

How to manage Transaction Log. - Hi All, I stheir a way we can manage Transaction log even before it fills up? I have some archive tables,...

Query Help- Cursor change into FOR WHILE LOOP, TEMP TABLE - I have created a STORED PROCEDURE, which includes 5 different CURSORs. The company I am working for has just informed...

SQL Server 2008 : Working with Oracle

Receive Data from Oracle Internet Directory - Hi all... I have to create a view that shows data from a OID (Oracle Internet Directory). I created a linked server,...

SQL Server 2008 : SQL Server Newbies

Script needed for DB Full Backup to Folder on nightly task - I am more on the Access Programming and SQL Query side with basic setup. SQL scripting for task is not...

Re-entering the DBA field - Looking for Advice/Suggestions - Hello Everyone, I'm a marketing professional looking to get back into the IT field that I left 10 years ago. I...

Cannot connect to SQL Server 2008 R2 remotely - I've attempting to create a Development box that host a SQL Server 2008 R2; however, I'm unable to connect to...

SQL Server 2008 : SQL Server 2008 High Availability

Log shipping and restore issue - Hi I have logshipping configured from production server to reporting server. We do some processing on reporting server and copy...

SQL Server 2008 : SQL Server 2008 Administration

Location of cdc tables - I configured CDC for one of our databases & enabled some tables. As expected, in SSMS I find the cdc-tables (including...

Why there is a Limitations on Index size (900 Bytes)? - Hi, Is it correct that all the pages, index/data/iam..etc pages does have the 8KB in size? if yes, why we have...

2008 R2 SP2 Install fails with no messages - I'm installing SP2 on my 2008 R2 servers and it has gone well for the most part. However, I have...

DB Backup Maintenance Plan Won't Execute - I've created a back up plan on one DB using the Maintenance Plans Wizard and the regular way (New Maintenance...

SQL Query Performance - Hi All I want to investigate Query Performance on my SQL Instance I've been looking at the sys.dm_exec_query_stats DMV How up to date...

Index Usage - Deleted - Hi All I'm using the below script to track index usage on a particular table. Why does the leaf_delete_count show zero? I...

Full DB Backup of filestreamed database slow after restart - Dear all, I have a database on SQL Server 2008 R2 enterprise edition whose backup suddenly running slow since a server...

Database corruption on a merge replication subscriber - Merge replication is being used to provide inter-site high availability for a 7*24 database with 99.999% availability requirements (we have...

model database stuck on Restore - Last night my backups failed on my maintenance plan and looks like my Model database was corrupted. It was restoring...

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

Career : Resumes and Job Hunters

How can I attract Senior Database Administrator candidates? - I have a few full-time DBA positions open on my team, one of which is for a Sr. DBA. We...

SQLServerCentral.com : Anything that is NOT about SQL!

Discussion: is e-Commerce making a mockery of SQL Server? - Some of you may have heard of my latest tales of woe, in that I am in an environment that...

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

When RS Scripter Uses Relative Paths - To Whom It May Concern I have discovered a bug in RSScripter when using relative paths. It appears when you run...

Parameter default won't delete. - I have a couple of reports that have start and end date parameters. I had thought about putting a default...

childt table not showing updates in report - :cool:I have a problem I have three tables all joined together One is the people.tbl the other the tanfactivity.tbl and...

Exporting to excel - How do I get my report to filter data by tabs when it is exported to excel?

How to Add a subreport in a parent report - I wan t to display data in parent report which has column [b]Position [/b] and [b]WeekDay[/b]. the position has data...

last day of the month parameter in ssrs - I occasionally need to schedule reports that run for an entire month and automatically email them. A monthly report, of...

Database Design : Design Ideas and Questions

Just a rant about how third parties design apps - And by third parties, I mean of all parties, Microsoft themselves. If a column needs to be added to a...

Pros and Cons - Table with loads of uniqe columns VS Row based keys with row data type - Hi, Which one is a better design strategy with respect to flexibility, performance and maintainability? A1: Defining Nullable columns in one table OR A2:...

Data Warehousing : Integration Services

Foreach Loop Failing to Retrieve Element - Hi all, I am stuck at figuring out why the following parameters for an Execute SQL Task and Foreach Loop...

Variables Display Gone - Hi all, I just got into work and started up BIDS to find that my variables display is blank. The...

WMI task and WQL query - Hi guys, I am using a WMI task to watch for files. We get about 20 txt files. when they...

Variable mapping - For Each Loop - Metadata Table (SQL ServerDB Table) COLUMNS INPUT TABLE Column1 Column2 OUTPUT TABLE ---------- ------- ------- -------------- TableA ColName1 NULL TableD TableA ColName2 NULL TableE TableA ColName3 NULL TableF TableB ColName4 ColName1 TableG TableB ColName5 NULL TableH TableB ColName6 NULL TableI Variables created: INPUT TABLE -

BCP in UNIX files - Hi guys. I am importing files from A SAP extract which use a UNIX format. The carriage return in UNIX...

Data Warehousing : Analysis Services

Calculated Members - How to compare values of selected month of Year A with values of same Month of Year B - HI, Month Over Month Comparison by Year. For example, I want to compare aggregated internet sales of "January 2012" with "January 2011". I...

Report Builder 3.0 Subreports - Error: Subreport could not be shown - Hi all, So I continue to delve into the bowels of Report Builder 3.0. My last post was asking about how...